【MySQL 性能调优实战·第 01 篇】索引原理B 树·聚簇索引·回表·覆盖索引——一切调优的起点MySQL 性能调优实战系列开篇。如果你只学一个 MySQL 调优知识点那必须是索引。索引是 MySQL 性能的基石——一个设计良好的索引可以让查询快 10-1000 倍一个缺失的索引可以让全表扫描拖垮整个数据库。但索引不是加了就快——不理解原理就建索引等于蒙眼开车。MySQL InnoDB 的索引基于B 树为什么不是哈希表为什么不是 B 树为什么不是跳表因为 B 树矮3 层存千万行、胖每页多关键字、连叶子链表——完美适配磁盘 IO 和范围查询。InnoDB 有两种索引聚簇索引叶子存完整行数据每表 1 个和二级索引叶子存索引列 主键每表多个。二级索引查找非索引列需要回表——回到聚簇索引再查一次这是性能杀手。覆盖索引查询列全在索引中和索引下推 ICPWHERE 条件在索引层过滤是减少回表的两大武器。联合索引的最左前缀原则决定了索引列的排列顺序——等值过滤 → 范围过滤 → 排序分组。今天我们从 B 树原理、索引类型到设计原则与常见陷阱彻底掌握 MySQL 索引。 文章目录 一、B 树为什么 InnoDB 选它做索引️ 二、聚簇索引与二级索引回表·覆盖索引·ICP 三、索引设计原则与常见陷阱 一、B 树为什么 InnoDB 选它做索引1.1 从磁盘 IO 说起为什么索引要考虑磁盘MySQL 的数据存储在磁盘上磁盘 IO 是数据库性能的最大瓶颈。一次磁盘随机 IO 大约 10msSSD 约 0.1ms而一次内存访问约 100ns——磁盘 IO 比内存慢 10 万倍。因此索引的核心目标是尽量减少磁盘 IO 次数。InnoDB 以**页Page**为单位管理磁盘数据默认页大小 16KB。每次磁盘 IO 读取一页到内存的 Buffer Pool 中。索引的设计必须围绕如何用最少的页访问次数找到目标数据展开。1.2 B 树结构矮·胖·连B 树是一种多路平衡搜索树InnoDB 选择它作为索引结构因为它有三个关键特性矮B 树的每个节点可以存储大量关键字16KB 页可以存几百个指针树的高度很低。对于千万级数据B 树通常只有 3 层——根节点常驻内存查找只需 2-3 次磁盘 IO。根节点(常驻内存) / | \ 中间节点 中间节点 中间节点 ← 第2层: 1次IO / | \ / | \ / | \ 叶子→叶子→叶子→叶子→叶子→叶子 ← 第3层: 1次IO [数据] [数据] [数据] [数据] [数据] [数据]胖每个节点存储多个关键字和指针。假设每个关键字指针占 12 字节一个 16KB 页可以存约 1300 个指针。3 层 B 根节点 1 个 第二层 1300 个 第三层 1300×1300 16.9 亿个叶子节点每个叶子存若干行数据足以覆盖千万到亿级数据。连B 树的叶子节点通过双向链表连接。这个特性对范围查询至关重要——WHERE id BETWEEN 100 AND 500只需定位到 id100 的叶子节点然后沿链表向右扫描到 id500无需回溯父节点。1.3 B 树 vs 其他数据结构vs 哈希索引哈希索引等值查询 O(1)但不支持范围查询、排序、最左前缀匹配。数据库中大量查询是范围查询WHERE age 20、ORDER BY create_time哈希索引无法满足。vs B 树B 树的非叶子节点也存数据导致每个节点能存的关键字更少树更高4-5 层 vs 3 层磁盘 IO 更多。B 树非叶子节点只存关键字指针更胖更矮。B 树不支持叶子链表范围查询需要中序遍历回溯父节点。vs 跳表跳表在内存数据库Redis ZSET中表现优秀但层数多20 层意味着 20 次磁盘 IO不可接受。B 树 3 层就够了。vs 红黑树红黑树是二叉树每个节点只有 2 个子节点树高度 log₂(n)千万级数据约 24 层24 次磁盘 IO完全不可接受。数据结构等值查询范围查询树高度(千万行)磁盘IO哈希O(1)❌ 不支持-1次红黑树O(log n)需回溯~24层24次B树O(log n)需回溯4-5层4-5次B树O(log n)链表扫描3层2-3次跳表O(log n)顺序扫描20层20次1.4 B 树的页分裂与合并B 树在插入数据时如果叶子页已满会触发页分裂——将满页一分为二中间关键字提升到父节点。页分裂的代价很高需要分配新页、移动数据、更新父节点和链表指针。顺序插入主键自增新数据总是插入最后一页页分裂极少发生。这是为什么 InnoDB 推荐自增主键——插入顺序与 B 树顺序一致避免频繁页分裂。随机插入UUID 主键新数据可能插入任意位置频繁触发页分裂导致数据页碎片化、写入性能下降。UUID 主键还有另一个问题36 字节的字符串比 4 字节的 INT 主键大得多导致每个页存的关键字更少树更高。️ 二、聚簇索引与二级索引回表·覆盖索引·ICP2.1 聚簇索引数据即索引InnoDB 的聚簇索引Clustered Index是一种数据即索引的设计——B 树的叶子节点存储完整的行数据而不是指向数据的指针。聚簇索引就是表数据的物理存储顺序。聚簇索引的规则如果表有主键主键就是聚簇索引如果没有主键第一个非空 UNIQUE 列是聚簇索引如果都没有InnoDB 自动生成一个 6 字节的隐藏 ROW_ID 作为聚簇索引聚簇索引的优势等值查询只需 1 次 B 树查找直接拿到完整行数据范围查询沿叶子链表扫描数据物理相邻缓存友好主键查询是最快的查询路径聚簇索引的劣势每表只能有 1 个聚簇索引数据只能按一种顺序物理存储随机主键插入导致频繁页分裂二级索引需要回表见下文2.2 二级索引索引列 主键二级索引Secondary Index的 B 树叶子节点存储的是索引列的值 主键值而不是完整行数据。查找非索引列时需要先在二级索引中找到主键值再到聚簇索引中查找完整行数据——这个过程叫回表。二级索引 idx_name (name): 叶子: | nameAlice, id5 | → | nameBob, id3 | → | nameCarol, id8 | 聚簇索引 (id): 叶子: | id3, nameBob, age25, ... | id5, nameAlice, age30, ... | id8, nameCarol, age28, ... |回表的代价每回一次表就是一次 B 树查找2-3 次磁盘 IO。如果查询返回 1000 行最坏情况下需要 1000 次回表这是巨大的性能开销。2.3 覆盖索引消除回表覆盖索引Covering Index是指查询所需的所有列都包含在索引中无需回表。覆盖索引不是一种特殊的索引类型而是一种索引使用方式——当索引包含了查询的所有列时就发生了覆盖索引。-- 表: user(id, name, age, email)-- 索引: idx_name_age (name, age)-- ✅ 覆盖索引: 查询列 name, age 都在索引中SELECTname,ageFROMuserWHEREnameAlice;-- EXPLAIN: Extra Using index-- ❌ 非覆盖: email 不在索引中需要回表SELECTname,age,emailFROMuserWHEREnameAlice;-- EXPLAIN: Extra NULL (需要回表)覆盖索引在 EXPLAIN 的 Extra 列显示“Using index”——这是判断是否使用覆盖索引的标志。覆盖索引的设计技巧将高频查询的 SELECT 列加入联合索引使其成为覆盖索引。例如SELECT order_id, status, create_time FROM orders WHERE user_id 100可以建idx(user_id, status, create_time)覆盖索引。2.4 索引下推 ICP减少回表索引下推Index Condition PushdownICP是 MySQL 5.6 引入的优化。在没有 ICP 时存储引擎根据二级索引找到主键回表取出完整行再由 Server 层判断 WHERE 条件。有了 ICP存储引擎在索引层就判断 WHERE 条件中索引列的条件只有满足条件的行才回表。-- 索引: idx_name_age (name, age)SELECT*FROMuserWHEREnameLIKEA%ANDage30;-- 无ICP: 存储引擎找到所有 name LIKE A% 的主键 → 逐行回表 → Server层过滤 age30-- 有ICP: 存储引擎在索引层同时过滤 name LIKE A% AND age30 → 只回表满足条件的行ICP 在 EXPLAIN 的 Extra 列显示“Using index condition”。ICP 适用于联合索引中WHERE 条件包含索引列但无法全部使用最左前缀时LIKE 右模糊查询name LIKE A%配合其他索引列条件时2.5 聚簇索引 vs 二级索引对比维度聚簇索引二级索引叶子节点存完整行数据索引列 主键数量1 个/表多个/表等值查询1 次 B 树查找2 次 B 树回表范围查询链表扫描逐行回表插入顺序按 PK 顺序任意位置页分裂随机插入易分裂影响小空间占用数据本身额外空间 三、索引设计原则与常见陷阱3.1 什么时候该建索引高频 WHERE 过滤列如果某列频繁出现在 WHERE 条件中且区分度高如手机号、邮箱应该建索引。区分度 COUNT(DISTINCT col) / COUNT(*)越接近 1 越好。JOIN 关联列JOIN 的关联列如果没有索引MySQL 会使用嵌套循环连接Nested Loop Join驱动表的每一行都要在被驱动表中全表扫描——这是性能灾难。ORDER BY / GROUP BY 列如果 ORDER BY 或 GROUP BY 的列有索引MySQL 可以利用索引的有序性避免 filesort文件排序大幅提升性能。DISTINCT 去重列索引本身有序且唯一DISTINCT 可以利用索引快速去重。3.2 什么时候不该建索引区分度低的列如性别只有 2 个值、状态只有几个值。索引的选择性差MySQL 优化器可能判断回表成本 全表扫描直接放弃索引。频繁更新的列每次 UPDATE/INSERT/DELETE 都需要更新索引 B 树索引越多写入越慢。很少用于查询的列索引占用磁盘空间二级索引的大小可能接近数据本身不查询的列建索引是浪费。小表数据量很小的表如配置表几十行全表扫描比索引查找更快数据可能全在内存中。3.3 联合索引与最左前缀原则联合索引INDEX idx_abc (a, b, c)的 B 树按 a → b → c 的顺序排列。查找时必须从最左列开始不能跳过——这就是最左前缀原则。匹配规则查询条件索引使用说明WHERE a1✅ 用 a最左列WHERE a1 AND b2✅ 用 a,b前两列WHERE a1 AND b2 AND c3✅ 用 a,b,c全部列WHERE b2❌ 不用跳过 aWHERE c3❌ 不用跳过 a,bWHERE a1 AND c3⚠️ 只用 a跳过 bc 不能用WHERE a1 AND b2 AND c3⚠️ 用 a,bb 范围后 c 不能用WHERE a IN(1,2) AND b2✅ 用 a,bIN 等值联合索引列的排列原则等值过滤列放前面WHERE a1 AND b2 → a 在前b 在后范围过滤列放后面范围条件后的列无法使用索引ORDER BY/GROUP BY 列放后面排序和分组可以利用索引有序性区分度高的列放前面更快缩小搜索范围3.4 索引失效的 7 大场景1. 函数计算对索引列使用函数索引失效。-- ❌ 索引失效: YEAR()函数SELECT*FROMordersWHEREYEAR(create_time)2024;-- ✅ 改为范围查询SELECT*FROMordersWHEREcreate_time2024-01-01ANDcreate_time2025-01-01;2. 隐式类型转换字符串列与数字比较时MySQL 会将字符串转为数字索引失效。-- ❌ 索引失效: varchar列与数字比较SELECT*FROMuserWHEREphone13800138000;-- phone是varchar-- ✅ 加引号SELECT*FROMuserWHEREphone13800138000;3. 左模糊查询LIKE %xxx无法使用索引B 树按前缀排序LIKE xxx%可以。-- ❌ 索引失效: 左模糊SELECT*FROMuserWHEREnameLIKE%Alice;-- ✅ 右模糊可走索引SELECT*FROMuserWHEREnameLIKEAlice%;4. OR 连接非索引列OR 的一侧没有索引整个条件无法使用索引。-- ❌ 索引失效: b没有索引SELECT*FROMuserWHEREa1ORb2;-- ✅ UNION ALL替代SELECT*FROMuserWHEREa1UNIONALLSELECT*FROMuserWHEREb2;5. 不等于!和可能导致索引失效——优化器可能判断回表成本过高选择全表扫描。-- ⚠️ 可能失效SELECT*FROMuserWHEREstatus!1;-- ✅ 改为INSELECT*FROMuserWHEREstatusIN(0,2,3);6. 最左前缀违反跳过联合索引的前导列索引无法使用。-- ❌ 索引 idx(a,b,c) 失效SELECT*FROMuserWHEREb2ANDc3;-- ✅ 包含最左列SELECT*FROMuserWHEREa1ANDb2ANDc3;7. 优化器放弃索引即使语法上可以用索引优化器也可能判断回表成本 全表扫描选择全表扫描。这通常发生在区分度低的列上——例如WHERE gender M返回 50% 的行回表 50% 的行比全表扫描还慢。3.5 索引设计实战案例案例 1用户表查询优化-- 原始查询SELECTid,name,ageFROMuserWHEREnameAliceANDage20ORDERBYageLIMIT10;-- 索引设计: idx_name_age (name, age)-- name等值过滤 → age范围过滤排序 → 覆盖索引(id,name,age都在索引中)-- EXPLAIN: typeref, ExtraUsing where; Using index案例 2订单表多条件查询-- 原始查询SELECTorder_id,status,amountFROMordersWHEREuser_id100ANDstatusPAIDANDcreate_time2024-01-01ORDERBYcreate_timeLIMIT20;-- 索引设计: idx_user_status_time (user_id, status, create_time)-- user_id等值 → status等值 → create_time范围排序-- 覆盖索引: order_id是主键已在索引中, status和create_time在索引中, amount需要回表-- 如果要完全覆盖: idx(user_id, status, create_time, amount)案例 3避免冗余索引-- ❌ 冗余: idx_a_b 包含了 idx_a 的所有功能INDEXidx_a(a),INDEXidx_a_b(a,b)-- ✅ 只保留联合索引INDEXidx_a_b(a,b)-- WHERE a1 也能用MySQL 性能调优实战系列进度篇号主题核心内容状态01索引原理本文B树/聚簇索引/回表/覆盖索引/ICP✅02执行计划EXPLAIN/type/key/Extra/优化器⏳ 下一篇03慢查询慢查询日志/pt-query-digest/优化案例待写04事务与锁ACID/MVCC/行锁/间隙锁/死锁待写05分库分表垂直拆分/水平拆分/ShardingSphere待写06监控Prometheus/Grafana/慢SQL告警/容量规划待写一句话总结索引原理三大维度B树InnoDB选B树做索引矮(3层千万行只需2-3次磁盘IO)/胖(每页存几百指针树更矮)/连(叶子链表范围查询无需回溯)。vs哈希不支持范围查询/vs B树非叶节点也存数据树更高/vs跳表20层磁盘IO不可接受/vs红黑树24层完全不可接受。页分裂随机插入导致频繁分裂所以推荐自增主键。B树的核心优势矮胖连完美适配磁盘IO和范围查询、聚簇索引与二级索引聚簇索引叶子存完整行数据每表1个主键即聚簇索引/二级索引叶子存索引列主键查找非索引列需回表。回表二级索引→聚簇索引每回一次表就是一次B树查找代价巨大。覆盖索引查询列全在索引中无需回表EXPLAIN显示Using index。索引下推ICPWHERE条件在索引层过滤减少回表MySQL 5.6默认开启。索引优化的核心减少回表覆盖索引消除回表ICP减少回表、索引设计原则该建高频WHERE/JOIN/ORDER BY区分度高。不该建区分度低/频繁更新/很少查询/小表。联合索引最左前缀等值过滤→范围过滤→排序分组。索引失效7大场景函数计算/隐式类型转换/左模糊/OR非索引列/不等于/最左前缀违反/优化器放弃。索引设计的哲学不建是减法减少写入开销建好是乘法查询加速。参考链接MySQL 8.0 Reference Manual - InnoDB IndexesMySQL Internals: InnoDB Page StructureIndex Condition Pushdown OptimizationHigh Performance MySQL, 4th Edition - Baron Schwartz
【MySQL 性能调优实战·第 01 篇】索引原理：B+ 树·聚簇索引·回表·覆盖索引——一切调优的起点
【MySQL 性能调优实战·第 01 篇】索引原理B 树·聚簇索引·回表·覆盖索引——一切调优的起点MySQL 性能调优实战系列开篇。如果你只学一个 MySQL 调优知识点那必须是索引。索引是 MySQL 性能的基石——一个设计良好的索引可以让查询快 10-1000 倍一个缺失的索引可以让全表扫描拖垮整个数据库。但索引不是加了就快——不理解原理就建索引等于蒙眼开车。MySQL InnoDB 的索引基于B 树为什么不是哈希表为什么不是 B 树为什么不是跳表因为 B 树矮3 层存千万行、胖每页多关键字、连叶子链表——完美适配磁盘 IO 和范围查询。InnoDB 有两种索引聚簇索引叶子存完整行数据每表 1 个和二级索引叶子存索引列 主键每表多个。二级索引查找非索引列需要回表——回到聚簇索引再查一次这是性能杀手。覆盖索引查询列全在索引中和索引下推 ICPWHERE 条件在索引层过滤是减少回表的两大武器。联合索引的最左前缀原则决定了索引列的排列顺序——等值过滤 → 范围过滤 → 排序分组。今天我们从 B 树原理、索引类型到设计原则与常见陷阱彻底掌握 MySQL 索引。 文章目录 一、B 树为什么 InnoDB 选它做索引️ 二、聚簇索引与二级索引回表·覆盖索引·ICP 三、索引设计原则与常见陷阱 一、B 树为什么 InnoDB 选它做索引1.1 从磁盘 IO 说起为什么索引要考虑磁盘MySQL 的数据存储在磁盘上磁盘 IO 是数据库性能的最大瓶颈。一次磁盘随机 IO 大约 10msSSD 约 0.1ms而一次内存访问约 100ns——磁盘 IO 比内存慢 10 万倍。因此索引的核心目标是尽量减少磁盘 IO 次数。InnoDB 以**页Page**为单位管理磁盘数据默认页大小 16KB。每次磁盘 IO 读取一页到内存的 Buffer Pool 中。索引的设计必须围绕如何用最少的页访问次数找到目标数据展开。1.2 B 树结构矮·胖·连B 树是一种多路平衡搜索树InnoDB 选择它作为索引结构因为它有三个关键特性矮B 树的每个节点可以存储大量关键字16KB 页可以存几百个指针树的高度很低。对于千万级数据B 树通常只有 3 层——根节点常驻内存查找只需 2-3 次磁盘 IO。根节点(常驻内存) / | \ 中间节点 中间节点 中间节点 ← 第2层: 1次IO / | \ / | \ / | \ 叶子→叶子→叶子→叶子→叶子→叶子 ← 第3层: 1次IO [数据] [数据] [数据] [数据] [数据] [数据]胖每个节点存储多个关键字和指针。假设每个关键字指针占 12 字节一个 16KB 页可以存约 1300 个指针。3 层 B 根节点 1 个 第二层 1300 个 第三层 1300×1300 16.9 亿个叶子节点每个叶子存若干行数据足以覆盖千万到亿级数据。连B 树的叶子节点通过双向链表连接。这个特性对范围查询至关重要——WHERE id BETWEEN 100 AND 500只需定位到 id100 的叶子节点然后沿链表向右扫描到 id500无需回溯父节点。1.3 B 树 vs 其他数据结构vs 哈希索引哈希索引等值查询 O(1)但不支持范围查询、排序、最左前缀匹配。数据库中大量查询是范围查询WHERE age 20、ORDER BY create_time哈希索引无法满足。vs B 树B 树的非叶子节点也存数据导致每个节点能存的关键字更少树更高4-5 层 vs 3 层磁盘 IO 更多。B 树非叶子节点只存关键字指针更胖更矮。B 树不支持叶子链表范围查询需要中序遍历回溯父节点。vs 跳表跳表在内存数据库Redis ZSET中表现优秀但层数多20 层意味着 20 次磁盘 IO不可接受。B 树 3 层就够了。vs 红黑树红黑树是二叉树每个节点只有 2 个子节点树高度 log₂(n)千万级数据约 24 层24 次磁盘 IO完全不可接受。数据结构等值查询范围查询树高度(千万行)磁盘IO哈希O(1)❌ 不支持-1次红黑树O(log n)需回溯~24层24次B树O(log n)需回溯4-5层4-5次B树O(log n)链表扫描3层2-3次跳表O(log n)顺序扫描20层20次1.4 B 树的页分裂与合并B 树在插入数据时如果叶子页已满会触发页分裂——将满页一分为二中间关键字提升到父节点。页分裂的代价很高需要分配新页、移动数据、更新父节点和链表指针。顺序插入主键自增新数据总是插入最后一页页分裂极少发生。这是为什么 InnoDB 推荐自增主键——插入顺序与 B 树顺序一致避免频繁页分裂。随机插入UUID 主键新数据可能插入任意位置频繁触发页分裂导致数据页碎片化、写入性能下降。UUID 主键还有另一个问题36 字节的字符串比 4 字节的 INT 主键大得多导致每个页存的关键字更少树更高。️ 二、聚簇索引与二级索引回表·覆盖索引·ICP2.1 聚簇索引数据即索引InnoDB 的聚簇索引Clustered Index是一种数据即索引的设计——B 树的叶子节点存储完整的行数据而不是指向数据的指针。聚簇索引就是表数据的物理存储顺序。聚簇索引的规则如果表有主键主键就是聚簇索引如果没有主键第一个非空 UNIQUE 列是聚簇索引如果都没有InnoDB 自动生成一个 6 字节的隐藏 ROW_ID 作为聚簇索引聚簇索引的优势等值查询只需 1 次 B 树查找直接拿到完整行数据范围查询沿叶子链表扫描数据物理相邻缓存友好主键查询是最快的查询路径聚簇索引的劣势每表只能有 1 个聚簇索引数据只能按一种顺序物理存储随机主键插入导致频繁页分裂二级索引需要回表见下文2.2 二级索引索引列 主键二级索引Secondary Index的 B 树叶子节点存储的是索引列的值 主键值而不是完整行数据。查找非索引列时需要先在二级索引中找到主键值再到聚簇索引中查找完整行数据——这个过程叫回表。二级索引 idx_name (name): 叶子: | nameAlice, id5 | → | nameBob, id3 | → | nameCarol, id8 | 聚簇索引 (id): 叶子: | id3, nameBob, age25, ... | id5, nameAlice, age30, ... | id8, nameCarol, age28, ... |回表的代价每回一次表就是一次 B 树查找2-3 次磁盘 IO。如果查询返回 1000 行最坏情况下需要 1000 次回表这是巨大的性能开销。2.3 覆盖索引消除回表覆盖索引Covering Index是指查询所需的所有列都包含在索引中无需回表。覆盖索引不是一种特殊的索引类型而是一种索引使用方式——当索引包含了查询的所有列时就发生了覆盖索引。-- 表: user(id, name, age, email)-- 索引: idx_name_age (name, age)-- ✅ 覆盖索引: 查询列 name, age 都在索引中SELECTname,ageFROMuserWHEREnameAlice;-- EXPLAIN: Extra Using index-- ❌ 非覆盖: email 不在索引中需要回表SELECTname,age,emailFROMuserWHEREnameAlice;-- EXPLAIN: Extra NULL (需要回表)覆盖索引在 EXPLAIN 的 Extra 列显示“Using index”——这是判断是否使用覆盖索引的标志。覆盖索引的设计技巧将高频查询的 SELECT 列加入联合索引使其成为覆盖索引。例如SELECT order_id, status, create_time FROM orders WHERE user_id 100可以建idx(user_id, status, create_time)覆盖索引。2.4 索引下推 ICP减少回表索引下推Index Condition PushdownICP是 MySQL 5.6 引入的优化。在没有 ICP 时存储引擎根据二级索引找到主键回表取出完整行再由 Server 层判断 WHERE 条件。有了 ICP存储引擎在索引层就判断 WHERE 条件中索引列的条件只有满足条件的行才回表。-- 索引: idx_name_age (name, age)SELECT*FROMuserWHEREnameLIKEA%ANDage30;-- 无ICP: 存储引擎找到所有 name LIKE A% 的主键 → 逐行回表 → Server层过滤 age30-- 有ICP: 存储引擎在索引层同时过滤 name LIKE A% AND age30 → 只回表满足条件的行ICP 在 EXPLAIN 的 Extra 列显示“Using index condition”。ICP 适用于联合索引中WHERE 条件包含索引列但无法全部使用最左前缀时LIKE 右模糊查询name LIKE A%配合其他索引列条件时2.5 聚簇索引 vs 二级索引对比维度聚簇索引二级索引叶子节点存完整行数据索引列 主键数量1 个/表多个/表等值查询1 次 B 树查找2 次 B 树回表范围查询链表扫描逐行回表插入顺序按 PK 顺序任意位置页分裂随机插入易分裂影响小空间占用数据本身额外空间 三、索引设计原则与常见陷阱3.1 什么时候该建索引高频 WHERE 过滤列如果某列频繁出现在 WHERE 条件中且区分度高如手机号、邮箱应该建索引。区分度 COUNT(DISTINCT col) / COUNT(*)越接近 1 越好。JOIN 关联列JOIN 的关联列如果没有索引MySQL 会使用嵌套循环连接Nested Loop Join驱动表的每一行都要在被驱动表中全表扫描——这是性能灾难。ORDER BY / GROUP BY 列如果 ORDER BY 或 GROUP BY 的列有索引MySQL 可以利用索引的有序性避免 filesort文件排序大幅提升性能。DISTINCT 去重列索引本身有序且唯一DISTINCT 可以利用索引快速去重。3.2 什么时候不该建索引区分度低的列如性别只有 2 个值、状态只有几个值。索引的选择性差MySQL 优化器可能判断回表成本 全表扫描直接放弃索引。频繁更新的列每次 UPDATE/INSERT/DELETE 都需要更新索引 B 树索引越多写入越慢。很少用于查询的列索引占用磁盘空间二级索引的大小可能接近数据本身不查询的列建索引是浪费。小表数据量很小的表如配置表几十行全表扫描比索引查找更快数据可能全在内存中。3.3 联合索引与最左前缀原则联合索引INDEX idx_abc (a, b, c)的 B 树按 a → b → c 的顺序排列。查找时必须从最左列开始不能跳过——这就是最左前缀原则。匹配规则查询条件索引使用说明WHERE a1✅ 用 a最左列WHERE a1 AND b2✅ 用 a,b前两列WHERE a1 AND b2 AND c3✅ 用 a,b,c全部列WHERE b2❌ 不用跳过 aWHERE c3❌ 不用跳过 a,bWHERE a1 AND c3⚠️ 只用 a跳过 bc 不能用WHERE a1 AND b2 AND c3⚠️ 用 a,bb 范围后 c 不能用WHERE a IN(1,2) AND b2✅ 用 a,bIN 等值联合索引列的排列原则等值过滤列放前面WHERE a1 AND b2 → a 在前b 在后范围过滤列放后面范围条件后的列无法使用索引ORDER BY/GROUP BY 列放后面排序和分组可以利用索引有序性区分度高的列放前面更快缩小搜索范围3.4 索引失效的 7 大场景1. 函数计算对索引列使用函数索引失效。-- ❌ 索引失效: YEAR()函数SELECT*FROMordersWHEREYEAR(create_time)2024;-- ✅ 改为范围查询SELECT*FROMordersWHEREcreate_time2024-01-01ANDcreate_time2025-01-01;2. 隐式类型转换字符串列与数字比较时MySQL 会将字符串转为数字索引失效。-- ❌ 索引失效: varchar列与数字比较SELECT*FROMuserWHEREphone13800138000;-- phone是varchar-- ✅ 加引号SELECT*FROMuserWHEREphone13800138000;3. 左模糊查询LIKE %xxx无法使用索引B 树按前缀排序LIKE xxx%可以。-- ❌ 索引失效: 左模糊SELECT*FROMuserWHEREnameLIKE%Alice;-- ✅ 右模糊可走索引SELECT*FROMuserWHEREnameLIKEAlice%;4. OR 连接非索引列OR 的一侧没有索引整个条件无法使用索引。-- ❌ 索引失效: b没有索引SELECT*FROMuserWHEREa1ORb2;-- ✅ UNION ALL替代SELECT*FROMuserWHEREa1UNIONALLSELECT*FROMuserWHEREb2;5. 不等于!和可能导致索引失效——优化器可能判断回表成本过高选择全表扫描。-- ⚠️ 可能失效SELECT*FROMuserWHEREstatus!1;-- ✅ 改为INSELECT*FROMuserWHEREstatusIN(0,2,3);6. 最左前缀违反跳过联合索引的前导列索引无法使用。-- ❌ 索引 idx(a,b,c) 失效SELECT*FROMuserWHEREb2ANDc3;-- ✅ 包含最左列SELECT*FROMuserWHEREa1ANDb2ANDc3;7. 优化器放弃索引即使语法上可以用索引优化器也可能判断回表成本 全表扫描选择全表扫描。这通常发生在区分度低的列上——例如WHERE gender M返回 50% 的行回表 50% 的行比全表扫描还慢。3.5 索引设计实战案例案例 1用户表查询优化-- 原始查询SELECTid,name,ageFROMuserWHEREnameAliceANDage20ORDERBYageLIMIT10;-- 索引设计: idx_name_age (name, age)-- name等值过滤 → age范围过滤排序 → 覆盖索引(id,name,age都在索引中)-- EXPLAIN: typeref, ExtraUsing where; Using index案例 2订单表多条件查询-- 原始查询SELECTorder_id,status,amountFROMordersWHEREuser_id100ANDstatusPAIDANDcreate_time2024-01-01ORDERBYcreate_timeLIMIT20;-- 索引设计: idx_user_status_time (user_id, status, create_time)-- user_id等值 → status等值 → create_time范围排序-- 覆盖索引: order_id是主键已在索引中, status和create_time在索引中, amount需要回表-- 如果要完全覆盖: idx(user_id, status, create_time, amount)案例 3避免冗余索引-- ❌ 冗余: idx_a_b 包含了 idx_a 的所有功能INDEXidx_a(a),INDEXidx_a_b(a,b)-- ✅ 只保留联合索引INDEXidx_a_b(a,b)-- WHERE a1 也能用MySQL 性能调优实战系列进度篇号主题核心内容状态01索引原理本文B树/聚簇索引/回表/覆盖索引/ICP✅02执行计划EXPLAIN/type/key/Extra/优化器⏳ 下一篇03慢查询慢查询日志/pt-query-digest/优化案例待写04事务与锁ACID/MVCC/行锁/间隙锁/死锁待写05分库分表垂直拆分/水平拆分/ShardingSphere待写06监控Prometheus/Grafana/慢SQL告警/容量规划待写一句话总结索引原理三大维度B树InnoDB选B树做索引矮(3层千万行只需2-3次磁盘IO)/胖(每页存几百指针树更矮)/连(叶子链表范围查询无需回溯)。vs哈希不支持范围查询/vs B树非叶节点也存数据树更高/vs跳表20层磁盘IO不可接受/vs红黑树24层完全不可接受。页分裂随机插入导致频繁分裂所以推荐自增主键。B树的核心优势矮胖连完美适配磁盘IO和范围查询、聚簇索引与二级索引聚簇索引叶子存完整行数据每表1个主键即聚簇索引/二级索引叶子存索引列主键查找非索引列需回表。回表二级索引→聚簇索引每回一次表就是一次B树查找代价巨大。覆盖索引查询列全在索引中无需回表EXPLAIN显示Using index。索引下推ICPWHERE条件在索引层过滤减少回表MySQL 5.6默认开启。索引优化的核心减少回表覆盖索引消除回表ICP减少回表、索引设计原则该建高频WHERE/JOIN/ORDER BY区分度高。不该建区分度低/频繁更新/很少查询/小表。联合索引最左前缀等值过滤→范围过滤→排序分组。索引失效7大场景函数计算/隐式类型转换/左模糊/OR非索引列/不等于/最左前缀违反/优化器放弃。索引设计的哲学不建是减法减少写入开销建好是乘法查询加速。参考链接MySQL 8.0 Reference Manual - InnoDB IndexesMySQL Internals: InnoDB Page StructureIndex Condition Pushdown OptimizationHigh Performance MySQL, 4th Edition - Baron Schwartz
