索引优化案例

一、索引分析

1、单表

建表SQL

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,`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且comments大于1的情况下，views最多的article_id

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）于是创建索引，最进本思路，where后面涉及字段全加上索引：

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

重新查询一次

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

为什么范围会导致索引失效？comments>1这个会导致索引失效

详情如下：

（2）解决办法：既然comments字段是一个敢为，因此加索引的时候不加上这个字段即可

[1]删除第一次建立的索引

drop index idx_article_ccv on article;

[2]第2次新建索引

#alter table `article` add index idx_article_cv(`category_id`,`views`);
create index idx_article_cv on article(category_id, views);

[3]第三次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也消失了，结果非常理想。

2、两表

建表SQL

 create table if not exists `class`( `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `card` INT(10) UNSIGNED NOT NULL, PRIMARY KEY(`id`) );
create table if not exists `book`( `bookid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `card` INT(10) UNSIGNED NOT NULL, PRIMARY KEY(`bookid`) );
insert into class(card) values(floor(1+rand()*20)); #执行20次
insert into book(card) values(floor(1+rand()*20)); #执行20次

（1）下面开始explain分析

explain select *from class left join book on class.card = book.card;

结论：type有ALL

（2）添加索引优化

尝试建索引，book表建索引Y，用在card字段

alter table book add index Y(card);

 

drop index Y on book;
alter table class add index Y(card);

删除book的索引Y，在class表建索引Y

3.三表

多加一个phone表

create table if not exists `phone`( `phoneid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `card` INT(10) UNSIGNED NOT NULL, PRIMARY KEY(`phoneid`) );
insert into phone(card) values(1+rand()*20); # 执行20次

三表left join查询

explain select *from class left join book on class.card = book.card left join phone on class.card = phone.card;

根据sql，应该在book表和phone表的card字段加索引

create index Y on book(card);
alter table phone add index Z(card);