联合索引是指对表上的多个列进行索引，其创建方法与单个索引一样，不同之处仅在于有多个索引列。例如，以下代码创建了一张t表，并且索引idx_a_b是联合索引，联合的列为（a,b）。
|
1 2 3 4 5 6 |
create table t( a int, b int, primary key(a), key idx_a_b(a,b) ) engine=innodb; |
那么何时需要使用联合索引呢？在讨论这个问题之前，先来看一下联合索引内部的结构。从本质上来说，联合索引也是一棵B+树，不同的是联合索引的键值的数量不是1，而是大于等于2。接着来讨论两个整型列组成的联合索引，假设两个键值的名称分别为a、b，如下图：
从上图可以观察到多个键值的B+树情况，其实和单个键值的B+树并没有什么不同，键值都是排序的，通过叶子节点可以逻辑上顺序地读出所有数据，就上面的例子来说，即（1,1）、（1,2）、（2,1）、（2,4）、（3,1）、（3,2）。其数据按(a,b)的顺序进行了存放，第一列肯定是升序排序的，第二列是根据第一列排序而排序的，跟order by a,b数据排序规则是相同的。如下示例：
|
1 2 3 4 5 6 7 8 9 10 11 12 |
mysql> select a,b from t1; +------+------+ | a | b | +------+------+ | 1 | 2 | | 3 | 3 | | 2 | 1 | | 2 | 2 | | 5 | 4 | | 1 | 4 | +------+------+ 6 rows in set (0.00 sec) |
如果给a,b添加一个组合索引，那么这个组合索引的数据分布规则其实就是select a,b from t1 order by a,b的结果，如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 |
mysql> select a,b from t1 order by a,b; +------+------+ | a | b | +------+------+ | 1 | 2 | | 1 | 4 | | 2 | 1 | | 2 | 2 | | 3 | 3 | | 5 | 4 | +------+------+ 6 rows in set (0.00 sec) |
第一列a肯定是排序好的（默认是升序），而第二个字段b其实就不是排序的了。但是如果a字段有相同的值时，那么对应的a字段的b字段就是排序的了。
当然，插入数据到联合索引时也是一样的道理（明白了这个，对理解gap锁会有帮助），比如我们现在对(a,b)插入(3,2)这样一条数据：
- 首先判断a字段，找到比3小的记录，然后插入到下一条记录的位置；
- 如果已经存在3这条记录，那么会接着比较b字段，如果比b字段大，就插入到下一条记录的位置，如果比b字段小，就插入到上一条记录的位置；
- 所以按照这个规则，(3,2)这条记录就会插入到(2,2)这条记录后面。
回到上图中，对于查询“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+树的特性就是数据是排序的才能使用二分查找法，因此对于b列的查询使用不到（a,b）的索引。
但是，对于“b=xxx”或者“b>xxx and b<xxx”的WHERE条件也不是说就100%无法使用（a,b）联合索引，有些情况是可以进行索引扫描，这主要看MySQL优化器Cost（成本）计算得出来的结果。如“SELECT * FROM TABLE WHERE b>xxx AND b<xxx”查询，如果优化器经过成本计算后觉得通过索引扫描比全表扫描快，那么就可能把整个索引扫描了一遍，然后筛选出来需要的行，再回表查找数据（所以这种查询有时候也可能是选择全表扫描）。在覆盖查询的场景下也是可以使用（a,b）联合索引的。比如“SELECT COUNT(*) FROM TABLE WHERE b>xxx AND b<xxx”时就可以用到（a,b）联合索引的b列，因为是要得到一个COUNT()结果，所以可以使用b列索引进行索引扫描。或者查询为“SELECT b FROM TABLE WHERE b>xxx AND b<xxx”时也属于覆盖场景，也可以使用b列进行索引扫描操作。
联合索引的第二个好处是已经对第二个键值进行了排序处理。例如，在很多情况下应用程序都需要查询某个用户的购物情况，并按照时间进行排序，最后取出最近三次的购买记录，这时使用联合索引可以避免多一次的排序操作，因为索引本身在叶子节点已经排序了。
来看一个例子，首先根据如下代码来创建测试表buy_log：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
create table buy_log ( userid int unsigned not null, buy_date date, key (userid), key (userid,buy_date) ) engine = innodb; insert into buy_log values(1,'2017-09-09'); insert into buy_log values(2,'2017-09-09'); insert into buy_log values(3,'2017-09-09'); insert into buy_log values(1,'2017-10-09'); insert into buy_log values(3,'2017-10-09'); insert into buy_log values(1,'2017-11-09'); insert into buy_log values(1,'2017-12-09'); |
以上创建了两个索引来进行比较，两个索引都包含了userid字段，如果只对于userid进行查询，如：
|
1 2 3 4 5 6 7 |
mysql> explain select * from buy_log where userid = '1'; +----+-------------+---------+------------+------+-----------------+----------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+------+-----------------+----------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | buy_log | NULL | ref | userid,userid_2 | userid_2 | 4 | const | 4 | 100.00 | Using index | +----+-------------+---------+------------+------+-----------------+----------+---------+-------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) |
从执行计划可以看出，possible_keys在这里有两个索引可供使用，分别是单个的userid索引和（userid,buy_date）的联合索引。但是优化器最终的选择是索引userid，因为该索引的叶子节点包含单个键值，索引理论上一个页能存放的记录应该更多。
接着假定要取出userid为1的最近3次的购买记录，其SQL语句如下，执行计划如下图：
|
1 2 3 4 5 6 7 |
mysql> explain select * from buy_log where userid=1 order by buy_date desc limit 3; +----+-------------+---------+------------+------+-----------------+----------+---------+-------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+------+-----------------+----------+---------+-------+------+----------+--------------------------+ | 1 | SIMPLE | buy_log | NULL | ref | userid,userid_2 | userid_2 | 4 | const | 4 | 100.00 | Using where; Using index | +----+-------------+---------+------------+------+-----------------+----------+---------+-------+------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec) |
同样，优化器使用了联合索引，因为在这个联合索引中buy_date已经排序好了。根据该联合索引取出数据，无须再对buy_date做一次额外的排序操作。若强制使用userid索引，在Extra则会看到Using filesort，即需要一次额外的排序操作才能完成查询。
正如前面介绍的那样，联合索引（a,b）其实是根据列a、b进行排序的，因此下列语句可以直接使用联合索引得到结果。
|
1 |
SELECT * FROM TABLE WHERE a=xxx ORDER BY b; |
然而对于联合索引（a,b,c）来说，下列语句同样可以直接通过联合索引得到结果：
|
1 2 |
SELECT * FROM TABLE WHERE a=xxx ORDER BY b; SELECT * FROM TABLE WHERE a=xxx AND b=xxx ORDER BY c; |
但是对于下面的语句，联合索引不能直接得到结果，其还需要执行一次filesort排序操作，因为索引（a,c）并未排序：
|
1 |
SELECT * FROM TABLE WHERE a=xxx ORDER BY c; |
另外对于下面的语句，联合索引也不能直接得到结果，其需要执行一次filesort排序操作：
|
1 |
SELECT * FROM TABLE WHERE a=xxx AND b>xxx ORDER BY c; |
跟上面的语句相比，不同之处就是b字段从等于变成了大于，就会导致ORDER BY C需要filesort排序操作。因为b=xxx时，c字段必然是排序的，但是b>xxx时，c字段就不是排序的了，所以需要filesort重新排序。联合索引（a,b,c）的索引排序规则为SELECT * FROM TABLE ORDER BY a,b,c。
另外对于buy_log表，如果有如下查询，统计userid等于1，时间大于’2017-10-09’的购买记录：
|
1 2 3 4 5 6 7 |
mysql> select count(*) from buy_log where userid = '1' and buy_date >= '2017-10-09'; +----------+ | count(*) | +----------+ | 3 | +----------+ 1 row in set (0.00 sec) |
该如何建立联合索引？对于这种联合索引有一个原则就是查询条件为 “=” 的排在组合索引的最左边，如果有多个条件为 “=”，那么选择性高的排在前面。所以这个联合索引应该创建成下面这样的：
|
1 |
mysql> alter table buy_log add index idx_userid_buydate(userid,buy_date); |
看一下执行计划是否走了这个联合索引：
|
1 2 3 4 5 6 7 |
mysql> explain select count(*) from buy_log where userid = '1' and buy_date >= '2017-10-09'; +----+-------------+---------+------------+-------+---------------------------+--------------------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+-------+---------------------------+--------------------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | buy_log | NULL | range | userid,idx_userid_buydate | idx_userid_buydate | 8 | NULL | 3 | 100.00 | Using where; Using index | +----+-------------+---------+------------+-------+---------------------------+--------------------+---------+------+------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec) |
下面简单描述一下为什么建立组合索引时有查询条件为“=”的排在组合索引的最左边，这跟索引原理有关系。如上组合索引建立应该是 (userid,buy_date)，而不应该是 (buy_date,userid)。因为如果把buy_date >= '2017-10-09'条件放在组合索引第一列，会扫描更多的数据。
我们知道索引是排序的，所以如果以userid = '1'为第一列，那么索引排列是如下这样的：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql> select userid,buy_date from buy_log order by userid,buy_date; +--------+------------+ | userid | buy_date | +--------+------------+ | 1 | 2017-09-09 | | 1 | 2017-10-09 | | 1 | 2017-11-09 | | 1 | 2017-12-09 | | 2 | 2017-09-09 | | 3 | 2017-09-09 | | 3 | 2017-10-09 | +--------+------------+ 7 rows in set (0.00 sec) |
数据库只需要扫描 3 行数据就可以返回结果了，因为数据库会使用二分查找法定位到第一条满足条件的数据页，也就是(1,2017-10-09)，然后往后扫描，直到不满足条件时即可。由于组合索引排序的特性，连数据过滤都不需要了。
如果以buy_date >= '2017-10-09'为第一列，那么索引排列是如下这样的：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql> select buy_date,userid from buy_log order by buy_date,userid; +------------+--------+ | buy_date | userid | +------------+--------+ | 2017-09-09 | 1 | | 2017-09-09 | 2 | | 2017-09-09 | 3 | | 2017-10-09 | 1 | | 2017-10-09 | 3 | | 2017-11-09 | 1 | | 2017-12-09 | 1 | +------------+--------+ 7 rows in set (0.00 sec) |
这个时候数据库就需要从第一个满足条件的行(2017-10-09,1)开始扫描，这里就需要扫描 4 行数据了，并且需要过滤掉不满足条件的数据。其开销比(userid,buy_date)组合索引大。这里案例是数据量小，如果是大数据量情况下，就可以发现其差距了。
如果查询条件由buy_date >= '2017-10-09'变成buy_date > '2017-10-09'，可以发现少了 = 号，这个时候对于联合索引 (buy_date, userid) 就更尴尬了，MySQL Server 在生成区间的时候只会使用 buy_date 字段，也就是说这个联合索引只能使用一个字段了。至于为啥，MySQL 如果用上 2 个字段形成扫描区间的话，就意味着要从大于 “2017-10-09” 的记录里找第一条第二个字段是 1 的记录，这个过程是不能用二分法的。
所以对于联合索引的创建基本都是“=”条件字段在前，其余条件再后。而对于都是“=”的条件字段，选择性越高的字段就在前面，选择性低的在后面，这样扫描的数据也会少些。
通过上面的说明，我们知道了只有多个条件为并集的情况下才能很好地使用组合索引。如果是 or 逻辑操作符，如下语句：
|
1 |
mysql> select * from buy_log where userid=1 or buy_date="2017-09-09"; |
这个查询是否能够使用到索引呢？这也同样要看MySQL得到的Cost值，有可能是进行索引扫描，然后刷选出需要的行，再回表。如果这种开销比直接全表扫描大，那么优化器就可能会直接全表扫描了。当然，还有MySQL中还有索引合并这个技术。
总结一下，组合索引创建遵循的基本规则：
- 多条件混合时，等值查询放在前面。
- 如果都是等值查询，选择性高的放在前面。
- 如果都是非等值查询，选择性高的放在前面。
