背景
数据量大概是百万到千万级别吧, 然后能加的索引都加上了, 优化后, explain的结果都走索引了, 但是查询耗时还是10+s
修改前
表结构
CREATE TABLE `book` (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
`name` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '名称',
`publish_time` datetime(3) NOT NULL COMMENT '发布时间',
PRIMARY KEY (`id`) USING BTREE,
INDEX `idx_publishtime`(`publish_time`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1000001 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
CREATE TABLE `category` (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
`name` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '名称',
`type` bigint NOT NULL COMMENT '类型',
PRIMARY KEY (`id`) USING BTREE,
INDEX `idx_type`(`type`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 20 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
CREATE TABLE `book_category` (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
`book_id` bigint NOT NULL COMMENT '书本id',
`category_id` bigint NOT NULL COMMENT '类型id',
PRIMARY KEY (`id`) USING BTREE,
INDEX `idx_categoryid`(`category_id`) USING BTREE,
INDEX `idx_bookid`(`book_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2706220 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
查询的sql
SELECT
*
FROM
`book`
WHERE
id IN ( SELECT book_id FROM book_category LEFT JOIN category ON category.id = book_category.category_id WHERE category.type = 0 )
ORDER BY
publish_time DESC
LIMIT 12
explain结果
id | select_tyle | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | extra |
---|---|---|---|---|---|---|---|---|---|---|---|
1 | SIMPLE | book | index | PRIMARY | idx_publishtime | 7 | 12 | 100.00 | Backward index scan | ||
1 | SIMPLE | <subquery2> | eq_ref | <auto_distinct_key> | <auto_distinct_key> | 8 | jav.book.id | 1 | 100.00 | ||
2 | MATERIALIZED | book_category | ALL | idx_categoryid,idx_bookid | 2863419 | 100.00 | |||||
2 | MATERIALIZED | category | eq_ref | PRIMARY,idx_type | PRIMARY | 8 | jav.book_category.category_id | 1 | 47.37 | Using where |
可以看到book_category
这个表还有优化空间
第一次修改
基于上次explain结果, book_category
存在优化空间, 在该表的book_id
和category_id
同时加上索引
此时表结构为
CREATE TABLE `book_category` (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
`book_id` bigint NOT NULL COMMENT '书本id',
`category_id` bigint NOT NULL COMMENT '类型id',
PRIMARY KEY (`id`) USING BTREE,
INDEX `idx_categoryid`(`category_id`) USING BTREE,
INDEX `idx_bookid`(`book_id`) USING BTREE,
INDEX `idx_bookid_categoryid`(`book_id`, `category_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2706220 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
优化后explain结果如下:
id | select_tyle | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | extra |
---|---|---|---|---|---|---|---|---|---|---|---|
1 | SIMPLE | book | index | PRIMARY | idx_publishtime | 7 | 12 | 100.00 | Backward index scan | ||
1 | SIMPLE | <subquery2> | eq_ref | <auto_distinct_key> | <auto_distinct_key> | 8 | jav.book.id | 1 | 100.00 | ||
2 | MATERIALIZED | book_category | index | idx_categoryid,idx_bookid,idx_bookid_categoryid | idx_bookid_categoryid | 16 | 2863419 | 100.00 | Using index | ||
2 | MATERIALIZED | category | eq_ref | PRIMARY,idx_type | PRIMARY | 8 | jav.book_category.category_id | 1 | 47.37 | Using where |
可以看到, 虽然已经加上索引了, video_category
的查询用上了type
为index
, 已经用上了索引, 但是查询范围还是比较大, rows
就几十万条数据了
把sql改成下面这个, explain结果一样, 故需再做优化
SELECT
video.*
FROM
`video`
WHERE
EXISTS ( SELECT 1 FROM video_category LEFT JOIN category ON category_id = category.id WHERE video.id = video_id AND category.type = 0 )
ORDER BY
publish_time DESC
LIMIT 12;
再修改
表结构已经优化过了, 能走的索引也都加上了, explain结果也说明已经走索引了, 故此时需要想办法把rows结果降低
将sql改成
SELECT
*
FROM
book
WHERE
id IN (
SELECT
book.id
FROM
`book`
INNER JOIN book_category ON book.id = book_id
INNER JOIN category ON category_id = category.id
AND category.type = 0
)
ORDER BY
publish_time DESC
LIMIT 12;
执行结果: 0.135s
explain结果如下:
id | select_tyle | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | extra |
---|---|---|---|---|---|---|---|---|---|---|---|
1 | SIMPLE | book | index | PRIMARY | idx_publishtime | 7 | 8 | 100.00 | Backward index scan | ||
1 | SIMPLE | book | eq_ref | PRIMARY | PRIMARY | 8 | jav.book.id | 1 | 100.00 | Using index | |
1 | SIMPLE | book_category | ref | idx_categoryid,idx_bookid | idx_bookid | 8 | jav.book.id | 2 | 100.00 | ||
1 | SIMPLE | category | eq_ref | PRIMARY,idx_type | PRIMARY | 8 | jav.book_category.category_id | 1 | 47.37 | Using where; FirstMatch(book) |
最后发现, 并没有走前面加的idx_bookid_categoryid
索引, 虽然现在没走这个索引, 虽然对原sql查询性能有提高, 但是对优化后的sql并没起作用, 索引的维护对数据的更新和插入性能都有影响, 故将其删除, 此时的表结构恢复最初的模样
CREATE TABLE `book_category` (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
`book_id` bigint NOT NULL COMMENT '书本id',
`category_id` bigint NOT NULL COMMENT '类型id',
PRIMARY KEY (`id`) USING BTREE,
INDEX `idx_categoryid`(`category_id`) USING BTREE,
INDEX `idx_bookid`(`book_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2706220 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;