MySQL索引优化

一、索引分析

1.1 单表案例

1.1.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,
`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');

1.1.2 查询要求 

查询category_id为1且comments大于1的情况下,views最多的article_id

1.优化前

结论：很显然，type是all，即最坏的情况。extra里还出现了using filesort，也是最坏的情况。优化是必须的。

2.开始优化

新建索引+删除索引
alter table article add index idx_article_ccv ( 'category_id , 'comments', 'views' );

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

结论:
type变成了range,这是可以忍受的。但是extra里使用Using filesort仍是无法接受的。

但是我们已经建立了索引,为啥没用呢?

这是因为按照BTree索引的工作原理，

先排序category_id,

如果遇到相同的category_id,则再排序comments,如果遇到相同的comments则再排序views.

当comments字段在联合素引里处于中间位置时，

因comments > 1条件是一个范围值(所谓range),

MySQL无法利用索引再对后面的views部分进行检索,即range类型查询字段后面的索引无效。

3.删除第一次建立的索引

drop index index_article_ccv on article;

4.第2次新建索引

alter table article add index idx_article_cv ('category_id' , 'views');

create index idx article_cv on article(category_id, views);

结论：

可以看到，type变为了ref，extra中的using filesort也消失了，结果非常理想。

1.2 两表案例

1.2.1 sql

create table if not exists class(
id int unsigned not null auto_increment,
card int unsigned not null,
primary key (id)
);
 
create table if not exists book(
bookid int unsigned not null auto_increment,
card int unsigned not null,
primary key (bookid)
);
 
insert into class(card) values (floor(1+(rand()*20)));
insert into class(card) values (floor(1+(rand()*20)));
insert into class(card) values (floor(1+(rand()*20)));
insert into class(card) values (floor(1+(rand()*20)));
insert into class(card) values (floor(1+(rand()*20)));
insert into class(card) values (floor(1+(rand()*20)));
insert into class(card) values (floor(1+(rand()*20)));
insert into class(card) values (floor(1+(rand()*20)));
insert into class(card) values (floor(1+(rand()*20)));
insert into class(card) values (floor(1+(rand()*20)));
insert into class(card) values (floor(1+(rand()*20)));
insert into class(card) values (floor(1+(rand()*20)));
insert into class(card) values (floor(1+(rand()*20)));
insert into class(card) values (floor(1+(rand()*20)));

1.2.2 案例

1.下面开始explain分析

结论：type有all

2.添加索引优化

可以看到第二行的type变为了ref，rows也减少了许多

3.第二次explain

这是由左连接特性决定的。left join条件用于确定如何从右表搜索行，左边一定都有。

所以，右边是我们的关键点，一定需要建立索引。

4.看一个右连接查询

优化较明显。这是因为RIGHTJOIN条件用于确定如何从左表搜索行,右边一定都有,所以左边是我们的关键点,一定需要建立索引。

1.3 三表案例

1.3.1 sql

create table if not exists phone(
phoneid int unsigned not null auto_increment,
card int unsigned not null,
primary key (phoneid)
);
 
insert into phone(card) values (floor(1+(rand()*20)));
insert into phone(card) values (floor(1+(rand()*20)));
insert into phone(card) values (floor(1+(rand()*20)));
insert into phone(card) values (floor(1+(rand()*20)));
insert into phone(card) values (floor(1+(rand()*20)));
insert into phone(card) values (floor(1+(rand()*20)));
insert into phone(card) values (floor(1+(rand()*20)));
insert into phone(card) values (floor(1+(rand()*20)));
insert into phone(card) values (floor(1+(rand()*20)));
insert into phone(card) values (floor(1+(rand()*20)));
insert into phone(card) values (floor(1+(rand()*20)));
insert into phone(card) values (floor(1+(rand()*20)));
insert into phone(card) values (floor(1+(rand()*20)));
insert into phone(card) values (floor(1+(rand()*20)));
insert into phone(card) values (floor(1+(rand()*20)));
insert into phone(card) values (floor(1+(rand()*20)));
insert into phone(card) values (floor(1+(rand()*20)));
insert into phone(card) values (floor(1+(rand()*20)));
insert into phone(card) values (floor(1+(rand()*20)));
insert into phone(card) values (floor(1+(rand()*20)));

1.3.2 案例

1.explain分析

2.优化

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

结论：join语句的优化

尽可能减少join语句中的NestedLoop(嵌套循环)的循环总次数：“永远用小结果集驱动大的结果集”小表驱动大表。

优先优化NestedLoop的内存循环；

保证join语句中被驱动表上join条件字段已经被索引；

当无法保证被驱动表的join条件字段被索引且内存资源充足的前提下，不要太吝啬joinBuffer的设置。

二、索引失效

2.1 建表sql

create table staffs(
id int auto_increment,
name varchar(24) not null default " comment '姓名'，
age int not null default 0 comment '年龄"，
pos varchar(20) not null default " comment '职位'，
add_time timestamp not null default current_timestamp comment '入职时间',
primary key(id)
);charset utf8 comment '员工记录表;

insert into staffs(name,age,pos,add_time) values('z3,22,'manager',nowO);
insert into staffs(name,age,pos,add_time) values('July ,23,'dev',now0);
insert into staffs(name,age,pos,add_time) values(2000',23,'dev ,now0);

select * from staffs;

create index idx_staffs_nameAgePos on staffs(name,age,pos);

2.2 全值匹配我最爱

2.3 最佳左前缀法则

如果索引了多个列，要遵循左前缀法则，指查询需要从索引的最左前列开始，并且按照索引列中的顺序去查询，若跳过则索引失效。

最佳左前缀法则：

带头大哥不能死，中间兄弟不能断。

这就类似于爬楼梯，

一楼楼梯被干掉了，怎么爬上二楼三楼？

二楼楼梯被干掉了，一楼可以到，但是怎么爬到三楼？

2.4 索引列上做某些操作，会导致索引失效

不在索引列上做任何操作(计算、函数、类型转换(手动or自动))，会导致索引失效，转向全表扫描

索引列上使用了表达式，如where substr(a,1,3)= "hhh'，where a =a +1，表达式是一大忌讳，再简单mysq也不认。

有时数据量不是大到严重影响速度时，一般可以先查出来，比如先查所有有订单记录的数据，再在程序中去筛选

2.5 存储引擎不能使用索引中范围条件右边的列

范围之后全失效

 2.6 尽量使用覆盖索引(只访问索引列(查询列和索引列一致))，减少select *

2.7 mysql在使用不等于运算符(!=或<>)时，无法使用索引，会导致全表扫描。

2.8 is null和is not null，无法使用索引

2.9 like若以通配符开头('%aa')，则索引失效，全表扫描

百分like写右面

问题：

如何解决like%字符串%时，索引不被使用的问题？

 2.10 字符串不加引号导致索引失效

2.11 少用or，用它来连接条件时，索引失效

2.12 小总结

1.带头大哥不能死

2.中间兄弟不能断

3.索引列上无计算

4.like百分加右边

5.范围之后全失效

6.字符串里有引号

全值匹配我最爱，最左前缀要遵守；
带头大哥不能死，中间兄弟不能断；
索引列上少计算，范围之后全失效；
Like百分写最右，覆盖索引不写星；
不等空值还有or，索引失效要少用；
VAR引号不可丢，SQL高级也不难！

三、索引优化面试题

3.1 建表sql

create table test03(
id int not null auto_increment,
c1 char(10),
c2 char(10),
c3 char(10),
c4 char(10),
c5 char(10),
primary key(id)
);
 
insert into test03(c1,c2,c3,c4,c5) values ('a1','a2','a3','a4','a5');
insert into test03(c1,c2,c3,c4,c5) values ('b1','b2','b3','b4','b5');
insert into test03(c1,c2,c3,c4,c5) values ('c1','c2','c3','c4','c5');
insert into test03(c1,c2,c3,c4,c5) values ('d1','d2','d3','d4','d5');
insert into test03(c1,c2,c3,c4,c5) values ('e1','e2','e3','e4','e5');
 
select * from test03;
 
【建索引】
create index idx_test03_c1234 on test03(c1,c2,c3,c4);
show index from test03;

3.2 explain分析

索引两大功能，一个是查找，一个是排序。这里c3就是用于排序，它没有统计到这里面。

只用c1一个字段索引，但是c2、c3用于排序，无filesort。

出现了filesort，我们建的索引是1234，它没有按照顺序来，3，2颠倒了。

用c1、c2两个字段索引，但是c2、c3用于排序，无filesort。

本例有常量c2的情况。

group by分组之前必排序，所以，跟order by情况基本是一致的。

定值、范围、排序，一般order by是给个范围。

group by基本上都需要排序，会有临时表产生。

3.3 一般性建议

1.对于单键索引，尽量选择针对当前query过滤性更好的索引。

2.在选择组合索引的时候，当前query中过滤性最好的字段，在索引字段顺序中的位置越靠前越好。

3.在选择组合索引的时候，尽量选择可以包含当前query中的where子句里更多字段的索引。

4.尽可能通过分析统计信息和调整query的写法，来达到选择合适索引的目的。

视频教程