建立联合索引abc，根据查询条件的不同，验证索引命中情况。
实验情况
a
1
2
3
4
5
6
7
| mysql> explain select * from demo where a = 1;
+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+
| 1 | SIMPLE | demo | NULL | ref | abc | abc | 4 | const | 1 | 100.00 | Using index |
+
1 row in set, 1 warning (0.00 sec)
|
结论：命中索引，匹配到a索引
ab
1
2
3
4
5
6
7
| mysql> explain select * from demo where a = 1 and b = 1;
+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+
| 1 | SIMPLE | demo | NULL | ref | abc | abc | 8 | const,const | 1 | 100.00 | Using index |
+
1 row in set, 1 warning (0.00 sec)
|
结论：命中索引，会使用到a索引
ac
1
2
3
4
5
6
7
| mysql> explain select * from demo where a = 1 and c = 1;
+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+
| 1 | SIMPLE | demo | NULL | ref | abc | abc | 4 | const | 1 | 20.00 | Using where; Using index |
+
1 row in set, 1 warning (0.00 sec)
|
结论：命中索引，会使用到a索引
abc
1
2
3
4
5
6
7
| mysql> explain select * from demo where a = 1 and b = 1 and c = 1;
+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+
| 1 | SIMPLE | demo | NULL | ref | abc | abc | 12 | const,const,const | 1 | 100.00 | Using index |
+
1 row in set, 1 warning (0.00 sec)
|
结论：命中索引，SQL查询优化器会自动优化调整查询条件。
acb
1
2
3
4
5
6
7
| mysql> explain select * from demo where a = 1 and c = 1 and b = 1;
+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+
| 1 | SIMPLE | demo | NULL | ref | abc | abc | 12 | const,const,const | 1 | 100.00 | Using index |
+
1 row in set, 1 warning (0.00 sec)
|
结论：命中索引，SQL查询优化器会自动优化调整查询条件为abc。
b
1
2
3
4
5
6
7
| mysql> explain select * from demo where b = 1;
+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+
| 1 | SIMPLE | demo | NULL | index | NULL | abc | 12 | NULL | 5 | 20.00 | Using where; Using index |
+
1 row in set, 1 warning (0.00 sec)
|
结论：未命中索引
ba
1
2
3
4
5
6
7
| mysql> explain select * from demo where b = 1 and a = 1;
+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+
| 1 | SIMPLE | demo | NULL | ref | abc | abc | 8 | const,const | 1 | 100.00 | Using index |
+
1 row in set, 1 warning (0.00 sec)
|
结论：命中索引，SQL查询优化器会自动优化调整查询条件为ab。
bc
1
2
3
4
5
6
7
| mysql> explain select * from demo where b = 1 and c = 1;
+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+
| 1 | SIMPLE | demo | NULL | index | NULL | abc | 12 | NULL | 5 | 20.00 | Using where; Using index |
+
1 row in set, 1 warning (0.00 sec)
|
结论：未命中索引
bca
1
2
3
4
5
6
7
| mysql> explain select * from demo where b = 1 and c = 1 and a = 1;
+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+
| 1 | SIMPLE | demo | NULL | ref | abc | abc | 12 | const,const,const | 1 | 100.00 | Using index |
+
1 row in set, 1 warning (0.00 sec)
|
结论：命中索引，SQL查询优化器会自动优化调整查询条件为abc。
bac
1
2
3
4
5
6
7
| mysql> explain select * from demo where b = 1 and a = 1 and c = 1;
+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+
| 1 | SIMPLE | demo | NULL | ref | abc | abc | 12 | const,const,const | 1 | 100.00 | Using index |
+
1 row in set, 1 warning (0.00 sec)
|
结论：命中索引，SQL查询优化器会自动优化调整查询条件为abc。
c
1
2
3
4
5
6
7
| mysql> explain select * from demo where c = 1;
+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+
| 1 | SIMPLE | demo | NULL | index | NULL | abc | 12 | NULL | 5 | 20.00 | Using where; Using index |
+
1 row in set, 1 warning (0.00 sec)
|
结论：未命中索引
cb
1
2
3
4
5
6
7
| mysql> explain select * from demo where c = 1 and b = 1;
+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+
| 1 | SIMPLE | demo | NULL | index | NULL | abc | 12 | NULL | 5 | 20.00 | Using where; Using index |
+
1 row in set, 1 warning (0.00 sec)
|
结论：未命中索引
ca
1
2
3
4
5
6
7
| mysql> explain select * from demo where c = 1 and a = 1;
+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+
| 1 | SIMPLE | demo | NULL | ref | abc | abc | 4 | const | 1 | 20.00 | Using where; Using index |
+
1 row in set, 1 warning (0.00 sec)
|
结论：命中索引，SQL查询优化器会自动优化调整查询条件变成ac，然后就会使用到索引a了
cba
1
2
3
4
5
6
7
| mysql> explain select * from demo where c = 1 and b = 1 and a = 1;
+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+
| 1 | SIMPLE | demo | NULL | ref | abc | abc | 12 | const,const,const | 1 | 100.00 | Using index |
+
1 row in set, 1 warning (0.00 sec)
|
结论：命中索引，SQL查询优化器会自动优化调整查询条件为abc。
cab
1
2
3
4
5
6
7
| mysql> explain select * from demo where c = 1 and a = 1 and b = 1;
+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+
| 1 | SIMPLE | demo | NULL | ref | abc | abc | 12 | const,const,const | 1 | 100.00 | Using index |
+
1 row in set, 1 warning (0.00 sec)
|
结论：命中索引，SQL查询优化器会自动优化调整查询条件为abc。