MySQL explain 执行计划解读

本文我们主要介绍了 MySQL 性能分析以及 explain 的使用,包括:组合索引、慢查询分析、MYISAM 和 INNODB 的锁定、MYSQL 的事务配置项等,希望能够对您有所帮助。

1.使用 explain 语句去查看分析结果
如 explain select * from test1 where id=1; 会出现:id selecttype table type possiblekeys key keylen ref rows extra 各列。


其中,
type=const 表示通过索引一次就找到了;
key=primary 的话,表示使用了主键;
type=all, 表示为全表扫描;
key=null 表示没用到索引。

type=ref,因为这时认为是多个匹配行,在联合查询中,一般为 REF。

2.MYSQL 中的组合索引
假设表有 id,key1,key2,key3, 把三者形成一个组合索引,则如:
复制代码 代码如下:

[sql] view plain copy

  1. where key1=….
  2. where key1=1 and key2=2
  3. where key1=3 and key2=3 and key3=2

根据最左前缀原则,这些都是可以使用索引的,如 from test where key1=1 order by key3,用 explain 分析的话,只用到了 normal_key 索引,但只对 where 子句起作用,而后面的 order by 需要排序。

3.使用慢查询分析(实用)
在 my.ini 中:
longquerytime=1
log-slow-queries=d:\mysql5\logs\mysqlslow.log
把超过 1 秒的记录在慢查询日志中
可以用 mysqlsla 来分析之。也可以在 mysqlreport 中,有如
DMS 分别分析了 select ,update,insert,delete,replace 等所占的百分比

4.MYISAM 和 INNODB 的锁定
myisam 中,注意是表锁来的,比如在多个 UPDATE 操作后,再 SELECT 时,会发现 SELECT 操作被锁定了,必须等所有 UPDATE 操作完毕后,再能 SELECT
innodb 的话则不同了,用的是行锁,不存在上面问题。

5.MYSQL 的事务配置项
innodbflushlogattrx_commit=1
表示事务提交时立即把事务日志 flush 写入磁盘,同时数据和索引也更新,很费性能。
innodbflushlogattrx_commit=0
事务提交时,不立即把事务日志写入磁盘,每隔 1 秒写一次,MySQL 挂了可能会丢失事务的数据。
innodbflushlogattrx_commit=2 ,在整个操作系统 挂了时才可能丢数据,一般不会丢失超过 1-2 秒的更新。
事务提交时,立即写入磁盘文件(这里只是写入到系统内核缓冲区,但不立即刷新到磁盘,而是每隔 1 秒刷新到磁盘,同时更新数据和索引),这种方案是不是性价比好一些,当然如何配置,决定于你对系统数据安全性的要求。

explain 用法详解
EXPLAIN tblname 或:EXPLAIN [EXTENDED] SELECT selectoptions
前者可以得出一个表的字段结构等等,后者主要是给出相关的一些索引信息,而今天要讲述的重点是后者。
举例

[sql] view plain copy

  1. mysql> explain select * from event;
  2. +—-+————-+——-+——+—————+——+———+——+——+——-+
  3. | id | selecttype | table | type | possiblekeys | key | key_len | ref | rows | Extra |
  4. +—-+————-+——-+——+—————+——+———+——+——+——-+
  5. | 1 | SIMPLE | event | ALL | NULL | NULL | NULL | NULL | 13 | |
  6. +—-+————-+——-+——+—————+——+———+——+——+——-+
  7. 1 row in set (0.00 sec)

各个属性的含义


id :select 查询的序列号

select_type:select 查询的类型,主要是区别普通查询和联合查询、子查询之类的复杂查询。

a.SIMPLE:查询中不包含子查询或者 UNION

b.查询中若包含任何复杂的子部分,最外层查询则被标记为:PRIMARY

c.在 SELECT 或 WHERE 列表中包含了子查询,该子查询被标记为:SUBQUERY

d.在 FROM 列表中包含的子查询被标记为:DERIVED(衍生)

e.若第二个 SELECT 出现在 UNION 之后,则被标记为 UNION;若 UNION 包含在 FROM 子句的子查询中,外层 SELECT 将被标记为:DERIVED

f.从 UNION 表获取结果的 SELECT 被标记为:UNION RESULT

table :输出的行所引用的表。

type :联合查询所使用的类型,表示 MySQL 在表中找到所需行的方式,又称“访问类型”。


type 显示的是访问类型,是较为重要的一个指标,结果值从好到坏依次是:
system > const > eqref > ref > fulltext > refornull > indexmerge > uniquesubquery > indexsubquery > range > index > ALL ,一般来说,得保证查询至少达到 range 级别,最好能达到 ref。

ALL: 扫描全表
index: 扫描全部索引树
range: 扫描部分索引,索引范围扫描,对索引的扫描开始于某一点,返回匹配值域的行,常见于 between、<、>等的查询
ref: 非唯一性索引扫描,返回匹配某个单独值的所有行。常见于使用非唯一索引即唯一索引的非唯一前缀进行的查找

eq_ref:唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配。常见于主键或唯一索引扫描
const, system: 当 MySQL 对查询某部分进行优化,并转换为一个常量时,使用这些类型访问。如将主键置于 where 列表中,MySQL 就能将该查询转换为一个常量。system 是 const 类型的特例,当查询的表只有一行的情况下, 使用 system。
NULL: MySQL 在优化过程中分解语句,执行时甚至不用访问表或索引。

如下所示:

possible_keys:指出 MySQL 能使用哪个索引在该表中找到行。查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询使用。如果是空的,没有相关的索引。这时要提高性能,可通过检验 WHERE 子句,看是否引用某些字段,或者检查字段不是适合索引。

key :显示 MySQL 实际决定使用的键。如果没有索引被选择,键是 NULL。

key_len:显示 MySQL 决定使用的键长度。表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度。如果键是 NULL,长度就是 NULL。文档提示特别注意这个值可以得出一个多重主键里 mysql 实际使用了哪一部分。

注:keylen 显示的值为索引字段的最大可能长度,并非实际使用长度,即 keylen 是根据表定义计算而得,不是通过表内检索出的。

ref:显示哪个字段或常数与 key 一起被使用。

rows:这个数表示 mysql 要遍历多少数据才能找到,表示 MySQL 根据表统计信息及索引选用情况,估算的找到所需的记录所需要读取的行数,在 innodb 上可能是不准确的。

Extra:包含不适合在其他列中显示但十分重要的额外信息。

Only index,这意味着信息只用索引树中的信息检索出的,这比扫描整个表要快。


using where 是使用上了 where 限制,表示 MySQL 服务器在存储引擎受到记录后进行“后过滤”(Post-filter),如果查询未能使用索引,Using where 的作用只是提醒我们 MySQL 将用 where 子句来过滤结果集。


impossible where 表示用不着 where,一般就是没查出来啥。

Using filesort(MySQL 中无法利用索引完成的排序操作称为“文件排序”)当我们试图对一个没有索引的字段进行排序时,就是 filesoft。它跟文件没有任何关系,实际上是内部的一个快速排序。


Using temporary(表示 MySQL 需要使用临时表来存储结果集,常见于排序和分组查询),使用 filesort 和 temporary 的话会很吃力,WHERE 和 ORDER BY 的索引经常无法兼顾,如果按照 WHERE 来确定索引,那么在 ORDER BY 时,就必然会引起 Using filesort,这就要看是先过滤再排序划算,还是先排序再过滤划算。

最后,再看一个查询计划的例子:

第一行:id 列为 1,表示第一个 select,selecttype 列的 primary 表示该查询为外层查询,table 列被标记为,表示查询结果来自一个衍生表,其中 3 代表该查询衍生自第三个 select 查询,即 id 为 3 的 select。[select d1.name……]
第二行:id 为 3,表示该查询的执行次序为 2(4→3),是整个查询中第三个 select 的一部分。因查询包含在 from 中,所以为 derived。[select id,name from t1 where othercolumn=’’]
第三行:select 列表中的子查询,selecttype 为 subquery,为整个查询中的第二个 select。[select id from t3]
第四行:selecttype 为 union,说明第四个 select 是 union 里的第二个 select,最先执行。[select name,id from t2]
第五行:代表从 union 的临时表中读取行的阶段,table 列的表示用第一个和第四个 select 的结果进行 union 操作。[两个结果 union 操作]

关于 MySQL 执行计划的局限性:

EXPLAIN 不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况
EXPLAIN 不考虑各种 Cache
EXPLAIN 不能显示 MySQL 在执行查询时所作的优化工作
部分统计信息是估算的,并非精确值
EXPALIN 只能解释 SELECT 操作,其他操作要重写为 SELECT 后查看。

备注:

filesort 是通过相应的排序算法, 将取得的数据在内存中进行排序。

MySQL 需要将数据在内存中进行排序,所使用的内存区域也就是我们通过 sortbuffersize 系统变量所设置的排序区。这个排序区是每个 Thread 独享的,所以说可能在同一时刻在 MySQL 中可能存在多个 sort buffer 内存区域。

在 MySQL 中 filesort 的实现算法实际上是有两种:

双路排序:是首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行指针信息,然后在 sort buffer 中进行排序。
单路排序:是一次性取出满足条件行的所有字段,然后在 sort buffer 中进行排序。

在 MySQL4.1 版本之前只有第一种排序算法双路排序,第二种算法是从 MySQL4.1 开始的改进算法,主要目的是为了减少第一次算法中需要两次访问表数据的 IO 操作,将两次变成了一次,但相应也会耗用更多的 sortbuffer 空间。当然,MySQL4.1 开始的以后所有版本同时也支持第一种算法。
MySQL 主要通过比较我们所设定的系统参数 maxlengthforsortdata 的大小和 Query 语句所取出的字段类型大小总和来判定需要使用哪一种排序算法。如果 maxlengthforsortdata 更大,则使用第二种优化后的算法,反之使用第一种算法。所以如果希望 ORDER BY 操作的效率尽可能的高,一定要注意 maxlengthforsortdata 参数的设置。如果 filesort 过程中,由于排序缓存的大小不够大,那么就可能会导致临时表的使用。

maxlengthforsortdata 的默认值是 1024。

关于 MySQL 性能分析及 explain 用法的知识就介绍到这里,希望本次的介绍能够对您有所收获!