MySQL-SQL性能问题的探索

人为优化:需要我们使用explain分析SQL的执行计划。该执行计划可以模拟SQL优化器执行SQL语句,可以帮助我们了解到自己编写SQL的好坏。
SQL优化器自动优化:最开始讲述MySQL执行原理的时候,我们已经知道MySQL有一个优化器,当你写了一个SQL语句的时候,SQL优化器如果认为你写的SQL语句不够好,就会自动写一个好一些的等价SQL去执行。

人为优化:需要我们使用explain分析SQL的执行计划。该执行计划可以模拟SQL优化器执行SQL语句,可以帮助我们了解到自己编写SQL的好坏。SQL优化器自动优化:最开始讲述MySQL执行原理的时候,我们已经知道MySQL有一个优化器,当你写了一个SQL语句的时候,SQL优化器如果认为你写的SQL语句不够好,就会自动写一个好一些的等价SQL去执行。SQL优化器自动优化功能【会干扰】我们的人为优化功能。当我们查看了SQL执行计划以后,如果写的不好,我们会去优化自己的SQL。当我们以为自己优化的很好的时候,最终的执行计划,并不是按照我们优化好的SQL语句来执行的,而是有时候将我们优化好的SQL改变了,去执行。SQL优化是一种概率问题,有时候系统会按照我们优化好的SQL去执行结果(优化器觉得你写的差不多,就不会动你的SQL)。有时候优化器仍然会修改我们优化好的SQL,然后再去执行。

1)查看执行计划

语法:explain + SQL语句

eg:explain select * from tb;

2)“执行计划”中需要知道的几个“关键字”

id:编号
select_type:查询类型
table:表
type:类型
possible_keys:预测用到的索引
key:实际使用的索引
key_len:实际使用索引的长度
ref:表之间的引用
rows:通过索引查询到的数据量
Extra:额外的信息

建表语句和插入数据:

# 建表语句
create table course
(
    cid int(3),
    cname varchar(20),
    tid int(3)
);

create table teacher
(
    tid int(3),
    tname varchar(20),
    tcid int(3)
);

create table teacherCard
(
    tcid int(3),
    tcdesc varchar(200)
);

# 插入数据
insert into course values(1,'java',1);
insert into course values(2,'html',1);
insert into course values(3,'sql',2);
insert into course values(4,'web',3);

insert into teacher values(1,'tz',1);
insert into teacher values(2,'tw',2);
insert into teacher values(3,'tl',3);

insert into teacherCard values(1,'tzdesc') ;
insert into teacherCard values(2,'twdesc') ;
insert into teacherCard values(3,'tldesc') ;

explain执行计划常用关键字详解

1)id关键字的使用说明

① 案例:查询课程编号为2 或 教师证编号为3 的老师信息:

# 查看执行计划
explain select t.*
from teacher t,course c,teacherCard tc
where t.tid = c.tid and t.tcid = tc.tcid
and (c.cid = 2 or tc.tcid = 3);

结果如下:

MySQL-SQL性能问题的探索
MySQL-id关键字的使用说明

接着,在往teacher表中增加几条数据。

insert into teacher values(4,'ta',4);
insert into teacher values(5,'tb',5);
insert into teacher values(6,'tc',6);

再次查看执行计划。

# 查看执行计划
explain select t.*
from teacher t,course c,teacherCard tc
where t.tid = c.tid and t.tcid = tc.tcid
and (c.cid = 2 or tc.tcid = 3);

结果如下:

MySQL-SQL性能问题的探索
MySQL-id关键字的使用说明2
表的执行顺序 ,因表数量改变而改变的原因:笛卡尔积。

a   b   c
2   3   4
最终:2 * 3 * 4  = 6 * 4 = 24
c   b   a
4   3   2
最终:4 * 3 * 2 = 12 * 2 = 24

分析:最终执行的条数,虽然是一致的。但是中间过程,有一张临时表是6,一张临时表是12,很明显6 < 12,对于内存来说,数据量越小越好,因此优化器肯定会选择第一种执行顺序。

结论:id值相同,从上往下顺序执行。表的执行顺序因表数量的改变而改变。

② 案例:查询教授SQL课程的老师的描述(desc)

# 查看执行计划
explain select tc.tcdesc from teacherCard tc 
where tc.tcid = 
(
    select t.tcid from teacher t 
    where  t.tid =  
    (select c.tid from course c where c.cname = 'sql')
);

结果如下:

MySQL-SQL性能问题的探索
MySQL-id关键字的使用说明3

结论:id值不同,id值越大越优先查询。这是由于在进行嵌套子查询时,先查内层,再查外层。

③ 针对②做一个简单的修改

# 查看执行计划
explain select t.tname ,tc.tcdesc from teacher t,teacherCard tc 
where t.tcid= tc.tcid
and t.tid = (select c.tid from course c where cname = 'sql') ;

结果如下:

MySQL-SQL性能问题的探索
MySQL-id关键字的使用说明4

结论:id值有相同,又有不同。id值越大越优先;id值相同,从上往下顺序执行。

2)select_type关键字的使用说明:查询类型

MySQL-SQL性能问题的探索
MySQL-select_type关键字的使用说明:查询类型
① simple:简单查询不包含子查询,不包含union查询。

explain select * from teacher;

结果如下:

MySQL-SQL性能问题的探索
MySQL-select_type关键字的使用说明:简单查询

② primary:包含子查询的主查询(最外层)

③ subquery:包含子查询的主查询(非最外层)

④ derived:衍生查询(用到了临时表)

a.在from子查询中,只有一张表;

b.在from子查询中,如果table1 union table2,则table1就是derived表;

explain select  cr.cname     
from ( select * from course where tid = 1  union select * from course where tid = 2 ) cr ;

结果如下:

MySQL-SQL性能问题的探索
MySQL-select_type关键字的使用说明:衍生查询
⑤ union:union之后的表称之为union表,如上例⑥ union result:告诉我们,哪些表之间使用了union查询3)type关键字的使用说明:索引类型system、const只是理想状况,实际上只能优化到index –> range –> ref这个级别。要对type进行优化的前提是,你得创建索引。

MySQL-SQL性能问题的探索
MySQL-type关键字的使用说明:索引类型

① system

源表只有一条数据(实际中,基本不可能);

衍生表只有一条数据的主查询(偶尔可以达到)。

② const

仅仅能查到一条数据的SQL ,仅针对Primary key或unique索引类型有效。

explain select tid from test01 where tid =1 ;

结果如下:

MySQL-SQL性能问题的探索
MySQL-const1
删除以前的主键索引后,此时我们添加一个其他的普通索引:

create index test01_index on test01(tid) ;
# 再次查看执行计划
explain select tid from test01 where tid =1 ;

结果如下:

MySQL-SQL性能问题的探索
MySQL-const2
③ eq_ref唯一性索引,对于每个索引键的查询,返回匹配唯一行数据(有且只有1个,不能多 、不能0),并且查询结果和数据条数必须一致。此种情况常见于唯一索引和主键索引。

delete from teacher where tcid >= 4;
alter table teacherCard add constraint pk_tcid primary key(tcid);
alter table teacher add constraint uk_tcid unique index(tcid) ;
explain select t.tcid from teacher t,teacherCard tc where t.tcid = tc.tcid ;

结果如下:

MySQL-SQL性能问题的探索
MySQL-eq_ref

总结:以上SQL,用到的索引是t.tcid,即teacher表中的tcid字段;如果teacher表的数据个数和连接查询的数据个数一致(都是3条数据),则有可能满足eq_ref级别;否则无法满足。条件很苛刻,很难达到。

④ ref

非唯一性索引,对于每个索引键的查询,返回匹配的所有行(可以0,可以1,可以多)

准备数据:

MySQL-SQL性能问题的探索
MySQL-ref1
创建索引,并查看执行计划:

# 添加索引
alter table teacher add index index_name (tname) ;
# 查看执行计划
explain select * from teacher     where tname = 'tz';

结果如下:

MySQL-SQL性能问题的探索
MySQL-ref2

⑤ range

检索指定范围的行 ,where后面是一个范围查询(between, >, <, >=, in)

in有时候会失效,从而转为无索引时候的ALL

# 添加索引
alter table teacher add index tid_index (tid) ;
# 查看执行计划:以下写了一种等价SQL写法,查看执行计划
explain select t.* from teacher t where t.tid in (1,2) ;
explain select t.* from teacher t where t.tid <3 ;

结果如下:

图片

⑥ index

查询全部索引中的数据(扫描整个索引)

⑦ ALL

查询全部源表中的数据(暴力扫描全表)

图片

注意:cid是索引字段,因此查询索引字段,只需要扫描索引表即可。但是tid不是索引字段,查询非索引字段,需要暴力扫描整个源表,会消耗更多的资源。4)possible_keys和keypossible_keys可能用到的索引。是一种预测,不准。了解一下就好。key指的是实际使用的索引。

# 先给course表的cname字段,添加一个索引
create index cname_index on course(cname);
# 查看执行计划
explain select t.tname ,tc.tcdesc from teacher t,teacherCard tc
where t.tcid= tc.tcid
and t.tid = (select c.tid from course c where cname = 'sql') ;

结果如下:

图片

有一点需要注意的是:如果possible_key/key是NULL,则说明没用索引。

5)key_len

索引的长度,用于判断复合索引是否被完全使用(a,b,c)。

① 新建一张新表,用于测试

# 创建表
create table test_kl
(
    name char(20) not null default ''
);
# 添加索引
alter table test_kl add index index_name(name) ;
# 查看执行计划
explain select * from test_kl where name ='' ; 

结果如下:

图片

结果分析:因为我没有设置服务端的字符集,因此默认的字符集使用的是latin1,对于latin1一个字符代表一个字节,因此这列的key_len的长度是20,表示使用了name这个索引。② 给test_kl表,新增name1列,该列没有设置“not null”结果如下:图片

结果分析:如果索引字段可以为null,则mysql底层会使用1个字节用于标识。

③ 删除原来的索引name和name1,新增一个复合索引

# 删除原来的索引name和name1
drop index index_name on test_kl ;
drop index index_name1 on test_kl ;
# 增加一个复合索引 
create index name_name1_index on test_kl(name,name1);
# 查看执行计划
explain select * from test_kl where name1 = '' ; --121
explain select * from test_kl where name = '' ; --60

结果如下:

图片

结果分析:对于下面这个执行计划,可以看到我们只使用了复合索引的第一个索引字段name,因此key_len是20,这个很清楚。再看上面这个执行计划,我们虽然仅仅在where后面使用了复合索引字段中的name1字段,但是你要使用复合索引的第2个索引字段,会默认使用了复合索引的第1个索引字段name,由于name1可以是null,因此key_len = 20 + 20 + 1 = 41呀!④ 再次怎加一个name2字段,并为该字段创建一个索引。不同的是:该字段数据类型是varchar

# 新增一个字段name2,name2可以为null
alter table test_kl add column name2 varchar(20) ; 
# 给name2字段,设置为索引字段
alter table test_kl add index name2_index(name2) ;
# 查看执行计划
explain select * from test_kl where name2 = '' ;  

结果如下:

图片

结果分析:key_len = 20 + 1 + 2,这个20 + 1我们知道,这个2又代表什么呢?原来varchar属于可变长度,在mysql底层中,用2个字节标识可变长度。

6)ref

这里的ref的作用,指明当前表所参照的字段。

注意与type中的ref值区分。在type中,ref只是type类型的一种选项值。

# 给course表的tid字段,添加一个索引
create index tid_index on course(tid);
# 查看执行计划
explain select * from course c,teacher t 
where c.tid = t.tid  
and t.tname = 'tw';

结果如下:

图片

结果分析:有两个索引,c表的c.tid引用的是t表的tid字段,因此可以看到显示结果为【数据库名.t.tid】,t表的t.name引用的是一个常量”tw”,因此可以看到结果显示为const,表示一个常量。7)rows(这个目前还是有点疑惑)被索引优化查询的数据个数 (实际通过索引而查询到的数据个数)

explain select * 
from course c,teacher t  
where c.tid = t.tid
and t.tname = 'tz' ;

结果如下:

图片

8)extra

表示其他的一些说明,也很有用。

① using filesort:针对单索引的情况

当出现了这个词,表示你当前的SQL性能消耗较大。表示进行了一次“额外”的排序。常见于order by语句中。

什么是“额外”的排序?

为了讲清楚这个,我们首先要知道什么是排序。我们为了给某一个字段进行排序的时候,首先你得先查询到这个字段,然后在将这个字段进行排序。

紧接着,我们查看如下两个SQL语句的执行计划。

# 新建一张表,建表同时创建索引
create table test02
(
    a1 char(3),
    a2 char(3),
    a3 char(3),
    index idx_a1(a1),
    index idx_a2(a2),
    index idx_a3(a3)
);
# 查看执行计划
explain select * from test02 where a1 ='' order by a1 ;
explain select * from test02 where a1 ='' order by a2 ; 

结果如下:

图片

结果分析:对于第一个执行计划,where后面我们先查询了a1字段,然后再利用a1做了依次排序,这个很轻松。但是对于第二个执行计划,where后面我们查询了a1字段,然而利用的却是a2字段进行排序,此时myql底层会进行一次查询,进行“额外”的排序。总结:对于单索引,如果排序和查找是同一个字段,则不会出现using filesort;如果排序和查找不是同一个字段,则会出现using filesort;因此where哪些字段,就order by哪些些字段。② using filesort:针对复合索引的情况不能跨列(官方术语:最佳左前缀)

# 删除test02的索引
drop index idx_a1 on test02;
drop index idx_a2 on test02;
drop index idx_a3 on test02;
# 创建一个复合索引
alter table test02 add index idx_a1_a2_a3 (a1,a2,a3) ;
# 查看下面SQL语句的执行计划
explain select *from test02 where a1='' order by a3 ;  --using filesort
explain select *from test02 where a2='' order by a3 ; --using filesort
explain select *from test02 where a1='' order by a2 ;

结果如下:

图片

结果分析:复合索引的顺序是(a1,a2,a3),可以看到a1在最左边,因此a1就叫做“最佳左前缀”,如果要使用后面的索引字段,必须先使用到这个a1字段。对于explain1,where后面我们使用a1字段,但是后面的排序使用了a3,直接跳过了a2,属于跨列;对于explain2,where后面我们使用了a2字段,直接跳过了a1字段,也属于跨列;对于explain3,where后面我们使用a1字段,后面使用的是a2字段,因此没有出现【using filesort】。

③ using temporary

当出现了这个词,也表示你当前的SQL性能消耗较大。这是由于当前SQL用到了临时表。一般出现在group by中。

explain select a1 from test02 where a1 in ('1','2','3') group by a1 ;
explain select a1 from test02 where a1 in ('1','2','3') group by a2 ; --using temporary

结果如下:

图片

结果分析:当你查询哪个字段,就按照那个字段分组,否则就会出现using temporary。针对using temporary,我们在看一个例子:using temporary表示需要额外再使用一张表,一般出现在group by语句中。虽然已经有表了,但是不适用,必须再来一张表。再次来看mysql的编写过程和解析过程。

编写过程

select dinstinct  ..from  ..join ..on ..where ..group by ..having ..order by ..limit ..

解析过程

from .. on.. join ..where ..group by ..having ..select dinstinct ..order by ..limit ..

很显然,where后是group by,然后才是select。基于此,我们再查看如下两个SQL语句的执行计划。

explain select * from test03 where a2=2 and a4=4 group by a2,a4;
explain select * from test03 where a2=2 and a4=4 group by a3;

分析如下:对于第一个执行计划,where后面是a2和a4,接着我们按照a2和a4分组,很明显这两张表已经有了,直接在a2和a4上分组就行了。但是对于第二个执行计划,where后面是a2和a4,接着我们却按照a3分组,很明显我们没有a3这张表,因此有需要再来一张临时表a3。因此就会出现using temporary。

④ using index

当你看到这个关键词,恭喜你,表示你的SQL性能提升了。

using index称之为“索引覆盖”。

当出现了using index,就表示不用读取源表,而只利用索引获取数据,不需要回源表查询。

只要使用到的列,全部出现在索引中,就是索引覆盖。

# 删除test02中的复合索引idx_a1_a2_a3
drop index idx_a1_a2_a3 on test02;
# 重新创建一个复合索引idx_a1_a2
create index idx_a1_a2 on test02(a1,a2);
# 查看执行计划
explain select a1,a3 from test02 where a1='' or a3= '' ;
explain select a1,a2 from test02 where a1='' and a2= '' ;

结果如下:

MySQL-SQL性能问题的探索
MySQL-using index
结果分析:我们创建的是a1和a2的复合索引,对于第一个执行计划,我们却出现了a3,该字段并没有创建索引,因此没有出现using index,而是using where,表示我们需要回表查询。对于第二个执行计划,属于完全的索引覆盖,因此出现了using index。针对using index,我们在查看一个案例:

explain select a1,a2 from test02 where a1='' or a2= '' ;
explain select a1,a2 from test02;

如果用到了索引覆盖(using index时),会对possible_keys和key造成影响:

a.如果没有where,则索引只出现在key中;

b.如果有where,则索引 出现在key和possible_keys中。

⑤ using where

表示需要【回表查询】,表示既在索引中进行了查询,又回到了源表进行了查询。

# 删除test02中的复合索引idx_a1_a2
drop index idx_a1_a2 on test02;
# 将a1字段,新增为一个索引
create index a1_index on test02(a1);
# 查看执行计划
explain select a1,a3 from test02 where a1="" and a3="" ;

结果如下:

MySQL-SQL性能问题的探索
MySQL-using where

结果分析:我们既使用了索引a1,表示我们使用了索引进行查询。但是又对于a3字段,我们并没有使用索引,因此对于a3字段,需要回源表查询,这个时候出现了using where。

⑥ impossible where(了解)

当where子句永远为False的时候,会出现impossible where

# 查看执行计划
explain select a1 from test02 where a1="a" and a1="b" ;

结果如下:

MySQL-SQL性能问题的探索
MySQL-impossible where(了解)

原创文章,作者:修行,如若转载,请注明作者昵称:修行及出处:https://www.xiuxingstudio.com/computer/database/2785.html

(0)
打赏 微信扫一扫 微信扫一扫 支付宝扫一扫 支付宝扫一扫
上一篇 2022年2月1日 00:22
下一篇 2022年2月3日 13:56

相关推荐

发表评论

登录后才能评论
在线客服 QQ交流群
分享本页
返回顶部