MySQL查询优化(分段查询优化索引的使用)
MySQL查询优化(分段查询优化索引的使用)
[toc]
实验环境
| 表名称 | 记录数 |
|---|---|
| live_user_join_room | 8376813 |
测试表的创建语句
CREATE TABLE `live_user_join_room` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`uid` int(11) DEFAULT NULL COMMENT '用户id',
`room_id` int(11) DEFAULT NULL COMMENT '直播间id',
`is_out` int(11) NOT NULL DEFAULT '0' COMMENT '0 未退出 1 已退出',
`time` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '时间',
`course_id` bigint(20) DEFAULT NULL COMMENT '课程 id',
`source` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '来源',
`source_id` int(11) DEFAULT NULL COMMENT '来源ID',
`created_by` int(11) NOT NULL DEFAULT '0' COMMENT '创建者',
`updated_by` int(11) NOT NULL DEFAULT '0' COMMENT '更新者',
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
`deleted_at` timestamp NULL DEFAULT NULL COMMENT '删除时间',
PRIMARY KEY (`id`),
KEY `a` (`uid`,`room_id`,`course_id`),
KEY `b` (`room_id`,`uid`),
KEY `created_at` (`created_at`)
) ENGINE=InnoDB AUTO_INCREMENT=8376813 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC COMMENT='用户加入房间'IN条件结果数量 1920 条记录
查询索引
SHOW KEYS FROM `test`.`live_user_join_room`; | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| live_user_join_room | 0 | PRIMARY | 1 | id | A | 7705019 | (NULL) | (NULL) | BTREE | YES | |||
| live_user_join_room | 1 | a | 1 | uid | A | 253274 | (NULL) | (NULL) | YES | BTREE | YES | ||
| live_user_join_room | 1 | a | 2 | room_id | A | 2121308 | (NULL) | (NULL) | YES | BTREE | YES | ||
| live_user_join_room | 1 | a | 3 | course_id | A | 1958726 | (NULL) | (NULL) | YES | BTREE | YES | ||
| live_user_join_room | 1 | b | 1 | room_id | A | 12046 | (NULL) | (NULL) | YES | BTREE | YES | ||
| live_user_join_room | 1 | b | 2 | uid | A | 2339853 | (NULL) | (NULL) | YES | BTREE | YES | ||
| live_user_join_room | 1 | created_at | 1 | created_at | A | 6517174 | (NULL) | (NULL) | BTREE | YES |
优化前的sql, 平均查询用时 8.36s 左右
SELECT uid,COUNT(DISTINCT(room_id)) AS num FROM `live_user_join_room` FORCE INDEX(`created_at`)
WHERE room_id IN (
SELECT id FROM live_im_rooms WHERE pid=1 AND room_status!=5 AND room_status!=6
AND course_id NOT IN (SELECT id FROM courses WHERE pid=1 AND `type`=1 AND price=0)
)
AND created_at>'2020-11-07'
GROUP BY uid优化前的explain的结果
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | PRIMARY | live_user_join_room | (NULL) | range | a,b,created_at | created_at | 4 | (NULL) | 2143928 | 100.00 | Using index condition; Using where; Using MRR; Using filesort |
| 1 | PRIMARY | live_im_rooms | (NULL) | eq_ref | PRIMARY | PRIMARY | 4 | test.live_user_join_room.room_id | 1 | 8.10 | Using where |
| 3 | SUBQUERY | courses | (NULL) | ref | PRIMARY,pid_class_id | pid_class_id | 4 | const | 320 | 1.00 | Using where |
优化后的sql, 平均查询用时 0.572s 左右
SELECT uid,COUNT(DISTINCT(room_id)) AS num FROM `live_user_join_room` USE INDEX(a)
WHERE room_id IN (
SELECT id FROM live_im_rooms WHERE pid=1 AND room_status!=5 AND room_status!=6
AND course_id NOT IN (SELECT id FROM courses WHERE pid=1 AND `type`=1 AND price=0)
)
AND id>(SELECT MIN(id) FROM `live_user_join_room` WHERE created_at>'2020-11-07')
GROUP BY uidexplain的结果
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | PRIMARY | live_user_join_room | (NULL) | index | a,b | a | 19 | (NULL) | 7705451 | 33.33 | Using where; Using index |
| 1 | PRIMARY | live_im_rooms | (NULL) | eq_ref | PRIMARY | PRIMARY | 4 | test.live_user_join_room.room_id | 1 | 8.10 | Using where |
| 4 | SUBQUERY | live_user_join_room | (NULL) | range | created_at | created_at | 4 | (NULL) | 2143928 | 100.00 | Using where; Using index |
| 3 | SUBQUERY | courses | (NULL) | ref | PRIMARY,pid_class_id | pid_class_id | 4 | const | 320 | 1.00 | Using where |
差异的原因个人理解为
MySQL 每次查询只能使用一个索引, 优化后的sql虽然扫描行数多, 但是消除了 Using filesort , 解决了慢查询的问题