MySQL联合索引最左匹配原则使用详解
前言
很多小伙伴在面试的过程中都会被问到：MySQL的索引的失效的情况有哪些？
这里顺便带大家复习一下这个八股文：
（1）联合索引不满足最左匹配原则。联合索引的使用遵循最左匹配原则，在联合索引中，最左侧的字段优先匹配。在查询的时候，如果想让查询条件走索引，那么最左边的字段要出现在查询条件中。
（2）运算操作或者函数操作。原因是因为如果没有进行运算操作，那么MySQL会直接走索引去B+树中查询数据。但是如果我们对字段进行了运算操作或者函数操作，实际上是拿新的字段去做查询，如果走索引，那么需要一个一个去对比，MySQL认为既然都要对比，不如直接全表扫描。
（3）字符串类型字段不加引号。字符串字段如果不加引号，会发生隐式转换成int类型，参数类型与字段类型不匹配。
（4）模糊查询匹配头部。索引本身相当于目录，将字符串字段从左到右依次排序，而左侧的占位符模糊匹配，导致无法正常按照目录进行匹配。所以索引会失效。
（5）or连接的条件。如果or连接的条件左右其中有一个不是索引字段，那么如果是单独使用的话肯定是要走全表扫描的，连接在一起再次进行索引查询反而是浪费性能了，所以索引会失效。
（6）索引列做 < 、> 或者<> 比较。
（7）查询使用is not null。如果使用is null正常走索引，但是使用is not null索引会失效。
（8）查询条件使用not in时，如果是主键走索引；如果是普通索引，则索引失效。
然后面试官很快就会给出一个场景题：
给你一个数据表，有a，b，c三个字段，然后我现在给这三个字段加上联合索引 index(a,b,c)，如果where后面跟着条件的顺序是(a,c,b)，那么索引会生效吗？
我们在学习MySQL索引的时候都会学习到最左匹配原则，带大家简单复习一下这个概念：
最左匹配原则：
最左匹配原则指的是，在使用联合索引时，MySQL 会根据联合索引中的字段顺序，从左到右依次到查询条件中去匹配，如果查询条件中存在与联合索引中最左侧字段相匹配的字段，则就会使用该字段过滤一批数据，直至联合索引中全部字段匹配完成，或者在执行过程中遇到范围查询（如 >、< ）才会停止匹配。对于 >=、<=、BETWEEN、like 前缀匹配的范围查询，并不会停止匹配。所以，我们在使用联合索引时，可以将区分度高的字段放在最左边，这也可以过滤更多数据。
所以，对于上面的场景题，我们会肯定的给出答案：不会生效。
那么，恭喜你，回去等待面试结果吧！！！
啊？难道结果不是这样吗？当然不是！
下面我们就一起来研究一下最左匹配原则的各种场景，看看联合索引是否会生效。
数据准备
（1）建表语句
CREATE TABLE `t_demo` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`a` varchar(15) DEFAULT NULL,
`b` varchar(15) DEFAULT NULL,
`c` varchar(15) DEFAULT NULL,
`d` varchar(15) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `INDEX_A_B_C` (`a`,`b`,`c`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
|
（2）快速产生测试数据10w条
执行下面的main方法后，产生数据文件为demo.sql，存放位置为E盘，我们只需要拿着这个数据直接去执行sql文件即可。
public static void main(String[] args) throws IOException {
for (int x = 1; x <= 100; x++) {
StringBuilder sql = new StringBuilder("INSERT INTO `t_demo`(a, b, c, d) VALUES ");
for (int i = 1; i <= 999; i++) {
splice(sql, ",");
}
splice(sql, ";");
sql.append("\r\n");
File file = new File("E:/demo.sql");
FileWriter fw = new FileWriter(file, true);
BufferedWriter bw = new BufferedWriter(fw);
bw.write(sql.toString());
bw.close();
fw.close();
}
}
private static void splice(StringBuilder sql, String s) {
String value = "('%s', '%s', '%s', '%s')";
String a = RandomStringUtils.randomNumeric(4);
String b = RandomStringUtils.random(2, true, false);
String c = RandomStringUtils.random(5, true, false);
String d = String.valueOf(System.currentTimeMillis());
sql.append(String.format(value, a, b, c, d)).append(s);
}
|
工具类RandomStringUtils的依赖如下：
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-lang3</artifactId>
<version>3.11</version>
</dependency
|
使用分析
下面的分析是通过explain查看执行计划，如果对执行计划不太了解的可以去先复习下。
条件是a,b,c
条件是a，b，c三个，查询abc所有的排列组合情况。
mysql> explain select * from t_demo where a = '8304' and b = 'iS' and c = 'qbAhz';
+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+
| 1 | SIMPLE | t_demo | NULL | ref | INDEX_A_B_C | INDEX_A_B_C | 144 | const,const,const | 1 | 100.00 | NULL |
+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from t_demo where b = 'iS' and a = '8304' and c = 'qbAhz';
+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+
| 1 | SIMPLE | t_demo | NULL | ref | INDEX_A_B_C | INDEX_A_B_C | 144 | const,const,const | 1 | 100.00 | NULL |
+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from t_demo where a = '8304' and c = 'qbAhz' and b = 'iS' ;
+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+
| 1 | SIMPLE | t_demo | NULL | ref | INDEX_A_B_C | INDEX_A_B_C | 144 | const,const,const | 1 | 100.00 | NULL |
+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from t_demo where c = 'qbAhz' and a = '8304' and b = 'iS' ;
+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+
| 1 | SIMPLE | t_demo | NULL | ref | INDEX_A_B_C | INDEX_A_B_C | 144 | const,const,const | 1 | 100.00 | NULL |
+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from t_demo where c = 'qbAhz' and b = 'iS' and a = '8304';
+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+
| 1 | SIMPLE | t_demo | NULL | ref | INDEX_A_B_C | INDEX_A_B_C | 144 | const,const,const | 1 | 100.00 | NULL |
+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from t_demo where b = 'iS' and c = 'qbAhz' and a = '8304';
+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+
| 1 | SIMPLE | t_demo | NULL | ref | INDEX_A_B_C | INDEX_A_B_C | 144 | const,const,const | 1 | 100.00 | NULL |
+
1 row in set, 1 warning (0.00 sec)
|
条件是a,b,c其中的两个
mysql> explain select * from t_demo where a = '8304' and b = 'iS';
+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+
| 1 | SIMPLE | t_demo | NULL | ref | INDEX_A_B_C | INDEX_A_B_C | 96 | const,const | 1 | 100.00 | NULL |
+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from t_demo where a = '8304' and c = 'qbAhz';
+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+
| 1 | SIMPLE | t_demo | NULL | ref | INDEX_A_B_C | INDEX_A_B_C | 48 | const | 12 | 10.00 | Using index condition |
+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from t_demo where b = 'iS' and a = '8304';
+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+
| 1 | SIMPLE | t_demo | NULL | ref | INDEX_A_B_C | INDEX_A_B_C | 96 | const,const | 1 | 100.00 | NULL |
+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from t_demo where b = 'iS' and c = 'qbAhz';
+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+
| 1 | SIMPLE | t_demo | NULL | ALL | NULL | NULL | NULL | NULL | 99918 | 1.00 | Using where |
+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from t_demo where c = 'qbAhz' and a = '8304';
+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+
| 1 | SIMPLE | t_demo | NULL | ref | INDEX_A_B_C | INDEX_A_B_C | 48 | const | 12 | 10.00 | Using index condition |
+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from t_demo where c = 'qbAhz' and b = 'iS';
+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+
| 1 | SIMPLE | t_demo | NULL | ALL | NULL | NULL | NULL | NULL | 99918 | 1.00 | Using where |
+
1 row in set, 1 warning (0.00 sec)
|
条件是a,b,c其中的一个
mysql> explain select * from t_demo where a = '8304';
+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+
| 1 | SIMPLE | t_demo | NULL | ref | INDEX_A_B_C | INDEX_A_B_C | 48 | const | 12 | 100.00 | NULL |
+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from t_demo where b = 'iS';
+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+
| 1 | SIMPLE | t_demo | NULL | ALL | NULL | NULL | NULL | NULL | 99918 | 10.00 | Using where |
+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from t_demo where c = 'qbAhz';
+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+
| 1 | SIMPLE | t_demo | NULL | ALL | NULL | NULL | NULL | NULL | 99918 | 10.00 | Using where |
+
1 row in set, 1 warning (0.00 sec)
|
结果分析
- 查询条件是a、b、c时，无论是什么顺序，由于优化器优化，都会走INDEX_A_B_C联合索引；
- 查询条件是a、b时，会走联合索引；
- 查询条件是a、c时，也会走联合索引，但是Extra信息里面多了一行：
Using index condition，意思是先条件过滤索引，过滤完索引后找到所有符合索引条件的数据行，随后用WHERE子句中的其他条件去过滤这些数据行，这种情况只有a条件用到联合索引，c条件回表到聚簇索引过滤。
- 查询条件是b、c时，不走联合索引；
- 查询条件是a时，会走联合索引；
- 查询条件是b时，不走联合索引；
- 查询条件是c时，不走联合索引；、
经过上面的结果，我们可以清楚的看到，如果联合索引的字段全部在查询条件中，那么优化器会进行优化，都会走联合索引。
即使缺了中间的字段，也是会走联合索引的。
