MySQL之Explain性能分析

　　一、Explain 概述
　　使用 EXPLAIN 关键字可以模拟优化器来执行SQL查询语句，从而知道MySQL是如何处理我们的SQL语句的。分析出查询语句或是表结构的性能瓶颈。 1、MySQL查询过程
　　通过explain我们可以获得以下信息： 表的读取顺序； 数据读取操作的操作类型； 哪些索引可以被使用； 哪些索引真正被使用； 表的直接引用； 每张表的有多少行被优化器查询了；
　　Explain使用方式:  explain+sql语句 , 通过执行explain可以获得sql语句执行的相关信息。 explain select * from L1;
　　二、Explain 详解
　　1) 数据准备 -- 创建数据库  CREATE DATABASE test_explain CHARACTER SET ＂utf8＂;   -- 创建表  CREATE TABLE L1(id INT PRIMARY KEY AUTO_INCREMENT,title VARCHAR(100) ); CREATE TABLE L2(id INT PRIMARY KEY AUTO_INCREMENT,title VARCHAR(100) );  CREATE TABLE L3(id INT PRIMARY KEY AUTO_INCREMENT,title VARCHAR(100) );  CREATE TABLE L4(id INT PRIMARY KEY AUTO_INCREMENT,title VARCHAR(100) );   -- 每张表插入3条数据  INSERT INTO L1(title) VALUES(＂zhang001＂),(＂zhang002＂),(＂zhang003＂);  INSERT INTO L2(title) VALUES(＂zhang004＂),(＂zhang005＂),(＂zhang006＂);  INSERT INTO L3(title) VALUES(＂zhang007＂),(＂zhang008＂),(＂zhang009＂);  INSERT INTO L4(title) VALUES(＂zhang010＂),(＂zhang011＂),(＂zhang012＂);
　　2) ID字段说明
　　select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序
　　id相同，执行顺序由上至下 EXPLAIN SELECT * FROM L1,L2,L3 WHERE L1.id=L2.id AND L2.id = L3.id;
　　id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行EXPLAIN SELECT * FROM L2 WHERE id = ( SELECT id FROM L1 WHERE id = (SELECT L3.id FROM L3 WHERE L3.title = ＂zhang＂));
　　3) select_type和table字段说明
　　表示查询类型，主要用于区别普通查询，联合查询，子查询等的复杂查询
　　simple :  简单的select查询，查询中不包含子查询或者UNION EXPLAIN SELECT * FROM L1 where id = 1;
　　primary :  查询中若包含任何复杂的子部分，最外层查询被标记
　　subquery :  在select或where列表中包含了子查询 EXPLAIN SELECT * FROM L2 WHERE id = ( SELECT id FROM L1 WHERE id = (SELECT L3.id FROM L3 WHERE L3.title = ＂zhang＂));
　　union :  union连接的两个select查询，第一个查询是dervied派生表，除了第一个表外，第二个以后的表select_type都是union
　　derived :  在from列表中包含的子查询被标记为derived（派生表），MySQL会递归执行这些子查询，把结果放到临时表中
　　union result :  UNION 的结果 EXPLAIN SELECT * FROM (select * from L3 union select * from L4)a;
　　4) type字段说明
　　type字段在 MySQL 官网文档描述如下：
　　The join type. For descriptions of the difffferent types.
　　type字段显示的是连接类型 ( join type表示的是用什么样的方式来获取数据)，它描述了找到所需数据所使用的扫描方式, 是较为重要的一个指标。
　　下面给出各种连接类型,按照从最佳类型到最坏类型进行排序: -- 完整的连接类型比较多  system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL   -- 简化之后,我们可以只关注一下几种  system > const > eq_ref > ref > range > index > ALL
　　一般来说,需要保证查询至少达到 range级别,最好能到ref,否则就要就行SQL的优化调整
　　下面介绍type字段不同值表示的含义:
　　system:  表中就仅有一行数据的时候. 这是const连接类型的一个特例,很少出现。
　　const:  const表示命中主键索引(primary key) 或者唯一索引(unique),表示通过索引一次就找到数据记录。因为只匹配一条记录,所以被连接的部分是一个常量。(如果将主键放在 where条件中, MySQL就能将该查询转换为一个常量) 这种类型非常快。
　　例如以下查询: explain select * from L1 where id = 3;  -- 为L1表的title字段添加唯一索引 alter table L1 add unique(title);  explain select * from L1 where title = ＂zhang＂;
　　eq_ref  : 对于前一个表中的每个一行，后表只有一行被扫描。除了system和const类型之外，这是最好的连接类型。只有当联接使用索引的部分都是主键或惟一非空索引时，才会出现这种类型。
　　例如以下查询： EXPLAIN SELECT L1.id,L1.title FROM L1 left join L2 on L1.id = L2.id;
　　ref  : 非唯一性索引扫描(使用了普通索引), 对于前表的每一行(row)，后表可能有多于一行的数据被扫描,它返回所有匹配某个单独值的行。
　　例如以下查询: -- 为L1表的title字段添加普通索引 alter table L1 add index idx_title (title) ; EXPLAIN SELECT * FROM L1 inner join L2 on L1.title = L2.title;
　　range  : 索引上的范围查询,检索给定范围的行,between，in函数，> 都是典型的范围(range)查询。
　　例如以下查询: EXPLAIN SELECT * FROM L1 WHERE L1.id between 1 and 10;
　　注: 当in函数中的数据很大时,可能会导致效率下降,最终不走索引
　　index  : 出现index 是 SQL 使用了索引, 但是没有通过索引进行过滤,需要扫描索引上的全部数据 ( 查找所有索引树,比ALL快一些,因为索引文件要比数据文件小   ), 一般是使用了索引进行排序分组。 EXPLAIN SELECT * FROM L2 group by id order by id;  -- 该count查询需要通过扫描索引上的全部数据来计数 EXPLAIN SELECT count(*) FROM L2;
　　ALL  : 没有使用到任何索引, 连接查询时对于前表的每一行，后表都要被全表扫描。 EXPLAIN SELECT * FROM L3 inner join L4 on L3.title = L4.title;
　　总结各类type类型的特点:
　　5) possible_keys 与 key说明
　　possible_keys
　　显示可能应用到这张表上的索引 , 一个或者多个. 查询涉及到的字段上若存在索引, 则该索引将被列出, 但不一定被查询实际使用.
　　key
　　实际使用的索引 ，若为null，则没有使用到索引。（两种可能，1.没建立索引, 2.建立索引，但索引失效）。查询中若使用了覆盖索引，则该索引仅出现在key列表中。
　　1. 理论上没有使用索引,但实际上使用了 EXPLAIN SELECT L3.id FROM L3;
　　2. 理论和实际上都没有使用索引 EXPLAIN SELECT * FROM L3 WHERE title = ＂zhang007＂;
　　3. 理论和实际上都使用了索引 EXPLAIN SELECT * FROM L2 WHERE title = ＂zhang004＂;
　　6) key_len字段说明
　　表示索引中使用的字节数, 可以通过该列计算查询中使用索引的长度。
　　key_len 字段能够帮你检查是否充分利用了索引，  ken_len 越长, 说明索引使用的越充分。
　　创建表
　　CREATE TABLE L5( 	a INT PRIMARY KEY, 	b INT NOT NULL, 	c INT DEFAULT NULL, 	d CHAR(10) NOT NULL );
　　使用explain 进行测试 EXPLAIN SELECT * FROM L5 WHERE a > 1 AND b = 1;
　　观察key_len的值, 索引中只包含了1列 是int类型 ,所以,key_len是4字节。
　　为b字段添加索引,进行测试
　　ALTER TABLE L5 ADD INDEX idx_b(b);  -- 执行SQL,这次将b字段也作为条件 EXPLAIN SELECT * FROM L5 WHERE a > 1 AND b = 1;
　　为c、d字段添加联合索引,然后进行测试 ALTER TABLE L5 ADD INDEX idx_c_b(c,d);   explain select * from L5 where c = 1 and d = ＂＂;
　　c字段是int类型 4个字节, d字段是 char(10)代表的是10个字符相当30个字节
　　数据库的字符集是utf8 一个字符3个字节,d字段是 char(10)代表的是10个字符相当30个字节,多出的一个字节用来表示是联合索引
　　下面这个例子中,虽然使用了联合索引,但是可以根据ken_len的长度推测出该联合索引只使用了一部分,没有充分利用索引,还有优化空间。 explain select * from L5 where c = 1 ;
　　7) ref 字段说明
　　显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值
　　L1.id=＂1＂; 1是常量 , ref = const EXPLAIN SELECT * FROM L1 WHERE L1.id=1;
　　8) rows 字段说明
　　表示MySQL根据表统计信息及索引选用情况,估算的找到所需的记录所需要读取的行数。
　　L3中的title没有添加索引, 所以L3中有3条记录,就需要读取3条记录进行查找。 EXPLAIN SELECT * FROM L3,L4 WHERE L3.id = L4.id AND L3.title LIKE ＂zhang007＂;
　　需要注意的是 rows只是一个估算值,并不准确 .所以rows行数过大的问题并不值得过多考虑，主要分析的还是索引是否使用正确了
　　9) fifiltered 字段说明
　　它指返回结果的行占需要读到的行(rows列的值)的百分比。
　　10) extra 字段说明
　　Extra 是 EXPLAIN 输出中另外一个很重要的列，该列显示MySQL在查询过程中的一些详细信息。
　　准备数据
　　CREATE TABLE users ( uid INT PRIMARY KEY AUTO_INCREMENT, uname VARCHAR(20), age INT(11) );  INSERT INTO users VALUES(NULL, ＂lisa＂,10); INSERT INTO users VALUES(NULL, ＂lisa＂,10); INSERT INTO users VALUES(NULL, ＂rose＂,11); INSERT INTO users VALUES(NULL, ＂jack＂, 12); INSERT INTO users VALUES(NULL, ＂sam＂, 13);
　　Using fifilesort
　　执行结果Extra为 Using filesort ，这说明，得到所需结果集，需要对所有记录进行文件排序。这类SQL语句性能极差，需要进行优化。
　　典型的，在一个没有建立索引的列上进行了order by，就会触发fifilesort，常见的优化方案是，在order by的列上添加索引，避免每次查询都全量排序。 EXPLAIN SELECT * FROM users ORDER BY age;
　　Using temporary
　　表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询 EXPLAIN SELECT COUNT(*),uname FROM users GROUP BY uname;
　　需要注意的是： 返回所有记录的SQL，不使用where条件过滤数据，大概率不符合预期，对于这类SQL往往需要进行优化； 使用了where条件的SQL，并不代表不需要优化，往往需要配合explain结果中的type（连接类型）来综合判断。例如本例查询的 age 未设置索引，所以返回的type为ALL，仍有优化空间，可以建立索引优化查询。 EXPLAIN SELECT * FROM users WHERE age=10;
　　Using index
　　表示直接访问索引就能够获取到所需要的数据(覆盖索引) , 不需要通过索引回表。 -- 为uname创建索引 alter table users add index idx_uname(uname); EXPLAIN SELECT uid,uname FROM users WHERE uname=＂lisa＂;
　　Using join buffffer
　　使用了连接缓存, 会显示join连接查询时,MySQL选择的查询算法。 EXPLAIN SELECT * FROM users u1 LEFT JOIN (SELECT * FROM users WHERE age = 1)u2 ON u1.age = u2.age;
　　Using join buffer (Block Nested Loop) 说明，需要进行嵌套循环计算, 这里每个表都有五
　　条记录，内外表查询的type都为ALL。
　　问题在于 两个关联表的关联使用了字段 age，并且age字段未建立索引，就会出现这种情况。
　　Using index condition
　　查找使用了索引 (但是只使用了一部分,一般是指联合索引)，但是需要回表查询数. explain select * from L5 where c > 10 and d = ＂＂;
　　Extra主要指标的含义(有时会同时出现) using index ： 使用覆盖索引的时候就会出现； using where ： 在查找使用索引的情况下，需要回表去查询所需的数据； using index condition ： 查找使用了索引，但是需要回表查询数据； using index & using where ： 查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据；