MySQL索引优化（一）查询索引

一、单表

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且comments大于1的情况下，views最多的article_id。
查询语句如下

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

结果如下

sql语句加上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，也是最坏的情况。优化是必须的。

2、优化查询

创建索引

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;

结果如下

可见type变成了range，这个是可以忍受的。但是extra里使用Using filesort仍是无法接受的。

但是我们修改查询语句，把comments > 1改为comments = 1

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

结果如下

会发现，range进一步优化成为了ref，Extra中的Using filesort也不见了。不过这条sql就违背了需求。(不过也可以得出结论，一般sql以等于为主，可以达到更好的优化。)

根据BTree索引的工作原理，先排序category_id，如果遇到相同的category_id则再排序comments，如果遇到相同的comments再排序views。
当comments字段在联合索引里处于中间位置时，因comments > 1条件是一个范围值（所谓range），MySQL无法利用索引再对后面的views部分进行检索，即range类型查询字段后面的索引无效，这也是为什么还有Using filesort的原因。

3、进一步优化查询

删除之前的索引，创建新的索引

drop index idx_article_ccv on article;
create index idx_article_cv on article(category_id, 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;

结果如下

可以看到，type变味了ref，Extra中的Using filesort也消失了，结果非常理想。

二、双表

创建数据表

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 (
    book_id int(10) unsigned not null auto_increment,
    card int(10) unsigned not null,
    primary key (book_id)
);
# 执行20次
insert into class(card) values (floor(1 + (rand() * 20)));
# 执行20次
insert into class(book) values (floor(1 + (rand() * 20)));

1、左连接（left join）

1）初步查询

执行，class相当于book的左表。

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

结果如下

看到了type中的all就需要优化了。

1）优化查询

删除book表索引

drop index Y on book;

给class的card添加索引（给左表添加索引）

alter table class add index Y (card);

执行

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

结果如下

可见第一行type变成了index。

2）进一步优化查询

给book的card添加索引（给右表添加索引）

alter table book add index Y (card);

执行

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

结果如下

可以看见第二行的type变为了ref，rows、Extra也被优化了，整体优化比较明显。

这个是由左连接特性决定的（左表全都有），left join条件用于确定如何从右表搜索行，左表一定是都有的。所以右表是关键点，一定要建立索引。

3）细节

对于给class的card添加索引优化不如给book的card添加索引优化效果明显。如何不更改索引，就能达到右表优化呢？
其实查询sql语句中两表位置互换下即可。

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

结果如下

同样可以达到效果。

2、右连接（right join）

思路和左连接一样
right join条件用于确定如何从左表查询，右表一定都有，所以左表是我们的关键点，一定要建立索引。

三、三表

新增新表

create table if not exists phone (
    phone_id int(10) unsigned not null auto_increment,
    card int(10) unsigned not null,
    primary key (phone_id)
);
# 执行20次
insert into phone(card) values (floor(1 + (rand() * 20)));

执行

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

结果如下

给phone和book创建索引

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

执行

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

结果如下

后两行的type都是ref且总rows优化很好，效果不错。同样的道理，这里的phone相当于book的右表，而book相当于class的右表。

四、结论

Join语句优化
1）尽可能减少Join语句中的嵌套循环总次数，永远用小结果集驱动大的结果集（例如书的类别是小结果集，书的名称是大结果集）。
2）优先优化嵌套循环的内层循环。
3）保证Join语句中被驱动表上Join条件字段已经被索引。
4）当无法保证被驱动表的Join条件字段被索引且内存资源充足的前提下，不要太吝啬JoinBuffer的设置。