查询优化神器 – explain命令

关于explain命令相信大家并不陌生，具体用法和字段含义可以参考官网explain-output，这里需要强调rows是核心指标，绝大部分rows小的语句执行一定很快。所以优化语句基本上都是在优化rows。

1. 慢查询优化基本步骤

1.   先运行看看是否真的很慢，注意设置SQL_NO_CACHE

2.   where条件单表查，锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起，单表每个字段分别查询，看哪个字段的区分度最高

3.   explain查看执行计划，是否与1预期一致（从锁定记录较少的表开始查询

4.   order by limit 形式的sql语句让排序的表优先查

5.   了解业务方使用场景

6.   加索引时参照建索引的几大原则

7.   观察结果，不符合预期继续从0分析

2. explain具体解释

Explain的结果一共有10项,分别是

id,  selecttype,  table,  type, possible_keys,  key,  key_len, ref, rows, Extra

 

selecttype(查找类型)

1.      simple 它表示简单的select,没有union和子查询

2.     primary 最外面的select,在有子查询的语句中，最外面的select查询就是primary

3.     union union语句的第二个或者说是后面那一个

 

key_len（字节长度）

1.      索引字段的附加信息：可以分为变长和定长数据类型讨论，当索引字段为定长数据类型，比如char，int，datetime，需要有是否为空的标记，这个标记需要占用1个字节；对于变长数据类型，比如：varchar，除了是否为空的标记外，还需要有长度信息，需要占用2个字节；(备注：当字段定义为非空的时候，是否为空的标记将不占用字节)

2.      同时还需要考虑表所使用的字符集，不同的字符集，gbk编码的为一个字符2个字节，utf8编码的一个字符3个字节;

3.      特别注意这个值可以得出一个多重主键里mysql实际使用了哪一部分。

 

Type(连接类型。有多个参数，先从最佳类型到最差类型介绍重要且困难)

一般来说，得保证查询至少达到range级别，最好能达到ref，否则就可能会出现性能问题。

1.      const 表最多有一个匹配行，const用于比较primarykey 或者unique索引（一般可以理解为const是最优化）

2.     eq_ref 使用有唯一性索引查找（主键或唯一性索引）

对于每个来自于前面的表的行组合，从该表中读取一行。这可能是最好的联接类型，除了const类型。它用在一个索引的所有部分被联接使用并且索引是UNIQUE或PRIMARY KEY

3.     ref 非唯一性索引访问

例如：（例如：select id from tablename where name='jay' name不是唯一索引）

对于每个来自于前面的表的行组合，所有有匹配索引值的行将从这张表中读取。如果联接只使用键的最左边的前缀，或如果键不是UNIQUE或PRIMARY KEY（换句话说，如果联接不能基于关键字选择单个行的话），则使用ref。如果使用的键仅仅匹配少量行，该联接类型是不错的

4.     ref_or_null 该联接类型如同ref，但是添加了MySQL可以专门搜索包含NULL值的行。在解决子查询中经常使用该联接类型的优化。

上面这五种情况都是很理想的索引使用情况。

5.     index_merge

6.     unique_subquery 

7.     range 给定范围内的检索，使用一个索引来检查行；索引范围扫描，常用语<,<=,>=,between等操作

        例如：select id from tablename where id>100;

8.     index

按索引次序扫描表，就是先读索引，再读实际的行，其实还是全表扫描。主要优点是避免了排序，因为索引是排好序的。（例如：select name from tablename）

该联接类型与ALL相同，除了只有索引树被扫描。这通常比ALL快，因为索引文件通常比数据文件小。（也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘中读的）当查询只使用作为单索引一部分的列时，MySQL可以使用该联接类型。

9.     ALL 对于每个来自于先前的表的行组合，进行完整的表扫描。如果表是第一个没标记const的表，这通常不好，并且通常在它情况下很差。通常可以增加更多的索引而不要使用ALL，使得行能基于前面的表中的常数值或列值被检索出。

 

ref   ref列显示使用哪个列或常数与key一起从表中选择行。

rows显示MYSQL执行查询的行数，简单且重要，数值越大越不好，说明没有用好索引

Extra  该列包含MySQL解决查询的详细信息。

1.   using index 只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的信息。这个比较容易理解，就是说明是否使用了索引

2.   using temporary 为了解决查询，MySQL需要创建一个临时表来容纳结果。典型情况如查询包含可以按不同情况列出列的GROUPBY和ORDER BY子句时。出现usingtemporary就说明语句需要优化了