欢迎大家关注我的公众号【老周聊架构】,Java后端主流技术栈的原理、源码分析、架构以及各种互联网高并发、高性能、高可用的解决方案。
一、执行计划是什么?
执行计划,简单的来说,是SQL在数据库中执行时的表现情况,通常用于SQL性能分析,优化等场景。在MySQL中使用 explain 关键字来查看
二、查看执行计划
EXPLAIN + sql语句
mysql> explain select 1;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.00 sec)
三、MySQL执行计划分析
- sql如何使用索引
- 联接查询的执行顺序
- 查询扫描的数据行数
四、各个字段的含义
ID
- id列中的数据为一组数字,表示执行select语句顺序
- id值相同时,执行顺序由上至下
- id值越大优先级越高,越先被执行
SELECT_TYPE
查询的类型,主要是用于区分普通查询、联合查询、子查询等复杂的查询。
- SIMPLE:简单的select查询,查询中不包含子查询或者union
- PRIMARY:查询中包含任何复杂的子部分,最外层查询则被标记为primary
- SUBQUERY:在select 或 where列表中包含了子查询
- DERIVED:在from列表中包含的子查询被标记为derived(衍生),mysql会递归执行这些子查询,把结果放在临时表里
- UNION:若第二个select出现在union之后,则被标记为union;若union包含在from子句的子查询中,外层select将被标记为derived
- UNION RESULT:从union表获取结果的select
- DEPENDENT SUBQUERY: 依赖外部结果的子查询
- DEPENDENT UNION: 当union作为子查询时,第二或是第二个后的查询的 select_type值
TABLE
- 输出数据行所在的表的名称
- < unionM,N>由ID为M,N查询union产生的结果集
- < derivedN>/< subqueryN> 由Id为n的查询产生的结果
PARTITIONS
- 对于分区表,显示查询的分区ID
- 对于非分区表,显示的为 NULL
TYPE
访问类型,sql查询优化中一个很重要的指标,结果值从好到坏依次是:
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
- system 这是const联接类型的一个特例,当查询的表只有一行时使用
- const:表示通过索引一次就找到了,const用于比较primary key 或者 unique索引。因为只需匹配一行数据,所有很快。如果将主键置于where列表中,mysql就能将该查询转换为一个const
- eq_ref:唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配。常见于主键 或 唯一索引扫描。
- ref:非唯一性索引扫描,返回匹配某个单独值的所有行。本质是也是一种索引访问,它返回所有匹配某个单独值的行,然而他可能会找到多个符合条件的行,所以它应该属于查找和扫描的混合体
- ref_or_null 类似于 ref类型的查询,但是附加了对 null 值列的查询
- range:只检索给定范围的行,使用一个索引来选择行。key列显示使用了那个索引。一般就是在where语句中出现了between、<、>、in等的查询。这种索引列上的范围扫描比全索引扫描要好。只需要开始于某个点,结束于另一个点,不用扫描全部索引
- index:Full Index Scan,index与ALL区别为index类型只遍历索引树。这通常为ALL块,应为索引文件通常比数据文件小。(Index与ALL虽然都是读全表,但index是从索引中读取,而ALL是从硬盘读取)
- ALL:Full Table Scan,遍历全表以找到匹配的行
POSSIBLE_KEYS
- 指出mysql能使用哪些索引来优化查询
- 查询列所涉及的列上的索引都会被列出来,但不一定会被使用
KEY
- 实际使用的索引,如果为NULL,则没有使用索引。
- 查询中如果使用了覆盖索引,则该索引仅出现在key列表中。
KEY_LEN
- 表示索引字段的最大的可能长度
- key_len的长度由字段定义计算而来,并非数据的实际长度
REF
- 表示那些列或常量被用于查找索引列上的值
ROWS
- 表示msql通过索引的统计信息,估算的所需读取的行数
- rows值的大小时个统计抽样的结果,并不十分的准确
FILTERED
- 表示返回的结果的行数占需要读取行数的百分比
- Filtered列的值越大越好
- Filtered列的值依赖的说统计信息
EXTRA
- Distinct 优化distinct操作,在找到第一匹配的元祖后即停止找同样的动作
- Not exists 使用 not exists 来优化查询
- Using filesort 使用额外操作进行排序,通常会出现在order by 或 group by 查询
- Using index 使用了覆盖索引进行查询
- Using temporary mysql需要使用临时表来处理查询,常见于排序,子查询,和分组查询
- Using where 需要在mysql服务器层使用 where条件来过滤数据
- slect tables /optimized away 直接通过索引来获得数据,不用访问表
五、执行计划的限制
- 无法展示存储过程,触发器,udf对查询的影响
- 无法使用 explain对存储过程进行分析
- 早期版本的mysql只支持对select语句进行分析