什么是组合索引？在哪些场景中，组合索引会失效？

什么是组合索引？

  由多个字段组成的索引叫组合索引。

问题：在哪些场景中，组合索引会失效？

场景：

数据表：job_status_trace_log【说明:id是主键】
数据量：35w
创建索引：ALTER table job_status_trace_log add INDEX creation_time_index(creation_time,job_name,source)

注意：创建表的时候，字段要超过组合索引字段的个数，因为它会命中覆盖索引，导致跟我下面测试的数据不一致：

例子：组合索引(a,b,c)都有哪些排列组合：

• a,b,c
• a,c,b
• c,a,b
• c,b,a
• b,c,a
• a,b
• b,a
• a,c
• c,a
• b,c
• a
• b
• c
• …就列举那么多已经够了

先看看总结，再看验证过程，可能会更棒

总结：

1、组合索引字段无论顺序如何改变都会用到索引，前提是所有字段都在where条件上
2、如果想要使用一个或者两个字段在where条件上，必须有组合索引里的第一个字段，但是与顺序无关，例如a,c或c,a，这种场景是可以命中索引的。但是，b,c或c,b这种是不会命中索引的。
3、如果组合索引存在范围查询，则组合索引可能会命中索引，这个跟B+Tree的叶子节点中存储的数据是否在当前的叶子节点中，即InnoDB存储引擎的最小存储单元——页，InnoDB页的大小默认是16k，可以通过参数查看页的默认大小：show global status like ‘innodb_page_size’;如果想要修改InnoDB页的大小，需要通过修改mysql源码才可以修改，找到源码文件(storage/innobase/include/univ.i)，找到参数：UNIV_PAGE_SIZE，该参数必须是2的n次方，例如4k、8k、16k、32k、64k等等。
4、order by 只能使用a，才能用到索引

排列组合一：a,b,c，恭喜成功命中索引

mysql> explain select * from job_status_trace_log where creation_time = '2020-01-01 00:00:00' and job_name ='member.channelRouteTask' and source='LITE_EXECUTOR'\G
*************************** 1. 行 ***************************
id           : 1
select_type  : SIMPLE
table        : job_status_trace_log
partitions   : NULL
type         : ref
possible_keys: creation_time_index
key          : creation_time_index
key_len      : 609
ref          : const,const,const
rows         : 1
filtered     : 100.00
Extra        : NULL
1 行于数据集 (0.02 秒)

排列组合二：a,c,b，恭喜成功命中索引

mysql> explain select * from job_status_trace_log where creation_time = '2020-01-01 00:00:00' and source='LITE_EXECUTOR' and job_name ='member.channelRouteTask'\G
*************************** 1. 行 ***************************
id           : 1
select_type  : SIMPLE
table        : job_status_trace_log
partitions   : NULL
type         : ref
possible_keys: creation_time_index
key          : creation_time_index
key_len      : 609
ref          : const,const,const
rows         : 1
filtered     : 100.00
Extra        : NULL
1 行于数据集 (0.03 秒)

排列组合三：c,a,b，恭喜成功命中索引

mysql> explain select * from job_status_trace_log where source='LITE_EXECUTOR' and creation_time = '2020-01-01 00:00:00' and job_name ='member.channelRouteTask'\G
*************************** 1. 行 ***************************
id           : 1
select_type  : SIMPLE
table        : job_status_trace_log
partitions   : NULL
type         : ref
possible_keys: creation_time_index
key          : creation_time_index
key_len      : 609
ref          : const,const,const
rows         : 1
filtered     : 100.00
Extra        : NULL
1 行于数据集 (0.03 秒)

排列组合四：c,b,a，恭喜成功命中索引

mysql> explain select * from job_status_trace_log where source='LITE_EXECUTOR' and job_name ='member.channelRouteTask' and creation_time = '2020-01-01 00:00:00'\G
*************************** 1. 行 ***************************
id           : 1
select_type  : SIMPLE
table        : job_status_trace_log
partitions   : NULL
type         : ref
possible_keys: creation_time_index
key          : creation_time_index
key_len      : 609
ref          : const,const,const
rows         : 1
filtered     : 100.00
Extra        : NULL
1 行于数据集 (0.07 秒)

排列组合五：b,c,a，恭喜成功命中索引

mysql> explain select * from job_status_trace_log where job_name ='member.channelRouteTask' and source='LITE_EXECUTOR' and creation_time = '2020-01-01 00:00:00'\G
*************************** 1. 行 ***************************
id           : 1
select_type  : SIMPLE
table        : job_status_trace_log
partitions   : NULL
type         : ref
possible_keys: creation_time_index
key          : creation_time_index
key_len      : 609
ref          : const,const,const
rows         : 1
filtered     : 100.00
Extra        : NULL
1 行于数据集 (0.02 秒)

排列组合六：a,b，恭喜成功命中索引

mysql> explain select * from job_status_trace_log where creation_time = '2020-01-01 00:00:00' and job_name ='member.channelRouteTask'\G
*************************** 1. 行 ***************************
id           : 1
select_type  : SIMPLE
table        : job_status_trace_log
partitions   : NULL
type         : ref
possible_keys: creation_time_index
key          : creation_time_index
key_len      : 407
ref          : const,const
rows         : 2
filtered     : 100.00
Extra        : NULL
1 行于数据集 (0.04 秒)

排列组合七：b,a，恭喜成功命中索引

mysql> explain select * from job_status_trace_log where job_name ='member.channelRouteTask' and creation_time = '2020-01-01 00:00:00'\G
*************************** 1. 行 ***************************
id           : 1
select_type  : SIMPLE
table        : job_status_trace_log
partitions   : NULL
type         : ref
possible_keys: creation_time_index
key          : creation_time_index
key_len      : 407
ref          : const,const
rows         : 2
filtered     : 100.00
Extra        : NULL
1 行于数据集 (0.03 秒)

排列组合八：a,c，恭喜成功命中索引

mysql> explain select * from job_status_trace_log where creation_time = '2020-01-01 00:00:00' and source='11'\G 
*************************** 1. 行 ***************************
id           : 1
select_type  : SIMPLE
table        : job_status_trace_log
partitions   : NULL
type         : ref
possible_keys: creation_time_index
key          : creation_time_index
key_len      : 5
ref          : const
rows         : 17
filtered     : 10.00
Extra        : Using index condition
1 行于数据集 (0.04 秒)

排列组合九：c,a，恭喜成功命中索引

mysql> explain select * from job_status_trace_log where source='11' and creation_time = '2020-01-01 00:00:00'\G
*************************** 1. 行 ***************************
id           : 1
select_type  : SIMPLE
table        : job_status_trace_log
partitions   : NULL
type         : ref
possible_keys: creation_time_index
key          : creation_time_index
key_len      : 5
ref          : const
rows         : 17
filtered     : 10.00
Extra        : Using index condition
1 行于数据集 (0.04 秒)

排列组合十：b,c，很遗憾全表扫描了

mysql> explain select * from job_status_trace_log where job_name ='member.channelRouteTask' and source='LITE_EXECUTOR'\G
*************************** 1. 行 ***************************
id           : 1
select_type  : SIMPLE
table        : job_status_trace_log
partitions   : NULL
type         : ALL
possible_keys: NULL
key          : NULL
key_len      : NULL
ref          : NULL
rows         : 4316676
filtered     : 1.00
Extra        : Using where
1 行于数据集 (0.03 秒)

排列组合十一：a，恭喜成功命中索引

mysql> explain select * from job_status_trace_log where creation_time = '2020-01-01 00:00:00'\G
*************************** 1. 行 ***************************
id           : 1
select_type  : SIMPLE
table        : job_status_trace_log
partitions   : NULL
type         : ref
possible_keys: creation_time_index
key          : creation_time_index
key_len      : 5
ref          : const
rows         : 17
filtered     : 100.00
Extra        : NULL
1 行于数据集 (0.02 秒)

排列组合十二：b，很遗憾全表扫描了

mysql> explain select * from job_status_trace_log where job_name ='member.channelRouteTask'\G
*************************** 1. 行 ***************************
id           : 1
select_type  : SIMPLE
table        : job_status_trace_log
partitions   : NULL
type         : ALL
possible_keys: NULL
key          : NULL
key_len      : NULL
ref          : NULL
rows         : 4316693
filtered     : 10.00
Extra        : Using where
1 行于数据集 (0.04 秒)

排列组合十三：c，很遗憾全表扫描了

mysql> explain select * from job_status_trace_log where source='LITE_EXECUTOR'\G 
*************************** 1. 行 ***************************
id           : 1
select_type  : SIMPLE
table        : job_status_trace_log
partitions   : NULL
type         : ALL
possible_keys: NULL
key          : NULL
key_len      : NULL
ref          : NULL
rows         : 4316702
filtered     : 10.00
Extra        : Using where
1 行于数据集 (0.06 秒)

排列组合十四：a>0,b=1,c=1，很遗憾全表扫描了

mysql> explain select * from job_status_trace_log where creation_time > '2020-01-01 00:00:00' and job_name ='member.channelRouteTask' and source='LITE_EXECUTOR'\G
*************************** 1. 行 ***************************
id           : 1
select_type  : SIMPLE
table        : job_status_trace_log
partitions   : NULL
type         : ALL
possible_keys: creation_time_index
key          : NULL
key_len      : NULL
ref          : NULL
rows         : 4316703
filtered     : 0.38
Extra        : Using where
1 行于数据集 (0.04 秒)

排列组合十五：a=1,b>0,c=1，很遗憾全表扫描了

mysql> explain select * from job_status_trace_log where  job_name ='member.channelRouteTask' and creation_time > '2020-01-01 00:00:00' and source='LITE_EXECUTOR'\G
*************************** 1. 行 ***************************
id           : 1
select_type  : SIMPLE
table        : job_status_trace_log
partitions   : NULL
type         : ALL
possible_keys: creation_time_index
key          : NULL
key_len      : NULL
ref          : NULL
rows         : 4316727
filtered     : 0.38
Extra        : Using where
1 行于数据集 (0.02 秒)

排列组合十六：a=1,b=1,c>0，很遗憾全表扫描了

mysql> explain select * from job_status_trace_log where  job_name ='member.channelRouteTask'  and source='LITE_EXECUTOR' and creation_time > '2020-01-01 00:00:00'\G
*************************** 1. 行 ***************************
id           : 1
select_type  : SIMPLE
table        : job_status_trace_log
partitions   : NULL
type         : ALL
possible_keys: creation_time_index
key          : NULL
key_len      : NULL
ref          : NULL
rows         : 4316730
filtered     : 0.38
Extra        : Using where
1 行于数据集 (0.06 秒)

排列组合十七：a>0，很遗憾全表扫描了

mysql> explain select * from job_status_trace_log where creation_time > '2020-01-01 00:00:00'\G
*************************** 1. 行 ***************************
id           : 1
select_type  : SIMPLE
table        : job_status_trace_log
partitions   : NULL
type         : ALL
possible_keys: creation_time_index
key          : NULL
key_len      : NULL
ref          : NULL
rows         : 4316730
filtered     : 38.09
Extra        : Using where
1 行于数据集 (0.03 秒)

总结：

• 1、组合索引字段无论顺序如何改变都会用到索引，前提是所有字段都在where条件上。
• 2、如果想要使用一个或者两个字段在where条件上，必须有组合索引里的第一个字段，但是与顺序无关，例如a,c或c,a，这种场景是可以命中索引的。但是，b,c或c,b这种是不会命中索引的。
• 3、如果组合索引存在范围查询，则组合索引可能会命中索引，这个跟B+Tree的叶子节点中存储的数据是否在当前的叶子节点中，即InnoDB存储引擎的最小存储单元——页，InnoDB页的大小默认是16k，可以通过参数查看页的默认大小：show global status like ‘innodb_page_size’;如果想要修改InnoDB页的大小，需要通过修改mysql源码才可以修改，找到源码文件(storage/innobase/include/univ.i)，找到参数:UNIV_PAGE_SIZE，该参数必须是2的n次方，例如4k、8k、16k、32k、64k等等。
• 4、order by 只能使用a，才能用到索引。

欢迎关注我的微信公众号，里面有很多干货，各种面试题