MySQL复合索引的深入探究

不念不忘少年蓝@ 2022-10-27 13:59 135阅读 0赞

复合索引(又称为联合索引)，是在多个列上创建的索引。创建复合索引最重要的是列顺序的选择，这关系到索引能否使用上，或者影响多少个谓词条件能使用上索引。复合索引的使用遵循最左匹配原则，只有索引左边的列匹配到，后面的列才能继续匹配。本文主要探究复合索引的创建顺序与使用情况。

（一）复合索引的概念  
在单个列上创建的索引我们称为单列索引，在2个以上的列上创建的索引称为复合索引。在单个列上创建索引相对简单，通常只需要考虑列的选择率即可，选择性越好，代表数据越分散，创建出来的索引性能也就更好。通常，某列选择率的计算公式为：  
selectivity = 施加谓词条件后返回的记录数 / 未施加谓词条件后返回的记录数  
可选择率的取值范围是(0,1\]，值越小，代表选择性越好。  
对于复合索引(又称为联合索引)，是在多个列上创建的索引。创建复合索引最重要的是列顺序的选择，这关系到索引能否使用上，或者影响多少个谓词条件能使用上索引。复合索引的使用遵循最左匹配原则，只有索引左边的列匹配到，后面的列才能继续匹配。

（二）什么情况下会使用复合索引的列  
复合索引遵循最左匹配原则，只有索引中最左列匹配到，下一列才有可能被匹配。如果左边列使用的是非等值查询，则索引右边的列将不会被查询使用，也不会被排序使用。

实验：哪些情况下会使用到复合索引

复合索引中的哪些字段被使用到了，是我们非常关心的问题。网络上一个经典的例子：

1  
2  
3  
4  
5  
6  
7  
8  
9  
10  
11  
12  
13  
14  
15  
\-- 创建测试表  
CREATE TABLE t1(  
c1 CHAR(1) not null,  
c2 CHAR(1) not null,  
c3 CHAR(1) not null,  
c4 CHAR(1) not null,  
c5 CHAR(1) not null  
)ENGINE innodb CHARSET UTF8;  
  
\-- 添加索引  
alter table t1 add index idx\_c1234(c1,c2,c3,c4);  
  
\--插入测试数据  
insert into t1 values('1','1','1','1','1'),('2','2','2','2','2'),  
('3','3','3','3','3'),('4','4','4','4','4'),('5','5','5','5','5');  
需要探索下面哪些查询语句使用到了索引idx\_c1234，以及使用到了索引的哪些字段？

(A) where c1=? and c2=? and c4>? and c3=?

(B) where c1=? and c2=? and c4=? order by c3

(D) where c1=? and c5=? order by c2,c3

(E) where c1=? and c2=? and c5=? order by c2,c3

(F) where c1>? and c2=? and c4>? and c3=?

A选项：

注：utf8编码，一个索引长度为3，这里12代表4个字段都用到该索引。

B选项：

这里特别留意，虽然索引中的c3字段没有放在索引的最后，但是确实使用到了索引中c2字段的有序特性，因为执行计划的Extra部分未出现"fileasort"关键字。这是为什么呢？这里用到了MySQL5.6版本引入的Index Condition Pushdown (ICP) 优化。其核心思想是使用索引中的字段做数据过滤。我们来整理一下不使用ICP和使用ICP的区别：

如果没有使用ICP优化，其SQL执行步骤为：

1.使用索引列c1,c2获取满足条件的行数据。where c1='2' and c2='2'

2.回表查询数据，使用where c4='2'来过滤数据

3.对数据排序输出

如果使用了ICP优化，其SQL执行步骤为：

1.使用索引列c1,c2获取满足条件的行数据。where c1='2' and c2='2'

2.在索引中使用where c4='2'来过滤数据

3.因为数据有序，直接按顺序取出满足条件的数据

C选项：

1.在c1列使用索引找到c1='2'的所有行，然后回表使用c4='2'过滤掉不匹配的数据  
2.根据上一步的结果，对结果中的c3,c2联合排序，以便于得到连续变化的数据，同时在数据库内部创建临时表，用于存储group by的结果。

C选项扩展：

D选项：

D选项扩展：

E选项：

F选项：

（三）如何创建复合索引  
复合索引创建的难点在于字段顺序选择，我的观点如下：

如果存在等值查询和排序，则在创建复合索引时，将等值查询字段放在前面，排序放在最后面；  
如果存在多个等值查询，则选择性好的放在前面，选择性差的放在后面；  
如果存在等值查询、范围查询、排序。等值查询放在最前面，范围查询和排序需根据实际情况决定索引顺序；  
此外，《阿里巴巴Java开发手册-2020最新嵩山版》中有几个关于复合索引的规约，我们可以看一下：

1.如果有order by的场景，请注意利用索引的有序性。order by后的字段是组合索引的一部分，并且放在组合索引的最后，避免出现filesort的情况，影响查询性能。

正例：where a=? b=? order by c; 索引a\_b\_c

反例：索引如果存在范围查询，那么索引有序性将无法使用。如：where a>10 order by b; 索引a\_b无法排序。

2.建复合索引的时候，区分度最高的在最左边，如果where a=? and b=?，a列的值几乎接近唯一值，那么只需建单列索引idx\_a即可。

说明：存在等号和非等号混合判断条件时，在建索引时，请把等号条件的列前置。如：where c>? and d=?,那么即使c的区分度

更高，也必须把d放在索引的最前列，即创建索引idx\_d\_c。

实验：应该如何创建复合索引

在有的文档里面讲到过复合索引的创建规则：ESR原则：精确(Equal)匹配的字段放在最前面，排序(Sort)条件放中间，范围(Range)匹配的字段放在最后面。接下来我们来探索一下该方法是否正确。

例子：存在员工表employees

1  
2  
3  
4  
5  
6  
7  
8  
9  
10  
11  
12  
13  
14  
15  
16  
17  
18  
19  
20  
21  
22  
mysql> show create table employees;  
\+-----------+-------------------------------  
| Table | Create Table  
\+-----------+-------------------------------------  
| employees | CREATE TABLE \`employees\` (  
\`emp\_no\` int(11) NOT NULL,  
\`birth\_date\` date NOT NULL,  
\`first\_name\` varchar(14) NOT NULL,  
\`last\_name\` varchar(16) NOT NULL,  
\`gender\` enum('M','F') NOT NULL,  
\`hire\_date\` date NOT NULL,  
PRIMARY KEY (\`emp\_no\`)  
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |  
\+-----------+-------------------------------------  
  
\-- 数据量约30万行  
mysql> select count(\*) from employees;  
\+----------+  
| count(\*) |  
\+----------+  
| 300024 |  
\+----------+  
现在需要查询1998年后入职的first\_name为"Ebbe"员工，并按照出生日期升序排序。

其SQL语句如下：

1  
2  
3  
4  
5  
select emp\_no,birth\_date,first\_name,last\_name,gender,hire\_date  
from employees  
where hire\_date >= '1998-01-01'  
and first\_name = 'Ebbe'  
order by birth\_date;  
为了优化该SQL语句的性能，需要在表上创建索引，为了保证where与order by都使用到索引，决定创建复合索引，有如下创建顺序：

（A）hire\_date,first\_name,birth\_date

（B）hire\_date,birth\_date,first\_name

（C）first\_name,hire\_date,birth\_date

（D）first\_name,birth\_date,hire\_date

（E）birth\_date,first\_name,hire\_date

（F）birth\_date,hire\_date,first\_name

确认哪种顺序创建索引是最优的。

Note:

1.date类型占3个字节的空间，hire\_date和 birth\_date都占用3个字节的空间。

2.first\_name是变长字段，多使用2个字节，如果允许为NULL值，还需多使用1个字节，占用16个字节

A选项：hire\_date,first\_name,birth\_date

1  
create index idx\_a on employees(hire\_date,first\_name,birth\_date);  
其执行计划如下：

B选项：hire\_date,birth\_date,first\_name

为避免干扰，删除上面创建的索引idx\_a，然后创建idx\_b。

1  
create index idx\_b on employees(hire\_date,birth\_date,first\_name);  
其执行计划如下：

C选项：first\_name,hire\_date,birth\_date

为避免干扰，删除上面创建的索引idx\_b，然后创建idx\_c。

1  
create index idx\_c on employees(first\_name,hire\_date,birth\_date);  
其执行计划如下：

D选项：first\_name,birth\_date,hire\_date

为避免干扰，删除上面创建的索引idx\_c，然后创建idx\_d。

1  
create index idx\_d on employees(first\_name,birth\_date,hire\_date);  
其执行计划如下：

E选项：birth\_date,first\_name,hire\_date

为避免干扰，删除上面创建的索引idx\_d，然后创建idx\_e。

1  
create index idx\_e on employees(birth\_date,first\_name,hire\_date);  
其执行计划如下：

F选项：birth\_date,hire\_date,first\_name

为避免干扰，删除上面创建的索引idx\_e，然后创建idx\_f。

1  
create index idx\_f on employees(birth\_date,hire\_date,first\_name);  
其执行计划如下：

通过上面的6个索引测试，我们发现，等值查询列和范围查询列放在复合索引前面，复合索引都能被使用到，只是使用到的列可能不一样。哪种方式创建索引最好呢？MySQL的查询优化器是基于开销(cost)来选择最优的执行计划的，我们不妨来看看上面的6个索引的执行开销。

索引 开销cost  
\---------- ------------  
idx\_a 8518  
idx\_b 8524  
idx\_c 13  
idx\_d 228  
idx\_e 78083  
idx\_f 78083

通过上面的开销，可以看到：

idx\_a和idx\_b：索引使用范围查询字段开头，导致索引只能使用到第一列，无法消除排序，导致开销较大；  
idx\_c和idx\_d：索引使用等值查询字段开头，范围查询和排序位于后面，开销是最小的；  
idx\_e和idx\_f ：索引使用排序字段开头，导致索引无法被使用到，走的全表扫描，开销巨大。  
更进一步，idx\_c和idx\_d如何选择呢？idx\_c使用索引进行等值查询+范围查询，然后对数据进行排序；idx\_d使用索引进行等值查询+索引条件下推查询，然后按照顺序直接获取数据。两种方式各有优劣，我们不妨再来看一个例子：

把上面6个索引都加到表上，看看如下SQL会选择哪个索引。

SQL2

（四）复合索引总结  
1.复合索引的创建，如果存在多个等值查询，则将选择性好的列放在最前面，选择性差的列放在后面；

2.复合索引的创建，如果涉及到等值查询和范围查询，不管非等值查询的列的选择性如何好，等值查询的字段要放在非等值查询的前面；

3.复合索引的创建，如果涉及到等值查询和范围查询和排序(order by、group by)，则等值查询放在索引最前面，范围查询和排序哪个在前，哪个在后，需要根据实际场景决定。如果范围查询在前，则无法使用到索引的有序性，需filesort，适用于返回结果较少的SQL，因为结果少则排序开销小；如果排序在前，则可以使用到索引的有序性，但是需要回表(或者索引条件下推)去查询数据，适用于返回结果较多的SQL，因为无需排序，直接取出数据。

4.复合索引的创建，一定不能把order by、group by的列放在索引的最前面，因为查询中总是where先于order by执行；

5.使用索引进行范围查询会导致后续索引字段无法被使用，如果有排序，无法消除filesort排序。例子：a\_b\_c索引，where a>? and b = ? order by c，则a可以被使用到，b无法被使用，c字段需filesort。