1、 什么是Mysql执行计划**
所谓的执行计划就是Mysql如何执行一条Sql语句,包括Sql查询的顺序、是否使用索引、以及使用的索引信息等内容。一个例子:
基本语法
explain select ...
复制代码一些变体
explain extended select ...
复制代码上述的语句是将表格形式的执行计划转化成 select语句,在使用 show warnings可以得到mysql优化器优化后的查询语句。
explain partitions select ...
复制代码用于分区表的EXPLAIN
2、执行计划包含的信息
不同版本的Mysql和不同的存储引擎执行计划不完全相同,但基本信息都差不多。mysql执行计划主要包含以下信息:
2.1 id
有一组数字组成。表示一个查询中各个子查询的执行顺序;
id相同执行顺序由上至下。
id不同,id值越大优先级越高,越先被执行。
id为null时表示一个结果集,不需要使用它查询,常出现在包含union等查询语句中。
2.2 select_type
每个子查询的查询类型,一些常见的查询类型。
id | select_type | 描述 | 示例 |
---|---|---|---|
1 | SIMPLE | 简单的 SELECT查询。没有表UNION查询,没有子查询(嵌套查询)。我们在本节之前内容中给出的示例基本上属于这种查询类型,它基本上不需要也不能再进行子查询拆分 | |
2 | PRIMARY | 由子查询(嵌套查询)的SQL语句下,最外层的Select 作为primary 查询。 | |
3 | SUBQUERY /DEPENDENT SUBQUERY | 这两种类型都表示第一个查询是子查询。区别是SUBQUERY表示的子查询不依赖于外部查询,而后者的子查询依赖于外部查询。 | |
4 | DERIVED | from字句中包含的查询。这两种类型都表示第一个查询是子查询。区别是SUBQUERY表示的子查询不依赖于外部查询,而后者的子查询依赖于外部查询。 | explain select * from (select * from t_interfacemethod_param where name = 'uid') t_interfacemethod_param |
5 | UNION | 出现在union后的查询语句中。从第二个或者在union 之后的select 作为 union 查询。这种查询类型出现在结果集与结果集的UNION操作中。 | |
6 | UNION RESULT | 结果集是通过union 而来的。这种查询类型出现在结果集与结果集的UNION操作中。 | |
7 | DEPENDENT UNION | 从第二个或者在union 之后的select 作为 union 查询, 依赖于外部查询。这种查询类型出现在结果集与结果集的UNION操作中。 | |
8 | UNCACHEABLE UNION | 第二个 或者 在UNION 查询之后的select ,属于不可缓存的查询。这种查询类型出现在结果集与结果集的UNION操作中。 |
2.3 table
查询的数据表,当从衍生表中查数据时会显示< derivedx > x 表示对应的执行计划id。
2.4 partitions
表分区、表创建的时候可以指定通过那个列进行表分区。
举个例子:
create table tmp (
id int unsigned not null AUTO_INCREMENT,
name varchar(255),
PRIMARY KEY (id)
) engine = innodb
partition by key (id) partitions 5;
2.5 type
访问类型
id | type | 说明 | 示例 |
---|---|---|---|
1 | ALL | 全表扫描,实际上是扫描数据表的聚簇索引,并在其上加锁还会视事务隔离情况加GAP间隙锁。在数据量非常少的情况下,做全表扫描和使用聚簇索引检索当然不会有太大的性能差异 | explain select * from myuser |
2 | index | 进行索引进行的扫描,它和ALL一样都是扫描,不同点是index类型的扫描只扫描索引信息,并不会对聚簇索引上对应的数据值进行任何形式的读取。 | # 以下语句还是要进行全表扫描,但是它并不需要读取任何数据信息。 explain select count(*) from myuser |
3 | range | 索引范围查找。在索引(聚簇索引和非聚簇索引都有可能)的基础上进行检索某一个范围内满足条件的范围,而并不是指定的某一个或者某几个值 | # 以下查询语句在聚簇索引上检索一个范围 explain select * from myuser where id >= 10 |
4 | index_subquery | 在子查询中使用 ref。在非聚簇索引的基础上使用“非唯一键索引”的方式进行查找 | |
5 | unique_subquery | 在子查询中使用 eq_ref | |
6 | ref_or_null | 对Null进行索引的优化的 ref | |
7 | fulltext | 使用全文索引 | |
8 | ref | 使用非唯一索引查找数据。在非聚簇索引的基础上使用“非唯一键索引”的方式进行查找 | # 在myuser中已基于user_name字段建立了非聚簇索引,且并非唯一键索引 explain select count(*) from myuser where user_name = '用户1' |
9 | eq_ref | 在join查询中使用PRIMARY KEYorUNIQUE NOT NULL索引关联。 | |
10 | const | 使用主键或者唯一索引,且匹配的结果只有一条记录。 | # 直接使用主键值就可以在索引中进行定位,无论数据量多大,这个定位的性能都不会改变 explain select * from myuser where id = 1 |
11 | system | 连接类型的特例,查询的表为系统表。 |
2.6 possible_keys
可能使用的索引,注意不一定会使用。查询涉及到的字段上若存在索引,则该索引将被列出来。当该列为 NULL时就要考虑当前的SQL是否需要优化了。
2.7 key
显示MySQL在查询中实际使用的索引,若没有使用索引,显示为NULL。
TIPS:查询中若使用了覆盖索引(覆盖索引:索引的数据覆盖了需要查询的所有数据),则该索引仅出现在key列表中
2.8 key_length
索引长度
char()、varchar()索引长度的计算公式:
(Character Set:utf8mb4=4,utf8=3,gbk=2,latin1=1) * 列长度 + 1(允许null) + 2(变长列)
复制代码其他类型索引长度的计算公式:
ex:
CREATE TABLE `student` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(128) NOT NULL DEFAULT '',
`age` int(11),
PRIMARY KEY (`id`),
UNIQUE KEY `idx` (`name`),
KEY `idx_age` (`age`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;
复制代码name 索引长度为: 编码为utf8mb4,列长为128,不允许为NULL
,字段类型为varchar(128)
。key_length = 128 * 4 + 0 + 2 = 514
;
age 索引长度:int类型占4位,允许null
,索引长度为5。
2.9 ref
表示上述表的连接匹配条件,即哪些列或常量被用于查找索引列上的值
2.10 rows
返回估算的结果集数目,并不是一个准确的值。
2.11 extra
extra
的信息非常丰富,常见的有:
id | extra | 说明 | 示例 |
---|---|---|---|
1 | Using index | 使用覆盖索引 | |
2 | Using where | 使用了用where子句来过滤结果集 | |
3 | Using filesort | 使用文件排序,使用非索引列进行排序时出现,非常消耗性能,尽量优化。 | |
4 | Using temporary | 使用了临时表 |
3、执行计划的局限性
-
执行计划不考虑Query Cache : 执行计划只考虑单次语句的执行效果,但实际上MySQL服务以及上层业务系统一般都会有一些缓存机制,例如MySQL服务中提供的Query Cache功能。所以实际上可能查询语句的重复执行速度会快一些。
-
执行计划不能分析insert语句:insert语句的执行效果实际上是和其他语句相互作用的,所以执行计划不能单独分析insert语句的执行效果。不过update和delete语句都是可以分析的(请使用MySQL Version 5.6+ 版本)。
-
执行计划不考虑可能涉及的存储过程、函数、触发器带来的额外性能消耗。
4、总结
-
type性能按照从高到低排序
system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range >index > ALL
1).常用type类型(根据索引优化)
:
system > const > eq_ref > ref > range >index > ALL
* 在实际开发中:system 、在属于理性型,基本达不到。const 很少能达到。 ref 、 range 实际能达到
-
性能按照extra排序
1). Using index:用了覆盖索引
2). Using index condition:用了条件索引(索引下推)
3). Using where:从索引查出来数据后继续用where条件过滤
4). Using join buffer (Block Nested Loop):join的时候利用了join buffer(优化策略:去除外连接、增大join buffer大小)
5). Using filesort:用了文件排序,排序的时候没有用到索引
6). Using temporary:用了临时表(优化策略:增加条件以减少结果集、增加索引,思路就是要么减少待排序的数量,要么提前排好序)
7). Start temporary, End temporary:子查询的时候,可以优化成半连接,但是使用的是通过临时表来去重
8). FirstMatch(tbl_name):子查询的时候,可以优化成半连接,但是使用的是直接进行数据比较来去重 -
常见优化手段
1). SQL语句中IN包含的值不应过多,不能超过200个,200个以内查询优化器计算成本时比较精准,超过200个是估算的成本,另外建议能用between就不要用in,这样就可以使用range索引了。
2). SELECT语句务必指明字段名称:SELECT * 增加很多不必要的消耗(cpu、io、内存、网络带宽);增加
了使用覆盖索引的可能性;当表结构发生改变时,前断也需要更新。所以要求直接在select后面接上字段
名。
3). 当只需要一条数据的时候,使用limit 1
4). 排序时注意是否能用到索引
5). 使用or时如果没有用到索引,可以改为union all 或者union
6). 如果in不能用到索引,可以改成exists看是否能用到索引
7). 使用合理的分页方式以提高分页的效率
8). 不建议使用%前缀模糊查询
9). 避免在where子句中对字段进行表达式操作
10). 避免隐式类型转换
11). 对于联合索引来说,要遵守最左前缀法则
12). 必要时可以使用force index来强制查询走某个索引
13). 对于联合索引来说,如果存在范围查询,比如between,>,<等条件时,会造成后面的索引字段失效。
14). 尽量使用inner join,避免left join,让查询优化器来自动选择小表作为驱动表
15). 必要时刻可以使用straight_join来指定驱动表,前提条件是本身是inner join