MySQL 高级 —— 复合索引简介（多列索引）

布满荆棘的人生 2023-06-30 14:54 19阅读 0赞

# 引言 #

**复合索引**是指包含多个数据列的索引，与之概念相对的是单列索引，仅包含一个数据列。在大多数情况下，建立多列索引的好处都要多于单列索引。另外，复合索引最多支持16个列，但请一定不要让复合索引包含太多的列，这会导致索引空间的浪费。

**索引是一种有序的数列**，复合索引也是如此。

相对于单一索引，复合索引有一些必须注意的使用细节，否则很容易造成索引失效，降低查询速度。而要了解这些注意细节，就必须从复合索引的作用方式入手。

# 一、复合索引的作用方式 #

所谓“复合”，那一定是包含有多个，然而人们更愿意说“复合索引”胜于“组合索引”（实际上复合索引也叫组合索引），是因为复合更有 1 + 1 > 2 的感觉。换句话说，**复合索引并不是多个单一索引的组合**。

为什么要强调这一点，是因为复合索引的作用方式在于不断地圈定范围，从左到右（**左前缀匹配原则**，下面会介绍），**在前面索引查找的基础之上再进行索引查找，这就是复合索引的作用本质**。

复合索引的性能要优于多个单一索引，即便是复合索引与多个单一索引都用到了相同的列。

# 二、左前缀原则及复合索引的生效情况 #

这一节我们假设有一个包含三个列（c1, c2, c3）的复合索引，来讨论一下复合索引的 “左前缀原则” 。

如果有一个复合索引，包含了三个列，想要在SQL查询中使用该索引，**必须使用复合索引靠左侧的列**。可以仅使用 c1，也可以使用 c1 和 c2 ，当然也可以使用 c1，c2，c3。这就是复合索引的**左前缀匹配原则**。

左前缀原则的索引生效，是由存储引擎对应的索引结构所决定的，在我们熟悉的 InnoDB 和 MyISAM 中，索引的数据类型是 BTree，正因为如此，我们才（一般）说索引是有序的，相对于其他的索引，比如哈希索引，我们同样可以通过哈希算法的特性了解到，这是一种随机的，无序的索引结构，它的作用在于存储更快。当然这些稍微有点题外话。

左前缀原则还有一些需要讨论的应用细节，仅仅通过 “必须使用靠左侧的列” 还是会在实际操作中存在疑惑。

如果只要求让复合索引生效，那只要使用第一个列，我们就可以说这个复合索引生效了。但生效并不意味着最快，复合索引有其本身的复杂度，如果想发挥出复合索引做大的性能效果，就需要尽可能的（在实际需求范围内）使用复合索引中更多的列。

在 WHERE 子句中，我们对一个查询进行筛选，**如何能充分发挥复合索引的作用呢？**

**案例1：复合索引的最大化**

WHERE c1 = x AND c2 = y AND c3 = z;

上述条件子句可以让包含 c1，c2，c3 的复合索引发挥最大的效果，当然，他们在 WHERE 子句中的书写顺序无关紧要，你也可以这样写：

WHERE c3 = x AND c2 = y AND c1 = z;

MySQL会自动对SQL语句进行优化，存储引擎依然会按照先查找 c1 再查找 c2 最后查找 c3 的顺序来执行，保证索引生效。

**案例2：左前缀匹配的生效情况**

WHERE c1 = x AND c3 = z;

上述条件中针对 c1 和 c3 进行了筛选，但根据左前缀匹配原则，上述子句并没有“靠左使用索引列”，中间跳过了 c2 ，因此这个筛选条件只用到了复合索引中的 c1 列，虽然复合索引生效，但是性能并不是最好的。

**案例3：范围查询（包括 >、<=、<>、like等）中复合索引的生效情况**

WHERE c1 > x AND c2 = y AND c3 = z;

上述条件中，c1 是范围查询，而 c2、c3 都是等值查询，这种情况依然只使用了复合索引的 c1 索引列。而c2、c3 的索引列并未生效。

WHERE c1 = x AND c2 < y AND c3 = z;

上述条件和前一种比较类似，在筛选条件中都用到了范围查询，但不同的是 c2 是范围查询，这时复合索引生效，但只用到了 c1 和 c2 索引列，并未使用 c3 索引列。

> 通过这两个例子，我们也可以发现，复合索引的各个索引列应该是一种从左到右逐级筛选的关系，因此，**在建立复合索引的时候也要充分考虑存储数据的列与列的关系，找到当列等于某个值时，数据范围依次缩小的列，将这样的列共同组成复合索引。**而且，**在查找不确定的列值时，会导致复合索引中后定义的（索引定义中右侧的，不是WHERE 子句中的右侧）索引列失效**。不过，失效并不意味着错误，根据具体的情况来讲，如果某个列真的是无法筛选等值的话，那么失效也是在所难免，应该辩证的去分析问题，一定不能为了追求索引的最大化，而导致业务逻辑错误！

另外，**IN() 函数也可以使索引正常生效，可以看做是一种多等值判断的情况。**

**案例4：order by 子句参与下的复合索引生效情况**

WHERE c1 = x AND c3 = z ORDER BY c2 ;

这条语句对 c2 列进行了排序，同时对 c1 和 c3 进行了等值筛选，通过左前缀匹配规则，复合索引中的 c1 索引列肯定是生效的，c3 索引列肯定是未生效的，而 c2 索引列有没有生效呢？答案是否定的，也就是说，此条件中的复合索引仅有 c1 列生效，而 c2 和 c3 列都失效了。

关于order by 子句对索引的使用，情况比较复杂，我会在后面的学习中单独对其进行总结，因此最有效的方式还是结合 explain 来进行分析。

# 三、复合索引的创建语句 #

CREATE INDEX index_name ON table_name(col1, col2, col3) ;

例如，在 student 表中对年级、班级建立复合索引：

CREATE INDEX idx_grade_class ON student(grade_id, class_id);

![202001181003486.png][]

# 四、使用执行报告查看复合索引的使用情况 #

使用复合索引后我们如何看到效果？我认为可以通过两种方式。第一种是直接观察SQL的执行速度，增加复合索引前和之后执行的时间可以为我们提供参考依据。

那么第二种方式就是通过 explain 执行报告，分析复合索引的使用情况。

还是以 student 表为例，来观察不同的SQL语句的执行报告，MySQL版本：5.7.27-log

表结构如下：

CREATE TABLE `student` (
      `stu_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学生id',
      `grade_id` int(11) DEFAULT NULL COMMENT '年级id',
      `class_id` int(11) DEFAULT NULL COMMENT '班级id',
      `stu_name` varchar(20) DEFAULT NULL COMMENT '学生姓名',
      `stu_gender` char(1) DEFAULT NULL COMMENT '学生性别，1:男。2:女',
      `stu_age` int(11) DEFAULT NULL COMMENT '学生年龄',
      `address` varchar(50) DEFAULT NULL COMMENT '家庭住址',
      `enrollment_time` date DEFAULT NULL COMMENT '入学日期',
      PRIMARY KEY (`stu_id`),
      KEY `idx_grade_class` (`grade_id`,`class_id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=79 DEFAULT CHARSET=utf8

EXPLAIN SELECT * FROM student WHERE grade_id = 1 ;

id  select_type  table    partitions  type    possible_keys    key              key_len  ref       rows  filtered  Extra   
    --  -----------  -------  ----------  ------  ---------------  ---------------  -------  ------  ------  --------  -----
     1  SIMPLE       student  (NULL)      ref     idx_grade_class  idx_grade_class  5        const       28    100.00  (NULL)

EXPLAIN SELECT * FROM student WHERE grade_id = 1 AND class_id = 3 ;

id  select_type  table    partitions  type    possible_keys    key              key_len  ref            rows  filtered  Extra   
    --  -----------  -------  ----------  ------  ---------------  ---------------  -------  -----------  ------  --------  -----
     1  SIMPLE       student  (NULL)      ref     idx_grade_class  idx_grade_class  10       const,const       4    100.00  (NULL)

EXPLAIN SELECT * FROM student WHERE class_id = 3 ;

id  select_type  table    partitions  type    possible_keys  key     key_len  ref       rows  filtered  Extra        
    --  -----------  -------  ----------  ------  -------------  ------  -------  ------  ------  --------  -------------
     1  SIMPLE       student  (NULL)      ALL     (NULL)         (NULL)  (NULL)   (NULL)      78     10.00  Using where

EXPLAIN SELECT * FROM student WHERE grade_id IN(1, 2) AND class_id = 3 ;

id  select_type  table    partitions  type    possible_keys    key              key_len  ref       rows  filtered  Extra                  
    --  -----------  -------  ----------  ------  ---------------  ---------------  -------  ------  ------  --------  -----------------------
     1  SIMPLE       student  (NULL)      range   idx_grade_class  idx_grade_class  10       (NULL)       5    100.00  Using index condition

EXPLAIN SELECT * FROM student ORDER BY grade_id, class_id ;

id  select_type  table    partitions  type    possible_keys  key     key_len  ref       rows  filtered  Extra           
    --  -----------  -------  ----------  ------  -------------  ------  -------  ------  ------  --------  ----------------
     1  SIMPLE       student  (NULL)      ALL     (NULL)         (NULL)  (NULL)   (NULL)      78    100.00  Using filesort

还有最后一个，范围查找，比较有意思：

EXPLAIN SELECT * FROM student WHERE grade_id > 2;

id  select_type  table    partitions  type    possible_keys    key     key_len  ref       rows  filtered  Extra        
    --  -----------  -------  ----------  ------  ---------------  ------  -------  ------  ------  --------  -------------
     1  SIMPLE       student  (NULL)      ALL     idx_grade_class  (NULL)  (NULL)   (NULL)      78     32.05  Using where

EXPLAIN SELECT * FROM student WHERE grade_id > 3;

id  select_type  table    partitions  type    possible_keys    key              key_len  ref       rows  filtered  Extra                  
    --  -----------  -------  ----------  ------  ---------------  ---------------  -------  ------  ------  --------  -----------------------
     1  SIMPLE       student  (NULL)      range   idx_grade_class  idx_grade_class  5        (NULL)       1    100.00  Using index condition

EXPLAIN SELECT * FROM student WHERE grade_id >= 3;

id  select_type  table    partitions  type    possible_keys    key     key_len  ref       rows  filtered  Extra        
    --  -----------  -------  ----------  ------  ---------------  ------  -------  ------  ------  --------  -------------
     1  SIMPLE       student  (NULL)      ALL     idx_grade_class  (NULL)  (NULL)   (NULL)      78     32.05  Using where

EXPLAIN SELECT * FROM student WHERE grade_id < 3;

id  select_type  table    partitions  type    possible_keys    key     key_len  ref       rows  filtered  Extra        
    --  -----------  -------  ----------  ------  ---------------  ------  -------  ------  ------  --------  -------------
     1  SIMPLE       student  (NULL)      ALL     idx_grade_class  (NULL)  (NULL)   (NULL)      78     67.95  Using where

范围查找所用索引的情况很令人费解，我暂时还没找到合理的解释，这种现象可能和表中数据太少有关系？所以关于SQL优化的理论知识，一定要配合 explain 执行计划来学习，有时候板上钉钉的结论依然可能是错误的。

[202001181003486.png]: https://img-blog.csdnimg.cn/202001181003486.png