Mysql 索引失效-最左匹配原则

最新推荐文章于 2024-02-21 23:42:44 发布
原创 最新推荐文章于 2024-02-21 23:42:44 发布 · 1.4k 阅读 · 0 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#mysql

本文探讨Mysql中复合索引的最左匹配原则，解释为何某些查询无法使用已建立的索引，通过实例说明(a,b,c)索引下，(a),(a,b),(a,b,c)查询有效，而(b,c)查询无效。

Mysql 索引失效-最左匹配原则

我们在建立复合索引的时候，常常会出现索引失效的问题。这时，我们可能就违背了最左原则。
例如：我创建的学生表
在这里插入图片描述
创建了一个组合索引(N_STUDENT_ID,VC_STUDENT_CODE,VC_STUDENT_NAME)
在这里插入图片描述
执行查询，索引的三个列都有

EXPLAIN select * from t_student where N_STUDENT_ID=1 and VC_STUDENT_CODE=00001 and VC_STUDENT_NAME=‘王亮’

查看查询执行计划
在这里插入图片描述
这里索引被用到。
再执行查询，索引其中1,2列：

EXPLAIN select * from t_student where N_STUDENT_ID=1 and VC_STUDENT_CODE=00001
这里索引也是用到了这里

在进行查询，用到2,3列

EXPLAIN select * from t_student where VC_STUDENT_CODE=00001 and VC_STUDENT_NAME=‘王亮’
这时发现索引失效了在这里插入图片描述

上述例子说明，MySQL组合索引遵循最左匹配原则，若有索引(a,b,c)
那么(a),(a,b),(a,b,c)有效，即从最左边开始的连续索引有效。

【项目实战】MySQL的复合索引与最左匹配原则
本本本添哥
01-29 467
最左匹配原则是指MySQL在查询时，会从复合索引的最左列开始匹配。在使用复合索引时，必须从索引的最左边列开始匹配。最左匹配原则意味着，当执行查询时，只有当SQL语句中包含了联合索引中最左边的列，并且这些列是以建立索引时的顺序来引用时，索引才会被使用。如果在查询中跳过了最左边的列，或者不以建立索引时的列顺序来引用，那么即便后面的列在索引中，也不会使用到这个索引。
Mysql-最左前缀法则以及索引失效情况
m0_56642224的博客
04-29 2239
如果索引了多列（联合索引），要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列，索引将会部分失效(后面的字段索引失效)。以 tb_user 表为例，我们先来查看一下之前 tb_user 表所创建的索引~在上述的表tb_user中，我们设置了一个联合索引，这个联合索引涉及到三个字段，顺序分别为：profession，age，status对于最左前缀法则指的是，查询时，最左边的列，也就是profession必须存在，否则索引全部失效。
mysql最左匹配原则与失效
狗蛋的博客
08-31 2382
mysql最左匹配原则
Mysql索引失效原理与最左匹配原则
tc979907461的博客
05-27 3432
索引的底层是一颗B+树，那么联合索引当然还是一颗B+树，只不过联合索引的健值数量不是一个，而是多个。构建一颗B+树只能根据一个值来构建，因此数据库依据联合索引最左的字段来构建B+树。 例子：假如创建一个（a,b)的联合索引，那么它的索引树是这样的 可以看到a的值是有顺序的，1，1，2，2，3，3，而b的值是没有顺序的1，2，1，4，1，2。所以b = 2这种查询条件没有办法利用索引，因为联合索引首先是按a排序的，b是无序的。 同时我们还可以发现在a值相等的情况下，b值又是按顺序排列的，但是这种顺序是相对的
【详解】面试必问：MySQL索引为什么会失效？最左匹配原则中间有间隙，为什么索引不会被命中？%开头索引为什么失效？范围查询索引为什么会失效？
CodingLJ
11-15 3193
首先看看索引的基本操作、索引的底层原理，先看这2篇文章： 【详解】MySQL索引的基本操作，索引（主键索引，普通索引，组合索引，唯一索引）_CodingLJ-优快云博客【详解】面试必问：MySQL索引底层原理（基于B+Tree）_CodingLJ-优快云博客 1、基础知识 知识补充： 多路搜索树是有序的，这个是数据结构中树的部分内容。以二叉搜索树为例，说下搜索树的特点，根节点的值始终大于左孩子的值，根结点的值始终小于右孩子结点的值；当然左右孩子也有这样的规律。 ...
mysql索引最左原则问题
weixin_33747129的博客
04-23 282
2019独角兽企业重金招聘Python工程师标准>>> ...
【MySQL】之联合索引与最左匹配原则
王廷云的博客
12-10 4353
最左匹配原则在我们 MySQL 开发过程中和面试过程中经常遇到，为了加深印象和理解，我在这里把 MySQL 的最左匹配原则详细的讲解一下，包括它的原理以及是否导致索引失效的场景。
精选资源
MySQL组合索引与最左匹配原则详解
01-19
之前在网上看到过很多关于mysql联合索引最左前缀匹配的文章，自以为就了解了其原理，最近面试时和面试官交流，发现遗漏了些东西，这里自己整理一下这方面的内容。 什么时候创建组合索引? 当我们的where查询存在多个...
MySQL联合索引最左匹配原则使用详解
个人博客，欢迎收藏。
01-05 1913
MySQL联合索引最左匹配原则
mysql-实用-索引失效和优化原则
07-23
mysql-实用-索引失效和优化原则
mysql最左侧原则失效,mysql索引失效的几种情况分析
weixin_39693662的博客
03-18 1250
1、最佳左前缀原则——如果索引了多列，要遵守最左前缀原则。指的是查询要从索引的最左前列开始并且不跳过索引中的列。前提条件：表中已添加复合索引(username,password,age)分析：该查询缺少username，查询条件复合索引最左侧username缺少，违反了最佳左前缀原则，导致索引失效，变为ALL，全表扫描分析：查询条件缺少username，password，查询条件复合索引最左侧us...
MySQL 索引失效-模糊查询，最左匹配原则，OR条件等。
Iamxuefather的博客
10-25 751
索引失效 介绍 索引失效就是我们明明在查询时的条件为索引列(包括自己新建的索引)，但是索引不能起效，走的是全表扫描。explain 后可查看type=ALL。 这是为什么呢？ 首先介绍有以下几种情况索引会出现失效： 当我们使用了左模糊匹配和左右模糊匹配的时候，像like ‘%str’或者‘%str%’。 当我们使用联合索引没有遵守最左匹配原则的时候。 当我们使用索引时对其索引字段进行计算、...
MySQL组合索引，最左匹配原则失效
小何的博客
11-30 1147
MySQL组合索引，最左匹配原则失效
备战2024秋招面试题-最左匹配原则、索引失效情况、算法（最长回文子串）
奔走的王木木Sir的博客
08-14 2883
数据库中的最左匹配原则、索引失效情况； 算法题：最长回文子串
Mysql复合索引最左匹配原则以及索引失效条件
xiaoxiao48的博客
04-09 6586
Mysql复合索引最左匹配原则以及索引失效条件复合索引最左匹配原则用EXPLAIN 来查看语句是否用到了索引索引失效的条件一般性建议 复合索引最左匹配原则 复合索引又叫联合索引。两个或更多个列上的索引被称作复合索引。 对于复合索引：Mysql从左到右的使用索引中的字段，一个查询可以只使用索引中的一部分，但只能是最左侧部分。例如索引是key index （a,b,c）。可以支持a | a,b| a,...
什么情况下索引会失效? 最左匹配法则
无限进步
02-21 734
在设计复合索引和编写查询语句时，应考虑到这一原则，以确保索引能够发挥最大的效能。二, 模糊查询，如果%号在前面也会导致索引失效。如果在添加索引的字段上进行了运算操作或者类型转换也都会导致索引失效。所以，通常情况下，想要判断出这条sql是否有索引失效的情况，可以使用explain执行计划来分析。三, 如果使用了复合索引，中间使用了范围查询，右边的条件索引也会失效。四, 不要在索引上进行运算操作, 索引会失效。一, 索引在使用的时候没有遵循最左匹配法则。五, 字符串不加单引号,造成索引失效.
MySQL联合查询、最左匹配、范围查询导致失效
Aen_hen的博客
12-06 519
小林coding中的实验结论不一致 - 注意！在小林coding中说到，关于范围查询对联合索引的影响对于 >=、
震惊！最左前缀原则失效了？？
weixin_46815582的博客
08-07 1244
问题 Mysql中组合索引的最左前缀原则想必大家都很熟悉，最近在学习的时候，我进行了几个小实验发现了一些很有趣的问题（测试环境为Mysql8.0.23） 表结构如下： 表里的字段只有主键索引（id）和联合索引（a，b，c） 这个数据库表创建了（a，b，c）这个联合索引，当我们创建组合索引abc时，Mysql其实维护了3个索引：a，ab和abc。最左前缀原则要求从左至右依次命中组合索引，要能使其生效必须保证 where 条件里最左边是 a 字段，比如以下这几种情况： where a =...
我去，为什么最左前缀原则失效了？
勇往直前的专栏
07-06 685
问题 最近，在 mysql 测试最左前缀原则，发现了匪夷所思的事情。根据最左前缀原则，本来应该索引失效，走全表扫描的，但是，却发现可以正常走索引。 表结构如下（ Mysql 版本 5.7.22）： CREATETABLE`user`( `id`int(11)NOTNULLAUTO_INCREMENT, `name`varchar(32)COLLATEutf8mb4_binDEFAULTNULL, `age`int(11)DEFAULTNULL, `add...
mysql 联合索引最左匹配原则
最新发布
10-17
<think>我们正在讨论MySQL联合索引的最左匹配原则。根据提供的引用，我们可以总结出以下核心要点： 1. 最左前缀匹配原则是指在使用联合索引时，查询条件必须从索引的最左列开始，并且不能跳过中间的列。 2. 该原则的根本原因在于B+树的数据结构，索引按照定义时的列顺序排序。 3. 在匹配过程中，如果遇到范围查询（如>、<）会停止匹配，而>=、<=、BETWEEN、like前缀匹配则不会停止匹配，但要注意like的通配符在开头会导致索引失效（虽然引用中未直接提及，但这是常识）。 4. 建议将区分度高的字段放在联合索引的左边，以过滤更多数据。 我们将详细解释这些要点，并举例说明。 首先，联合索引（复合索引）是指对表上的多个列进行索引。例如，我们创建一个联合索引： ```sql CREATE INDEX idx_name ON table_name (col1, col2, col3); ``` 这个索引在B+树中的结构是按照col1排序，col1相同再按col2排序，col1和col2都相同再按col3排序。 最左匹配原则意味着： - 查询条件中必须包含col1（即最左列），才能使用该索引。 - 如果查询条件中只有col2和col3，而没有col1，则不会使用该索引（除非有覆盖索引等情况，但这里指索引用于过滤）。 - 如果查询条件有col1和col3，而没有col2，那么索引只会使用col1（因为跳过了col2，col3无法被利用）。 举例说明： - 有效查询（使用整个索引）： WHERE col1 = 'a' AND col2 = 'b' AND col3 = 'c' - 有效查询（使用索引的前两列）： WHERE col1 = 'a' AND col2 = 'b' - 有效查询（使用索引的第一列）： WHERE col1 = 'a' - 无效查询（没有最左列）： WHERE col2 = 'b' AND col3 = 'c' -- 不会使用索引 - 部分有效查询（有最左列，但跳过了中间列）： WHERE col1 = 'a' AND col3 = 'c' -- 只有col1使用索引，col3不会 对于范围查询： - 当使用范围查询（>、<）时，索引匹配到该列就会停止，后面的列无法使用索引进行查找（但可以使用索引扫描）。例如： WHERE col1 = 'a' AND col2 > 10 AND col3 = 'c' 这里，索引会使用col1和col2（因为col2是范围查询，所以后面的col3无法再使用索引进行精确查找）。 - 但是，对于>=、<=、BETWEEN、like（注意是前缀匹配，即like 'abc%'）等，虽然也是范围查询，但不会立即停止匹配，其后的列仍然可能被使用。但要注意，如果like使用了前导通配符（如'%abc'）则索引失效。 另外，引用[3]提到：在建立联合索引时，将区分度高的字段放在最左边，这样可以在查询时更有效地过滤数据。 需要注意的是，最左匹配原则不仅适用于等值查询，也适用于范围查询，但要遵循上述规则。 下面我们通过一个具体的例子来演示索引的使用情况： 假设有一个表`user`，有字段`a`, `b`, `c`，并创建联合索引`idx_abc(a,b,c)`。 查询1：`SELECT * FROM user WHERE a=1 AND b=2 AND c=3;` - 索引使用：全部使用，效率最高。 查询2：`SELECT * FROM user WHERE a=1 AND b=2;` - 索引使用：a和b，没有问题。 查询3：`SELECT * FROM user WHERE a=1 AND c=3;` - 索引使用：只有a，因为跳过了b，所以c不会使用索引进行查找（但可能会在a=1的结果中扫描，然后过滤c=3）。 查询4：`SELECT * FROM user WHERE b=2 AND c=3;` - 索引使用：不会使用索引（全表扫描），因为缺少最左列a。 查询5：`SELECT * FROM user WHERE a=1 AND b>2 AND c=3;` - 索引使用：a和b（b是范围查询，所以c无法使用索引进行精确查找）。但注意，在MySQL 5.6之后引入了索引条件下推（ICP），可以在存储引擎层对c进行过滤，但这里c的过滤不是在索引查找阶段，而是在索引扫描阶段。 查询6：`SELECT * FROM user WHERE a=1 AND b like 'abc%' AND c=3;` - 索引使用：a、b和c都可能使用。因为like是前缀匹配，所以MySQL会继续匹配c。 然而，如果查询是`SELECT * FROM user WHERE a=1 AND b like '%abc' AND c=3;`，则b的查询条件无法使用索引（因为通配符开头），所以只有a使用索引。 总结： 最左匹配原则是MySQL联合索引的核心规则，理解它有助于设计合理的索引和编写高效的查询语句。 相关问题： 1. 在哪些情况下联合索引的最左匹配原则会失效？ 2. 如何选择联合索引的列顺序？ 3. 范围查询在联合索引中是如何影响索引使用的？ 4. 什么是索引条件下推（ICP）？它如何优化联合索引的查询？ </think>### MySQL联合索引最左匹配原则详解 MySQL的**最左前缀匹配原则**是联合索引（复合索引）的核心使用规则，决定了查询是否能高效利用索引。以下是关键要点： #### 一、核心原理 1. **索引结构基础** 联合索引`(col1, col2, col3)`在B+树中的排序逻辑： - 先按`col1`排序 - `col1`相同时按`col2`排序 - `col1`和`col2`相同时按`col3`排序 这种结构要求查询必须**从最左列开始匹配**。 2. **匹配规则** - ✅ **有效匹配**： - `WHERE col1 = ?` - `WHERE col1 = ? AND col2 = ?` - `WHERE col1 = ? AND col2 = ? AND col3 = ?` （索引按定义顺序连续使用） - ❌ **无效匹配**： - `WHERE col2 = ?`（跳过`col1`） - `WHERE col1 = ? AND col3 = ?`（跳过`col2`） （未连续匹配的列无法利用索引）[^1][^3] #### 二、范围查询的特殊性 - **停止匹配的运算符**：`>`、`<` ```sql WHERE col1 = 1 AND col2 > 10 AND col3 = 'A' -- 仅使用col1和col2的索引，col3失效 ``` - **继续匹配的运算符**：`BETWEEN`、`LIKE '前缀%'`、`>=`、`<=` ```sql WHERE col1 = 1 AND col2 LIKE 'Abc%' AND col3 = 'A' -- 索引可匹配所有三列[^3] ``` #### 三、索引设计建议 1. **高频查询列放左侧** `WHERE a=? AND b=?`比`WHERE b=? AND a=?`更高效时，优先创建`(a,b)`索引。 2. **高区分度列优先** 将区分度高的列（如`user_id`）放最左，可快速过滤更多数据[^3]。 3. **避免冗余索引** 已有`(a,b)`索引时，单独创建`(a)`索引是冗余的（最左匹配已覆盖）。 #### 四、示例分析 假设表`users`有联合索引`(last_name, first_name, age)`： ```sql ✅ 有效查询： SELECT * FROM users WHERE last_name = 'Smith' AND first_name = 'John' ✅ 有效查询（范围）： SELECT * FROM users WHERE last_name = 'Smith' AND age >= 30 -- age使用索引 ❌ 无效查询： SELECT * FROM users WHERE first_name = 'John' -- 未指定last_name ``` #### 五、底层原因 B+树的**有序嵌套结构**决定了： - 缺失最左列时，无法定位索引的起始搜索点 - 跳过中间列会破坏索引的有序性，导致后续列只能全扫描[^2] --- ### 相关问题 1. 如何验证一个SQL语句是否使用了联合索引？ 2. 范围查询导致索引部分失效时，有哪些优化策略？ 3. 在哪些场景下联合索引比单列索引更高效？ 4. 索引下推（ICP）如何与最左匹配原则协同工作？ [^1]: MySQL索引的最左前缀匹配原则详解 [^2]: MySQL 索引最左匹配原则详解 [^3]: MySQL联合索引最左匹配原则使用详解
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值