一分40多的sql优化到秒出，但是还有问题
本帖最后由 李宁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 > 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 >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 >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 >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 > 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 > 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 > 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 >
优化后的执行计划：
root@localhost 09:42:12 > 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: 1select_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)
把order by limit 去掉就可以走idx_test索引了，可能是limit不参与成本计算，造成优化器选择索引错误
解决方法：
如你使用的 强制使用索引
或者是改写SQL语句，改成子查询等
页:
[1]