MYSQL性能调优（二）EXPLAIN/DESC

一、语法

EXPLAIN/DESC + [查询SQL];

 

二、输出概要

id:  执行优先级标识
select_type: 每个select子句的查询类型
table:  输出结果集的表
partitions:  匹配的分区
type:  表的访问类型
possible_keys:  查询时可能使用的索引
key:  实际使用的索引
key_len:  索引字段的长度
ref:  列与索引的比较
rows:  扫描出的行数(估算的行数)
filtered:  按表条件过滤的行百分比
Extra:  执行情况的描述和说明

 

三、输出详解

1、id

id 相同时，执行顺序由上至下

id 不同时，id值越大优先级越高，越先被执行

 

2、select_type

(1) SIMPLE (简单SELECT，不使用UNION或子查询等)

(2) PRIMARY (查询中的最外层查询)

(3) UNION (UNION中的第二个或后面的SELECT语句)

(4) SUBQUERY (子查询中的第一个SELECT，结果不依赖于外部查询)

(5) UNION RESULT (UNION的结果)

(6) DEPENDENT UNION (UNION中的第二个或后面的SELECT语句，取决于外面的查询)

(7) DEPENDENT SUBQUERY (子查询中的第一个SELECT，取决于外面的查询)

(8) DERIVED(派生表的SELECT, FROM子句的子查询)

 

3、table

显示该行请求的数据库表名称，如上2-(7)(8)，可能是表名，可能是简称，可能是第几步执行的结果

 

4、partitions

表所在的分区，值为NULL代表未分区

 

5、type

‘访问类型’，即链表方式

常见的访问类型（性能从低到高）有： ALL、index、range、index_merge、ref_or_null、 ref、eq_ref、const、system、NULL

（1）ALL

顾名思义，mysql将进行全表扫描以找到匹配行

（2）index

与all一样都是读全表，优点就是避免了排序，区别是index只遍历索引树，而索引文件通常比数据文件小，所以一般比all快

(3)、range

范围扫描，一个有限制的索引扫描。使用一个索引来检索给定范围的行，key列为被使用的索引

通常就是出现在语句中的 =、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN、IN 等操作符

(4)、index_merge

ndex_merge表示使用了索引合并优化方法。key列包含了使用的索引的清单，key_len包含了使用的索引的最长的关键元素

(5)、ref_or_null

类似ref，但是可以搜索值为NULL的行

(6)、ref

一种非唯一性索引扫描，返回所有匹配某个单个值的行。不适用唯一索引，而是使用普通索引或者唯一索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行。

creator为普通索引（非主键索引或唯一索引）

(7)、eg_ref

唯一索引扫描，最多只返回一条符合条件的记录。使用唯一性索引或主键查找，是const之外最好的联接类型，简单的select查询不会出现eg_ref

(8)、 const

const用于primary key 或者unique key 的所有列与常数比较。确定最多只会有一个匹配行时，MySQL优化器会在查询前读取它而且只读取一次，所以非常快。

如将主键置于where条件中，MySQL将把查询转换为一个常量。

 

(9)、system

system是const的特例，表仅有一行时满足条件，平时不会出现

(10)、NULL

mysql能够在优化阶段分解查询语句，在执行阶段不用访问表或索引。例如，在索引列找最大或最小值，只需要查找索引，不需要在执行时访问表

 

6、possible_keys、key、key_len

（1）possible_keys显示查询可能会用到哪些索引，但不一定会使用。如果显示为NULL，则没有可用的索引

（2）key显示查询实际采用的索引。如果显示为NULL，则表示未使用索引。如果想MySQL强制使用或忽视possible_keys列中的索引，使用FORCE INDEX、USE INDEX或者IGNORE INDEX

（3）key_len显示索引中使用的字节数。可以通过该值计算出具体使用了索引中的哪些列

注意：在不损失精确性的情况下，长度越短越好。key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，并不是通过表内检索出的

 

计算方式：

--定长数据类型：如int、char、datetime等，需要有是否为空的标记，这个标记占用一个字节（对于not null来说不需要这一个字节）

• tinyint：1字节
• smallint：2字节
• int：4字节
• bigint：8字节
• date：3字节
• timestamp：4字节
• datetime：8字节

--变长数据类型：如varchar，除了是否为空的标记外，还需要有长度信息，需要占用两个字节

--对于char，varchar，blob，text等，key_len的长度还和字符集有关，latin1 一个字符占用一个字节，gbk 一个字符占用两个字节，utf8 一个字符占用三个字节，utf8mb4一个字符占用4个字节

 

例如：

字符集; utf8mb4

`id` int(11) NOT NULL AUTO_INCREMENT,

`dept_id` int(11) DEFAULT NULL ,

PRIMARY KEY (`id`),

UNIQUE KEY `id_index` (`id`,`dept_id`) USING BTREE

ken_len=4（ int 4字节，仅使用了主键索引，未使用唯一索引）

 `org_id` int(11) DEFAULT NULL

ken_len=4+1（ int 4字节 加 1字节 DEFAULT NULL 标识）

`creator` varchar(20) DEFAULT NULL

key_len=83=20*4+2+1( creator字段默认为空【DEFAULT NULL】标记加1字节，变长数据类型 加2字节记录长度)

 

7、ref

ref表示当查询使用索引时，与索引连接查询的列。当为常数时，ref为const

8、rows

rows表示mysql估算执行查询时必须检查的行数

9、filtered

filtered表示返回结果的行数占需要读取行数的百分比，值越大越好

filtered只有在type为ALL或者index时适用，因为走索引就能找到要要查询的记录，filtered=100

注意：filtered值在很多时候是不准确的

10、Extra

（1）distinct

MySQL发现第1个匹配行后，停止为当前的行组合搜索更多的行

（2）Using index

只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的信息。即，对表的全部的请求列都是同一个索引的部分的时候

（3）Using where

mysql服务器将在存储引擎检索行后再进行过滤

（4）Using temporary

表示MySQL需要使用临时表来存储结果集（一般需要加索引优化）

（5）Using filesort

MySQL中无法利用索引完成的排序操作称为“文件排序”。mysql 会对结果使用一个外部索引排序，而不是按索引次序从表里读取行

（6）Using join buffer

在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果（可能需要添加索引优化）

（7）Impossible WHERE,Impossible HAVING

where语句、having总是为false,会导致没有符合条件的行

（8）Select tables optimized away

使用某些聚合函数（比如：max、min）来访问存在索引的某个字段

（9）No tables used

查询语句中使用from dual 虚拟表或不含任何from子句

(10) no matching row in const table、 No matching min/max row

表为空或者表中根据唯一键查询时没有匹配的行

(11)  Not exists

MySQL能够对查询执行LEFT JOIN优化，并且在找到与LEFT JOIN条件匹配的一行后，不会在上一行组合中检查此表中的更多行

例如：

MySQL会扫描a，并使用a.id的值查找s中的行。 如果MySQL在s中找到一个匹配的行，它会知道s.id永远不会为NULL，并且不扫描t2中具有相同id值的其余行

(12)  Using index condition

 会先条件过滤索引，过滤完索引后找到所有符合索引条件的数据行，随后用 WHERE 子句中的其他条件去过滤这些数据行

(13) Start temporary,End temporary

表示半连接中使用了Duplicate Weedout策略的临时表

（14）Using sort_union(...) , Using union(...) , Using intersect(...)

表示使用了 index merge 索引合并。一般可以通过建立复合索引优化

1）index intersect merge 就是多个索引条件扫描得到的结果进行交集运算, 即AND运算。当符合以下两种where条件或组合时会使用 index intersect merge

      -- 条件使用到复合索引中的所有字段或者左前缀字段(对单字段索引也适用)

      -- 主键上的任何范围条件

2) index uion merge就是多个索引条件扫描，对得到的结果进行并集运算，即 OR 运算。当符合以下两种where条件或组合时会使用 index uion merge

     -- 条件使用到复合索引中的所有字段或者左前缀字段(对单字段索引也适用)

     --主键上的任何范围条件

    -- 任何符合 index intersect merge 的where条件

3) sort_union 多个条件扫描进行 OR 运算，但是不符合 index union merge算法的，此时可能会使用 sort_union算法（ sort-union和union的区别在于返回结果之前是否排序）