Explain如何助力SQL语句的优化及其分析方法

Explain如何助力SQL语句的优化

• • 何为Explain
  • 怎样运行Explain
  • 如何分析Explain的表格
  • 利用EXPLAIN对SQL优化

何为Explain

Explain是本菜几天前才接触到的内容，做一篇blog梳理梳理自己现在这几天所学的内容。
Explain本质上是一个数据库SQL语句的模拟器，他通过对SQL语句的模拟，返回一个SQL语句的性能分析表，根据这张表的内容，工程狮可以具体了解到为啥我的程序这么慢了。第一款支持 explain功能的是MySQL 5.5，但是初始级的explain只提供对于select语句的模拟，随后到MySQL 5.7，才逐渐支持对于后续update，delete和insert语句的模拟和分析。

怎样运行Explain

运行Explain 语句非常简单，一般是explain+SQL代码。

返回一张SQL语句的分析表。

如何分析Explain的表格

分析一张explain的表格就要知道其各个字段名的含义
explain表格分别有id, select_type, table, partitions, type, possible keys, key, key_len, ref, rows, filtered, extras 12个字段
±—±&＃8212;&＃8212;&＃8212;&＃8212;-±&＃8212;&＃8212;±&＃8212;&＃8212;&＃8212;&＃8211;±&＃8212;&＃8211;±&＃8212;&＃8212;&＃8212;&＃8212;&＃8211;±—±&＃8212;&＃8211;±&＃8212;&＃8212;&＃8212;-+
| id | select_type | table| partitions | type | possible_keys | key | key_len |
±—±&＃8212;&＃8212;&＃8212;&＃8212;-±&＃8212;&＃8212;±&＃8212;&＃8212;&＃8212;&＃8211;±&＃8212;&＃8211;±&＃8212;&＃8212;&＃8212;&＃8212;&＃8211;±—±&＃8212;&＃8211;±&＃8212;&＃8212;&＃8212;-+
±&＃8212;-±&＃8212;&＃8212;±&＃8212;&＃8212;&＃8211;±&＃8212;&＃8212;-+
|ref | rows | filtered | Extra |
±—±&＃8212;&＃8212;±&＃8212;&＃8212;&＃8211;±&＃8212;&＃8212;+

Id: 返回的是检索表格的序列号，每增加一次子查询，id值+1，数字越大越优先执行。

select_type 反应的是表格类型

同时select_type 还有insert，update和delete字段，分别表明explain分析的是这几种语句。

tables 表名。

Partitions 如果查询是基于分区表的，会在这里标明。

type 指的是表内的访问方式，换句话说是数据库怎样查询这张表的，是通过全表查询得到结果，还是通过索引查询得到部分结果。
type总共有14种之多，但是最常见的一共有几种，分别all,index,range,ref,eq_ref，const，排序是从劣到优进行排列

1. ALL/SYSTEM:
   是全表查询，在Innodb数据库引擎中用All表示，在其他数据库引擎中用SYSTEM来表示，全表查询是最暴力的查询方式，一般出现在select
   *的语句中，出现这种情况可以加索引进行查找。

2. INDEX:
   是按照索引来进行全表查询，只不过是按照索引的顺序来查找的，速度比前一种方式还要慢，因为数据库在扫描前表的时候需要读取索引，出现这种情况可以考虑根据需求重新设计SQL，固定到一段表格中进行查询然后返回需要的结果。

   
   这种情况会导致INDEX的出现，其中blog_name是索引。

3. RANGE: 从range开始，程序就可以使用此种性能的SQL了，range是指有范围的对索引项进行扫描，一般出现在索引加 ‘<’,’>’, in, or，between…and…等情况中，range虽然可以应用在SQL查询中，但仍然不是一个好的选择。

   
   表中的数据必须要有不符合SQL条件的其类型才会变为range，否则其类型为index，如果表中所有数据的blog_number小于2，类型为index，如果有数据大于或等于2，类型为range。

4. REF： 查找条件列为索引但是不是唯一索引或主键，如果数据中有重复的值，其类型为ref。
   REF和 RANGE的区别在于一个查找于区间范围，一个查找于固定的值。

5. REF_EQ：在ref_eq中查找列通过数据库得知返回的结果集只有一个值，则可达到REF_EQ的标准，也就是说当我们查询主键或唯一键的时候，SQL语句就达到了REF_EQ的标准。为了提升数据库性能，我们可以通过拆解字段名的方式，达到唯一键的标准，通过唯一键查询数据库，速度会大大加快。
   
   当blog_number为唯一键即达到此标准。

6. CONST：当主键作为where之后的条件查询的时候，mysql会通过优化器将其优化为在常量时间内完成的SQL，此时SQL为CONST。

possible_keys : 显示此表中的所有索引。

key: 实际使用到的索引，为空则表示没有索引。

key_len: 索引的长度，一般是要控制在20以内，长度为20字节的索引其区分度能够在90%以上，足够使用。

rows: 表示查询到结果集需要查询多少行元数据。

filter: 查找记录占总记录的百分比

利用EXPLAIN对SQL优化

我们可以通过type的定义对SQL进行优化，如果SQL为INDEX型，我们可以根据业务逻辑缩小查找范围进行优化; 如果其为range类型，尽量将索引的的范围查找变为等值查找，如果必须为范围查找，则可以将其设为主键，这样其SQL为CONST类型，非常快捷。

同时explain表返回的索引字段名长度可以帮助程序猿判断程序是否过长而影响性能。一般情况下索引长度为20的时候既可以区分90%的数据。