MySQL Execution Plan -- IN條件與ORDER BY組合優化

基於本次618大促JDV平臺支持大促過程中的表現，共從大促總結、能力沉澱、待提升項3個方向也進行了相應總結和反思。 ...

測試環境

MySQL版本： 5.7.27-30-log Percona Server (GPL), wsrep_31.39

涉及表結構：

CREATE TABLE `scout_job` (
  `task_id` varchar(22) NOT NULL DEFAULT '' COMMENT '任務id',
  `job_id` int(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'jobId',
  `env_id` varchar(10) NOT NULL DEFAULT '' COMMENT '環境id',
  `status` int(2) NOT NULL DEFAULT '0' COMMENT '0-初始化任務 1-任務執行中 2-執行成功 3-執行失敗 -1:任務被清理',
  `start_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '開始時間',
  `end_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '結束時間',
  PRIMARY KEY (`job_id`) USING BTREE,
  KEY `idx_envid` (`env_id`) USING BTREE,
  KEY `idx_id_status_endTime` (`env_id`,`status`,`end_time`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=3416771 DEFAULT CHARSET=utf8mb4 COMMENT='任務記錄表'

涉及SQL：

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對應執行計劃：

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
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: scout_job
   partitions: NULL
         type: ref
possible_keys: idx_envid,idx_id_status_endTime
          key: idx_envid
      key_len: 42
          ref: const
         rows: 152938
     filtered: 20.00
        Extra: Using index condition; Using where; Using filesort
1 row in set, 1 warning (0.00 sec)

查看滿足WHERE條件數據：

mysql> SELECT COUNT(1) FROM scout_job WHERE env_id = '393684' and status in (2,3);
+----------+
| COUNT(1) |
+----------+
|    94828 |
+----------+
1 row in set (0.15 sec)

通過profiling查看耗時情況：

mysql> SHOW PROFILE CPU,BLOCK IO,SWAPS FOR QUERY 1;
+--------------------------+----------+----------+------------+--------------+---------------+-------+
| Status                   | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out | Swaps |
+--------------------------+----------+----------+------------+--------------+---------------+-------+
| starting                 | 0.000065 |     NULL |       NULL |         NULL |          NULL |  NULL |
| checking permissions     | 0.000005 |     NULL |       NULL |         NULL |          NULL |  NULL |
| Opening tables           | 0.000014 |     NULL |       NULL |         NULL |          NULL |  NULL |
| init                     | 0.000031 |     NULL |       NULL |         NULL |          NULL |  NULL |
| System lock              | 0.000008 |     NULL |       NULL |         NULL |          NULL |  NULL |
| optimizing               | 0.000011 |     NULL |       NULL |         NULL |          NULL |  NULL |
| statistics               | 0.000156 |     NULL |       NULL |         NULL |          NULL |  NULL |
| preparing                | 0.000019 |     NULL |       NULL |         NULL |          NULL |  NULL |
| Sorting result           | 0.000004 |     NULL |       NULL |         NULL |          NULL |  NULL |
| executing                | 0.000002 |     NULL |       NULL |         NULL |          NULL |  NULL |
| Sending data             | 0.000005 |     NULL |       NULL |         NULL |          NULL |  NULL |
| Creating sort index      | 0.208818 |     NULL |       NULL |         NULL |          NULL |  NULL |
| innobase_commit_low (-1) | 0.000011 |     NULL |       NULL |         NULL |          NULL |  NULL |
| end                      | 0.000005 |     NULL |       NULL |         NULL |          NULL |  NULL |
| query end                | 0.000016 |     NULL |       NULL |         NULL |          NULL |  NULL |
| innobase_commit_low (-1) | 0.000008 |     NULL |       NULL |         NULL |          NULL |  NULL |
| closing tables           | 0.000011 |     NULL |       NULL |         NULL |          NULL |  NULL |
| freeing items            | 0.000033 |     NULL |       NULL |         NULL |          NULL |  NULL |
| cleaning up              | 0.000017 |     NULL |       NULL |         NULL |          NULL |  NULL |
+--------------------------+----------+----------+------------+--------------+---------------+-------+
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列上有序，即可避免排序操作，如：

mysql> DESC SELECT job_id FROM scout_job WHERE env_id = '393684' and status in (2) ORDER by end_time desc limit 2 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: scout_job
   partitions: NULL
         type: ref
possible_keys: idx_envid,idx_id_status_endTime
          key: idx_id_status_endTime
      key_len: 46
          ref: const,const
         rows: 34002
     filtered: 100.00
        Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)

相比IN中包含多個值的執行計劃，IN單個值的執行計劃中的rows仍較大，但Extra列中Using filesort已被消除。

通過profiling查看耗時情況：

+--------------------------+----------+----------+------------+--------------+---------------+-------+
| Status                   | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out | Swaps |
+--------------------------+----------+----------+------------+--------------+---------------+-------+
| starting                 | 0.000066 |     NULL |       NULL |         NULL |          NULL |  NULL |
| checking permissions     | 0.000005 |     NULL |       NULL |         NULL |          NULL |  NULL |
| Opening tables           | 0.000013 |     NULL |       NULL |         NULL |          NULL |  NULL |
| init                     | 0.000028 |     NULL |       NULL |         NULL |          NULL |  NULL |
| System lock              | 0.000007 |     NULL |       NULL |         NULL |          NULL |  NULL |
| optimizing               | 0.000013 |     NULL |       NULL |         NULL |          NULL |  NULL |
| statistics               | 0.000126 |     NULL |       NULL |         NULL |          NULL |  NULL |
| preparing                | 0.000016 |     NULL |       NULL |         NULL |          NULL |  NULL |
| Sorting result           | 0.000003 |     NULL |       NULL |         NULL |          NULL |  NULL |
| executing                | 0.000002 |     NULL |       NULL |         NULL |          NULL |  NULL |
| Sending data             | 0.000039 |     NULL |       NULL |         NULL |          NULL |  NULL |
| innobase_commit_low (-1) | 0.000004 |     NULL |       NULL |         NULL |          NULL |  NULL |
| end                      | 0.000002 |     NULL |       NULL |         NULL |          NULL |  NULL |
| query end                | 0.000009 |     NULL |       NULL |         NULL |          NULL |  NULL |
| innobase_commit_low (-1) | 0.000005 |     NULL |       NULL |         NULL |          NULL |  NULL |
| closing tables           | 0.000005 |     NULL |       NULL |         NULL |          NULL |  NULL |
| freeing items            | 0.000022 |     NULL |       NULL |         NULL |          NULL |  NULL |
| cleaning up              | 0.000011 |     NULL |       NULL |         NULL |          NULL |  NULL |
+--------------------------+----------+----------+------------+--------------+---------------+-------+

耗時為208ms的Creating sort index 已被優化掉，查詢從208ms優化到0.1毫秒。

對於IN包含多個值的情況，可以通過SQL改寫來優化：

# 改寫前SQL:
DESC SELECT job_id FROM scout_job WHERE env_id = '393684' and status in (2,3) ORDER by end_time desc limit 2 

# 改寫後SQL:
SELECT job_id FROM (
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
UNION 
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
) AS T1 ORDER BY end_time DESC LIMIT 2

由於MySQL的UNION限制，對於含有ORDER BY的查詢需要使用派生表的方式解決。

如果IN包含值較多，改寫後的SQL會看起來比較"複雜"，也可以考慮在應用程式端進行調整，將IN操作改為等值操作。