Mysql进阶--索引单表优化

对一张表进行索引的优化。

1.一张表的索引优化策略，先创建一张表并插入部分数据，，执行语句，

CREATE TABLE IF NOT EXISTS article(
    id INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    author_id INT(10) UNSIGNED NOT NULL,
    category_id INT(10) UNSIGNED NOT NULL,
    views INT(10) UNSIGNED NOT NULL,
    comments INT(10) UNSIGNED NOT NULL,
    title VARCHAR(255) NOT NULL,
    content TEXT NOT NULL
);

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');

2.业务需求：查询category_id为1且comments 大于1的情况下，views最多的article_id，执行语句，

1	select id,category_id from article where category_id = 1 and comments > 1 order by views limit 1;

3.语句没问题，然后查看一下索引，执行语句，

1	show index from articel;

4.然后进行性能分析，使用Explain关键字，很明显，type是ALL，也就是查询了每一条数据，Extra里还出现了Using filesort，这可是九死一生的情况，数据量少还不明显，但是如果是百万级数据量，优化是势在必行，

5.开始优化，

新建索引(两种创建方式)：

1 2	# ALTER TABLE article ADD INDEX idx_article_ccv('category_id', 'comments', 'views'); create index idx_article_ccv on article(category_id, comments, views);

执行Sql语句后，再查看索引情况：

6.索引设置成功，再来进行性能分析情况，可以看到type变为了range，但是Extra还是Using where；Using filesort，

7.尝试修改Sql语句，尝试将comments > 1 修改为 = 1，可以看到Using filesort消息了，这是什么导致的？

1	Explain select id,category_id from article where category_id = 1 and comments = 1 order by views limit 1;

8.继续分析，两者最大的区别，就是>是一种范围，而=是代表一种常量，这就是原因：

这是因为按照B+Tree索引的工作原理，先排序 category_id，如果遇到相同的 category_id 则再排序comments，如果遇到相同的 comments 则再排序 views。
当comments字段在联合索引里处于中间位置时，因为comments>1条件是一个范围值（所谓 range），MySQL 无法利用索引再对后面的views部分进行检索，即 range 类型查询字段后面的索引无效。

9.删除刚刚创建的索引，

1	DROP INDEX idx_article_ccv ON article;

10.再分析查看 = 和 >的性能分析，可以发现，两者都一样了，type都是ALL，Extra都是Using filesort，因为都没有使用索引了，

11.再次回到原题，既然是range 后（comments > 1）导致了索引失效，那么是不是可以跳过comments，不创建comments的索引，创建新的索引，执行语句，可以和上面的索引进行对比，少了一个comments，

1 2	# ALTER TABLE article ADD INDEX idx_article_ccv('category_id', 'views'); create index idx_article_ccv on article(category_id, views);

12.再新索引的基础上进行性能分析，可以看到type变为了ref，Extra也没有了Using filesort，结果很理想

1	Explain select id,category_id from article where category_id = 1 and comments > 1 order by views limit 1;