Explain

Explain 是个什么鬼

Mysql提供了一个命令expalin,它可以对select查询语句进行分析，并输出select执行的详细信息，帮助开发人员对sql性能进行分析和优化。

Expalin 输出格式

mysql> explain select * from user where id = 2\G*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: const
         rows: 1
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

各名词含义如下：

• id:查询的标识符，mysql会为每个查询自动分配一个唯一的标识符，数字越大越先执行 ，如果说数字一样大，那么就从上往下依次执行，id列为null的就表是这是一个结果集，不 需要使用它来进行查询

• select_type：查询类型

• table：查询中用到的表

• type:此列非常关键，是用来说明表与表之间是如何进行关联，有没有使用索引等。

• possible_keys：查询中可能使用到的索引

• key:查询中真正使用到的索引

• key_len:用于处理查询的索引长度，如果是单列索引，那就整个索引长度算进去，如果是多列索引，那么查询不一定都能使用到所有的列，具体使用到了多少个列的索引，这里就会计算进去，没有使用到的列，这里不会计算进去

• ref:显示使用哪个列或常数与key一起从表中选择行(如果是使用的常数等值查询，这里会显示const，如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段，如果是条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这里可能显示为func)

EXPLAIN SELECT * FROM test_data WHERE fl_code ='Oi4Nas10003';
           id: 1
  select_type: SIMPLE
        table: test_data         type: refpossible_keys: index_fl_code
          key: index_fl_code
      key_len: 38
          ref: const
         rows: 1
        Extra: USING WHERE; USING INDEX
    EXPLAIN SELECT * FROM test_data WHERE fl_code  IN ('21312','Oi4Nas10003')
           id: 1
  select_type: SIMPLE
        table: test_data         type: RANGEpossible_keys: index_fl_code
          key: index_fl_code
      key_len: 38
          ref: (NULL)
         rows: 2
        Extra: Using where; Using index

• rows :这里是执行计划中估算的扫描行数，不是精确值

• Extra:额外的信息

select_type

SIMPLE: 表示此查询不包含 UNION 查询或子查询PRIMARY:在嵌套的查询中是最外层的SELECT语句，在UNION查询中是最前面的SELECT语句UNION:表示此查询是 UNION 的第二或随后的查询

DEPENDENT UNION, UNION 中的第二个或后面的查询语句, 取决于外面的查询UNION RESULT, UNION 的结果

SUBQUERY: 子查询中的第一个 SELECTDEPENDENT SUBQUERY: 子查询中的第一个 SELECT, 取决于外面的查询. 即子查询依赖于外层查询的结果.

type

system: 表中只有一条数据. 这个类型是特殊的 const 类型.const: 针对主键或唯一索引的等值查询扫描, 最多只返回一行数据. const 查询速度非常快, 因为它仅仅读取一次即可.例如下面的这个查询, 它使用了主键索引, 因此 type 就是 const 类型的.
mysql> explain select * from user where id = 2\G*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user_info
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: const
         rows: 1
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

eq_ref: 此类型通常出现在多表的 join 查询, 表示对于前表的每一个结果,
都只能匹配到后表的一行结果. 并且查询的比较操作通常是 =, 查询效率较高. 

例如:
    mysql> EXPLAIN SELECT * FROM user_info, order_info WHERE user_info.id = order_info.user_id\G*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: order_info
         type: index
possible_keys: user_product_detail_index
          key: user_product_detail_index
      key_len: 314
          ref: NULL
         rows: 9
        Extra: Using where; Using index*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: user_info
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: test.order_info.user_id
         rows: 1
        Extra: NULL
2 rows in set, 1 warning (0.00 sec)


ref: 此类型通常出现在多表的 join 查询, 针对于非唯一或非主键索引, 或者是使用了 最左前缀 规则索引的查询. 

例如下面这个例子中, 就使用到了 ref 类型的查询:

mysql> EXPLAIN SELECT * FROM user_info, order_info WHERE user_info.id = order_info.user_id AND order_info.user_id = 5\G*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user_info
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: const
         rows: 1
        Extra: NULL*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: order_info
         type: ref
possible_keys: user_product_detail_index
          key: user_product_detail_index
      key_len: 9
          ref: const
         rows: 1
        Extra: Using index
2 rows in set, 1 warning (0.01 sec)

range: 表示使用索引范围查询, 通过索引字段范围获取表中部分数据记录. 这个类型通常出现在 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN() 操作中.
当 type 是 range 时, 那么 EXPLAIN 输出的 ref 字段为 NULL, 并且 key_len 字段是此次查询中使用到的索引的最长的那个.
    
mysql> EXPLAIN SELECT *->         FROM user_info
->         WHERE id BETWEEN 2 AND 8 \G*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user_info
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: NULL
         rows: 7
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

index: 表示全索引扫描(full index scan), 和 ALL 类型类似, 只不过 ALL 类型是全表扫描, 而 index 类型则仅仅扫描所有的索引, 而不扫描数据.
index 类型通常出现在: 所要查询的数据直接在索引树中就可以获取到, 而不需要扫描数据. 当是这种情况时, Extra 字段 会显示 Using index.

mysql> EXPLAIN SELECT name FROM  user_info \G*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user_info
         type: index
possible_keys: NULL
          key: name_index
      key_len: 152
          ref: NULL
         rows: 10
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

上面的例子中, 我们查询的 name 字段恰好是一个索引, 因此我们直接从索引中获取数据就可以满足查询的需求了, 而不需要查询表中的数据. 因此这样的情况下, type 的值是 index, 并且 Extra 的值是 Using index.

ALL: 表示全表扫描, 这个类型的查询是性能最差的查询之一. 
通常来说, 我们的查询不应该出现 ALL 类型的查询, 因为这样的查询在数据量大的情况下, 对数据库的性能是巨大的灾难.
如一个查询是 ALL 类型查询, 那么一般来说可以对相应的字段添加索引来避免.

type 类型的性能比较

ALL < index < range ~ index_merge < ref < eq_ref < const < system

key_len

表示查询优化器使用了索引的字节数. 这个字段可以评估组合索引是否完全被使用, 或只有最左部分字段被使用到.
key_len 的计算规则如下:

字符串char(n): n 字节长度

varchar(n): 如果是 utf8 编码, 则是 3 n + 2字节; 如果是 utf8mb4 编码, 则是 4 n + 2 字节.

数值类型:

    TINYINT: 1字节
    
    SMALLINT: 2字节
    
    MEDIUMINT: 3字节
    
    INT: 4字节
    
    BIGINT: 8字节

时间类型

    DATE: 3字节
    
    TIMESTAMP: 4字节
    
    DATETIME: 8字节

字段属性: NULL 属性 占用一个字节. 如果一个字段是 NOT NULL 的, 则没有此属性.