今天收到用户反馈前端页面打开很慢。数据库服务器负载也告警了。 登录服务器查询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 | +----+--------------------+-------+--------+---------------+---------+---------+----------------------------+--------+---------------------------------+
