MYSQL索引优化案例分析

（一）单表分析

案例一：
建article表

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

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，也是最坏的情况，所以必须优化。

开始建索引

第一次尝试：
建立category_id,comments,views组合索引

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

再次explain：

extra：using filesort还是在

当comments=1时，explain：

第二次尝试：

先删除之前的索引

drop index idx_article_ccv on article;

建立category_id,views

create index idx_article_cv on article(category_id,views);

再次explain：

结论：
可以看到type变成ref，extra中的using file sort也消失了，结果非常理想。

（二）两表分析

案例二：
建class和book表

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条

两表左连接：

下面开始explain：

添加索引优化：
方式一：在左连接的右表book上建索引

alter table book add index y(card);

方式二：在左连接的左表class上建索引

 alter table class add index y(card);

结论：
从什么可以看到方式一的第二行type变为ref，rows也变成了优化比较明显。
这是由左连接特性决定的，left join条件用于确定如何从右表搜索行，左边一定都有，
所以右边才是我们的关键点，一定需要建立索引。

注意：
左连接建右表，右连接建左表。

（三）三表分析

案例三：
建iphone表

create table if not exists phone(
phoneid int(10) UNSIGNED not null auto_increment,
card int(10) UNSIGNED not null,
PRIMARY KEY(phoneid)
)engine=innodb;

insert into phone(card) values(floor(1+(rand()*20)));#插20条

三表左连接：

explain：

在book和phone表上建索引：

alter table book add index y(card);
alter table phone add index z(card);

结论：
从上图看出后两行的type都为ref，且rows都优化很好，效果不错。因此索引最好设置在需要经常查询的字段上。

（四）结论

Join语句的优化

• 尽可能减少Join语句中的NestedLoop的循环总次数：“永远用小结果集驱动大的结果集”。
• 优先优化NestedLoop的内层循环。
• 保证Join语句中被驱动表上Join条件字段已经被索引。
• 当无法保证被驱动表的Join条件字段被索引且内存资源充足的前提下，不要太吝惜JoinBuffer的设置。