乐趣区

关于索引:技术分享-EXPLAIN-执行计划详解2Extra

jiezi

作者：胡呈清

爱可生 DBA 团队成员，善于故障剖析、性能优化，集体博客：https://www.jianshu.com/u/a95…，欢送探讨。

本文起源：原创投稿
* 爱可生开源社区出品，原创内容未经受权不得随便应用，转载请分割小编并注明起源。

Extra

Extra 是 EXPLAIN 输入中另外一个很重要的列，该列显示 MySQL 在查问过程中的一些详细信息。

Using index

应用索引笼罩的状况下，执行打算的 extra 会显示为 “Using index”：

  • 查问的字段都蕴含在应用的索引中；
  • where 子句应用的字段也都蕴含在应用的索引中。

比方：

有组合索引：idx_a (first_name,last_name,birth_date)

mysql> explain select first_name,last_name,birth_date from employees where \
first_name='Mayuri' and last_name like 'Alpay' and birth_date > '1968-01-01'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employees
   partitions: NULL
         type: range
possible_keys: idx_a
          key: idx_a
      key_len: 127
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using where; Using index

Using index condition

查问数据时如果应用 index condition down 索引条件下推就会在执行打算的 extra 字段中呈现 “Using index condition”。

应用二级索引查找数据时，where 条件中属于索引一部分但无奈应用索引的条件（比方 like ‘%abc’ 左侧字符不确定），MySQL 也会把这部分判断条件下推到存储引擎层，筛选之后再进行回表，这样回表时须要查找的数据就更少。

索引条件下推的特点：

  • 下推的条件波及的字段肯定要是应用到的二级索引的一部分，因为二级索引索引存储了这些字段的值，能力进行筛选，所以叫做“索引条件下推”；
  • 大幅减小回表时的随机 I/O 开销。因为索引条件下推能够在查找完二级索引后利用条件筛选，减小后果集，减小接下来回表的次数，而回表做的是随机 I/O（开销大），所以可能节俭大量的 I/O 开销；
  • 大幅减小了存储引擎层到 MySQL 服务层的传输开销。条件下推给了存储引擎层，提前进行筛选，这样返回给 MySQL 服务层的数据就变少了；
  • 剩下的不能用到索引的 where 条件还是在 MySQL 服务层失效。

示例 1

有一个组合索引：idx_a (first_name,last_name,birth_date)

SQL:

mysql> explain select * from employees where first_name='Mayuri' and last_name like '%Alpay'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employees
   partitions: NULL
         type: ref
possible_keys: idx_a
          key: idx_a
      key_len: 58
          ref: const
         rows: 230
     filtered: 11.11
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)

查问的 where 子句中 first_name=’Mayuri’ 条件能够应用到二级索引 idx_a，而 last_name like ‘%Alpay’ 条件最左的字符不固定所以不能应用二级索引。索引下推的执行过程是：

  • 通过二级索引 idx_a 查找满足 first_name=’Mayuri’ 条件的记录；
  • MySQL 把条件 last_name like ‘%Alpay’ 下推到 InnoDB 层，对上一步的后果进行筛选（因为 last_name 是索引 idx_a 的一部分，所以能够间接筛选，否则不行）；
  • 取出上一步后果中的主键值，进行回表。

示例 2

有一个组合索引：idx_a (first_name,last_name,birth_date)

SQL: select * from employees where first_name > 'Mayuri' and last_name = 'Alpay';

在索引树上 first_name > ‘Mayuri’ 的数据行，对于 last_name 的值来说是无序的。所以搜寻索引树时，只能用 first_name > ‘Mayuri’ 来找出数据，而后再把 last_name = ‘Alpay’ 这个条件下推到 innodb 层筛选数据。

示例 3

有一个组合索引：idx_a (first_name,last_name,birth_date)

SQL 为：

mysql> explain select * from employees where  first_name='Mayuri' and last_name > 'Alpay'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employees
   partitions: NULL
         type: range
possible_keys: idx_a
          key: idx_a
      key_len: 124
          ref: NULL
         rows: 226
     filtered: 100.00
        Extra: Using index condition

尽管这里显示了“Using index condition”，但实际上是不须要做索引条件下推的，因为索引的第一个字段固定，对于 last_name 字段值来说也是有序的。这属于一个“bug”，顺便考古找到了丁奇老师回复的一条评论：

示例 4

如果是索引笼罩不须要回表，即便产生索引条件下推，也不会呈现 “Using index condition”：

mysql> explain select first_name,last_name,birth_date from employees where  
first_name > 'Mayuri' and last_name > 'Alpay'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employees
   partitions: NULL
         type: range
possible_keys: idx_a
          key: idx_a
      key_len: 124
          ref: NULL
         rows: 226
     filtered: 100.00
        Extra: Using where; Using index

小结

总的来说，只有是用到 index filter，就会产生索引条件下推。但不肯定呈现 Using index condition 就肯定产生了索引条件下推。对于 index filter 的概念，详见：SQL 中的 where 条件，在数据库中提取与利用浅析。

https://www.jianshu.com/p/89e…

Using where

就是后面说的 MySQL 服务层能够把属于索引的一部分但又无奈应用索引的条件下推到存储引擎层，而其余条件还是得在 MySQL 服务层利用来过滤存储引擎层返回的数据。当呈现这的状况，执行打算的 extra 字段就会呈现 “Using where”，它能够和 “Using index” 一起呈现，也能够和 “Using index condition” 一起呈现。

  • 全表扫描的时候，MySQL 服务层利用 where 条件过滤数据 
mysql> explain select emp_no,first_name,last_name from employees where hire_date = '1959-12-06'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employees
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 299454
     filtered: 10.00
        Extra: Using where
  • 应用索引拜访数据，然而 where 子句中有除了该索引蕴含的字段之外的条件时。
mysql> explain select emp_no,first_name,last_name from employees where first_name='Mayuri' and hire_date = '1959-12-06'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employees
   partitions: NULL
         type: ref
possible_keys: idx_a
          key: idx_a
      key_len: 58
          ref: const
         rows: 230
     filtered: 10.00
        Extra: Using where
  • 应用索引拜访数据，并达到索引笼罩，然而 where 子句中有属于索引一部分但无奈应用索引的条件（比方 like ‘%abc’ 左侧字符不确定）条件时：
mysql> explain select first_name,last_name,birth_date from employees where  first_name='Mayuri' and last_name like '%Alpay'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employees
   partitions: NULL
         type: ref
possible_keys: idx_a
          key: idx_a
      key_len: 58
          ref: const
         rows: 230
     filtered: 11.11
        Extra: Using where; Using index
  • 应用索引拜访数据，并且应用索引条件下推，并且 where 子句中有除了该索引蕴含的字段之外的条件时 
mysql> explain select * from employees where  first_name='Mayuri' and last_name like '%Alpay' and hire_date>'1969-01-01'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employees
   partitions: NULL
         type: ref
possible_keys: idx_a
          key: idx_a
      key_len: 58
          ref: const
         rows: 230
     filtered: 3.70
        Extra: Using index condition; Using where
退出移动版