我的编程空间,编程开发者的网络收藏夹
学习永远不晚

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

短信预约 -IT技能 免费直播动态提醒
省份

北京

  • 北京
  • 上海
  • 天津
  • 重庆
  • 河北
  • 山东
  • 辽宁
  • 黑龙江
  • 吉林
  • 甘肃
  • 青海
  • 河南
  • 江苏
  • 湖北
  • 湖南
  • 江西
  • 浙江
  • 广东
  • 云南
  • 福建
  • 海南
  • 山西
  • 四川
  • 陕西
  • 贵州
  • 安徽
  • 广西
  • 内蒙
  • 西藏
  • 新疆
  • 宁夏
  • 兵团
手机号立即预约

请填写图片验证码后获取短信验证码

看不清楚,换张图片

免费获取短信验证码

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

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

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

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

下载Word文档到电脑,方便收藏和打印~

下载Word文档

编程热搜

目录