1. 索引不是加得越多越快从一次查询慢了三十倍说起刚接手一个订单查询模块的时候我遇到过一个挺典型的现象一张两百万行左右的订单表某个按商户号 时间范围 状态组合筛选的列表页响应时间从 80ms 一路涨到 2.4 秒。当时团队里有人提了个很自然的想法——多加点索引总没坏处于是那张表上先后加了七个单列索引结果查询反而更慢了写入延迟也跟着上来了。这件事让我重新把索引这件事从头梳理了一遍也成了我今天想聊的核心数据库索引的建立原则本质上不是哪里慢就在哪里加索引而是一套关于成本、选择性和访问路径的取舍方法。这篇文章面向的是已经会写 SQL、但还没系统想过索引怎么设计的朋友也适合那些被慢查询折腾过、想搞清楚为什么加了索引还是走全表扫描的开发者。我不会只告诉你要在 where 条件列上建索引这种谁都知道的话而是把索引的物理结构、联合索引的列序逻辑、索引失效的真实原因、上线变更的操作细节一条一条拆开讲。看完之后你至少能做到两件事看到一条慢 SQL能判断该不该建索引、建什么样的索引看到一张已有索引的表能判断哪些索引是冗余的、哪些是白建的。先把几个关键词摆在明面上B树、聚簇索引、回表、最左前缀、覆盖索引、索引选择性、执行计划。这几个词是理解索引的地基后面所有原则都是从它们推导出来的而不是背下来的口诀。1.1 索引的成本账读性能是借来的不是白来的很多人对索引的理解停留在加快查询这一层但数据库不会凭空变快。索引在物理上是一份额外的、有序的数据结构它带来三方面成本存储空间、写入放大、优化器决策负担。存储空间好理解一个联合索引在大表上占几个 GB 很常见。写入放大才是真正容易被忽略的部分每次 INSERT除了往聚簇索引里插一条还要往每一个二级索引里插一条并且要维护 B树的有序性——这意味着可能触发页分裂产生随机写。一张表上挂十个索引一次插入就变成十一次索引维护。DELETE 和 UPDATE 同理UPDATE 如果改到了被索引的列等于先删后插。所以那条加了七个索引反而更慢的路子问题不在于索引无效而在于单列索引在这种多条件组合查询里优化器往往只能选一个用或者做 index merge但 index merge 的效果经常不如一个合适的联合索引选哪个都不够精确最终扫出来的行数依然很多同时七个索引让写入路径变长缓冲池里能缓存的数据页变少整体命中率下降。一个判断习惯在动手建索引之前先问自己这张表的读写比是多少。读远大于写的表比如配置表、字典表、历史归档表索引可以大胆一些写密集的表订单流水、日志、消息队列落库表每加一个索引都要给出理由。1.2 选择性决定一个索引值不值得建的第一道门槛**索引选择性selectivity**的定义很简单某一列上不重复值的数量除以总行数。选择性越接近 1说明这一列越独特索引的过滤能力越强接近 0 则说明这列取值高度重复索引几乎筛不掉数据。举个具体的一张一千万行的用户表gender列只有两三个取值选择性大概 0.0000003phone列基本唯一选择性接近 1。在gender上单独建索引查男性用户要返回五百万行优化器大概率直接放弃索引走全表扫描——因为走索引要先扫索引页、再回表五百万次成本比顺序扫全表还高。实操上我一般用这两条 SQL 快速评估-- 单列选择性 SELECT COUNT(DISTINCT phone) / COUNT(*) AS sel_phone FROM t_user; -- 联合列选择性评估组合是否有意义 SELECT COUNT(DISTINCT CONCAT(merchant_id, |, status)) / COUNT(*) AS sel_combo FROM t_order;经验阈值单列选择性低于 0.01 的基本不考虑单独建索引0.01 到 0.1 之间的要看查询模式通常作为联合索引的后续列而不是首列高于 0.1 的才值得作为候选。这里有个反直觉的点选择性低不等于这列永远不该出现在索引里。如果业务上高频查询就是某商户下状态为待处理的订单那么status放在联合索引的第二位是很合理的——因为第一位merchant_id已经把数据缩小到一个很小的范围在这个范围里再按status过滤索引的有序性就能发挥作用。这就是后面要讲的联合索引列序问题。1.3 三类最容易被过度索引的表踩过几次之后我总结出三类特别容易堆索引的表遇到时我会格外克制。第一类是状态机驱动的业务表比如订单表、工单表。这类表上往往有十几个状态字段的查询需求每加一个查询就加一个索引最后表上七八个索引写入延迟肉眼可见地涨。正确的做法是把高频组合收敛成两三个联合索引。第二类是宽表 / 大字段表。如果表里有 TEXT、BLOB 或者很长的 VARCHAR在别的列上建索引时要注意二级索引虽然只存索引列 主键但回表时要把整行读出来如果行很大、跨多个页回表成本会非常高。这类表更适合用覆盖索引避免回表。第三类是日志型 / 流水型追加表。这类表写入量极大、几乎不更新索引设计应该以时间 少量维度为主不要按业务查询的每一个条件都建索引。我见过一张埋点表上挂了十一个索引单条插入耗时 3ms 以上把索引砍到三个之后降到 0.4ms。2. 把 B树拆开看索引原则都是从这棵树上长出来的如果只记结论索引原则永远是死记硬背理解了 B树的物理布局最左前缀回表覆盖索引这些东西会变得非常自然。我建议每个写业务代码的人都花半小时把这棵树的形状在脑子里画出来。2.1 一页 16KB磁盘 IO 才是真正的成本单位InnoDB 的存储单位是页page默认 16KB。B树的每个节点就是一页。数据库从磁盘读数据最小单位就是一页——哪怕你只需要一行它也要把整页 16KB 读进内存缓冲池。所以衡量一条查询快不快核心指标不是扫了多少行而是读了多少页。一棵三层 B树能存多少数据粗算一下。假设主键是 bigint 占 8 字节页指针 6 字节非叶子节点一条索引项约 14 字节一页 16384 字节能放约 1170 条。叶子节点存整行数据假设一行 200 字节一页能放约 80 条。那么层级单页可容纳累计可索引行数根节点第 1 层1170 个指针—第 2 层1170 页1170 × 1170 ≈ 137 万行第 3 层1170 × 1170 页约 16 亿行也就是说一张十几亿行的表用主键查一行只需要 3 次页读取。而全表扫描一张一千万行的表按每页 80 行算要读 12.5 万页。这个数量级的差距就是索引存在的全部意义。这里有个实用推论索引要尽量窄。二级索引的每条记录 索引列 主键值 页指针。索引列越窄比如用 INT 而不是 VARCHAR(64)一页能装的记录就越多树就越矮同样行数下 IO 次数更少。这也是为什么很多团队会把长字符串的编码如订单号额外存一个 bigint 的哈希列来建索引。2.2 聚簇索引与二级索引回表到底贵在哪里InnoDB 的表是索引组织表index organized table数据本身就存在主键的 B树叶子节点上这棵树叫聚簇索引clustered index。所以按主键查是最快的路径一趟走完就拿到整行。其他所有索引都叫二级索引secondary index它的叶子节点存的不是整行数据而是索引列的值 主键值。这意味着通过二级索引找到主键之后还要拿着主键再去聚簇索引里查一次完整行——这个动作叫回表。回表的代价在于它是随机 IO。二级索引扫出来的主键可能是散落在聚簇索引各处的每一条都要单独定位一次页。如果一条查询通过二级索引筛出 10 万行那就是 10 万次回表这时候优化器很可能判断不如直接全表顺序扫于是你看到执行计划里key列是 NULL明明建了索引却没用上。理解这一点之后很多玄学现象就解释得通了为什么LIMIT 10的查询很快去掉LIMIT就慢因为前者只需要回表 10 次。为什么同样是范围查询返回行数少的时候走索引返回行数一多就变成全表扫因为回表次数上去了成本模型翻转。为什么SELECT *比SELECT id, name慢因为前者必须回表后者可能被覆盖索引直接满足。2.3 范围查询为什么在 B树上特别划算B树和二叉搜索树最大的结构差异是数据全部在叶子节点且叶子节点之间用双向链表串起来了。这个设计对范围查询极其友好。执行WHERE create_time BETWEEN 2024-01-01 AND 2024-01-31的时候数据库先在 B树上做一次二分定位找到第一个满足条件的叶子节点然后顺着叶子链表的指针往后顺序扫直到超出范围为止。整个过程是一次定位 一段顺序扫描而不是一万次独立的随机查找。这也解释了一条常被误传的规则。有人说范围查询的列不能建索引这是错的。范围查询的列非常适合建索引只是范围查询之后的列在索引里失去精确过滤能力在老版本或某些场景下。真正要避免的是在范围条件之后还想靠索引精确定位这种期待。3. 联合索引的列序最左前缀不是口诀是物理布局联合索引也叫复合索引是实际项目里最有价值也最容易建错的类型。(a, b, c)这样一个索引本质上不是三个索引合在一起而是一棵按 a 排序、a 相同按 b 排序、a 和 b 都相同按 c 排序的树。所有关于列序的规则都是从这句排序描述里推出来的。3.1 最左前缀法则的物理原因因为树是先按 a 排、再按 b 排、最后按 c 排所以WHERE a 1能用a 在树里是有序的能定位到连续的一段。WHERE a 1 AND b 2能用定位到 a 的那段之后段内 b 也是有序的。WHERE b 2用不了b 的有序性只在 a 相同的前提下成立全局看 b 是乱序的无法二分定位。WHERE a 1 AND c 3部分能用a 能定位c 在段内无序只能把 a1 的记录全扫一遍再逐条判断 c。还有一个细节值得说清楚WHERE a 1 AND b 2 AND c 3这种写法a和b都能用上索引定位但c用不上——因为 b 是范围条件b 相同的前提下 c 才有顺序而 b 是一个区间区间内每个不同的 b 值后面跟着的 c 序列是各自独立的无法合并成一次二分。这就是范围之后的列失效。不过MySQL 5.6 之后引入的**索引下推Index Condition Pushdown, ICP**能部分缓解这个问题虽然 c 不能用来定位但可以在存储引擎层直接用索引里的 c 值做过滤不用把整行读出来再判断。执行计划里Extra列出现Using index condition就是这个机制在工作。它减少的是回表次数不是扫描的索引记录数——这两者要分清。3.2 列序怎么排三条优先级规则联合索引的列顺序我一般按下面这个顺序决策第一优先等值条件优先于范围条件。因为等值条件能定位范围条件只能顺着扫把等值列放前面能让索引的定位能力最大化。第二优先区分度高的列靠前。在都是等值条件的情况下先按区分度高的列排序能让每层索引更快收敛到小范围。比如(user_id, status)通常优于(status, user_id)。第三优先考虑排序和分组。如果查询里有ORDER BY create_time DESC把create_time放在联合索引的合适位置可以让索引本身的有序性直接满足排序需求避免额外的filesort。执行计划里如果出现Using filesort就是排序没有用上索引。举个具体的对照例子。假设订单表高频查询是SELECT id, order_no, amount FROM t_order WHERE merchant_id ? AND status IN (1, 2) AND create_time ? AND create_time ? ORDER BY create_time DESC LIMIT 20;那么idx_merchant_status_time (merchant_id, status, create_time)是比较合适的两个等值/IN 条件在前定位create_time在后既能做范围过滤又能直接支撑排序因为前两列等值时第三列有序。这里有个小陷阱status IN (1,2)严格说不是纯等值。IN 在优化器里可能被展开成多个等值条件的 OR也可能走范围。如果status的取值很少且固定影响不大如果status有很多取值而 IN 里包含很多个索引的效率会下降这种情况下可以考虑把status和create_time的顺序调换用时间先做范围。这类选择不要靠猜用真实数据量的环境跑 EXPLAIN 对比。3.3 一页索引失效清单照着查就行索引失效的原因很多但业务代码里翻来覆去就那几种。我整理了一份自查清单遇到慢查询先往这张表上对写法是否用上索引原因WHERE phone 13800000000phone 是 varchar否隐式类型转换等同于对列做了函数运算WHERE DATE(create_time) 2024-01-01否列上套了函数索引值无法直接比较WHERE order_no LIKE %abc否前导模糊无法二分定位WHERE order_no LIKE abc%是前缀匹配可定位WHERE a 1 OR b 2b 无索引否OR 的任一支路无索引整体退化为全表扫WHERE a 1 OR b 2两列都有索引可能是触发 index merge需看执行计划WHERE a 1 5否表达式运算破坏索引可用性WHERE a 1 AND b LIKE %x%联合索引(a,b)部分a 可用b 不可用WHERE a IS NULL视情况索引会存 NULL可以使用但选择性差WHERE a ! 1通常否不等于在优化器看来过滤能力弱倾向于全表扫关于隐式类型转换值得单独强调一次因为它是最隐蔽的一类。phone列定义为varchar(20)但代码里传了long类型的值MySQL 会把列值转成数字再比较等价于CAST(phone AS SIGNED) 13800000000索引直接作废。这类问题在 ORM 框架里尤其常见因为参数类型往往由调用方决定。我在代码评审时会专门检查实体类字段类型和数据库列类型是否一致这种对不上的情况迟早会出问题。4. 从慢查询到 DDL建立索引的完整决策链路前面讲的是原理这一节讲流程。我的习惯是永远不要凭直觉建索引必须走发现问题 → 定位根因 → 设计方案 → 验证效果 → 上线变更这条链路。跳过任何一步都可能建出一个看起来有用但实际没用的索引。4.1 起点永远是慢查询日志不是猜测索引设计的输入应该是真实的慢 SQL而不是开发时的想象。开启慢查询日志是第一步SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 0.5; SET GLOBAL log_queries_not_using_indexes ON;几个参数说明一下。long_query_time 0.5是我在大多数业务系统里用的经验值——低于 500ms 的查询即使慢一点对用户体验影响有限而高于这个值的往往意味着扫描量异常。log_queries_not_using_indexes会把没走索引的查询也记下来但它会把很多小表查询也写进去日志量可能爆炸建议只在排查期临时开启。拿到慢日志之后用pt-query-digest之类的工具做聚合按总耗时和平均耗时两个维度排序。注意是总耗时优先——一条 2 秒执行一次的查询对系统整体的压力可能远大于一条 10 秒执行一次的报表 SQL。4.2 EXPLAIN 里真正该看的四列拿到具体 SQL 之后跑 EXPLAIN。输出有十几列但真正决定成败的就四个列关注点危险信号type访问类型ALL全表扫、index全索引扫要警惕key实际使用的索引为 NULL 说明没走索引rows预估扫描行数与实际返回行数差几个数量级说明选择性差Extra附加信息Using filesort、Using temporary需要优化type的常见取值按效率从高到低大概是systemconsteq_refrefrangeindexALL。业务查询里ref和range是常态能接受index意味着扫了整个索引树通常说明索引设计有问题ALL就是全表扫。rows这列特别值得结合业务判断。如果rows是 200 万但实际只返回 20 行说明这个条件的过滤能力被严重低估了——可能是统计信息过期也可能是列顺序不对导致索引只能做粗过滤。这种情况可以先跑一次ANALYZE TABLE 表名更新统计信息再看rows有没有变化。4.3 上线索引变更那些出过一次事故才会记住的细节设计好索引之后最后一步是 DDL。这一步在小表上毫无痛感在大表上是事故高发区。第一确认在线 DDL 的能力。MySQL 5.6 之后支持 Online DDL但不同操作的代价差别很大。加二级索引一般可以ALGORITHMINPLACE, LOCKNONE不阻塞读写但如果是修改列类型、加主键这类操作可能退化成COPY期间整表锁住。执行前务必先跑ALTER TABLE t_order ADD INDEX idx_mst (merchant_id, status, create_time), ALGORITHMINPLACE, LOCKNONE;如果数据库不支持指定的算法会直接报错而不是静默降级——这正是我们要的保护。第二避开业务高峰并准备好回滚。加索引虽然不锁表但会消耗大量 IO 和 CPU在高峰期执行可能导致其他查询抖动。我的习惯是提前写好DROP INDEX的回滚语句并设定观察窗口变更后 30 分钟内监控慢查询数量、QPS、写入延迟三个指标。第三注意索引名规范。我见过最难受的场景是接手一张表上面有idx1、idx2、idx_abc三种命名风格混用完全看不出每个索引是给哪个查询服务的。统一的命名规则应该一眼能看出列构成比如idx_{首列}_{次列}_{第三列}并在团队文档里维护一份索引 - 对应查询的映射表。5. 覆盖索引与索引下推把回表成本压到零前面反复提到回表是二级索引的主要成本。既然成本明确优化方向也就明确了要么让索引直接提供查询需要的所有列要么让过滤在索引层就完成。这两个方向分别对应覆盖索引和索引下推。5.1 覆盖索引Using index是最想看到的 Extra当一条查询需要的所有列SELECT 列、WHERE 列、ORDER BY 列都包含在某个索引里时数据库不需要回表直接从索引叶子节点拿到结果。这就是覆盖索引执行计划里Extra会显示Using index。改造起来往往只需要动一下 SELECT 列表。比如-- 需要回表要取 amount SELECT id, order_no, amount FROM t_order WHERE merchant_id ? AND status 1; -- 如果在 (merchant_id, status, order_no, amount) 上有索引则完全覆盖 SELECT id, order_no, amount FROM t_order WHERE merchant_id ? AND status 1;第二条能覆盖是因为id是主键天然存在于每个二级索引的叶子节点里。但覆盖索引有代价它要求索引包含更多列索引本身变宽占空间变大写入成本上升。所以我一般只在高频且返回列少的查询上做覆盖索引比如列表页的主查询——返回id、单号、状态、时间这四五列覆盖掉很值。而详情页要返回几十个字段强行覆盖会让索引宽得离谱不如老老实实回表。还有一个高频场景值得单独说用覆盖索引优化深分页。LIMIT 1000000, 20这种写法数据库要扫 100 万零 20 条记录、丢弃前 100 万条。如果用覆盖索引先只取主键再回表取数据能省掉大量回表操作SELECT o.* FROM t_order o JOIN ( SELECT id FROM t_order WHERE merchant_id ? AND status 1 ORDER BY create_time DESC LIMIT 1000000, 20 ) t ON o.id t.id;子查询里的SELECT id只需要扫索引假设索引是(merchant_id, status, create_time)主键隐含不回表外层再按 20 个主键精确回表。这个改写在我处理过的几个列表接口上把响应时间从秒级降到了百毫秒级。5.2 索引下推让过滤发生在存储引擎层索引下推ICP解决的是另一个问题。还是那个联合索引(a, b, c)查询WHERE a 1 AND b LIKE %x%。没有 ICP 的时候存储引擎按a 1把所有记录的主键返回给 Server 层Server 层逐条回表读出整行再判断b LIKE %x%。如果a 1有两万条就是两万次回表其中大部分是白费的。有 ICP 之后存储引擎在扫索引的时候直接用索引里已有的b值做LIKE判断不满足的记录直接跳过不回表。回表次数可能从两万降到两百。ICP 默认开启可以通过SET optimizer_switch index_condition_pushdownon确认。它的适用条件是查询条件里包含索引中的列但该列因为前面有范围条件等原因无法用于定位。执行计划里看到Using index condition就说明它在工作。需要提醒的是ICP 和覆盖索引是两个不同的机制不要混淆覆盖索引是不回表ICP 是晚回表。前者看Using index后者看Using index condition两者可以同时出现在一个执行计划里。6. 索引的日常治理建完之后才是长期工作很多团队的索引是一次性工程——上线时精心设计之后再也不看。结果是索引随着需求迭代不断堆积最终变成一团没人敢动的历史遗留。我的做法是把索引治理变成一项有节奏的例行工作而不是等出问题再救火。6.1 冗余索引与重复索引的识别重复索引是指列完全相同、只是名字不同的索引纯粹浪费。冗余索引是指某个索引的列是另一个索引的前缀比如已有(a, b, c)再建(a)或(a, b)后者就是冗余的——最左前缀已经覆盖了它们的功能。检测 SQL 大致是这样SELECT TABLE_NAME, INDEX_NAME, GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) AS cols FROM information_schema.STATISTICS WHERE TABLE_SCHEMA your_db GROUP BY TABLE_NAME, INDEX_NAME ORDER BY TABLE_NAME, cols;拿到结果后按cols排序人眼扫一遍前缀关系的一律可以删掉短的那个。注意主键不用算进去因为 InnoDB 的二级索引叶子隐含主键它不构成冗余。不过删索引同样有风险。如果某个冗余索引被某个查询通过FORCE INDEX显式引用删掉会直接报错。删除前我一般先把它标记为不可见MySQL 8.0 支持ALTER TABLE ... ALTER INDEX ... INVISIBLE观察一到两周确认没有查询受影响再真正 DROP。5.7 没有这个特性只能靠灰度加监控。6.2 统计信息过期一个经常被误判为索引失效的问题有时候索引设计没问题写法也没问题但执行计划就是不走索引。查了半天代码最后发现是统计信息不准——优化器基于过期的行数估计做出了错误决策。ANALYZE TABLE t_order;可以手动刷新。默认情况下 InnoDB 会在表数据变化超过一定比例时自动触发但大表上这个触发可能滞后。如果一张表经历过大批量导入或删除我一般会主动跑一次 ANALYZE。想确认统计信息的具体情况可以查SELECT * FROM mysql.innodb_index_stats WHERE table_name t_order AND index_name idx_mst;关注n_diff_pfx01、n_diff_pfx02这些列它们表示索引各前缀的不重复值数量。如果这些数字和你用COUNT(DISTINCT)算出来的真实值差距很大说明统计信息已经不新鲜了。6.3 一份可以照着走的索引复查清单最后把我自己用的复查清单列出来新接手一张表的时候我会逐项过一遍通常能砍掉 20% 到 40% 的冗余索引结构层面主键是否使用了自增或有序的 ID避免随机主键导致页分裂是否存在前缀冗余的索引对是否有超过四个列的联合索引通常说明设计过度考虑拆分或改用覆盖索引是否在区分度低于 0.01 的列上有独立索引。查询层面把慢查询日志 Top 20 的 SQL 逐条 EXPLAIN检查key是否为 NULL、type是否为ALL、rows是否与实际返回差距过大检查是否存在隐式类型转换和列上函数调用。运维层面确认索引总量与表数据量的比例是否合理我见过索引总大小超过数据本身两倍的确认最近一次 ANALYZE 的时间确认是否存在长期未被使用的索引——MySQL 8.0 的sys.schema_unused_indexes视图可以直接查5.7 需要开启performance_schema相关采集后自行统计。还有一点是我踩过坑之后才想明白的索引设计要跟着查询模式走而不是跟着表结构走。同一个业务对象的表在 OLTP 场景下可能只需要两个索引在报表场景下可能需要完全不同的索引组合。把这两类查询放在同一个库上索引设计永远会左右为难读写的互相拖累也无解。这时候该考虑的是读写分离或者数据同步到分析型库而不是继续在单张表上堆索引。我个人在实际操作中的体会是索引这件事最难的不是记住那些规则而是养成先看执行计划再动手的习惯。刚开始我也凭直觉加过索引加完一看key还是 NULL白白增加了一堆写入成本。后来强迫自己每次都先 EXPLAIN、再验证、再上线虽然单次慢一点但基本没再出现过建了索引反而更慢的情况。另外如果你手上有历史包袱很重的表别指望一次清理干净挑慢查询 Top 5 的 SQL 对应的索引先动改完观察两周效果出来了再推下一批。 
长河君 · 2026/10/31 12:26:07 · 阅读 1.2k 数据库索引设计原则：B+树、联合索引与慢查询治理
1. 索引不是加得越多越快从一次查询慢了三十倍说起刚接手一个订单查询模块的时候我遇到过一个挺典型的现象一张两百万行左右的订单表某个按商户号 时间范围 状态组合筛选的列表页响应时间从 80ms 一路涨到 2.4 秒。当时团队里有人提了个很自然的想法——多加点索引总没坏处于是那张表上先后加了七个单列索引结果查询反而更慢了写入延迟也跟着上来了。这件事让我重新把索引这件事从头梳理了一遍也成了我今天想聊的核心数据库索引的建立原则本质上不是哪里慢就在哪里加索引而是一套关于成本、选择性和访问路径的取舍方法。这篇文章面向的是已经会写 SQL、但还没系统想过索引怎么设计的朋友也适合那些被慢查询折腾过、想搞清楚为什么加了索引还是走全表扫描的开发者。我不会只告诉你要在 where 条件列上建索引这种谁都知道的话而是把索引的物理结构、联合索引的列序逻辑、索引失效的真实原因、上线变更的操作细节一条一条拆开讲。看完之后你至少能做到两件事看到一条慢 SQL能判断该不该建索引、建什么样的索引看到一张已有索引的表能判断哪些索引是冗余的、哪些是白建的。先把几个关键词摆在明面上B树、聚簇索引、回表、最左前缀、覆盖索引、索引选择性、执行计划。这几个词是理解索引的地基后面所有原则都是从它们推导出来的而不是背下来的口诀。1.1 索引的成本账读性能是借来的不是白来的很多人对索引的理解停留在加快查询这一层但数据库不会凭空变快。索引在物理上是一份额外的、有序的数据结构它带来三方面成本存储空间、写入放大、优化器决策负担。存储空间好理解一个联合索引在大表上占几个 GB 很常见。写入放大才是真正容易被忽略的部分每次 INSERT除了往聚簇索引里插一条还要往每一个二级索引里插一条并且要维护 B树的有序性——这意味着可能触发页分裂产生随机写。一张表上挂十个索引一次插入就变成十一次索引维护。DELETE 和 UPDATE 同理UPDATE 如果改到了被索引的列等于先删后插。所以那条加了七个索引反而更慢的路子问题不在于索引无效而在于单列索引在这种多条件组合查询里优化器往往只能选一个用或者做 index merge但 index merge 的效果经常不如一个合适的联合索引选哪个都不够精确最终扫出来的行数依然很多同时七个索引让写入路径变长缓冲池里能缓存的数据页变少整体命中率下降。一个判断习惯在动手建索引之前先问自己这张表的读写比是多少。读远大于写的表比如配置表、字典表、历史归档表索引可以大胆一些写密集的表订单流水、日志、消息队列落库表每加一个索引都要给出理由。1.2 选择性决定一个索引值不值得建的第一道门槛**索引选择性selectivity**的定义很简单某一列上不重复值的数量除以总行数。选择性越接近 1说明这一列越独特索引的过滤能力越强接近 0 则说明这列取值高度重复索引几乎筛不掉数据。举个具体的一张一千万行的用户表gender列只有两三个取值选择性大概 0.0000003phone列基本唯一选择性接近 1。在gender上单独建索引查男性用户要返回五百万行优化器大概率直接放弃索引走全表扫描——因为走索引要先扫索引页、再回表五百万次成本比顺序扫全表还高。实操上我一般用这两条 SQL 快速评估-- 单列选择性 SELECT COUNT(DISTINCT phone) / COUNT(*) AS sel_phone FROM t_user; -- 联合列选择性评估组合是否有意义 SELECT COUNT(DISTINCT CONCAT(merchant_id, |, status)) / COUNT(*) AS sel_combo FROM t_order;经验阈值单列选择性低于 0.01 的基本不考虑单独建索引0.01 到 0.1 之间的要看查询模式通常作为联合索引的后续列而不是首列高于 0.1 的才值得作为候选。这里有个反直觉的点选择性低不等于这列永远不该出现在索引里。如果业务上高频查询就是某商户下状态为待处理的订单那么status放在联合索引的第二位是很合理的——因为第一位merchant_id已经把数据缩小到一个很小的范围在这个范围里再按status过滤索引的有序性就能发挥作用。这就是后面要讲的联合索引列序问题。1.3 三类最容易被过度索引的表踩过几次之后我总结出三类特别容易堆索引的表遇到时我会格外克制。第一类是状态机驱动的业务表比如订单表、工单表。这类表上往往有十几个状态字段的查询需求每加一个查询就加一个索引最后表上七八个索引写入延迟肉眼可见地涨。正确的做法是把高频组合收敛成两三个联合索引。第二类是宽表 / 大字段表。如果表里有 TEXT、BLOB 或者很长的 VARCHAR在别的列上建索引时要注意二级索引虽然只存索引列 主键但回表时要把整行读出来如果行很大、跨多个页回表成本会非常高。这类表更适合用覆盖索引避免回表。第三类是日志型 / 流水型追加表。这类表写入量极大、几乎不更新索引设计应该以时间 少量维度为主不要按业务查询的每一个条件都建索引。我见过一张埋点表上挂了十一个索引单条插入耗时 3ms 以上把索引砍到三个之后降到 0.4ms。2. 把 B树拆开看索引原则都是从这棵树上长出来的如果只记结论索引原则永远是死记硬背理解了 B树的物理布局最左前缀回表覆盖索引这些东西会变得非常自然。我建议每个写业务代码的人都花半小时把这棵树的形状在脑子里画出来。2.1 一页 16KB磁盘 IO 才是真正的成本单位InnoDB 的存储单位是页page默认 16KB。B树的每个节点就是一页。数据库从磁盘读数据最小单位就是一页——哪怕你只需要一行它也要把整页 16KB 读进内存缓冲池。所以衡量一条查询快不快核心指标不是扫了多少行而是读了多少页。一棵三层 B树能存多少数据粗算一下。假设主键是 bigint 占 8 字节页指针 6 字节非叶子节点一条索引项约 14 字节一页 16384 字节能放约 1170 条。叶子节点存整行数据假设一行 200 字节一页能放约 80 条。那么层级单页可容纳累计可索引行数根节点第 1 层1170 个指针—第 2 层1170 页1170 × 1170 ≈ 137 万行第 3 层1170 × 1170 页约 16 亿行也就是说一张十几亿行的表用主键查一行只需要 3 次页读取。而全表扫描一张一千万行的表按每页 80 行算要读 12.5 万页。这个数量级的差距就是索引存在的全部意义。这里有个实用推论索引要尽量窄。二级索引的每条记录 索引列 主键值 页指针。索引列越窄比如用 INT 而不是 VARCHAR(64)一页能装的记录就越多树就越矮同样行数下 IO 次数更少。这也是为什么很多团队会把长字符串的编码如订单号额外存一个 bigint 的哈希列来建索引。2.2 聚簇索引与二级索引回表到底贵在哪里InnoDB 的表是索引组织表index organized table数据本身就存在主键的 B树叶子节点上这棵树叫聚簇索引clustered index。所以按主键查是最快的路径一趟走完就拿到整行。其他所有索引都叫二级索引secondary index它的叶子节点存的不是整行数据而是索引列的值 主键值。这意味着通过二级索引找到主键之后还要拿着主键再去聚簇索引里查一次完整行——这个动作叫回表。回表的代价在于它是随机 IO。二级索引扫出来的主键可能是散落在聚簇索引各处的每一条都要单独定位一次页。如果一条查询通过二级索引筛出 10 万行那就是 10 万次回表这时候优化器很可能判断不如直接全表顺序扫于是你看到执行计划里key列是 NULL明明建了索引却没用上。理解这一点之后很多玄学现象就解释得通了为什么LIMIT 10的查询很快去掉LIMIT就慢因为前者只需要回表 10 次。为什么同样是范围查询返回行数少的时候走索引返回行数一多就变成全表扫因为回表次数上去了成本模型翻转。为什么SELECT *比SELECT id, name慢因为前者必须回表后者可能被覆盖索引直接满足。2.3 范围查询为什么在 B树上特别划算B树和二叉搜索树最大的结构差异是数据全部在叶子节点且叶子节点之间用双向链表串起来了。这个设计对范围查询极其友好。执行WHERE create_time BETWEEN 2024-01-01 AND 2024-01-31的时候数据库先在 B树上做一次二分定位找到第一个满足条件的叶子节点然后顺着叶子链表的指针往后顺序扫直到超出范围为止。整个过程是一次定位 一段顺序扫描而不是一万次独立的随机查找。这也解释了一条常被误传的规则。有人说范围查询的列不能建索引这是错的。范围查询的列非常适合建索引只是范围查询之后的列在索引里失去精确过滤能力在老版本或某些场景下。真正要避免的是在范围条件之后还想靠索引精确定位这种期待。3. 联合索引的列序最左前缀不是口诀是物理布局联合索引也叫复合索引是实际项目里最有价值也最容易建错的类型。(a, b, c)这样一个索引本质上不是三个索引合在一起而是一棵按 a 排序、a 相同按 b 排序、a 和 b 都相同按 c 排序的树。所有关于列序的规则都是从这句排序描述里推出来的。3.1 最左前缀法则的物理原因因为树是先按 a 排、再按 b 排、最后按 c 排所以WHERE a 1能用a 在树里是有序的能定位到连续的一段。WHERE a 1 AND b 2能用定位到 a 的那段之后段内 b 也是有序的。WHERE b 2用不了b 的有序性只在 a 相同的前提下成立全局看 b 是乱序的无法二分定位。WHERE a 1 AND c 3部分能用a 能定位c 在段内无序只能把 a1 的记录全扫一遍再逐条判断 c。还有一个细节值得说清楚WHERE a 1 AND b 2 AND c 3这种写法a和b都能用上索引定位但c用不上——因为 b 是范围条件b 相同的前提下 c 才有顺序而 b 是一个区间区间内每个不同的 b 值后面跟着的 c 序列是各自独立的无法合并成一次二分。这就是范围之后的列失效。不过MySQL 5.6 之后引入的**索引下推Index Condition Pushdown, ICP**能部分缓解这个问题虽然 c 不能用来定位但可以在存储引擎层直接用索引里的 c 值做过滤不用把整行读出来再判断。执行计划里Extra列出现Using index condition就是这个机制在工作。它减少的是回表次数不是扫描的索引记录数——这两者要分清。3.2 列序怎么排三条优先级规则联合索引的列顺序我一般按下面这个顺序决策第一优先等值条件优先于范围条件。因为等值条件能定位范围条件只能顺着扫把等值列放前面能让索引的定位能力最大化。第二优先区分度高的列靠前。在都是等值条件的情况下先按区分度高的列排序能让每层索引更快收敛到小范围。比如(user_id, status)通常优于(status, user_id)。第三优先考虑排序和分组。如果查询里有ORDER BY create_time DESC把create_time放在联合索引的合适位置可以让索引本身的有序性直接满足排序需求避免额外的filesort。执行计划里如果出现Using filesort就是排序没有用上索引。举个具体的对照例子。假设订单表高频查询是SELECT id, order_no, amount FROM t_order WHERE merchant_id ? AND status IN (1, 2) AND create_time ? AND create_time ? ORDER BY create_time DESC LIMIT 20;那么idx_merchant_status_time (merchant_id, status, create_time)是比较合适的两个等值/IN 条件在前定位create_time在后既能做范围过滤又能直接支撑排序因为前两列等值时第三列有序。这里有个小陷阱status IN (1,2)严格说不是纯等值。IN 在优化器里可能被展开成多个等值条件的 OR也可能走范围。如果status的取值很少且固定影响不大如果status有很多取值而 IN 里包含很多个索引的效率会下降这种情况下可以考虑把status和create_time的顺序调换用时间先做范围。这类选择不要靠猜用真实数据量的环境跑 EXPLAIN 对比。3.3 一页索引失效清单照着查就行索引失效的原因很多但业务代码里翻来覆去就那几种。我整理了一份自查清单遇到慢查询先往这张表上对写法是否用上索引原因WHERE phone 13800000000phone 是 varchar否隐式类型转换等同于对列做了函数运算WHERE DATE(create_time) 2024-01-01否列上套了函数索引值无法直接比较WHERE order_no LIKE %abc否前导模糊无法二分定位WHERE order_no LIKE abc%是前缀匹配可定位WHERE a 1 OR b 2b 无索引否OR 的任一支路无索引整体退化为全表扫WHERE a 1 OR b 2两列都有索引可能是触发 index merge需看执行计划WHERE a 1 5否表达式运算破坏索引可用性WHERE a 1 AND b LIKE %x%联合索引(a,b)部分a 可用b 不可用WHERE a IS NULL视情况索引会存 NULL可以使用但选择性差WHERE a ! 1通常否不等于在优化器看来过滤能力弱倾向于全表扫关于隐式类型转换值得单独强调一次因为它是最隐蔽的一类。phone列定义为varchar(20)但代码里传了long类型的值MySQL 会把列值转成数字再比较等价于CAST(phone AS SIGNED) 13800000000索引直接作废。这类问题在 ORM 框架里尤其常见因为参数类型往往由调用方决定。我在代码评审时会专门检查实体类字段类型和数据库列类型是否一致这种对不上的情况迟早会出问题。4. 从慢查询到 DDL建立索引的完整决策链路前面讲的是原理这一节讲流程。我的习惯是永远不要凭直觉建索引必须走发现问题 → 定位根因 → 设计方案 → 验证效果 → 上线变更这条链路。跳过任何一步都可能建出一个看起来有用但实际没用的索引。4.1 起点永远是慢查询日志不是猜测索引设计的输入应该是真实的慢 SQL而不是开发时的想象。开启慢查询日志是第一步SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 0.5; SET GLOBAL log_queries_not_using_indexes ON;几个参数说明一下。long_query_time 0.5是我在大多数业务系统里用的经验值——低于 500ms 的查询即使慢一点对用户体验影响有限而高于这个值的往往意味着扫描量异常。log_queries_not_using_indexes会把没走索引的查询也记下来但它会把很多小表查询也写进去日志量可能爆炸建议只在排查期临时开启。拿到慢日志之后用pt-query-digest之类的工具做聚合按总耗时和平均耗时两个维度排序。注意是总耗时优先——一条 2 秒执行一次的查询对系统整体的压力可能远大于一条 10 秒执行一次的报表 SQL。4.2 EXPLAIN 里真正该看的四列拿到具体 SQL 之后跑 EXPLAIN。输出有十几列但真正决定成败的就四个列关注点危险信号type访问类型ALL全表扫、index全索引扫要警惕key实际使用的索引为 NULL 说明没走索引rows预估扫描行数与实际返回行数差几个数量级说明选择性差Extra附加信息Using filesort、Using temporary需要优化type的常见取值按效率从高到低大概是systemconsteq_refrefrangeindexALL。业务查询里ref和range是常态能接受index意味着扫了整个索引树通常说明索引设计有问题ALL就是全表扫。rows这列特别值得结合业务判断。如果rows是 200 万但实际只返回 20 行说明这个条件的过滤能力被严重低估了——可能是统计信息过期也可能是列顺序不对导致索引只能做粗过滤。这种情况可以先跑一次ANALYZE TABLE 表名更新统计信息再看rows有没有变化。4.3 上线索引变更那些出过一次事故才会记住的细节设计好索引之后最后一步是 DDL。这一步在小表上毫无痛感在大表上是事故高发区。第一确认在线 DDL 的能力。MySQL 5.6 之后支持 Online DDL但不同操作的代价差别很大。加二级索引一般可以ALGORITHMINPLACE, LOCKNONE不阻塞读写但如果是修改列类型、加主键这类操作可能退化成COPY期间整表锁住。执行前务必先跑ALTER TABLE t_order ADD INDEX idx_mst (merchant_id, status, create_time), ALGORITHMINPLACE, LOCKNONE;如果数据库不支持指定的算法会直接报错而不是静默降级——这正是我们要的保护。第二避开业务高峰并准备好回滚。加索引虽然不锁表但会消耗大量 IO 和 CPU在高峰期执行可能导致其他查询抖动。我的习惯是提前写好DROP INDEX的回滚语句并设定观察窗口变更后 30 分钟内监控慢查询数量、QPS、写入延迟三个指标。第三注意索引名规范。我见过最难受的场景是接手一张表上面有idx1、idx2、idx_abc三种命名风格混用完全看不出每个索引是给哪个查询服务的。统一的命名规则应该一眼能看出列构成比如idx_{首列}_{次列}_{第三列}并在团队文档里维护一份索引 - 对应查询的映射表。5. 覆盖索引与索引下推把回表成本压到零前面反复提到回表是二级索引的主要成本。既然成本明确优化方向也就明确了要么让索引直接提供查询需要的所有列要么让过滤在索引层就完成。这两个方向分别对应覆盖索引和索引下推。5.1 覆盖索引Using index是最想看到的 Extra当一条查询需要的所有列SELECT 列、WHERE 列、ORDER BY 列都包含在某个索引里时数据库不需要回表直接从索引叶子节点拿到结果。这就是覆盖索引执行计划里Extra会显示Using index。改造起来往往只需要动一下 SELECT 列表。比如-- 需要回表要取 amount SELECT id, order_no, amount FROM t_order WHERE merchant_id ? AND status 1; -- 如果在 (merchant_id, status, order_no, amount) 上有索引则完全覆盖 SELECT id, order_no, amount FROM t_order WHERE merchant_id ? AND status 1;第二条能覆盖是因为id是主键天然存在于每个二级索引的叶子节点里。但覆盖索引有代价它要求索引包含更多列索引本身变宽占空间变大写入成本上升。所以我一般只在高频且返回列少的查询上做覆盖索引比如列表页的主查询——返回id、单号、状态、时间这四五列覆盖掉很值。而详情页要返回几十个字段强行覆盖会让索引宽得离谱不如老老实实回表。还有一个高频场景值得单独说用覆盖索引优化深分页。LIMIT 1000000, 20这种写法数据库要扫 100 万零 20 条记录、丢弃前 100 万条。如果用覆盖索引先只取主键再回表取数据能省掉大量回表操作SELECT o.* FROM t_order o JOIN ( SELECT id FROM t_order WHERE merchant_id ? AND status 1 ORDER BY create_time DESC LIMIT 1000000, 20 ) t ON o.id t.id;子查询里的SELECT id只需要扫索引假设索引是(merchant_id, status, create_time)主键隐含不回表外层再按 20 个主键精确回表。这个改写在我处理过的几个列表接口上把响应时间从秒级降到了百毫秒级。5.2 索引下推让过滤发生在存储引擎层索引下推ICP解决的是另一个问题。还是那个联合索引(a, b, c)查询WHERE a 1 AND b LIKE %x%。没有 ICP 的时候存储引擎按a 1把所有记录的主键返回给 Server 层Server 层逐条回表读出整行再判断b LIKE %x%。如果a 1有两万条就是两万次回表其中大部分是白费的。有 ICP 之后存储引擎在扫索引的时候直接用索引里已有的b值做LIKE判断不满足的记录直接跳过不回表。回表次数可能从两万降到两百。ICP 默认开启可以通过SET optimizer_switch index_condition_pushdownon确认。它的适用条件是查询条件里包含索引中的列但该列因为前面有范围条件等原因无法用于定位。执行计划里看到Using index condition就说明它在工作。需要提醒的是ICP 和覆盖索引是两个不同的机制不要混淆覆盖索引是不回表ICP 是晚回表。前者看Using index后者看Using index condition两者可以同时出现在一个执行计划里。6. 索引的日常治理建完之后才是长期工作很多团队的索引是一次性工程——上线时精心设计之后再也不看。结果是索引随着需求迭代不断堆积最终变成一团没人敢动的历史遗留。我的做法是把索引治理变成一项有节奏的例行工作而不是等出问题再救火。6.1 冗余索引与重复索引的识别重复索引是指列完全相同、只是名字不同的索引纯粹浪费。冗余索引是指某个索引的列是另一个索引的前缀比如已有(a, b, c)再建(a)或(a, b)后者就是冗余的——最左前缀已经覆盖了它们的功能。检测 SQL 大致是这样SELECT TABLE_NAME, INDEX_NAME, GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) AS cols FROM information_schema.STATISTICS WHERE TABLE_SCHEMA your_db GROUP BY TABLE_NAME, INDEX_NAME ORDER BY TABLE_NAME, cols;拿到结果后按cols排序人眼扫一遍前缀关系的一律可以删掉短的那个。注意主键不用算进去因为 InnoDB 的二级索引叶子隐含主键它不构成冗余。不过删索引同样有风险。如果某个冗余索引被某个查询通过FORCE INDEX显式引用删掉会直接报错。删除前我一般先把它标记为不可见MySQL 8.0 支持ALTER TABLE ... ALTER INDEX ... INVISIBLE观察一到两周确认没有查询受影响再真正 DROP。5.7 没有这个特性只能靠灰度加监控。6.2 统计信息过期一个经常被误判为索引失效的问题有时候索引设计没问题写法也没问题但执行计划就是不走索引。查了半天代码最后发现是统计信息不准——优化器基于过期的行数估计做出了错误决策。ANALYZE TABLE t_order;可以手动刷新。默认情况下 InnoDB 会在表数据变化超过一定比例时自动触发但大表上这个触发可能滞后。如果一张表经历过大批量导入或删除我一般会主动跑一次 ANALYZE。想确认统计信息的具体情况可以查SELECT * FROM mysql.innodb_index_stats WHERE table_name t_order AND index_name idx_mst;关注n_diff_pfx01、n_diff_pfx02这些列它们表示索引各前缀的不重复值数量。如果这些数字和你用COUNT(DISTINCT)算出来的真实值差距很大说明统计信息已经不新鲜了。6.3 一份可以照着走的索引复查清单最后把我自己用的复查清单列出来新接手一张表的时候我会逐项过一遍通常能砍掉 20% 到 40% 的冗余索引结构层面主键是否使用了自增或有序的 ID避免随机主键导致页分裂是否存在前缀冗余的索引对是否有超过四个列的联合索引通常说明设计过度考虑拆分或改用覆盖索引是否在区分度低于 0.01 的列上有独立索引。查询层面把慢查询日志 Top 20 的 SQL 逐条 EXPLAIN检查key是否为 NULL、type是否为ALL、rows是否与实际返回差距过大检查是否存在隐式类型转换和列上函数调用。运维层面确认索引总量与表数据量的比例是否合理我见过索引总大小超过数据本身两倍的确认最近一次 ANALYZE 的时间确认是否存在长期未被使用的索引——MySQL 8.0 的sys.schema_unused_indexes视图可以直接查5.7 需要开启performance_schema相关采集后自行统计。还有一点是我踩过坑之后才想明白的索引设计要跟着查询模式走而不是跟着表结构走。同一个业务对象的表在 OLTP 场景下可能只需要两个索引在报表场景下可能需要完全不同的索引组合。把这两类查询放在同一个库上索引设计永远会左右为难读写的互相拖累也无解。这时候该考虑的是读写分离或者数据同步到分析型库而不是继续在单张表上堆索引。我个人在实际操作中的体会是索引这件事最难的不是记住那些规则而是养成先看执行计划再动手的习惯。刚开始我也凭直觉加过索引加完一看key还是 NULL白白增加了一堆写入成本。后来强迫自己每次都先 EXPLAIN、再验证、再上线虽然单次慢一点但基本没再出现过建了索引反而更慢的情况。另外如果你手上有历史包袱很重的表别指望一次清理干净挑慢查询 Top 5 的 SQL 对应的索引先动改完观察两周效果出来了再推下一批。