知序
返回
保存草稿
发布
H1
H2
H3
1
2
3
Excalidraw 图
AI 写作
AI 优化
目录
输入内容后显示目录
Markdown
# MySQL 索引:从 B+ 树到查询优化 > 导读:理解 InnoDB 索引为什么采用 B+ 树、二级索引为什么会回表,以及如何依据查询模式设计联合索引和排查失效。 ## 一、先建立索引模型 ### 为什么需要索引?它解决了什么问题?有什么缺点? - 先想想没有索引的情况。数据是存储在磁盘上的,查询数据时,如果没有索引,会加载所有的数据到内存,依次进行检索,读取磁盘次数较多。有了索引,就不需要加载所有数据。 - 索引是一种**加快查询速度**的数据结构。索引为部分列建立有序访问路径,使数据库能快速缩小候选记录范围,并可辅助排序、连接和覆盖查询。 - 索引不是免费优化:它会**占用额外存储空间**,且 INSERT、UPDATE、DELETE 时需要同步维护索引。因此应围绕真实的 WHERE、JOIN、ORDER BY 和返回字段设计索引,而不是为每个列都建立索引。 ### 索引的底层数据结构是什么? - B+树:B+树是B树的升级版,非叶子节点只存在索引,叶子节点存放数据。而且叶子节点用链表连接起来。 - Hash索引:底层是用哈希表实现的 - 哈希表是键值对的组合,通过键(特定哈希算法)可以快速查询值,时间接近0(1)。 - 哈希冲突 - 如果两个(或多个)键值,映射到一个相同的槽位上,他们就产生了hash冲突。最常见的解决Hash冲突的方法是链地址法,将哈希冲突的数据存放在链表中。 - 特点 - Hash索引只能用于精确匹配索引列的查询(=,in),不支持范围查询(between,>,< ,...) - 无法利用索引完成排序操作、 - 查询效率高,通常(不存在hash冲突的情况)只需要一次检索就可以了,效率通常要高于B+tree索引 - R-Tree空间索引:空间索引是MyISAM引擎的一个特殊索引类型,主要用于地理空间数据类型,通常使用较少 - 全文索引:是一种通过建立倒排索引,快速匹配文档的方式。 ### 为什么InnoDB使用B+树做索引而不是B树?  [编辑 Excalidraw 源图](/images/excalidraw/02-index-b-tree-vs-b-plus-tree-diagram.excalidraw) - B 树在每层同时保存键和数据,单页可容纳的路由键较少,查询终点也不统一。B+ 树所有查询都要到叶子节点,路径长度固定。 - B+树非叶子节点不存数据,同样大小的节点能存更多 key ,这使得树更矮,I/O 次数更少 - B+树的叶子节点使用双向链表连接,范围查询效率高 - 针对`SELECT * WHERE id BETWEEN 10 AND 50`,B+树找到 10 的叶子节点,沿链表顺序扫描到 50,B树需要多次回到根节点,效率低。 ### 三到四层 B+ 树为什么能容纳海量数据?  [编辑 Excalidraw 源图](/images/excalidraw/02-index-b-plus-tree-diagram.excalidraw) - 每层页都能容纳大量索引项,层数增加带来乘法级容量;具体容量受键长度、页填充率和行大小影响。 ## 理解 InnoDB 索引存了什么 ### 聚簇索引和非聚簇索引的叶子节点分别存什么? - 聚簇索引叶子节点保存完整数据行; - 非聚簇索引保存索引列和主键值,因此它能定位行,但不总能直接返回全部列。 ### InnoDB和MyIsam的非聚簇索引有什么区别吗? - InnoDB:非聚簇索引的叶子节点存储的是索引列的值以及对应的主键值 - MyISAM:非聚簇索引的叶子节点存储的是索引列的值以及该记录在数据文件中的物理地址(行指针)。通过这个物理地址可以直接定位到数据文件中相应的数据记录。  *图:二级索引叶子节点存索引列与主键值* ``` 二级索引的叶子节点存储:索引列的值 + 主键值(不是完整行数据) > 💬 ### Q:二级索引的叶子节点存储的不是该行记录的地址吗? > > 存行地址是 **MyISAM** 的做法,InnoDB 存的是**主键值**,这是两种存储引擎的核心区别。 > > ``` > MyISAM(堆表): > 数据文件(.MYD)和索引文件(.MYI)是分开的 > 数据行存在堆文件里,有固定的物理地址 > 所有索引的叶子节点都存行的物理地址 → 一步取到数据 > > InnoDB(聚簇表): > 数据本身就是按主键组织的 B+ 树(聚簇索引) > 数据行没有独立的"物理地址",它就在主键索引的叶子节点里 > 二级索引的叶子节点只能存主键值,再去主键索引查完整数据 → 回表 > ``` > > 为什么 InnoDB 不存物理地址?因为 InnoDB 会做页分裂、数据迁移,行的物理位置会变。如果二级索引存的是物理地址,每次数据移动都要更新所有二级索引,代价极高。存主键值就稳定多了——主键不变,二级索引就不需要更新。 idx_user_id(user_id) 的 B+ 树叶子节点: ┌──────────────────────────────────────────┐ │ user_id=1001 | id=1 ← 主键值 │ │ user_id=1001 | id=10 ← 同一用户的多条 │ │ user_id=1002 | id=5 │ │ user_id=1002 | id=20 │ │ user_id=1003 | id=15 │ └──────────────────────────────────────────┘ 注意:二级索引叶子节点只存 (索引列值 + 主键值),不存完整行数据 这样设计的好处:节省空间,主键更新时只需更新一处(主键索引) 代价:查询完整行数据需要"回表" 对比: 主键索引叶子节点:[id=1 | user_id=1001 | status=3 | amount=299.00 | create_time=...] 二级索引叶子节点:[user_id=1001 | id=1] ← 只有索引列和主键 ``` ### Q:什么是回表,如何用覆盖索引避免它? A:二级索引查到主键后再访问聚簇索引取得缺失列,称为回表;若查询列都在二级索引中,则可直接返回。  *图:回表 2 次随机 I/O 与覆盖索引对比* ``` 回表:通过二级索引查到主键后,再去主键索引查完整数据的过程。 回表 vs 覆盖索引 流程对比: 回表查询(SELECT * FROM orders WHERE user_id = 1001): 二级索引 idx_user_id 主键索引 PRIMARY ┌─────────────────┐ ┌─────────────────┐ │ user_id │ id │ │ id │ 完整行 │ ├─────────────────┤ ├──────┼──────────┤ │ 1001 │ 1 │───找到 id=1 ──→│ 1 │ {...} │ ← 1 次随机 I/O │ 1001 │ 10 │───找到 id=10──→│ 10 │ {...} │ ← 1 次随机 I/O └─────────────────┘ └─────────────────┘ 2 次回表 = 2 次随机 I/O(代价高) 覆盖索引(SELECT id, user_id FROM orders WHERE user_id = 1001): 二级索引 idx_user_id ┌─────────────────┐ │ user_id │ id │ ├─────────────────┤ │ 1001 │ 1 │ ← 直接用索引中的值,不需要回表 │ 1001 │ 10 │ ← id 已经在索引中了 └─────────────────┘ 0 次回表 = 0 次额外 I/O(最优) 判断依据: EXPLAIN 结果的 Extra 列出现 "Using index" → 覆盖索引 ✓ 出现 "Using index condition" → 索引下推(ICP),但仍需回表 查询示例:SELECT * FROM orders WHERE user_id = 1001 步骤 1:在 idx_user_id 的 B+ 树中查找 user_id=1001 → 找到叶子节点:[(user_id=1001, id=1), (user_id=1001, id=10)] 步骤 2:拿到 id=1,去主键索引 B+ 树查找 → 找到完整行:[id=1, user_id=1001, status=3, amount=299.00, ...] 步骤 3:拿到 id=10,再次去主键索引 B+ 树查找 → 找到完整行:[id=10, user_id=1001, status=2, amount=199.00, ...] user_id=1001 有 2 条记录 → 2 次回表(2 次随机 I/O) 避免回表的方法:覆盖索引 索引:(user_id),查询:SELECT id, user_id FROM orders WHERE user_id = 1001 → 索引叶子节点已经有 user_id 和 id,不需要回表 ✓(EXPLAIN 显示 Using index) 索引:(user_id),查询:SELECT * FROM orders WHERE user_id = 1001 → 需要 status、amount 等字段,索引中没有,需要回表 ✗ ```  [编辑 Excalidraw 源图](/images/excalidraw/02-index-clustered-secondary-index-diagram.excalidraw)  [编辑 Excalidraw 源图](/images/excalidraw/02-index-back-to-table-diagram.excalidraw) ## 四、让索引匹配查询条件 ### Q:联合索引为什么遵循最左前缀? A:联合索引按列顺序排列,缺失前导列就无法有效缩小后续列的有序范围。 ``` 联合索引 (a, b, c) 的存储方式: 先按 a 排序,a 相同再按 b 排序,b 相同再按 c 排序 最左前缀原则: WHERE a=1 → 用到索引(a) WHERE a=1 AND b=2 → 用到索引(a,b) WHERE a=1 AND b=2 AND c=3 → 用到索引(a,b,c) WHERE b=2 → 不用索引(没有 a,无法定位) WHERE a=1 AND c=3 → 只用到索引(a),c 跳过了 b WHERE a=1 AND b>2 AND c=3 → 用到(a,b),c 在范围查询后失效 ``` ### Q:联合索引列顺序该如何决定? A:优先匹配高频查询形态:通常把等值过滤列放在范围列之前,并同时评估选择性、排序和覆盖需求。 ```sql -- 场景:orders 表,常见查询是按 status 和 create_time 过滤 -- 问题:应该建 (status, create_time) 还是 (create_time, status)? -- 先分析各列的选择性(区分度): SELECT COUNT(DISTINCT status) / COUNT(*) AS status_selectivity, COUNT(DISTINCT DATE(create_time)) / COUNT(*) AS date_selectivity FROM orders; -- 假设结果: -- status_selectivity = 0.0004 ← 只有 4 种状态,区分度极低 -- date_selectivity = 0.0365 ← 365 天,区分度较高 -- 常见误区:认为区分度高的列一定要放前面 -- 实际上要看查询模式! -- 对于查询:WHERE status = 1 AND create_time > '2024-01-01' -- (status, create_time) 更好: -- status 作为等值过滤,先缩小范围 -- create_time 在 status 确定后做范围查询,可以利用索引有序性 -- 方案 A:INDEX (status, create_time) EXPLAIN SELECT * FROM orders WHERE status = 1 AND create_time > '2024-01-01 00:00:00'; -- type: range, key_len: 6(status 1字节 + datetime 5字节) -- Extra: Using index condition ← 两列都用到了索引 -- 方案 B:INDEX (create_time, status) EXPLAIN SELECT * FROM orders WHERE status = 1 AND create_time > '2024-01-01 00:00:00'; -- type: range, key_len: 5(只用到 create_time,status 在范围查询后失效) -- Extra: Using index condition; Using where ← status 需要在 Server 层过滤 -- 结论:对于"等值 + 范围"的查询,等值列放前面更高效 ``` > 💬 ### Q:联合索引创建的场景有哪些? > > 主要四种场景: > > **1. 多列等值查询** > 多个条件同时出现时,联合索引比多个单列索引更高效(一次 B+ 树查找 vs 索引合并): > ```sql > -- 建 (user_id, status),而不是两个单独索引 > SELECT * FROM orders WHERE user_id = 1001 AND status = 2; > ``` > > **2. 等值 + 范围组合** > 等值列放前面,范围列放后面,两列都能被用到: > ```sql > -- 建 (status, create_time),status 等值过滤,create_time 范围查询 > SELECT * FROM orders WHERE status = 1 AND create_time > '2024-01-01'; > ``` > > **3. 覆盖索引(避免回表)** > 把 SELECT 的字段也加入索引,查询直接从索引返回,不用回主键索引: > ```sql > -- 建 (user_id, status, amount),三列都在索引里,无需回表 > SELECT status, amount FROM orders WHERE user_id = 1001; > ``` > > **4. ORDER BY 利用索引排序** > 联合索引可以让 ORDER BY 走索引,避免 filesort: > ```sql > -- 建 (user_id, create_time),WHERE 等值 + ORDER BY 都走索引 > SELECT * FROM orders WHERE user_id = 1001 ORDER BY create_time DESC; > ``` > > **选择原则**:等值列放前,范围列放后;高频查询的列优先;把 SELECT 的字段纳入索引实现覆盖;不要建太多,写入时每个索引都要维护。  [编辑 Excalidraw 源图](/images/excalidraw/02-index-composite-index-order-diagram.excalidraw) ### Q:索引下推如何减少无效回表? A:引擎先使用索引中可判断的条件过滤记录,仅对仍满足条件的记录回表。 ### 没有 ICP 时(MySQL 5.6 之前) ```sql -- 查询:找出 user_id=1001 且 amount > 200 的订单 -- 假设有联合索引 (user_id, amount) SELECT * FROM orders WHERE user_id = 1001 AND amount > 200; ``` ``` 没有 ICP 的执行过程: 存储引擎层: 1. 在 (user_id, amount) 索引中找到所有 user_id=1001 的记录 找到:[(user_id=1001, id=1), (user_id=1001, id=10)] 2. 对每条记录回表,取出完整行数据 回表 id=1 → 得到完整行(amount=299.00) 回表 id=10 → 得到完整行(amount=199.00) 3. 将完整行数据返回给 Server 层 Server 层: 4. 应用 amount > 200 的过滤条件 id=1: amount=299.00 > 200 ✓ 保留 id=10: amount=199.00 > 200 ✗ 丢弃 问题:id=10 的回表是多余的!amount=199.00 不满足条件, 但没有 ICP 时,存储引擎不知道这个条件,只能全部回表 ``` ### 有 ICP 时(MySQL 5.6+) ``` 有 ICP 的执行过程: 存储引擎层: 1. 在 (user_id, amount) 索引中找到所有 user_id=1001 的记录 2. 在索引层直接判断 amount > 200(不需要回表!) amount=299.00 > 200 ✓ → 需要回表 amount=199.00 > 200 ✗ → 直接跳过,不回表! 3. 只对满足条件的记录回表 对比: 没有 ICP:2 次回表 有 ICP:1 次回表(减少 50%) EXPLAIN 中的标志: Extra: Using index condition ← 表示使用了 ICP ``` ## 五、为什么看似有索引却没有用 ### Q:哪些常见写法会削弱索引能力? A:函数或计算包裹索引列、隐式类型转换、前置通配符和不满足最左前缀,都会使定位范围变差。 ```sql -- 1. 对索引列做函数运算 WHERE YEAR(create_time) = 2024 -- ✗ 失效 WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01' -- ✓ -- 2. 隐式类型转换(字符串字段用数字查询) -- 表结构:user_id VARCHAR(20),有索引 WHERE user_id = 12345 -- ✗ 相当于 CAST(user_id AS INT) = 12345,索引失效 WHERE user_id = '12345' -- ✓ -- 3. LIKE 以通配符开头 WHERE name LIKE '%张' -- ✗ 失效 WHERE name LIKE '张%' -- ✓ 可以用索引 -- 4. OR 连接的条件有一个没有索引 WHERE name = '张三' OR address = '北京' -- ✗ address 没索引则失效 -- 5. 联合索引不满足最左前缀 WHERE age = 25 -- ✗ 联合索引 (name, age),跳过了 name -- 6. 索引列参与计算 WHERE id + 1 = 10 -- ✗ 失效 WHERE id = 9 -- ✓ -- 7. 使用 != 或 <> WHERE status != 1 -- ✗ 通常失效(全表扫描更快) ```  ### Q:隐式类型转换为什么值得单独排查? A:类型不一致可能迫使引擎转换索引列,导致无法按原有有序性定位。 ```sql -- 用 EXPLAIN 验证: EXPLAIN SELECT * FROM sessions WHERE user_id = 12345; -- type: ALL ← 全表扫描,索引失效! EXPLAIN SELECT * FROM sessions WHERE user_id = '12345'; -- type: ref ← 走索引 ``` ## 六、如何把原理落到索引设计 ### Q:如何设计可维护的索引? A:围绕高频 WHERE、JOIN、ORDER BY 和返回列设计;兼顾选择性、覆盖收益与写入维护成本。 ``` 1. 选择区分度高的列建索引 区分度 = 不同值的数量 / 总行数 性别(区分度 ~0.5)不适合建索引 用户 ID(区分度接近 1)适合建索引 2. 频繁出现在 WHERE、JOIN ON、ORDER BY 的列 3. 联合索引的列顺序: 等值查询的列放前面,范围查询的列放后面 4. 控制索引数量(每个索引都会增加写操作的开销) 一张表的索引不超过 5~6 个 5. 长字符串用前缀索引 6. 避免冗余索引:已有 (a, b),不需要再建 (a) ``` ### Q:主键索引和唯一索引如何分工? A:主键承担行身份和聚簇组织;唯一索引表达额外业务唯一约束,二者语义不同。 ``` 两者都能保证“值唯一”,但语义和底层组织方式不完全一样: 主键索引: 一张 InnoDB 表只能有一个主键 主键索引就是聚簇索引 叶子节点直接存整行数据 主键列不能为 NULL 唯一索引: 一张表可以有多个唯一索引 通常是二级索引 叶子节点存“唯一列值 + 主键值” MySQL 中多数情况下允许多个 NULL ``` ``` 结论: 主键更偏“行身份标识”, 唯一索引更偏“业务唯一约束”。 ``` ### Q:为什么常推荐自增或趋势递增主键? A:它们更利于叶子页顺序追加和二级索引紧凑;是否采用 UUID 仍需权衡分布式生成和写入成本。 ``` 自增 ID 的优势: 1. 顺序写,B+ 树叶子节点更容易顺序追加 2. 页分裂少,写入成本低 3. 主键更短,二级索引更省空间 UUID 的问题: 1. 随机写,容易触发页分裂 2. 主键更长,所有二级索引都会跟着膨胀 3. 插入局部性差,缓存命中率更低 ``` ``` 所以在绝大多数 OLTP 场景里, 主键优先选自增 ID 或趋势递增 ID, 除非业务上必须使用分布式全局 ID。 ``` ### Q:索引合并能替代联合索引吗? A:它是优化器的折中策略;对长期高频组合条件,通常仍应优先设计匹配的联合索引。 ```sql -- a 和 b 各有单列索引 SELECT * FROM t WHERE a = 10 OR b = 20; ``` ``` 索引合并指的是优化器分别使用多个单列索引, 再把结果做并集、交集或排序合并,最后返回结果。 它通常出现在: 1. OR 条件 2. AND 条件但没有合适联合索引 ``` ``` 注意: 索引合并通常只是优化器的折中方案, 不代表它优于合理设计的联合索引。 如果查询是长期高频查询,往往还是应该优先补联合索引。 ``` ### Q:为什么前置通配符难以利用普通 B+ 树索引? A:B+ 树按前缀有序,`%abc` 无法形成可定位的连续范围;需改写需求或使用全文检索等方案。 ```sql LIKE 'abc%' -- 通常可以走索引 LIKE '%abc' -- 通常不能走普通 B+ 树索引 LIKE '%abc%' -- 通常也不能走普通 B+ 树索引 ``` ``` 原因: B+ 树索引按前缀有序,只有“从左到右”连续匹配时,才能快速定位范围。 优化思路: 1. 能改成前缀匹配,就尽量改成前缀匹配 2. 全文检索需求交给搜索引擎或全文索引 3. 特定场景下可用生成列、反转字段、分词表等技巧绕开 ``` ## 七、快速复盘:高频追问 ### Q:B+ 树为什么比 B 树更适合做索引? A:三个原因:① 非叶子节点不存数据,同等大小能存更多 key,树更矮,I/O 次数更少;② 叶子节点双向链表,范围查询效率高;③ 所有查询都到叶子节点,性能稳定。 ### Q:主键索引和二级索引的叶子节点分别存什么? A:主键索引(聚簇索引)的叶子节点存储完整的行数据(所有列)。二级索引的叶子节点只存储索引列的值和主键值,不存完整行数据。这就是为什么通过二级索引查询完整数据时需要"回表"——拿到主键后再去主键索引查一次。 ### Q:什么是回表?怎么避免? A:通过二级索引查到主键后,再去主键索引查完整数据的过程叫回表。避免方法:使用覆盖索引,让查询的所有字段都在索引中,不需要回表。 ### Q:什么是索引下推(ICP)? A:MySQL 5.6 引入的优化。在使用联合索引时,把 WHERE 条件中能用索引过滤的部分下推到存储引擎层处理,减少回表次数。例如联合索引 (user_id, amount),查询 WHERE user_id=1001 AND amount>200,ICP 会在索引层先过滤 amount>200,只对满足条件的记录回表。EXPLAIN 中 Extra 显示 `Using index condition`。 ### Q:什么情况下索引会失效? A:① 对索引列做函数运算;② 隐式类型转换(如字符串列用数字查询);③ LIKE 以通配符开头;④ 联合索引不满足最左前缀;⑤ OR 条件中有列没有索引;⑥ 索引列参与计算。 ### Q:为什么主键推荐用自增 ID? A:自增 ID 保证主键顺序插入,B+ 树叶子节点按顺序追加,不会触发页分裂,写入效率高。UUID 是随机的,插入时需要找到合适位置,频繁触发页分裂,效率低。 ## 总结:用一句话记住 索引优化的本质不是“多建索引”,而是让 B+ 树的有序结构与真实查询的过滤、排序和返回列相匹配;先看访问路径,再设计最小且有效的索引集合。 --- 上一篇:[01 - InnoDB 架构](./01-innodb-architecture.md) | 下一篇:[03 - 事务与 MVCC](./03-transaction-mvcc.md)
预览
发布文章
添加摘要让读者更快了解文章内容
取消
确认发布
AI 写作助手
输入一个主题,AI 将为你生成一篇 Markdown 博客文章
AI 正在写作中...
取消
开始生成
AI 优化助手
描述你的修改需求,AI 将在原文基础上进行补充或微调
AI 正在优化中...
取消
开始优化
画布
从已有图里选
✕
图名
保存后生成 /images/excalidraw/<图名>.svg(只能用字母、数字、短横线、下划线、点)
取消
保存并插入