再学mysql-联合索引后面的列失效分析

站长

2023年08月28日 17:43 · 阅读数 13

1 前言

基础不牢，地动山摇！！！索引是Mysql提高查询效率的一大利器（针对innodb引擎，以下相同），对于多条件查询的情况，我们可以创建联合索引进一步提高查询效率，但如果使用不当，联合索引就会失效或者使用联合索引不充分，前者比较好理解不进行深入分析，本文主要分析后者的情况。

2 准备工作

2.1 数据初始化

首先我们创建一个简单的表

CREATE TABLE `multiple_part_index_demo` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `c1` int(11) NOT NULL DEFAULT '0',
  `c2` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `idx_c1_c2` (`c1`,`c2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8；;

这个表创建了一个c1和c2的联合索引，继续添加一些初始数据

insert into `multiple_part_index_demo` (`c1`, `c2`) values(1, 2);
INSERT INTO `multiple_part_index_demo` (`c1`, `c2`) VALUES(1, 2);
INSERT INTO `multiple_part_index_demo` (`c1`, `c2`) VALUES(2, 4);
INSERT INTO `multiple_part_index_demo` (`c1`, `c2`) VALUES(3, 1);
insert into `multiple_part_index_demo` (`c1`, `c2`) values(3, 2);
INSERT INTO `multiple_part_index_demo` (`c1`, `c2`) VALUES(4, 1);
INSERT INTO `multiple_part_index_demo` (`c1`, `c2`) VALUES(6, 1);
INSERT INTO `multiple_part_index_demo` (`c1`, `c2`) VALUES(6, 2);
insert into `multiple_part_index_demo` (`c1`, `c2`) values(6, 4);
INSERT INTO `multiple_part_index_demo` (`c1`, `c2`) VALUES(8, 1);
INSERT INTO `multiple_part_index_demo` (`c1`, `c2`) VALUES(8, 1);
INSERT INTO `multiple_part_index_demo` (`c1`, `c2`) VALUES(8, 1);
insert into `multiple_part_index_demo` (`c1`, `c2`) values(8, 2);
INSERT INTO `multiple_part_index_demo` (`c1`, `c2`) VALUES(8, 2);
INSERT INTO `multiple_part_index_demo` (`c1`, `c2`) VALUES(8, 4);

通过执行 select * from multiple_part_index_demo; 最终的表数据显示如下

+----+----+----+
| id | c1 | c2 |
+----+----+----+
|  1 |  1 |  2 |
|  2 |  1 |  2 |
|  3 |  2 |  1 |
|  4 |  3 |  1 |
|  5 |  3 |  3 |
|  6 |  4 |  1 |
|  7 |  6 |  2 |
|  8 |  6 |  3 |
|  9 |  6 |  4 |
| 10 |  8 |  1 |
| 11 |  8 |  1 |
| 12 |  8 |  1 |
| 13 |  8 |  2 |
| 14 |  8 |  2 |
| 15 |  8 |  4 |
+----+----+----+

2.2 构建B+树

我们根据初始化的数据构建一棵B+树，这里不讲解如何构造B+树，可查阅其他资料帮助消化。Myqsl实际页大小为16kb，这里假设每页只能存放3条用户数据，超过3条页就会分裂，图中不同颜色代表不同的涵义，注意右上角的颜色说明。

再学mysql-联合索引后面的列失效分析

3 索引命中分析

我们都知道索引都是有序的，它会按字段值的大小升序排序，那如果是联合索引呢？比如给出的c1和c2列联合索引，首先会根据c1比较，如果比较c1就可以比出大小，那么c1小的就会排在前面，否则继续比较c2，c2比较小的排在前面，这个规律应该不难理解，但也非常非常重要，下面分析的索引使用列的情况就是依据这个规则。

3.1 等值查询

执行sql1

sql1: SELECT * FROM multiple_part_index_demo WHERE c1 = 6 AND c2 = 2;

查询结果如下，返回一条数据

+----+----+----+
| id | c1 | c2 |
+----+----+----+
|  8 |  6 |  2 |
+----+----+----+

我们再看下这条sql命中索引的情况

+----+-------------+--------------------------+------------+------+---------------+-----------+---------+-------------+------+----------+-------------+
| id | select_type | table                    | partitions | type | possible_keys | key       | key_len | ref         | rows | filtered | Extra       |
+----+-------------+--------------------------+------------+------+---------------+-----------+---------+-------------+------+----------+-------------+
|  1 | SIMPLE      | multiple_part_index_demo | NULL       | ref  | idx_c1_c2     | idx_c1_c2 | 8       | const,const |    1 |   100.00 | Using index |
+----+-------------+--------------------------+------------+------+---------------+-----------+---------+-------------+------+----------+-------------+

我们主要看看explain中的key_len，key_len表示执行sql命中索引使用列的字节数之和，这里的长度为8，因为c1和c2都是int类型，固定长度为4个字节，4 + 4 = 8，所以可以推出c1和c2都被使用到。再根据B+树的结构来分析下，条件c1=6，在p1页里因为1 < 6 < 8，所以可以找到索引目录项(1, 2)，而(8, 1)就被过滤掉了，下面的页过滤不符合条件的项原理相同，所以可以得到查找的路径和叶子节点的数据（p6页是叶子节点，括号内分别是c1和c2）

p1(1, 2) -> p2(6, 1) -> p6(6, 2)(6, 3)(6, 4)

因为最终的叶子节点的数据c1都是6，所以c2是有序的，此时可以根据c2=2条件来较少扫描行数（这里一条记录就是一行），我们分步骤来分析下c2的比较情况 (1) 首先与(6, 2)比较，2=2符合条件返回给客户端，因为2>=2，因此需要继续往下扫描比较 (2) 与(6, 3)比较，2!=3无需返回给客户端，并且2<3，c2是有序排序，因此后面的肯定比2大，因此可以直接推出扫描因此，可以看到，用索引查找的过程中c1和c2都是有用到，所以explain出来的key_len=8。

3.2 无等号范围查询

执行下sql2

sql2: SELECT * FROM multiple_part_index_demo WHERE c1 < 3 AND c2 = 2;

查询结果如下，返回两条数据

+----+----+----+
| id | c1 | c2 |
+----+----+----+
|  1 |  1 |  2 |
|  2 |  1 |  2 |
+----+----+----+

看下这条sql命中索引的情况

+----+-------------+--------------------------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
| id | select_type | table                    | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+--------------------------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | multiple_part_index_demo | NULL       | range | idx_c1_c2     | idx_c1_c2 | 4       | NULL |    3 |    10.00 | Using where; Using index |
+----+-------------+--------------------------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+

这种情况的key_len为4，说明索引只使用到了c1列。在这里我再把图贴过来，免得再去翻。

再学mysql-联合索引后面的列失效分析再来分析下这种情况索引命中的情况，根据条件c1 < 3，查找节点的原理和上面的一样不再分析，可以得到查找的路径和叶子节点的数据

p1(1, 2) -> p2(1, 2) -> p4(1, 2)(1, 3)(2, 1)

最后看下叶子节点是怎么过滤不符合条件的记录，c1包含了两个值1和2，导致c2并不是有序排列的，所以三条记录需要拿出来遍历判断c2是否等于2，具体步骤如下 (1) 与(1, 2)比较，2=2符合条件返回给客户端，因为2>=2，因此需要继续往下扫描比较 (2) 与(1, 3)比较，3!=2不符合条件，虽然2<3，但是c2是无序的，因此需要继续往下扫描比较 (3) 与(2, 1)比较，1!=2不符合条件，全部扫描完成，推出扫描因此，范围查询条件后的列不会被索引使用。

3.3 有等号范围查询

上面给出了两种情况，别以为完了，还剩下一种。

sql3: SELECT * FROM multiple_part_index_demo WHERE c1 <= 3 AND c2 = 2;

看查询结果，此时仍然返回两条数据

+----+----+----+
| id | c1 | c2 |
+----+----+----+
|  1 |  1 |  2 |
|  2 |  1 |  2 |
+----+----+----+

继续看下执行计划

+----+-------------+--------------------------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
| id | select_type | table                    | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+--------------------------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | multiple_part_index_demo | NULL       | range | idx_c1_c2     | idx_c1_c2 | 8       | NULL |    4 |    10.00 | Using where; Using index |
+----+-------------+--------------------------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+

p1(1, 2) -> p2(1, 2)(3, 1) -> p4(1, 2)(1, 3)(2, 1), p5(3, 1)(3, 3)(4, 1)

查询条件c2=2，分析下叶子节点的数据过滤具体步骤 (1) 与(1, 2)比较，2=2符合条件返回给客户端，因为2>=2，因此需要继续往下扫描比较 (2) 与(1, 3)比较，3!=2不符合条件，虽然2<3，但是c2是无序的，因此需要继续往下扫描比较 (3) 与(2, 1)比较，1!=2不符合条件，虽然1<3，但是c2是无序的，因此需要继续往下扫描比较 (4) 与(3, 1)比较，1!=2不符合条件，并且1<2，因为c1等于3的记录c2是有序的，所以后面的记录要c1比较大，要么c2比较大，因此不需要再继续往下扫描比较这里可以看成是等值查询和范围查询的结合，sql3等价于下面这条sql

SELECT * FROM multiple_part_index_demo WHERE (c1 < 3 AND c2 = 2) OR (c1 = 3 AND c2 = 2);

4 总结

经过上面的分析，我们可以得出，当查询条件为范围查询时，索引后面的列就不会被用到，开发过程中应避免这种sql，把范围查询改成等值查询（=或者in），可能你的sql查询效率会更高。