mysql数据库关联查询慢_Mysql表关联字段未建索引导致查询慢,优化后查询效率显著提升...

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

发布时间：2020-08-05 10:30:23

来源：ITPUB博客

阅读：77

作者：shawnloong

今天收到用户反馈前端页面打开很慢。数据库服务器负载也告警了。

登录服务器查询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毫秒。提升速度上千倍

免责声明：本站发布的内容(图片、视频和文字)以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。