【MySQL进阶】索引优化案例

单表索引优化案例

例如：一个 article 表的字段如下：

 create table article( 
 id int(10) unsigned primary key auto_increment comment '序号',
 author_id int(10) unsigned not null comment '作者id',
 category_id int(10) unsigned not null comment '分类id',
 comments int(10) unsigned not null comment '回帖的备注',
 views int(10) unsigned not null comment '浏览量',
 title varchar(255) not null comment '文章的标题',
 content text not null comment '文章的正文'
 );

插入数据

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 最多的 author_id。

1、题解：

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

2、通过 explain 执行计划进行分析来判断是否需要优化：

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

① Type 的值为 ALL，说明是全表扫描，这里表记录数为 3 ，记录数比较少，可以不用优化；
② Extra 中出现了 Using filesort ，说明使用到了文件类排序，极其需要优化！！！（需要建立索引）

3、优化过程：
1）、首先，因为 where 后使用到了 category_id，comments，views 三个字段，所以首先考虑建立这三个字段的 名为 index_cate_com_views 符合索引。

alter table article add index index_cate_com_views(category_id,comments,views);

2）、再次通过 explain 来分析查询语句：
如图，type 的值改变为 range，说明使用到了索引的范围查询，但是 Using filesort 依旧没有去除掉。

3）、分析原因：

首先，possible_keys 和 key 的值都是 index_cate_com_views，说明实际用到了符合索引，但是依旧使用了文件类排序。为什么呢？因为该查询语句使用到了符合索引的 comments 列的范围值导致符合索引的 views 列没有生效，也就是类似于索引失效。

证明我们的猜想：（将 comments 条件改为确定的一个值）

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

4）、对索引进行优化，由于不能更改题目，所以我们考虑更改符合索引，由于 comments 条件用到了范围值，我们就只建立 category_id 和 views 两个字段的符合索引，将先前的符合索引删除掉。

// 删除原先的索引
alter table article drop index index_cate_com_views;

// 添加新的索引
alter table article add index index_cate_views(category_id,views);

5）、再次通过 explain 分析该条查询语句

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

问题解除！！

两表索引优化案例

class 分类表的字段如下：

create table class( 
id int(10) unsigned primary key not null auto_increment, 
card int(10) unsigned not null
);

在 class 表中插入数据：

// 多次执行
// floor(x)函数：取小于 x 的最大整数
// rand()函数：产生一个0~1之间的随机数
insert into class(card) values(floor(1+(rand()*20)));

book 图书表的字段如下：

create table book(
book_id int(10) unsigned primary key not null auto_increment,
card int(10) unsigned not null
);

在 book 表中插入数据：

// 多次执行
// floor(x)函数：取小于 x 的最大整数
// rand()函数：产生一个0~1之间的随机数
insert into book(card) values(floor(1+(rand()*20)));

题目：请优化两表的左连接（left join）查询和右连接（right join）查询。

左连接（left join）

1）、通过 explain 执行计划分析左连接（left join）查询。

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

2）、结果分析：
type 都是 ALL，说明两个表都是全表扫描，需要通过索引进行优化。

3）、添加索引，问题来了索引应该建在哪个表上呢？
① 假设将索引建在 class 表上，再次通过 explain 分析查询语句：

// 在表 class 的 card字段建立索引
alter table class add index index_card(card);

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

② 假设将索引建在 book 表上，再次通过 explain 分析查询语句：

// 删除建立在 class 表中得索引，避免影响判断
alter table class drop index index_card;

alter table book add index index_card(card);

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

在表 class 的 card 字段上建立索引比较于在表 book 的 card 字段上建立索引，很明显在 book 表上建立索引的优化更加明显。

4）、分析原因（为什么在表 book 上建立索引更加适合）：
这是由左连接特性决定的， left join 条件用于确定如何从右表搜索行，左边表记录一定都有，所以右表是关键，一定要在左连接（left join）的右表上建立索引。

右连接（right join）

1）、通过 explain 执行计划分析右连接（right join）查询。

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

2）、同理左连接（left join）的特性，类比右连接（right join）的特性，即 right join 条件用于确定如何从左表搜索行，右表的记录一定都有，所以左表是关键，一定要在右连接（right join）的左表上建立索引。

alter table class add index index_card(card);

三表索引优化案例

在两表的基础上再新增一张 phone 表，其字段如下：

create table phone(
 phone_id int(10) unsigned not null primary key auto_increment,
 card int(10) not null
 );

插入数据：

// 执行多次
insert into phone(card) values(floor(1+(rand()*20)));

题目：
优化以下三表连接查询：

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

1）、先通过 explain 分析没有添加索引前的情况：

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

2）、根据左连接（left join）的特性，右表为最关键，所以在两个左连接的右表上建立索引也就是这里的 book 表和 phone 表的 card 字段。

alter table book add index index_card(card);

alter table phone add index index_card(card);

3）、再次通过 explain 分析添加索引后的结果：

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

后 2 行的 type 都是 ref，且总 rows 优化很好，效果明显。因此索引最好设置在经常查询的字段中。

结论

① 尽可能减少 join 语句中得嵌套循环的循环总次数；
② 永远使用小的结果集驱动大的结果集；
③ 保证 join 语句中被驱动表上的 join 条件字段已经被建立索引；
④ 当无法保证被驱动表的 join 条件字段被建立索引且内存资源充足的前提下，不要太吝啬 JoinBuffer 的设置。