★ FEATURED ARTICLE 我挑一个比较典型的例子开场之前在某业务系统里排查一条由慢查询负载导致的线上问题订单明细表有上千万行某个对账接口按“状态 创建时间 用户维度”三个条件过滤再按创建时间倒序取前 50 条。数据量小时一切正常等行数涨上来之后这条 SQL 直接跑到了十几秒数据库 CPU 也跟着飙高。第一次排查时我的第一反应是“加索引”但深入研究后发现随便加两个单列索引和正确设计一个复合索引效果能差出两个数量级。这篇文章就围绕数据库复合索引来写把底层排序原理、最左前缀原则、列顺序怎么定、执行计划怎么验证、索引失效怎么排查、索引维护成本怎么取舍一次讲清楚。适合刚接触数据库索引的开发者也适合正在被慢查询折磨、想动手优化线上索引的同学。1. 复合索引不是“多个索引”而是一棵多键排序树1.1 单列索引的局限为什么分别建两个索引不够很多同学在遇到“一个查询同时过滤两个字段”时直觉做法是给两个字段各自建一个单列索引。比如WHERE status PAID AND user_id 100就建idx_status(status)和idx_user(user_id)。这种思路不能说完全没用但要说清楚它的局限性。数据库确实存在某种“索引合并”机制通过分别扫描两个索引取交集再回表部分场景下能生效。但它的代价是需要分别扫两个 B 树再对结果做合并运算这个过程不一定比扫全表快。更重要的是一旦查询里包含范围条件、排序、分组、关联比如status PAID AND create_time 2024-01-01 ORDER BY user_id两个单列索引就很难充分发挥作用排序可能还是要走临时文件。而复合索引的做法是把多个字段作为一个联合键组织在同一棵 B 树里叶节点按复合键的顺序排好。这样一次索引扫描就能同时命中多个过滤条件范围扫描和排序也能顺手处理。这才是复合索引存在的根本意义把“多个条件的叠加过滤”变成“一次有序的定位”。1.2 复合索引的底层结构B 树里的联合键怎么排复合索引叶子节点的排序规则是“从左到右逐列比较”先按第一列排序第一列相同的行再按第二列排序第二列仍然相同的再按第三列排序依次类推。这个规则决定了后续所有使用方式。用一个具体例子说明。假设表里有四行数据ab13112012如果建了复合索引(a, b)B 树里这些行的实际排列顺序是先按a排序得到1, 1, 1, 2在a 1这三行内部再按b排序变成(1,1), (1,2), (1,3), (2,0)。注意这里(2,0)不会排到(1,1)前面因为第一列2 1第二列再小也没用。这就能解释一个常见疑问为什么WHERE b 0没法用(a, b)索引快速定位因为b在复合键里不是“第一排序键”整个 B 树不是按b组织的等于要在整棵树里把所有b值“捞出来”看一遍。1.3 列顺序是复合索引的灵魂对复合索引来说列顺序就是设计骨架。(a, b)和(b, a)是两棵完全不同结构的 B 树不是“换换位置”那么简单。我经常用一个电话簿来类比电话簿先按姓氏拼音排再按名字排。你想找“张伟”就会用“Z”定位到张姓区域再在张姓区域里找“伟”但如果你想找“名字叫伟的人”电话簿根本没有按名字索引只能从头翻。复合索引本质上就是这个道理第一列决定了“大的分区”后面的每一列都只能在前面列已经定位好的范围内继续细化。你设计索引时等于在决定电话簿按什么顺序排而排列顺序直接决定了哪些查询能走索引、哪些查询只能碰运气。理解这个之后再看最左前缀原则就顺理成章了。2. 最左前缀原则它能用在哪、不能用在哪一次说清2.1 最左前缀原则的准确含义最左前缀原则听起来抽象用排序规则复述一下就非常朴素一个复合索引(a, b, c)只有查询条件里“从第一列开始、连续使用若干列”时索引才能被高效使用。连续使用意味着a ?可以用a ? AND b ?可以用a ? AND b ? AND c ?也可以用。但b ?单独用、或者a ? AND c ?跳过了中间的b后续列就没办法继续参与索引的定位。为什么必须“从第一列开始”因为 B 树的第一排序键是a你如果完全不限定a整棵树没有任何一个范围能被预先锁定只能从根开始全量遍历叶节点。这就是最左匹配的物理含义。2.2 一张表看清哪些查询能走索引假设表上有复合索引(status, user_id, create_time)我总结成了下面这种对照表开发时可以直接对着判断WHERE 条件索引使用情况原因status PAID可用全部索引列第一列等值status PAID AND user_id 100可用全部索引列前两列等值status PAID AND user_id 100 AND create_time 2024-01-01可用全部索引列第三列是范围索引内范围扫描user_id 100不可用跳过了第一列status PAID AND create_time 2024-01-01部分可用只用statuscreate_time无法精确定位create_time 2024-01-01不可用第一列缺失ORDER BY status, user_id可用与复合索引排序方向匹配免去额外排序WHERE status PAID ORDER BY create_time部分可用排序无法完全走索引因为跳过了user_id看到中间几行可能会疑惑为什么status PAID AND create_time 2024-01-01只有status能用因为索引内部排序是status相同后先按user_id排而不是先按create_time排。即使你把status PAID锁定成一个分区这个分区内的数据也是按user_id组织的create_time ...没法借助索引直接跳转。2.3 跳过中间列的代价不报错但白干一半最容易被忽视的是WHERE status PAID AND create_time 2024-01-01这种“用第一列和第三列”的场景。数据库优化器仍然会尝试使用(status, user_id, create_time)索引通过status缩减范围之后对索引里大量create_time不满足条件的数据逐条判断符合条件的再回表。我把这种现象叫作“半失效”SQL 不慢到报警但也绝对不是你期望的速度。问题在于create_time条件没有参与到索引的范围裁剪中实际扫描的索引行数可能比最终结果行数大很多。行数一旦上千万这种“白干一半”的差距就是秒级和毫秒级的差距。3. 复合索引设计的最优顺序从查询条件反推列顺序3.1 先分等值条件和范围条件等值放前范围放后在设计复合索引时我习惯先把一个查询里的所有过滤条件分成两类等值条件、IN和范围条件、、、、BETWEEN。排序规则很简单等值条件尽量放在范围条件前面。原因是 B 树一旦在某列上用了范围定位后面的列就基本丧失了有序过滤能力。还是以(a, b, c)为例如果查询是where a 1 and b 10 and c 5索引最多能做到a 1等值 b 10范围c 5这一列没法参与索引范围裁剪因为b这一维已经展开了范围c的有序性只在单个b值下存在。正确做法是把等值列排前面范围列放后面。比如上述查询更好的索引形状是(a, c, b)让a 1和c 5先锁到很窄的区间再用b范围扫描。这看起来只是列顺序的调整实际能把扫描行数从几万降到几十。3.2 多个等值列之间怎么排让区分度更高的字段往前放等值列之间也有顺序讲究很多资料说“与 WHERE 条件的顺序一致就行”这只说对了一半因为 SQL 优化器会自己优化条件顺序真正决定 B 树结构的是索引定义顺序不是 SQL 书写顺序。多个等值列并存时哪个区分度更高哪个就应该靠前。区分度是指字段去重后的取值数量与总行数的比值。比如status只有 2 个可选值而user_id可能一千万行里有几十万个不同值那user_id区分度远高于status。把高区分度列放前面能更快地裁剪索引区间后续列的定位成本更低。具体到WHERE user_id 100 AND status PAID这个查询(user_id, status)和(status, user_id)都能用索引但通常(user_id, status)的实测效果更稳。因为前者先用user_id 100定位到一个很小的集合再在集合内用status过滤后者则要先扫完半个status分区里所有user_id。注意区分度不是越高越好还要结合查询频度一起看。如果一个低区分度字段是最高频查询的必经条件那它可能得放在最前面因为最左前缀要求第一列必须出现。先保住最高频查询再优化次要查询。3.3 把排序和分组也“塞”进索引复合索引的另一大隐藏收益是帮ORDER BY和GROUP BY省掉文件排序。数据库做排序时如果发现数据在索引里已经天然有序就可以直接按序取数不再额外建临时表排序。设计时尽量让索引列顺序和排序字段顺序一致。举个完整例子SELECT user_id, status, create_time FROM user_order WHERE status PAID ORDER BY user_id, create_time;如果只考虑过滤条件(status, user_id, create_time)就能覆盖status PAID但ORDER BY需要的是user_id, create_time排序而现有索引内部是status - user_id - create_time排序锁定位后恰好也是这个顺序所以这里可以天然免排序。如果把create_time放到user_id前面排序就一定得额外处理。这是我一直强调的“一石二鸟”设计复合索引时不要只看 WHERE 条件尽量把 SELECT 的字段、ORDER BY、GROUP BY 使用的列一起考虑进去争取构造出覆盖索引省掉回表。3.4 覆盖索引让查询直接在索引里拿到结果覆盖索引的意思是查询需要的所有列都在索引里数据库查完 B 树拿到数据不需要再回原表读行。判断依据是执行计划里的Extra出现Using index。在复合索引上做覆盖索引特别常见典型场景是统计数据SELECT COUNT(*) FROM user_order WHERE status PAID AND create_time 2024-01-01;如果表上有(status, create_time)索引这个查询可以直接遍历索引得到计数不用回表。我把覆盖索引当作复合索引设计里的“白送收益”——它会显著改善性能尤其在大量行需要被过滤时。4. 一次完整的索引优化与失效排查从 EXPLAIN 开始4.1 EXPLAIN 里必须盯死的六个字段任何索引优化最后都要用执行计划验证。我平时在项目里给人讲优化第一步永远是看EXPLAIN重点盯这几个字段type从ALL到ref/range/eq_ref的提升代表扫描范围大幅缩小。出现ALL说明没走索引。key实际使用的索引名。很多时候possible_keys列出的索引不止一个但最终选用的只有一个以key为准。key_len使用到的索引字节数。这个字段能精确看出索引到底用了多少列。复合索引(a, b, c)如果key_len只覆盖了a b的字节长度说明c没被用上。rows优化器估算需要扫描的行数。这个数字越小越好最怕从百万跌到万级还不自知。Extra出现Using filesort或Using temporary说明排序/分组没有完全走索引出现Using index说明覆盖索引生效。filtered表示过滤比例结合rows一起判断实际回表量。4.2 一个完整优化案例同样一张表先慢后快拿开头提到的场景做个完整演示。表结构大致如下CREATE TABLE user_order ( id BIGINT PRIMARY KEY, user_id BIGINT NOT NULL, status VARCHAR(16) NOT NULL, create_time DATETIME NOT NULL, amount DECIMAL(10,2), KEY idx_user_status_time (user_id, status, create_time) );最开始的慢查询是SELECT id, amount FROM user_order WHERE create_time 2024-01-01 AND create_time 2024-02-01 AND status PAID ORDER BY create_time DESC LIMIT 50;原始表上只建了一个单列索引在create_time执行计划长这样关键字段值typeindexkeyidx_create_timerows约 300 万ExtraUsing where很明显数据库是按整个create_time索引做全序扫描再逐行过滤status最后找到 50 行。一旦时间范围内有大量非 PAID 状态数据性能就会急速劣化。当时我重新整理了高频查询发现这个查询里status和user_id是等值条件create_time是范围条件于是把索引调整为(user_id, status, create_time)ALTER TABLE user_order ADD KEY idx_user_status_time (user_id, status, create_time);修改后执行计划变这样关键字段值typerangekeyidx_user_status_timerows约 2000ExtraUsing index conditionrows直接从 300 万降到 2000查询时间从 12 秒掉到 40 毫秒。这不是玄学而是复合索引把所有等值列放在最前面让索引快速定位到某个用户 某个状态的小区间再在这个小区间内做时间范围扫描。你要知道原来的方案里status过滤是在索引层之外完成的而新的方案让数据库在索引内就完成了大部分过滤。4.3 复合索引失效的经典雷区函数、隐式类型转换、模糊前缀即使复合索引设计正确实际 SQL 也可能把它“废掉”。踩过几种常见坑每个都值得说明白。对索引列做函数或运算。比如WHERE DATE(create_time) 2024-01-01这本质上是对每一行create_time算完函数再和常量比较函数结果无法走 B 树有序定位。正确写法是create_time 2024-01-01 AND create_time 2024-01-02让索引直接在原始列范围内扫描。隐式类型转换。某次排查发现user_id是数字类型但代码里传进了字符串数据库做了类型转换索引就被放弃。最直观的排查方式是看执行计划里key是否为空或者对比字段类型的定义。前导通配符。WHERE name LIKE %abc%没法用普通索引因为 B 树是有序的必须从左匹配才能定位改成abc%才能触发索引范围扫描。范围列后的等值失效。虽然在前面讲过但值得再强调一次复合索引(a, b, c)里如果a是范围后面的b, c都很难参与定位。所以等值列别放在范围列之后。4.4 优化后的验证方式不只跑一次还要看变化趋势索引改完后不要只执行一次 SQL 就说搞定。我的习惯是在相同数据集下分别跑优化前后 SQL记录EXPLAIN的rows、key_len、Extra和实际耗时再做一次并发压测因为单条查询快不代表并发下负载稳定。另外线上变更后我会持续观察几天慢查询日志和索引命中率。如果某个复合索引在命中率极低的情况下仍然被大量写入那它可能是一个“为低概率查询付出的高额成本”后续需要重新评估。5. 复合索引的隐性代价不能只盯着读得快5.1 写放大和存储成本索引不是免费的复合索引最大的缺点是写放大。每往表里插入一行数据除了更新主键索引还要维护所有二级索引复合索引把多列拼在一起索引键长更新也有更大的树结构调整成本。表写入频率越高索引数量多寡带来的差异越明显。存储上更需要精打细算一个三列复合索引可能比两个单列索引占用的空间还要多因为每一行都要存储三列值的冗余副本。对大宽表来说这是真实可感知的磁盘与内存压力。所以在设计阶段我会做一次“索引预算”每个非主键索引都要有明确用途要么支撑了某条高频查询要么帮某条核心查询避免了排序否则宁可先不建。索引越多不代表越快反而让优化器在选择执行计划时更容易“选错”。5.2 冗余索引清理当复合索引已经覆盖单列索引复合索引(a, b)天生可以作为a单列索引使用因为索引的“前缀”a就是一棵按a排序的 B 树。这时候如果表里还单独建了idx_a(a)那它就属于冗余索引白白增加写放大。平时参与过几个项目的索引复盘发现常见的冗余组合包括既有复合索引冗余单列索引结论(a, b)(a)冗余可删(a, b, c)(a, b)冗余可删(a, b, c)(a)冗余可删(a, b, c)(a, c)不冗余但多数时候可以商量(a, c)单独建并不算严格冗余因为复合索引(a, b, c)不能高效支持“跳过 b 只用 ac”的查询。如果(a, c)这条查询不是高频就值得权衡是否值得保留。我的原则是除非它有线上实测支撑否则倾向删掉。5.3 什么时候复合索引不如单列索引复合索引不是万能膏药也有该退场的时候。最典型的情况是表的查询条件非常发散多个过滤字段之间几乎不会同时出现。比如一张配置表既有按status查的请求又有按type查的请求两者各自独立、很少同时出现这时按每个字段建单列索引反而更灵活复合索引反而帮不上忙。另外如果第一列是低区分度字段但它又不是高频查询入口那这个复合索引会一直处于“锁不定、扫很长”的状态。比如(gender, create_time)这种索引如果业务查询里很少只有gender条件性别本身区分度又低索引的收益非常有限还会拖慢写入。这时候我会优先考虑换成(create_time, gender)或者干脆只留create_time索引。6. 复合索引在项目里的落地清单和长期维护6.1 我常用的索引设计核对表在项目交付里我给自己定了一套索引设计流程基本可以替换掉“拍脑袋加索引”的坏习惯收集表上所有重要查询按调用频率排序找出 top 20。对每个查询拆解条件哪些列是等值哪些是范围哪些参与排序/分组。优先为最高频的几个查询设计复合索引遵循“等值前置、范围后置、排序尽量并入”的原则。同一张表有多个索引时检查是否存在前缀重叠删除冗余。用EXPLAIN验证key命中了rows明显下降Extra没有非预期的Using filesort或Using temporary。上线后观察慢查询日志和索引使用统计定期复查。这套流程最重要的一点是“先看查询再建索引”。很多工程问题不是索引太少而是索引太多且没有命中真实查询。6.2 复合索引设计时最容易犯的“贪心”错误我见过最大规模的索引事故就是开发同学试图用一个超级复合索引覆盖表上所有查询。结果字段顺序无法同时满足多个高频查询为了迁就某个低频查询把高区分度字段排到了后面最后 top 查询全部部分失效慢查询数量不减反增。另一个贪心是“把 SELECT 的字段全塞进索引”。覆盖索引是好事但为此把所有字段都放进复合索引会造成索引页膨胀缓存命中率下降。覆盖索引优先覆盖高频查询中返回量大的字段低频字段宁可回表读。还有一点很容易被忽略复合索引列越长key_len越大单页能容纳的索引项越少树越高定位时要多读的层也更多。所以能选短字段就不要用长字段能选定长整型就不要选超长字符串。6.3 定期维护索引集合删比加更考验人线上系统的查询模式会随业务变化三个月前的高频条件三个月后可能完全没人用。我会建议运维侧定期从慢查询日志和索引统计里拉数据找出那些“基本没被命中的索引”确认后尽早删除。删除索引前我会执行一个稳妥流程在从库上先删除对应索引观察一周确认没有慢查询回升后再在主库删除。修改期间不要同时变更应用代码避免变量混杂。这么说吧索引设计和代码重构很像加索引容易删索引需要勇气。但一个长期不用的复合索引就是一颗持续漏血的定时炸弹它不报错但一直在拖慢写入和浪费内存。7. 写在最后一点个人体会做数据库优化这几年我最大的感受是复合索引设计不是“会用几个语句建索引”就够的它本质上是“根据数据的物理组织方式反过来设计查询路径”的过程。每次看到有人把key_len和rows的变化放在一起分析我就知道这个人已经入了门。如果你正在调试一条慢查询我的建议是先别急着加索引。先把 SQL 完整写下来把等值、范围、排序、分组字段全部列在一张纸上再动手设计复合索引。执行计划里那几列字段比任何面试八股都更能说明问题的真相。顺着这个思路往下走你大概率会发现很多慢查询根本不是“索引没建”而是“索引建歪了”。调整顺序、删掉冗余、重新验证往往比多加几个索引更立竿见影。
阅读完成 · 觉得有帮助？
