SQL性能测试与优化：Explain

explain select surname,first_name form a,b where a.id=b.id 

就像上边这个语句，就是在普通的Sql前边加个explain，一般是查询语句。

下边输出的这个东西叫执行计划

id(查询序列号)

• id如果相同，可以认为是一组，从上往下顺序执行

• 在所有组中，id值越大，优先级越高，越先执行

• 关注点：id号每个号码，表示一趟独立的查询,一个sql的查询趟数越少越好

 SELECT识别符。这是SELECT的查询序列号。有几个select，id的值就会有几个，相同id下，一行代表一个表，id不同，值越大执行顺序越靠前。

select_type（查询类型）

SIMPLE：简单查询，没有union和子查询

 EXPLAIN SELECT * from `sys_message` where title ='0';

 PRIMARY ：最外层查询 (在存在子查询的语句中，最外面的select查询就是primary)

EXPLAIN SELECT * from sys_message where id =10 UNION SELECT * FROM sys_message where id=11;

 DERIVED：在FROM列表中包含的子查询被标记为DERIVED(衍生);MySQL会递归执行这些子查询, 把结果放在临时表里。

EXPLAIN SELECT *FROM (SELECT* FROM sys_message LIMIT 5) AS s; 

  SUBQUERY： 映射为子查询(在SELECT或WHERE列表中包含了子查询)

EXPLAIN SELECT sys_message.*,(select 2 from sys_message as p2) FROM 
sys_message where title = (select title from sys_message where id=10);

EXPLAIN SELECT title FROM sys_message where text = (select text from sys_message where id=10); 

 

DEPENDENT SUBQUERY：与SUBQUERY的区别维SUBQUERY的值为单值，依赖子查询为

union： 联合 

UNION RESULT： 联合的结果

table

这个就是表的名称，可能会出现临时表，结果表等

type：联接类型（很重要的指标）

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

SQL性能优化的目标：至少要达到 range级别，要求是ref级别，最好是consts级别。（阿里巴巴开发手册要求）

system：表仅有一行(=系统表)。这是const联接类型的一个特例。

const：针对主键或者唯一索引的等值查询，最多只会返回一行数据，const查询速度非常快，常量连接

EXPLAIN SELECT * from ceshi where id=1;

eq_ref: 简单来说就是主键或者唯一索引进行等值查询。

EXPLAIN SELECT * from ceshi c1,ceshi2 c2 where c1.id=c2.id;

 ref：非主键非唯一索引   对于前表后表可能有多歌数据与之对应

 EXPLAIN SELECT * from ceshi c1,ceshi2 c2 where c1.name=c2.name;

EXPLAIN SELECT * from ceshi  where name = '张三'; 

fulltext：全文索引

EXPLAIN SELECT * from ceshi2   where MATCH(content)  AGAINST('1') ;

 ref_or_null：类似REF，只是搜索条件包括：连接字段的值可以为NULL的情况

 EXPLAIN SELECT * from ceshi  where name ='张三' OR name is NULL;

index_merge ： 索引合并表示查询使用了两个以上的索引，最后取交集或者并集，常见and ，or的条件使用了不同的索引.效率不是很高

EXPLAIN SELECT * from ceshi where name='1' OR type ='0'; 

 unique_subquery：针对一些包含‘IN’的子查询中，如果查询优化器决定将‘IN’子查询转化为‘Exists’子查询时，并且子查询可以使用主键进行等值匹配  ，type就是 unique_subquery

explain select * from ceshi where name in (SELECT  id from ceshi2 WHERE ceshi.name=ceshi2.name ) OR age=1;

 

 

index_subquery：该联接类型类似于unique_subquery。可以替换IN子查询，但只适合下列形式的子查询中的非唯一索引：value IN (SELECT key_column FROM single_table WHERE some_expr) 

 

range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引。key_len包含所使用索引的最长关键元素。在该类型中ref列为NULL。当使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符，用常量比较关键字列时，可以使用range

EXPLAIN SELECT * from ceshi  where age BETWEEN 1 AND 2; 

 

index：该联接类型与ALL相同，除了只有索引树被扫描。这通常比ALL快，因为索引文件通常比数据文件小。 

explain select id from ceshi;

 

 

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

possible_keys

possible_keys列指出MySQL能使用哪个索引在该表中找到行。注意，该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。

key

key列显示MySQL实际决定使用的键(索引)。如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

key_len

计算规则如下：
（当然，索引的最大长度为768，如果超过了这个长度，mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索 引。）

字符串 :

char(n)：n字节长度
varchar(n)：如果是utf-8，则长度 3n + 2 字节，加的2字节用来存储字符串可变长信息
数值类型:

tinyint：1字节
smallint：2字节
int：4字节
bigint：8字节
时间类型:

date：3字节
timestamp：4字节
datetime：8字节
如果字段允许为 NULL，需要1字节记录是否为 NULL

ref

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

rows

rows 列显示 MySQL 认为它执行查询时必须检查的行数。注意这是一个预估值。

filterd

和rows相关，表示从rows中取了百分之多少，值越大越好

Extra

Extra 是 EXPLAIN 输出中另外一个很重要的列，该列显示 MySQL 在查询过程中的一些详细信息，MySQL 查询优化器执行查询的过程中对查询计划的重要补充信息。

1. Distinct：MySQL发现第1个匹配行后，停止为当前的行组合搜索更多的行。
2. Not exists：MySQL能够对查询进行LEFT JOIN优化，发现1个匹配LEFT JOIN标准的行后，不再为前面的的行组合在该表内检查更多的行。
3. range checked for each record (index map: #)：MySQL没有发现好的可以使用的索引，但发现如果来自前面的表的列值已知，可能部分索引可以使用。对前面的表的每个行组合，MySQL检查是否可以使用range或index_merge访问方法来索取行。
4. Using filesort：MySQL需要额外的一次传递，以找出如何按排序顺序检索行。通过根据联接类型浏览所有行并为所有匹配WHERE子句的行保存排序关键字和行的指针来完成排序。然后关键字被排序，并按排序顺序检索行。
5. Using index：从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。当查询只使用作为单一索引一部分的列时，可以使用该策略。
6. Using temporary：为了解决查询，MySQL需要创建一个临时表来容纳结果。典型情况如查询包含可以按不同情况列出列的GROUP BY和ORDER BY子句时。
7. Using where：WHERE子句用于限制哪一个行匹配下一个表或发送到客户。除非你专门从表中索取或检查所有行，如果Extra值不为Using where并且表联接类型为ALL或index，查询可能会有一些错误。
8. Using sort_union(...), Using union(...), Using intersect(...)：这些函数说明如何为index_merge联接类型合并索引扫描。
9. Using index for group-by：类似于访问表的Using index方式，Using index for group-by表示MySQL发现了一个索引，可以用来查询GROUP BY或DISTINCT查询的所有列，而不要额外搜索硬盘访问实际的表。并且，按最有效的方式使用索引，以便对于每个组，只读取少量索引条目。

通过相乘EXPLAIN输出的rows列的所有值，你能得到一个关于一个联接如何的提示。这应该粗略地告诉你MySQL必须检查多少行以执行查询。当你使用max_join_size变量限制查询时，也用这个乘积来确定执行哪个多表SELECT语句。