MySQL索引之联合索引

已于 2022-09-28 01:23:07 修改
阅读量2.9w 收藏 154
点赞数 31
分类专栏： 数据库 # mysql 文章标签： mysql
于 2021-09-11 22:46:54 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_38192427/article/details/120244367
版权
本文详细介绍了MySQL中的联合索引原理，包括其存储结构、查询流程以及最左前缀匹配原则。联合索引在多条件查询时提高效率，其B+Tree结构基于最左字段排序。最左前缀匹配原则说明了在查询时如何有效利用索引。此外，文章还讨论了索引下推优化（Index Condition Pushdown, ICP）在MySQL 5.6后的应用，减少了回表次数，提高了查询效率。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

目录

1. 联合索引

在平时开发中，我们最常见的是聚集索引，但在我们需要多条件查询的时候，就不得不建立联合索引，来提高我们的查询效率

  • 联合索引：也称复合索引，就是建立在多个字段上的索引。联合索引的数据结构依然是 B+ Tree
  • 一颗 B+ Tree 只能根据一个值来构建，所以联合索引使用 最左 的字段来构建 B+ Tree

1.1. 联合索引的存储结构

如下图所示，表的数据如右图，ID 为主键，创建的联合索引为 (A，B)，注意联合索引顺序，左图是模拟的联合索引的 B+ Tree 存储结构
在这里插入图片描述

  • 叶子节点是线性排列，并且每个节点的数据排列顺序和创建索引字段的顺序一致。如 1,1，1,2，3,1 是对应着联合索引 (A，B) 字段顺序的，可以对照右图看
  • 非叶子节点存储完整的索引关键字信息，排列规则和叶子节点一致。如 2,8，4,3 是对应着联合索引 (A，B) 字段顺序的
  • InnoDB 会使用主键索引 B+ Tree 维护索引和数据文件，同样联合索引 (A，B) 也会生成一个 B+ Tree ，只不过联合索引 B+ Tree的 data 部分存储的是联合索引所在行的主键值。如 01，02，102，09 等它们是联合索引所在行的主键值
  • 根据图中叶子节点的数据可以看出，所有的数据都是按照列 A 进行排序的 1，1，1，2，3，3，4，4；B 列的顺序为 1，2，2，1，1，5，1，5，B 列是 全局 无序的。如果使用 B = 1 这种查询条件是没有办法使用到索引的，因为联合索引首先是按 A 排序的（使用 最左 的字段来构建 B+ Tree），B 是无序的
  • 我们还可以发现在 A 值相等的情况下，B 值又是按顺序排列的，但是这种顺序是局部的。如 1,1，1,2，1,2，3,1，3,5。所以最左匹配原则遇上范围查询就会停止，剩下的字段都无法使用索引。例如 A = 1 and B = 2，A,B 字段都可以使用索引，因为在 A 值确定的情况下 B 是相对有序的，而 A > 1 and B = 2，A 字段可以匹配上索引，但 B 值不可以，因为 A 的值是一个范围，在这个范围中 B 是无序的

1.2. 联合索引的查询流程

  • InnoDB 会使用主键索引 B+ Tree 维护索引和数据文件，同样联合索引 (A，B) 也会生成一个 B+ Tree ，只不过联合索引 B+ Tree的 data 部分存储的是联合索引所在行的主键值
  • 拿到联合索引所在行的主键值后，在通过主键索引 B+ Tree 就可以直接拿到具体的行数据了

1.3. 最左前缀匹配原则

最左优先，以最左边的为起点任何 连续 的索引都能匹配上，但遇到范围查询 (>、<、between、like) 就会停止匹配。之所以会有 最左前缀匹配原则和联合索引的索引构建方式及存储结构 是有关系的

  • 联合索引是使用多列索引的第一列（最左）构建的 B+ Tree
  • 用上面 (A，B) 的例子就是使用 A 列构建的 B+ Tree ，当 A 列值相等时再以 B 列进行排序（相对有序）

1.3.1. 最左前缀匹配原则说明

  • 如查询 A = 1，那么 A 字段肯定是可以使用索引的，因为所构建的索引 B+ Tree 是依据最左即 A 列构建的，数据自然是全局有序的
  • 如查询 A = 1 and B = 2，那么 A,B 字段都可以使用索引，因为在 A 值确定的情况下 B 是相对有序的
  • 如查询 B= 2 and A = 1，对于这种情况 A,B 字段都可以使用索引的。MySQL 查询优化器会判断纠正这条 sql 语句该以什么样的顺序执行效率最高，最后才生成真正的执行计划。所以，当然是我们能尽量的利用到索引时的查询顺序效率最高咯，所以 MySQL 查询优化器会最终以这种顺序进行查询执行
  • 另外如查询 B = 2，这种情况 也是会用到索引的；可观察 explain 结果中的 type 字段是一个 index，index 与 ALL 的区别为 index 类型只遍历索引树，这通常比 ALL 快。而上述 3 种查询情况 type 字段是一个 ref。详情：https://blog.csdn.net/qq_24690761/article/details/52787897

2. 索引下推

现在有一个需求：检索出表中名字第一个字是张，而且没有删除的信息 (is_del = 1)

select * from t_user where name like '张%' and is_del=1
  • 在 MySQL 5.6 之前，只能从匹配的位置一个个回表。到主键索引上找出数据行，再对比字段值
  • 在 MySQL 5.6 中引入的索引下推优化 (index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数

2.1. 无索引下推的执行流程

根据 (username, is_del) 联合索引查询所有满足名称以“张”开头的索引，然后回表查询出相应的全行数据，然后再筛选出未删除的用户数据。过程如下图

每一个虚线箭头表示回表一次
在这里插入图片描述

2.2. 有索引下推的执行流程

每一个虚线箭头表示回表一次
在这里插入图片描述
图 1 跟图 2 的区别是，InnoDB 在 (username, is_del) 索引内部就判断了数据是否逻辑删除，对于逻辑删除的记录，直接判断并跳过。在我们的这个例子中，只需要对 ID1、ID4 这两条记录回表取数据判断，就只需要回表 2 次

  • InnoDB 引擎的表，索引下推 只能用于联合索引，因为 InnoDB 的主键索引树叶子结点上保存的是全行数据，所以这个时候索引下推并不会起到减少查询全行数据的效果
  • 索引下推一般可用于所求查询字段（select 列）不是或不全是联合索引 的字段，查询条件为多条件查询且查询条件子句（where 或 order by）字段全是联合索引

参考：https://blog.csdn.net/sinat_41917109/article/details/88944290

Mysql的联合索引
yjl33的专栏
03-29 2512
两个或更多个列上的索引被称作联合索引，联合索引又叫复合索引。对于复合索引:Mysql从左到右的使用索引中的字段，一个查询可以只使用索引中的一部份，但只能是最左侧部分。例如索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行查找，但不支持 b,c进行查找 .当最左侧字段是常量引用时，索引就十分有效。
mysql索引详解
热门推荐
学Java，找哪吒
07-07 10万+
MySQL的存储引擎架构将查询处理与数据的存储/提取相分离
【MySQL】联合索引的使用
qq_45795794的博客
01-13 1731
目录1、背景2、数据示例3、联合索引B+树结构4、联合索引的几种使用方式【1】全值匹配【2】部分列匹配【3】列前缀匹配【4】范围匹配【5】排序【6】分组5、总结 1、背景 联合索引就是给多个列建一个索引，使用联合索引时要满足最左匹配原则，不然会索引失效，本篇文章就通过explain执行计划研究一下联合索引，能让我们避免使用联合索引的一些坑。 2、数据示例 创建一张表，有三个索引：主键索引、普通索引、联合索引，我们重点关注联合索引： CREATE TABLE test1 ( id INT AUTO_I
mysql建立联合索引_mysql之联合索引
weixin_35824356的博客
01-19 5863
mysql之联合索引测试：前期准备：建立联合索引？CREATE TABLE `test` (`id` bigint(16) NOT NULL AUTO_INCREMENT,`aaa` varchar(16) NOT NULL,`bbb` varchar(16) NOT NULL,`ccc` int(11) NOT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB DEF...
MySQL索引总结（小白也能看懂）
最新发布
weixin_45865163的博客
04-22 953
我们在进行模糊查询的时候，如果把 % 放在了前面，最左的 n 个字母便是模糊不定的，无法根据索引的有序性准确的定位，只能进行全表扫描，找出符合条件的数据。（创建 1 个联合索引相当于创建了 3 个索引），包含这些列的所有查询都会走索引而不会全表扫描。唯一索引的属性列不能出现重复的数据，但是允许数据为 NULL，一张表允许创建多个唯一索引。前缀索引是对文本的前几个字符创建索引，相比普通索引建立的数据更小，因为只取前几个字符。的字段，如果有，则选择该字段为默认的主键，否则。的表中，当没有显示的指定表的主键时，
mysql 复合索引，联合索引
刘瑞东的专栏
04-13 2781
对于复合索引:Mysql从左到右的使用索引中的字段，一个查询可以只使用索引中的一部份，但只能是最左侧部分。例如索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行查找，但不支持 b,c进行查找 .当最左侧字段是常量引用时，索引就十分有效。 两个或更多个列上的索引被称作复合索引。 利用索引中的附加列，您可以缩小搜索的范围，但使用一个具有两列的索引
MySQL 联合索引
Dablelv 的博客专栏。
01-26 2151
联合索引指建立在多个列上的索引。MySQL 可以创建联合索引（即多列上的索引）。一个索引最多可以包含 16 列。联合索引可以测试包含索引中所有列的查询，或仅测试第一列、前两列、前三列等等的查询。如果在索引定义中以正确的顺序指定列，则复合索引可以加快对同一表的多种查询的速度。下面是一个联合索引的例子。name 索引是针对 last_name 和 first_name 列的索引。该索引可加速查询。这些查询为 last_name 和 first_name 值的组合。
MySQL联合索引用法示例
09-10
MySQL联合索引是一种优化查询性能的机制，它允许在多个列上创建单个索引，以便在查询中同时使用这些列时能更快地找到数据。本示例将深入讲解联合索引的概念，并通过实例来展示如何在MySQL中创建和使用联合索引。 ...
深入浅析Mysql联合索引最左匹配原则
09-09
MySQL的联合索引是一种优化查询性能的有效手段，它遵循“最左匹配原则”。这个原则指出，当创建一个包含多个列的联合索引时（例如，KEY test_col1_col2_col3 ON test(col1, col2, col3)），MySQL会在查询时从索引的...
MySQL全文索引、联合索引、like查询、json查询速度哪个快
09-08
本文将深入探讨全文索引、联合索引、LIKE查询以及JSON查询这四种不同方法在实际应用中的速度比较，并通过实例进行详细解析。 首先，全文索引（Full-text Index）是MySQL提供的一种特殊类型的索引，专门用于提高全文...
MySQL联合索引功能与用法实例分析
09-09
MySQL联合索引，也称为复合索引，是数据库优化中的一种关键策略，它允许数据库系统在多个列上创建单一的索引，以提高查询性能。理解联合索引的工作原理和如何有效利用它对于提升数据库查询效率至关重要。 联合索引...
mysql之联合索引
weixin_44348521的博客
09-09 1603
联合索引的最左原则索引并非越多越好，过多的索引会增加数据的维护速度还有磁盘空间的浪费。当表的数据量很大的时候，可以考虑建立索引。表中经常查数据的字段，可以考虑建立索引。想要保证表中数据的唯一性，可以考虑建立唯一索引。想要保证俩张表中的数据的完整性跟准确性，可以考虑建立外键约束。经常对多列数据进行查询时，可以考虑建立联合索引。
mysql 联合索引
无效的博客
12-19 2920
mysql 联合索引建立应该遵循左到右顺序，以where时候可能的column排序为基准。 如果例如： where firstname='' and lastname='' and age='' 建立的联合索引顺序应该为key(firstname lastname age)， 这样就相当于建立一个key(firstname)、一个key(firstname lastname)、以及本身ke
MySQL索引长度(key_len)计算
程序员Forlan的博客
05-25 2736
计算规则 1.索引字段，没有设置not null，则需要加一个字节。 2.定长字段：int固定占四个字节、date占三个字节、char(n)占n个字符， 3.varchar(n)占n个字符+2个字节。 4.不同的字符集，一个字符占用的字节数不同 latin1编码，每个字符占用一个字节 gbk编码，每个字符占用两个字节 utf8编码，每个字符占用三个字节 utf8mb4编码，每个字符占用四个字节 例子 可以通过explain ...
mysql中的联合索引
z_alvin的博客
09-20 376
联合索引又叫复合索引。对于复合索引:Mysql从左到右的使用索引中的字段， 一个查询可以只使用索引中的一部份，但只能是最左侧部分。 例如索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行查找， 但不支持 b,c进行查找 .当最左侧字段是常量引用时，索引就十分有效。 如：建立 姓名、年龄、性别的复合索引。 select * from user wher...
MYSQL创建联合索引
唐僧取经
06-09 3400
【代码】MYSQL创建联合索引。
mysql联合索引的数据结构
m0_57084845的博客
09-12 606
存储引擎首先从根节点（一般常驻内存）开始查找，第一个索引的第一个索引列为1,12大于1，第二个索引的第一个索引列为56,12小于56，于是从这俩索引的中间读到下一个节点的磁盘文件地址，从磁盘上Load这个节点，通常伴随一次磁盘IO，然后在内存里去查找。InnoDB会使用主键索引在B+树维护索引和数据文件，然后我们创建了一个联合索引（b，c，d）也会生成一个索引树，同样是B+树的结构，只不过它的data部分存储的是联合索引所在行的主键值（联合索引：也称复合索引，就是建立在多个字段上的索引。
mysql联合索引详解
2301_77822280的博客
06-10 4872
这样的数据来检索的时候，b+树会优先比较a列来确定下一步的所搜方向，如果a列相同再依次比较b列和c列，最后得到检索的数据；)这样的数据来检索时，b+树可以用a列来指定搜索方向，但下一个字段b列的缺失，所以只能把a列的数据找到，然后再匹配c列的数据了， 这个是非常重要的性质，例如索引是key index (a,b,c). 可以支持a a,b a,b,c 3种组合进行查找，但不支持 b,c进行查找。order by c；a、b列使用索引查找，因b列是范围查询，因此c列不能使用索引，会出现file sort。
索引和联合索引
03-24
<think>好的，我现在需要处理用户关于“索引和联合索引”的查询。用户之前询问过@NotBlank注解，现在转向数据库索引，可能是在学习后端开发中的数据持久化部分。 首先，要明确用户的需求。他们可能想了解索引的基本概念，以及联合索引的具体应用场景和优化技巧。由于用户之前接触的是Java校验，可能对数据库优化不熟悉，需要从基础讲起。 接下来，我需要确定回答的结构。用户提供的示例回答结构清晰，分点说明，我应该效仿。先介绍索引，再讲联合索引，然后对比，注意事项，最后使用建议。这样用户能逐步理解。 索引部分要解释是什么、为什么使用、优缺点。联合索引则需要强调多个列的组合，以及最左前缀原则。需要举例说明，比如(name, age)的查询情况，这样用户更容易理解。 同时，用户可能对如何创建索引和联合索引的SQL语法感兴趣，所以需要给出示例代码，比如CREATE INDEX语句。注意事项部分要提到过度索引的问题，写操作的性能影响，以及索引选择策略。 还要考虑用户的实际应用场景，比如在订单表中使用联合索引来优化查询，这能帮助他们将理论应用到实践中。最后，总结部分要强调合理使用索引的重要性，避免常见误区。 需要确保用词准确，避免技术术语过多，但又要保持专业性。例如，解释“最左前缀原则”时，要简明扼要，用例子说明。同时，检查是否有遗漏点，比如索引的类型（B-tree、哈希等），但用户的问题可能更关注基础，所以暂时不深入。 最后，确保回答符合之前的格式要求，使用中文，数学公式用$...$，独立公式用$$...$$，但这次内容可能不需要太多公式，但如果有涉及计算的地方要注意格式。检查是否有错误，比如索引的优缺点是否全面，联合索引的应用场景是否明确。</think>以下是对数据库索引和联合索引的全面解析，采用技术文档结构呈现： 一、索引基础原理 1. **本质定义** $$ \text{索引} = \text{预排序数据结构} + \text{快速定位指针} $$ - 采用B+Tree/Hash等数据结构实现（主流数据库默认B+Tree） - 存储字段值+对应数据物理地址 2. **核心价值** - 将$O(n)$的全表扫描优化为$O(\log n)$的树搜索 - 典型空间换时间策略（索引占表空间20%-30%） 3. **创建语法示例** ```sql -- 单列索引 CREATE INDEX idx_name ON users(name); -- 联合索引（多列组合） CREATE INDEX idx_name_age ON employees(name, age DESC); ``` 二、联合索引专项解析 1. **结构特性** - 索引键值按声明顺序存储：$(\text{col1}, \text{col2}, \text{col3}) \rightarrow$ 先按col1排序，col1相同按col2排序，依此类推 - 存储结构示例： ``` (Alice,20) -> 0x1234 (Alice,25) -> 0x5678 (Bob,18) -> 0x9abc ``` 2. **最左前缀原则** - 有效查询模式： ```sql WHERE col1 = ? WHERE col1 = ? AND col2 = ? WHERE col1 = ? AND col2 > ? ``` - 失效场景： ```sql WHERE col2 = ? -- 缺少左前缀 WHERE col1 LIKE '%abc' -- 左前缀模糊匹配 WHERE col1 = ? OR col2 = ? -- OR操作破坏连续性 ``` 三、性能对比矩阵 | 对比维度 | 单列索引 | 联合索引 | |----------------|---------------------------|-------------------------| | 存储开销 | 每个索引独立存储 | 多列组合存储，节省空间 | | 查询覆盖 | 仅优化单列查询 | 支持多列组合查询 | | 排序优化 | 单字段排序 | 多字段复合排序 | | 维护成本 | 写操作需维护多个索引 | 单索引维护成本更低 | 四、实践应用建议 1. **优先场景** - 高频组合查询：`WHERE status=1 AND create_time > '2023'` - 多列排序需求：`ORDER BY last_name, first_name` - 覆盖索引优化：`SELECT id,name FROM users WHERE age=25` 2. **设计策略** - 区分度原则：高区分度列（如手机号）放左侧 - 长度控制：varchar列建立前缀索引`INDEX idx(email(10))` - 冷热分离：更新频率差异大的列避免组成联合索引 五、典型案例分析 **订单表优化场景**： ```sql CREATE TABLE orders ( id BIGINT PRIMARY KEY, user_id INT NOT NULL, status TINYINT NOT NULL, create_time DATETIME NOT NULL, amount DECIMAL(10,2) ); -- 创建联合索引 CREATE INDEX idx_user_status_time ON orders(user_id, status, create_time); ``` **生效查询**： ```sql SELECT * FROM orders WHERE user_id=123 AND status=2 ORDER BY create_time DESC LIMIT 10; EXPLAIN结果： type=ref, key=idx_user_status_time, Extra=Using where ``` 六、注意事项 1. **索引失效陷阱**： - 隐式类型转换：`WHERE varchar_col=123` - 函数操作：`WHERE YEAR(create_time)=2023` - 不等于操作：`WHERE status != 1` 2. **监控维护**： ```sql -- 查看索引使用率 SELECT * FROM sys.schema_index_statistics WHERE table_name='orders'; -- 重建索引 ALTER TABLE orders REBUILD INDEX idx_name; ``` 正确使用索引可使查询速度提升$10^2 \sim 10^4$倍，但需根据EXPLAIN执行计划和慢查询日志持续优化。建议单表索引数控制在$[3,5]$个区间，避免过度索引影响写性能。
评论 5
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值