MySQL的索引详解

MySQL的索引详解
⽬录
⼀. 索引基础
1.1 简介
1.2 索引的⼯作原理
1.3 索引的类型
1.4 索引的⽅法
1.5 索引的优点
⼆. ⾼性能的索引策略
2.1 独⽴的列
2.2 前缀索引和索引的选择性
2.3 多列索引
2.4 选择合适的索引列顺序
2.5 聚簇索引
2.6 覆盖索引
2.7 使⽤索引扫描来做排序
2.8 冗余和重复索引
2.9 未使⽤的索引
⼀. 索引基础
1.1 简介
在MySQL中，索引（index）也叫做“键（key）”，它是存储引擎⽤于快速找到记录的⼀种数据结构。

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

索引优化应该是对查询性能优化最有效的⼿段，创建⼀个真正最优的索引经常需要重写SQL查询语句。

1.2 索引的⼯作原理
要理解MySQL中索引的⼯作原理，最简单的⽅法就是去看⼀看⼀本书的索引部分：⽐如你想在⼀本书中寻找某个主题，⼀般会先看书的索引⽬录，找到对应的章节、对应的页码后就可以快速找到你想看的内容。

在MySQL中，存储引擎⽤类似的⽅法使⽤索引，其先在索引中查找对应的值，然后根据匹配的索引记录找到对应的数据⾏，最后将数据结果集返回给客户端。

1.3 索引的类型
在MySQL中，通常我们所指的索引类型，有以下⼏种：
常规索引
常规索引，也叫普通索引（index或key），它可以常规地提⾼查询效率。

⼀张数据表中可以有多个常规索引。

常规索引是使⽤最普遍的索引类型，如果没有明确指明索引的类型，我们所说的索引都是指常规索引。

主键索引
主键索引（Primary Key），也简称主键。

它可以提⾼查询效率，并提供唯⼀性约束。

⼀张表中只能有⼀个主键。

被标志为⾃动增长的字段⼀定是主键，但主键不⼀定是⾃动增长。

⼀般把主键定义在⽆意义的字段上（如：编号），主键的数据类型最好是数值。

唯⼀索引
唯⼀索引（Unique Key），可以提⾼查询效率，并提供唯⼀性约束。

⼀张表中可以有多个唯⼀索引。

全⽂索引
全⽂索引（Full Text），可以提⾼全⽂搜索的查询效率，⼀般使⽤Sphinx替代。

但Sphinx不⽀持中⽂检索，Coreseek是⽀持中⽂的全⽂检索引擎，也称作具有中⽂分词功能的Sphinx。

实际项⽬中，我们⽤到的是Coreseek。

外键索引
外键索引（Foreign Key），简称外键，它可以提⾼查询效率，外键会⾃动和对应的其他表的主键关联。

外键的主要作⽤是保证记录的⼀致性和完整性。

注意：只有InnoDB存储引擎的表才⽀持外键。

外键字段如果没有指定索引名称，会⾃动⽣成。

如果要删除⽗表（如分类表）中的记录，必须先删除⼦表（带外键的表，如⽂章表）中的相应记录，否则会出错。

创建表的时候，可以给字段设置外键，如 foreign key(cate_id) references cms_cate(id)，由于外键的效率并不是很好，因此并不推荐使⽤外键，但我们要使⽤外键的思想来保证数据的⼀致性和完整性。

1.4 索引的⽅法
在MySQL中，索引是在存储引擎层实现的，⽽不是在服务器层。

MySQL⽀持的索引⽅法，也可以说成是索引的类型（这是⼴义层⾯上的），主要有以下⼏种：
B-Tree 索引
如果没有特别指明类型，那多半说的就是B-Tree 索引。

不同的存储引擎以不同的⽅式使⽤B-Tree索引，性能也各不相同。

例如：MyISAM使⽤前缀压缩技术使得索引更⼩，但InnoDB则按照原始的数据格式存储索引。

再如MyISAM通过数据的物理位置引⽤被索引的⾏，⽽InnoDB则根据主键引⽤被索引的⾏。

B-Tree 对索引列是顺序存储的，因此很适合查找范围数据。

它能够加快访问数据的速度，因为存储引擎不再需要进⾏全表扫描来获取需要的数据。

如果⼀个索引中包括多个字段（列）的值，那它就是⼀个复合索引。

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

如下：
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;
people表中也已经插⼊了如下⼀些数据：
id last_name first_name birthday gender
1Clinton Bill1970-01-013
2Allen Cuba1960-01-013
3Bush George1970-01-013
4Smith Kim1970-01-013
5Allen Cally1989-06-083
……………
我们创建了⼀个复合索引 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服务器中可能会有⼀些永远都不会⽤到的索引，这样的索引完全是累赘，建议考虑删除。

但要注意的是，唯⼀索引的唯⼀性约束功能，可能某个唯⼀索引⼀直没有被查询使⽤，却能⽤于避免产⽣重复的数据。