上周五晚上十点业务群里突然弹出一条告警订单管理页面某个接口的 p99 延迟涨到了 3.2 秒。我登录上去看了一眼SQL 很简单就是按用户和状态查订单可表里几十万行数据居然在做全表扫描。加了一个联合索引之后接口耗时掉到 200 毫秒以内——整个过程不到五分钟。这就是数据库索引设计原则在实际项目里的价值你懂不懂它背后的逻辑直接决定了线上 SQL 是秒开还是慢如蜗牛。这篇文章不打算讲教科书式的长篇理论我尽量按照自己在业务里摸爬滚打的经验把索引到底要不要建、联合索引怎么排字段顺序、哪些场景会让索引失效、主键索引和唯一索引怎么选、以及向量数据库和时序数据库这类特殊场景下的索引思路讲透。适合正在做 MySQL 调优的 DBA、被慢查询折磨的后端开发以及准备系统补齐索引知识的人。1. 先把“要不要索引”这事想清楚1.1 索引的本质与它的“账单”很多人对索引的第一印象是“查询变快”这没错但索引不是免费午餐。它本质上是一棵额外维护的有序数据结构最常见的载体是 B 树你可以把它想象成一本字典的拼音检字表数据本身像正文按拼音排好索引则是另一套目录让你不用翻遍每一页就能定位到目标位置。问题在于这套目录需要有人持续维护。每当你往表里插入一行、删除一行、或者更新一个索引列数据库都要同步调整对应的 B 树节点。这带来的账单至少有三笔写放大原来一次 INSERT 只需要写一条数据现在可能还要更新三四个索引树写性能明显下降。存储成本每个二级索引都会占用独立的磁盘空间索引太多宝贵的内存缓存放不下反而可能让热数据频繁淘汰。优化器负担索引数量越多查询优化器可选择的范围越大偶尔会选错执行计划出现“明明有索引却走全表扫描”的诡异现象。所以在建索引之前我建议你先回答一个问题这个 SQL 是高频查询吗如果某个查询每天只跑一两次每次慢 1 秒其实没什么大碍与其加索引不如想办法缓存但如果它是核心接口每个请求都触达那索引优化就该排上日程。1.2 什么场景才真正值得建索引通常我按下面几条来判断读多写少良好的索引等于“空间换时间”如果一张表写入量极大、查询极少索引价值不大。数据量过了门槛几百行的配置表即使全表扫描也很快建索引就是浪费维护成本。我更愿意对百万行以上的表做系统设计。选择性高区分度好一个列的不同取值越多索引越有价值。比如性别列只有“男/女”单独建索引通常没有意义因为过滤后仍然剩下大量行。查询模式固定如果业务 SQL 总是围绕几个固定的 WHERE、ORDER BY、GROUP BY 组合索引就很划算如果全是动态报表SQL 千奇百怪盲目建索引只会让系统更臃肿。说句实话国内很多团队的习惯是“见慢就加索引”先加着再说最后一张表堆了二十多个索引写入拖垮了磁盘也涨得厉害。我更推荐把索引当成一种策略性投资以实际慢查询为依据而不是预判所有可能。1.3 存储引擎决定了索引长什么样这里必须提一下存储引擎的差异。同样是 MySQLInnoDB 和 MyISAM 的索引结构完全不同这直接影响你的设计决策。InnoDB 是聚簇索引组织表主键索引的叶子节点直接存整行数据二级索引的叶子节点只存索引列和主键值。查询如果走二级索引通常还要拿到主键后“回表”一次才能读取整行数据。MyISAM 则是堆表结构索引叶子节点存的是行指针数据和索引完全分离。对比项InnoDBMyISAM索引类型聚簇索引非聚簇索引主键索引叶子存整行数据存行指针二级索引叶子存索引键 主键值存行指针是否强制必须有主键强烈建议有可以没有主键事务支持支持基本不支持行锁支持锁表正是因为 InnoDB 二级索引要回表我们才特别强调“覆盖索引”让查询列全部包含在索引里这样引擎不用回表性能会有一个质的提升。后面专门讲。关于主键InnoDB 默认按主键聚簇主键如果是个很长的 UUID 字符串插入时 B 树页分裂概率高随机 IO 也会变多所以业务上能用自增主键就用自增主键。但如果你有明确的自然键且不会变比如分布式场景下的雪花 ID用它做主键也没问题关键是尽量避免过长、随机、频繁更新的列。2. 索引设计核心原则从“怎么建”到“按什么规则建”2.1 高基数优先区分度决定索引价值我们先谈一个概念基数Cardinality。它表示某列不重复值的数量。设计索引时我习惯计算“选择性”选择性 COUNT(DISTINCT column) / COUNT(*)选择性越接近 1说明该列的区分度越高索引过滤效果越好。举个例子一张一千万用户的表gender性别只有 2 个值选择性约 0.0000002在这列上建索引过滤后仍然有五百万人和全表扫描没什么区别。mobile手机号基本每行不同选择性接近 1用它做 WHERE 条件时索引能迅速把范围缩小到一行。我个人的经验阈值是选择性低于 0.2 的列除非是联合索引的前缀或用于覆盖查询否则不值得单独建索引像状态位、类型字段这类低基数列更多时候放在联合索引里配合高基数列使用而不是自己“挑大梁”。2.2 最左前缀匹配联合索引到底在排什么“where 条件 a and b应该怎么建索引”是新手最容易纠结的问题也是热词里被反复搜的。要理解答案首先要看懂联合索引的内部结构。MySQL 的联合索引(a, b, c)并不是分别对 a、b、c 建三个索引而是先按 a 排序a 相同时再按 b 排序b 也相同时再按 c 排序。这导致一个重要的规则查询必须从最左边的列开始匹配跳过了前面列后面的索引就用不上。这个规则就是最左前缀匹配。举个例子如果有一个联合索引(user_id, status, create_time)那么WHERE user_id 1 AND status 2能命中索引。WHERE user_id 1也能命中索引因为它满足最左前缀。WHERE status 2无法走索引因为跳过了第一列 user_id。WHERE user_id 1 AND status 0 AND create_time 2024-01-01则部分命中注意范围条件后面的字段顺序有讲究。所以“a and b 怎么建索引”的答案不是简单的“建一个 (a, b) 联合索引”而是要回答等值条件优先放前面范围条件放后面。假如你的 SQL 是SELECT order_id, amount FROM t_order WHERE user_id 123 AND status PAID ORDER BY create_time DESC LIMIT 10;这里 user_id 和 status 都是等值匹配create_time 用于排序。一个比较合理的联合索引是(user_id, status, create_time)。因为前两列把数据范围缩得很小第三列刚好覆盖排序需求避免了 filesort。相反如果把 create_time 放最前面整体利用率就下降很多。实战中还经常看到这样的问题SQL 条件里有多个等值条件到底谁放前面一个简单标准是看基数谁更能帮引擎缩小范围谁放前面如果两个列都有索引需求用实际执行计划的 rows 数来验证而不是靠猜。2.3 用覆盖索引“干掉”回表InnoDB 的二级索引叶子节点只保存索引列和主键值所以当查询请求的列全部包含在索引中时引擎可以直接从索引树返回结果完全不需要回表。这叫做覆盖索引它能把一次 B 树查询 一次随机回表压缩成一次索引查询。举个实际场景权限系统里经常要查“用户ID100 拥有的未过期角色ID”如果表结构是user_role(user_id, role_id, expire_time, status)我们最好像下面这样建CREATE INDEX idx_user_role_status ON user_role(user_id, status, role_id);然后查询SELECT role_id FROM user_role WHERE user_id 100 AND status 1;由于 select 的 role_id、where 的 user_id 和 status 都在索引(user_id, status, role_id)里这个查询不仅能用到联合索引主键顺序还完全避免了回表。在高并发接口里省掉一次随机 IO 的收益非常可观。除了普通查询覆盖索引也常被用来优化深分页。比如LIMIT 1000000, 10直接扫描一页页跳会非常慢可以先利用覆盖索引拿到需要的主键 id再与原表 JOIN 回取完整行SELECT t.* FROM t_order t JOIN (SELECT id FROM t_order ORDER BY create_time LIMIT 1000000, 10) tmp ON t.id tmp.id;这里里层的子查询如果走覆盖索引相当于快速定位到那一小段数据的主键区间再回详情表取数据整体性能明显提升。另一个相关机制是索引下推ICP也就是把 WHERE 里部分条件“下推”到存储引擎层过滤减少回表行数这个在 MySQL 5.6 默认开启不需要人工干预但前提是索引设计得足够合理。2.4 冗余索引与“宁缺毋滥”冗余索引是线上最常见的资源浪费。比如你已经建了(a, b)联合索引又单独建了一个(a)索引后者在多数情况下就是完全冗余的因为(a, b)的最左前缀已经覆盖了a的查询场景。我见过更夸张的情况同一张表开发同事每人从网上抄了一段 create index 脚本(type, status)、(status, type)看着差不多就都加上了最后两个索引几乎一模一样写入速度却被拖慢了一截。如果你不确定某个索引是否有用有个小技巧查看 MySQL 8.0 的sys.schema_redundant_indexes视图或者手动打开performance_schema它会帮你直接列出冗余索引。另外要区分唯一索引和普通索引。唯一索引除了加速查询还承担着约束职责防止列重复。如果业务上已经能保证唯一那么普通索引就够用如果需要数据库挡一道重复数据那就建唯一索引。两张的写性能差异在于唯一索引每次写入都要做冲突检测会比普通索引多一次读操作。同时MySQL 8.0 还提供了隐藏索引INVISIBLE可以先让索引“隐形”观察一段时间确定没有查询依赖它再删除这是治理冗余索引很实用的手段。3. 实操一条慢 SQL 从 0 到 1 建索引3.1 场景背景与建表下面我们做一个完整的实操演示。假设业务表是用户订单表t_order数据量 60 万行。最开始每个字段都建了零散索引但有一个接口一直慢SELECT order_id, amount, receiver FROM t_order WHERE user_id 12345 AND status PAID ORDER BY create_time DESC LIMIT 10;表结构简单还原一下CREATE TABLE t_order ( id BIGINT NOT NULL AUTO_INCREMENT, order_no VARCHAR(32) NOT NULL, user_id BIGINT NOT NULL, status VARCHAR(16) NOT NULL COMMENT PAID/SHIPPED/..., amount DECIMAL(12,2) DEFAULT NULL, receiver VARCHAR(64) DEFAULT NULL, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, pay_time DATETIME DEFAULT NULL, PRIMARY KEY (id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;这里注意一个细节user_id 是 BIGINTstatus 是 VARCHAR。如果 WHERE 里出现user_id 12345这种字符串和数字比较MySQL 会产生隐式类型转换索引可能直接失效后面会专门列出来。3.2 用 EXPLAIN 拆解执行计划我们先在“未加合理索引”的状态下执行 EXPLAINEXPLAIN SELECT order_id, amount, receiver FROM t_order WHERE user_id 12345 AND status PAID ORDER BY create_time DESC LIMIT 10;输出关键部分type: ALL说明全表扫描。rows: 600000优化器估算扫描 60 万行。Extra: Using where; Using filesort过滤和排序都是在执行阶段完成的量级一大就会慢。看明白执行计划之后按上一节的原则user_id 和 status 是等值条件create_time 负责排序我建一个联合索引CREATE INDEX idx_user_status_time ON t_order(user_id, status, create_time);再跑一次 EXPLAINtype: ref走索引查找。rows: 大约几十因为 user_id12345 和 status 过滤后行数很少。Extra: Using index condition没有 filesort排序由索引顺序天然完成非常快。这里要特别说一个容易犯错的地方如果你建的索引是(status, user_id, create_time)MySQL 也会用但 SELECT 中 order_id、amount、receiver 都不在索引里每次都需要回表取整行。当前场景下行数不多性能也能接受但如果结果集很大回表次数多性能就会断崖式下降。这就是“覆盖索引”和“索引顺序”共同作用的结果不能只看有没有用到索引还要看扫描范围和回表数量。3.3 索引失效的高发场景清单排查了这么多年的慢查询我总结出下面这张“索引失效清单”遇到这类问题可以直接对照失效场景示例原因与解决思路对索引列使用函数或运算WHERE DATE(create_time)2024-06-01索引是基于原始列的函数包裹后无法直接匹配改写为范围条件create_time ? AND create_time ?隐式类型转换WHERE order_no 202400001order_no 是 VARCHAR字符串列和数字比较时列会被转成数字索引失效保持字段类型一致违反最左前缀联合索引(user_id, status)但 WHERE 只有status跳过第一列索引无法使用调整索引顺序或改写 SQLLIKE 前缀模糊WHERE name LIKE %张前缀不确定无法走 B 树考虑全文索引或 ESOR 连接非索引列WHERE user_id1 OR amount100优化器可能放弃索引改写为 UNION ALL反向查询WHERE status PAID不等于往往选择性很低即使走索引也接近全表扫描优化器可能直接弃用统计信息过期索引本身没问题但 rows 估算严重偏差执行ANALYZE TABLE刷新统计信息其中隐式类型转换是我在真实项目里见过最多也最隐蔽的问题。比如有个接口传入order_no是数字列定义却写成 VARCHAR表面看WHERE order_no202400001也能正确返回结果但 MySQL 必须把每行的 order_no 转成数字再比较索引列上多了函数转换索引自然用不上。改法是前端参数强制转成字符串或者把列类型改成 BIGINT。OR 条件也是一样WHERE user_id1 OR amount100如果只有 user_id 有索引amount 没索引优化器会认为还不如全表扫一遍。这种情况我会把 OR 改成两个查询用 UNION ALL 合并让两条分支各自发挥索引的价值。3.4 DDL 与 show 命令实操建索引有一些标准动作别在线上直接手敲。我通常的流程是先确认是否已有相关索引SHOW INDEX FROM t_order;看输出里的Non_unique、Column_name、CardinalityCardinality 越大说明索引区分度越好。在测试环境建索引并跑 EXPLAIN确认 rows 明显下降Extra 里不再有 filesort。线上执行 DDL尽量使用在线模式ALTER TABLE t_order ADD INDEX idx_user_status_time (user_id, status, create_time), ALGORITHMINPLACE, LOCKNONE;ALGORITHMINPLACE表示不重建整张表LOCKNONE表示允许 DDL 期间继续读写。这是 MySQL 5.6 的要求比早期版本直接重建表导致长时间锁表要温和得多。如果是亿级大表我更建议用 gh-ost 这类在线变更工具分批次做避免主从延迟和高峰期负载。修改完索引别忘了观察一段时间。如果需要回滚直接 DROP 对应的索引即可ALTER TABLE t_order DROP INDEX idx_user_status_time;所有索引变更都要写进变更记录尽量不要在一天之内对同一张表反复加减索引。我见过有人改了几次之后线上还残留着一堆临时索引导致深夜任务跑批时 IO 飙升。4. 容易被忽略的索引细节主键、唯一、视图与特殊数据库4.1 主键索引与唯一索引别把两件事混为一谈主键索引和唯一索引都是数据库面试高频题但很多人以为“都是唯一且非空”就把它们等同了这在实际设计里会出问题。对比项主键索引唯一索引是否允许 NULL不允许允许但唯一索引里 NULL 可重复视数据库而定一张表可以有几个1 个多个InnoDB 中是否聚簇是决定数据物理存储顺序否单独的二层索引主要用途标识每一行物理数据约束业务字段唯一性是否强制要求InnoDB 强烈建议设计看业务需要实际选型时我通常遵守这么一条原则主键尽量与业务无关保持稳定、简短、趋势递增业务上真正需要“不能重复”的字段比如手机号、订单号、身份证号则用唯一索引约束。一个常见误区是拿手机号作为主键手机号一旦换绑、注销、重新发号业务上允许复用数据模型就会非常别扭。唯一的代价前面提过每次插入都会检查重复。但如果某个唯一性规则是硬需求比如同一用户同一活动不能重复参与那么数据库层的唯一索引仍然是性价比最高的防线比代码里先 SELECT 再 INSERT 的更可靠。4.2 视图加索引Oracle 物化视图的正确姿势有人问“oracle 视图加索引”我立刻想到普通视图和物化视图的差异。普通视图本质是一个虚拟 SQL 查询不存储数据所以不能在普通视图上直接建索引。Oracle 里如果你想对视图查询加速最常用的方案是把视图改成物化视图Materialized View。物化视图会实际存储计算结果相当于一张 DBA 维护的汇总表所以可以在上面的物化视图日志和物化视图本身建索引。比如一个订单日汇总视图CREATE MATERIALIZED VIEW mv_order_day REFRESH COMPLETE ON DEMAND AS SELECT user_id, DATE(create_time) AS day, COUNT(*) AS cnt, SUM(amount) AS total FROM t_order GROUP BY user_id, DATE(create_time);物化视图建成后就可以在其上建索引比如CREATE INDEX idx_mv_day_user ON mv_order_day(day, user_id);报表查询秒查。但要注意刷新成本低频的日汇总适合REFRESH COMPLETE ON DEMAND高频场景要设计增量刷新否则每次全量重建反而拖垮系统。MySQL 对物化视图的原生支持不如 Oracle 那么完善我一般用“汇总表 定时任务”的方式实现核心思路一样把复杂的聚合结果预生成再用索引服务查询。4.3 索引表空间与物理存储细节索引的物理存储也值得关注。MySQL InnoDB 默认是独立表空间每个表的数据和索引都放在磁盘上的.ibd文件里用ibd2sdi之类工具可以查看内部结构。有些团队会单独规划索引表空间让索引文件放到独立磁盘上减少数据文件竞争这在传统 Oracle 数据库里比较常见MySQL 里更多还是通过 SSD 硬件来兜底。删除大量数据后索引文件往往不会自动缩小。比如一张表删除了几百万行过期订单表空间依然很大查询不一定变快但磁盘占用和备份时间会很难看。这时可以执行ALTER TABLE t_order ENGINEInnoDB, ALGORITHMINPLACE;或者对部分引擎使用OPTIMIZE TABLE t_order;这个过程会重建表并整理碎片但要注意锁和 IO 开销建议在维护窗口执行千万别在业务高峰期跑。我当时接手的一套系统里一张表半年没整理碎片磁盘占用比实际数据多了 3 倍整理完之后备份时间直接砍半。4.4 数据库不止关系型全文、向量与时序索引如果你只见过 MySQL 的 B 树索引可能会对其他数据库的索引感到陌生。这里简单说几种常见的。全文索引MySQL 5.7 的全文索引通过倒排索引实现适合LIKE %xxx%无法覆盖的场景。语法是MATCH(col) AGAINST(keyword)但注意中文分词需要 ngram 插件还受停用词影响使用前要充分测试。双向索引与反转键索引Oracle 里有 reverse key index主要解决右增长型主键的 IO 热点问题MySQL 8.0 开始支持降序索引可以显式指定ORDER BY col DESC场景下的索引方向避免文件排序。向量数据库索引这两年 AI 应用普及支持 RAG 检索的向量数据库大量出现常用 HNSW、IVF 等索引算法它们基于图或聚类结构适合做相似度 TOP-K 查询而不是精确匹配和范围扫描。它的“索引”和传统 B 树差得很远不能套用关系型索引原则。时序数据库 TDengine时间序列场景按时间主键排序写入标签索引解决设备/分组过滤SQL 写法上强调时间窗口设计思路和事务型数据库差异也很大。所以如果你想跨数据库工作一定要先搞清楚自己面对的是什么场景再选择合适的索引结构别抓住 B 树不放。5. 索引运维与故障排查从监控到死锁5.1 一个慢查询的故障排查实录有一次同事反馈某个统计报表的接口在每天凌晨跑批时特别慢接口超时严重。我先看数据库进程列表SHOW FULL PROCESSLIST;发现大量 SQL 都是同一类查询走全表扫描。于是把这条 SQL 抓出来 EXPLAIN问题定位到typeALL再看 WHERE 条件里的字段发现其中一列因为带了函数转换索引没法用。改写 SQL 之后查询时间从 30 秒降到 300 毫秒。整个排查过程的经验是不要一上来就盲目加索引先看执行计划。很多时候问题是 SQL 写法导致的而不是索引缺失。如果执行计划正常、索引也在、统计信息也新那再考虑表数据分布、磁盘 IO、连接池配置等外部因素。各种数据库可视化工具比如 dbx 这类数据库管理工具可以帮你直接查看索引信息、导出 DDL但根本判断还是要基于 EXPLAIN。5.2 索引与死锁为什么索引会影响锁范围数据库死锁是另一个高频排查场景。InnoDB 的锁机制和索引关系非常紧密如果 UPDATE 或 DELETE 走的是索引锁定范围通常是一小批记录如果没有索引可用引擎可能锁住大量记录甚至退化为表锁级别的冲突。举个例子BEGIN; UPDATE t_order SET amount amount 1 WHERE status PAID;如果没有(status)相关索引这条 UPDATE 很可能要扫描大量行并锁住它们两个并发事务互相等锁死锁概率急剧上升。我们曾经优化过一条类似 SQL加了一个联合索引后锁范围缩小到一个用户的分区数据死锁立刻消失。排查死锁时可以执行SHOW ENGINE INNODB STATUS;看 LATEST DETECTED DEADLOCK 部分里面会列出两个事务各自持有什么锁、等待什么锁很多死锁都会指向“没有索引导致锁范围过大”这个根源。解决方案一般包括给高频 DML 条件加索引缩小锁定行数。尽量把事务做小避免长事务。多个会话访问同一批数据时保持固定顺序减少交叉等待。5.3 索引健康度检查统计信息与碎片整理索引的健康度不是建完就不用管了。优化器依赖表统计信息估算行数如果统计信息过旧可能会选错索引。我建议把下面几个命令加入周期性巡检-- 查看索引基数 SHOW INDEX FROM t_order; -- 更新统计信息 ANALYZE TABLE t_order; -- 低频整理碎片大表谨慎 OPTIMIZE TABLE t_order;SHOW INDEX里的Cardinality是一个估算值正常情况下应该和表大小匹配如果明显偏低说明索引可能没被使用或者统计信息需要刷新。大表上ANALYZE也可能有短暂元数据锁注意维护窗口执行。至于OPTIMIZE幅度最大的重建操作最好先看磁盘可用空间并预估下来执行时间。5.4 常见索引问题速查表症状可能原因设计原则/解法where A and B 查询很慢缺少联合索引或索引字段顺序不合理等值条件先放范围后放参考最左前缀排序查询很慢索引没覆盖 ORDER BY 字段把排序字段放到联合索引末尾明明建了索引却不生效函数包裹/隐式类型转换/OR/模糊前缀改写 SQL保持类型一致插入变慢索引太多精简冗余索引批量写入或异步写更新一行却锁了一片条件列无索引给 WHERE 条件建索引EXPLAIN rows 严重不准统计信息过旧ANALYZE TABLE磁盘碎片大大量删改维护窗口 OPTIMIZE TABLE6. 一点个人经验最后分享一点经验。索引设计不是一次性工作它会随着业务变化持续迭代。我建议团队维护一张索引台账记录每条索引对应的核心 SQL、创建时间、创建人、命中情况每季度用performance_schema或慢查询日志做一次复盘把长期没被使用的索引清掉。还有一个挺有效的习惯上线前的 SQL Review 清单里必须有 EXPLAIN 这一项。如果一条新 SQL 要上生产但是执行计划显示typeALL直接打回重查别等线上告警再去救火。索引这种东西平时不出问题一出问题就是性能事故早点把风险控制住比事后反复加索引要舒服得多。