mysql索引两个表_Mysql索引优化单表、两表、三表实践

单表

新建表

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

);

插入数据

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。

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

使用EXPLAIN查看执行计划

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

开始优化

第一次优化

查看索引：

show index from article;

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

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

新建与删除索引：

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也消失了，结果很理想。

保持更新中。。。