4.索引优化小技巧

时间:2022-05-17 22:46:25

测试数据初始化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');
#两表
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) not null auto_increment,
card int(10) unsigned not null,
primary key(book_id)

);

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 book(card) values(floor(1+(rand()*20)));
insert into book(card) values(floor(1+(rand()*20)));
insert into book(card) values(floor(1+(rand()*20)));
insert into book(card) values(floor(1+(rand()*20)));
insert into book(card) values(floor(1+(rand()*20)));
insert into book(card) values(floor(1+(rand()*20)));
insert into book(card) values(floor(1+(rand()*20)));
insert into book(card) values(floor(1+(rand()*20)));
#三表
create table if not exists phone (
phone_id int(10) not null auto_increment,
card int(10) unsigned not null,
primary key(phone_id)

)engine=innodb;

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

执行案列

#查询category_id 为1 且comments大于1的情况下,views最多的article_id.
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,也是最坏的情况,优化是必须的
#开始优化
#1.1 新建索引 + 删除索引
create index idx_article_ccv on article(category_id,comments,views);
#范围之后全失效--后面会说
explain select id,author_id from article where category_id =1 and comments =1 order by views desc limit 1;
#1.2 第二次explain
drop index idx_article_ccv on article;
explain select id,author_id from article where category_id =1 and comments > 1 order by views desc limit 1;
#####################
#下面开始explain 分析
explain select * from class left join book on class.card=book.card;
#结论 type 有all
#添加索引优化
alter table book add index Y(card);
#第二次解析explain
explain select * from class left join book on class.card=book.card;
#可以看到第二行的type变成了ref,rows也变少了优化比较明显
#这是由左连接的特性决定的。left join 条件用于确定如何从右表搜索行,左表一定都有
#所以右边是我们的关注点,一定要建立索引
#删除旧索引+ 新建 第三次explain
drop index Y on book;
alter table class add index Y(card);
explain select * from class left join book on class.card=book.card;

#然后来看一个右连接查询:
#优化比较明显,这是因为Right join条件用于确定如何从左表搜索行,右表一定都有,所以左表是我们关注点,一定要建立索引
explain select * from class right join book on class.card=book.card;
drop index X on class;
alter table class add index Y(card);

####################
explain select * from class left join book on class.card=book.card left join phone on book.card=phone.card;
alter table phone add index Z(card);
alter table book add index Y(card);

#后两行的type 都是ref 且中rows优化较好,效果不错。因此索引最好设置在需要经常查询的字段中。
Join语句的优化
注意:
尽可能的减少Join语句中的NestedLoop的循环总次数:"永远用小结果集驱动大的结果集"
优先优化NestedLoop的内层循环
保证Join语句中被驱动的表上Join条件字段已被索引

当无法保证被驱动表的Join条件字段被索引且内存资源充足的前提下,不要太吝啬使用JoinBuffer的设置
优化需要优化的Query
定位优化对象性能瓶颈
从Explain入手
永远小结果集驱动大结果集#
尽可能在索引中完成排序
只取自己需要的Column
仅仅使用最有效的过滤条件
尽可能避免复杂的join和子查询

索引失效问题

create table staffs (
id int primary key  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 '入职时间'
)charset utf8 comment '员工记录表';insert into staffs(name,age,pos,add_time) values('wxt',22,'manager',now());
insert into staffs(name,age,pos,add_time) values('wxt2',23,'test',now());
insert into staffs(name,age,pos,add_time) values('wxt3',24,'dev',now());
insert into staffs(name,age,pos,add_time) values('wxt4',25,'dev',now());
select * from staffs;
alter table staffs add index index_staff_nameAgePos(name,age,pos);
#1.全值匹配我最爱
explain select * from staffs where name="wxt3";
explain select * from staffs where name="wxt3" and age=24;
explain select * from staffs where name="wxt3" and age=24 and pos='dev';
explain select * from staffs where age=24 and pos='dev';
explain select * from staffs where pos='dev';
#2.最佳左前缀法则 如果索引了多例,要遵守最左前缀法则。指的是查询从索引的最左前列开#始并且不跳过索引中的列。
explain select * from staffs where name="wxt3" and pos='dev';
explain select * from staffs where age=24 and pos='dev';
explain select * from staffs where  pos='dev';
#3.不在索引列上做任何操作(计算、函数、(自动or手动)类型转换),会导致索引失效而转向全表扫描
explain select * from staffs where left(name,4)='wxt3';
explain select * from staffs where name='wxt3';
#4.存储引擎不能使用索引中范围条件右边的列
explain select * from staffs where name="wxt3" and age=24 and pos='dev';
explain select * from staffs where name="wxt3" and age>23 and pos='dev';
#范围之后全失效(注意key-len的变化)
#5.尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致)),减少select*
explain select  * from staffs where name="wxt3" and age=24 and pos='dev';
explain select  name,age,pos from staffs where name="wxt3" and age=24 and pos='dev';
explain select  name,age,pos from staffs where name="wxt3" and age >23 and pos='dev';
explain select  name,age,pos from staffs where name="wxt3" and age =24;
explain select  name from staffs where name="wxt3" and age >23 and pos='dev';
#6.mysql在使用不等于(!=或者<>)的时候无法使用索引会导致全表扫描
explain select  * from staffs where name="wxt3";
explain select  * from staffs where name!="wxt3";
explain select  * from staffs where name<>"wxt3";
#7.is null,is not null 也无法使用索引
explain select  * from staffs where name is null;
explain select  * from staffs where name is not null;
#8.like以通配符开头('%abc...')mysql索引失效会变成全表扫描操作
explain select  * from staffs where name like '%wxt3%';
explain select  * from staffs where name like '%wxt3';
explain select  * from staffs where name like 'wxt3%';
#9.字符串不加单引号索引失效(如果列类型是字符串,那一定要在条件中将数据使用引号引用起来。否则不使用索引)
explain select * from staffs where name=200;
explain select * from staffs where name='200';
#10.少用or,用它连接时会索引失效
explain select * from staffs where name='wxt' or name='wxt3';

问题:解决like'%字符串%'索引不被使用的方法

1、可以使用主键索引
2、使用覆盖索引,查询字段必须是建立覆盖索引字段
3、当覆盖索引指向的字段是varchar(380)及380以上的字段时,覆盖索引会失效!

create table tbl_user(
id int(11) not null auto_increment,
name varchar(20) default null,
age int(11) default null,
email varchar(20) default null,
primary key(id)
)engine=innodb auto_increment=1 default charset=utf8;

insert into tbl_user(name,age,email) values('1aa1',21,'aa@163.com'); 
insert into tbl_user(name,age,email) values('2aa2',21,'bb@163.com'); 
insert into tbl_user(name,age,email) values('3aa3',21,'cc@163.com'); 
insert into tbl_user(name,age,email) values('4aa4',21,'dd@163.com'); 

#before index
explain select name ,age from tbl_user where name like '%aa%';
explain select id from tbl_user where name like '%aa%';
explain select name from tbl_user where name like '%aa%';
explain select age from tbl_user where name like '%aa%';

explain select id, name from tbl_user where name like '%aa%';
explain select id,name ,age from tbl_user where name like '%aa%';
explain select name ,age from tbl_user where name like '%aa%';

explain select * from tbl_user where name like '%aa%';
explain select id, name,age,email from tbl_user where name like '%aa%';

#create indexalter table tbl_user add index idx_user_nameAge(name,age);

explain select id from tbl_user where name like '%aa%';
explain select name from tbl_user where name like '%aa%';
explain select age from tbl_user where name like '%aa%';
explain select * from tbl_user where name like '%aa%';
explain select id, name,age,email from tbl_user where name like '%aa%';
.....

 

总结

like KK%相当于=常量     %KK和%KK% 相当于范围

1.带头大哥不能死
2.中间兄弟不能断
3.索引列上无计算
4.like %加右边
5.范围之后全失效
6.字符串中有引号

【优化总结口诀】
全值匹配我最爱,最左前缀要遵守;
带头大哥不能死,中间兄弟不能断;
索引列上少计算,范围之后全失效;
like百分写最右,覆盖索引不写星;
不等空值还有or,索引失效要减少;
var引号不能丢,sql高级也不难;

小总结
假设index(a,b,c)
where 语句                                                    索引是否被使用
where a=3                                                    Y,使用到a
where a=3 and b=5                                            Y,使用到a,b
where a=3 and b=5 and c=4                                    Y,使用到a,b,c
where b=3 / where b=3 and c=4 或者 where c=4                  N
where a=3 and c=5                                            使用到a,但是c不可以,b中间断了
where a=3 and b>4 and c=5                                    使用到a和b ,c不能使用在范围之后,b断了
where a=3 and b like 'kk%' and c=4                           Y,使用到a,b,c
where a=3 and b like '%kk' and c=4                           Y,只使用到a
where a=3 and b like '%kk%' and c=4                          Y,只使用到a
where a=3 and b like 'k%kk%' and c=4                         Y,使用到a,b,c

like 结合test03表
select * from test03 where c1='a1' and c2 like 'kk%' and c3='c3';

一般性建议:

对于单键索引,尽量选择针对当前query过滤性更好的索引 在选择组合索引的时候,当前Query中过滤性最好的字段在索引字段顺序中,位置越靠前越好。 在选择组合索引的时候,尽量选择可以能包含当前query中的where子句中更多字段的索引 尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的

当只一行数据使用limit 1

面试题讲解

定值、范围还是排序,一般order by是给个范围 group by 基本上都需要进行排序,会有临时表产生

题目1:
【建表语句】
create table test03(
 id int primary key not null auto_increment,
c1 char(10),
c2 char(10),
c3 char(10),
c4 char(10),
c5 char(10)
);
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;
explain select * from test03 where c1='a1' ;
explain select * from test03 where c1='a1' and c2='a2';
explain select * from test03 where c1='a1' and c2='a2' and c3='a3';
explain select * from test03 where c1='a1' and c2='a2' and c3='a3' and c4='a4';

问题:我们创建符合索引idx_test03_c1234,根据以下的sql分析索引的使用情况
1) explain select * from test03 where c1='a1' and c2='a2' and c3='a3' and c4='a4';
2) explain select * from test03 where c1='a1' and c2='a2' and c4='a4' and c3='a3';
3) explain select * from test03 where c1='a1' and c2='a2' and c3 > 'a3' and c4='a4';
4) explain select * from test03 where c1='a1' and c2='a2' and c4 > 'a4' and c3='a3';
5) explain select * from test03 where c1='a1' and c2='a2' and c4 = 'a4' order by c3; #c3的作用在排序而不是查找

6) explain select * from test03 where c1='a1' and c2='a2'  order by c3;
7) explain select * from test03 where c1='a1' and c2='a2'  order by c4; #出现filesort

8.1) explain select * from test03 where c1='a1' and c5='a5' order by c2,c3;#只用c1一个字段索引,但是c2,c3用于排序,无filesort
8.2) explain select * from test03 where c1='a1' and c5='a5' order by c3,c2;#出现了filesort,我们建立的索引是1234,但是没有按照顺序,32颠倒了
9) explain select * from test03 where c1='a1' and c2='a2' order by c2,c3;
10) explain select * from test03 where c1='a1' and c2='a2' and c5='c5' order by c2,c3;#用c1 c2两个字段索引,但是c2,c3用于排序,无filesort
explain select * from test03 where c1='a1' and c2='a2' and c5='c5' order by c3,c2; #本案例有常量c2的情况,注意和8.2的对比
explain select * from test03 where c1='a1' and c5='a5' order by c3,c2;#filesort

11) explain select * from test03 where c1='a1' and c4='a4' group by c2,c3;
12) explain select * from test03 where c1='a1' and c4='a4' group by c3,c2;#usering where ;usering filesort;usering temporary

查看索引的使用情况

show status like 'Handler_read%';
Handler_read_first    41
Handler_read_key    16075067
Handler_read_last    0
Handler_read_next    9534050
Handler_read_prev    0
Handler_read_rnd    411
Handler_read_rnd_next    126758972
 #大家可以注意:handler_read_key:这个值越高越好,越高表示使用索引查询到的次数。#handler_read_rnd_next:这个值越高,说明查询低效。
 #这时我们会看到handler_read_rnd_next值很高,这是因为我们前面没有加索引的时候,做过多次查询的原因