vlambda博客
学习文章列表

MySQL联合索引使用规则分析

MySql联合索引

对数据库方面的知识有点匮乏或者说缺乏系统性的学习，平时的工作中虽然有用到了数据库，也仅限于基本的数据库的增删改查，具体的实现机制或者原理了解的比较浅显或者甚至比较混乱，所以最近也在看一些数据库相关的文章或者书籍。不过在看了多篇文章后，发现这些文章居然有些地方是相互矛盾的，这就让人很疑惑。和朋友讨论，也找了一些有参考价值的文章或者评论，但是最近还是觉得自己亲自实践一下比较好。

1. 前期准备：

  • 创建一张表，并且在(c1,c2,c3)上创建一个联合索引IDX_U1

CREATE TABLE `i_test` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `c1` int(11) DEFAULT NULL COMMENT 'c1',
  `c2` int(11) DEFAULT NULL COMMENT 'c2',
  `c3` int(11) DEFAULT NULL COMMENT 'c3',
  `c4` int(11) DEFAULT NULL COMMENT 'c4',
  `c5` int(11) DEFAULT NULL COMMENT 'c5',
  `c6` int(11) DEFAULT NULL COMMENT 'c6',
  PRIMARY KEY (`ID`),
  KEY `IDX_U1` (`c1`,`c2`,`c3`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='i_test'

2. 测试用例

2.1 可以分以下几种情况：

  • 查询所有列，索引按顺序使用，在索引列顺序的前中后插入不在索引中的列

  • 查询所有列，索引乱序使用，在索引条件的前中后插入不在索引中的列

  • 查询所有列，顺序使用部份索引，主要有(c1c2,c1c3,c2c3)三种情况

  • 查询所有列，乱序使用部份索引，主要有(c2c1,c3c1,c3c2)三种情况

  • 查询所有列，单独使用索引中的一列

  • 查询索引中的列，索引按顺序使用，在索引列顺序的前中后插入不在索引中的列

  • 查询索引中的列，索引乱序使用，在索引条件的前中后插入不在索引中的列

  • 查询索引中的列，顺序使用部份索引，主要有(c1c2,c1c3,c2c3)三种情况，在索引列顺序的前中后插入不在索引中的列

  • 查询索引中的列，乱序使用部份索引，主要有(c2c1,c3c1,c3c2)三种情况，在索引列顺序的前中后插入不在索引中的列

  • 查询索引中的列，单独使用索引中的一列，在索引列顺序的前中后插入不在索引中的列

  • 查询索引中的列，使用非索引列查询

3.先说结论

  • 联合索引的使用顺序和where条件中的顺序并没有很强的关系，因为sql执行器会优化sql来匹配索引顺序；在使用中，为了减少执行器优化开销，应该尽量按照索引顺序使用

  • 联合索引是按照顺序最左匹配的；即：如果跳过某个索引列，其后的索引列都不会使用索引。

  • 如果查询列为索引列的话，会使用到覆盖索引，比如： select c1,c2,c3 from i_test where c2='' and c3='' 就会使用到覆盖索引，而且会减少回表查询的操作

4 相关概念

4.1 覆盖索引

如果一个索引包含（或者说覆盖了）所有满足查询所需要的数据，那么就称这类索引为覆盖索引

4.2 Extra字段解释

using index ：使用覆盖索引的时候就会出现
using where：在查找使用索引的情况下，需要回表去查询所需的数据
using index condition：查找使用了索引，但是需要回表查询数据
using index & using where：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据

5.实践验证

5.1 查询所有列，索引按顺序使用，在索引列顺序的前中后插入不在索引中的列

mysql> explain select * from i_test where c1='' and c2='' and c3='';
+----+-------------+--------+------------+------+---------------+--------+---------+-------------------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys | key    | key_len | ref               | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+--------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | i_test | NULL       | ref  | IDX_U1        | IDX_U1 | 15      | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+---------------+--------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (1.89 sec)

mysql> explain select * from i_test where c1='' and c2='' and c3='' and c4='';
+----+-------------+--------+------------+------+---------------+--------+---------+-------------------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key    | key_len | ref               | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+--------+---------+-------------------+------+----------+-------------+
|  1 | SIMPLE      | i_test | NULL       | ref  | IDX_U1        | IDX_U1 | 15      | const,const,const |    1 |   100.00 | Using where |
+----+-------------+--------+------------+------+---------------+--------+---------+-------------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from i_test where c4='' and c1='' and c2='' and c3='';
+----+-------------+--------+------------+------+---------------+--------+---------+-------------------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key    | key_len | ref               | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+--------+---------+-------------------+------+----------+-------------+
|  1 | SIMPLE      | i_test | NULL       | ref  | IDX_U1        | IDX_U1 | 15      | const,const,const |    1 |   100.00 | Using where |
+----+-------------+--------+------------+------+---------------+--------+---------+-------------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from i_test where c1='' and c4='' and c2='' and c3='';
+----+-------------+--------+------------+------+---------------+--------+---------+-------------------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key    | key_len | ref               | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+--------+---------+-------------------+------+----------+-------------+
|  1 | SIMPLE      | i_test | NULL       | ref  | IDX_U1        | IDX_U1 | 15      | const,const,const |    1 |   100.00 | Using where |
+----+-------------+--------+------------+------+---------------+--------+---------+-------------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
5.1.1结论

可以看到，以上几种情况都使用到了索引；可以说，联合索引按顺序使用，不论顺序中是否使用了其他条件，都会使用到索引。

5.2 查询所有列，索引乱序使用，在索引条件的前中后插入不在索引中的列

mysql> explain select * from i_test where c3='' and c2='' and c1='';
+----+-------------+--------+------------+------+---------------+--------+---------+-------------------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys | key    | key_len | ref               | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+--------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | i_test | NULL       | ref  | IDX_U1        | IDX_U1 | 15      | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+---------------+--------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from i_test where c4='' and c3='' and c2='' and c1='';
+----+-------------+--------+------------+------+---------------+--------+---------+-------------------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key    | key_len | ref               | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+--------+---------+-------------------+------+----------+-------------+
|  1 | SIMPLE      | i_test | NULL       | ref  | IDX_U1        | IDX_U1 | 15      | const,const,const |    1 |   100.00 | Using where |
+----+-------------+--------+------------+------+---------------+--------+---------+-------------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from i_test where c3='' and c4='' and c2='' and c1='';
+----+-------------+--------+------------+------+---------------+--------+---------+-------------------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key    | key_len | ref               | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+--------+---------+-------------------+------+----------+-------------+
|  1 | SIMPLE      | i_test | NULL       | ref  | IDX_U1        | IDX_U1 | 15      | const,const,const |    1 |   100.00 | Using where |
+----+-------------+--------+------------+------+---------------+--------+---------+-------------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from i_test where c3='' and c2='' and c1='' and c4='';
+----+-------------+--------+------------+------+---------------+--------+---------+-------------------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key    | key_len | ref               | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+--------+---------+-------------------+------+----------+-------------+
|  1 | SIMPLE      | i_test | NULL       | ref  | IDX_U1        | IDX_U1 | 15      | const,const,const |    1 |   100.00 | Using where |
+----+-------------+--------+------------+------+---------------+--------+---------+-------------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
5.2.1 结论

乱序使用所有索引列，也会使用到索引。所以可以得出结论：使用索引和where条件中的索引列顺序无关，只是和使用到的索引列的顺序有关。应该是MySQL的执行器优化了语句的结果；因为执行器对SQL的语句优化也是有开销的，所以实际使用中where中的语句最好还是按照索引的顺序写会比较好。

5.3 查询所有列，顺序使用部份索引，主要有(c1c2,c1c3,c2c3)三种情况

mysql> explain select * from i_test where c1='' and c2='';
+----+-------------+--------+------------+------+---------------+--------+---------+-------------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys | key    | key_len | ref         | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+--------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | i_test | NULL       | ref  | IDX_U1        | IDX_U1 | 10      | const,const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+---------------+--------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from i_test where c1='' and c3='';
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+-----------------------+
| id | select_type | table  | partitions | type | possible_keys | key    | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | i_test | NULL       | ref  | IDX_U1        | IDX_U1 | 5       | const |    1 |   100.00 | Using index condition |
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from i_test where c2='' and c3='';
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | i_test | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
5.3.1 结论

在使用索引中的部份索引列的时候，必须保证索引的顺序使用即c1,c2 会用到 c1,c2两个列上的索引，c1,c3因为跳过了c2所以不会使用到c3的索引；c2,c3跳过了c1,不会使用索引。所以可以得出一个结论：索引是按照顺序最左匹配的；即：如果跳过某个索引列，其后的索引列都不会使用索引。

5.4 查询所有列，乱序使用部份索引，主要有(c2c1,c3c1,c3c2)三种情况

基于 5.2.1 的结论可以推断得知，此处的查询结果应该和5.3中的结果一致，执行过程就不贴出来了。

5.4.1 结论

参见 5.3.1 结论

5.5 查询所有列，单独使用索引中的一列

基于 5.3.1 的结论可以推断得知，只有在使用c1查询的时候会使用到索引，其他时候都不会使用到。执行过程就不贴出来了。

5.6 以下几种情况一起讨论

5.6.1 查询索引中的列，索引按顺序使用，在索引列顺序的前中后插入不在索引中的列

此种情况同 5.1,会使用到覆盖索引

mysql> explain select c1,c2,c3 from i_test  where c1='' and  c2='' and c3='';
+----+-------------+--------+------------+------+---------------+--------+---------+-------------------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key    | key_len | ref               | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+--------+---------+-------------------+------+----------+-------------+
|  1 | SIMPLE      | i_test | NULL       | ref  | IDX_U1        | IDX_U1 | 15      | const,const,const |    1 |   100.00 | Using index |
+----+-------------+--------+------------+------+---------------+--------+---------+-------------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select c1,c2,c3 from i_test  where c3='' and  c2='' and c1='';
+----+-------------+--------+------------+------+---------------+--------+---------+-------------------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key    | key_len | ref               | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+--------+---------+-------------------+------+----------+-------------+
|  1 | SIMPLE      | i_test | NULL       | ref  | IDX_U1        | IDX_U1 | 15      | const,const,const |    1 |   100.00 | Using index |
+----+-------------+--------+------------+------+---------------+--------+---------+-------------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
5.6.1.1结论

可以看到，Extra是using index表示使用到了覆盖索引，

5.6.2 查询索引中的列，索引乱序使用，在索引条件的前中后插入不在索引中的列

基于 5.2.1 的结论可以推断得知，此种情况同 5.6.1

5.7 查询索引中的列，顺序使用部份索引，主要有(c1c2,c1c3,c2c3)三种情况，在索引列顺序的前中后插入不在索引中的列

mysql> explain select c1,c2,c3 from i_test where c1='' and c2='';
+----+-------------+--------+------------+------+---------------+--------+---------+-------------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key    | key_len | ref         | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+--------+---------+-------------+------+----------+-------------+
|  1 | SIMPLE      | i_test | NULL       | ref  | IDX_U1        | IDX_U1 | 10      | const,const |    1 |   100.00 | Using index |
+----+-------------+--------+------------+------+---------------+--------+---------+-------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select c1,c2,c3 from i_test where c1='' and c3='';
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+--------------------------+
| id | select_type | table  | partitions | type | possible_keys | key    | key_len | ref   | rows | filtered | Extra                    |
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+--------------------------+
|  1 | SIMPLE      | i_test | NULL       | ref  | IDX_U1        | IDX_U1 | 5       | const |    1 |   100.00 | Using where; Using index |
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select c1,c2,c3 from i_test where c2='' and c3='';
+----+-------------+--------+------------+-------+---------------+--------+---------+------+------+----------+--------------------------+
| id | select_type | table  | partitions | type  | possible_keys | key    | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+--------+------------+-------+---------------+--------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | i_test | NULL       | index | NULL          | IDX_U1 | 15      | NULL |    1 |   100.00 | Using where; Using index |
+----+-------------+--------+------------+-------+---------------+--------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
5.7.1 结论

可以看到，以上几种情况都使用了索引，和5.3的结论不一样；这里我们就需要注意Extra字段了，可以看到，在c2c3的组合中，使用到了using where using index表示使用到了覆盖索引。

5.8 查询索引中的列，乱序使用部份索引，主要有(c2c1,c3c1,c3c2)三种情况，在索引列顺序的前中后插入不在索引中的列

基于 5.2.1 的结论可以推断得知，此种情况同 5.7,会使用到覆盖索引

5.9 查询索引中的列，单独使用索引中的一列，在索引列顺序的前中后插入不在索引中的列

此种情况同 5.7,会使用到覆盖索引

5.10查询索引中的列，使用非索引列查询

mysql> explain select c1,c2,c3 from i_test where c4='';
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | i_test | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
5.10.1

这种情况，虽然查询的是索引列中的数据，但是没有使用到索引；Extra字段为using where表示使用到索引的情况下，会去回表查询。


推荐阅读

[5.05] 暗喻幻想港中35周年限定版1.03+DLC+金手指+王中王存档 [5.05] 暗喻幻想港中35周年限定版1.03+DLC+金手指+王中王存档
MySQL系列(一) : SQL语句是如何执行的? MySQL系列(一) : SQL语句是如何执行的?

相关文章