第十五章 索引优化分析（3）

五、索引优化

5.1 索引分析

单表

create table if not exists article(
id int not null primary key auto_increment,
author_id int not null,  #作者id
category_id int not null, # 分类
views int not null,   # 被查看的次数
comments int not null,  # 评论个数
title VARBINARY(255) not null, #标题，n个字节可变二进制数据类型
content text not null  #正文内容，存放最大长度为65535个字符的字符串。
);

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

案例：查询category_id为1且comments大于1的情况下，views最多的article_id。

explain select id,author_id,category_id,views,comments
from article
where category_id=1 and comments>1
ORDER BY views desc
limit 1

分析：

       先完成，后优化；
       先功能，后性能。

ALL：最坏的情况；
Using filesort：文件内排序，不好的。

开始优化：
方案1：新建索引+删除索引

# 三个字段建立一个ccv的复合索引
create index idx_article_ccv on article(category_id,comments,views);
show index from article

explain select id,author_id,category_id,views,comments
from article
where category_id=1 and comments>1
ORDER BY views desc
limit 1

原因：
本来是找固定的房间，但是这里是一个范围，所以进行了文件内排序。按照btree索引的工作原理，先排序category_id，再排序comments，最后排序views，当comments字段处于联合索引的中间位置的时候，因comments>1，条件是一个范围值（range），此时mysql无法利用索引对后面的views部分进行索引，即range字段后用面的索引是无效的。因此，范围后的索引会失效。因此，该索引的建立只解决了全表扫描的问题，并未优化文件内排序的问题，故删除重新优化。

drop index idx_article_ccv on article;
show index from article

方案2：重新建立索引

# 思路：将范围的要求绕开索引
create index idx_article_cv on article(category_id,views);

explain select id,author_id,category_id,views,comments
from article
where category_id=1 and comments>1
ORDER BY views desc
limit 1

drop index idx_article_cv on article;

两表

建表：

create table if not exists class(
id int not null primary key auto_increment,
card int not null
);

create table if not exists book(
bookid int not null primary key auto_increment,
card int not null
);

# floor函数返回小于等于该值的最大整数.
# RAND()函数调用可以在0和1之间产生一个随机数
insert into class(card) values
(floor(1+(rand()*20))),
(floor(1+(rand()*20))),
(floor(1+(rand()*20))),
(floor(1+(rand()*20))),
(floor(1+(rand()*20))),
(floor(1+(rand()*20))),
(floor(1+(rand()*20))),
(floor(1+(rand()*20)));

insert into book(card) values
(floor(1+(rand()*20))),
(floor(1+(rand()*20))),
(floor(1+(rand()*20))),
(floor(1+(rand()*20))),
(floor(1+(rand()*20))),
(floor(1+(rand()*20))),
(floor(1+(rand()*20))),

select * from class;
select * from book;

left左表全部内容

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

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

优化：

# 添加索引（先试一下class表）
alter table class add index Y(card);

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

drop index Y on class;

# 再试一下book
alter table book add index X(card);

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

drop index X on book;

分析：
比较左表和右表分别加索引的查询结果，我们发现，左连接表示的是左表都有，右表部分有，因此只用给右表加上索引即可，左表都需要。有的会说，为什么两个表不都加上索引，理论上是可以的，但是索引也会增加缓存，造成运行变慢。因此，对于连接性的，给子表加索引，可以最大化提升性能。

case：
生活中我们可以根据索引，来决定查询表的位置，而不是死板的写好查询，非得修改索引，尽量保持工作中的数据库的原始完整性。

三表

create table if not exists phone(
phoneid int not null primary key auto_increment,
card int not null
)engine = INNODB;

# floor函数返回小于等于该值的最大整数.
# RAND()函数调用可以在0和1之间产生一个随机数
insert into phone(card) values
(floor(1+(rand()*20))),
(floor(1+(rand()*20))),
(floor(1+(rand()*20))),
(floor(1+(rand()*20))),
(floor(1+(rand()*20))),
(floor(1+(rand()*20))),
(floor(1+(rand()*20))),
(floor(1+(rand()*20)));

select * from phone;

建立三表连接

# 三表连接
select * 
from class
left join book
on class.card=book.card
left join phone
on book.card=phone.card;

分析索引：

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

优化：

# 建立索引(右连接，建立子表book和phone）
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;

join语句优化总结：

主表：left左边的表，right右边的表，往往为第一个from后面的表；
子表：left右边的表，right左边的表，一般为嵌套在里面的表。

1. 尽可能减少join语句中的nestedloop的循环总次数（不要循环较多和嵌套），即永远用小结果集驱动大的结果集；例如：书比书本类别多，因此将class书类别作为主表（小表）来驱动book书（大表）。
2. 优先优化内层循环；
3. 保证join语句中被驱动的的表（子表）上的join条件的字段已经被索引；
4. 当无法保证被驱动表的join条件字段被索引且内存充足的前提下，应将joinbuffer（连接缓存）设置大一点。