InnoDB的B+树索引

B+树索引其本质就是B+树在数据库中的实现，但是B+索引在数据库中有一个特点就是其高扇出性，因此在数据库中，B+树的高度一般都在2～3层，也就是对于查找某一键值的行记录，最多只需要2到3次IO，这倒不错。因为我们知道现在一般的磁盘每秒至少可以做100次IO，2～3次的IO意味着查询时间只需0.02～0.03秒。

数据库中的B+树索引可以分为聚集索引（clustered index）和辅助聚集索引（secondary index）辅助聚集索引有时也称非聚集索引（non-clustered index）。

但是不管是聚集还是非聚集的索引，其内部都是B+树的，即高度平衡的，叶节点存放着所有的数据。聚集索引与非聚集索引不同的是，叶节点存放的是否是一整行的信息。

聚集索引

InnoDB存储引擎表是索引组织表，即表中数据按照主键顺序存放。而聚集索引就是按照每张表的主键构造一颗B+树，并且叶节点中存放着整张表的行记录数据，因此也让聚集索引的叶节点成为数据页。聚集索引的这个特性决定了索引组织表中数据也是索引的一部分。同B+树数据结构一样，每个数据页都通过一个双向链表来进行链接。

由于实际的数据页只能按照一颗B+树进行排序，因此每张表只能拥有一个聚集索引。在许多情况下，查询优化器非常倾向于采用聚集索引，因为聚集索引能够让我们在索引的叶节点上直接找到数据。此外，由于定义了数据的逻辑顺序，聚集索引能够特别快地访问针对范围值的查询。查询优化器能够快速发现某一段范围的数据页需要扫描。

现在我们来看一张表，我们以人为的方式让其每个页只能存放两个行记录，如：

create table t(a int not null primary key,b varchar(8000));

insert into t select 1,repeat('a',7000);

insert into t select 2,repeat('a',7000);

insert into t select 3,repeat(a',7000);

insert into t select 4,repeat('a',7000);

可以看到，我们表的定义和插入方式使得目前每个页只能存放两个行记录，我们用py_innodb_page_info工具来分析表空间，可得：py_innodb_page_info.py-v mytest/t.ibd

page level为0000的即是数据页。我们要分析的是page level为0001的页，该页是B+树的根，我们来看看索引的根页中存放的数据。

我们直接通过页尾的Page Directory来分析，从00 63可以知道该页中行开始的位置。接着通过Recorder Header来分析，0xc063开始的值为69 6e 66 69 6d 75 6d 00，就代表infimum伪行记录。之前的5个字节01 00 02 00 1b就是Recorder Header，分析第4位到第8位的值1代表该行记录中只有一个记录（需要记住的是，InnoDB的Page Directory是稀疏的），即infimum记录本身。我们通过Recorder Header中最后的两个字节00 1b来判断下一条记录的位置，即c063+1b=c073，读取键值可得80 01，就是主键为1的键值（我们制定的INT是无符号的，因此二进制是0x8001，而不是0x0001），80 01后值00 00 00 04代表指向数据页的页号。以同样的方式，可以找到80 02和80 04这两个键值以及它们指向的数据页。

通过以上对于非数据页节点的分析，我们发现数据页上存放的是完整的行记录，而在非数据页的索引页中，存放的仅仅是键值以及指向数据页的偏移量，而不是一个完整的行记录。因此我们构造的这颗二叉树大致如图5-14所示。

 

许多数据库的文档会这样告诉读者：聚集索引按照顺序物理地存储数据。但是试想，如果聚集索引必须按照特定顺序存放物理记录的话，则维护成本即显得非常之高了。所以，聚集索引的存储并不是物理上的连续，相反是逻辑上连续的。这其中有两点：一是我们前面说过的页通过双向链表链接，页按照主键的顺序排列。另一点是每个页中的记录也是通过双向链表进行维护，物理存储上可以同样不按照主键存储。

聚集索引的另一个好处是，它对于主键的排序查找和范围查找速度非常快。叶节点的数据就是我们要查询的数据，如我们要查询一张注册用户的表，查询最后注册的10位用户，由于B+树索引是双向链表的，我们可以快速找到最后一个数据页，并取出10条记录，我们用Explain进行分析，可得：

explain select * from Profile order by id limit 10;

另一个是范围查询（range query），如果要查找主键某一范围内的数据，通过叶节点的上层中间节点就可以得到页的范围，之后直接读取数据页即可，又如：

explain select * from Profile where id＞10 and id＜10000;

Explain得到了MySQL的执行计划（execute plan），并且rows列给出了一个查询结果的预估返回行数。要注意的是，rows代表的是一个预估值，不是确切的值，如果我们实际进行这句SQL的查询，可以看到实际上只有9 946行记录：

select count(*) from Profile where id＞10 and id＜10000; 

辅助索引

对于辅助索引（也称非聚集索引），叶级别不包含行的全部数据。叶节点除了包含键值以外，每个叶级别中的索引行中还包含了一个书签（bookmark），该书签用来告诉InnoDB存储引擎，哪里可以找到与索引相对应的行数据。因为InnoDB存储引擎表是索引组织表，因此InnoDB存储引擎的辅助索引的书签就是相应行数据的聚集索引键。显示了InnoDB存储引擎中辅助索引与聚集索引的关系。

辅助索引的存在并不影响数据在聚集索引中的组织，因此每张表上可以有多个辅助索引。当通过辅助索引来寻找数据时，InnoDB存储引擎会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键，然后再通过主键索引来找到一个完整的行记录。举例来说，如果在一颗高度为3的辅助索引树中查找数据，那么需要对这颗辅助索引遍历3次找到指定主键；如果聚集索引树的高度同样为3，那么还需要对聚集索引进行3次查找，才能最终找到一个完整的行数据所在的页，因此一共需要6次逻辑IO来访问最终的一个数据页。

对于其他的一些数据库，如Microsoft SQL Server数据库，其表类型有一种不是索引组织表，称为堆表。在数据的存放按插入顺序方面，与MySQL的MyISAM存储引擎有些类似。堆表的特性决定了堆表上的索引都是非聚集的，但是堆表没有主键。因此这时书签是一个行标识符（row identifier，RID），可以用如“文件号：页号：槽号”的格式来定位实际的行。

堆表的非聚集索引既然不需要再通过主键对聚集索引进行查找，那不是速度会更快吗？答案是也许，在某些只读的情况下，书签为行标识符方式的非聚集索引可能会比书签为主键方式的非聚集索引快。但是考虑在OLTP（OnLine Transaction Processing，在线事务处理）应用的情况下，表可能还需要发生插入、更新、删除等DML操作。当进行这类操作时，书签为行标识符方式的非聚集索引可能需要不断更新行标识符所指向数据页的位置，这时的开销可能就会大于书签为主键方式的非聚集索引了。

Microsoft SQL Server数据库DBA问过这样的问题，为什么在SQL Server上还要使用索引组织表？堆表的书签性使得非聚集查找可以比主键书签方式更快，并且非聚集可能在一张表中存在多个，我们需要对多个非聚集索引的查找。而且对于非聚集索引的离散读取，索引组织表上的非聚集索引会比堆表上的聚集索引慢一些。当然，在一些情况下，使用堆表的确会比索引组织表更快，但是我觉得大部分是由于存在于OLAP（On-Line Analytical Processing，在线分析处理）的应用。其次就是前面提到的，表中数据是否需要更新，并且更新会否影响到物理地址的变更。此外另一个不能忽视的是对于排序和范围查找，索引组织表可以通过B+树的中间节点就找到要查找的所有页，然后进行读取，而堆表的特性决定了这对其是不能实现的。最后，非聚集索引的离散读，的确是存在上述情况，但是一般的数据库都通过实现预读（read ahead）技术来避免多次的离散读操作。因此，具体是建堆表还是索引组织表，这取决于你的应用，不存在哪个更优的情况。这和InnoDB存储引擎好还是MyISAM存储引擎好的问题是一样的，具体情况具体分析。

接下来，我们通过阅读表空间文件来分析InnoDB存储引擎的非聚集索引，我们还是分析上一小节所用的表t。

不同的是，在表t上再建立一个列c，并对列c创建非聚集索引： 

alter table t add c int not null;

update t set c=0-a;

alter table t add key idx_c(c); 

show index from t;

select a,c from t;

然后用py_innodb_page_info工具来分析表空间，可得：py_innodb_page_info.py-v t.ibd

对比前一次我们的分析，可以看到这次多了一个页。分析page offset为4的页，该页为非聚集索引所在页，通过工具hexdump分析可得：

因为只有4行数据，并且列c只有4个字节，因此在一个非聚集索引页中即可完成，整理分析可得下图所示的关系：

显示了表t中辅助索引idx_c和聚集索引的关系。可以看到辅助索引的叶节点中包含了列c的值和主键的值。这里键值因为我特意设为负值，你会发现-1以7f ff ff ff的方式进行内部存储。7（0111）最高位为0，代表负值，实际的值应该取反后，加1，即得-1。

B+树索引的管理

索引的创建和删除可以通过两种方法，一种是ALTER TABLE，另一种是CREATE/DROP INDEX。ALTER TABLE创建索引的语法为：

ALTER TABLE tbl_name

|ADD{INDEX|KEY}[index_name]

[index_type] (index_col_name，……) [index_option]……

ALTER TABLE tbl_name

DROP PRIMARY KEY

|DROP {INDEX|KEY} index_name

CREATE/DROP INDEX的语法同样很简单：

CREATE [UNIQUE] INDEX index_name

[index_type]

ON tbl_name（index_col_name，……）

DROP INDEX index_name ON tbl_name

索引可以索引整个列的数据，也可以只索引一个列的开头部分数据，如前面我们创建的表t，b列为varchar(8000)，但是我们可以只索引前100个字段，如：

alter table t add key idx_b (b(100));

目前MySQL数据库存在的一个普遍问题是，所有对于索引的添加或者删除操作，MySQL数据库是先创建一张新的临时表，然后把数据导入临时表，删除原表，再把临时表重名为原来的表名。因此对于一张大表，添加和删除索引需要很长的时间。对于从Microsoft SQL Server或Oracle数据库的DBA来说，MySQL数据库的索引维护始终让他们非常苦恼。

InnoDB存储引擎从版本InnoDB Plugin开始，支持一种称为快速索引创建方法。当然这种方法只限定于辅助索引，对于主键的创建和删除还是需要重建一张表。对于辅助索引的创建，InnoDB存储引擎会对表加上一个S锁。在创建的过程中，不需要重建表，因此速度极快。但是在创建的过程中，由于上了S锁，因此创建的过程中该表只能进行读操作。删除辅助索引操作就更简单了，只需在InnoDB存储引擎的内部视图更新下，将辅助索引的空间标记为可用，并删除MySQL内部视图上对于该表的索引定义即可。

查看表中索引的信息可以使用SHOW INDEX语句。如我们来分析表t，之前先加一个联合索引，可得：

alter table t add key idx_a_b(a,c);

show index from t;

因为在表t上有3个索引：一个主键索引，c列上的索引，和b列前100个字节构成的索引。

接着我们来具体讲解每个列的含义：

1. Table：索引所在的表名。
2. Non_unique：非唯一的索引，可以看到primary key是0，因为必须是唯一的。
3. Key_name：索引的名称，我们可以通过这个名称来DROP INDEX。
4. Seq_in_index：索引中该列的位置，如果看联合索引idx_a_b就比较直观了。
5. Column_name：索引的列
6. Collation：列以什么方式存储在索引中。可以是'A'或者NULL。B+树索引总是A，即排序的。如果使用了Heap存储引擎，并且建立了Hash索引，这里就会显示NULL了。因为Hash根据Hash桶来存放索引数据，而不是对数据进行排序。
7. Cardinality：非常关键的值，表示索引中唯一值的数目的估计值。Cardinality/表的行数应尽可能接近1，如果非常小，那么需要考虑是否还需要建这个索引。
8. Sub_part：是否是列的部分被索引。如果看idx_b这个索引，这里显示100，表示我们只索引b列的前100个字符。如果索引整个列，则该字段为NULL。
9. Packed：关键字如何被压缩。如果没有被压缩，则为NULL。
10. Null：是否索引的列含有NULL值。可以看到idx_b这里为Yes。因为我们定义的b列允许NULL值。
11. Index_type：索引的类型。InnoDB存储引擎只支持B+树索引，所以这里显示的都是BTREE。
12. Comment：注释。

Cardinality值非常关键，优化器会根据这个值来判断是否使用这个索引。但是这个值并不是实时更新的，并非每次索引的更新都会更新该值，因为这样代价太大了。因此这个值是不太准确的，只是一个大概的值。上面显示的结果主键的Cardinality为2，但是很显然我们表中有4条记录，这个值应该是4。如果需要更新索引Cardinality的信息，可以使用ANALYZE TABLE命令。如：

analyze table t;

show index from t;

这时的Cardinality的值就对了。不过，在每个系统上可能得到的结果不一样，因为ANALYZE TABLE现在还存在一些问题，可能会影响得到最后的结果。

另一个问题是MySQL数据库对于Cardinality计数的问题，在运行一段时间后，可能会看到下面的结果：

show index from Profile;

Cardinality为NULL，在某些情况下可能会发生索引建立了、但是没有用到，或者explain两条基本一样的语句，但是最终出来的结果不一样。一个使用索引，另外一个使用全表扫描，这时最好的解决办法就是做一次ANALYZE TABLE的操作。因此我建议在一个非高峰时间，对应用程序下的几张核心表做ANALYZE TABLE操作，这能使优化器和索引更好地为你工作。

---------------------------------------------------------------------------------------------------------------------------------------------------------

何时使用索引

并不是在所有的查询条件下出现的列都需要添加索引。对于什么时候添加B+树索引，我的经验是访问表中很少一部分行时，使用B+树索引才有意义。对于性别字段、地区字段、类型字段，它们可取值的范围很小，即低选择性。如：

SELECT * FROM student WHERE sex='M'

对于性别，可取值的范围只有'M'、'F'。对上述SQL语句得到的结果可能是该表50%的数据（我们假设男女比例1：1），这时添加B+树索引是完全没有必要的。相反，如果某个字段的取值范围很广，几乎没有重复，即高选择性，则此时使用B+树索引是最适合的，例如姓名字段，基本上在一个应用中都不允许重名的出现。

因此，当访问高选择性字段并从表中取出很少一部分行时，对这个字段添加B+树索引是非常有必要的。但是如果出现了访问字段是高选择性的，但是取出的行数据占表中大部分的数据时，这时MySQL数据库就不会使用B+树索引了，我们先来看一个例子：

show index from member;

表member大约有500万行数据。usernick字段上有一个唯一的索引。这时如果我们查找'David'这个用户时，得到执行计划如下：

explain select * from member where usernick='David';

可以看到使用了usernick这个索引，这也符合我们前面提到的高选择性、选取表中很少行的原则。但是如果执行下面这条语句：

explain select * from member where usernick＞'David';

可以看到possible_keys依然是usernick，但是实际优化器使用的索引key显示的是NULL。为什么？因为这不符合我们前面说的原则，虽然usernick这个字段的值是高选择性的，但是我们取出的行占了表中很大的一部分。

select @a:=count(id) from member where usernick＞'David';　　4544637

select @b:=count(id) from member;　　4827542

select @a/@b;　　0.9414

可以看到我们将取出表中94%的行，因此优化器没有使用索引。也许有人看到这里会问，谁会执行这句话啊？查找姓名大于David的字段，这种情况几乎不存在。的确如此，但是我们来考虑member表上的registdate字段（代表用户的注册时间），该字段是日期类型，字段上有一个regdate的非唯一索引。

我们来看下面两条语句的执行计划：

explain select * from member where registdate＜'2006-04-23';

explain select * from member where registdate＜'2006-04-24';

查找用户注册时间小于某个时间的SQL语句。出人意料的是，只是相差了1天，2条SQL语句的执行计划竟然不同。在执行第二条SQL语句的时候，虽然同样可以使用idx_regdate索引，但是优化器却没有使用该索引，而是对其全表进行扫描。MySQL数据库的优化器会通过EXPLAIN的rows字段预估查询可能得到的行，如果大于某一个值，则B+树会选择全表的扫表。至于这个值，根据我的经验（并没有在源代码中得到验证）一般在20%。即当取出的数据量超过表中数据的20%，优化器就不会使用索引，而是进行全表的扫表。

但是，预估的返回行数的值是不准确的，可以看到优化器判断注册日期小于2006-04-23的行为788 696，但实际得到的却是：

select count(id) from member where registdate＜'2006-04-23';　　523046

实际却只有523 046行，少了33%。这可能对于优化器的选择产生一定的后果，如果我们对比强制使用索引和使用优化器选择的全表扫描来查询注册日期小于2006-04-24的数据，最终会发现：

select id,userid,sex,registdate into outfile 'a'  from member force index(idx_regdate) where registdate＜'2006-04-24';

select id,userid,sex,registdate into outfile 'b' from member where registdate＜'2006-04-24';

第一句SQL语句我们强制使用idx_regdate索引，所用的时间为4.15秒，根据优化器选择的全表扫方式，执行第二句SQL语句却需要18.7秒。因此优化器的选择并不完全是正确的，有时你更应该相信自己的判断。

顺序读、随机读与预读取

任何时候Why都比What重要，索引使用的原则，即高选择、取出表中少部分的数据。但是为什么只能是少部分数据？

在知道为什么之前，了解两个概念——顺序读和随机读：

1. 顺序读（Sequntial Read）是指顺序地读取磁盘上的块（Block）。
2. 随机读（Random Read）是指访问的块不是连续的，需要磁盘的磁头不断移动。

当前传统机械磁盘的瓶颈之一就是随机读取的速度较低。

不管是否开启RAID卡的Write Back功能，磁盘的随机读性能都远远小于顺序读的性能。

在数据库中，顺序读是指根据索引的叶节点数据就能顺序地读取所需的行数据。这个顺序只是逻辑地顺序读，在物理磁盘上可能还是随机读取。但是相对来说，物理磁盘上的数据还是比较顺序的，因为是根据区来管理的，区是64个连续页。如根据主键进行读取，或许通过辅助索引的叶节点就能读取到数据。

随机读，一般是指访问辅助索引叶节点不能完全得到结果的，需要根据辅助索引叶节点中的主键去找实际行数据。因为一般来说，辅助索引和主键所在的数据段不同，因此访问是随机的方式。

SQL语句select id,userid,sex,registdate into outfile 'a' from member force index(idx_regdate) where registdate＜'2006-04-24'；

就是一句典型的随机读取。而正是因为读取的方式是随机的，并且随机读的性能会远低于顺序读，因此优化器才会选择全表的扫描方式，而不是去走idx_regdate这个辅助索引。

为了提高读取的性能，InnoDB存储引擎引入了预读取技术（read ahead或者prefetch）。预读取是指通过一次IO请求将多个页预读取到缓冲池中，并且估计预读取的多个页马上会被访问。传统的IO请求每次只读取1个页，在传统机械硬盘较低的IOPS下，预读技术可以大大提高读取的性能。

InnoDB存储引擎有两个预读方法，称为随机预读取（random read ahead）和线性预读取（linear read ahead）。

随机预读是指当一个区（64个连续页）中13个页也在缓冲区中，并在LRU列表的前端（即页是频繁地被访问），则InnoDB存储引擎会将这个区中剩余的所有页预读到缓冲区。

线性预读基于缓冲池中页的访问模式，而不是数量。如果一个区中的24个页都被顺序地访问了，则InnoDB存储引擎会读取下一个区的所有页。 

对比数据库TPC-C测试性能，发现TPC-C的结果禁用预读取的性能比启用预读取的性能提高了10%。InnoDB存储引擎官方也发现了这个问题，从InnoDB Plugin 1.0.4开始，随机访问的预读取被取消了，而线性的预读取还是保留了，并且加入了innodb_read_ahead_threshold参数。该参数表示一个区中的多少页被顺序访问时，InnoDB存储引擎才启用预读取，即预读下一个区的所有页。参数innodb_read_ahead_threshold的默认值为56，即当一个区中56个页都已被访问过并且访问模式是顺序的，则预读取下一个区的所有页。

show variables like 'innodb_read_ahead_threshold';

另一个问题是固态硬盘，固态硬盘的接口规范、定义、功能和使用等方面与传统机械硬盘相同，但是它们的内部构造完全不同，固态硬盘没有读写磁头，读取数据不需要围绕中心轴旋转，因此，它的随机读性能得到了质的飞跃。在使用固态硬盘的情况下，优化器的20%选择原理可能就不怎么准确了，我们应该更充分地利用固态硬盘的特性。当然，这不只是InnoDB存储引擎遇到的问题，对于其他数据库，目前都存在没有充分利用固态硬盘特性的情况。相信随着固态硬盘的普及，各数据库厂商会加快这一方面的优化。

辅助索引的优化使用

辅助索引的叶节点包含有主键，但是辅助索引的叶并不包含完整的行信息。因此，InnoDB存储引擎总是会先从辅助索引的叶节点判断是否能得到所需的数据。

让我们来看一个例子：

create table t(a int not null,b varchar(20),primary key(a),key(b));

insert into t select 1,'kangaroo';

insert into t select 2,'dolphin';

insert into t select 3,'dragon';

insert into t select 4,'antelope';

如果执行select * from t，估计很多人以为会得到如下的结果：

select * from t order by a;

***************************1.row***************************

a：1

b：kangaroo

***************************2.row***************************

a：2

b：dolphin

***************************3.row***************************

a：3

b：dragon

***************************4.row***************************

a：4

b：antelope

4 rows in set（0.01 sec）

但是实际执行的结果却是：select * from t;

***************************1.row***************************

a：4

b：antelope

***************************2.row***************************

a：2

b：dolphin

***************************3.row***************************

a：3

b：dragon

***************************4.row***************************

a：1

b：kangaroo

4 rows in set（0.00 sec）

因为辅助索引中包含了主键a的值，因此访问b列上的辅助索引就能得到a值，那这样就可以得到表中所有的数据。并且通常情况下，一个辅助索引页中能存放的数据比主键页上存放的数据多，因此优化器选择了辅助索引，如果我们解释这句SQL语句，可得到如下结果：

explain select * from t;

可以看到，优化器最终选择的索引是b，如果想得到对列a排序的结果，你还需对其进行ORDER BY操作，这样优化器会直接走主键，避免对a列的排序操作。

如：explain select * from t order by a;

select*from t order by a\G；

或者强制使用主键来得到结果：

select * from t force index(PRIMARY);

联合索引

联合索引是指对表上的多个列做索引。

联合索引的创建方法与之前介绍的一样，如：alter table t add key idx_a_b(a,b);

什么时候需要使用联合索引呢？在讨论这个之前，我们要来看一下联合索引内部的结果。从本质上来说，联合索引还是一颗B+树，不同的是联合索引的键值的数量不是1，而是大于等于2。讨论两个整型列组成的联合索引，假定两个键值的名称分别为a、b，可以看到多个键值的B+树情况，其实和我们之前讨论的单个键值没有什么不同，键值都是排序的，通过叶节点可以逻辑上顺序地读出所有数据，就上面的例子来说即：（1，1），（1，2），（2，1），（2，4），（3，1），（3，2）。数据按（a，b）的顺序进行了存放。

因此，对于查询SELECT * FROM TABLE WHERE a=xxx and b=xxx，显然是可以使用（a，b）的这个联合索引。对于单个的a列查询SELECT * FROM TABLE WHERE a=xxx也是可以使用这个（a，b）索引。但是对于b列的查询SELECT * FROM TABLE WHERE b=xxx，不可以使用这颗B+树索引。可以看到叶节点上的b值为1、2、1、4、1、2，显然不是排序的，因此对于b列的查询使用不到（a，b）的索引。

联合索引的第二个好处是，可以对第二个键值进行排序。例如，在很多情况下我们都需要查询某个用户的购物情况，并按照时间排序，取出最近三次的购买记录，这时使用联合索引可以避免多一次的排序操作，因为索引本身在叶节点已经排序了。

create table buy_log(userid int unsigned not null,buy_date date);

insert into buy_log values(1,'2009-01-01');

insert into buy_log values(2,'2009-01-01');

insert into buy_log values(3,'2009-01-01');

insert into buy_log values(1,'2009-02-01');

insert into buy_log values(3,'2009-02-01');

insert into buy_log values(1,'2009-03-01');

insert into buy_log values(1,'2009-04-01');

alter table buy_log add key(userid);

alter table buy_log add key(userid,buy_date);

我们建立了两个索引来进行比较。两个索引都包含了userid字段。如果只对于userid进行查询，优化器的选择是：

explain select * from buy_log where userid=2;

可以看到possible_keys这里有两个索引可以使用，分别是单个的userid索引和userid、buy_date的联合索引。但是优化器最终的选择是userid，因为该叶节点包含单个键值，因此一个页能存放的记录应该更多。

接着看以下的查询，我们假定要取出userid=1的最近3次购买记录，并分析使用单个索引和联合索引的区别：

explain select * from buy_log where userid=1 order by buy_date desc limit 3;

同样，对于上述的SQL语句都可以使用userid和userid，buy_date的索引。但是这次优化器使用了userid、buy_date的联合索引userid_2，因为在这个联合索引中buy_date已经排序好了。

如果我们强制使用userid的单个索引，会得到如下结果：

explain select * from buy_log force index(userid) where userid=1 order by buy_date desc limit 3;

在Extra这里，我们可以看到Using filesort，filesort是指排序，但是并不是在文件中完成。我们可以对比执行：

show status like 'sort_rows';

+-----------------+-------+

|Variable_name|Value

|Sort_rows|7

+-----------------+-------+

select * from buy_log force index(userid) where userid=1 order by buy_date desc limit 3;

show status like 'sort_rows';

+-----------------+-------+

|Variable_name|Value

|Sort_rows|10

+-----------------+-------+

可以看到增加了排序的操作，但是如果使用userid、buy_date的联合索引userid_2，就不会有这一次的额外操作了，如：

show status like 'sort_rows';

+-----------------+-------+

|Variable_name|Value

|Sort_rows|10

+-----------------+-------+

select * from buy_log where userid=1 order by buy_date desc limit 3;

show status like 'sort_rows';

+-----------------+-------+

|Variable_name|Value

|Sort_rows|10

+-----------------+-------+