MySql性能优化（三）执行计划详解

官网地址
在具体的应用当中&＃xff0c;我们排查sql有没有走索引&＃xff0c;性能如何&＃xff0c;需要查看Sql语句具体的执行过程&＃xff0c;以方便我们调整sql来加快sql的执行效率。
可以使用 EXPLAIN &＃43; SQL 的语句来模拟优化器执行sql语句&＃xff0c;从而知道mysql如何处理sql语句。

概述

执行计划当中包含的信息

id

select查询的序列号&＃xff0c;包含一组数字&＃xff0c;表示查询中执行select子句或者操作表的顺序
id 分为三种情况&＃xff1a;

1.id 相同
如果id相同&＃xff0c;那么执行顺序从上到下

2.id 不同
如果id不同。如果是子查询&＃xff0c;id顺序会递增&＃xff0c;id大的先执行

2.id 相同和不同的同时存在
相同的认为是同一组&＃xff0c;从上往下顺序执行。在所有组中&＃xff0c;id值越大&＃xff0c;越先被执行

select_type

simple: 简单的查询&＃xff0c;不包含子查询和union
primary:查询中包含任何复杂的子查询&＃xff0c;最外层查询被标记为primary
union:若第二个查询出现在union之后&＃xff0c;则被标记为union
dependent union:跟union类似&＃xff0c;这里的dependent/dependent all 表示union /union all 联合而成的结果会受到外部影响
union result: 从union获取结果的select
subquery: 在select或者where列表当中包含的子查询
dependent subquery&＃xff1a;subquery的子查询要受到外部表查询的影响
DERIVED&＃xff1a;form子句中出现的子查询&＃xff0c;也叫作派生类
UNCACHEABLE SUBQUERY:表示使用子查询的结果不能被缓存
uncachedable union:表示union子查询的结果不能被缓存


table

表示对应行正在访问哪一个表&＃xff0c;表名或者别名&＃xff0c;可能是临时表或者union合并结果集

1.如果是具体的表名&＃xff0c;则表明从实际的物理表当中获取数据&＃xff0c;当然也可能是表的别名
2.表名是derivedN的形式&＃xff0c;表明使用了id为N的查询产生的衍生表
3.当有union result 的时候&＃xff0c;表名是union n1,n2等的形式&＃xff0c;n1&＃xff0c;n2表示擦浴union的id


type

type显示的是访问类型&＃xff0c;访问类型是表示我是以何种方式去访问我们的数据&＃xff0c;最容易想到的是全表扫描&＃xff0c;直接暴力遍历一张表去寻找需要的数据&＃xff0c;效率十分低下&＃xff0c;访问类型有很多&＃xff0c;效率从好到坏分别有&＃xff1a;
system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>ALL
一般情况下&＃xff0c;得保证查询达到range范围及其以上

1.system:表只有一行记录&＃xff08;等于系统表&＃xff09;&＃xff0c;这是const类型的特例&＃xff0c;平时不会出现
2.const&＃xff1a;这个表至多一个匹配行
explain select * from table1 where table_unique_no &＃61; "1234***"
3.eq_ref使用唯一索引进行数据查找
explain select * from table1,table2 where table1.no &＃61; table2.no;
4.ref 使用非唯一索引进行数据的查找
create index idx_1 on table1(no2);
explain select * from table1,table2 where table1.no2 &＃61; table2.no2;
5.ref_or_null 对于某个字段既需要关联条件&＃xff0c;也需要null值的情况下&＃xff0c;查询优化器会选择这种访问方式
explain select * from table1 where no is null or no &＃61; "xxxx";
6.index_merge:在查询中需要多个索引组合使用
7.unique_subquery:利用索引关联子查询&＃xff0c;不在扫描全表&＃xff0c;使用的是唯一索引
explain select * from table1 where table1.no in (select distinct no from table2 ) ;
8.index_subquery:和unique_subquery 类似&＃xff0c;不过使用的不是唯一索引
explain select * from table1 where table1.no2 in (select distinct no2 from table2 ) ;
9.range:表示利用索引查询的时候限制了范围&＃xff0c;在指定范围内进行查询&＃xff0c;这样避免了使用index的全索引扫描&＃xff0c;适用操作符有 &＃61;, <>, >, >&＃61;, <, <&＃61;, IS NULL, BETWEEN, LIKE, or IN()
create index idx_3 on table1(no3);
explain select * from table1 where table1.no3 between 100 and 1000;
10.index:全索引扫描&＃xff0c;效率比all好&＃xff0c;主要有两种情况&＃xff0c;一种当前查询覆盖索引&＃xff0c;即我们当前需要的数据在索引中就可以拿到&＃xff0c;二是使用了索引进行排序&＃xff0c;这样就避免了数据的重排序
explain select no3 form table1;
11.ALL:全表扫描&＃xff0c;一般出现这种且数据量较大的时候就需要优化了
explain select * from table1


possible_keys

显示可能应用在这个表中的索引&＃xff0c;一个或者多个&＃xff0c;查询涉及到的字段上若存在索引&＃xff0c;则该索引将被列出&＃xff0c;但不一定被实际使用。

key

实际使用的索引&＃xff0c;若为null&＃xff0c;则没有使用索引&＃xff0c;查询中若使用了覆盖索引&＃xff0c;则该索引和查询的select字段重叠

key_len

表示索引中使用的字节数&＃xff0c;可以通过key_len计算查询中使用的索引长度&＃xff0c;在不损失精度的情况下长度越短越好

ref

显示索引哪一列被使用了&＃xff0c;如果可能的话&＃xff0c;是一个常数

rows

根据表的统计信息及索引的使用情况&＃xff0c;大致估算出找出所需记录需要读取的行数&＃xff0c;此参数很重要&＃xff0c;直接反应的sql查询多少数据&＃xff0c;在完成目标的情况下越少越好

extra

包含额外的信息

1.using filesort:说明mysql无法利用索引进行排序&＃xff0c;只能利用排序算法进行排序&＃xff0c;会消耗额外的位置
2.using temporary:建立临时表来保存中间结果&＃xff0c;查询完成之后把临时表删除
3.using index:这个表示当前的查询时覆盖索引的&＃xff0c;直接从索引中读取数据&＃xff0c;而不用访问数据表。如果同时出现using where 表名索引被用来执行索引键值的查找&＃xff0c;如果没有&＃xff0c;表面索引被用来读取数据&＃xff0c;而不是真的查找
4.using where:使用where进行条件过滤
5.using join buffer:使用连接缓存&＃xff0c;情况没有模拟出来
6.impossible where&＃xff1a;where语句的结果总是false