表结构
| 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` = 0 ORDER BY id ASC LIMIT 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: range
possible_keys: idx_test
key: idx_test
key_len: 4
ref: NULL
rows: 35654
filtered: 98.30
Extra: Using where; Using index; Using filesort
1 row in set, 2 warnings (0.00 sec)
因为没有自动使用创建的索引，需要强制，于是改了sql发现可以自动使用了,or导致了没有自动使用索引
root@localhost 11:08:08 [sipi_xxl_db]> desc select * from (SELECT id FROM `xxl_job_log` WHERE ( (trigger_code not in (0, 200) ) and (handle_code != 200) )AND `alarm_status` = 0 union SELECT id FROM `xxl_job_log` WHERE handle_code != 0 and (handle_code != 200) AND `alarm_status` = 0 ) aa ORDER BY aa.id ASC LIMIT 1000\G
*************************** 1. row ***************************
id: 1 select_type: PRIMARY
table:
partitions: NULL
type: ALLpossible_keys: NULL
key: NULL key_len: NULL
ref: NULL
rows: 14217
filtered: 100.00
Extra: Using filesort
*************************** 2. row ***************************
id: 2
select_type: DERIVED
table: xxl_job_log
partitions: NULL
type: rangepossible_keys: I_handle_code,idx_alarm_status,idx_test
key: idx_test
key_len: 4
ref: NULL
rows: 35522
filtered: 40.00
Extra: Using where; Using index
*************************** 3. row ***************************
id: 3 select_type: UNION
table: xxl_job_log
partitions: NULL
type: rangepossible_keys: I_handle_code,idx_alarm_status,idx_test
key: idx_test
key_len: 4
ref: NULL
rows: 18
filtered: 50.00
Extra: Using where; Using index
*************************** 4. row ***************************
id: NULL
select_type: UNION RESULT
table:
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
filtered: NULL
Extra: Using temporary4 rows in set, 1 warning (0.00 sec)
root@localhost 11:08:28 [sipi_xxl_db]>
