[讨论中] 一分40多的sql优化到秒出，但是还有问题

205 1 2024-3-13 09:55

本帖最后由李宁ning 于 2024-3-13 09:56 编辑

表结构
| xxl_job_log | CREATE TABLE `xxl_job_log` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `job_group` int NOT NULL COMMENT '执行器主键ID',
  `job_id` int NOT NULL COMMENT '任务，主键ID',
  `executor_address` varchar(255) DEFAULT NULL COMMENT '执行器地址，本次执行的地址',
  `executor_handler` varchar(255) DEFAULT NULL COMMENT '执行器任务handler',
  `executor_param` varchar(512) DEFAULT NULL COMMENT '执行器任务参数',
  `executor_sharding_param` varchar(20) DEFAULT NULL COMMENT '执行器任务分片参数，格式如 1/2',
  `executor_fail_retry_count` int NOT NULL DEFAULT '0' COMMENT '失败重试次数',
  `trigger_time` datetime DEFAULT NULL COMMENT '调度-时间',
  `trigger_code` int NOT NULL COMMENT '调度-结果',
  `trigger_msg` text COMMENT '调度-日志',
  `handle_time` datetime DEFAULT NULL COMMENT '执行-时间',
  `handle_code` int NOT NULL COMMENT '执行-状态',
  `handle_msg` text COMMENT '执行-日志',
  `alarm_status` tinyint NOT NULL DEFAULT '0' COMMENT '告警状态：0-默认、1-无需告警、2-告警成功、3-告警失败',
  PRIMARY KEY (`id`),
  KEY `I_trigger_time` (`trigger_time`),
  KEY `I_handle_code` (`handle_code`),
  KEY `idx_alarm_status` (`alarm_status`),
  KEY `i_job_id` (`job_id`) USING BTREE,
  KEY `idx_test` (`handle_code`,`alarm_status`,`trigger_code`,`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=88113582 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |

sql语句
SELECT id FROM `xxl_job_log` force index(idx_test )WHERE !((trigger_code in (0, 200) and handle_code = 0)OR(handle_code = 200))AND `alarm_status` = 0ORDER BY id ASCLIMIT 1000  ;

说明：不要在意两次sql的执行结果，因为有状态列，会更改，导致执行结果相差。

默认sql的执行计划：
root@localhost 09:42:16 [sipi_xxl_db]> desc SELECT id FROM `xxl_job_log` WHERE !( (trigger_code in (0, 200) and handle_code = 0) OR (handle_code = 200) ) AND `alarm_status` = 0 ORDER BY id ASC LIMIT 1000\G
*************************** 1. row ***************************
         id: 1
  select_type: SIMPLE
      table: xxl_job_log
partitions: NULL
      type: index
possible_keys: I_handle_code,idx_alarm_status,idx_test
      key: PRIMARY
   key_len: 8
      ref: NULL
      rows: 174320
   filtered: 0.29
      Extra: Using where
1 row in set, 2 warnings (0.08 sec)

默认执行计划的执行时间
root@localhost 10:00:03 [sipi_xxl_db]>  SELECT id FROM `xxl_job_log`
-> WHERE !(
->  (trigger_code in (0, 200) and handle_code = 0)
-> OR
-> (handle_code = 200)
->  )
->  AND `alarm_status` = 0
-> ORDER BY id ASC
-> LIMIT 1000  ;

+----------+
| id    |
+----------+
| 86366624 |
| 86367424 |
| 86368941 |
| 86368950 |
| 86368951 |
| 86368952 |
| 86368954 |
| 86368957 |
| 86368961 |
| 86368963 |
| 86368965 |
| 86368967 |
| 86368969 |
| 86368972 |
+----------+
14 rows in set, 1 warning (1 min 44.35 sec)

优化思路：
当时是handle_code最少，其次是alarm_status，然后是trigger_code，然后再包括一个id。但是现在看来应该是覆盖索引使查询更快了

root@localhost 10:06:08 [sipi_xxl_db]>  SELECT count(id) FROM `xxl_job_log`  WHERE !(trigger_code in (0, 200));
+-----------+
| count(id) |
+-----------+
|    18420 |
+-----------+

1 row in set, 1 warning (5.51 sec)

root@localhost 10:06:54 [sipi_xxl_db]>  SELECT count(id) FROM `xxl_job_log`  WHERE !(handle_code = 0);
+-----------+
| count(id) |
+-----------+
| 7546629 |
+-----------+
1 row in set, 1 warning (13.16 sec)

root@localhost 10:07:16 [sipi_xxl_db]> SELECT count(id) FROM `xxl_job_log` where  (handle_code = 200);
+-----------+
| count(id) |
+-----------+
| 7547483 |
+-----------+
1 row in set (7.58 sec)

root@localhost 10:08:03 [sipi_xxl_db]> SELECT count(id) FROM `xxl_job_log` where `alarm_status` = 0;
+-----------+
| count(id) |
+-----------+
| 7548087 |
+-----------+
1 row in set (4.39 sec)

优化方法
create index idx_test on xxl_job_log(handle_code,alarm_status,trigger_code,id);

优化后
root@localhost 10:02:45 [sipi_xxl_db]> SELECT id FROM `xxl_job_log` force index(idx_test ) -> WHERE !( -> (trigger_code in (0, 200) and handle_code = 0) -> OR -> (handle_code = 200) -> ) -> AND `alarm_status` = 0 -> ORDER BY id ASC -> LIMIT 1000  ;+----------+| id    |+----------+| 86369946 || 86370069 || 86370221 || 86370238 || 86370276 || 86370322 || 86370802 || 86371162 || 86371210 |+----------+9 rows in set, 1 warning (0.01 sec)root@localhost 10:02:57 [sipi_xxl_db]>

优化后的执行计划：
root@localhost 09:42:12 [sipi_xxl_db]> desc SELECT id FROM `xxl_job_log` force index(idx_test ) WHERE !( (trigger_code in (0, 200) and handle_code = 0) OR (handle_code = 200) ) AND `alarm_status` = 0 ORDER BY id ASC LIMIT 1000\G*************************** 1. row ***************************          id: 1  select_type: SIMPLE       table: xxl_job_log partitions: NULL       type: rangepossible_keys: idx_test       key: idx_test    key_len: 4       ref: NULL       rows: 35654    filtered: 98.30       Extra: Using where; Using index; Using filesort1 row in set, 2 warnings (0.00 sec)

慢sql

全部回复(1)

只看楼主