MySQL高级——索引单表优化案例

一、建表语句

CREATE TABLE IF NOT EXISTS `article` (
	`id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
	`author_id` INT(10) UNSIGNED NOT NULL COMMENT '作者id',
	`category_id` INT(10) UNSIGNED NOT NULL COMMENT '分类id',
	`views` INT(10) UNSIGNED NOT NULL COMMENT '查看次数',
	`comments` INT(10) UNSIGNED NOT NULL COMMENT '评论内容',
	`title` VARBINARY(255) NOT NULL COMMENT '文章标题',
	`content` TEXT NOT NULL COMMENT '文章内容'
) COMMENT='文章表';
INSERT INTO `article`(`author_id`, `category_id`, `views`, `comments`, `title`, `content`) VALUES
(1, 1, 1, 1, '1', '1'),
(2, 2, 2, 2, '2', '2'),
(1, 1, 3, 3, '3', '3');

二、案例需求

• 查询 category_id 为1 且 comments 大于 1 的情况下,views 最多的 article_id。

三、索引单表优化分析

1、直接执行explain查看情况

• 执行案例需求的sql如下：

EXPLAIN SELECT id,author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;

• 结论：很显然,type 是 ALL,即最坏的情况。Extra 里还出现了 Using filesort,也是最坏的情况。优化是必须的。
• 执行索引查询sql如下：

SHOW INDEX FROM article

• 结论：只有主键索引

2、开始第一次优化

（1）、第一1新建复合索引

create index idx_article_ccv on article(category_id,comments,views);

（2）第1次执行EXPLAIN

EXPLAIN SELECT id,author_id FROM article WHERE category_id = 1 AND comments >1 ORDER BY views DESC LIMIT 1;

• 结论：type 变成了 range,这是可以忍受的。但是 extra 里使用 Using filesort 仍是无法接受的。但是我们已经建立了索引,为啥没用呢?
• 这是因为按照 BTree 索引的工作原理,先排序 category_id,如果遇到相同的 category_id 则再排序 comments；如果遇到相同的 comments 则再排序 views；
• 当 comments 字段在联合索引里处于中间位置时,因comments > 1 条件是一个范围值(所谓 range),MySQL 无法利用索引再对后面的 views 部分进行检索,即 range 类型查询字段后面的索引无效。

（3）、删除建立的复合索引

DROP INDEX idx_article_ccv ON article;

（4）再执行索引查询，只剩主键索引

SHOW INDEX FROM article

2、开始第二次优化

（1）、第2次新建复合索引

create index idx_article_cv on article(category_id,views);

（2）、第2次执行EXPLAIN

EXPLAIN SELECT id,author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;

• 结论：可以看到,type 变为了 ref,Extra 中的 Using filesort 也消失了,结果非常理想。