作者：KG沈健《??数据库允许空值(null)，往往是悲剧的开始???》本文使用explain分析SQL的执行计划，分析null对索引命中的影响。Explain是MySQL性能调优过程中必须掌握的工具。今天花1分钟简单说一下explain结果中常见的类型结果及其含义，通过相同SQL语句的性能差异来说明建立正确索引的重要性。.解释结果中的类型字段是什么意思？MySQL官网的解释非常简洁，只用了3个字：j??ointype。它描述了用于查找所需数据的扫描方法。最常见的扫描方式有：system：系统表，数据量小，往往不需要磁盘IO；const：常量连接；eq_eq_rf（index（主键）或非空唯一索引（uniquenotnull）主键）或非空唯一索引（uniquenotnull）等价扫描；ref：非主键非唯一索引等效扫描；范围：范围扫描；index：索引树扫描；ALL：全表扫描（fulltablescan）；画外音：这些是最常见的，如果你去解释你工作中的SQL语句，95%都是以上几种。上面的各种扫描方式从快到慢依次为：system>const>eq_ref>ref>range>index>ALL以下举例。1.系统解释select*frommysql.time_zone;上例中，从系统库mysql的系统表time_zone中查询数据，扫码类型为system。这些数据已经加载到内存中，不需要磁盘IO。这种类型的扫描是最快的。解释select*from(select*fromuserwhereid=1)tmp;再举个例子，内层嵌套（const）返回临时表，外层嵌套从临时表查询，其扫描类型也是system，同样不需要磁盘IO，速度超快。2.const数据准备：createtableuser(idintprimarykey,namevarchar(20))engine=innodb;insertintouservalues(1,'shenjian');insertintouservalues(2,'zhangsan');插入用户值（3，'lisi'）；const扫描条件是：命中主键（primarykey）或唯一（unique）索引；连接的部分是一个常量（const）值；解释select*fromuserwhereid=1;如上例，id为PK，connection部分为常量1。画外音：这里注意，防止隐式类型转换。这种类型的扫描效率极高，返回的数据量小，而且速度非常快。3.eq_ref数据准备：createtableuser(idintprimarykey,namevarchar(20))engine=innodb;insertintouservalues(1,'shenjian');insertintouservalues(2,'zhangsan');insertintouservalues(3,'lisi');createtableuser_ex(idintprimarykey,ageint)engine=innodb;insertintouser_exvalues(1,18);insertintouser_exvalues(2,20);insertintouser_exvalues(3,30);insertintouser_exvalues(4,40);insertintouser_exvalues(5,50);eq_ref扫描条件是，对于前表的每一行（row），只有一行扫描后一个表。再细化一点：加入查询；命中主键（primarykey）或非空唯一（uniquenotnull）索引；等价联系；解释select*fromuser,user_exwhereuser.id=user_ex.id;如上例，id为Primarykey，通过eq_ref扫描joinquery。这些扫描也非常快。4.ref数据准备：createtableuser(idint,namevarchar(20),index(id))engine=innodb;插入用户值(1,'shenjian');插入用户值(2,'zhangsan');插入用户值(3,'lisi');创建表user_ex(idint,ageint,index(id))engine=innodb;插入user_ex值（1,18）；插入user_ex值（2,20）；插入user_ex值（3,30）；插入user_ex值（4,40）；插入user_ex值（5,50）；如果将上面eq_ref案例中的主键索引改成普通的非唯一（nonunique）索引。解释select*fromuser,user_exwhereuser.id=user_ex.id;从eq_ref降级为ref。此时，对于前表中的每一行（row），可能会扫描到后表中不止一行的数据。解释select*fromuserwhereid=1;当id改为普通的非唯一索引时，常量连接查询也从const降级为ref，因为扫描的数据可能不止一行。Ref扫描可能出现在join中，也可能出现在单表普通索引中。每个匹配项可能返回多行数据。虽然它比eq_ref慢，但它仍然是一种快速连接类型。5.range数据准备：createtableuser(idintprimarykey,namevarchar(20))engine=innodb;insertintouservalues(1,'shenjian');insertintouservalues(2,'zhangsan');insertintouservalues(3,'lisi');insertintouservalues(4,'wangwu');insertintouservalues(5,'zhaoliu');rangescan比较容易理解，它是rangeontheindex一种针对特定范围的值扫描索引的查询。解释select*fromuserwhereidbetween1and4;解释select*fromuserwhereidin(1,2,3);解释select*fromuserwhereid>3;上例中的between、in和>是典型的范围（range）查询。画外音：必须是索引，否则不能批量“跳过”。六、index索引类型，需要扫描索引上的所有数据。解释来自用户的计数（*）；如上例，id为主键，count查询需要扫描索引上的所有数据进行统计。画外音：这个表是针对InnoDB引擎的。它只比全表扫描快一点。7.ALL数据准备：createtableuser(idint,namevarchar(20))engine=innodb;insertintouservalues(1,'shenjian');insertintouservalues(2,'zhangsan');插入用户值（3，'lisi'）;创建表user_ex（idint，ageint）engine=innodb;插入user_ex值（1,18）;插入user_ex值（2,20）;插入user_ex值(3,30);insertintouser_exvalues(4,40);insertintouser_exvalues(5,50);explainselect*fromuser,user_exwhereuser.id=user_ex.id;如果没有在id上建索引，对于前一张表的每一行（row），后一张表都要全表扫描。在今天的文章中，同一个join语句出现了三次：扫描类型为eq_ref，此时id为主键；扫描类型为ref，此时id为非唯一普通索引；时间id上没有索引；可见建立正确的索引对于提高数据库的性能是多么的重要。全表扫描代价极大，性能低下，应尽量避免。通过explain来分析SQL语句是非常有必要的。总结(1)explain结果中的type字段表示（广义的）连接类型，它描述了用于查找所需数据的扫描方法；(2)常见的扫描类型有：system>const>eq_ref>ref>range>index>ALL，扫描速度由快到慢；(3)各种扫描类型的要点是：system最快：无磁盘IOconst：PK上的等值查询或唯一eq_ref：PK上的join查询或唯一等值匹配，对于其中的每一行（row）前表，后表只有一行命中ref：非唯一索引，等值匹配，可能有多行hitrange：对索引进行范围扫描，例如：between/in/>index：Fullsetscanon索引，例如：InnoDB的countALL是最慢的：全表扫描（4）建立正确的索引（index）非常重要；（5）使用explain理解和优化执行计划非常重要；想法比结论更重要，希望大家有所收获。画外音：本文在MySQL5.6上测试。
