MySQL（五） 索引失效

5. 表优化

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` VARBINARY(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');
 
SELECT * FROM ARTICLE;

• 查询category_id 为 1，且comment大于 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;

我们不难发现一个问题，虽然只有3条数据，但是查询类型却是全表扫描（type=ALL），并且使用的是 Using filesort文件排序显示。 那么随着表的增大到百万级的数据量时，这将会大大增加数据库的负担。

即： type为All，即最坏的情况；Extra中也出现了 Using filesort,也是最坏的情况。

• 查看索引情况

show index from article;

• 新建索引

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

• 再次 explain 分析查询语句

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

通过 possible_keys 和 key， 我们不难发现这已经使用了 索引 idx_article_ccv, 但是查询类型也由All 改成了 range，提高了效率。 但是Extra 依然为 Using filesort。 这里的原因是 range范围查询 之后的字段 将不会使用索引，导致索引失效的问题。

• 上述的并不是最优的建索引的做法，因此需要删除之前建的索引。

drop index idx_article_ccv on article;

再次查询,检验索引已经删除了。

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

• ​ 换个 建索引的做法：

create index idx_article_cv on article(category_id,views);

• 查看索引的结果

select index from article;

• explain 解析查询

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

我们可以发现现在的结果是我们能够接受的了。查询类型为ref，不再是All全表扫描。运用了索引，也不会出现 文本内存查询 。

总结：为什么第一次会出现索引失效的情况呢？原因是我们建立索引的时候是以 category、comments、views 的顺序建的 组合索引。又因为在SQL中的索引的底层是以 BTree 的方式建立的，那就要按照 B树 索引工作原理，先排序 category_id ，如果遇到相同的 category_id,则再排序comments，否则索引结束；如果遇到了comments，再排序views。 因为此时的 comments 字段在 联合索引 里处于 中间位置， 因此 comments>1 是一个范围值（即为 range），那么MySQL的 comments字段就失效，MySQL就无法利用索引再对 views 部分进行检索，即range类型查询字段后面的索引失效。

2. （两表）索引优化案例二：

• 建表操作：

CREATE TABLE IF NOT EXISTS `class`(
`id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`card` INT (10) UNSIGNED NOT NULL
);
CREATE TABLE IF NOT EXISTS `book`(
`bookid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`card` INT (10) UNSIGNED NOT NULL
);
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(