网站开发多久能学会,做网站开发的有哪些公司好,义乌企业网站,asp.net做购物网站《数据库允许空值#xff0c;往往是悲剧的开始》一文通过explain来分析SQL的执行计划#xff0c;来分析null对索引命中情况的影响#xff0c;有不少朋友留言#xff0c;问explain结果中的type字段#xff0c;ref#xff0c;ALL等不一样的值究竟是什么含义。今天花1分钟简… 《数据库允许空值往往是悲剧的开始》一文通过explain来分析SQL的执行计划来分析null对索引命中情况的影响有不少朋友留言问explain结果中的type字段refALL等不一样的值究竟是什么含义。今天花1分钟简单说下常见的type结果及代表的含义并且通过同一个SQL语句的性能差异说明建对索引多么重要。explain结果中的type字段代表什么意思MySQL的官网解释非常简洁只用了3个单词连接类型(the join type)。它描述了找到所需数据使用的扫描方式。最为常见的扫描方式有system系统表少量数据往往不需要进行磁盘IOconst常量连接eq_ref主键索引(primary key)或者非空唯一索引(unique not null)等值扫描ref非主键非唯一索引等值扫描range范围扫描index索引树扫描ALL全表扫描(full table scan)画外音这些是最常见的大家去explain自己工作中的SQL语句95%都是上面这些类型。上面各类扫描方式由快到慢system const eq_ref ref range index ALL下面一一举例说明。一、systemexplain select * from mysql.time_zone;上例中从系统库mysql的系统表time_zone里查询数据扫码类型为system这些数据已经加载到内存里不需要进行磁盘IO。这类扫描是速度最快的。explain select * from (select * from user where id1) tmp;再举一个例子内层嵌套(const)返回了一个临时表外层嵌套从临时表查询其扫描类型也是system也不需要走磁盘IO速度超快。二、const数据准备create table user (id int primary key,name varchar(20))engineinnodb;insert into user values(1,shenjian);insert into user values(2,zhangsan);insert into user values(3,lisi);const扫描的条件为(1)命中主键(primary key)或者唯一(unique)索引(2)被连接的部分是一个常量(const)值explain select * from user where id1;如上例id是PK连接部分是常量1。画外音别搞什么类型转换的幺蛾子。这类扫描效率极高返回数据量少速度非常快。三、eq_ref数据准备create table user (id int primary key,name varchar(20))engineinnodb;insert into user values(1,shenjian);insert into user values(2,zhangsan);insert into user values(3,lisi);create table user_ex (id int primary key,age int)engineinnodb;insert into user_ex values(1,18);insert into user_ex values(2,20);insert into user_ex values(3,30);insert into user_ex values(4,40);insert into user_ex values(5,50);eq_ref扫描的条件为对于前表的每一行(row)后表只有一行被扫描。再细化一点(1)join查询(2)命中主键(primary key)或者非空唯一(unique not null)索引(3)等值连接explain select * from user,user_ex where user.iduser_ex.id;如上例id是主键该join查询为eq_ref扫描。这类扫描的速度也异常之快。四、ref数据准备create table user (id int,name varchar(20) ,index(id))engineinnodb;insert into user values(1,shenjian);insert into user values(2,zhangsan);insert into user values(3,lisi);create table user_ex (id int,age int,index(id))engineinnodb;insert into user_ex values(1,18);insert into user_ex values(2,20);insert into user_ex values(3,30);insert into user_ex values(4,40);insert into user_ex values(5,50);如果把上例eq_ref案例中的主键索引改为普通非唯一(non unique)索引。explain select * from user,user_ex where user.iduser_ex.id;就由eq_ref降级为了ref此时对于前表的每一行(row)后表可能有多于一行的数据被扫描。explain select * from user where id1;当id改为普通非唯一索引后常量的连接查询也由const降级为了ref因为也可能有多于一行的数据被扫描。ref扫描可能出现在join里也可能出现在单表普通索引里每一次匹配可能有多行数据返回虽然它比eq_ref要慢但它仍然是一个很快的join类型。五、range数据准备create table user (id int primary key,name varchar(20))engineinnodb;insert into user values(1,shenjian);insert into user values(2,zhangsan);insert into user values(3,lisi);insert into user values(4,wangwu);insert into user values(5,zhaoliu);range扫描就比较好理解了它是索引上的范围查询它会在索引上扫码特定范围内的值。explain select * from user where id between 1 and 4;explain select * from user where idin(1,2,3);explain select * from user where id3;像上例中的betweenin都是典型的范围(range)查询。画外音必须是索引否则不能批量跳过。六、indexindex类型需要扫描索引上的全部数据。explain count (*) from user;如上例id是主键该count查询需要通过扫描索引上的全部数据来计数。画外音此表为InnoDB引擎。它仅比全表扫描快一点。七、ALL数据准备create table user (id int,name varchar(20))engineinnodb;insert into user values(1,shenjian);insert into user values(2,zhangsan);insert into user values(3,lisi);create table user_ex (id int,age int)engineinnodb;insert into user_ex values(1,18);insert into user_ex values(2,20);insert into user_ex values(3,30);insert into user_ex values(4,40);insert into user_ex values(5,50);explain select * from user,user_ex where user.iduser_ex.id;如果id上不建索引对于前表的每一行(row)后表都要被全表扫描。今天这篇文章中这个相同的join语句出现了三次(1)扫描类型为eq_ref此时id为主键(2)扫描类型为ref此时id为非唯一普通索引(3)扫描类型为ALL全表扫描此时id上无索引有此可见建立正确的索引对数据库性能的提升是多么重要。另外《类型转换带来的大坑》中也提到不正确的SQL语句可能导致全表扫描。全表扫描代价极大性能很低是应当极力避免的通过explain分析SQL语句非常有必要。总结(1)explain结果中的type字段表示(广义)连接类型它描述了找到所需数据使用的扫描方式(2)常见的扫描类型有systemconsteq_refrefrangeindexALL其扫描速度由快到慢(3)各类扫描类型的要点是system最快不进行磁盘IOconstPK或者unique上的等值查询eq_refPK或者unique上的join查询等值匹配对于前表的每一行(row)后表只有一行命中ref非唯一索引等值匹配可能有多行命中range索引上的范围扫描例如between/in/index索引上的全集扫描例如InnoDB的countALL最慢全表扫描(full table scan)(4)建立正确的索引(index)非常重要(5)使用explain了解并优化执行计划非常重要思路比结论重要希望大家有收获。画外音本文测试于MySQL5.6。架构师之路-分享技术思路相关推荐《缓冲池(buffer pool)这次彻底懂了》《写缓冲(change buffer)这次彻底懂了》《两类非常隐蔽的全表扫描 | 1分钟系列》《MyISAM与InnoDB的索引差异 | 1分钟系列》《数据库允许null悲剧的开始 | 1分钟系列》
