设为首页 - 加入收藏 ASP站长网（Aspzz.Cn）- 科技、建站、经验、云计算、5G、大数据,站长网！

热搜：重新数据创业者

当前位置：首页 > 站长学院 > MySql教程 > 正文

Mysql表关联字段未建索引导致查询慢优化后查询效率显著提升

发布时间：2022-06-29 12:35 所属栏目：115 来源：互联网

导读：今天收到用户反馈前端页面打开很慢。数据库服务器负载也告警了。登录服务器查询Mysql占用CPU过高，很直接打开show full process 跟慢查询发现很多以下sql都是在10S以上 # User@Host: gyw[gwy] @ [x.x.x.x] Id: 19513 # Query_time: 11.326904 Lock_time: 0.

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

（编辑：ASP站长网）

相关内容

网友评论

推荐文章

热点阅读