Mysql索引优化单表、两表、三表实践

 

单表

新建表

?

1 2 3 4 5 6 7 8 9

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 INT(10) UNSIGNED NOT NULL, content TEXT NOT NULL );

 

插入数据

?

1 2 3 4

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	SELECT id,author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;

　　

使用EXPLAIN查看执行计划

?

1	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也是最坏的情况，需要优化。

 

开始优化

第一次优化
查看索引：

?

1	show index from article;

此时我们通过建立索引优化，但是我们不清楚索引建立到哪些列上是最合适的，所以需要尝试。

我们第一次建立联合索引，在三个字段上：category_id,comments,views。

新建与删除索引：

?

1 2	create index ide_article_ccv on article(category_id,comments,views); DROP INDEX ide_article_ccv ON article;

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

 

第二次优化：
create index ide_article_cv on article(category_id,views);

结论：type变成ref,Extra中的Using filesort也消失了，结果很理想。

 

 

保持更新中。。。