MySQL 高级 —— 复合索引简介(多列索引)

引言

复合索引是指包含多个数据列的索引,与之概念相对的是单列索引,仅包含一个数据列。在大多数情况下,建立多列索引的好处都要多于单列索引。另外,复合索引最多支持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);

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

使用复合索引后我们如何看到效果?我认为可以通过两种方式。第一种是直接观察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 执行计划来学习,有时候板上钉钉的结论依然可能是错误的。

 

已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 编程工作室 设计师:CSDN官方博客 返回首页