数据库索引原理与优化：B+树、联合索引、回表与索引失效实战

1. 索引到底在解决什么问题从一条跑不动的查询说起数据库索引这个词,几乎所有做后端的人都听过,但真正能把“什么时候该建、建几列、按什么顺序建”说清楚的人并不多。我自己带过几个模拟项目,最常见的场景是:接口从测试环境的几十毫秒,上线后变成几百毫秒甚至几秒,一查慢查询日志,发现是一条WHERE user_id ? AND status ?的语句在扫全表。加了个索引,响应时间掉到几十毫秒,于是团队里形成一种朴素认知——“慢就加索引”。这个认知在数据量小的时候不会出事,等到表涨到千万级,索引本身开始拖慢写入、占用磁盘、甚至让优化器选错执行计划,问题就回来了。所以这篇内容我想把数据库索引这件事从头捋一遍:它是什么、底层长什么样、建立原则背后的数学依据是什么、哪些坑我在实际项目里踩过。适合刚接触后端开发、正在准备面试、或者手里有一张“不知道该不该加索引”的表的同学看。1.1 没有索引时,数据库到底在干什么要理解索引的价值,先得理解没有索引时数据库的工作方式。InnoDB 把数据组织成一棵按主键排序的 B 树,数据行全部挂在叶子节点上,这棵树的另一个名字叫聚簇索引。当你执行一条没有命中任何索引的查询时,优化器只能选择全表扫描:从这棵树的第一个叶子页开始,沿着叶子节点的双向链表一路往后读,每读到一行就把WHERE条件拿来比对一次,不匹配就丢掉。这个过程的专业叫法是full table scan,执行计划里显示为type: ALL。全表扫描的成本不是线性的“读一行算一次”,而是“读一个页算一次磁盘 IO”。InnoDB 默认页大小 16KB,假设一行数据平均 200 字节,一页大概装 80 行;一张 500 万行的表就是 6 万多个页,也就是接近 1GB 的数据量。如果这些页不在 Buffer Pool 里,全表扫描意味着大量随机或顺序 IO,磁盘再快也扛不住并发。我做过一个粗糙的对照测试:同一张 300 万行的模拟订单表,同一条等值查询,无索引时耗时 1.8 秒左右,加了索引后稳定在 3 毫秒以内,差距接近三个数量级。更隐蔽的问题是,全表扫描的代价会随着数据增长而放大。今天 10 万行扫一遍 20 毫秒,业务觉得能接受;半年后 500 万行,同样的语句可能变成 1 秒,而这个变化是渐进的,监控上不一定有明显拐点,等到用户投诉才被发现。索引的意义就在于把“随数据量线性增长”的查找成本,压成“随数据量对数增长”的树高成本。1.2 索引的本质:用写入成本和空间换读取效率很多人把索引理解成“目录”,这个类比方向对但不够准确。书末的目录是额外印上去的静态内容,而数据库索引是和主表保持实时一致的第二份数据结构。你往主表插一行,所有相关索引都要同步插入;你更新一个被索引的列,对应的索引条目要删旧插新;你删一行,索引里也要清理。这意味着索引不是免费的,它的代价体现在三个地方:空间成本:每个二级索引都是一棵独立的 B 树,叶子节点存储索引列的值加主键值。一张表建 5 个单列索引,索引占用的空间有时能到数据本身的 30% 以上。写入放大:一次INSERT可能触发 1 次聚簇索引插入 N 次二级索引插入,N 是这张表上的索引数量。高并发写入场景下,索引过多会直接体现为 TPS 上不去。优化器负担:索引越多,优化器在生成执行计划时需要评估的组合越多,极端情况下选错索引的概率反而上升。所以“索引建立原则”的第一条,不是“怎么建得快”,而是**“先想清楚这条查询值不值得为它付出写入代价”**。读多写少的表,比如配置表、商品类目表,多建几个索引问题不大;写多读少的表,比如日志表、行为埋点表,索引数量要严格控制,能用联合索引覆盖多个查询就绝不拆成多个单列索引。1.3 三个信号告诉你:这张表该考虑索引了我不太赞成“凭感觉加索引”,实际工作里我更依赖三个可观测的信号来判断。第一个信号是慢查询日志里同一类语句反复出现,比如某条按create_time范围筛选的语句每天出现几百次,那就值得为它建索引。第二个信号是执行计划里rows的估算值远大于实际返回行数,这说明数据库读了很多无效数据,典型的“扫了十万行只返回三条”。第三个信号是filtered字段很低,意味着大量行被读出来之后又被条件过滤掉,这是索引缺失或索引选择性差的直接表现。反过来,也有明确不该急着加索引的情况。比如表本身只有几千行,全表扫描的成本可能比走索引再回表还低,优化器自己也会选择type: ALL;再比如某张表一天只被查询几次,却要承受每秒上千次的写入,那加索引的收益远小于代价。这类判断没有标准答案,但有一个可量化的思路:估算这条查询每天的调用次数乘以单次节省的时间,再和写入性能损失的估值做对比。粗糙,但比拍脑袋强。2. 索引的底层结构:为什么主流选择是 B 树想把索引建好,绕不开底层结构这一关。很多同学背过“B 树矮胖、叶子节点有链表、支持范围查询”,但面试或实际调优时被追问“为什么不用红黑树”“为什么不全部用哈希索引”就答不上来。我个人的理解是:数据结构的选择从来不是因为它“更先进”,而是因为它更贴合磁盘这种存储介质的物理特性。把这一层想通了,后面所有的索引设计原则都是自然推论。2.1 从二叉树到 B 树:一场关于磁盘 IO 次数的优化二叉查找树的查找复杂度是 O(log₂N),听起来很美,但它的每个节点只存一个键值。500 万条数据,树高约 23 层,如果每个节点都是一次磁盘 IO,那就是 23 次随机 IO。机械盘上一次随机 IO 约 10 毫秒,23 次就是 230 毫秒,SSD 好一些,但依然浪费。问题的根源在于每次 IO 只搬运了一个键值,而磁盘一次 IO 的最小单位是页(通常 4KB 或 16KB),搬运能力被严重浪费。B 树的思路是:既然一次 IO 能搬 16KB,那就让一个节点塞满 16KB 的键值和指针,这样树的分叉数(fan-out)就能上千,树高被压到 3 到 4 层。B 树在 B 树基础上又做了两点改造:一是所有数据只存在叶子节点,非叶子节点只存键值和子节点指针,进一步提高了单页能容纳的指针数量;二是叶子节点之间用双向链表串起来,范围查询时可以顺着链表顺序扫描,不需要反复回到根节点。这个设计带来的直接结果是:一次等值查询的 IO 次数等于树高,通常 3 次左右;一次范围查询则是“3 次定位 链表顺序扫描”。顺序扫描的页在磁盘上物理相邻,预读机制能进一步放大吞吐。这就是 B 树统治关系型数据库索引的根本原因——不是理论上最优,而是在“页”这个粒度上最优。2.2 算一算三层 B 树能装多少行这个计算我建议每个人都动手算一遍,算完你对“为什么千万级表加索引还这么轻松”就有直觉了。InnoDB 的页是 16KB,非叶子节点里每条记录包含一个主键值和一个 6 字节的页指针。假设主键是BIGINT,占 8 字节,那么一条索引记录约 14 字节,一页 16384 字节能放大约 1170 条。根节点 1170 个指针指向 1170 个第二层节点,第二层每个节点又是 1170 个指针,第三层就是叶子节点,数量为 1170 × 1170 ≈ 137 万。叶子节点存的是完整数据行。假设一行 1KB,一页能放 16 行,那么三层树的总容量就是 137 万 × 16 ≈2190 万行。也就是说,一张两千万行的表,走主键查询只需要 3 次页访问,前两层几乎必然在 Buffer Pool 里,真正的磁盘 IO 往往只有 1 次。这就是“矮胖树”的威力。这个计算还有两个实用推论。第一,主键越短越好,用BIGINT而不是 36 位 UUID 字符串,因为主键长度直接影响每个非叶子节点能放多少指针,进而影响树高。第二,行越小,单页装的行越多,同样的树高能撑的数据量越大,所以大字段(长文本、JSON)尽量拆表或用溢出页存储,别让它们挤占叶子节点的空间。2.3 哈希索引、全文索引、位图索引的适用边界B 树不是万能的,搞清楚其他索引结构的边界,能帮你避开“用错工具”的坑。下面这张表是我自己整理的一张对照,实际选型时可以直接参考:索引类型结构特点擅长明显短板B 树索引有序、支持范围与排序等值、范围、前缀匹配、ORDER BY前导模糊匹配失效,占用空间较大哈希索引键值直接映射桶位置纯等值查询,命中即 O(1)不支持范围、排序、前缀匹配全文索引倒排索引,词项到文档映射大段文本的关键词检索中文分词需额外配置,维护成本高位图索引每个取值一个位图低基数枚举列的多人组合筛选高并发写入时锁冲突严重需要说明的是,InnoDB不支持用户显式创建哈希索引,但它有一个**自适应哈希索引(AHI)**机制:当某个索引页被频繁以相同模式访问时,InnoDB 会自动在内存里为它建哈希表,把 B 树的 3 次页访问压缩成 1 次。这个特性是自动的,不需要也不建议手动干预,但在高并发点查场景下确实能带来明显收益。另外,内存型存储引擎通常支持显式哈希索引,如果你的场景是纯 KV 点查,选型时可以优先考虑。全文索引这里要多说一句。很多团队想做“标题模糊搜索”,第一反应是LIKE %关键词%,这个写法在任何 B 树索引上都用不了,必然全表扫描。数据量不大时可以靠缓存或搜索引擎解决,数据量大就必须上全文索引或者独立的检索组件。我在一个模拟的内容管理项目里试过用全文索引替代LIKE,同样数据量下查询从 2 秒降到 40 毫秒,但代价是插入时需要分词建索引,写入延迟上升了约 15%。这个取舍需要根据读写比例来定。3. 聚集索引与二级索引:回表这件事必须掰开揉碎如果说 B 树结构是索引的“物理层”,那聚集索引和二级索引的区别就是“逻辑层”最重要的一课。我见过太多人建了索引却依然慢,最后的根因都是“回表次数太多”。这个概念不理解透,后面所有的联合索引、覆盖索引技巧都无从谈起。3.1 聚簇表:数据本身就是索引InnoDB 是索引组织表(index organized table),这句话的含义是:表的数据不是独立存放的,而是直接挂在主键 B 树的叶子节点上。所以主键索引不是“指向数据的指针”,它就是数据本身。这个设计带来两个重要后果。第一,每张 InnoDB 表必须有且只有一个聚簇索引。如果你显式定义了主键,主键就是聚簇索引;如果没有主键,InnoDB 会找第一个非空唯一索引来代替;如果连唯一索引都没有,它会隐式生成一个 6 字节的ROW_ID作为聚簇索引。隐式主键的问题在于你无法利用它做任何有意义的查询,而且ROW_ID是全局共享的,高并发插入时可能产生争用。所以建表时显式指定一个单调递增的主键几乎是最佳实践,AUTO_INCREMENT的BIGINT是稳妥选择。第二,主键的顺序决定了插入的位置。自增主键的插入永远发生在 B 树最右侧,是顺序写,页分裂少;而随机主键(比如 UUID)会导致插入位置随机分布,频繁触发页分裂和页重组,写入性能可能下降数倍,同时索引碎片率上升。我在一个模拟项目里对比过两种情况:同样插入 100 万行,自增主键耗时约 40 秒,随机字符串主键耗时接近 3 分钟,差距非常直观。如果业务确实需要 UUID 做外部标识,常见做法是用自增 ID 做聚簇索引,额外加一个唯一索引存 UUID,把随机性隔离到二级索引上。3.2 二级索引的回表过程与代价估算除了聚簇索引,其他所有索引都叫二级索引(辅助索引)。二级索引的叶子节点存的不是完整数据行,而是索引列的值 主键值。这个设计很关键:它让二级索引的体积远小于主表,一页能装更多条目,但同时意味着——通过二级索引查到主键之后,还要再回到聚簇索引里取一次完整数据行。这个过程就叫回表。回表的代价容易被低估。假设一条查询通过二级索引匹配到 1000 行,那就意味着 1000 次主键查找。虽然这些主键查找大部分能命中 Buffer Pool,但每一次都是一次 B 树下降,1000 次累积起来可能就是几十毫秒。如果匹配行数上升到 10 万行,回表成本可能超过全表扫描,这时候优化器会干脆放弃索引,直接扫全表——这也是为什么有时候“明明有索引却没用上”,不是索引失效,而是优化器算完账觉得不划算。举一个具体的估算例子。某张模拟用户表 200 万行,建了idx_city (city)。执行SELECT * FROM user WHERE city A市,假设 A 市有 20 万用户,占比 10%。走索引的话,需要回表 20 万次;全表扫描需要读约 200 万行对应的页。按每页 80 行算,全表约 2.5 万个页,顺序读;走索引则要读二级索引的页加上 20 万次随机回表。两者的成本在这个量级上已经接近,优化器很可能选择全表扫描。这不是 bug,而是成本模型的正常结果。降低回表次数,是索引优化的核心命题之一。3.3 覆盖索引:把回表直接省掉既然回表是主要成本,最直接的优化思路就是“不回表”。如果一条查询需要的所有列,都能在二级索引里找到,那数据库就不需要再去聚簇索引取数据了,这个技巧叫覆盖索引(covering index)。执行计划里Extra显示Using index,就是命中了覆盖索引。举个最常见的例子。假设有联合索引idx_user_status (user_id, status),下面两条语句的表现完全不同:-- 需要回表:SELECT * 要取索引里没有的列 SELECT * FROM orders WHERE user_id 1001 AND status 1; -- 覆盖索引:只取索引里已有的列 SELECT user_id, status FROM orders WHERE user_id 1001 AND status 1;第二条语句在EXPLAIN里会显示Using index,扫描的页数大幅减少,数据量越大差距越明显。我在一个模拟的对账任务里做过对照:同样扫描 50 万行,不回表的版本 120 毫秒,回表版本 900 毫秒,差了七倍多。实践中怎么用好覆盖索引?我的习惯是先看高频查询的SELECT列表,再把过滤列和返回列一起塞进联合索引。但这里有个陷阱:不要为了覆盖而把所有列都塞进索引,那等于把索引变成了另一张表,空间和写入成本都会爆炸。合理的做法是只针对最热的几条查询做覆盖设计,冷查询保持回表即可。注意:覆盖索引的判定依赖SELECT的具体列。SELECT *几乎永远无法覆盖,除非索引包含了所有列(这在实际中不可接受)。所以线上代码尽量避免SELECT *,至少在高频查询上不要用。4. 联合索引与最左前缀:顺序决定命中率单列索引容易理解,联合索引才是真正考验设计功力的地方。同一个联合索引,列顺序换一下,可能从“完美命中”变成“完全用不上”。这一章我把最左前缀原则、列顺序排布、索引下推三件事讲清楚。4.1 最左前缀原则的真实含义最左前缀原则的准确表述是:联合索引(a, b, c)的排序方式是先按 a 排序,a 相同的再按 b 排序,b 相同的再按 c 排序。索引本身就是一个有序结构,只有当你从最左边开始给出确定的值,数据库才能利用这个有序性做二分定位。由此可以推出一张命中表,我建议直接记住:查询条件能否用到索引用到了哪几列a 1能aa 1 AND b 2能a, ba 1 AND b 2 AND c 3能a, b, ca 1 AND c 3能,但只用到 aab 2不能无b 2 AND c 3不能无a 1 AND b 2 AND c 3能,但 c 只用于过滤a, b最后一行需要特别解释。当b使用了范围条件,索引在b这一层就“断开”了,后面的c无法再用于精确定位,只能在已经定位的范围内逐条比对。在 MySQL 5.6 之前,这意味着要回表后才能判断c;有了**索引条件下推(ICP)**之后,c的判断可以在索引层完成,过滤后再回表,回表次数大幅减少。执行计划里Extra显示Using index condition就代表 ICP 生效了。很多资料把最左前缀讲成“必须从第一列开始用”,这个说法容易误导。更准确的理解是:索引的有序性从最左列开始建立,跳过某一列之后,后续列在索引中的有序性就不再有全局意义。所以(a, b, c)上查a 1 AND c 3,c在a 1的范围内其实是有序的,但因为b不确定,c的有序性无法被用来做跨 b 的定位。理解到这一层,列顺序怎么排就有依据了。4.2 列顺序怎么排:等值在前,范围在后,排序最末联合索引的列顺序,我总结了一条实践规则:等值条件列放最左,范围条件列放中间,排序列放最右。原因分别对应三个能力:等值列用于精确定位,范围列用于缩小扫描区间,排序列用于避免额外排序。先看等值列优先。假设查询是WHERE status 1 AND user_id 1001。索引建成(status, user_id)和(user_id, status)在功能上都能命中两列,但效果不同。user_id的区分度远高于status(status 可能只有几个取值),所以(user_id, status)能把扫描范围压缩得更小。区分度高的列放前面,这是通用原则。再看范围列的位置。如果查询是WHERE user_id 1001 AND create_time 2024-01-01,索引应该建成(user_id, create_time)。这样user_id定位到具体用户,create_time在其内部做范围扫描,一气呵成。如果反过来建成(create_time, user_id),那么create_time的范围扫描会跨越所有用户,user_id只能在索引里逐条过滤,效率差很多。最后是排序列。如果有WHERE user_id 1001 ORDER BY create_time DESC,那么索引(user_id, create_time)不仅能用于过滤,还能直接提供有序结果,省掉一次filesort。执行计划里如果看到Using filesort,就该检查是不是排序列没有和索引顺序对齐。这里有个细节:排序列的方向必须与索引一致。MySQL 8.0 之前不支持降序索引,ORDER BY create_time DESC在联合索引里可能仍然触发 filesort;8.0 之后可以显式建(user_id, create_time DESC),才能真正免排序。4.3 索引下推到底省了多少索引下推(Index Condition Pushdown, ICP)是 MySQL 5.6 引入的优化,值得单独说,因为它经常能把一个“看起来没用”的索引救回来。原理是:在存储引擎层扫描索引时,就把索引列上能判断的条件先过滤掉,只把真正需要回表的行返回给 Server 层。拿前面的例子,索引(a, b, c),查询a 1 AND b 2 AND c 3。没有 ICP 时,存储引擎根据a 1和b 2定位到一批主键,全部回表,Server 层再用c 3过滤。有 ICP 时,存储引擎在索引上直接判断c 3,不满足的根本不回表。假设b 2匹配 10000 行,其中c 3的只有 200 行,那回表次数就从 10000 降到 200,差距是 50 倍。ICP 默认开启,可以用SET optimizer_switch index_condition_pushdownon确认。需要注意的是,它只对二级索引生效(聚簇索引本来就要读整行,没有下推的意义),而且索引列上不能有子查询之类的复杂表达式。实际排查时,如果EXPLAIN的Extra里没有Using index condition,可以先检查条件写法是不是把列包在函数里了——那会导致下推失效。5. 索引建立原则:从区分度开始算,而不是凭感觉前面讲了结构和机制,这一章进入正题:到底怎么决定给哪一列建索引。我把它拆成四个可操作的判断维度:区分度、查询频率、写入代价、组合可能性。这四个维度都想一遍,基本不会建出太离谱的索引。5.1 选择性怎么算,算到什么程度算合格选择性(selectivity)的定义是:某一列的不同值个数(基数)除以总行数,结果越接近 1 说明区分度越高。计算公式很简单:-- 单列选择性 SELECT COUNT(DISTINCT status) / COUNT(*) AS selectivity FROM orders; -- 联合索引的选择性(通常远高于单列) SELECT COUNT(DISTINCT CONCAT(user_id, -, status)) / COUNT(*) AS sel FROM orders;判断标准我自己用的一套经验值如下,不是硬性规定,但足够指导决策:选择性区间判断典型例子0.9 以上非常适合建索引用户 ID、订单号、手机号0.1 ~ 0.9可以考虑,优先放进联合索引城市、类目 ID、状态组合0.01 ~ 0.1单列价值低,建议联合性别年龄段、支付方式0.01 以下单独建索引基本无意义性别、是否删除标记需要强调的是,选择性低不等于完全没用。is_deleted这种列选择性极低(可能 0.001),但它在很多查询里都会出现,这时候正确的做法不是给它建单列索引,而是把它和其他高区分度的列组成联合索引,并且放在后面。反过来,如果某张表 99% 的查询都带is_deleted 0,那这个列放在联合索引的第二位反而能显著缩小扫描范围。还有一个更贴近实际的判断方法:看EXPLAIN的rows和filtered。如果某列作为条件时rows估算值很大而filtered很小,说明这个条件的过滤能力差;如果rows接近实际返回行数,说明过滤能力强。这比单纯算选择性更贴近真实执行。5.2 哪些列值得建,哪些列建了纯属负担我把“值得建”和“不值得建”整理成两张清单,日常评审建表语句时可以直接对着看。值得优先考虑的列:主键:必须建,而且要用短、单调递增的类型。外键关联列:JOIN 时的驱动条件,没索引会导致被驱动表全表扫描。高频等值查询列:用户 ID、订单号、商品 ID 这类。高频范围查询列:时间字段,但通常要和其他列组成联合索引。排序列:和等值条件列组成联合索引,顺带解决排序。不值得单独建的列:取值极少的枚举列:性别、布尔标记,单独建索引几乎必然被优化器忽略。更新极频繁的列:每次更新都要维护索引,写放大明显。大字段:长文本、JSON、二进制,索引体积会失控。已经有前缀索引覆盖的列:比如(a)已存在,再建(a, b)的前缀部分是冗余的。查询里从不作为条件出现的列:建了就是纯成本。这里有个很常见的反面案例:某张模拟的流水表,团队为了“保险”给每个字段都建了单列索引,结果表上挂了十几棵 B 树,写入 TPS 只有同结构无索引表的四分之一,而慢查询日志显示真正用到的索引只有三个。索引不是越多越安全,而是越精准越安全。5.3 前缀索引:长字符串列的省钱方案VARCHAR(255)的 URL、邮箱、订单备注这类列,如果整列建索引,索引体积会非常大。InnoDB 支持前缀索引,只索引前 n 个字符,能显著压缩索引大小。但 n 取多少有讲究,取太短区分度不够,取太长起不到压缩作用。我的做法是写个循环脚本,逐步测试不同前缀长度下的选择性,找到“接近全列选择性”的最小值:SELECT COUNT(DISTINCT LEFT(email, 8)) / COUNT(*) AS sel_8, COUNT(DISTINCT LEFT(email, 12)) / COUNT(*) AS sel_12, COUNT(DISTINCT LEFT(email, 16)) / COUNT(*) AS sel_16, COUNT(DISTINCT email) / COUNT(*) AS sel_full FROM users;假设结果是sel_8 0.82、sel_12 0.96、sel_16 0.99、sel_full 0.995,那选 12 或 16 就足够了,没必要索引全部 255 个字符。建索引的语法是:ALTER TABLE users ADD INDEX idx_email_prefix (email(16));前缀索引有两个限制必须知道。第一,它无法用于覆盖索引,因为索引里只有前缀,拿不到完整值。第二,它无法用于ORDER BY,因为前缀排序和完整值排序可能不一致。所以前缀索引只适合“纯过滤”场景,如果这列还要参与排序或返回,就得考虑其他方案。5.4 冗余索引与重复索引:清理比新建更重要索引治理里,清理往往比新增更能带来收益。冗余索引的判定规则不复杂,但很容易被忽略:(a)和(a, b)同时存在:前者冗余,因为后者能覆盖前者的所有用途。(a, b)和(b, a)同时存在:不冗余,两者服务不同的查询模式。主键列出现在二级索引末尾:不算冗余,因为二级索引本来就要存主键。唯一索引和普通索引在同一列上:普通索引冗余。清理之前务必确认没有查询依赖被删的索引。稳妥的做法是先用performance_schema或者慢查询日志统计各索引的使用次数。MySQL 8.0 提供了一个更直接的办法,查询sys.schema_unused_indexes视图,能列出“从实例启动以来从未被使用过”的索引:SELECT * FROM sys.schema_unused_indexes WHERE object_schema NOT IN (mysql, sys);这个视图的结果只能作为参考,因为统计周期内可能刚好没有触发某些查询(比如月度报表)。我的经验是观察周期至少覆盖一个完整的业务周期,比如一个月,再决定删除。删除索引是低风险操作(可以随时加回来),但删除前最好还是备份一下建索引语句,方便回滚。6. 索引失效的典型场景与排查手法索引失效是索引话题里被问得最多的部分。很多“失效”其实不是索引坏了,而是索引从设计上就用不上。我按出现频率从高到低排了一下,基本覆盖日常遇到的九成情况。6.1 五类最常见的失效写法第一类是列上做运算或函数。WHERE DATE(create_time) 2024-01-01这种写法,B 树没法用create_time的有序性,因为索引里存的是原始值,不是函数结果。正确写法是改成范围:-- 失效 SELECT * FROM orders WHERE DATE(create_time) 2024-01-01; -- 生效 SELECT * FROM orders WHERE create_time 2024-01-01 00:00:00 AND create_time 2024-01-02 00:00:00;同理,WHERE id 1 100、WHERE UPPER(name) ABC都属于这一类。MySQL 8.0 引入了函数索引(也叫表达式索引),可以把UPPER(name)建进索引里,但维护成本更高,能用改写解决就不要用。第二类是隐式类型转换。最常见的是字符串列用数字去查:-- user_code 是 VARCHAR 类型,这里传了数字 SELECT * FROM users WHERE user_code 10086;MySQL 会把字符串列隐式转成数字再比较,等价于CAST(user_code AS SIGNED) 10086,列上套了函数,索引直接失效。反过来,数字列用字符串查通常没问题,因为转换方向是往数字转。记住一条:比较两边的类型必须一致,尤其是字符串列要用引号包起来。第三类是以通配符开头的模糊匹配。LIKE %关键词和LIKE %关键词%都用不了 B 树索引,因为有序性是从左往右的。LIKE 关键词%是可以用索引的,这是最左前缀原则在字符串匹配上的直接体现。如果业务确实需要前导模糊,那就得考虑全文索引或检索组件,不要指望 B 树。第四类是OR连接了无索引的列。WHERE a 1 OR b 2,如果b没有索引,MySQL 很可能直接放弃索引改走全表扫描,因为OR的语义要求两边都能高效定位,一边不行整个计划就退化。解决办法要么给b也建索引(可能触发 index merge),要么用UNION ALL拆成两条语句。第五类是联合索引跳过最左列。这个前面已经讲过,(a, b, c)上只查b和c是彻底用不上的。6.2 读懂 EXPLAIN:四个字段决定一切排查索引问题,EXPLAIN是绕不开的工具。字段很多,但真正需要盯的只有四个:type:访问类型,从好到坏依次是system const eq_ref ref range index ALL。看到index和ALL就要警惕,前者是全索引扫描,后者是全表扫描。key:实际使用的索引名。如果是NULL,说明一个索引都没用上。key_len:使用的索引字节长度,可以反推用到了联合索引的前几列。比如(a INT, b VARCHAR(50))全是utf8mb4,如果key_len只有 5(41 可空),说明只用了a一列。Extra:最有信息量的字段。Using index是覆盖索引,Using index condition是 ICP,Using where表示在 Server 层过滤(可能意味着索引没完全用上),Using filesort和Using temporary是两个性能红灯。一个真实的排查例子。某次接口慢,EXPLAIN出来type: ref、key: idx_user_status、rows: 12000、Extra: Using where。看起来索引用上了,但rows太大。仔细看key_len,发现只用了联合索引的第一列user_id,而status没用上。原因是 SQL 里写的是status ! 3,不等于条件无法用于索引定位,只能过滤。后来把业务逻辑改成枚举出具体状态值,rows从 12000 降到 300,问题解决。6.3 慢查询定位的完整流程我把自己的排查流程固化成了五步,基本能覆盖绝大多数性能问题:开启慢查询日志,设置long_query_time(建议先设 1 秒,再逐步调到 0.1 秒),用mysqldumpslow或pt-query-digest之类的工具做聚合,找出总耗时最高的 SQL。对目标 SQL 执行EXPLAIN,重点看type、rows、Extra。对比索引定义和查询条件,判断是“没建索引”“索引顺序不对”还是“写法导致失效”。做对照实验,在测试库上用FORCE INDEX强制走索引,或者临时加索引,对比执行时间。这一步能验证你的假设是否成立。上线前用EXPLAIN再确认一次,并观察上线后一段时间的慢查询数量变化。一个容易忽略的点:慢查询日志记录的是“执行时间超过阈值的 SQL”,但很多问题的根源是“执行频率极高的快 SQL”。一条 50 毫秒的 SQL 每秒跑 1000 次,总耗时比一条 2 秒的 SQL 每天跑 10 次要严重得多。所以聚合分析时,除了看单次耗时,还要看总耗时占比。7. 上线前后的索引管理:变更、验证与回滚索引设计得再好,落地时也可能出问题。大表加索引锁表、索引加完没效果、加完反而变慢,这些情况我都遇到过。这一章讲讲工程实践层面的东西。7.1 大表加索引:在线 DDL 没有想象中那么安全MySQL 5.6 之后支持 Online DDL,加索引时可以做到不阻塞读写,语法是:ALTER TABLE orders ADD INDEX idx_user_create (user_id, create_time), ALGORITHMINPLACE, LOCKNONE;但“不阻塞”不等于“零影响”。加索引的过程中,数据库需要扫描全表构建新索引,这会消耗大量 IO 和 CPU,同时占用 Buffer Pool,可能把热数据挤出去,导致其他查询变慢。更危险的是MDL(元数据锁)等待:如果加索引前有一个长事务持有表的元数据锁,ALTER语句会一直等待,而它一旦开始等待,后续所有对该表的查询都会被阻塞,形成雪崩。我的做法是:大表变更前先查information_schema.innodb_trx确认没有长事务,设置lock_wait_timeout为一个较小值(比如 5 秒),失败就重试,而不是无限等待。变更时间选在业务低峰,变更后观察一段时间的慢查询和主从延迟。SET SESSION lock_wait_timeout 5;对于特别大的表(上亿行),即使 Online DDL 也可能耗时数小时,这时可以考虑第三方在线变更工具,原理是建影子表 增量同步 原子切换,对主库压力更可控。是否需要走到这一步,取决于表的大小和业务对延迟的容忍度。7.2 索引效果验证:别只看“快了多少”很多人验证索引效果的方式是“加之前跑一次,加之后跑一次”,这不够严谨。更可靠的做法是做对照实验,把变量控制住:用同样的数据量、同样的参数,在测试库上跑,避免线上数据变化干扰结论。用EXPLAIN确认执行计划确实变了,而不是“碰巧变快了”。关注扫描行数的变化,而不只是耗时。扫描行数从 10 万降到 200,比耗时从 800ms 降到 100ms 更能说明索引有效。同时测写入侧的影响。加索引后插入 1 万行,对比耗时变化。如果写入延迟上升超过可接受范围,就要重新评估。我在一个模拟项目里踩过一个坑:给某张表加了个联合索引,读查询确实快了,但那条链路上的批量导入任务从 8 分钟变成 25 分钟,原因是每次批量插入都要维护这个新索引。后来改成导入前删索引、导入后重建,整体时间反而降到了 5 分钟。这个思路在数据仓库的批量加载场景里很常用,值得记住。7.3 常见问题速查表下面这张表是我这些年攒下来的,基本覆盖了日常遇到的高频问题,可以直接对着排查:现象可能原因处理方向有索引但type: ALL索引选择性差,优化器主动放弃检查rows与总行数比例,考虑联合索引key有值但rows很大只用到联合索引第一列看key_len,调整列顺序或补条件Extra: Using filesort排序列不在索引中或方向不一致把排序列加入联合索引并统一方向Extra: Using temporary存在GROUP BY或DISTINCT无法用索引让分组列走索引顺序,或减少分组维度加索引后写入明显变慢索引数量过多导致写放大清理未使用索引,合并冗余索引索引加了但查询没变化条件写法导致索引失效检查函数包裹、隐式转换、前导模糊主从延迟突然增大大表 DDL 或大批量写入拆分批次,错峰执行,监控复制延迟索引占用空间异常大长字符串整列索引改用前缀索引,评估前缀长度同一条 SQL 有时快有时慢执行计划抖动或 Buffer Pool 命中率变化检查统计信息是否过期,必要时ANALYZE TABLE删除数据后查询变慢索引碎片,页填充率下降评估是否需要重建索引,但注意重建的锁影响提示:ANALYZE TABLE会重新采集索引统计信息,有时候执行计划选错索引,跑一次就能修正。但它是采样估算,不是精确统计,对超大表的效果有限。MySQL 8.0 可以调整innodb_stats_persistent_sample_pages提高采样精度,代价是采集耗时增加。最后分享一个我在实际项目里养成的习惯:每次新建索引,都在变更记录里写清楚“这条索引服务哪几条 SQL、预期降低多少扫描行数、验证结果是什么”。听起来有点繁琐,但半年后回头看,你会非常清楚哪些索引是有效的、哪些是当时拍脑袋加的。索引治理这件事,靠的不是一次性的优化,而是持续的记录和清理。还有一个心得:面对一张“慢查询很多”的表,不要一上来就加索引。先花半小时把慢查询按 SQL 模板聚合,看看是不是同一类模式反复出现,大概率你会发现三五个索引就能覆盖八成问题,而不是需要十几个。索引是稀缺资源,用得越克制,效果越好。