MySQL最左匹配原则

ops/2024/9/20 4:01:41/ 标签: mysql, 数据库, 后端, sql, 面试, 跳槽, 考研

MySQL索引的加左原则,也被称为最左匹配原则(Leftmost Prefix Rule)或最左前缀规则(Leftmost Prefixes),是指在创建复合索引时,应将经常用于查询的列放在索引的最左边,以便MySQL能够更有效地使用该索引来优化查询性能。

为什么要遵循加左原则?

MySQL索引的加左原则(Leftmost Prefix Rule)是由于其B-Tree索引的数据结构和存储方式决定的。

B-Tree索引是一种平衡的多路查找树,它将数据存储在一个有序的结构中,允许快速查找、插入和删除操作。每个叶子节点包含了对应的行数据指针或行数据本身,而非叶子节点则包含了索引值和指向子树的指针。

在复合索引中,MySQL会将多个列的值组合成一个键来进行排序和查找。这个键的排序方式是从左到右的,也就是说,MySQL首先会比较这个复合键的最左边一列,然后是第二列,以此类推。

当MySQL执行一个查询时,它会尝试使用索引来加速查找。它会从索引的最左边开始匹配查询条件,并逐步向右移动。如果查询条件与索引的某一列不匹配,MySQL就无法继续使用该索引来过滤数据了。

以下是几个原因,解释了为什么MySQL索引有加左原则:

数据排序:B-Tree索引是按照复合键的顺序进行排序的,首先是最左边的列。如果你不遵循加左原则,索引的排序方式就与查询条件的顺序不匹配,导致MySQL无法有效地使用索引。

索引范围扫描:当你在WHERE子句中使用范围查询(如WHERE col1 > 10 AND col1 < 20)时,MySQL可以使用索引的最左边一列来确定需要扫描的索引范围。只有当查询条件与索引的最左边一列匹配时,MySQL才可以执行索引范围扫描。

索引选择性:选择性是指索引中不同值的数量与总行数的比率。最左边的列应该是选择性最高的列,这样可以使MySQL更快地排除不符合条件的行。

索引覆盖:如果你的查询只涉及到复合索引中的一部分列,并且这些列正好是最左边的几列,那么MySQL可以直接从索引中获取所需的数据,而不需要回表查找。这种情况被称为“索引覆盖”,可以极大地提高查询效率。

索引合并:在某些情况下,MySQL可能会使用多个索引来优化查询。最左匹配原则可以帮助MySQL更好地选择和合并这些索引。

总之,遵循加左原则可以使MySQL更好地利用B-Tree索引的特性,提高查询效率。然而,索引设计是一个复杂的过程,需要根据具体的查询模式和数据分布来决定最佳的索引策略。

遵循最左匹配原则可以帮助MySQL更有效地使用复合索引来加速查询。以下是几个原因:

减少查找的范围:当MySQL从索引的最左边开始匹配时,它可以快速缩小需要查找的数据范围,从而提高查询速度。

避免全表扫描:如果不遵循最左匹配原则,MySQL可能需要进行全表扫描来找到符合条件的数据,这将大大降低查询性能。

提高索引的选择性:选择性是指索引中不同值的数量与总行数的比率。如果最左边的列有很高的选择性,MySQL可以更准确地估计数据分布,选择更好的执行计划。

举例说明:

假设我们有一个名为users的表,包含nameageaddress三列,并且我们创建了一个复合索引(name, age)

  1. 完全匹配:如果查询语句是SELECT * FROM users WHERE name = 'John' AND age = 30;,MySQL会从索引的最左边开始匹配,首先匹配name列,然后匹配age列。因为查询条件与索引的最左边两列完全匹配,所以MySQL可以使用整个复合索引来快速定位数据。

  2. 部分匹配:如果查询语句是SELECT * FROM users WHERE age = 30 AND name = 'John';,MySQL仍然会从索引的最左边开始匹配,但它首先尝试匹配age列,因为age在索引的最左边。由于age列不是完全匹配的第一个条件,MySQL只能使用索引的部分(即age列)来过滤数据,剩余的条件(name = 'John')需要在过滤后的结果集中进行额外的检查。

  3. 不匹配:如果查询语句是SELECT * FROM users WHERE address = 'New York';,MySQL无法使用这个复合索引来优化查询,因为address列不在索引的最左边。

如何应用加左原则?

  1. 分析查询语句:在创建复合索引之前,仔细分析你的查询语句,确定哪些列经常被用作查询条件。

  2. 选择正确的顺序:将这些列按照使用频率或选择性从高到低的顺序排列在复合索引中。选择性是指索引中不同值的数量与总行数的比率,选择性越高,索引越有用。

  3. 避免使用函数或表达式:在索引中使用函数或表达式会使MySQL无法使用索引的最左匹配原则。尽量在查询语句中避免对索引列使用函数或表达式。

  4. 注意索引类型:不同的索引类型(如B-Tree索引、Hash索引)对最左匹配原则的适用性不同。B-Tree索引是最常用的索引类型,也是唯一支持范围查询的索引类型,适合大多数情况。

  5. 使用EXPLAIN分析:使用EXPLAIN语句来分析你的查询语句,了解MySQL是如何使用索引的。如果发现MySQL不是使用你预期的索引,可能需要重新考虑你的索引策略。

例外情况

使用全文索引:全文索引不遵循最左匹配原则,因为它们是用来支持复杂的文本搜索的。

使用覆盖索引:覆盖索引包含了查询语句所需的所有列,MySQL可以直接从索引中获取数据,而不需要访问表数据。在这种情况下,不需要遵循最左匹配原则。

注意事项

  1. 不要过度索引:虽然复合索引可以提高查询效率,但过度索引会增加写入操作的开销,并可能导致索引维护的性能问题。

  2. 适当调整索引顺序:如果你的查询模式发生了变化,可能需要重新评估并调整索引的顺序。

  3. 考虑使用单列索引:对于某些列,如果它们经常单独用作查询条件,或者作为WHERE子句中的第一个条件,可能更适合使用单列索引而不是复合索引。

使得索引失效或效果不佳的情况:

  1. 没有使用最左边的索引列:如果你的查询条件不包含复合索引的最左边一列,MySQL就不能使用这个索引来过滤数据。例如,假设你有一个复合索引(col1, col2, col3),但你的查询语句是SELECT * FROM table WHERE col2 = 'value2' AND col3 = 'value3';,那么MySQL可能不会使用这个复合索引。

  2. 在最左边的索引列上使用不等值条件:如果你在最左边的索引列上使用了不等值条件(如WHERE col1 > 'value1'),MySQL可能会选择不使用索引,而进行全表扫描。因为在B-Tree索引中,等值条件可以帮助MySQL快速定位到需要查找的数据范围。

  3. 在最左边的索引列上使用函数或表达式:如果你在最左边的索引列上使用了函数或表达式(如WHERE LEFT(col1, 3) = 'abc'),MySQL也不能使用索引。因为索引是基于原始值建立的,而不是基于函数或表达式的结果。

  4. 在最左边的索引列上使用LIKE查询且通配符在前面:如果你的LIKE查询的通配符在前面(如WHERE col1 LIKE '%value1'),MySQL同样不能使用索引。因为通配符在前面,MySQL无法确定从哪里开始查找。

  5. 使用OR条件连接索引列:如果你在WHERE子句中使用OR条件来连接不同的索引列(如WHERE col1 = 'value1' OR col2 = 'value2'),MySQL可能会选择不使用索引,而进行全表扫描。因为OR条件会扩大查询结果的范围,MySQL很难通过索引来确定哪些行符合条件。

  6. 索引列的选择性太低:如果最左边的索引列的选择性非常低(即大多数行都有相同的值),那么MySQL可能会选择不使用索引,而直接扫描表数据。

  7. 索引列顺序与查询条件顺序不一致:如果你的复合索引列的顺序与查询条件的顺序不一致,MySQL可能会选择不使用索引,或者使用索引但效果不佳。例如,假设你有一个复合索引(col1, col2),但你的查询语句是SELECT * FROM table WHERE col2 = 'value2' AND col1 = 'value1';,那么MySQL可能不会使用这个复合索引。

  8. 索引列中存在NULL值:如果最左边的索引列中存在NULL值,MySQL可能会选择不使用索引。因为NULL值的存在使得MySQL无法确定索引的顺序和范围。

  9. 使用了不等于(!=)或NOT IN操作符:这些操作符会使得MySQL无法确定从哪里开始或结束扫描索引,因此可能会选择不使用索引。

以上这些情况都可能导致MySQL索引失效或效果不佳。如果你发现自己的查询语句没有使用到预期的索引,可以使用EXPLAIN语句来分析查询计划,找出原因,并进行相应的优化。记住,索引设计是一个动态的过程,需要根据实际的查询模式和数据分布来调整。


http://www.ppmy.cn/ops/95704.html

相关文章

【数据结构与算法】冒泡排序

冒泡排序目录 一.冒泡排序原理二.图示三.冒泡排序具体实现四.冒泡排序升级版五.完整代码 一.冒泡排序原理 还是老样子,我们如何对这个进行排序呢? 冒泡排序的原理是,将两两进行比较,如果前面较大的我们就进行交换到后面. 然后再对交换后的这个和下一个进行比较,一轮过后,最大值…

微信小程序模板与配置(一)wxml模板语法渲染页面结构

一、数据绑定 基本原则&#xff1a;在data中定义数据&#xff0c;在wxml中使用数据 1、在data中定义页面的数据 在页面对应的.js文件中&#xff0c;把数据定义到data对象中即可&#xff1a; /*** 页面的初始数据*/data: {info: hello world}, 输出结果&#xff1a; 2、M…

15:【stm32】时钟树

时钟树 1、时钟树1.1&#xff1a;简要的介绍1.2&#xff1a;基本结构1.2.1&#xff1a;stm32的内部结构1.2.2&#xff1a;树的关键节点1.2.3&#xff1a;系统时钟的来源 2、RCC标准库编程2.1&#xff1a;片上外设的复位与释放2.2&#xff1a;时钟系统配置2.3&#xff1a;时钟树…

ElasticSearch的Mapping和DSL语言

ElasticSearch的Mapping和DSL语言 文章目录 ElasticSearch的Mapping和DSL语言mapping(映射)动态映射类型自动识别 静态映射关于后期更改mapping字段类型&#xff1f;重建索引步骤 常用mapping参数配置index_template 和mapping_template Query DSL数据初始化无条件查询&#xf…

Jedis的使用以及Java和redis的连接

一、Jedis 我们要使用 Java 来操作 Redis&#xff0c;什么是Jedis 是 Redis 官方推荐的 java连接开发工具&#xff01; 使用Java 操作 Redis 中间件&#xff01; 1、导入对应的依赖 <!--导入jedis的包--> <dependencies><!-- https://mvnrepository.com/artif…

haproxy实验

基本介绍 http://t.csdnimg.cn/Z64QVhttp://t.csdnimg.cn/Z64QV 实验环境 功能IP客户端172.25.254.233haproxy eth0:172.25.254.100 eth1:192.168.0.10 rs1eth0:192.168.0.101rs2eth0:192.168.0.102 安装haproxy yum install haproxy -y 多进程和多线程 vim /etc/hap…

关于xilinx的FFTIP的使用和仿真

工具&#xff1a;vivado2018.3&#xff0c;modelsim10.6d 场景&#xff1a;在进行数据进行频谱分析的时候&#xff0c;使用FPGA来完成FFT的计算可以加快数据的计算速度。 下面使用仿真完成DDS产生的数据的FFT以及IFFT。原始数据使用DDSIP产生&#xff0c;通过IP产生的波形数据…

一次不严谨的C++、C、Pascal、Rust等对比

起因 现在ACM用得多的基本上就两种语言&#xff0c;C和Python3&#xff0c;还有部分Java&#xff0c;但是当年ACM必学的Pascal、新近流行的rust也有人用&#xff0c;只不过用户很少。 就以一道codeforce上的算法小题为样本&#xff0c;来对比一样用户数量、执行效率、易写程度…

浙大数据结构慕课课后题(06-图3 六度空间)

题目要求&#xff1a; 输入格式: 输入第1行给出两个正整数&#xff0c;分别表示社交网络图的结点数N&#xff08;1<N≤103&#xff0c;表示人数&#xff09;、边数M&#xff08;≤33N&#xff0c;表示社交关系数&#xff09;。随后的M行对应M条边&#xff0c;每行给出一对正…

【LeetCode】无重复字符的最长子串

给定一个字符串 s &#xff0c;请你找出其中不含有重复字符的最长子串的长度。 示例 1: 输入: s “abcabcbb” 输出: 3 解释: 因为无重复字符的最长子串是 “abc”&#xff0c;所以其长度为 3。 示例 2: 输入: s “bbbbb” 输出: 1 解释: 因为无重复字符的最长子串是 “b”&…

截屏工具:PixPin

省流&#xff1a;使用 PixPin 截图&#xff0c;设置格式为 JPG。 ‍ 一、介绍 PixPin ‍ 一个好用的截图工具。 官网&#xff1a;https://pixpinapp.com/ ​​ ‍ 支持功能&#xff1a; 截图。支持界面元素识别&#xff0c;支持 CtrlC​ 复制到剪贴板&#xff0c;Ctrl…

docker做Llm开发时可能会遇到的问题

如果没有开启GPU&#xff0c;会报错 docker: Error response from daemon: could not select device driver "" with capabilities: [[gpu]]. 原因可能是 &#xff1a;没有安装 GPU Docker 运行时 则按照如下参照安装&#xff0c; 基于 Docker 的深度学习环境&…

【数据结构与算法】选择排序

选择排序目录 一.问题引入二.图解三.具体实现四.每次找最小值实现五.完整代码 一.问题引入 假设我们想要将此排序按从小到大,那么我们可以怎么排呢? 我们可以每次从里面选择最大的,放在最后面,继续选剩下的最大的放在刚刚放在最后面的前面一个. 每次选择最小的放在前面也是同样…

57qi5rW35LqRZUhS pc.mob SQL注入漏洞复现

0x01 产品简介 57qi5rW35LqRZUhS是大中型企业广泛采用人力资源管理系统。某云是国内顶尖的HR软件供应商,是新一代eHR系统的领导者。 0x02 漏洞概述 57qi5rW35LqRZUhS pc.mob 接口存在SQL注入漏洞,未经身份验证的远程攻击者除了可以利用 SQL 注入漏洞获取数据库中的信息(例…

政务大数据解决方案(六)

政务大数据解决方案通过构建全面的数据集成平台&#xff0c;将来自不同政府部门和公共服务领域的多维度数据汇聚起来&#xff0c;运用先进的数据分析和人工智能技术进行深度挖掘与预测&#xff0c;从而为政府提供实时、精准的信息支持&#xff0c;优化决策流程&#xff0c;提高…

JDBC连接Mysql

一、JDBC实战技术 1.1JDBC概念 JDBC&#xff08;Java DataBase Connectivity&#xff1a;java数据库连接&#xff09;是一种用于执行SQL语句的Java API&#xff0c;可以为多种关系型数据库提供统一访问&#xff0c;它是由一组用Java语言编写的类和接口组成的。JDBC的作用&…

C语言—函数递归

一、递归概念 递归其实是⼀种解决问题的⽅法&#xff0c;在C语⾔中&#xff0c;递归就是函数⾃⼰调⽤⾃⼰。下面举一个例子&#xff1a; 上述就是⼀个简单的递归程序&#xff0c;只不过上⾯的递归只是为了演⽰递归的基本形式&#xff0c;不是为了解决问题&#xff0c;代码最终…

gitlab设置中文

Centos7&#xff08;最小化&#xff09;系统安装部署私人gitlab远程仓库-CSDN博客 目录 登录gitlab 点击edit profile 点击preferences&#xff08;偏好&#xff0c;爱好&#xff09;&#xff0c; 下滑找到Localization&#xff08;本地&#xff09; 点击language&#xf…

redis五种数据结构以及一些基本操作

Redis 支持五种基本的数据结构&#xff0c;这些数据结构使得 Redis 能够灵活应用于各种场景。 字符串&#xff08;Strings&#xff09; 是 Redis 中最基本的数据类型&#xff0c;可以被理解为简单的 key-value 对&#xff0c;其中 value 是字符串。字符串类型的数据可以是任何…

为什么要有二级指针

提示&#xff1a;文章 文章目录 前言一、背景二、 2.1 2.2 总结 前言 前期疑问&#xff1a; 本文目标&#xff1a; 一、背景 之前一直疑问为什么要有二级指针&#xff0c;一直没有写这个帖子&#xff0c;今天整理了一下&#xff0c;收获颇丰 二、 2.1 // 增加对二级指针…