索引简介

在 MySQL 中，索引（index）也叫做键（key），它是存储引擎用于快速找到记录的一种数据结构。

索引对于良好的性能非常关键，尤其是当表中的数据量越来越大时，索引对性能的影响就愈发重要。

索引优化是对查询性能优化最有效的手段。

在 MySQL 中，通常我们所指的索引类型，有以下几种：

普通索引

普通索引（index 或 key）是指索引类型为 normal 的索引，或者创建索引时没有明确指定类型的索引。普通索引是使用最普遍的索引类型。

主键索引

主键索引（Primary Key），也简称主键。它可以提高查询效率，并提供唯一性约束。一张表中只能有一个主键。被标志为自动增长的字段一定是主键，但主键不一定是自动增长。一般把主键定义在无意义的字段上（如：编号），主键的数据类型最好是数值。

唯一索引

唯一索引（Unique Key），可以提高查询效率，并提供唯一性约束。一张表中可以有多个唯一索引。

全文索引

全文索引（Full Text），可以提高全文搜索的查询效率，一般使用 Sphinx 替代。但 Sphinx 不支持中文检索，Coreseek 是支持中文的全文检索引擎，也称作具有中文分词功能的 Sphinx。实际项目中，用的是 Coreseek。

外键索引

外键索引（Foreign Key），简称外键，它可以提高查询效率，外键会自动和对应的其他表的主键关联。外键的主要作用是保证记录的一致性和完整性。

注意：只有 InnoDB 存储引擎的表才支持外键。外键字段如果没有指定索引名称，会自动生成。如果要删除父表（如类目表）中的记录，必须先删除子表（带外键的表，如文章表）中的相应记录，否则会出错。 创建表的时候，可以给字段设置外键。由于外键的效率并不是很好，因此并不推荐使用外键，但我们要使用外键的思想来保证数据的一致性和完整性。

复合索引

在 MySQL 中，索引是在存储引擎层实现的，而不是在服务器层。如果没有特别指明类型，那多半说的就是 BTree 索引。不同的存储引擎以不同的方式使用 BTree 索引，性能也各不相同。例如：MyISAM 使用前缀压缩技术使得索引更小，但 InnoDB 则按照原始的数据格式存储索引。再如 MyISAM 通过数据的物理位置引用被索引的行，而 InnoDB 则根据主键聚集被索引的行。

BTree 对索引列是顺序存储的，因此很适合查找范围数据。它能够加快访问数据的速度，因为存储引擎不再需要进行全表扫描来获取需要的数据。

复合索引也称组合索引、多列索引，是指包含多个字段的索引。

复合索引对多个字段值进行排序的依据是创建索引时列的顺序。

create table people (id int unsigned not null auto_increment primary key comment '主键id',last_name varchar(20) not null default '' comment '姓',first_name varchar(20) not null default '' comment '名',birthday date not null default '1970-01-01' comment '出生日期',gender tinyint unsigned not null default 3 comment '性别：1男，2女，3未知',key last_first_bir(last_name, first_name, birthday)
) engine=innodb default charset=utf8;

插入一些测试数据：

INSERT people(id,last_name,first_name,birthday,gender) 
VALUES 
(0, 'Clinton', 'Bill', '1970-01-01', 3),
(0, 'Allen', 'Cuba', '1970-01-01', 3),
(0, 'Bush', 'George', '1970-01-01', 3),
(0, 'Smith', 'Kim', '1970-01-01', 3),
(0, 'Allen', 'Cally', '1989-06-08', 3);

我们创建了一个复合索引 key last_first_bir(last_name, first_name, birthday)，对于表中的每一行数据，该索引中都包含了姓、名和出生日期这三列的值。索引也是根据这个顺序来排序存储的，如果某两个人的姓和名都一样，就会根据他们的出生日期来对索引排序存储。

Btree 索引适用于全键值、键值范围或键前缀查找，其中键前缀查找只适用于根据最左前缀查找。

复合索引生效的几种方式

全值匹配

全值匹配指的是和索引中的所有列进行匹配。例如：查找姓 Allen、名 Cuba、出生日期为 1960-01-01 的人。

select id,last_name,first_name,birthday 
from people 
where last_name='Allen' and first_name='Cuba' and birthday='1970-01-01';

explain 分析的结果如下：

+----+-------------+--------+------------+------+----------------+----------------+---------+-------------------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys  | key            | key_len | ref               | rows | filtered | Extra       |
+----+-------------+--------+------------+------+----------------+----------------+---------+-------------------+------+----------+-------------+
|  1 | SIMPLE      | people | NULL       | ref  | last_first_bir | last_first_bir | 127     | const,const,const |    1 |   100.00 | Using index |
+----+-------------+--------+------------+------+----------------+----------------+---------+-------------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

说明： 改变 where 子句中的各个参数的顺序，对分析结果没有影响。

匹配复合索引的最左前缀

比如只使用索引的第一列，查找所有姓为 Allen 的人。

select id,last_name,first_name,birthday 
from people where last_name='Allen';

explain 分析结果如下：

mysql> explain select id,last_name,first_name,birthday-> from people where last_name='Allen';
+----+-------------+--------+------------+------+----------------+----------------+---------+-------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys  | key            | key_len | ref   | rows | filtered | Extra       |
+----+-------------+--------+------------+------+----------------+----------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | people | NULL       | ref  | last_first_bir | last_first_bir | 62      | const |    2 |   100.00 | Using index |
+----+-------------+--------+------------+------+----------------+----------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

匹配列前缀

比如只匹配索引的第一列的值的开头部分，查找所有姓氏以 A 开头的人。

select id,last_name,first_name,birthday 
from people where last_name like 'A%';

explain 分析结果如下：

+----+-------------+--------+------------+-------+----------------+----------------+---------+------+------+----------+--------------------------+
| id | select_type | table  | partitions | type  | possible_keys  | key            | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+--------+------------+-------+----------------+----------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | people | NULL       | range | last_first_bir | last_first_bir | 62      | NULL |    2 |   100.00 | Using where; Using index |
+----+-------------+--------+------------+-------+----------------+----------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

匹配范围值

比如范围匹配姓氏在 Allen 和 Clinton 之间的人。

select id,last_name,first_name,birthday 
from people where last_name BETWEEN 'Allen' And 'Clinton';

explain 分析结果如下：

+----+-------------+--------+------------+-------+----------------+----------------+---------+------+------+----------+--------------------------+
| id | select_type | table  | partitions | type  | possible_keys  | key            | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+--------+------------+-------+----------------+----------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | people | NULL       | range | last_first_bir | last_first_bir | 62      | NULL |    4 |   100.00 | Using where; Using index |
+----+-------------+--------+------------+-------+----------------+----------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

精确匹配第一列并范围匹配后面的列

比如查找姓 Allen，并且名字以字母 C 开头的人。即全匹配复合索引的第一列，范围匹配第二列。

select id,last_name,first_name,birthday 
from people where first_name like'C%' and last_name = 'Allen';

explain 分析结果如下：

+----+-------------+--------+------------+-------+----------------+----------------+---------+------+------+----------+--------------------------+
| id | select_type | table  | partitions | type  | possible_keys  | key            | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+--------+------------+-------+----------------+----------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | people | NULL       | range | last_first_bir | last_first_bir | 124     | NULL |    2 |   100.00 | Using where; Using index |
+----+-------------+--------+------------+-------+----------------+----------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

可以发现，复合索引遵循最左前缀匹配法则。

复合索引会失效的情况

（1）如果不是按照复合索引的最左列开始查找，就无法使用索引。

例如：对于上面的测试数据，索引无法用于查找查找 first_name 为 Cuba 的人，也无法查找某个特定出生日期的人，因为这两列都不是复合索引 key(last_name, first_name, birthday) 的最左数据列。

类似地，索引也无法查找 last_name 以某个字母结尾的人，即 如果将 % 放在 like 模糊匹配的字符串开头，就无法使用索引。

（2）如果查找时跳过了索引中的列，就只有前面的索引列会用到，后面的索引列会失效。

比如查找姓（last_name） Allen 且出生日期在某个特定日期的人。这里查找时，由于没有指定查找名（first_name），故 MySQL 只能使用该复合索引的第一列（即 last_name）。

（3）如果查询中有某个列的范围查询，则该列右边的索引列都会失效。

例如查询条件为 where last_name='Allen' and first_name like 'C%' and birthday='1970-01-01'，这个查询只能使用索引的前两列，因为这里的 like 是一个范围条件。

假如范围查询的列的值的数量有限，那么可以通过使用多个等于条件代替范围条件进行优化，使右边的列也可以用到索引。

（4）如果将索引作为表达式或函数的一部分，就无法使用索引。

select id,last_name from people where length(last_name)=4;

现在，我们知道了复合索引中列的顺序是多么的重要，这些限制都和索引列的顺序有关。在优化性能的时候，可能需要使用相同的列但顺序不同的索引来满足不同类型的查询需求，比如在一张表中，可能需要两个复合索引 key(last_name, first_name, birthday) 和 key(first_name, last_name, birthday) 。

Btree 索引是最常用的索引结构（索引方式），如果没有特别说明，都是指 Btree 索引。

复合索引中字段的顺序非常重要，因为 MySQL 只能高效的使用索引的最左前缀列。

创建一个包含两个列的复合索引，和创建两个只包含一列的索引是大不相同的。

索引的优点

索引可以让 MySQL 快速地查找到我们所需要的数据，但这并不是索引的唯一作用。

最常见的 Btree 索引，按照顺序存储数据。所以，MySQL可以用来做 Order By和 Group By 操作。因为数据是有序存储的，Btree 也就会把相关的列值都存储在一起。

最后，因为索引中也存储了实际的列值，所以某些查询只使用索引就能够获取到全部的数据，无需再回表查询。

据此特性，总结出索引有如下三个优点：

• 索引大大减少了 MySQL 服务器需要扫描的数据量。
• 索引可以帮助服务器避免文件排序和临时表。
• 索引可以将随机 I/O 变为顺序 I/O。

索引并不总是最好的工具，也不是说索引越多越好。总的来说，当索引帮助存储引擎快速找到记录带来的好处大于其带来的额外工作时，索引才是有用的。

高性能的索引策略

正确地创建和使用索引是实现高性能查询的基础。前面，已经介绍了各种类型的索引及其优缺点，现在来看看如何真正地发挥这些索引的优势。下面的几个小节将帮助大家理解如何高效地使用索引。

独立的列

我们经常会看到一些查询不恰当地使用索引，或者使得 MySQL 无法使用已有的索引。

如果 SQL 查询语句中的列不是独立的，则 MySQL 就不会使用到索引。

独立的列是指索引列不能是表达式的一部分，也不能是函数的参数。

例如：下面这条SQL查询语句，就无法使用主键索引 id：

select * from people where id+1=3;

很容易看出，上面的 where 表达式其实可以简写为 where id=2，但是 MySQL 无法自动解析这个表达式。我们应该养成简化 where 条件的习惯，始终将索引列单独放在比较运算符的一侧。故要想使用到主键索引，正确地写法为：

select * from people where id=2;

下面是另一个常见的错误写法：

select ... from ...  
where to_days(current_date()) - to_days(date_col) <= 10;

前缀索引和索引的选择性

有时候，我们需要索引很长的字符列，这会让索引变得大且慢。通常的解决方法是，只索引列的前面几个字符，这样可以大大节约索引空间，从而提高索引的效率。但是，也会降低索引的选择性。

索引的选择性是指不重复的索引值的数目（也称为基数）与表中的记录总数的比值，取值范围是 0 到 1。

唯一索引的选择性是 1，这是最好的索引选择性，性能也是最好的。

一般情况下，某个列前缀的选择性也是足够高的，足以满足查询性能。对于 Blob、Text 或很长的 Varchar 类型的列，必须使用前缀索引，即只对列的前面几个字符进行索引，因为 MySQL 不允许索引这些列的完整长度。

添加前缀索引的方法如下：

# 只索引 address 字段的前 8 个字符
alter table user add key(address(8)); 

前缀索引是一种能使索引更小、更快的有效办法，但缺点是，MySQL 无法使用前缀索引做 Order By 和 Group By 操作，也无法使用前缀索引做覆盖扫描。

有时，后缀索引（suffix index）也有用途，例如查找某个域名的所有电子邮件地址。但 MySQL 原生并不支持后缀索引，我们可以把字符串反转后存储，并基于此建立前缀索引，然后通过触发器来维护这种索引。

复合索引

复合索引（也称组合索引、多列索引）必须要注意多个列的顺序。

如前面的 key(last_name, first_name, birthday) 就是一个复合索引。

一个常见的错误就是，为每个列创建单独的索引，或者按照错误的顺序创建了复合索引。

先来看第一个问题，为每个列创建单独的索引，从 show create table 中，很容易看到这种情况：

create table t (c1 int,c2 int,c3 int,key(c1),key(c2),key(c3)
);

这种错误的索引策略，一般是由于人们听到一些专家诸如“把where条件里面的列都加上索引”这样模糊的建议导致的。

在多个列上创建独立的单列索引大部分情况下并不能提高 MySQL 的查询性能。在 MySQL 5.0 及以后的版本中，引入了一种叫索引合并（index merge）的策略，它在一定程度上可以使用表上的多个单列索引来定位指定的行。但效率还是比复合索引差很多。

例如：表 film_actor 在字段 film_id 和 actor_id 上各有一个单列索引，SQL 语句如下：

select film_id,actor_id 
from film_actor where actor_id=1 or film_id=1;

在 MySQL 5.0 以后的版本中，查询能够同时使用这两个单列索引进行扫描，并将结果进行合并。

这种算法有三个变种：or 条件的并集（union）、and 条件的交集（intersection）、组合前两种情况的并集和交集。

上面的查询就是使用了两个索引扫描的并集，通过 explain 分析的 Extra 列（Extra 的值中会出现 union 字样），可以看出这一点：

explain select film_id,actor_id 
from film_actor where actor_id=1 or film_id=1\G;

索引合并策略有时候是一种优化的结果，但实际上它更能说明表上的索引建得很糟：

当出现对多个索引做交集操作时（通常有多个 and 条件），通常意味着需要一个包含所有相关列的复合索引，而不是多个独立的单列索引。

当出现对多个索引做并集操作时（通常有多个 or 条件），通常需要消耗大量的 CPU 和内存资源在算法的缓存、排序和合并操作上。此时，可以将查询改写成两个查询 union 的方式。

select film_id,actor_id from film_actor where actor_id=1
union all
select film_id,actor_id from film_actor where film_id=1 and actor_id<>1;

当使用 UNION 时，MySQL 会把结果集中重复的记录删掉，而使用 UNION ALL ，MySQL 会把所有的记录返回，且效率高于 UNION。

如果在 explain 的结果中，发现了索引的并集，应该好好检查一下 SQL 查询语句和表的结构，看是不是已经是最优的了，能否将其拆分为多个查询 Union 的方式等等。

选择合适的索引列顺序

最容易引起困惑的就是复合索引中列的顺序。

在复合索引中，正确地列顺序依赖于使用该索引的查询，并且同时需要考虑如何更好地满足排序和分组的需要。

索引列的顺序意味着索引首先按照最左列进行排序，其次是第二列，第三列…。所以，索引可以按照升序或者降序进行扫描，以满足符合列顺序的 order by、group by 和 distinct 等子句的查询需求。

当不需要考虑排序和分组时，将选择性最高的列放到复合索引的最左侧（最前列）通常是很好的。这时，索引的作用只是用于优化 where 条件的查找。但是，可能我们也需要根据那些使用频率最高的查询来调整索引列的顺序，让这种情况下索引的选择性最高。

以下面的查询为例：

select * from payment where staff_id=2 and customer_id=500;

是应该创建一个 key(staff_id, customer_id) 的索引还是 key(customer_id, staff_id) 的索引？

可以先执行一些查询来确定表中值的分布情况，并确定哪个列的选择性更高。比如：可以用下面的查询来预测一下：

select sum(staff_id=2), sum(customer_id=500) from payment\G

假如，结果显示：sum(staff_id=2)的值为 7000，而 sum(customer_id=500) 的值为 60。由此可知，在上面的查询中，customer_id 的选择性更高，应该将其放在索引的最前面，也就是使用key(customer_id, staff_id) 。

但是，这样做有一个地方需要注意，查询的结果非常依赖于选定的具体值。如果按照上述方法优化，可能对其他不同条件值的查询不公平，也可能导致服务器的整体性能变得更糟。

如果是从 pt-query-digest 这样的工具的报告中提取“最差查询”，再按上述办法选定的索引顺序往往是非常高效的。假如，没有类似地具体查询来运行，那么最好还是根据经验法则来做，因为经验法则考虑的是全局基数和选择性，而不是某个具体条件值的查询。

通过经验法则，判断选择性的方法如下：

select count(distinct staff_id)/count(*) as staff_id_selectivity,
count(distinct customer_id)/count(*) as customer_id_selectivity,
from payment\G

假如，结果显示：staff_id_selectivity的值为0.001，而customer_id_selectivity的值为0.086。我们知道，值越大，选择性越高。故 customer_id 的选择性更高。因此，还是将其作为索引列的第一列：

alter table payment add key(customer_id, staff_id);

尽管，关于选择性和全局基数的经验法则值得去研究和分析，但一定别忘了 order by、group by 等因素的影响，这些因素可能对查询的性能造成非常大的影响。

聚簇索引

聚簇索引也称聚集索引，它并不是一种单独的索引类型，而是一种数据存储方式（或者说结构）。具体的细节依赖于其实现方式，InnoDB 的聚簇索引实际上在同一结构中保存了 索引列（主键索引）和数据行。

因为是存储引擎负责实现索引，因此并不是所有的存储引擎都支持聚簇索引。我们主要关注 InnoDB 的聚簇索引。

在 InnoDB 中，每个表必须有一个聚簇索引，默认主键索引就是聚簇索引。如果表中没有主键，InnoDB 会选择一个合适的唯一索引作为聚簇索引，如果也没有这样的索引，就隐式定义一个隐藏的列（row_id） 作为主键来聚集数据（聚簇索引）。

索引的 Btree 结构

Btree（binary search tree）即二叉查找树（实际上是 N 叉树）。索引采用有序存储的方式，这种有序的数据结构能够让查找数据、顺序访问、插入及删除的动作，都在对数（logN）时间内完成。

整个 Btree 结构由多个结点组成，每个结点对应于真实物理空间划分的固定大小的 page（页），Innodb 中默认是 16 KB。

索引（索引列的值）被有序存放在所有的叶子节点中，每个叶子节点就是一个 page，每个 page 中顺序存储了多个索引（同一种索引的多个值），相邻的索引之间有一个单向指针（地址映射关系），同一个节点内的多个索引值相当于存储在单向链表中。

整个 Btree 结构按照节点从下往上分为三类：

• 叶子节点（leaf page)：它是树的叶子，没有子节点。每个叶子节点存储了同一种索引的多个值 (0,1)。相邻的节点之间有一个双向指针（相当于双向链表），这样就可按顺序找到下一个（next）或上一个（prev）叶子节点。假设有四个叶子节点： (0,1)、(2,3)、(4,5)、(6,7)。
• 内部节点（non-leaf page）：如果一个节点既不是叶子，也不是根，那它就是内部节点。也称作非叶子节点。相邻的内部节点之间也有一个双向指针。内部节点会记录它的每一个子节点（叶子节点）存储的索引的最小值。假设内部节点有两个子节点 (0,1)、(2,3)。那么它存储的就是 (0,2)。
• 根节点（root page）：它是整个树的根，没有父节点。根节点也可以有多个，相邻的根节点之间也有一个双向指针。根节点会记录它的每一个子节点（内部节点）存储的索引的最小值。假设根节点有两个子节点 (0,2)、(4,6)。那么它存储的就是 (0,4)。

利用索引查找指定的值时，是从根节点开始查找的。

下图是一个简易的 Btree 结构。

聚簇索引和非聚簇索引的区别

对于索引的值和记录行这两种数据，聚簇索引和非聚簇索引的区别如下：

聚簇索引：

• 叶子节点：存储的是主键索引值本身和完整的数据记录行。
• 内部节点：只包含了索引列的值。
• 根节点：只包含了索引列的值。

非聚簇索引：

• 叶子节点：存储的是普通索引值本身和主键的值。
• 内部节点：只包含了索引列的值。
• 根节点：只包含了索引列的值。

非聚簇索引又称辅助索引、普通索引、二级索引。
辅助索引的叶子节点存储的是主键值。

通过辅助索引查找数据时，会进行两次查找。先查找辅助索引的叶子节点来获得对应的主键值，然后通过主键到聚簇索引中查找对应的数据行。这个过程被称为回表。

辅助索引（普通索引）的叶子节点中，不包含整个记录的完整信息，它只包含普通索引字段和主键。如果想要获取完整的记录行，还需要通过主键到聚簇索引中回表查询。

聚簇索引的优点

• 聚簇索引最大限度地提高了 I/O 密集型应用的性能。
• 因为聚簇索引把所有的数据（不仅仅是索引字段本身）都存储在了 Btree 结构中，故查询速度更快（无需回表）。

聚簇索引的缺点

• 插入速度严重依赖于插入顺序。按照主键的顺序插入是写数据速度最快的方式。但如果不是按照主键顺序插入数据，那么，在操作完毕后，最好使用 OPTIMIZE TABLE 命令重新组织一下表。
• 更新聚簇索引列的代价很高，因为会强制 InnoDB 将每个被更新的行移动到新的位置。
• 在插入新行，或者主键被更新，导致需要移动行的时候，可能面临页分裂（page split）的问题。页分裂会导致表占用更多的磁盘空间。

在 InnoDB 中，聚簇索引就相当于表，不仅仅是主键索引，整个表的数据都在聚簇索引中。聚簇索引的每一个叶子节点都包含了主键值、事务ID、用于事务和 MVCC（多版本控制）的回滚指针以及所有的剩余列。

为了保证数据行按顺序插入，最简单的方法是将主键定义为 auto_increment 自动增长。尽可能地按主键顺序插入数据，并且尽可能地使用单调增加的主键值来插入新行。

对于高并发的情况下，在 InnoDB 中按主键顺序插入可能会造成明显的主键值争用的问题。

覆盖索引

非聚簇索引中因为不包含完整的数据信息，查找完整的数据记录时需要回表，所以普通的索引查询操作实际上要做两次索引查询（第一次是查询普通索引获取主键，第二次是到聚簇索引中查询完整的记录行）。

因此，一般来说，普通索引查询的效率要低于主键索引实现的聚簇索引。

通常大家都会根据查询的 where 条件来创建合适的索引，但这只是索引优化的一个方面。设计优秀的索引，应该考虑整个查询，而不单单是 where 条件部分。

索引确实是一种查找数据的高效方式，也可以使用索引来直接获取列的数据，这样就不必再回表读取数据行。如果索引的叶子节点中已经包含了要查询的全部字段，那么，还有什么必要再回表查询呢？

如果一个索引包含了当前 SQL 要查询的所有字段，这个索引对于该 SQL 查询语句来说，就是覆盖索引。（无需回表）

如果一个 SQL 查询语句，要查询的字段都存在于索引列中，那么，这个查询就是索引覆盖查询。（无需回表）

覆盖索引是非常有用的，能够极大地提高性能，因为无需回表。

• 索引条目通常远小于数据行大小，所以如果只读取索引就可获取到想要查询的字段，那 MySQL 就会极大地减少数据访问量。覆盖索引对 I/O 密集型的应用也有帮助，因为索引比整个数据行更小，更容易全部放入内存中。
• 索引是按照索引的列值顺序存储的（至少在单个页内是这样），所以对于 I/O 密集型的范围查询比随机从磁盘读取每一行的数据 I/O 要少得多。
• 覆盖索引特别有用。如果普通索引能够覆盖查询，就不会再回表。

Btree 的普通索引可以成为覆盖索引，但哈希索引、空间索引和全文索引等均不支持覆盖索引。

对于索引覆盖查询，在 explain 的 Extra 列，可以看到 Using index 字样。如：

explain select id from people;
explain select last_name from people;
explain select id,first_name from people;
explain select last_name,first_name,birthday from people;
explain select last_name,first_name,birthday from people where last_name='Allen';

people 表包含一个主键（id）索引和一个多列的复合索引 key(last_name, first_name, birthday)，这两个索引覆盖了四个字段的值。

例如，key(last_name, first_name, birthday) 对于 select last_name,first_name from people 就是覆盖索引。

使用索引扫描来做排序

MySQL 有两种方式可以生成有序的结果集：通过排序操作（order by）和按索引顺序扫描的自动排序（即通过索引来排序）。其实，这两种排序操作是不冲突的，也就是说 order by 可以使用索引来排序。

确切地说，MySQL 的对结果集的排序方式有下面两种：

索引排序

索引排序是指使用索引中的字段值对结果集进行排序。如果 explain 出来的 type 参数的值为 index，就说明 MySQL 一定使用了索引排序。如：

explain select id from people;
explain select id,last_name from people order by id desc;
explain select first_name from people;
explain select last_name from people order by last_name;
explain select id,last_name from people order by first_name asc;

mysql> explain select id,last_name from people order by first_name asc;
+----+-------------+--------+------------+-------+---------------+----------------+---------+------+------+----------+-----------------------------+
| id | select_type | table  | partitions | type  | possible_keys | key            | key_len | ref  | rows | filtered | Extra                       |
+----+-------------+--------+------------+-------+---------------+----------------+---------+------+------+----------+-----------------------------+
|  1 | SIMPLE      | people | NULL       | index | NULL          | last_first_bir | 127     | NULL |    5 |   100.00 | Using index; Using filesort |
+----+-------------+--------+------------+-------+---------------+----------------+---------+------+------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)

如上所示，type 的值是 index，就说明了使用了索引排序。key 的值为 last_first_bir，说明使用的是复合索引。Extra 的值为 Using index; Using filesort，说明不仅使用了索引排序还使用了文件排序。之所以还会用文件排序，是因为 order by first_name asc 没有遵循复合索引的最左前缀匹配法则，导致索引失效。

也就是说，虽然用了索引排序，但索引排序无效，可理解为没有用到索引排序。

注意：就算 explain 出来的 type 的值不是 index，也有可能是索引排序。如：

explain select id from people where id >3;
explain select id,last_name 
from people where id >3 order by id desc;

mysql> explain select id,last_name from people where id >3 order by id desc;
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------+
| id | select_type | table  | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                            |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------+
|  1 | SIMPLE      | people | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    2 |   100.00 | Using where; Backward index scan |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------+
1 row in set, 1 warning (0.00 sec)

如上所示，type 的值为 range，表示用了索引范围查询。extra 的值为 Using where; Backward index scan ，其中 Backward index scan 表示反向索引扫描排序。

文件排序

文件排序（filesort）是指将查询出来的结果集通过额外的操作进行排序，然后返回给客户端。这种排序方式，没有使用到索引排序，效率较低。虽然文件排序，MySQL 将其称为 filesort，但并不一定使用磁盘文件。

如果 explain 出来的 Extra 参数的值包含 Using filesort 字符串，就说明是文件排序。此时，你就必须对索引或 SQL 查询语句进行优化了。如：

explain select id,last_name,first_name 
from people where id > 3 order by last_name;

mysql> explain select id,last_name,first_name from people where id > 3 order by last_name;
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------------+
| id | select_type | table  | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                       |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------------+
|  1 | SIMPLE      | people | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    2 |   100.00 | Using where; Using filesort |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)

MySQL 可以使用同一个索引既满足 where 子句查找，又满足 order by 子句排序。如果可能，设计索引时，应该尽可能地同时满足这两种操作。

只有当索引的列包含 where 条件的字段和 order by 的字段，且索引中列的顺序和 where + order by 中包含的所有字段的顺序一致（注意：order by 在 where 的后面）时，才有可能使用到索引排序。

现在，我们来优化上面的那条 SQL 语句，使其利用索引排序。

首先，添加一个多列索引。

alter table people add key id_last_name(id,last_name);

会发现，仅添加 key id_last_name(id,last_name)，还是没办法使用索引排序，这是因为，where + order by 语句也要满足索引的最左前缀要求，而 where id > 3 是一个范围条件，会导致后面的 order by last_name 无法使用索引 key id_last_name(id,last_name)。

其次，将 SQL 语句中的 order by last_name 改为 order by id,last_name。

mysql> explain select id,last_name,first_name from people where id > 3 order by id,last_name;
+----+-------------+--------+------------+-------+----------------------+---------+---------+------+------+----------+-----------------------------+
| id | select_type | table  | partitions | type  | possible_keys        | key     | key_len | ref  | rows | filtered | Extra                       |
+----+-------------+--------+------------+-------+----------------------+---------+---------+------+------+----------+-----------------------------+
|  1 | SIMPLE      | people | NULL       | range | PRIMARY,id_last_name | PRIMARY | 4       | NULL |    2 |   100.00 | Using where; Using filesort |
+----+-------------+--------+------------+-------+----------------------+---------+---------+------+------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)

事实证明还是无效，可能因为 id 是聚簇索引，比较特殊（有待考证…）。普通索引是可以利用这种改法，来实现索引排序。

注意：如果 SQL 查询语句是一个关联多张表的关联查询，则只有当 order by 排序的字段全部来自于第一张表时，才有可能使用到索引排序。

不能使用到索引排序的情况

如果 order by 根据多个字段排序，但多个字段的排序方向不一致，即有的字段是 asc（升序，默认是升序），有的字段是 desc（降序）。

mysql> explain select * from people where last_name='Allen' order by first_name asc, birthday desc;
+----+-------------+--------+------------+------+----------------+----------------+---------+-------+------+----------+---------------------------------------+
| id | select_type | table  | partitions | type | possible_keys  | key            | key_len | ref   | rows | filtered | Extra                                 |
+----+-------------+--------+------------+------+----------------+----------------+---------+-------+------+----------+---------------------------------------+
|  1 | SIMPLE      | people | NULL       | ref  | last_first_bir | last_first_bir | 62      | const |    2 |   100.00 | Using index condition; Using filesort |
+----+-------------+--------+------------+------+----------------+----------------+---------+-------+------+----------+---------------------------------------+
1 row in set, 1 warning (0.00 sec)

如果 order by 包含了一个不在索引列的字段。

mysql> explain select * from people where last_name='Allen' order by first_name, gender;
+----+-------------+--------+------------+------+----------------+----------------+---------+-------+------+----------+---------------------------------------+
| id | select_type | table  | partitions | type | possible_keys  | key            | key_len | ref   | rows | filtered | Extra                                 |
+----+-------------+--------+------------+------+----------------+----------------+---------+-------+------+----------+---------------------------------------+
|  1 | SIMPLE      | people | NULL       | ref  | last_first_bir | last_first_bir | 62      | const |    2 |   100.00 | Using index condition; Using filesort |
+----+-------------+--------+------------+------+----------------+----------------+---------+-------+------+----------+---------------------------------------+
1 row in set, 1 warning (0.00 sec)

如果索引列的第一列是一个范围查找条件。如：

mysql> explain select * from people where last_name like 'A%' order by first_name;
+----+-------------+--------+------------+-------+----------------+----------------+---------+------+------+----------+---------------------------------------+
| id | select_type | table  | partitions | type  | possible_keys  | key            | key_len | ref  | rows | filtered | Extra                                 |
+----+-------------+--------+------------+-------+----------------+----------------+---------+------+------+----------+---------------------------------------+
|  1 | SIMPLE      | people | NULL       | range | last_first_bir | last_first_bir | 62      | NULL |    2 |   100.00 | Using index condition; Using filesort |
+----+-------------+--------+------------+-------+----------------+----------------+---------+------+------+----------+---------------------------------------+
1 row in set, 1 warning (0.01 sec)

对于这种情况，可以将 SQL 语句优化为：

mysql> explain select * from people-> where last_name like 'A%' order by last_name,first_name;
+----+-------------+--------+------------+-------+----------------+----------------+---------+------+------+----------+-----------------------+
| id | select_type | table  | partitions | type  | possible_keys  | key            | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+--------+------------+-------+----------------+----------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | people | NULL       | range | last_first_bir | last_first_bir | 62      | NULL |    2 |   100.00 | Using index condition |
+----+-------------+--------+------------+-------+----------------+----------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

冗余和重复索引

MySQL 允许在相同的列上创建多个索引（只不过索引的名称不同），由于 MySQL 需要单独维护重复的索引，并且优化器在优化查询时也需要逐个地进行分析考虑，故重复的索引会影响性能。

重复索引是指在相同的列上按照相同的列顺序创建的类型相同的索引。应该避免创建重复索引，发现以后也应立即删除。

冗余索引和重复索引不同。如果创建了索引 key(A, B)，再来创建索引 key(A)，就是冗余索引。因为 key(A) 只是前一个索引的前缀索引。索引 (A, B) 也可以当做索引 (A) 来使用。但是，如果再创建索引 (B,A)，就不是冗余索引了。

冗余索引通常发生在为表添加新索引的时候。例如，有人可能会增加一个新的索引 (A, B)，而不是扩展已有的索引 (A)。还有一种情况是，将一个二级索引 (A) 扩展为 (A, ID)，其中 ID 是主键，对于 InnoDB 来说，二级索引中已经默认包含了主键列，所以这也是冗余的。

大多数情况下，都不需要冗余索引。应该尽量扩展已有的索引而不是创建新索引。但有时，出于性能方面的考虑，也需要冗余索引，因为扩展已有的索引会导致其变大，从而会影响其他使用该索引的查询语句的性能。

在扩展索引的时候，需要特别小心。因为二级索引的叶子节点包含了主键值，所以在列 (A) 上的索引就相当于在 (A, ID) 上的索引。如果有人用了像 where A=5 order by ID 这样的查询，索引 (A) 就非常有用。但是，如果你将索引 (A) 修改为索引 (A, B)，则实际上就变成了索引(A, B, ID)，那么，上面查询的 order by 语句就无法使用索引排序，而只能使用文件排序了。

推荐使用 Percona 工具箱中的 pt-upgrade 工具来仔细检查计划中的索引变更。

因此，只有当你对一个索引相关的所有查询都很清楚时，才去扩展原有的索引。否则，创建一个新的索引（让原有索引成为新索引的冗余索引）才是最保险的方法。

未使用的索引

MySQL 服务器中可能会有一些永远都不会用到的索引，这样的索引完全是累赘，建议考虑删除。但要注意的是，唯一索引的唯一性约束功能，可能某个唯一索引一直没有被查询使用，却能用于避免产生重复的数据。

总结

无论是单列索引还是复合索引都必须遵守最左前缀匹配法则。

where 条件中索引失效的情况

依然采用上面的测试数据进行说明，复合索引为 KEY last_first_bir (last_name,first_name,birthday)。

注意：调整 where 条件中多个字段的顺序，不会对索引是否失效造成影响 。真正有影响的是索引中各个列的顺序，它与 where 条件中字段的顺序无关，但与 where 用到了哪些字段以及如何用有关。

（1）如果将 % 放在 like 模糊匹配的字符串开头，就无法使用索引。

select id,last_name from people where last_name like '%cli%';

（2）如果不是按照复合索引的最左列开始查找，就无法使用索引。

select id,last_name from people where first_name= 'Bill';

（3）如果查找时跳过了索引中的列，就只有前面的索引列会用到，后面的索引列会失效。

select id,last_name from people where birthday='1970-01-01' AND last_name= 'Allen';

因为跳过了 first_name 列，所以 birthday 会失效，但 last_name 会生效。

（4）如果查询中有某个列的范围查询，则该列右边的索引列都会失效。

select id,last_name from people where first_name like 'C%' and last_name='Allen' and birthday='1970-01-01';

因为 first_name 用了范围查询，所以只有 birthday 会失效。

（5）如果将索引作为表达式或函数的一部分，就无法使用索引。

select * from people where id+1=3;select id,last_name from people where length(last_name)=4;

排序时索引失效的情况

使用 order by 排序时，如果索引失效，就会导致文件排序（filesort）。

where + order by 包含的所有字段（order by 在 where 的后面），必须遵守最左前缀匹配法则。

order by 中多个字段的顺序、排序方式，都会对索引是否失效有影响。

（1）如果 order by 根据多个字段排序，但多个字段的排序方向不一致，就会导致文件排序。

select * from people where last_name='Allen' order by first_name asc, birthday desc;

（2）如果 order by 包含了一个不在索引列的字段，就会导致文件排序。

select * from people where last_name='Allen' order by first_name, gender;

（3）如果索引列的第一列是一个范围查找条件，而 order by 中又没有包含该列时，就会导致文件排序。

select * from people where last_name like 'A%' order by first_name;

（4）如果 order by 中的字段没有遵循最左前缀匹配法则，就会导致文件排序。

select * from people where last_name like 'A%' order by first_name,last_name;select * from people order by first_name,last_name;

（5）如果 where + order by 的字段，没有遵循最左前缀匹配法则，就会导致文件排序。

select * from people where first_name='Bill' order by last_name;

（6）如果 order by 排序的字段不是来自同一张表，就会导致文件排序。