MySQL的索引详解

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_name, first_name, birthday)
) engine=innodb default charset=utf8;

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

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

复合索引对如下类型的查询有效：

全值匹配

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

SQL语句为：

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

。

匹配最左前缀

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

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

匹配列前缀

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

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

匹配范围值

比如范围匹配姓氏在Allen和Clinton之间的人。SQL语句为：

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

这里也只使用了索引的第一列。

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

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

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

只访问索引的查询

B-Tree 通常可以支持“只访问索引的查询”，即查询只需要访问索引，而无需访问数据行。这和“覆盖索引”的优化相关，后面再讲。

下面介绍一些复合索引会失效的情况：

（1）如果不是按照复合索引的最左列开始查找，则无法使用索引。例如：上面的例子中，索引无法用于查找查找名为Cuba的人，也无法查找某个特定出生日期的人，因为这两列都不是复合索引 key(last_name, first_name, birthday) 的最左数据列。类似地，也无法查找姓氏以某个字母结尾的人，即like范围查询的模糊匹配符%，如果放在第一位会使索引失效。

（2）如果查找时跳过了索引中的列，则只有前面的索引列会用到，后面的索引列会失效。比如查找姓Allen且出生日期在某个特定日期的人。这里查找时，由于没有指定查找名（first_name），故MySQL只能使用该复合索引的第一列（即last_name）。

（3）如果查询中有某个列的范围查询，则该列右边的所有列都无法使用索引优化查找。例如有查询条件为 where last_name='Allen' and first_name like ‘C%' and birthday='1992-10-25'，这个查询只能使用索引的前两列，因为这里的 like 是一个范围条件。假如，范围查询的列的值的数量有限，那么可以通过使用多个等于条件代替范围条件进行优化，来使右边的列也可以用到索引。

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

B-Tree索引是最常用的索引类型，后面，如果没有特别说明，都是指的B-Tree索引。

1、哈希索引

哈希索引（hash index）基于哈希表实现，只有精确匹配索引所有列的查询才有效。在MySQL中，只有Memory引擎显示支持哈希索引。

2、空间数据索引（R-Tree）

MyISAM引擎支持空间索引，可以用作地理数据存储。和B-Tree索引不同，该索引无须前缀查询。

3、全文索引

全文索引是一种特殊类型的索引，它查找的是文本中的关键词，而不是直接比较索引中的值。全文索引和其他几种索引的匹配方式完全不一样，它更类似于搜索引擎做的事情，而不是简单的where条件匹配。可以在相同的列上，同时创建全文索引和B-Tree索引，全文索引适用于 Match Against 操作，而不是普通的where条件操作。

索引可以包含一个列（即字段）或多个列的值。如果索引包含多个列，一般会将其称作复合索引，此时，列的顺序就十分重要，因为MySQL只能高效的使用索引的最左前缀列。创建一个包含两个列的索引，和创建两个只包含一列的索引是大不相同的。

1.5 索引的优点

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

最常见的B-Tree索引，按照顺序存储数据，所以，MySQL可以用来做Order By和Group By操作。因为数据是有序存储的，B-Tree也就会把相关的列值都存储在一起。最后，因为索引中也存储了实际的列值，所以某些查询只使用索引就能够获取到全部的数据，无需再回表查询。据此特性，总结出索引有如下三个优点：

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

此外，有人用“三星系统”（three-star system）来评价一个索引是否适合某个查询语句。三星系统主要是指：如果索引能够将相关的记录放到一起就获得一星；如果索引中的数据顺序和查找中的排列顺序一致就获得二星；如果索引中的列包含了查询需要的全部列就获得三星。

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

对于非常小的表，大部分情况下简单的全表扫描更高效，没有必要再建立索引。对于中到大型的表，索引带来的好处就非常明显了。

二. 高性能的索引策略

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

2.1 独立的列

我们通常会看到一些查询不当地使用索引，或者使得MySQL无法使用已有的索引。如果SQL查询语句中的列不是独立的，则MySQL就不会使用到索引。“独立的列”是指索引列不能是表达式的一部分，也不能是函数的参数。

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

select id,last_name,first_name,birthday from people where id+1=3;

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

select id,last_name,first_name,birthday from people where id=2;

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

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

2.2 前缀索引和索引的选择性

有时候，我们需要索引很长的字符列，这会让索引变得大且慢。通常的解决方法是，只索引列的前面几个字符，这样可以大大节约索引空间，从而提高索引的效率。但是，也会降低索引的选择性。索引的选择性是指，不重复的索引值的数目（也称为基数）与数据表中的记录总数的比值，取值范围是0到1。

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

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

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

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

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

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

2.3 多列索引

多列索引是指一个索引中包含多个列，必须要注意多个列的顺序。多列索引也叫复合索引，如前面的 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;

在MySQL5.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;

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

2.4 选择合适的索引列顺序

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

索引列的顺序意味着索引首先按照最左列进行排序，其次是第二列，第三列…。所以，索引可以按照升序或者降序进行扫描，以满足精确符合列顺序的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 等因素的影响，这些因素可能对查询的性能造成非常大的影响。

2.5 聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。具体的细节依赖于其实现方式，但InnoDB 的聚簇索引实际上在同一结构中保存了 B-Tree 索引和数据行。

当表中有聚簇索引时，它的数据行实际上存放在索引的叶子页（leaf page）中，也就是说，叶子页包含了行的全部数据，而节点页只包含了索引列的数据。

因为是存储引擎负责实现索引，因此并不是所有的存储引擎都支持聚簇索引。本节我们主要关注InnoDB，这里讨论的内容对于任何支持聚簇索引的存储引擎都是适用的。

InnoDB 通过主键聚集数据，如果没有定义主键，InnoDB 会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB 会隐式定义一个主键来作为聚簇索引。

聚簇索引的优点：

• 可以把相关的数据保存在一起。
• 数据访问更快。聚簇索引将索引和数据保存在同一个B-Tree中，因此，从聚簇索引中获取数据通常比非聚簇索引要快。
• 使用覆盖索引扫描的查询可以直接使用节点页中的主键值。

如果在设计表和查询时，能充分利用上面的优点，就可以极大地提升性能。

聚簇索引的缺点：

• 聚簇索引最大限度地提高了I/O密集型应用的性能，但如果数据全部放在内存中，则访问的顺序就没那么重要了，聚簇索引也就没什么优势了。
• 插入速度严重依赖于插入顺序。按照主键的顺序插入是插入数据到InnoDB表中速度最快的方式。但如果不是按照主键顺序插入数据，那么，在操作完毕后，最好使用 OPTIMIZE TABLE 命令重新组织一下表。
• 更新聚簇索引列的代价很高，因为会强制InnoDB将每个被更新的行移动到新的位置。
• 基于聚簇索引的表在插入新行，或者主键被更新，导致需要移动行的时候，可能面临“页分裂（page split）”的问题。页分裂会导致表占用更多的磁盘空间。

在InnoDB中，聚簇索引“就是”表，所以不像MyISAM那样需要独立的行存储。聚簇索引的每一个叶子节点都包含了主键值、事务ID、用于事务和MVCC（多版本控制）的回滚指针以及所有的剩余列。

InnoDB的二级索引（非聚簇索引）和聚簇索引差别很大，二级索引的叶子节点中存储的不是“行指针”，而是主键值。故通过二级索引查找数据时，会进行两次索引查找。存储引擎需要先查找二级索引的叶子节点来获得对应的主键值，然后根据这个主键值到聚簇索引中查找对应的数据行。

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

对于高并发工作负载，在InnoDB中按主键顺序插入可能会造成明显的主键值争用的问题。这个问题非常严重，可自行百度解决。

2.6 覆盖索引

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

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

如果一个索引包含（或者覆盖）了所有需要查询的字段（列）的值，我们称之为“覆盖索引”。

覆盖索引是非常有用的，能够极大地提高性能。考虑一下，如果查询只需要扫描索引，而无须回表获取数据行，会带来多少好处：

• 索引条目通常远小于数据行大小，所以如果只需要读取索引，那MySQL就会极大地减少数据访问量。覆盖索引对I/O密集型的应用也有帮助，因为索引比数据更小，更容易全部放入内存中。
• 因为索引是按照列值顺序存储的（至少在单个页内是这样），所以对于I/O密集型的范围查询比随机从磁盘读取每一行的数据I/O要少得多。
• 由于InnoDB的聚簇索引，覆盖索引对InnoDB表特别有用。InnoDB的二级索引（非聚簇索引）在叶子节点中保存了行的主键值，所以如果二级主键能够覆盖查询，则可以避免对主键索引的二次查询。

在所有这些场景中，在索引中就完成所有查询的成本一般比再回表查询小得多。

B-Tree索引可以成为覆盖索引，但哈希索引、空间索引和全文索引等均不支持覆盖索引。

当发起一个被索引覆盖的查询（也叫做索引覆盖查询）时，在 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)，这两个索引覆盖了四个字段的值。如果一个SQL查询语句，要查询的字段都在这四个字段之中，那么，这个查询就可以被称为索引覆盖查询。如果一个索引包含了某个SQL查询语句中所有要查询的字段的值，这个索引对于该查询语句来说，就是一个覆盖索引。例如，key(last_name, first_name, birthday) 对于 select last_name,first_name from people 就是覆盖索引。

2.7 使用索引扫描来做排序

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

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

1、索引排序

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

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

注意：就算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;

2、文件排序

文件排序（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可以使用同一个索引既满足查找，又满足查询。如果可能，设计索引时，应该尽可能地同时满足这两种操作。

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

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

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

alter table people add key(id,last_name);

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

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

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

下面列出几种不能使用索引排序的情况：

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

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

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

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

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

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

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

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

2.8 冗余和重复索引

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

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

冗余索引和重复索引不同。如果创建了索引 key(A, B)，再来创建索引 key(A)，就是冗余索引。因为索引(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工具来仔细检查计划中的索引变更。

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

2.9 未使用的索引

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