今天收到用户反馈前端页面打开很慢。数据库服务器负载也告警了。
登录服务器查询Mysql占用CPU过高，很直接打开show full process 跟慢查询发现很多以下sql都是在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, '%Y-%m-%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:00'
and odet.sell_time <= '2017-09-04 23:59:59'
GROUP BY sellerId, sellTime, pkgId, pkgPrice
WITH ROLLUP;


手动查看一下执行计划发现，使用Using temporary; Using filesort使用到了临时表，这样效率是最差的
explain 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, '%Y-%m-%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:00'
->and odet.sell_time <= '2017-09-04 23:59:59'
-> 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|
+----+--------------------+-------+--------+---------------+---------+---------+----------------------------+--------+---------------------------------+


尝试在在order_detail 上加一个复合索引(scenic_id,sell_time),但是依然如此未走索引，仔细检查发现order_checkticket order_detail_id未建索引。加上索引后执行计划如下
+----+--------------------+-------+--------+-------------------------+-----------------+---------+----------------+------+--------------------------------------------------------+
| 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)
我们看key已经走了索引使用idx_od_si_stime
查询速度只要0.01毫秒。提升速度上千倍