数据库MySQL
Source
Edit
History

1. MySQL-explain使用说明

Catalogue
  1. 1. id

explain可以查看SQL语句的执行计划。下面, 我们就对explain查询出来的字段做一些使用说明。
返回的信息有10列, 分别是:

  • id
  • select_type
  • table
  • type
  • possible_keys
  • key
  • key_len
  • ref
  • rows
  • filtered
  • Extra

id

id表示查询执行的顺序, 有以下规则:

  • id相同时，由上到下执行
  • id不同时，id大的先执行
  • id号每个号码，表示一趟独立的查询。一个 sql 的查询趟数越少越好。
  1. select_type 列:
值 含义
SIMPLE 不包含子查询或是UNION操作的查询
PRIMARY 查询中如果包含任何子查询,那么最外层的查询则被标记为PRIMARY
SUBQUERY 在SELECT或WHERE列表中包含了子查询
DEPENDENT SUBQUERY 依赖外部结果的子查询
DERIVED 出现在FROM子句中的子查询会被标记为DERIVED(衍生),MySQL会递归执行这些子查询,把结果放在临时表里
UNION 若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中，外层的SELECT将被标记为: DERIVED
DEPENDENT UNION 当union作为子查询时,第二或是第二个后的查询的select_type值
UNION RESULT 从UNION表获取结果的SELECT

衍生表的优化: https://dev.mysql.com/doc/refman/5.6/en/derived-table-optimization.html

  • table 列: 指明是从哪个表中获取数据
值 含义
<unionM,N> 由ID为M,N查询union产生的结果集
<derived N>/<subquery N> 由ID为N的查询产生的结果集

  • partitions 列: 对于分区表, 显示查询的分区ID; 非分区表, 显示为NULL

  • type 列: 访问类型, 表示找到所查询数据的方法。性能由好到差:

值 含义
system/const 表中有且只有一个匹配的行时使用, 如对主键(primary key)或是唯一索引(unique index)的查询, 这是效率最高的连接方式。system是const的一种特殊情况,既表本身只有一行数据的情况。
eq_ref 根据唯一索引或者主键查找, 对于每个索引键, 表中只有一条记录与之匹配。
ref 非唯一索引查找, 返回匹配某个单独值的所有行。
ref_or_null 类似于ref类型的查询,但是附加了对NULL值列的查询
index_merge 该连接查询表示使用了索引合并优化方法。
range 索引范围扫描, 常见于between、>、< 这样的查询条件
index full index scan 全索引扫描, 同ALL的区别是, 遍历的是索引树
all full table scan 全表扫描, 这是效率最差的连接方式

system
avatar

const
avatar

eq_ref
avatar

ref
avatar

ref_or_null
avatar

index_merge
avatar

range
avatar

index
avatar

all
avatar

  • possible_keys: 表示此次查询中可能使用的索引。
  • key: 表示实际在此次查询中使用的索引。
  • key_len: 表示实际使用的索引的最大长度。复合索引可能为部分列(根据查询条件)长度之和。该值越小越好。

如何计算key_len

1. 先看索引上字段的类型+长度比如 int=4 ; varchar(20) =20 ; char(20) =20 
2. 如果是 varchar 或者 char 这种字符串字段，视字符集要乘不同的值，比如 utf-8 
3. varchar 这种动态字符串要加 2 个字节
4. 允许为空的字段要加 1 个字节
  • ref: 表示哪些列或常量被用于索引查找, 即连接查询的条件。
  • rows: 根据统计信息预估的扫描的行数
  • filtered: 表示返回结果的行数占需读取行数的百分比, 该值越高, 说明性能越好。
  • extra: 表示查询的其他信息:
值 含义
Distinct 优化distinct操作,在找到第一匹配的元组后即停止找同样值的动作
Not exists 使用not exists来优化查询
Using filesort 说明MySQL会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取。MySQL无法利用索引完成的排序操作称为”文件排序”
Using temporary MySQL需要使用临时表来处理查询, 常见于order by,sub_query和group by
Using index 使用覆盖索引(Covering index)来进行查询，避免访问表的数据行。如果同时出现Using where，表明索引被用来执行索引键值的查找;如果没有Using where，表明索引用来读取数据而非执行查找动作。
Using where 使用where条件来过滤数据
Using join buffer 使用了连接缓存
impossible where where子句的值总是false，不能用来获取任何元组
select tables optimized away 直接通过索引来获得数据, 不用访问表(MyISAM引擎优化)