搞 MySQL 的人迟早会在某个深夜面对一条慢 SQL。数据量刚到百万级一个简单的 WHERE 查询从 20 毫秒膨胀到 3 秒加了一个索引后又缩回 30 毫秒。这种“恐怖片变喜剧片”的体验我经历过不止一次。这篇索引笔记适合已经能熟练写 CRUD、但没系统撸过索引原理的同行也适合被复合索引和索引失效问题折磨过的同学。我会把 B 树的组织方式、聚簇索引和二级索引的区别、复合索引的建法与失效场景全部串起来最后再分享一套线上排查索引问题的实战手法。全程有依据、有数据、有踩坑记录可以直接照着落地。1. 索引的底层逻辑B树为什么能成为数据库标配1.1 先算一笔账三层B树能放下多少行数据InnoDB 的索引底层是一棵 B 树每个节点对应磁盘上一个页Page默认大小 16KB。B 树和普通二叉树最大的区别是非叶子节点不存完整行数据只存“索引键 指向子节点的指针”所以每个节点能容纳的子节点数量非常夸张。我来算一笔具体数字。假设主键是 BIGINT占 8 字节每个指向子节点的指针占 6 字节那非叶子节点上一条索引记录大约 14 字节。一个 16KB 的页理论上可以放16 * 1024 / 14 ≈ 1170 个索引条目也就是说一棵三层 B 树的非叶子部分只需要两个层级一个根节点 1170 个中间节点每个中间节点下面再接叶子节点。极限情况下叶子节点数量为 1170 * 1170约 137 万个页。如果每行记录平均 1KB一个叶子页能放 16 行那么整棵树能容纳的数据量是1170 * 1170 * 16 ≈ 2190 万行这个计算虽然粗糙但结论很直观千万级表的主键查询从根节点走到叶子节点只需要 3 次磁盘 I/O。这里还没有算 InnoDB 的内存缓冲池命中实际热数据可能连磁盘都不用碰。这也解释了为什么 B 树会碾压二叉搜索树和哈希索引。哈希索引做等值查询确实快但做不了范围查询也无法利用索引排序二叉搜索树在数据量变大后树高会飞速增长导致磁盘 I/O 次数成倍上升。B 树靠“矮壮”的形态和叶子节点之间的双向链表同时解决了等值、范围、排序三种场景。很多面试题问“为什么 MySQL 选 B 树”本质就是在问这组权衡。注意叶子节点的双向链表是整个 B 树容易忽略但极其关键的设计。它让范围查询、ORDER BY 排序、倒序扫描都能从定位到的叶子节点直接顺序推进不需要回根节点重新走一遍。1.2 聚簇索引与二级索引主键索引为什么特殊InnoDB 里有一类特殊的索引叫聚簇索引Clustered Index主键索引就是聚簇索引它的叶子节点直接存放整行数据。换句话说表数据本身就是按主键顺序组织的一棵 B 树。所以“主键索引”这个说法本质上是“聚簇索引”而不是普通索引。这个设计带来一个关键结论通过主键查询时索引里直接就是完整行不需要额外操作。也就是你常听到的“主键查询最快”的底层原因。普通索引在 InnoDB 里叫二级索引Secondary Index叶子节点不存整行数据只存“索引键 主键值”。以 user 表为例CREATE TABLE user ( id INT PRIMARY KEY, name VARCHAR(32), age INT, KEY idx_name (name) ) ENGINEInnoDB;执行SELECT * FROM user WHERE name 张三时MySQL 先走 idx_name 这棵二级索引 B 树找到 name 张三 对应的主键 id再拿这个 id 回聚簇索引里查完整行。这一步额外查找就叫“回表”table lookup。这里有一个值得多问一句的问题为什么二级索引叶子节点存的是“主键值”而不是“物理行地址”因为 InnoDB 的数据页在插入、删除、页分裂时会发生物理迁移如果二级索引存行地址每次移动都要更新所有二级索引而存主键值后数据移动只影响聚簇索引自己二级索引完全不用动代价小得多。这是可靠性和写性能之间的一个经典取舍。另外InnoDB 默认要求每张表必须有主键。如果你建表时不指定主键它会按“第一个非空唯一索引 - 隐藏 row_id”的顺序自动兜底。工程上我建议永远显式定义主键因为隐藏 row_id 不可见且不受你控制出问题后排查非常麻烦。1.3 索引表空间你建的那些索引到底占了多大地方很多人建索引时只会想“加了索引查询快”但没注意磁盘空间。InnoDB 的独立表空间模式下每张表对应一个 .ibd 文件索引和数据都放在这个文件里。顺手分享一个查看表空间占用的 SQLSELECT table_name, ROUND((data_length index_length) / 1024 / 1024, 2) AS total_mb, ROUND(data_length / 1024 / 1024, 2) AS data_mb, ROUND(index_length / 1024 / 1024, 2) AS index_mb FROM information_schema.tables WHERE table_schema your_db ORDER BY total_mb DESC;其中 data_length 对应聚簇索引的数据量index_length 对应所有二级索引的总数据量。看到这两个数字你就能直观感受到“新建一个索引 新建一棵完整的 B 树 额外占用一份磁盘空间”。这里有一个不少人踩过的坑早期 MySQL 默认的共享表空间模式下删除表和索引不一定能立刻释放磁盘空间文件碎片的空洞会留在 ibdata1 里。MySQL 8.0 默认开启 innodb_file_per_table也就是每张表独立表空间删除索引、重建表后磁盘才真正可见地释放。如果你还维护着老版本 5.6、5.7 的实例建表前最好确认一下这个参数否则运维排查磁盘暴涨时很难定位到具体表。2. 索引类型与取舍不是每个字段都需要建索引2.1 索引分类速查主键、唯一、普通、前缀、复合、全文MySQL 里常见索引类型很容易混我用一张表直接列清楚索引类型特点典型使用场景主键索引聚簇索引唯一且非空一张表只能有一个主键定位、聚簇组织数据唯一索引二级索引列值不能重复可允许 NULL手机号、邮箱等有唯一约束的业务字段普通索引二级索引无唯一限制高频查询字段加速检索复合索引多个字段联合建立遵循最左前缀where 中有多个条件前缀索引取字符串前 N 个字符建索引超长 VARCHAR减少索引体积全文索引倒排索引不是 B 树大文本的模糊搜索不支持中文分词前缀索引值得多说一句。比如一张表存了 URL 或者长描述字段如果直接对整列建索引索引体积会很大B 树层级也可能变高。用LEFT(column, N)建前缀索引能显著减少索引大小但有代价前缀索引无法用于覆盖索引也无法用它做精确的 ORDER BY 排序。所以长字符串字段要么用前缀索引换取体积和速度要么接受索引变大保功能得自己权衡。全文索引则是另一个维度的东西它底层是倒排索引结构不能替代普通 B 树索引也不建议在小项目里滥用。日常业务大部分场景B 树索引完全够用。2.2 区分度与基数SELECT COUNT(DISTINCT) 算一算建索引前先问一个核心问题这个字段的取值到底散不散索引区分度低数据库可能宁可全表扫也不走索引。区分度可以用一条 SQL 算出来SELECT COUNT(DISTINCT column_name) AS distinct_cnt, COUNT(*) AS total_cnt, COUNT(DISTINCT column_name) / COUNT(*) AS selectivity FROM table_name;selectivity 越接近 1说明字段选择性越高索引收益越大selectivity 很低比如性别字段只有 2 个值那单独建索引基本没用。原因很简单你通过索引找到大量记录最后还是要回表取行跟直接全表扫描差异不大优化器也会根据成本选择全表扫。另一个常用信息在SHOW INDEX FROM table_name的 Cardinality 列它表示索引中不同值的估算数量。如果 Cardinality 和表行数差了几万倍说明这个索引区分度很差。需要留意的是Cardinality 是 InnoDB 从采样页预估出来的不精确适合用来大致判断不适合做精细对比。我之前接手过一个线上表总数据量 800 万status 字段只有 3 个值但是前同事在 status 上单独建了索引。结果 EXPLAIN 死活不走索引被开发质疑“索引失效”。其实不是失效是优化器算完发现全表扫的成本更低。区分度低的索引在优化器眼里就是废索引。2.3 回表、覆盖索引与索引下推一次查询到底走了几步要理解索引优化必须把“一次 SELECT 的执行路径”拆开。假设有二级索引 idx_name(name)执行SELECT name, age FROM user WHERE name 张三;如果表里没有复合索引MySQL 会走三步先查 idx_name 定位主键 id再回聚簇索引取整行最后从行里过滤出 name、age 返回。百行、千行数据时没感觉但回表次数一旦和百万级行数挂钩查询就崩了。避免回表最常用的手段是覆盖索引Covering Index。如果索引中包含查询所需的全部列那直接从二级索引读出来就能返回不需要回表。比如SELECT id, name FROM user WHERE name 张三;idx_name 的叶子节点已经有 name 和主键 id这条查询直接可以从索引里取到结果EXPLAIN 的 Extra 列会显示 Using index。所以说“覆盖索引”不是一种类型而是一种“索引覆盖了查询列”的状态。还有一个容易忽略的优化叫索引下推Index Condition PushdownICPMySQL 5.6 引入。它把 where 里的一部分过滤条件下推到二级索引的遍历阶段提前过滤减少回表次数。EXPLAIN 的 Extra 列显示 Using index condition 就是这个机制在生效。比如复合索引 (a, b)查询WHERE a 1 AND b LIKE x%老版本必须先通过 a1 定位多个主键再回表过滤 b新版本能在二级索引遍历时直接过滤 b回表的次数大幅下降。这个特性你可能每天都在用但没意识到它才是复合索引“捡漏”查询能撑住的真实原因。3. 复合索引实战where a and b 到底怎么建索引3.1 最左前缀原则拆开来看什么叫“顺序匹配”复合索引本身不神秘它就是在 B 树里先按第一列排序第一列相同再按第二列排序以此类推。这种排序方式决定了它只能按照“从最左边开始连续匹配”的规则生效。假设有一张订单表CREATE TABLE orders ( id BIGINT PRIMARY KEY, user_id INT NOT NULL, status TINYINT NOT NULL, order_type VARCHAR(20), created_at DATETIME NOT NULL, KEY idx_user_status_created (user_id, status, created_at) ) ENGINEInnoDB;idx_user_status_created 能走索引的查询组合有WHERE user_id 100WHERE user_id 100 AND status 1WHERE user_id 100 AND status 1 AND created_at 2024-01-01WHERE user_id 100 AND created_at 2024-01-01user_id 用来定位created_at 借助 ICP 可以在索引里过滤不能走全索引的典型组合WHERE status 1WHERE created_at 2024-01-01WHERE created_at 2024-01-01 AND status 1原因就是这些查询没有从 user_id 出发B 树里 status 和 created_at 的排序只在 user_id 固定时才有意义。很多人记不住“最左前缀”的细节其实只要理解复合索引的字典式排序一切都顺理成章。排序场景同理。ORDER BY user_id, status可以利用索引顺序直接返回避免 filesort但ORDER BY status, created_at不走索引排序MySQL 只能自己排序。判断标准永远是一句话查询条件或排序条件能否匹配索引列从左到右的前缀顺序。3.2 按SQL场景定制索引三步设计法网上很多文章讲复合索引只会说“等值在前、范围在后”但实际业务里 SQL 千奇百怪照搬规则容易翻车。我自己的实操流程是三步第一步把高频 SQL 拆成三类谓词等值条件、范围条件、排序/分组条件。第二步确定索引列的顺序等值条件放在最前面范围条件放中间或后面排序字段放在能连上的位置。第三步用 EXPLAIN 验证重点看 key_len、type、Extra确认索引真的覆盖到了预期查询。用一个线上订单列表页举例。业务上最频繁的查询是SELECT * FROM orders WHERE user_id 100 AND status 1 AND created_at 2024-01-01 ORDER BY created_at DESC LIMIT 20;这个 SQL 的等值条件是 user_id 和 status范围条件是 created_at排序也是 created_at。按规则索引应该设计成ALTER TABLE orders ADD INDEX idx_user_status_created (user_id, status, created_at);这样 user_id 和 status 可以精确定位到少量数据created_at 可以在这个范围内继续走索引排序LIMIT 20 在索引层就能截断回表压力非常小。但如果你把范围条件 created_at 放在前面比如 idx_created_user_status那 user_id 和 status 就无法在索引里精确定位每次都要先扫一大段 created_at 范围的数据再逐个回表过滤 user_id 和 status。效果差很多。3.3 字段顺序怎么排等值在前范围在后“等值在前范围在后”这条经验值得多说一点原理。复合索引的排序决定第一列等值后第二列才能在“第一列的所有行”里有序一旦第一列是范围第二列的排序就不连续了。举个简单例子索引 (a, b)查询WHERE a 100 AND b 5a 走索引范围定位到一批主键但 b 在这个结果集里不是有序的b5 没法继续用索引精确定位最终只能对 a 范围内的每一行回表判断 b。如果把索引改成 (b, a)查询变成WHERE b 5 AND a 100b 精确命中等值条件在索引里定位到所有 b5 的范围a 在这个范围内依然有序可以直接走范围扫描效率明显更高。所以等值条件字段放范围条件前面不是玄学而是为了最大化利用复合索引的有序性。注意这里有个常见误区不是说范围条件不能建索引而是说范围条件适合放在复合索引的靠后位置因为范围之后的其他字段基本无法继续走索引精确定位了。还要警惕一个高频坑索引字段顺序和 SQL 写法不匹配。比如你建了 idx(user_id, status, created_at)但代码里经常写WHERE user_id 100 AND created_at 2024-01-01且不带 status那 status 这一列就变成“白建”索引仍能用到 user_id但 key_len 通常只会显示 user_id 部分的长度。如果业务不确定 status 会不会出现那索引设计时就要考虑把可能缺失的等值条件往后放把稳定出现的等值条件放最前。这种取舍EXPLAIN 的 key_len 一眼就能看出来。4. 索引失效的典型场景写成这样等于白建4.1 函数与表达式索引列不能“被加工”最常见的索引失效场景是对索引列做函数或运算。B 树里记录的是列原始值如果你在查询条件里写WHERE YEAR(created_at) 2024 WHERE price 1 100 WHERE SUBSTRING(name, 1, 3) abcMySQL 无法直接根据索引定位“YEAR(created_at) 2024”这种派生值只能把每行数据取出来计算后再过滤索引自然就废了。正确处理方式是把函数去掉改成等价的区间范围-- 错误 WHERE YEAR(created_at) 2024 -- 正确 WHERE created_at 2024-01-01 00:00:00 AND created_at 2025-01-01 00:00:00如果业务真的需要按函数结果查询MySQL 8.0 支持函数索引本质上是把函数计算后的结果单独存一遍。但函数索引在工作中用得少因为它无法直接替代已有的普通索引还需要额外的存储和计算成本。我的建议是能改写 SQL 就改写别让业务依赖函数索引。这里有个初学者常犯的认知误区函数如果写在查询条件左侧变量上不影响索引。比如WHERE price 100 / 1.1100 / 1.1 是常量表达式MySQL 会先算出结果再去索引定位索引完全不受影响。所以关键不是“有没有函数”而是“索引列有没有被加工”。4.2 隐式类型转换与字符集不一致隐式类型转换是生产环境里最阴间的索引失效原因因为 SQL 看起来完全正常。举一个真实场景。user 表的 mobile 是 VARCHAR(20)代码里写SELECT * FROM user WHERE mobile 13800138000;mobile 是字符串但条件是整数 13800138000。MySQL 的比较规则会把字段值转成浮点数进行比较于是索引列 mobile 被隐式转换B 树上的原始字符串无法直接定位索引失效。EXPLAIN 出来通常是 typeALLrows 拉满。但同样写法如果字段是 INT 主键 idSELECT * FROM user WHERE id 100;字符串参数会被转成数值因为转换作用在参数上而不是索引列上索引不会失效。这个差异很多人记反。核心判断标准就一条到底是索引列被转换还是查询参数被转换索引列被转换就失效参数被转换就不影响。字符集不一致也会触发类似的隐式转换。如果 JOIN 的两张表关联字段一个用 utf8mb4一个用 latin1MySQL 为了比较可能对其中一边做字符集转换导致另一边的索引失效。排查时如果发现两个表 DDL 里字符集不一致先统一再说。注意这种问题在 ORM 框架里更容易被掩盖。比如 MyBatis 传参如果写成数字类型和数据库字段类型对不上SQL 出来就是隐式转换。排查慢 SQL 时第一件事就是拿实际 SQL 和实际 EXPLAIN 验证别只看应用日志里的“模糊版” SQL。4.3 OR、LIKE 与 NOT IN别忽略 MySQL 的“悲观”选择OR 条件非常容易让索引失效。比如WHERE a 1 OR b 2如果 a 和 b 都有独立索引MySQL 在部分版本里可以做 index merge 合并索引但在实际业务表里经常因为成本估算、统计信息偏差干脆选择全表扫描。更麻烦的是如果 a 有索引、b 没有索引那这一条 SQL 基本不可能走索引。经验做法是尽量把 OR 改写为 UNION ALLSELECT * FROM t WHERE a 1 UNION ALL SELECT * FROM t WHERE b 2;LIKE 则是唯一一种“部分能用”的模糊查询。LIKE abc%可以用索引因为字符集排序允许前缀匹配LIKE %abc和LIKE %abc%通常无法用索引因为 B 树无法从“包含某个子串”的任意位置开始定位。NOT IN、!、 这类条件走索引与否取决于数据分布。严格来说 MySQL 可能用 range 扫描但代价不低而且一旦结果集占比上升优化器又会选择全表扫。所以别把“不等于”语义写在唯一性很高的字段上指望索引救命这类查询本质上是范围遍历不是点查。还有一个细节IS NULL 和 IS NOT NULL。MySQL 对 IS NULL 的优化相对友好可以用 ref_or_null 方式访问索引但 IS NOT NULL 通常就是全表扫。如果业务需要大量 IS NULL 查询建议在索引设计阶段专门评估可空字段或者用默认值替代 NULL。4.4 用 EXPLAIN 验证type/key/key_len 怎么读遇到任何索引疑问第一时间跑 EXPLAIN 看执行计划。以刚才的隐式转换为例EXPLAIN SELECT * FROM user WHERE mobile 13800138000\G结果里你会看到 typeALL、keyNULL、rows整表行数。改成字符串传参EXPLAIN SELECT * FROM user WHERE mobile 13800138000\G这时 typeref、keyidx_mobilerows 瞬间降到个位数。这就是“有没有用索引”最直接的证据。EXPLAIN 里我最看重三列type从好到差大致是 system const eq_ref ref range index ALL。看到 ALL 基本就是全表扫ref/range 说明索引在工作。key实际使用的索引名NULL 就是没走索引。key_lenMySQL 实际使用的索引字节长度。复合索引里通过比较 key_len 和理论最大长度可以判断到底用到了哪几列。比如 idx_user_status_created(user_id INT, status TINYINT, created_at DATETIME)如果 key_len4说明只有 user_id 参与如果 key_len5说明 user_id status 都用到了如果等于三列之和那就是完整覆盖。注意 NULL 字段会额外加 1 字节VARCHAR 会加变长长度字节算的时候别太教条。Extra 里看到 Using index 是覆盖索引看到 Using index condition 是索引下推看到 Using filesort 说明排序没用上索引通常需要调整索引顺序或加排序字段。这些状态词比 type 更能定位真实瓶颈。5. 索引管理与线上排查别让索引变成“负资产”5.1 冗余索引与无用索引sys 库直接告诉你索引不是越多越好。二级索引每多一个INSERT/UPDATE/DELETE 时就多维护一棵 B 树写放大的代价肉眼可见。MySQL 5.7 以后的 sys 库提供了两张非常实用的表-- 冗余索引完全包含在另一个索引里的低效索引 SELECT * FROM sys.schema_redundant_indexes; -- 长时间未使用的索引需要开启 performance_schema SELECT * FROM sys.schema_unused_indexes;schema_redundant_indexes 特别值得定期看。比如你既有 idx_user_id(user_id)又有 idx_user_status(user_id, status)那 idx_user_id 就是严格冗余的因为后者已经包含了前者的最左前缀。这种冗余索引在业务变更过程中非常容易积累每次大版本迭代后都应该查一遍。删除索引前还要确认 performance_schema 是否开启。如果没开启schema_unused_indexes 查出来可能是空表不代表索引真的没用过。需要动态开启UPDATE performance_schema.setup_consumers SET enabled YES WHERE name LIKE %events_statements_history%;另外大表删除索引建议用在线 DDLMySQL 8.0 的 ALTER TABLE 默认支持 INPLACE 算法但生产环境大表我更推荐 pt-online-schema-change 这类工具在后台以小批量的方式完成 DDL避免锁表和主从延迟。5.2 索引碎片与统计信息OPTIMIZE 和 ANALYZE 的区别索引和数据经过大量增删改后页会产生碎片B 树可能出现逻辑顺序与物理顺序不一致的情况查询会多出随机 I/O。判断碎片率可以查 information_schemaSELECT table_name AS tbl, ROUND(data_free / 1024 / 1024, 2) AS data_free_mb FROM information_schema.tables WHERE table_schema your_db ORDER BY data_free DESC;data_free 只是辅助参考核心还是看业务查询有没有变慢。清理碎片用OPTIMIZE TABLE your_table;但这里必须强调OPTIMIZE TABLE 会重建表大表操作时可能锁表或造成主从复制延迟线上最好交给 pt-online-schema-change 或安排低峰期执行。小表随便搞大表谨慎。很多运维会把 OPTIMIZE TABLE 和 ANALYZE TABLE 搞混。ANALYZE TABLE 只是重新估算索引基数更新优化器统计信息不会重建表。当 EXPLAIN 走了明显不对的执行计划比如明明有高性能索引却选了全表扫先执行ANALYZE TABLE your_table;试试通常能纠正统计信息偏差。只有统计信息正常但性能还是差才需要考虑 OPTIMIZE 或重新设计索引。5.3 常用排查 SQL 与日常维护节奏我这里整理一套常用的“索引体检”命令建议直接收藏-- 1. 查看表所有索引 SHOW INDEX FROM your_table; -- 2. 查看建表语句确认字符集和索引定义 SHOW CREATE TABLE your_table; -- 3. 查看 SQL 实际执行计划 EXPLAIN SELECT ...; -- 4. 查看某库下所有表的索引空间占比 SELECT table_schema, table_name, ROUND(index_length / 1024 / 1024, 2) AS index_mb FROM information_schema.tables WHERE table_schema your_db ORDER BY index_mb DESC; -- 5. 查看慢查询日志是否开启 SHOW VARIABLES LIKE slow_query_log%;日常维护节奏我的建议是每个月用 sys.schema_redundant_indexes 清一次冗余索引大促或版本上线前手动捞 top SQL 跑一遍 EXPLAIN检查有没有新引入的失效索引新索引上线不要在业务高峰期直接 ALTER优先用在线 DDL 工具慢查询日志的 long_query_time 设置成 1 秒比较合理太小日志量爆炸太大排查不到问题。5.4 常见问题速查表现象可能原因解决手段明明建了索引EXPLAIN 显示 ALL隐式类型转换、函数、低区分度、统计信息旧检查参数类型、改写 SQL、ANALYZE TABLE走了索引但查询还是很慢回表过多、区分度低、结果集过大尝试覆盖索引、重排复合索引顺序复合索引只使用了第一列范围条件位置靠前、最左前缀不满足调整字段顺序、改写 SQL 条件索引添加后写入明显变慢索引过多写放大严重清理冗余索引和无用索引磁盘空间异常增长碎片堆积、无用索引未清理OPTIMIZE 或删除无用索引OR 条件查询不走索引OR 双侧字段索引状态不一致改写为 UNION ALLORDER BY 出现 Using filesort排序字段不在索引前缀里把排序字段加入复合索引并放对位置最后分享一个我自己的小习惯。拿到一条慢 SQL我不会第一时间去建索引而是先用 EXPLAIN 看当前执行路径再结合 rows、key_len、Extra 判断到底是缺索引、索引建错了还是这个查询本身就不该出现在业务主流程里。这套流程帮我避掉了好几次“加了索引反而更慢”的尴尬。索引优化没有银弹真正有用的不是背规则而是把 B 树、最左前缀、回表和覆盖索引这几个模型理解透然后让 EXPLAIN 告诉你答案。