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