Py学习  »  DATABASE

mysql 中的explain关键字

LXH • 5 年前 • 402 次点击  
阅读 40

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);

复制代码

表中数据如下

id uname age
1 lxh 24
3 zhangsan 23
10 sdsx 12
11 x33 35

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;
复制代码

输出结果

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE stu NULL ALL PRIMARY NULL NULL NULL 1 100 NULL
1 SIMPLE sc NULL eq_ref uk_score_stuid uk_score_stuid 4 lxhtest.stu.id 1 100 NULL

const

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

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

执行结果如下

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE stu null const PRIMARY PRIMARY 4 const 1 100 null

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语句为啥没索引


今天看啥 - 高品质阅读平台
本文地址:http://www.jintiankansha.me/t/6v9I4AFd8k
Python社区是高质量的Python/Django开发社区
本文地址:http://www.python88.com/topic/25241
 
402 次点击