★ FEATURED ARTICLE 1. 索引到底是什么为什么SQL慢大多栽在它头上先说个我踩了三年的坑。刚工作那会儿我接手过一套报表系统数据库里有一张三百万行的订单流水表跑了几个月的分页查询每次打开页面要转五六秒的圈。当时第一反应是“加内存”“上缓存”折腾了好几天没效果。后来才发现那条SQL里的关联字段压根没建索引全表扫描从头扫到尾加一行索引之后查询从5秒降到了50毫秒。从此我对索引的态度就变了SQL调优百分之七八十的问题追根到底都是索引的问题。索引是数据库为了加速数据检索而维护的一种额外的数据结构。你把它理解成书的目录不翻目录一页页找和先看目录定位页码再翻过去速度完全不是一个量级。数据库里最常用的索引结构叫B树它能让查询、插入、删除的平均代价都保持在比较稳定的对数级水平而不是线性地跟着数据量膨胀。但索引不是免费的午餐。每建一个索引数据库在写入数据时就要额外维护这个索引结构相当于你每次往书里加内容都得顺手更新一下目录。所以索引不是越多越好而是要精准匹配你的查询模式。很多人一上来就给所有字段都建了索引结果写入性能被拖垮存储空间膨胀好几倍查询也没快多少典型的用力过猛。这篇文章我打算把索引讲透B树为什么能扛住千万级数据聚簇索引和二级索引的区别到底在哪哪些SQL写法会导致索引失效以及如何用最左前缀原则和覆盖索引设计一套高效的索引方案。适合已经会写增删改查、但在性能调优上还摸不着门道的开发者尤其是天天跟MySQL、PostgreSQL这类关系型数据库打交道的后端同学。2. B树结构拆解为什么它能扛住千万级数据2.1 从二叉搜索树到B树的选择逻辑很多人第一次接触索引原理都会先看到二叉搜索树。从算法角度讲二叉搜索树的查询时间复杂度是O(log n)听起来已经很快了为什么数据库不直接用呢因为数据库的数据存在磁盘上不是内存里。二叉搜索树每个节点只存一个键值树高度会随着数据量增大而快速变深。三百万行数据二叉树的深度可能接近二十多层意味着一次查询要做二十多次磁盘I/O。磁盘随机读一次大概是10毫秒级别20多次下来就是两百多毫秒这还没算网络和CPU的开销。B树的核心思路是让一个节点尽量多存数据把树压扁。一个节点不再是只存一个键值而是存一个页的数据MySQL InnoDB一个页默认16KB。假设一行索引数据大约占几十字节一个页能存几百个键值树的高度就能压缩到三四层。三百万行数据的B树一般三层就够了查询最多做三次磁盘I/O性能自然就上来了。这就是为什么数据库选B树而不是二叉搜索树磁盘I/O次数决定性能瓶颈B树通过高扇出特性把树高压到极致。这个道理直到我自己做了压测才真正体会到之前光看理论总觉得二叉树挺快的。2.2 叶子节点的链表结构藏着什么秘密B树有一个非常容易被忽略的设计所有数据都存在叶子节点上并且叶子节点之间用指针串成了一个有序链表。这个设计对范围查询来说简直是降维打击。举个例子你要查订单表中创建时间在某个时间段内的所有记录。如果索引是B树找到第一条匹配记录之后还得回到父节点去判断下一个节点在哪里来回跳转很费劲。如果是B树找到了起点之后直接沿着叶子节点的链表一路往后扫就行连续读取的效率极高而且这些叶子页在磁盘上往往也是物理连续的能命中顺序I/O比随机I/O快得多。这也是为什么范围查询BETWEEN、、在B树索引下表现得特别好的根本原因。有时候我们肉眼看到一条SQL慢但其实问题不在索引结构本身而是没让这条SQL走上正确的索引路径。这一点在后面聊索引失效的时候会展开。2.3 聚簇索引与二级索引的各自分工InnoDB的表数据本身就是按聚簇索引组织的。什么意思呢就是表里的数据行直接按主键的顺序物理存储在B树的叶子节点上。所以InnoDB表必须有一个主键如果你没建它会自动选一个非空的唯一索引实在没有就自己生成一个隐藏主键。聚簇索引的叶子节点存的是整行数据找到了主键就等于找到了整条记录不需要额外的回表操作。二级索引就不同了它叶子节点存的是索引字段的值加上主键值。比如你在user_name字段上建了索引这个索引的叶子节点存的是 user_name 和 id。查询的时候先通过二级索引找到主键再用主键去聚簇索引里查整行数据这个过程叫回表。回表本身不是问题问题在于回表会引入额外的随机I/O。如果你查询出来的行数很多每行都要回一次表性能就会急剧下降。这时候覆盖索引就派上用场了我后面会专门讲。聚簇索引和二级索引的分工很多人记不住我提供一个记忆锚点聚簇索引是“数据即索引索引即数据”二级索引是“先拿主键再回表换整行”。理解了这个后面优化SQL时你心里就有了一张路线图。3. 索引分类与实战选型别再把索引当成万能膏药3.1 普通索引、唯一索引与主键索引的区别索引在创建形式上分为几种各有各的适用场景。普通索引是最基础的它只加速查询不限制字段值重复纯粹是为了查询性能服务。唯一索引在普通索引的基础上加了唯一性约束同一个字段值不能重复出现既能加速查询又能保证数据在应用层之外多一道防线。主键索引是唯一索引的极端形态每个表只能有一个而且InnoDB里主键索引就是聚簇索引本身。开发的时候有一个容易被忽视的原则在保证业务正确的前提下能用普通索引就别为了省事加唯一约束。唯一约束会带来额外的写前检查成本在并发写入场景下还可能引发锁等待。我接过一个真实案例某团队在用户手机号字段上建了唯一索引结果每次批量导入数据时只要文件里有一个重复手机号整批数据全挂后来把唯一约束去掉改成应用层校验问题就迎刃而解了。3.2 组合索引的威力与代价真正体现索引设计功力的是组合索引也叫联合索引。比如你经常用 user_id 和 status 两个字段一起查订单那么建一个 (user_id, status) 的组合索引比分别建两个单列索引高效得多。原因在于组合索引可以一次性过滤掉两个维度的数据。单列索引只能帮你在第一步缩小范围第二步还得靠其他方式。另外组合索引还能避免多个单列索引同时存在时数据库优化器只选其中一个而浪费另一个的问题。组合索引的代价同样明显索引本身占用空间更大写操作维护的成本更高而且一旦设计不合理还会出现索引冗余。比如建了 (a, b, c) 的组合索引再单独建一个 (a) 的索引其实大部分情况下是浪费的因为最左前缀原则下 (a, b, c) 已经能覆盖 a 字段的查询。3.3 前缀索引与函数索引的适用边界字符串字段做索引是个老大难问题。比如文章表里有个 content 字段动辄几百上千字直接建索引既占空间又慢。前缀索引的思想是只取字符串的前几个字符建索引比如取前10个字符。代价是碰撞概率上升查询可能多扫一些记录但磁盘占用和写入成本大幅下降。什么时候用前缀索引字段很长但查询基本靠前缀匹配的场景比如订单号、流水号、URL路径。什么时候不适合如果查询条件经常是包含关系比如 LIKE %abc%前缀索引完全帮不上忙因为它没法从前缀推断中段的匹配关系。函数索引则是把字段经过计算后的结果建索引比如 DATE(create_time) 这种。MySQL 5.7之后原生支持函数索引PostgreSQL也支持表达式索引。实际工作中遇到按日期分组统计的慢SQL有时候用函数索引能立竿见影。但函数索引有个坑如果你在查询里对字段用了函数但索引是针对原字段建的优化器就没法用索引这是很多人突然发现“索引怎么没生效”的原因之一。4. 索引失效的八种典型场景这些坑我全踩过4.1 对索引列使用函数或计算这是索引失效的最高频原因。假设你在 create_time 字段上建了索引查询条件写成 WHERE DATE(create_time) 2024-01-01这个时候MySQL需要先对每一行的 create_time 做日期函数计算然后再比对索引自然就废了。正确的写法是 WHERE create_time 2024-01-01 AND create_time 2024-01-02让索引能直接按区间定位。基本原则是不要让数据库在索引列上做任何运算包括函数、算术表达式、类型转换都等于告诉优化器“这个字段没法直接比较”。实践中有个反直觉的地方查询条件右侧的计算没问题比如 WHERE create_time NOW() - INTERVAL 1 DAY 是能用索引的因为计算发生在非索引列那一侧左边的索引列还是原始值MySQL可以正常比较。4.2 隐式类型转换导致索引作废这种错误隐蔽性极强。某张表的 phone 字段是 varchar 类型查询写成 WHERE phone 13800138000手机号数字后面没加引号。MySQL会自动把字符串字段转换为数字再比较等于在索引列上加了隐式转换函数索引直接失效。解决方式很简单查询条件的类型必须和字段定义类型严格一致该加引号就加引号。推荐的排查手段是定期用 EXPLAIN 查看执行计划看到 type 列从 ref 变成 ALL或者 possible_keys 有但 key 为 NULL就要怀疑是不是类型转换了。4.3 LIKE 模糊查询的头部通配符陷阱WHERE name LIKE %张% 这种写法由于通配符在开头MySQL没办法利用B树的有序性定位起点只能全表扫描。但 WHERE name LIKE 张% 是能走索引的因为字符串排序天然有前缀顺序可以定位到“张”开头的第一条记录然后往后扫。所以业务上如果必须要做包含匹配要么接受全表扫描要么引入分词搜索方案或者用全文索引。指望一个B树索引搞定所有模糊搜索本身就是不合理的期望。4.4 组合索引的左缺位组合索引 (a, b, c)如果查询条件只写了 b 和 c不写 a索引大概率用不上。因为B树的索引顺序是先按 a 排再按 b 排再按 c 排没有 a 等于没有了第一层目录只能在二级索引的整个空间里翻找优化器会评估直接全表扫反而更快。这是最左前缀原则最核心的体现。查询条件里必须出现组合索引的最左字段索引才可能生效。但要注意最左前缀原则并非要求查询条件里一定包含所有字段只要有最左字段就可以逐步匹配比如只查 a或者 a 加 b都能用上索引。4.5 OR条件打破索引匹配WHERE a 1 OR b 2如果只有 a 字段有索引b 字段没有MySQL大概率会放弃索引走全表扫描。因为 OR 表示两边任一成立走索引能快速拿到 a1 的结果但 b2 的部分没法用索引数据库需要把两个结果集合并在无法同时加速的情况下优化器干脆全表扫。解决方案一般是把 OR 改写为 UNION或者确保 OR 两边的字段都建了索引。实际工作中我更倾向于改写查询逻辑把 OR 拆成两个查询再合并执行计划更可控。4.6 数据分布不均导致优化器放弃索引索引不是建了就一定会被用上。如果某列的值高度重复比如 status 字段只有两个值0和1分布比例是9:1那么查 status0 的时候哪怕有索引优化器计算后发现要扫90%的数据直接用索引的效率反而不如全表扫描于是它就会放弃索引。这种情况你怎么改写SQL都没用因为不是SQL的问题是数据分布的问题。应对方案是别在低区分度字段上单独建索引组合进别的查询条件里使用。所谓区分度就是字段去重后的数量占总记录数的比例区分度太低的字段单独建索引弊大于利。4.7 NULL值查询与索引擦肩而过WHERE column IS NULL 能不能走索引取决于数据库版本和实现。InnoDB的B树索引不存储NULL值所以在某些场景下IS NULL 查询无法走索引。更麻烦的是WHERE column NULL 这种写法压根不会报错只是永远不会返回数据因为SQL里NULL判断必须用IS NULL。设计表结构时给字段加 NOT NULL DEFAULT 约束是规避这一类问题最省事的办法。很多业务字段本身就不应该有NULL用空字符串或0代替完全不影响语义但能避免一堆索引上的麻烦。4.8 排序字段与索引顺序不匹配ORDER BY 同样能用索引加速。如果组合索引是 (a, b, c)查询里 ORDER BY b因为跳过了 a 字段排序没法走索引结果就会触发 filesort说白了就是数据库自己开一块内存来排序数据量大时性能灾难。还有一种情况是 ORDER BY a DESC, b ASC升降序混用InnoDB的索引顺序是固定的升降序不一致时也没法直接复用索引顺序需要额外排序。这部分在专门聊排序优化时再展开。5. 索引设计的实战心法从执行计划到覆盖索引5.1 用 EXPLAIN 看懂SQL的每一步想设计好索引第一步是学会看执行计划。EXPLAIN SELECT ... 是MySQL分析SQL执行路径的标准工具不需要真正执行查询就能输出计划安全得很。核心看几个列type、key、rows、Extra。type列从好到差依次是 system、const、eq_ref、ref、range、index、ALL。看到ALL就没走索引index表示扫了整棵索引树range已经是范围扫描的级别。实际生产环境追求的是ref和range如果出现index或ALL就要审视查询条件和索引设计的匹配度。key列告诉你实际用了哪个索引如果显示NULL说明没用索引。rows列是优化器估算扫描的行数值越小越好。Extra列出现 Using filesort 或 Using temporary 基本可以断定有排序或分组没有利用索引需要重点优化。我带团队的习惯是所有慢SQL上线前必须贴执行计划评审这个习惯帮我们挡掉了至少一半的线上事故。新人一开始觉得繁琐但看多了之后对索引的理解会直接从“背概念”变成“看路径”。5.2 覆盖索引查询字段全部住在索引里回表操作虽然不慢但次数一多就成了瓶颈。覆盖索引的思路是让二级索引的叶子节点覆盖你查询的所有字段这样查一次索引树就拿到了全部数据完全不需要回表。比如你有组合索引 (user_id, status)现在要查某个用户的订单状态统计查询字段写 status条件写 user_id那么整个查询在二级索引内部就完成了Extra列会显示 Using index。速度比回表快了一个数量级尤其在查询结果集很大的时候。设计覆盖索引有个常用的“黄金搭配”把最常用的查询条件字段放组合索引左边把频繁SELECT的字段加进组合索引。但索引不能无限膨胀字段太多会导致占用空间大、写入慢所以只在核心高频查询上做覆盖索引优化别每个查询都覆盖。5.3 最左前缀原则的实战应用组合索引的字段顺序直接决定索引的适用性。设计顺序的核心规则是区分度高的字段放前面等值查询字段放前面范围查询字段放后面。为什么区分度高的放前面因为B树先按第一字段排序区分度高的字段在前能快速把数据分成很多小组接下来的过滤更精准。如果反过来第一字段区分度很低比如性别那么索引的前半段大量数据都一样后面的字段排序对缩小范围帮助有限。等值查询字段放前面是因为等值条件能精确定位范围查询只能圈定一个区间。比如 (type, create_time) 和 (create_time, type)如果业务是查某个类型下某段时间的数据前者效率远高于后者因为先精确锁定 typecreate_time 的区间过滤只在前者的小范围内进行。5.4 冗余索引清理与索引下推线上库随着业务演进索引经常是只增不减的久而久之产生大量冗余索引。比如建了 (a, b, c)又建了 (a, b)后者其实就是前者的前缀完全可以删除。索引冗余不仅浪费空间还会让优化器产生选择困难延长执行计划生成的时间。清理索引前必须做两件事一是用慢查询日志找到真正高频的查询模式二是对照SHOW INDEX查看现有索引结构然后再删。如果数据库版本支持索引下推比如MySQL 5.6以上组合索引在查询时可以把 WHERE 条件中不属于索引最左部分的条件在索引遍历过程中就提前过滤掉而不是等回表后再过滤。这个特性不需要你做什么了解它有助于理解为什么某些看似“违反最左前缀”的查询性能表现也可以接受。6. 排序分组类SQL的索引优化比想象中要讲究6.1 filesort 与索引排序的取舍ORDER BY 字段如果恰好是组合索引的一部分并且顺序一致MySQL可以直接按索引顺序取数不需要额外排序。否则就需要 filesort在内存中排数据量超过内存容量时还会落盘性能急剧下降。判断方式是看 EXPLAIN 的 Extra 列是否出现 Using filesort。出现时优先尝试调整索引顺序让 ORDER BY 字段和索引顺序对齐。一个典型的优化案例某统计报表SQL按 create_time 排序原查询5秒我把组合索引从 (user_id, status, create_time) 调整为 (user_id, create_time, status)查询直接降到100毫秒因为排序路径从filesort变成了索引顺序扫描。当然不是所有排序都能被索引覆盖。如果需要按多个字段混合排序或者排序列根本没建索引合理使用 filesort 也是一种选择毕竟不能为了所有排序场景都建索引。6.2 GROUP BY 分组慢的思路GROUP BY 本质上是先分组再聚合优化路径和排序类似。MySQL中 GROUP BY 经常隐含排序操作有时你只是分组没想排序它默默给你排了一遍。可以在 GROUP BY 子句后面加 ORDER BY NULL 来消除无谓的排序这个技巧在明细分类统计时很见效。GROUP BY 能用索引的前提和 ORDER BY 类似需要字段顺序匹配组合索引。如果分组字段很多比如 GROUP BY a, b对应组合索引小字段内部有序时效率最高。还有一个实践心得分组统计尽量不要在明细表上直接做物化成中间汇总表。比如业务每天要按地区统计订单量与其每次实时扫几百万行来算不如每天定时把汇总结果写一张小表查询时只扫几百行。索引能加速单次查询但解决不了同一查询反复执行的根本浪费。6.3 分页深翻页的性能黑洞LIMIT 100000, 20 这种深分页查询是索引优化的大敌。虽然 LIMIT 字段本身不算索引失效但深分页意味着MySQL需要先扫描前十万行再丢弃它们然后才返回你要的20行。就算有索引扫描十万行二级索引回表耗时也完全不可控。优化方案之一是利用覆盖索引延迟关联。先只查主键等定位到了那20个主键之后再回原表取完整数据。SQL大概长这样先写一个子查询拿到 id 列表再用 JOIN 回原表。这样一来深度扫描只需要在索引树内部完成回表只发生在最终那20行上。如果业务允许游标分页是更好的选择记住上一页最后一条记录的主键下一页直接 WHERE id 上一页的id ORDER BY id LIMIT 20完全不扫描前面废弃的数据。这种方案在类目列表、消息中心、流水记录等按时间倒序翻页的场景非常适合。7. 慢SQL排查实操记录从发现问题到索引落地7.1 一个订单统计查询的完整优化过程前段时间我们遇到一个定时统计任务原本每天凌晨跑十几分钟业务数据量涨上来之后跑了一个多小时还没结束。我接手后先看慢查询日志定位到一条按天统计订单金额的SQL于是拿出执行计划。type 是 ALLrows 显示扫描了一百多万行Extra 里还有 Using temporary 和 Using filesort基本可以断定这条SQL一直被全表扫描。订单表里主键是 id业务上每天按 create_time 统计Create_time 上有一个单列索引但统计SQL还按 user_id 做了 JOIN联合条件没有匹配的组合索引。我的调整方案是把原来 create_time 单列索引扩展为组合索引 (user_id, create_time)让 JOIN 的等值条件先用索引定位再把时间条件做范围筛选。加了之后执行计划从 ALL 变成 ref 和 range扫描行数从一百多万降到几万任务从十几分钟降到了30秒以内。7.2 分页慢查询改写实战另一个案例是后台交易列表页每次翻到500页之后接口响应超过5秒。原始SQL是 SELECT * FROM trade_log ORDER BY create_time DESC LIMIT 100000, 50。执行计划的 Extra 列果然有 Using filesort 和 Using index condition说明排序没走索引还需要回表。我的第一版优化是把索引调整为 (create_time, id)ORDER BY 可以完全使用索引顺序避免 filesort。接着用延迟关联改写查询先查一个子查询 SELECT id FROM trade_log ORDER BY create_time DESC LIMIT 100000, 50拿到50个id之后再 JOIN 回原表取完整数据。实测从5秒降到了200毫秒。这是一个非常典型的经验延迟关联覆盖索引解决深分页几乎是无痛的通用方案。核心原因就是先把最贵的深度扫描限制在索引树内部回表只发生在最终结果上。7.3 日常索引巡检建议经验成型之后我们团队养成了每周看一次慢查询报告的习惯。具体做三件事查慢查询日志里出现频率最高的SQL导出执行计划分析是否走了合理索引对比索引大小和增长情况判断是否有冗余。这套动作跑了一个季度核心接口的P99延迟下降了近七成效果非常直观。另一个建议是建索引时命名规范别起没意义的名字。我们约定组合索引前缀是 idx_ 加所涵盖的字段名比如 idx_user_id_create_time一眼就能看出索引结构后面排查问题时省了很多事。8. 给新人的三个忠告和一个万能排查模板8.1 别为了索引而索引面试里经常有人背“索引能提升查询性能所以要多建索引”这个理解太片面。索引是空间换时间的典型方案每一个索引背后都是写性能和存储空间的代价。正确的做法是先定位慢SQL从慢SQL反推需要的索引而不是上来就无差别建索引。我见过一张表十几万行建了十二个索引写入被拖到无法忍受删掉六个之后反而整体变快了。8.2 理解数据分布之后再谈优化优化SQL时建议先摸清楚字段的区分度、数据量级、查询频率。高区分度字段可以靠B树快速定位低区分度字段适合作为组合索引的辅助维度。数据分布变了索引方案可能也要跟着调整没有一劳永逸的索引设计。8.3 排查问题先看执行计划网上优化SQL的文章很多但最可靠的永远是自己的执行计划。遇到任何慢查询第一步做 EXPLAIN看 type 和 key判断是否走了索引第二步看 Extra确认有没有 filesort 或 temporary第三步才考虑怎么写SQL更好。沿着这个流程走大部分索引问题的原因都会自动浮现。我在实际工作中总结了一个万能排查顺序屡试不爽先看执行计划再看索引区分度最后调整SQL写法。执行计划告诉你路是怎么走的区分度告诉你索引有没有价值SQL写法决定优化器能不能选对路。这三个都过了还慢再考虑改表结构或者上缓存顺序不能反。数据库优化的本质是对成本的理解磁盘I/O是成本内存排序也是成本回表是成本索引维护更是成本。搞清楚每一笔开销花在哪里索引设计自然心中有数。
阅读完成 · 觉得有帮助？
