In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
2025-01-26 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >
Share
Shulou(Shulou.com)06/01 Report--
I received feedback from users today that the front-end page opened very slowly. The database server load is also alarmed.
Login server query Mysql takes up too much CPU. Open show full process directly and find that many of the following sql are above 10s.
# User@Host: gyw [gwy] @ [x.x.x.x] Id: 19513
# Query_time: 11.326904 Lock_time: 0.000327 Rows_sent: 69 Rows_examined: 1417696
SET timestamp=1504507662
SELECT odet.seller AS sellerId
Odet.agreementprice_id AS agreementpriceId
Odet.customer_id AS customerId
(SELECT realname
FROM sys_user suser
WHERE suser.id = odet.seller)
AS sellerName
Odet.pkgticket_id AS pkgId
Odet.pkgticket_price AS pkgPrice
DATE_FORMAT (odet.sell_time,'% Ymuri% mmury% d') AS sellTime
Sum (oct.tourist_number-IFNULL (ort.tourist_remain,0)) as totalPeople
Sum ((oct.tourist_number-IFNULL (ort.tourist_remain,0)) * odet.pkgticket_price) as totalMoney
(SELECT name
FROM scenic_pkgticket spkg
WHERE spkg.id = odet.pkgticket_id)
AS pkgticketName
FROM order_detail odet,order_checkticket oct
LEFT JOIN order_refundticket ort
On oct.id = ort.id
WHERE odet.id=oct.order_detail_id
And odet.scenic_id = 215
And odet.sell_time > = '2017-09-04 00 00'
And odet.sell_time odet.agreementprice_id AS agreementpriceId
-> odet.customer_id AS customerId
-> (SELECT realname FROM sys_user suser WHERE suser.id = odet.seller) AS sellerName
-> odet.pkgticket_id AS pkgId
-> odet.pkgticket_price AS pkgPrice
-> DATE_FORMAT (odet.sell_time,'% Ymuri% mmi% d') AS sellTime
-> sum (oct.tourist_number-IFNULL (ort.tourist_remain, 0)) as totalPeople
-> sum ((oct.tourist_number-IFNULL (ort.tourist_remain, 0)) *
-> odet.pkgticket_price) as totalMoney
-> (SELECT name
-> FROM scenic_pkgticket spkg
-> WHERE spkg.id = odet.pkgticket_id) AS pkgticketName
-> FROM order_detail odet, order_checkticket oct
-> LEFT JOIN order_refundticket ort
-> on oct.id = ort.id
-> WHERE odet.id = oct.order_detail_id
-> and odet.scenic_id = 215
-> and odet.sell_time > = '2017-09-04 00 purl 0000'
-> and odet.sell_time GROUP BY sellerId, sellTime, pkgId, pkgPrice WITH ROLLUP
+ -- +
| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+ -- +
| | 1 | PRIMARY | oct | ALL | NULL | NULL | NULL | NULL | 414589 | Using temporary; Using filesort |
| | 1 | PRIMARY | ort | eq_ref | PRIMARY | PRIMARY | 8 | sd_ets.oct.id | 1 | NULL |
| | 1 | PRIMARY | odet | eq_ref | PRIMARY | PRIMARY | 8 | sd_ets.oct.order_detail_id | 1 | Using where |
| | 3 | DEPENDENT SUBQUERY | spkg | eq_ref | PRIMARY | PRIMARY | 8 | func | 1 | NULL |
| | 2 | DEPENDENT SUBQUERY | suser | eq_ref | PRIMARY | PRIMARY | 8 | func | 1 | NULL |
+ -- +
Try to add a composite index (scenic_id,sell_time) on order_detail, but still do not leave the index, check carefully and find that order_checkticket order_detail_id does not have an index. After adding the index, the execution plan is as follows
+-+- -+
| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+-+- -+
| | 1 | PRIMARY | odet | range | PRIMARY,idx_od_si_stime | idx_od_si_stime | 14 | NULL | 183 | Using index condition; Using temporary; Using filesort |
| | 1 | PRIMARY | oct | ref | idx_oct_odi | idx_oct_odi | 8 | sd_ets.odet.id | 1 | NULL |
| | 1 | PRIMARY | ort | eq_ref | PRIMARY | PRIMARY | 8 | sd_ets.oct.id | 1 | NULL |
| | 3 | DEPENDENT SUBQUERY | spkg | eq_ref | PRIMARY | PRIMARY | 8 | func | 1 | NULL |
| | 2 | DEPENDENT SUBQUERY | suser | eq_ref | PRIMARY | PRIMARY | 8 | func | 1 | NULL |
+-+- -+
5 rows in set (0.00 sec)
We see that key has gone. Indexing uses idx_od_si_stime.
The query speed is only 0.01 milliseconds. Increase the speed by a thousand times
Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.
Views: 231
*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.