mysql优化-记一次sql优化的过程

背景

数据量大概是百万到千万级别吧, 然后能加的索引都加上了, 优化后, 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;