做后端开发年头越长碰到 SQL 慢查询的次数就越多。上周帮同事排查一个线上接口超时的问题报警里那条 SQL 跑了将近 4 秒表里也就 300 万行数据。同事很困惑“我明明给 where 条件里的字段建了索引为什么还是全表扫描”我拉出执行计划一看问题出在组合索引的字段顺序上——他把区分度最高的字段放在了中间最左前缀完全没利用上。这种典型的表索引使用错误在很多团队里每天都在发生。所以这篇内容不打算写成一节节课件的复述而是想用项目实战的视角把表索引从原理、结构、设计套路到线上故障排查串一遍。适合正在学数据库原理的开发者、需要优化接口性能的后端工程师以及准备系统梳理索引知识的 DBA 新人。看完之后你至少能独立回答这几个问题索引为什么能加速查询B 树到底长什么样组合索引怎么设计才不浪费为什么加了索引还是慢1. 表索引的本质一张表为什么需要“目录”1.1 没有索引时查询是怎么“硬扛”的很多刚入行的同学对全表扫描没有体感觉得“反正数据库会帮我找到数据”。实际上没有索引的时候数据库要定位一行数据唯一办法就是从头到尾扫描整张表的每一个数据页。你可以把一张表想象成一本没有目录、也没有页码的书想找某一句话只能从第一页翻到最后一页。这个过程对数据库来说就是顺序读一个数据页一个数据页读进去把每一行的目标字段取出来做判断命中条件就返回。这里有一个很关键的量化概念磁盘 IO。传统机械硬盘随机读一次大约 10ms 级别SSD 虽然快很多但相比内存访问仍然有数量级差距。一张 300 万行的订单表如果每行约 200 字节数据总量在 600MB 左右全表扫描意味着数据库可能要读上万个数据页。哪怕 SSD 顺序读性能不错也要几百毫秒如果是机械磁盘跑到秒级是常有的事。我见过很多线上案例一条查询本来只需要返回几十行结果却因为缺索引把整张几百万行的表翻了个底朝天。这就是慢查询最常见的源头之一。更要命的是这种慢会随着表数据量增长线性恶化——今天跑 1 秒半年后可能变 5 秒而你什么都没改。1.2 索引的本质用“冗余”换“速度”索引做的事情其实很原始把数据重新组织一份有序的“目录”用额外的存储空间换掉查询时的全表扫描。你建了一个 index on user_order(user_id)数据库就会额外维护一棵按 user_id 排序的树结构树里只存索引键和对应行的位置指针。查询的时候数据库不是去翻整张表而是先在索引树上快速定位到 user_id 对应的叶子节点拿到行指针后再去表里取完整数据。用字典来类比非常贴切。没有索引时查一个字要从第一页开始挨个找有索引时相当于有了拼音检字表先精确翻到拼音所在页码再跳到对应正文。数据库索引和这个思路本质上是一模一样的。但天下没有免费的午餐。索引带来的代价同样清晰每次插入、更新、删除数据时索引结构也要同步更新写入性能会下降索引本身要占磁盘空间设计不当会白占地方还拖慢优化器一张表上的索引越多优化器做执行计划时的选择成本也越高。所以索引设计本质上是在“读性能”和“写性能、存储成本”之间做权衡。很多人只记住了索引能加速没记住它是拿什么换来的结果就是遇到频繁写入的表就乱建索引最后写入慢、锁冲突多得不偿失。2. 索引的数据结构为什么数据库偏爱 B 树2.1 B 树为什么能撑住千万级数据面试里最常问的一个问题是“为什么 InnoDB 的索引用 B 树而不是红黑树或者哈希”。答案的核心在于磁盘 IO 次数。一个存储在磁盘上的数据结构查询效率最重要的指标不是时间复杂度而是“要读多少个磁盘块”。B 树的特点是非叶子节点只存索引键和下一层节点的指针不存实际数据所以每个节点可以塞下非常多的键。每个节点通常对应一个磁盘页默认 16KB假设每个索引键加指针合计约 24 字节那么一个节点的扇出大约有 682 个分支。两层的 B 树能存下约 46 万条索引项三层大约 3 亿条。也就是说千万级数据量的表一次索引查找只需要读 3 个左右的数据页。这和全表扫描上万个页相比差距是几千倍。红黑树虽然是平衡二叉树但每个节点只存一个键树高是 log2N。1000 万数据大概要 24 层也就是最坏情况要读 24 个磁盘页。B 树只需要 3 层左右。哈希索引虽然等值查找能到 O(1)但无法处理范围查询也无法支持排序所以主流引擎的默认索引结构仍然是 B 树。我补充一个实操层面的细节B 树的叶子节点之间是用链表串起来的而且叶子节点本身直接包含了完整行数据或行的指针聚簇索引包含完整行记录二级索引包含主键值。这个设计让“SELECT * WHERE id BETWEEN 1000 AND 2000”这类范围扫描变得非常顺畅——数据库只要找到第一个符合条件的叶子节点然后顺着链表一路向后读就行不需要反复回溯树根。2.2 哈希索引、全文索引另外两种常见形态虽然 B 树是绝对主力但索引家族里还有几个重要成员。一个是哈希索引基于哈希表实现等值查询速度极快但完全没法做范围查询也没法用于排序。InnoDB 里有个很特殊的自适应哈希索引它不是由用户创建的而是存储引擎在检测到某些热点索引页被反复等值访问时自动加上的缓存结构对 DBA 来说基本透明但了解它能帮你解释“为什么这条查询越跑越快”。另一个是全文索引它的底层是倒排索引专门处理“在一大段文本里找某个词”的场景。你对一篇文章做 like %关键词%普通 B 树帮不上忙只能全表扫但全文索引可以维护“单词 - 所在行”的映射关系查询时直接定位到行。做知识库、文章搜索、站内搜索的时候非常有用。MySQL 里用 MATCH ... AGAINST 语法配合 ngram 解析器还能支持中文搜索。不同索引形态的适用场景我整理成了下面这张表方便对照选择索引类型底层结构擅长场景明显短板B 树索引B 树等值、范围、排序、前缀查询对非前缀模糊查询无能为力哈希索引哈希表精确等值匹配不支持范围、排序、部分匹配全文索引倒排索引文本关键词搜索存储成本高不适合精确值查询空间索引R 树地理位置、几何数据普通业务基本用不上3. 实战认知执行计划里的索引真相3.1 EXPLAIN 是索引体检的起点我调 SQL 性能的第一步永远是 EXPLAIN不是直接加索引。MySQL 里在慢查询前加 EXPLAIN 关键字就能看到优化器打算怎么执行这条语句。关键要看四列type、key、rows、Extra。type 列表示访问类型从好到差大概是 system const eq_ref ref range index ALL。前几个意味着直接命中唯一索引或者主键是非常理想的状态ref 表示用普通非唯一索引做等值匹配range 是索引范围扫描index 是遍历索引树虽然没全表扫描但也不快ALL 就是全表扫描属于危险信号。key 列表示优化器实际用到的索引名。很多人建了索引但发现 key 是 NULL说明这条 SQL 压根没走索引这时候就要看为什么。rows 列是优化器预估要扫描的行数这个数是估出来的不一定准但数量级很能说明问题。同一张表同样的 where 条件rows 从 300 万变成 300性能差异一眼就能看出来。我最开始学执行计划的时候犯过一个错误只盯 key 列看到有索引就以为万事大吉。实际上 key 走了索引但 rows 很大也很常见比如范围查询里 20% 的数据都能命中优化器可能直接放弃索引。后面会专门说这种情况。3.2 聚簇索引、二级索引与回表要真正读懂执行计划必须理解 InnoDB 的两层索引结构。InnoDB 的表本身就是一个聚簇索引主键索引的叶子节点直接存了整行数据。所以基于主键的查询找到叶子节点就等于找到了所有列不需要额外的“查字典正文”步骤。而我们对 user_id 这类字段建的索引属于二级索引它的叶子节点存的是“索引字段值 主键值”。什么意思如果查询是 SELECT * FROM user_order WHERE user_id 10086数据库先在二级索引树上找到 user_id 为 10086 的叶子节点拿到主键 id然后再通过主键回到聚簇索引上把完整行数据读出来。这个过程叫回表。回表多了一次随机 IO在数据量大时是主要性能损耗。所以就有了覆盖索引的优化思路让二级索引直接覆盖你需要的全部字段这样查询只需访问索引树本身完全不用回表。比如 SELECT id, user_id FROM user_order WHERE user_id 10086如果存在索引 (user_id)那这个查询只需要读二级索引就能返回Extra 里会看到 Using index这就是覆盖索引生效了。实际项目中我会把高频查询里 select 的字段和组合索引的字段对齐尽量把常用查询变成覆盖查询这是收益最大且最容易被忽视的优化手段。4. 索引设计套路从业务 SQL 反推索引4.1 先从慢查询日志里找“高频 SQL”做索引设计最忌讳的是不看业务查询凭感觉给每个字段建一个索引。正确做法是从慢查询日志和实际业务场景出发找到那些高频、关键、耗时的 SQL针对它们设计索引。评论区很多同学问“一张表到底建几个索引合适”答案永远取决于这张表的查询模式。具体操作分三步。第一步打开慢查询日志并设置合理阈值比如 long_query_time 1跑一阵子收集真实数据。第二步把收集到的慢 SQL 按执行次数和总耗时排序挑出Top 10。第三步对每条 SQL 提取 where、join、order by、group by 里涉及的字段这些就是索引的候选列。举个例子一个典型的电商订单查询是“查某个用户在某个时间段内的订单列表按创建时间倒序”SELECT id, order_no, amount, status FROM user_order WHERE user_id ? AND create_time ? ORDER BY create_time DESC LIMIT 50。这里高频条件字段是 user_id 和 create_time排序字段也是 create_time所以组合索引 (user_id, create_time) 是显而易见的正确选择。user_id 做等值筛选create_time 既做范围过滤又做排序一个索引同时覆盖了筛选和排序需求。4.2 组合索引设计最左前缀与区分度组合索引是索引设计中最容易翻车的部分。B 树对组合索引的排序规则是先按第一个字段排第一个字段相同的再按第二个字段排依此类推。所以查询条件必须从索引最左边的字段开始连续匹配才能有效利用索引。这就是“最左前缀原则”。基于这个原则设计组合索引有几个实用规则。等值条件的字段要放在最前面范围条件的字段放后面。因为范围条件一旦出现后面的索引字段就基本用不上了。比如索引是 (user_id, create_time)SQL 是 WHERE user_id 1 AND create_time 2024-01-01那么 create_time 可以做范围定位排序也能利用索引但如果反过来建了 (create_time, user_id)而你经常用 user_id 做等值筛选这个索引就等于自废武功。要优先把区分度高的字段放在前面。区分度可以简单计算SELECT COUNT(DISTINCT column_name) / COUNT(*) FROM table。结果越接近 1说明字段越不重复索引选择效果越好。性别这种区分度只有 0.5 的字段单独建索引基本没有意义优化器极大概率会放弃它。一个真实案例我刚带团队时有个同事给订单表建了索引 (status, user_id, create_time)。status 只有几个枚举值区分度极低被放在最左边。结果 user_id 的等值过滤根本用不上索引前缀查询照样扫描大量行。调整成 (user_id, create_time, status) 之后同样的 SQL 从 2.3 秒降到 40 毫秒。这个例子我每次讲培训都要提因为太典型了。4.3 唯一索引、普通索引怎么选主键字段天然是唯一索引业务上如果有唯一性约束比如订单号、身份证号应该建唯一索引。唯一索引的好处除了约束数据还能给优化器更多信息帮助它生成更优执行计划。但需要注意业务上并不强制唯一但经常作为查询条件的字段比如 user_id建普通索引就够了没必要刻意建成唯一索引。唯一索引有额外的唯一性检查成本写入时会多一次查找判断对写入频繁的表有一定影响。另外很多人纠结前缀索引的问题。对于很长的字符串字段比如 URL、长文本标识B 树索引如果整列建索引体积会很大。可以用前缀索引只取前 N 个字符建索引ALTER TABLE article ADD INDEX idx_url(url(30))。要确定 N 值可以对比不同前缀长度下的区分度通常取到区分度接近整列 80%-90% 就够。比如整列区分度是 0.92前缀长度 30 的区分度是 0.88那取 30 就是划算的。5. 索引的维护成本与监控治理5.1 索引不是越多越好我在很多项目里见过“索引囤积症”开发同学为了防止慢查询把 where、order by 里出现过的字段全部单独建了索引。一张表十几个索引写入慢得离谱不说磁盘占用翻了几倍。更麻烦的是优化器要在这么多索引里做选择很容易选错执行计划产生本来可以避免的随机读。判断索引是否冗余有一个最常用的标准如果一个索引的前缀字段和另一个索引完全一致那它很可能就是多余的。比如你已经有了索引 (a, b, c)那么单独的索引 (a) 和 (a, b) 基本都是冗余的因为前者完全能覆盖后者的查询需求。定期用 SHOW INDEX 和查询 performance_schema 里 schema_unused_indexes 视图可以找出那些建了但从没被用过的索引确认后直接删除。当然删除索引要谨慎。先在测试环境关掉该索引并压测确认没有查询路径依赖它之后再在低峰期操作。线上 DDL 用 ALTER TABLE 直接删索引一般很快但如果是加索引在 MySQL 5.6 之后可以用 ONLINE DDLALGORITHMINPLACE不过还是建议在低峰期执行避免长时间的元数据锁影响业务。5.2 碎片、统计信息与优化器辅助索引经过大量增删改之后会产生碎片。碎片率高的索引叶子节点页利用率低、逻辑顺序和物理顺序不一致导致明明走了索引却要读很多额外的页。可以定期执行 ANALYZE TABLE 更新统计信息让优化器拿到准确的行数估算也可以用 ALTER TABLE xxx ENGINEInnoDB 重建表来整理物理碎片。不过重建表代价不小一般放在维护窗口做。关于优化器辅助两个特性值得了解。一个是索引下推Index Condition PushdownICP优化器把 where 条件中能下推的部分过滤动作下推到存储引擎层在索引遍历过程中提前进行判断减少回表次数。另一个是 MRRMulti-Range Read把回表的随机读取优化成批量排序后再顺序读取对范围查询和大批量数据读取很有帮助。这些不是你自己配置的参数但理解它们能帮你解释为什么同样的索引在 MySQL 新版本上表现更好。6. 常见问题排查索引失效与救火实录6.1 为什么加了索引还是全表扫描这是线上被问得最多的问题。我把常见的“索引失效”场景整理成一张速查表遇到问题直接对着排查场景典型写法原因与处理对索引列使用函数WHERE DATE(create_time) 2024-01-01函数让索引列失去有序性改写为范围查询或使用函数索引隐式类型转换WHERE user_id 123user_id 是 int 且索引存在字符串转数值可能导致无法走索引注意应用传入参数类型非前缀模糊查询WHERE name LIKE %张三%无法利用 B 树前缀匹配可考虑全文索引或拆词方案组合索引顺序不当索引 (a,b)SQL 只用 b违反最左前缀原则调整索引字段顺序优化器认为全表更快WHERE status 3该值占比超过 20%区分度太低索引回表代价反而大可考虑覆盖索引OR 连接非索引列WHERE id 1 OR status 1OR 两侧只要有一侧不走索引整体就无法走索引对索引列做表达式计算WHERE price * 2 100表达式破坏有序列改成 price 50这些场景我自己都踩过。最典型的是一次上线事故接口从 5 毫秒变成 5 秒检查发现代码里给 user_id 传了字符串参数数据库隐式转型导致索引失效。查出原因后所有人哭笑不得修复方式就是在应用层保证参数类型正确一行代码的事。6.2 优化器不选索引要不要强制走索引有时候你会碰到这种情况索引明明存在条件也符合最左前缀但 EXPLAIN 出来还是 ALL。多半是选择性不够好。比如 city 字段有 100 个城市却要查“上海”这个城市的数据占了全表 30%优化器算了一笔账走索引要回表 30% 的行还要随机 IO不如直接全表扫描来得快。这是优化器的理性选择不是故障。解决思路不是强制走索引而是思考怎么让查询本身更高效。一是可以 SELECT 用到的字段做成覆盖索引让回表消失这会显著改变优化器的判断二是结合业务条件缩小范围比如加上时间边界三是确认统计信息是否过期跑一次 ANALYZE TABLE 看看。极少情况下你确实确定索引更好可以在 SQL 里用 FORCE INDEX 指定但我不建议默认使用因为这种“人肉绑架”一旦数据分布变化反而会锁死优化器。我记得有一次处理一个报表慢查询业务方坚持说“强制走索引后变快了”但观察一段时间后发现强制索引在数据量翻倍后反而更慢因为不同时间段的数据分布完全不同。最终方案是重新设计了组合索引让优化器自己选择效果更稳定。所以我的经验是尽量让优化器认可你的索引而不是让它做违背直觉的选择。6.3 热点行更新与索引维护的连锁反应一个容易被忽略的问题索引本身也是锁竞争的一部分。对二级索引进行更新时在并发的写事务里可能产生间隙锁、插入意向锁冲突。高并发环境下某些频繁更新的索引字段会成为性能瓶颈点。我做秒杀系统的时候把库存字段从频繁更新的索引列中拆了出去因为库存变更会对索引树的叶子节点产生大量维护操作极大放大锁等待时间。这个经验可能比较少见但对支付、库存这类写入密集型项目价值非常大。7. 一点点个人体会数据库这块知识两个月不碰就会手生。表索引看起来是最基础的内容但越是基础越需要在真实场景里反复打磨认知。我遇到过很多人背诵了 B 树的结构、最左前缀的原则但到了线上还是会在字段类型、索引顺序、冗余判断这些看似不起眼的环节翻车。我自己最大的教训是时刻把“查询模式优先”放在心中而不是“给字段建索引”。先分析业务里真正的慢查询再反推索引设计比凭直觉堆索引有效得多。如果你正准备优化一张线上表的索引建议先做三件事查慢日志、跑 EXPLAIN、用区分度公式算一遍候选列。等这三件事做完大概率问题已经能定位到七八成了。最后送大家一句我常跟团队说的话索引不是越多越好也不是越少越好而是“恰好覆盖你真正需要的查询”最好。把这条原则落实到位大多数 SQL 性能问题都不会找上你。