The lack of indexing of the associated fields in the Mysql table leads to slow query, and the query efficiency is significantly improved after optimization.
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
