MySql优化之执行计划

MySql基本架构:

首先我们对mysql的基本架构做一个认知:
连接器:连接器的作用是与客户端建立连接,获取权限,维持和管理连接,例如用户和密码校验,分配权限等等
分析器:mysql会把输入的sql字符串按照一定的规则进行解析,形成一个语法树,如果不符合mysql的语法,就会报错
优化器:对表中的索引进行选择,对多表连接的优化,决定表的连接顺序,不同的执行方式对SQL语句的执行效率影响很大
执行器负责sql的具体执行,而存储引擎层分为Innodb和MyiSam的不同结构,主要是为了做日志备份和存储的,这里不多做赘述.

我们讨论的执行计划更多的是在优化器的这个阶段,对于普通开发人员而言,了解sql执行计划是优化sql的第一步

不多废话,直接进入正题:
首先我们查询一条简单的sql: explain select * from book_info

1.id

: id列作为一个标识,代表了sql的执行顺序,如果id相同,则从上到下的执行,如果id不同,则优先执行id号大的
例如:explain select * from book_info where ccm_book_id in(select * from (select ccm_book_id from book_file order by id desc limit 10) s2 )

由上图可见,先进行id为3的子查询:
select ccm_book_id from book_file order by id desc limit 10
其中id为2的MATERIALIZED代表被物化的子查询
其次依次执行id为1的两条sql
再执行第二条子查询:
select * from (select ccm_book_id from book_file order by id desc limit 10)
最后执行最外层的book_info的查询:
select * from book_info where ccm_book_id in(select * from (select ccm_book_id from book_file order by id desc limit 10) s2 )

2.select_type

:表示该查询的类型,是普通查询还是子查询或者联合查询
下图是select_type的基本类型介绍

simple:最简单的查询,不包含子查询和union
explain select * from book_info

primary:当sql中包含子查询,那么外层的sql会被标记
select * from book_info where ccm_book_id in(select * from (select ccm_book_id from book_file order by id desc limit 10) s2 )

由上图可见,id为1的两条查询都被标记为primary

union:union后面的查询语句会被标记
explain select * from book_info where id=1030 union select * from book_info where id=1032;

DEPENDENT UNION:代表union中的结果会作为条件被使用
explain select * from book_file where book_id in(select id from book_info where id=1030 union select id from book_info where id=1032);

UNION RESULT:union之后的结果返回集合
SUBQUERY:子查询中的语句会被标识
explain select * from book_info bi where EXISTS (select * from book_file bf)

DEPENDENT SUBQUERY:子查询被作为条件使用
explain select * from book_info bi where EXISTS (select * from book_file bf where id>1000 and bf.book_id=bi.id )

DERIVED:from中存在的子查询,也称为派生类
select count(*) from (select book_id from book_file group by book_id) s2

UNCACHEABLE SUBQUERY与UNCACHEABLE UNION作用的地方在于变量查询,查询出来的结果无法被缓存,由于mysql5.8之后缓存已经消除,这里就不多做解释

3.table

table表示每条查询的表名,若存在别名则使用别名,若存在子查询,则默认的名称为dervedN,其中n代表的是id,若为union 查询 ,则为union n,n代表的也是id

4.type

type是执行过程非常重要的一个属性,它表示的是访问类型,访问类型表示我们是以何种方式去访问我们的数据,效率从最好到最坏依次是:
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
一般情况下,尽量避免使用ALL(全表扫描),最好是能够达到ref级别

• all:全表扫描,若该表数据量比较大,则需要进行优化:
  explain select * from book_info
  优化思路:
  (1).如果避免不了全表扫描的话,那么可以选择把查询的条件不要使用通配符*,而是选择具体的字段,将这些字段进行联合索引,使得级别为index
  (2).当数据量过大,索引文件检索起来也很慢的时候,则需要进行分库分表

• index:全索引扫描,主要两种情况,一种是全表扫描(1)中提到的覆盖索引,第二种是使用索引进行排序
  explain select * from book_info order by id desc
  优化思路:
  (1)全索引扫描档索引文件足够大时,io的读写也是非常大的瓶颈,我们需要对索引文件进行过滤,使用where拼接索引条件,例如: explain select * from book_info where company_id=2877 order by id desc,或者是 explain select * from book_info where id>100 order by id desc,使得type提升为ref或者是range

• range:避免了全索引扫描,在指定的范围内进行查询,通用的操作符 =, <>, >, >=, <, <=, IS NULL, BETWEEN, LIKE, or IN()
  explain select * from book_info where id>100 order by id desc

• index_subquery:利用索引来关联子查询,子表中的结果为主表中的索引列
  explain select * from student where s_id in(select s_id from score);

• unique_subquery:这种类型和index_subquery非常类似,但是他的子查询的结果为唯一的
  explain select * from score where s_id in(select s_id from student);

• index_merge：在查询过程中需要多个索引组合使用，没有模拟出来

• ref_or_null：对于某个字段即需要关联条件，也需要null值的情况下，查询优化器会选择这种访问方式

• ref：使用了非唯一性索引进行数据的查找
  explain select * from book_info bi join book_suit bs on bi.suit_id=bs.id;

• eq_ref ：使用唯一性索引进行数据查找
  explain select * from book_info bi1 join book_info bi2 on bi1.id=bi2.id;

• const：这个表至多有一个匹配行
  explain select * from book_info where id=1029;

• system：表只有一行记录（等于系统表），这是const类型的特例，平时不会出现

5.possible_keys:

查询中表涉及到的索引,一个或者多个,若查询中涉及到的索引,都会被列出来,但不一定有实际使用

key

实际使用的索引,如果为null,则没有使用索引

key_len

索引的字节数,在不损失精度的情况下,长度越小意味着读写的效率越高,越好

ref

显示索引的哪一列被使用了

rows

根据表的统计信息和索引使用情况,大致估算出所需记录需要读取的行数,这个参数也是比较重要的,他能够反映出该sql找了多少数据,在达到目的的情况下,越少越好

extra

包含的额外信息:

• using filesort:说明mysql无法利用索引进行排序,只能利用排序算法排序,此排序效率较低
• using temporary:建立临时表来保存中间结果,查询完成之后把临时表会删除掉
• using index:使用索引进行查询,不用直接访问数据表
• using where : 使用where条件进行过滤
• using join buffe:使用连接缓存
• impossible where :where语句的结果总是为false,不存在的记录