【MySQL】之联合索引与最左匹配原则

2023-12-10 06:52

本文主要是介绍【MySQL】之联合索引与最左匹配原则,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

前言:


最左匹配原则在我们 MySQL 开发过程中和面试过程中经常遇到,为了加深印象和理解,我在这里把 MySQL 的最左匹配原则详细的讲解一下,包括它的原理以及是否导致索引失效的场景。

在讲解 MySQL 的最左匹配原则之前,我们需要了解一下 MySQL 的联合索引也称复合索引),因为最左匹配原则是在联合索引的基础上产生的,没有联合索引就没有最左匹配原则这个概念。


一、联合索引


1、什么是联合索引

我们知道,单值索引指的是只使用一个字段作为索引字段的索引,而联合索引就是使用多个字段来共同构建成一个索引:

KEY idx_abc (a, b, c);

2、为什么要使用联合索引

2-1、减少开销

建一个联合索引 (a, b, c),实际相当于建了 (a)、(a, b)、(a, b, c) 三个索引。这样我们就不需要创建 (a)、(b)、(c) 三个单值索引了。我们知道,每多一个索引,都会增加数据库写操作的开销和磁盘空间的开销,对于大量数据的表,使用联合索引会大大的减少开销!

2-2、覆盖索引

对联合索引 (a, b, c),如果有如下的 sql: select a, b, c from test where a=1 and b=2。那么 MySQL 可以直接通过遍历索引取得数据,而无需回表,从而减少了很多的随机 IO 操作。而减少 IO 操作,特别的随机 IO 是 DBA 主要的优化策略,在真正的实际应用中,覆盖索引是主要的提升性能的优化手段之一。

2-3、提高效率

联合索引的字段越多,通过索引筛选出的数据越少。假如有 1000W 条数据的表,有如下 sql: select * from table where a=1 and b=2 and c=3,假设每个条件可以筛选出 10% 的数据,如果只有单值索引,那么通过该索引能筛选出 1000W * 10% = 100w 条数据,然后再回表从 100w 条数据中找到符合 b=2 and c=3 的数据,然后再排序,再分页。

但如果是联合索引,则通过索引直接筛选出的数据为:1000w * 10% * 10% * 10% = 1w,这效率的提升可想而知!


二、最左匹配原则


1、最左匹配原则的规则

在联合索引当中,索引匹配时:最左字段优先,以最左边的字段为起点任何连续的字段索引都能匹配上,如果遇到范围查询 (>、<、between、like) 时就会停止匹配

2、索引是否生效的场景

是否满足最左匹配原则是衡量联合索引命中与否的依据。存在场景比较多,假设我们创建了以 a, b, c 三个字段的联合索引 idx_abc(a, b, c),下面我们分别展开讨论索引是否失效的场景。

2-1、全字段全值匹配

索引的全部字段都在查找条件当中,并且都是使用 = 进行全值匹配的情况下,索引是命中生效的:

select * from table_name where a = '1' and b = '2' and c = '3'
select * from table_name where b = '2' and a = '1' and c = '3'
select * from table_name where c = '3' and b = '2' and a = '1'
......

虽然 where 子句几个搜索条件顺序调换了,但不影响查询结果,这是由于 MySQL 的查询优化器会自动调整 where 子句的条件顺序以使用适合的索引,所以 MySQL 不存在 where 子句的顺序问题而造成索引失效。

2-2、从左到右按顺序匹配

select * from table_name where a = '1'
select * from table_name where a = '1' and b = '2'
select * from table_name where a = '1' and b = '2' and c = '3'

只要是按照联合索引创建的字段从左到右的顺序依次使用,不管使用其中多少个字段,都会命中索引。

2-3、缺失最左边的字段

select * from table_name where  b = '2' 
select * from table_name where  c = '3'
select * from table_name where  b = '1' and c = '3' 

这种缺失了最左边 a 字段的情况就是违背最左匹配原则的典型例子,结果就是没有用到索引(索引失效)。

因为缺失了最左边的字段,导致索引数据结构 B+ 树不知道第一步该查哪个节点,从而需要去全表扫描了。在建立搜索树的时候 a 就是第一个比较因子,必须要先根据 a 来搜索,进而才能往后继续查询 b 和 c。

2-4、缺失中间的字段

假如去掉中间的字段,保留最左边和右边的字段(就是我们说的索引字段不连续):

select * from table_name where a = '1' and c = '3' 

结果就只用到了 a 列的索引,而 b 列和 c 列都没有用到。

因为在这种情况下进行数据检索时,B+ 树可以用 a 来指定第一步的搜索方向,但由于下一个字段 b 的缺失,所以只能先把 a = 1 的数据主键 ID 都找出来,然后通过查到的主键 ID 回表查询相关行,再去匹配 c 值的数据了。当然,这至少把 a = 1 的数据筛选出来了,总比直接全表扫描好多了

2-5、匹配范围值

出现匹配范围值的情况可能比较复杂或难以理解,但我们只需要牢记最左匹配原则的规则:遇到范围查询 (>、<、between、like) 时就会停止匹配

比如下面这种情况:

select * from table_name where  a = 1 and b > 3 and c = 'mm';

这种情况下,由于 a 是等值匹配,所以 B+ 树走完 a 索引之后 b 还是有序的,但走完 b 索引之后,由于 b 是范围匹配,所以此时 c 已经是无序的了,最终只使用了 (a, b) 两个索引(由于此时 c 就没法走索引,所以优化器只能根据 a, b 得到数据的主键 ID 回表查询,最终影响了执行效率)。

再比如下面的情况:

select * from table_name where  a > 1 and b > 1
select * from table_name where  a > 1 and a < 3 and b > 1;

当多个列同时进行范围查找时,只有对索引最左边的那个列进行范围查找才用到 B+ 树索引,也就是只有 a 用到索引,在 a > 11 < a < 3 的范围内 b 是无序的,所以 b 不能用索引,找到 a 的记录后,只能根据条件 b > 1 继续逐条过滤。

2-6、like 语句匹配问题

当索引列是字符型,并且使用了 like 语句进行模糊查询时,如果通配符 % 不出现在开头,则可以用到索引,否则将会违背了最左匹配原则,而不会使用索引,走的是全表扫描:

select * from table_name where a like 'As%';  //走索引查询
select * from table_name where  a like '%As'  //全表查询
select * from table_name where  a like '%As%' //全表查询

我们先了解一下字符型字段的比较规则:当列是字符型的话,它的比较规则是先比较字符串的第一个字符,第一个字符小的那个字符串就比较小,如果两个字符串第一个字符相通,那就再比较第二个字符,第二个字符比较小的那个字符串就比较小,依次类推,比较字符串。

所以,如果通配符 % 出现在开头,B+ 树则无法进行比较匹配,进而导致索引失效。

3、解决文件排序的问题

当我们对查询的数据进行 order by 排序时,一般情况下,我们是先把数据记录加载到内存中,再用一些排序算法,比如快速排序,归并排序等在内存中对这些记录进行排序。但有时候查询的结果集太大不能在内存中进行排序时,需要暂时借助磁盘空间存放中间结果,排序操作完成后再把排好序的结果返回客户端。Mysql 把这种在磁盘上进行排序的方式称为文件排序(Filesort)。

文件排序是非常慢非常耗性能的,但如果 order by 子句用到了索引列,就有可能避免文件排序的问题:

select * from table_name order by a, b, c limit 10;

因为 B+ 树索引本身就是按照上述规则排序的,准确来说就是:索引是有序的,所以得到的结果集已经排好序了,不用再进行额外的排序操作。

注意:order by 的子句后面的字段顺序也必须按照索引字段的顺序给出,不能颠倒顺序(MySQL 不会自动调整排序字段的顺序)。

下面这种就是因为颠倒顺序而没有使用索引的情况:

select * from table_name order by b, c, a limit 10;

下面这种是用到部分索引的情况:

select * from table_name order by a limit 10;
select * from table_name order by a, b limit 10;

下面这种情况,由于联合索引左边列为常量,后边的列排序可以用到索引:

select * from table_name where a =1 order by b, c limit 10;

这篇关于【MySQL】之联合索引与最左匹配原则的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/476340

相关文章

MySQL 8 中的一个强大功能 JSON_TABLE示例详解

《MySQL8中的一个强大功能JSON_TABLE示例详解》JSON_TABLE是MySQL8中引入的一个强大功能,它允许用户将JSON数据转换为关系表格式,从而可以更方便地在SQL查询中处理J... 目录基本语法示例示例查询解释应用场景不适用场景1. ‌jsON 数据结构过于复杂或动态变化‌2. ‌性能要

MySQL字符串常用函数详解

《MySQL字符串常用函数详解》本文给大家介绍MySQL字符串常用函数,本文结合实例代码给大家介绍的非常详细,对大家学习或工作具有一定的参考借鉴价值,需要的朋友参考下吧... 目录mysql字符串常用函数一、获取二、大小写转换三、拼接四、截取五、比较、反转、替换六、去空白、填充MySQL字符串常用函数一、

MySQL中比较运算符的具体使用

《MySQL中比较运算符的具体使用》本文介绍了SQL中常用的符号类型和非符号类型运算符,符号类型运算符包括等于(=)、安全等于(=)、不等于(/!=)、大小比较(,=,,=)等,感兴趣的可以了解一下... 目录符号类型运算符1. 等于运算符=2. 安全等于运算符<=>3. 不等于运算符<>或!=4. 小于运

虚拟机Centos7安装MySQL数据库实践

《虚拟机Centos7安装MySQL数据库实践》用户分享在虚拟机安装MySQL的全过程及常见问题解决方案,包括处理GPG密钥、修改密码策略、配置远程访问权限及防火墙设置,最终通过关闭防火墙和停止Net... 目录安装mysql数据库下载wget命令下载MySQL安装包安装MySQL安装MySQL服务安装完成

MySQL进行数据库审计的详细步骤和示例代码

《MySQL进行数据库审计的详细步骤和示例代码》数据库审计通过触发器、内置功能及第三方工具记录和监控数据库活动,确保安全、完整与合规,Java代码实现自动化日志记录,整合分析系统提升监控效率,本文给大... 目录一、数据库审计的基本概念二、使用触发器进行数据库审计1. 创建审计表2. 创建触发器三、Java

MySQL逻辑删除与唯一索引冲突解决方案

《MySQL逻辑删除与唯一索引冲突解决方案》本文探讨MySQL逻辑删除与唯一索引冲突问题,提出四种解决方案:复合索引+时间戳、修改唯一字段、历史表、业务层校验,推荐方案1和方案3,适用于不同场景,感兴... 目录问题背景问题复现解决方案解决方案1.复合唯一索引 + 时间戳删除字段解决方案2:删除后修改唯一字

Zabbix在MySQL性能监控方面的运用及最佳实践记录

《Zabbix在MySQL性能监控方面的运用及最佳实践记录》Zabbix通过自定义脚本和内置模板监控MySQL核心指标(连接、查询、资源、复制),支持自动发现多实例及告警通知,结合可视化仪表盘,可有效... 目录一、核心监控指标及配置1. 关键监控指标示例2. 配置方法二、自动发现与多实例管理1. 实践步骤

MySQL 主从复制部署及验证(示例详解)

《MySQL主从复制部署及验证(示例详解)》本文介绍MySQL主从复制部署步骤及学校管理数据库创建脚本,包含表结构设计、示例数据插入和查询语句,用于验证主从同步功能,感兴趣的朋友一起看看吧... 目录mysql 主从复制部署指南部署步骤1.环境准备2. 主服务器配置3. 创建复制用户4. 获取主服务器状态5

SpringBoot中六种批量更新Mysql的方式效率对比分析

《SpringBoot中六种批量更新Mysql的方式效率对比分析》文章比较了MySQL大数据量批量更新的多种方法,指出REPLACEINTO和ONDUPLICATEKEY效率最高但存在数据风险,MyB... 目录效率比较测试结构数据库初始化测试数据批量修改方案第一种 for第二种 case when第三种

MySql基本查询之表的增删查改+聚合函数案例详解

《MySql基本查询之表的增删查改+聚合函数案例详解》本文详解SQL的CURD操作INSERT用于数据插入(单行/多行及冲突处理),SELECT实现数据检索(列选择、条件过滤、排序分页),UPDATE... 目录一、Create1.1 单行数据 + 全列插入1.2 多行数据 + 指定列插入1.3 插入否则更