mysql 中的explain关键字

mysql优化在实际的开发中是很重要，有很多可以评估自己写的 sql 的质量与效率，mysql为我们提供了一个辅助武器explain，它向我们展示了 mysql 接收到一条sql语句的执行计划，根绝explain返回的结果可以知道sql写的怎样,

demo数据库

建表语句

CREATE TABLE test (
  id    INT(11) NOT NULL AUTO_INCREMENT,
  uname VARCHAR(255),
  age int,
  PRIMARY KEY (id)
);

alter table test add index uname_index(uname);

复制代码

表中数据如下

explain的关键字有很多，此处只讲解最关键的type，key，rows

type

类型，官方全程“join type”,意思是“连接类型”，注意这里不是字面意思量表之间的链接，确切说是数据库引擎查找表的一种方式，在《高性能mysql》一书中作者更是觉得称呼它为访问类型更贴切一些；

type的类型达到了14种之多，这里只记录和理解最重要且经常遇见的六种类型，它们分别是all,index,range,ref,eq_ref，const。从左到右，它们的效率依次是增强的

all

全表扫描，如果只是查找一个数据项的sql出现了all类型，代表sql处于一种最原声的状态，有很大的优化空间，就好比一万个人中找一个人，只能挨个找一遍 以test表为例

index

另一种方式的全表扫描，只不过是按照索引的顺序，

ref

查找条件列使用了索引而且不为主键和unique,意思就是虽然使用了索引，但该索引列的值并不唯一，有重复 比如，test表的索引是 uname

eq_ref

ref_eq 与 ref相比牛的地方是，它知道这种类型的查找结果集只有一个,使用了主键或者唯一性索引进行查找时， 下面这两张表一张学生表，一张成绩表，成绩表里的学生id，t_id,就是使用了主键

-- 建表语句
create table ref_stu2 (
  id    INT(11) NOT NULL AUTO_INCREMENT,
  uname VARCHAR(255),
  age   VARCHAR(255),
  PRIMARY KEY (id)
);

create table ref_score2 (
  id     INT(11) NOT NULL AUTO_INCREMENT,
  stu_id int(11) not null,
  score  int(11),
  PRIMARY KEY (id)
 
);

explain select * from ref_stu2 stu, ref_score2 sc where stu.id = sc.stu_id;
复制代码

输出结果

const

将一个主键放置到where后面作为条件查询，mysql优化器就能把这次查询优化转化为一个常量

explain select * from ref_stu2 stu where stu.id = 1;
复制代码

执行结果如下

key

查询使用到的索引，type类型为index_merge（查询使用了两个以上的索引）时，这里可能出现两个以上的索引，其他的select_type这里只会出现一个。

rows

这里是执行计划中估算的扫描行数，不是精确值,值越小，代表效率越高

索引覆盖

覆盖索引（covering index）指一个查询语句的执行只用从索引中就能够取得，不必从数据表中读取

参考 MySQL SQL 优化之覆盖索引

关于like关键字是否用到索引

总结：like关键字是否使用索引的前提是做前缀原则，即‘x’和‘x%’是使用索引的，他们的执行计划中type都是range，rows都是比表的行数少的，而其他两个type都是index，这种情况叫，全索引扫描，具体介绍如下， 如果模糊查询时，查询是否包含某个字符串，可以采用locate函数， select * from test where locate("x",uname); 查询test表中uname字段，含有字符串‘x’的数据。

mysql 全表扫描、全索引扫描、索引覆盖(覆盖索引)

full index scan：全索引扫描，查询时，遍历索引树来获取数据行。如果数据不是密集的会产生随机IO 在执行计划中是Type列，index

full table scan：通过读物理表获取数据，顺序读磁盘上的文件。这种情况会顺序读磁盘上的文件。 在执行计划中是Type列，all

covering index：覆盖索引，如果where条件的列和返回的数据在一个索引中，那么不需要回查表，那么就叫覆盖索引。 在执行计划中是extra那一列，using index

full index scan vs full table scan

全索引扫描一般情况下比全表扫描好，但一定不是绝对的

大多数数据是存在磁盘上的，读取磁盘的次数是影响效率的关键。 由于索引扫描后要利用索引中的指针去逐一访问记录，假设每个记录都使用索引访问，则读取磁盘的次数是查询包含的记录数T； 如果表扫描则读取磁盘的次数是存储记录的块数B； 如果T>B 的话索引就没有优势了，对于大多数数据库来说，这个比例是10%（oracle，postgresql等），最终执行的时候，先对结果数量估算，如果小于（T<B）这个比例用索引，大于的话即直接表扫描所以结果也就不一定咯。

引用网上的一个例子 已知如下信息： 假设一张表含有10万行数据--------100000行 我们要读取其中20%(2万)行数据----20000行 表中每行数据大小80字节----------80bytes 数据库中的数据块大小8K----------8000bytes 所以有以下结果： 每个数据块包含100行数据---------100行（数据块大小/每行数据的大小 8000/80 ） 这张表一共有1000个数据块--------1000块(数据总条数/每个块包含的数据个数 100000/100)

背后的故事：

通过索引读取20000行数据 = 约20000个table access by rowid = 需要处理20000个块来执行这个查询（通过索引去读数据，在索引中找到一个键值，然后这个键值对应的rowid去读表数据，rowid只对应一条记录，所以读一个块也只是为了找到对应rowid的那条记录，所以一次在一个块中只读一条记录） 而如果是全表扫描呢，这个表一共是1000块，也就1000次读取，采用后者明显效率高。

小计：最近在看mongo，发现原来mongo里也有这个关键字，哈哈，看来是通用的啊

参考链接： mysql中explain的type的解释 你的like语句为啥没索引