翼度科技»论坛 编程开发 mysql 查看内容

MySQL Execution Plan -- IN条件与ORDER BY组合优化

8

主题

8

帖子

24

积分

新手上路

Rank: 1

积分
24
测试环境

MySQL版本: 5.7.27-30-log Percona Server (GPL), wsrep_31.39
涉及表结构:
  1. CREATE TABLE `scout_job` (
  2.   `task_id` varchar(22) NOT NULL DEFAULT '' COMMENT '任务id',
  3.   `job_id` int(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'jobId',
  4.   `env_id` varchar(10) NOT NULL DEFAULT '' COMMENT '环境id',
  5.   `status` int(2) NOT NULL DEFAULT '0' COMMENT '0-初始化任务 1-任务执行中 2-执行成功 3-执行失败 -1:任务被清理',
  6.   `start_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '开始时间',
  7.   `end_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '结束时间',
  8.   PRIMARY KEY (`job_id`) USING BTREE,
  9.   KEY `idx_envid` (`env_id`) USING BTREE,
  10.   KEY `idx_id_status_endTime` (`env_id`,`status`,`end_time`) USING BTREE
  11. ) ENGINE=InnoDB AUTO_INCREMENT=3416771 DEFAULT CHARSET=utf8mb4 COMMENT='任务记录表'
复制代码
涉及SQL:
  1. SELECT job_id FROM scout_job WHERE env_id = '393684' and status in (2,3) ORDER by end_time desc limit 2;
复制代码
在系统没有任何压力情况下,该SQL执行时间超过200ms。
问题分析

查看SQL对应执行计划:
  1. mysql> DESC SELECT job_id FROM scout_job WHERE env_id = '393684' and status in (2,3) ORDER by end_time desc limit 2 \G
  2. *************************** 1. row ***************************
  3.            id: 1
  4.   select_type: SIMPLE
  5.         table: scout_job
  6.    partitions: NULL
  7.          type: ref
  8. possible_keys: idx_envid,idx_id_status_endTime
  9.           key: idx_envid
  10.       key_len: 42
  11.           ref: const
  12.          rows: 152938
  13.      filtered: 20.00
  14.         Extra: Using index condition; Using where; Using filesort
  15. 1 row in set, 1 warning (0.00 sec)
复制代码
查看满足WHERE条件数据:
  1. mysql> SELECT COUNT(1) FROM scout_job WHERE env_id = '393684' and status in (2,3);
  2. +----------+
  3. | COUNT(1) |
  4. +----------+
  5. |    94828 |
  6. +----------+
  7. 1 row in set (0.15 sec)
复制代码
通过profiling查看耗时情况:
  1. mysql> SHOW PROFILE CPU,BLOCK IO,SWAPS FOR QUERY 1;
  2. +--------------------------+----------+----------+------------+--------------+---------------+-------+
  3. | Status                   | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out | Swaps |
  4. +--------------------------+----------+----------+------------+--------------+---------------+-------+
  5. | starting                 | 0.000065 |     NULL |       NULL |         NULL |          NULL |  NULL |
  6. | checking permissions     | 0.000005 |     NULL |       NULL |         NULL |          NULL |  NULL |
  7. | Opening tables           | 0.000014 |     NULL |       NULL |         NULL |          NULL |  NULL |
  8. | init                     | 0.000031 |     NULL |       NULL |         NULL |          NULL |  NULL |
  9. | System lock              | 0.000008 |     NULL |       NULL |         NULL |          NULL |  NULL |
  10. | optimizing               | 0.000011 |     NULL |       NULL |         NULL |          NULL |  NULL |
  11. | statistics               | 0.000156 |     NULL |       NULL |         NULL |          NULL |  NULL |
  12. | preparing                | 0.000019 |     NULL |       NULL |         NULL |          NULL |  NULL |
  13. | Sorting result           | 0.000004 |     NULL |       NULL |         NULL |          NULL |  NULL |
  14. | executing                | 0.000002 |     NULL |       NULL |         NULL |          NULL |  NULL |
  15. | Sending data             | 0.000005 |     NULL |       NULL |         NULL |          NULL |  NULL |
  16. | Creating sort index      | 0.208818 |     NULL |       NULL |         NULL |          NULL |  NULL |
  17. | innobase_commit_low (-1) | 0.000011 |     NULL |       NULL |         NULL |          NULL |  NULL |
  18. | end                      | 0.000005 |     NULL |       NULL |         NULL |          NULL |  NULL |
  19. | query end                | 0.000016 |     NULL |       NULL |         NULL |          NULL |  NULL |
  20. | innobase_commit_low (-1) | 0.000008 |     NULL |       NULL |         NULL |          NULL |  NULL |
  21. | closing tables           | 0.000011 |     NULL |       NULL |         NULL |          NULL |  NULL |
  22. | freeing items            | 0.000033 |     NULL |       NULL |         NULL |          NULL |  NULL |
  23. | cleaning up              | 0.000017 |     NULL |       NULL |         NULL |          NULL |  NULL |
  24. +--------------------------+----------+----------+------------+--------------+---------------+-------+
  25. 19 rows in set, 1 warning (0.00 sec)
复制代码
根据profiling结果可以发现99.9%的耗时在Creating sort index环节,查询条件中包含IN操作,MySQL需要对满足env_id = '393684' and status in (2,3)条件的结果集进行排序(ORDER by end_time desc)然后取前2行(limit 2),由于满足条件记录较多,所以排序操作消耗时间较长。
问题优化

由于表上存在索引idx_id_status_endTime (env_id,status,end_time) ,如果IN条件仅包含1个可选值,通过该索引经过WHERE条件过滤后的数据在end_time列上有序,即可避免排序操作,如:
  1. mysql> DESC SELECT job_id FROM scout_job WHERE env_id = '393684' and status in (2) ORDER by end_time desc limit 2 \G
  2. *************************** 1. row ***************************
  3.            id: 1
  4.   select_type: SIMPLE
  5.         table: scout_job
  6.    partitions: NULL
  7.          type: ref
  8. possible_keys: idx_envid,idx_id_status_endTime
  9.           key: idx_id_status_endTime
  10.       key_len: 46
  11.           ref: const,const
  12.          rows: 34002
  13.      filtered: 100.00
  14.         Extra: Using where; Using index
  15. 1 row in set, 1 warning (0.00 sec)
复制代码
相比IN中包含多个值的执行计划,IN单个值的执行计划中的rows仍较大,但Extra列中Using filesort已被消除。
通过profiling查看耗时情况:
  1. +--------------------------+----------+----------+------------+--------------+---------------+-------+
  2. | Status                   | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out | Swaps |
  3. +--------------------------+----------+----------+------------+--------------+---------------+-------+
  4. | starting                 | 0.000066 |     NULL |       NULL |         NULL |          NULL |  NULL |
  5. | checking permissions     | 0.000005 |     NULL |       NULL |         NULL |          NULL |  NULL |
  6. | Opening tables           | 0.000013 |     NULL |       NULL |         NULL |          NULL |  NULL |
  7. | init                     | 0.000028 |     NULL |       NULL |         NULL |          NULL |  NULL |
  8. | System lock              | 0.000007 |     NULL |       NULL |         NULL |          NULL |  NULL |
  9. | optimizing               | 0.000013 |     NULL |       NULL |         NULL |          NULL |  NULL |
  10. | statistics               | 0.000126 |     NULL |       NULL |         NULL |          NULL |  NULL |
  11. | preparing                | 0.000016 |     NULL |       NULL |         NULL |          NULL |  NULL |
  12. | Sorting result           | 0.000003 |     NULL |       NULL |         NULL |          NULL |  NULL |
  13. | executing                | 0.000002 |     NULL |       NULL |         NULL |          NULL |  NULL |
  14. | Sending data             | 0.000039 |     NULL |       NULL |         NULL |          NULL |  NULL |
  15. | innobase_commit_low (-1) | 0.000004 |     NULL |       NULL |         NULL |          NULL |  NULL |
  16. | end                      | 0.000002 |     NULL |       NULL |         NULL |          NULL |  NULL |
  17. | query end                | 0.000009 |     NULL |       NULL |         NULL |          NULL |  NULL |
  18. | innobase_commit_low (-1) | 0.000005 |     NULL |       NULL |         NULL |          NULL |  NULL |
  19. | closing tables           | 0.000005 |     NULL |       NULL |         NULL |          NULL |  NULL |
  20. | freeing items            | 0.000022 |     NULL |       NULL |         NULL |          NULL |  NULL |
  21. | cleaning up              | 0.000011 |     NULL |       NULL |         NULL |          NULL |  NULL |
  22. +--------------------------+----------+----------+------------+--------------+---------------+-------+
复制代码
耗时为208ms的Creating sort index 已被优化掉,查询从208ms优化到0.1毫秒。
对于IN包含多个值的情况,可以通过SQL改写来优化:
  1. # 改写前SQL:
  2. DESC SELECT job_id FROM scout_job WHERE env_id = '393684' and status in (2,3) ORDER by end_time desc limit 2
  3. # 改写后SQL:
  4. SELECT job_id FROM (
  5. SELECT * FROM (SELECT job_id, end_time FROM scout_job WHERE env_id = '393684' AND STATUS IN (2) ORDER BY end_time DESC LIMIT 2) AS T2
  6. UNION
  7. SELECT * FROM (SELECT job_id, end_time FROM scout_job WHERE env_id = '393684' AND STATUS IN (3) ORDER BY end_time DESC LIMIT 2) AS T3
  8. ) AS T1 ORDER BY end_time DESC LIMIT 2
复制代码
由于MySQL的UNION限制,对于含有ORDER BY的查询需要使用派生表的方式解决。
如果IN包含值较多,改写后的SQL会看起来比较"复杂",也可以考虑在应用程序端进行调整,将IN操作改为等值操作。

来源:https://www.cnblogs.com/gaogao67/p/17611316.html
免责声明:由于采集信息均来自互联网,如果侵犯了您的权益,请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容,谢谢合作!

举报 回复 使用道具