InnoDB 索引

本文如无特殊说明，所有内容均是针对 InnoDB 存储引擎而言。

首先，确保我们正在使用的 MySQL 引擎是 InnoDB（Support 行为 DEFAULT）：

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

本文样例均在以下 MySQL 版本中测试通过：

# mysql -V
mysql  Ver 14.14 Distrib 5.6.22, for Linux (x86_64) using  EditLine wrapper

索引

注意，索引在 MySQL 中也被称为键（key）。而且，InnoDB 中的主键、Unique 键底层都是使用索引来实现的。

索引

关于索引，MySQL 官方文档说的很清晰：

Indexes are used to find rows with specific column values quickly. Without an index, MySQL must begin with the first row and then read through the entire table to find the relevant rows. The larger the table, the more this costs. If the table has an index for the columns in question, MySQL can quickly determine the position to seek to in the middle of the data file without having to look at all the data. This is much faster than reading every row sequentially.

也就是说，索引（Index）是用来加快查找速度的。如果没有索引，那么就需要对整个表进行扫描。随着表大小的增大，所耗时间也会随着增大。而如果有索引，我们就可以很快就定位到所需数据所在的数据文件，再进一步查找就要快很多了。美团点评技术团队博客的一篇文章 MySQL 索引原理及慢查询优化的一个例子就解释的很通俗易懂：

可以类比字典，如果要查 “mysql” 这个单词，我们肯定需要定位到 m 字母，然后从下往下找到 y 字母，再找到剩下的 sql。如果没有索引，那么你可能需要把所有单词看一遍才能找到你想要的。

从这个例子，我们也可以看到，利用索引的查询其实是一步步缩小查询范围的；而且根据《MySQL 技术内幕：InnoDB 存储引擎（第 2 版）》一书，InnoDB 引擎的索引基于 B+ 树实现，

B+ 树索引并不能找到一个给定键值的具体行。B+ 树索引能找到的只是被查找数据行所在的页。然后数据库通过把页读入到内存，再在内存中进行查找，最后得到要查找的数据。

这跟字典查单词过程确实很像，我们先由开头的字母一步步缩小查询，最后定位到一页（或几页），再在这一页（几页）查找到我们想要的单词。

可见，索引是存储引擎用于快速找到数据的一种数据结构（B+ 树）。

不过，索引也不是什么时候都适用的。当我们需要获取的数据较多，同时利用索引会导致较多的磁盘 I/O，这样 MySQL 优化器并不会使用索引，而是批量从磁盘读取数据到内存，然后再处理。因为这时从磁盘顺序读的效率要比多次磁盘 I/O 效率高。可见官方文档解释：

Indexes are less important for queries on small tables, or big tables where report queries process most or all of the rows. When a query needs to access most of the rows, reading sequentially is faster than working through an index. Sequential reads minimize disk seeks, even if not all the rows are needed for the query.

B+ 树简介

B 树通常意味着所有的值都是按顺序存储的，并且每一个叶子页到根的距离相同。下图展示了 B-Tree 索引的抽象表示，大致反映了 InnoDB 索引是如何工作的：
图片来源：《高性能 MySQL（第 3 版）》

更多详细关于 B+ 树介绍请参考《MySQL 技术内幕：InnoDB 存储引擎（第 2 版）》第 5.3 节。

聚集索引和辅助索引

InnoDB 中的索引有两种——聚集索引（Clusted Index）和辅助索引（Secondary Index）。他们都是基于 B+ 树构建。

聚集索引

简介

每一个 InnoDB 表都有唯一一个特殊的索引——聚集索引。它是按照每张表的主键构造的一颗 B+ 树，叶子节点存放即位整张表的行记录（record）。

按 MySQL 官方文档，聚集索引的构造有三种方式：

• 如果表中定义了主键，那 InnoDB 引擎就会使用该主键为聚集索引。
• 如果表中没有定义主键，那 InnoDB 引擎就会将找到的第一个非 NULL 的 UNIQUE 索引（列）作为聚集索引。
• 如果表中没有定义主键也没有合适的 UNIQUE 索引，InnoDB 引擎会内部创建一个隐藏的聚集索引。

样例如下：

# 主键聚集索引
mysql> create table t1 (id int not null, name varchar(10), primary key (id));
Query OK, 0 rows affected (0.00 sec)
 
mysql> show index from t1\G;
*************************** 1. row ***************************
        Table: t1
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
1 row in set (0.00 sec)
 
# Unique 聚集索引
mysql> create table t2 (id int not null, name varchar(10), unique key (id));
Query OK, 0 rows affected (0.00 sec)
 
mysql> show index from t2\G;
*************************** 1. row ***************************
        Table: t2
   Non_unique: 0
     Key_name: id
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
1 row in set (0.00 sec)

# 隐藏的聚集索引
mysql> create table t3 (id int not null, name varchar(10));
Query OK, 0 rows affected (0.00 sec)

mysql> show index from t3\G;
Empty set (0.00 sec)

磁盘存储

对于聚集索引，《高性能 MySQL（第 3 版）》的说明最本质：

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。具体的细节依赖于某实现方式，但 InnoDB 的聚簇索引实际上是在同一个结构中保存了 B-Tree 索引和数据行。
当表有聚簇索引时，它的数据行实际上存放在索引的叶子叶（leaf page）中。术语“聚簇”表示数据行和相邻的键值紧凑的存储在一起。因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。

这也就是说，聚簇索引有如下两个优点：

可以把相关数据保存在一起。例如实现电子邮箱时，可以根据用户 ID 来聚集数据，这样只需要从磁盘读取少数的数据页就能获取某个用户的全部邮件。如果没有使用聚簇索引，则每封邮件获取都可能导致一次磁盘 I/O。
数据访问更快。聚簇索引将索引和数据保存在同一个 B-Tree 中，因此从聚簇索引中获取数据通常比在非聚簇索引中查找更快。

从以上描述，我们可以看到，聚集索引的目的之一就在于减少磁盘 I/O 来提升数据获取效率；另外，通过将索引和数据存在在一起来加快数据获取。

在《高性能 MySQL（第 3 版）》一书中，作者并没有说聚集索引在磁盘是否顺序存放，而是说“数据行和相邻的键值紧凑的存储在一起”。这跟我们平常看到的“聚集索引按照顺序物理地存储数据”不一样。哪一种更加准确，个人更加倾向于前者，因为我们可以在《MySQL 技术内幕：InnoDB 存储引擎（第 2 版）》一书找到证据：

如果聚集索引必须按照特定顺序存放物理记录，则维护成本显得非常之高。所以，聚集索引的存储并不是物理上连续的，而是逻辑上连续的。这其中有两点：一是前面说过的页通过双向链表链接，页按照主键的顺序排序；另一点是每个页中的记录也是通过双向链表进行维护的，物理存储上可以同样不按照主键存储。

数据结构

聚集索引的数据结构就是一个 B+ 树，只不过这个 B+ 树把键值和数据（记录）一起存放。如《高性能 MySQL（第 3 版）》书中所示：
图片来源：《高性能 MySQL（第 3 版）》

可以看出，聚集索引的每一个叶子节点都包含了主键值（col1）、事务 ID、用户事务的 MVCC 的回滚指针以及所有的剩余列（在这个例子中是 col2）。如果主键是一个列前缀索引，InnoDB 也会包含完整的主键列和剩下的其他列。

由此也可以看出，InnoDB 中，聚集索引“就是”表。

辅助索引

简介

除了聚集索引之外的索引都叫辅助索引（All indexes other than the clustered index are known as secondary indexes.）。所以辅助索引也称为非聚集索引。它的叶子节点并不存放行记录数据，而是聚集索引建。也就是说，要想获取行记录数据，利用辅助索引还需要聚集索引作为一个桥梁。那这是怎么做到的呢？看官方文档说明：

In InnoDB, each record in a secondary index contains the primary key columns for the row, as well as the columns specified for the secondary index. InnoDB uses this primary key value to search for the row in the clustered index.

也就是说，辅助索引其实不单单包括了自身所在行，还包括了主键所在的行！！！所以就是说，辅助索引（可能已经是一个联合索引）其实是和聚集索引一起构成了一个“联合索引”（称为索引扩展 Index Extensions）。这里官方文档就说的更加清楚了：

InnoDB automatically extends each secondary index by appending the primary key columns to it. Consider this table definition:

CREATE TABLE t1 (
  i1 INT NOT NULL DEFAULT 0,
  i2 INT NOT NULL DEFAULT 0,
  d DATE DEFAULT NULL,
  PRIMARY KEY (i1, i2),
  INDEX k_d (d)
) ENGINE = InnoDB;

This table defines the primary key on columns (i1, i2). It also defines a secondary index k_d on column (d), but internally InnoDB extends this index and treats it as columns (d, i1, i2).

也因此，如果主键的长度较大，这会使得辅助索引实际使用的空间很大，所以，要尽量选用长度较短的主键（官方文档）：

If the primary key is long, the secondary indexes use more space, so it is advantageous to have a short primary key.

样例如下：

mysql> CREATE TABLE t1 (
    ->   i1 INT NOT NULL DEFAULT 0,
    ->   i2 INT NOT NULL DEFAULT 0,
    ->   d DATE DEFAULT NULL,
    ->   PRIMARY KEY (i1, i2),
    ->   INDEX k_d (d)
    -> ) ENGINE = InnoDB;
Query OK, 0 rows affected (0.00 sec)
 
mysql> INSERT INTO t1 VALUES
    -> (1, 1, '1998-01-01'), (1, 2, '1999-01-01'),
    -> (1, 3, '2000-01-01'), (1, 4, '2001-01-01'),
    -> (1, 5, '2002-01-01'), (2, 1, '1998-01-01'),
    -> (2, 2, '1999-01-01'), (2, 3, '2000-01-01'),
    -> (2, 4, '2001-01-01'), (2, 5, '2002-01-01'),
    -> (3, 1, '1998-01-01'), (3, 2, '1999-01-01'),
    -> (3, 3, '2000-01-01'), (3, 4, '2001-01-01'),
    -> (3, 5, '2002-01-01'), (4, 1, '1998-01-01'),
    -> (4, 2, '1999-01-01'), (4, 3, '2000-01-01'),
    -> (4, 4, '2001-01-01'), (4, 5, '2002-01-01'),
    -> (5, 1, '1998-01-01'), (5, 2, '1999-01-01'),
    -> (5, 3, '2000-01-01'), (5, 4, '2001-01-01'),
    -> (5, 5, '2002-01-01');
Query OK, 25 rows affected (0.00 sec)
Records: 25  Duplicates: 0  Warnings: 0

# 关闭 index-extension
mysql> SET optimizer_switch = 'use_index_extensions=off';
Query OK, 0 rows affected (0.00 sec)

mysql> EXPLAIN SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: ref
possible_keys: PRIMARY,k_d
          key: k_d
      key_len: 4    # !!!
          ref: const
         rows: 5
        Extra: Using where; Using index
1 row in set (0.01 sec)
 
# 开启 index-extension
mysql> SET optimizer_switch = 'use_index_extensions=on';
Query OK, 0 rows affected (0.00 sec)

mysql> EXPLAIN SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: ref
possible_keys: PRIMARY,k_d
          key: k_d
      key_len: 8    # !!!
          ref: const,const
         rows: 1
        Extra: Using index
1 row in set (0.00 sec)

从上例可以看到，当 index-extension 关闭时，key_len 为 4 个字节；开启时，key_len 就变为 8 个字节，说明此时辅助索引扩展了主键索引，这可以优化查询效果。

数据结构

InnoDB 辅助索引的叶子节点中存储的不是“行指针”，而是主键值，并以此作为指向行的“指针”。如下图所示：
图片来源：《高性能 MySQL（第 3 版）》

下图是聚集索引和辅助索引的区别：
图片来源：《高性能 MySQL（第 3 版）》

前缀索引及索引选择性

前缀索引

前缀索引指的是我们只索引某列开始的部分字符，这样可以大大节约索引空间，从而提高索引效率。例如：

mysql> create table t1 (
    -> id int unsigned auto_increment,
    -> name varchar(20),
    -> phone varchar(20),
    -> email varchar(20),
    -> primary key (id),
    -> key i_name (name(2))
    -> );
Query OK, 0 rows affected (0.01 sec)
 
mysql> insert into t1 (name, phone, email) values 
    -> ('xhf', '13736474664', 'xhf@aa.com'),
    -> ('xhf', '13423474664', 'xhf1@aa.com'),
    -> ('xhf', '13364474664', 'xhf2@aa.com'),
    -> ('zsw', '13336474664', 'zsw@aa.com'),
    -> ('ehs', '13336472436', 'ehs@aa.com'),
    -> ('ehw', '13336472436', 'ehw@aa.com'),
    -> ('whw', '13373626442', 'whw@aa.com'),
    -> ('wje', '13333736264', 'wje@aa.com');
Query OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0
 
mysql> show index from t1\G;
...... 
*************************** 2. row ***************************
        Table: t1
   Non_unique: 1
     Key_name: i_name
 Seq_in_index: 1
  Column_name: name
    Collation: A
  Cardinality: 8
     Sub_part: 2
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
2 rows in set (0.00 sec)

其中，Sub_part 字段表示列 name 的前 2 个字符被索引。

索引选择性

根据《高性能 MySQL（第 3 版）》定义，索引选择性是是指：

不重复的索引值（也称为基数，cardinality）和数据表的记录总数（#T）的比值，范围从 1/#T 到 1 之间。索引的选择性越高则查询效率越高，因为选择性高可以让 MySQL 在查找时过滤掉更多的行。唯一索引的选择性是 1，这是最好的索引选择性，性能也是最好的。

基数 Cardinality 可以通过 show index from table 来获得，如上一小节所示。不过，需要特别注意的是，在 InnoDB 引擎，Cardinality 是一个预估值，并不是精确的。

另外，我们可以通过如下方法来计算索引选择性：

mysql> select count(distinct name)/count(*) from t1;
+-------------------------------+
| count(distinct name)/count(*) |
+-------------------------------+
|                        0.7500 |
+-------------------------------+
1 row in set (0.00 sec)

而对于前缀索引，我们可以通过如下方法来计算最适合前缀长度：

mysql> select count(distinct left(name, 1))/count(*) as sel1,
    -> count(distinct left(name, 2))/count(*) as sel2,
    -> count(distinct left(name, 3))/count(*) as sel3
    -> from t1;
+--------+--------+--------+
| sel1   | sel2   | sel3   |
+--------+--------+--------+
| 0.5000 | 0.6250 | 0.7500 |
+--------+--------+--------+
1 row in set (0.00 sec)

当然，即使某前缀长度的选择性最高，但也要考虑其可能带来的问题，如长度过长导致索引存储空间大。

其他类型索引

联合索引

联合索引在官方文档中称为多列索引（Multiple Column Indexes），也即其是由表中多列组成的索引。按照之前定义，联合索引也属于辅助索引。

既然已经有单列索引，但为什么还需要多列索引呢？Segmentfault 上的一个针对问题 MySQL 里创建‘联合索引’的意义？的回答说的挺易懂，特摘录如下：

索引列越多，通过索引筛选出的数据越少。有 1000W 条数据的表，有如下 sql:select * from table where a = 1 and b =2 and c = 3，假设假设每个条件可以筛选出 10% 的数据，如果只有单值索引，那么通过该索引能筛选出 1000W*10%=100w 条数据，然后再回表从 100w 条数据中找到符合 b=2 and c= 3 的数据，然后再排序，再分页；如果是复合索引，通过索引筛选出 1000w *10% *10% *10%=1w，然后再排序、分页，哪个更高效，一眼便知。

除了能够加快查询速度，联合索引的另一个好处是已经对第二个键值进行了排序。当我们对查询结果进行排序的时候就可以充分利用这个特性：

# 创建一个表
mysql> create table t1 (
    -> id int unsigned auto_increment,
    -> name varchar(20),
    -> phone varchar(20),
    -> email varchar(20),
    -> primary key (id),
    -> key i_name_phone (name, phone)
    -> );
 
# 测试数据
mysql> select * from t1;
+----+------+-------------+-------------+
| id | name | phone       | email       |
+----+------+-------------+-------------+
|  1 | xhf  | 13736474664 | xhf@aa.com  |
|  2 | zsw  | 13336474664 | zsw@aa.com  |
|  3 | ehs  | 13336472436 | ehs@aa.com  |
|  4 | whw  | 13373626442 | whw@aa.com  |
|  5 | wje  | 13333736264 | wje@aa.com  |
|  6 | xhf  | 13423474664 | xhf1@aa.com |
|  7 | xhf  | 13364474664 | xhf2@aa.com |
|  8 | xhf  | 13423474664 | xhf3@aa.com |
+----+------+-------------+-------------+
8 rows in set (0.00 sec)
 
# 测试
mysql> explain select name, phone from t1 where name='xhf' order by phone;
+----+-------------+-------+------+---------------+--------------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key          | key_len | ref   | rows | Extra                    |
+----+-------------+-------+------+---------------+--------------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | t1    | ref  | i_name_phone  | i_name_phone | 23      | const |    4 | Using where; Using index |
+----+-------------+-------+------+---------------+--------------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)
 
mysql> explain select name, phone from t1 where name='xhf' order by id;
+----+-------------+-------+------+---------------+--------------+---------+-------+------+------------------------------------------+
| id | select_type | table | type | possible_keys | key          | key_len | ref   | rows | Extra                                    |
+----+-------------+-------+------+---------------+--------------+---------+-------+------+------------------------------------------+
|  1 | SIMPLE      | t1    | ref  | i_name_phone  | i_name_phone | 23      | const |    4 | Using where; Using index; Using filesort |
+----+-------------+-------+------+---------------+--------------+---------+-------+------+------------------------------------------+
1 row in set (0.00 sec)
 
mysql> explain select name, phone from t1 where name='xhf' order by email;
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                       |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | t1    | ALL  | i_name_phone  | NULL | NULL    | NULL |    8 | Using where; Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+

从以上结果可以看出，当我们按 phone 对查询结果进行排序时，我们并不需要用到 filesort，也即，这时 MySQL 服务器层并不需要对存储引擎返回的结果进行排序；而另外两种情况，都需要在服务器层进行排序。

覆盖索引

对于覆盖索引（Covering Index），58 沈剑的一篇微信文章一分钟了解索引技巧写的通俗易懂：

被查询的列，数据能从索引中取得，而不用通过行定位符 row-locator 再到 row 上获取，即“被查询列要被所建的索引覆盖”，这能够加速查询速度。

举例，登录业务需求：select uid, login_time from t_user where login_name=? and passwd=?

可以建立(login_name, passwd, login_time)的联合索引，由于 login_time 已经建立在索引中了，被查询的 uid 和 login_time 就不用去 row 上获取数据了，从而加速查询。

对于该类型索引，更加严谨的说法来自《MySQL 技术内幕：InnoDB 存储引擎（第 2 版）》一书：

从辅助索引就可以得到查询的记录，而不需要查询聚集索引中的记录。使用覆盖索引的一个好处是辅助索引不包含整行记录的所有信息，故其大小要远小于聚集索引。

可以看出，覆盖索引也是联合索引的一种。

沿用上边例子，我们展示下覆盖索引用法及特性：

# 为了检验联合索引效果，我们先关闭 Index Condition Pushdown (ICP) 优化
mysql> set optimizer_switch = 'index_condition_pushdown=off';
Query OK, 0 rows affected (0.00 sec)
 
mysql> explain select id, name, phone from t1 where name='xhf';
+----+-------------+-------+------+---------------+--------------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key          | key_len | ref   | rows | Extra                    |
+----+-------------+-------+------+---------------+--------------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | t1    | ref  | i_name_phone  | i_name_phone | 23      | const |    4 | Using where; Using index |
+----+-------------+-------+------+---------------+--------------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)
 
mysql> explain select email from t1 where name='xhf' and phone='13736474664';
+----+-------------+-------+------+---------------+--------------+---------+-------------+------+-------------+
| id | select_type | table | type | possible_keys | key          | key_len | ref         | rows | Extra       |
+----+-------------+-------+------+---------------+--------------+---------+-------------+------+-------------+
|  1 | SIMPLE      | t1    | ref  | i_name_phone  | i_name_phone | 46      | const,const |    1 | Using where |
+----+-------------+-------+------+---------------+--------------+---------+-------------+------+-------------+
1 row in set (0.00 sec)
 
mysql> explain select * from t1 where name='xhf' and phone='13736474664';
+----+-------------+-------+------+---------------+--------------+---------+-------------+------+-------------+
| id | select_type | table | type | possible_keys | key          | key_len | ref         | rows | Extra       |
+----+-------------+-------+------+---------------+--------------+---------+-------------+------+-------------+
|  1 | SIMPLE      | t1    | ref  | i_name_phone  | i_name_phone | 46      | const,const |    1 | Using where |
+----+-------------+-------+------+---------------+--------------+---------+-------------+------+-------------+
1 row in set (0.00 sec)

因为 id、name 及 phone 默认组成一个联合索引，所以在当前 where 条件下他们的数据可以直接从该索引获得；而 email 及 * 并不能从该索引直接获取，而需要在 MySQL 服务器层进行过滤。

优化技术

Index Condition Pushdown (ICP)

延用我们在“联合索引”的例子，关闭和开启 Index Condition Pushdown (ICP) 时查询可能会得到不同的结果，例如：

mysql> set optimizer_switch = 'index_condition_pushdown=off';
Query OK, 0 rows affected (0.00 sec)
 
mysql> explain select email from t1 where name='xhf' and phone='13736474664';
+----+-------------+-------+------+---------------+--------------+---------+-------------+------+-------------+
| id | select_type | table | type | possible_keys | key          | key_len | ref         | rows | Extra       |
+----+-------------+-------+------+---------------+--------------+---------+-------------+------+-------------+
|  1 | SIMPLE      | t1    | ref  | i_name_phone  | i_name_phone | 46      | const,const |    1 | Using where |
+----+-------------+-------+------+---------------+--------------+---------+-------------+------+-------------+
1 row in set (0.00 sec)
 
mysql> set optimizer_switch = 'index_condition_pushdown=on';
Query OK, 0 rows affected (0.00 sec)
 
mysql> explain select email from t1 where name='xhf' and phone='13736474664';
+----+-------------+-------+------+---------------+--------------+---------+-------------+------+-----------------------+
| id | select_type | table | type | possible_keys | key          | key_len | ref         | rows | Extra                 |
+----+-------------+-------+------+---------------+--------------+---------+-------------+------+-----------------------+
|  1 | SIMPLE      | t1    | ref  | i_name_phone  | i_name_phone | 46      | const,const |    1 | Using index condition |
+----+-------------+-------+------+---------------+--------------+---------+-------------+------+-----------------------+
1 row in set (0.00 sec)

也即后一个查询使用了 ICP 技术。这样能够提高效率，因为该技术使得 where 部分的过滤操作放在了存储引擎层而不是服务器层。

Index Merge

有待添加

Multi-Range Read

有待添加

索引使用规范

• 全值匹配
• 匹配最左前缀
• 匹配列前缀
• 匹配范围值
• 精确匹配某一列并范围匹配另外一列
• 只访问索引的查询（覆盖索引）

表设计规范

• 使用递增主键，最好不要是那种没有规律类型，如 UUID
• 列默认值不设为 null，设定为 0 或 false
• 外键自动添加索引（InnoDB 默认行为）

附：Explain 工具

我们在前文多次用到了 Explain 来查看 MySQL 对某语句可能的执行计划，但并没有做过多解释。下边就让我们来看看常用的一些字段的以及以及它们可能会有的值。

注：这里主要总结或摘录自《高性能 MySQL（第 3 版）》附录，部分参考自：

• 官文文档
• EXPLAIN 执行计划中要重点关注哪些要素

字段详细意义

id 列

这一列总是包含一个编号，表示 select 所属的行。如果在语句当中没有子查询或联合，那么只会有唯一的 select，于是每一行在这个列中都将显示一个 1.否则，内层的 select 语句一般会顺序编号，对应于其在原始语句中的位置。

select_type 列

这一列显示了对英航是简单还是复杂 select（如果是后者，那么是三种复杂类型中的哪一种）。SIMPLE 值意味着查询不包括子查询和 UNION。如果查询有任何复杂的子部分，则最外层部分标记为 PRIMARY，其他部分标记为 SUBQUERY、DERIVED、UNION 或 UNION RESULT。

table 列

这一列显示了对应行正在访问哪个表。在通常情况下，它相当明了：它就是那个表，或是该表的别名（如果 SQL 中定义了别名）。

type 列

• ALL
  全表扫描，通常意味着 MySQL 必须扫描整张表，从头到尾，去找到需要的行。（这里也有个例外，例如在查询里使用了 LIMIT，或者在 Extra 列中显示“Using distinct/not exists”。）

• index
  跟全表扫描一样，只是 MySQL 扫描表时按索引次序进行而不是行。它的主要优点是避免了排序；最大的缺点是要承担按索引次序读取整个表的开销。这通常意味着若是按随机次序访问行，开销将会非常大。

如果在 Extra 列中看到“Using index”，说明 MySQL 正在使用覆盖索引，它只扫描索引的数据，而不是按索引次序的每一行。它比按索引次序全表扫描的开销要少很多。

注意：type 列里的 index 跟 Extra 列的 Using index 完全不是同一个意思。

• range

范围扫描就是一个有限制的索引扫描，它开始于索引里的某一点，返回匹配这个值域的行。这比全索引扫描好一些，因为它用不着遍历全部索引。显而易见的范围扫描是带有 BETWEEN 或在 WHERE 子句里带有 > 的查询。

当 MySQL 使用索引去查找一系列值时，例如 IN() 和 OR 列表，也会显示为范围扫描。然而，这两者其实是相当不同的访问类型，在性能上有重要的差异。更多信息可查看《高性能 MySQL（第 3 版）》第 5 章“什么事范围条件”。

此类扫描的开销跟索引类型相当。

• index_subquery
  子查询中可以用到索引。

• unique_subquery
  子查询中可以用到唯一索引，效率比 index_subquery 更高些。

• index_merge
  可以利用index merge特性用到多个索引，提高查询效率。

• fulltext
  全文检索。

• ref
  这是一种索引访问（有时也叫索引查找），它返回所有匹配某个单个值的行。然而，它可能会找到多个符合条件的行，因此，它是查找和扫描的混合体。此类索引访问只有当使用非唯一性索引或者唯一性索引的非唯一性前缀时才会发生。把它叫做 ref 是因为索引要跟某个参考值相比较。这个参考值或者是一个常数，或者是来自多表查询前一个表里的结果值。

ref_or_null 是 ref 之上的一个变体，它意味着 MySQL 必须在初次查找的结果里进行第二次查找以找出 NULL 条目。

• eq_ref
  使用这种索引查找，MySQL 知道最多只返回一条符合条件的记录。这种访问方法可以在 MySQL 使用主键或者唯一性索引查找时看到，它会将它们与某个参考值作比较。MySQL 对于这类访问类型的优化做的非常好，因为它知道无需估计匹配行的范围或在找到匹配行后再继续查找。

• const, system
  当 MySQL 能对查询的某部分进行优化并将其转换成一个常量时，它就会使用这些访问类型。举例来说，如果你通过将某一行的主键放入 WHERE 子句里的方式来选取此行的主键，MySQL 就能把这个查询转换为一个常量。然后就可以高效地将表从联接执行中移除。

• NULL
  这种访问方式意味着 MySQL 能在优化阶段分解查询语句，在执行阶段甚至用不着再访问表或者索引。例如，从一个索引列里选取最小值可以通过单独查找索引来完成，不需要在执行时访问表。

possible_keys 列

这一列显示了查询可以使用哪些索引，这是基于查询访问的列和使用的比较操作符来判断的。这个列是在优化过程的早期创建的，因为有些罗列出来的索引可能对于后续优化过程是没有的。

注意：这里的 key 表示索引。

key 列

这一列显示了 MySQL 决定采用哪个索引来优化对该表的访问。如果该索引没有出现在 possible_keys 列中，那么 MySQL 选用它是出于另外的原因——例如，它可能选择了一个覆盖索引，哪怕没有 WHERE 语句。

换句话说，possible_keys 解释了哪一个索引能有助于高效地行查找，而 key 显示的是优化采用哪一个索引可以最小化查询成本。

key_len 列

该列显示了 MySQL 在索引里使用的字节数。如果 MySQL 正在使用的只是索引里的某些列，那么就可以用这个值来算出具体是哪些列。

关于 key_len 详细计算可参考博文解读 EXPLAIN 执行计划中的 key_len。

ref 列

这一列显示了之前的表在 key 列记录的索引中查找值所用的列或常量。

rows 列

这一列是 MySQL 估计为了找到所需的行而要读取的行数。这个数字是内嵌循环关联计划里的循环数目。也就是说它不是 MySQL 认为它最终要从表里读取出来的行数，而是 MySQL 为了找到符合查询的每一点上标准的那些行而必须读取的行的平均数。（这个标准包括 SQL 里给定的条件，以及来自联接次序上前一个表的当前列。）

根据表的统计信息和索引的选用情况，这个估算可能很不精确。

filtered 列

这一列是在 MySQL 5.1 里新加进去的，在使用 EXPLAIN EXTENDED 时出现。它显示的是针对表里符合某个条件（WHERE 子句或联接条件）的记录数的百分比所做的一个悲观估算。如果把 rows 列和这个百分比相乘，就能看到 MySQL 估算它将和查询计划里前一个表关联的行数。

Extra 列

这一列包含的是不适合在其他列显示的额外信息。MySQL 用户手册里记录了大多数可以在这里出现的值。其中重要的值如下：

• Using index
  表示使用覆盖索引。注意和 type 列的 index 值区分开来。

• Using where（服务器层面）
  意味着 MySQL 服务器将在存储引擎检索行后（而不是在存储引擎），再进行过滤。

• Using index condition（存储引擎层面）
  意味着使用 Index Condition Pushdown (ICP) 技术，使得 where 部分的过滤操作放在了存储引擎层而不是服务器层。

• Select tables optimized away
  使用某些聚合函数来访问存在索引的某个字段时，优化器会通过索引直接一次定位到所需要的数据行完成整个查询，例如 MIN()/MAX()，这种也是比较好的结果之一。

• Using temporary
  意味着 MySQL 在对查询结果排序时会使用一个临时表。

• Using filesort
  意味着 MySQL 会对结果使用一个外部索引排序，而不是按索引次序从表里读取行。MySQL 有两种文件排序算法。Explain 不会告诉我们 MySQL 将使用哪一种文件排序，也不会告诉我们排序会在内存里还是磁盘上完成。

• Range checked for each record (index map:N)
  意味着没有好用的索引，新的索引将在联接的每一行上重新估算。N 是 possible_keys 列中索引的位图，并且是冗余的。

• Impossible WHERE
  对Where子句判断的结果总是false而不能选择任何数据，例如 where 1=0，无需过多关注。

限制

Explain 得到的结果只是个近似结果。有时候它是一个很好的近似，但在其他时候，可能与真相相差甚远。以下是一些相关限制：

• Explain 根本不会告诉你触发器、存储过程或 UDF 会如何影响查询
• 它并不支持存储过程，尽管可以手动抽取查询并单独地对其进行 Explain 操作
• 它并不会告诉你 MySQL 在查询执行中所做的特定优化
• 它并不会显示关于查询执行计划的所有信息
• 它并不区分具有相同名字的食物。例如，它对内存排序和临时文件排序都适用“Using filesort”，并且对于磁盘上和内存中的临时表都显示“Using temporary”。
• 可能会误导。例如，它会对一个有着很小 LIMIT 的查询显示全索引扫描。（MySQL 5.1 的 Explain 关于检查的行数会显示更精确的信息，但早期版本并不考虑 LIMIT）