Explain用法笔记

Explain用法笔记

在官方文档中，对于explain的作用是这么解释的：

explain语句提供了MYSQL如何执行语句的信息。explain可以与select、delete、insert、replace与update语句共同使用。它返回

explain select count(*)
from equity_stock

对于显示的结果列进行解释

字段名称	作用
table	表名称
type	连接类型
key	使用到的索引名称
key_len	索引长度
ref	索引的哪一列被使用
rows	查询行数（估计值）
extra	如何解析额外查询信息

extra

• Distinct:一旦MYSQL找到了与行相联合匹配的行，就不再搜索了
• Not exists: MYSQL优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行，就不再搜索了
• Range checked for each Record（index map:#）:没有找到理想的索引，因此对于从前面表中来的每一个行组合，MYSQL检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一
• Using filesort: 看到这个的时候，查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行
• Using index: 列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候
• Using temporary 看到这个的时候，查询需要优化了。这里，MYSQL需要创建一个临时表来存储结果，这通常发生在对不同的列集进行ORDER BY上，而不是GROUP BY上
• Where used 使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行，并且连接类型ALL或index，这就会发生，或者是查询有问题不同连接类型的解释（按照效率高低的顺序排序）
• system 表只有一行：system表。这是const连接类型的特殊情况
• const:表中的一个记录的最大值能够匹配这个查询（索引可以是主键或惟一索引）。因为只有一行，这个值实际就是常数，因为MYSQL先读这个值然后把它当做常数来对待
• eq_ref:在连接中，MYSQL在查询时，从前面的表中，对每一个记录的联合都从表中读取一个记录，它在查询使用了索引为主键或惟一键的全部时使用
• ref:这个连接类型只有在查询使用了不是惟一或主键的键或者是这些类型的部分（比如，利用最左边前缀）时发生。对于之前的表的每一个行联合，全部记录都将从表中读出。这个类型严重依赖于根据索引匹配的记录多少—越少越好
• range:这个连接类型使用索引返回一个范围中的行，比如使用>或<查找东西时发生的情况
• index: 这个连接类型对前面的表中的每一个记录联合进行完全扫描（比ALL更好，因为索引一般小于表数据）
• ALL:这个连接类型对于前面的每一个记录联合进行完全扫描，这一般比较糟糕，应该尽量避免

type

MYSQL中type类型总共有14种，其效率排序分别为：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

而常用的有六种，分别是all,index,range,ref,eq_ref,const，其中all的效率最低而

• all：全表扫描，属于最暴力且效率最低的一种查找，常常存在于查找非索引列的情况，可以通过添加索引来避免。

• index：只是另外一种形式的全表扫描，不同的是使用到了索引，但是索引不包含所需要的全部数据，因此可能需要回表再次查询一次数据。有没有回表可以查看extra字段中是否有Uses index字段。

• const：整个表只有一条符合数据，如果将一个主键放置到where后面作为条件查询，mysql优化器就能把这次查询优化转化为一个常量。至于如何转化以及何时转化，这个取决于优化器。const查询非常快因为只需要一次查询即可停止，这种情况会出现在使用主键以及唯一索引作为查询条件的情况

  SELECT * FROM tbl_name WHERE primary_key=1;
  SELECT * FROM tbl_name
    WHERE primary_key_part1=1 AND primary_key_part2=2;
  

• range：range指的是有范围的索引扫描，相对于index的全索引扫描，它有范围限制，因此要优于index。关于range比较容易理解，需要记住的是出现了range，则一定是基于索引的。同时除了显而易见的between，and以及’>’,’<'外，in和or也是索引范围扫描

• ref：查找条件列使用了索引而且不为主键和unique，因此当找到第一条数据的时候还需要继续进行查询。或者是连接使用的key符合最左前缀原则。

  SELECT * FROM ref_table WHERE key_column=expr;
  
  SELECT * FROM ref_table,other_table
    WHERE ref_table.key_column=other_table.column;
  
  SELECT * FROM ref_table,other_table
    WHERE ref_table.key_column_part1=other_table.column
    AND ref_table.key_column_part2=1;
  

当使用最左匹配时，可以通过explain结果中的key_len来进行判断使用到的列

如果索引是NULL，则长度为NULL。如果不是NULL，则为使用的索引的长度。所以通过此字段就可推断出使用了那个索引

计算规则：

1.定长字段，int占用4个字节，date占用3个字节，char(n)占用n个字符。

2.变长字段varchar(n)，则占用n个字符+两个字节。

3.不同的字符集，一个字符占用的字节数是不同的。Latin1编码的，一个字符占用一个字节，gdk编码的，一个字符占用两个字节，utf-8编码的，一个字符占用三个字节。

4.对于所有的索引字段，如果设置为NULL，则还需要1个字节。

什么是最左匹配原则

原理：

在构建联合索引的时候，也是通过b+树来构建的。可以看到a的值是有序的，而单纯按照b来看是无序的。只有在先根据a查找的情况下对b进行排序，此时b才是有序的。

**最左匹配原则：**最左优先，以最左边的为起点任何连续的索引都能匹配上。同时遇到范围查询(>、<、between、like)就会停止匹配。

3.匹配列前缀

如果id是字符型，那么前缀匹配用的是索引，中坠和后缀用的是全表扫描。

select * from staffs where id like ‘A%’;//前缀都是排好序的，使用的都是联合索引

select * from staffs where id like ‘%A%’;//全表查询

select * from staffs where id like ‘%A’;//全表查询

example: