我给查询字段建好了索引,一条简单的查询却慢得离谱,EXPLAIN 一看竟然全表扫描根本没走索引,我对着在索引列上用函数和隐式类型转换让索引失效这个坑排查了大半天的复盘

这是一个让我对数据库索引"从迷信到敬畏"的经典坑。它最让人崩溃的地方在于:索引明明就在那儿、查询条件明明就用了那个建了索引的字段,可数据库就是视而不见,固执地一行行去全表扫描。

事情起于一个慢查询告警。我们有一张用户表 users,数据量几百万,phone 字段(类型是 varchar)上早就建好了索引。有一个按手机号查用户的接口,平时飞快,可那天监控突然报它慢得离谱,几百毫秒甚至上秒。我把那条 SQL 拎出来一看,简单得不能再简单:

-- users 表: phone 字段是 varchar(20), 上面有索引 idx_phone
-- 几百万行数据

-- 出问题的查询(我写的):
SELECT * FROM users WHERE phone = 13800138000;
--                              ↑ 注意: 我传的是【数字】, 没加引号!

-- 我的预期: phone 上有索引, 这应该是一次飞快的索引查找(几毫秒)
-- 实际: 慢查询, 几百毫秒~上秒

我第一反应是"怎么可能"——phone 上有索引啊!于是我祭出 EXPLAIN 看执行计划,结果那一行输出让我倒吸一口凉气:

EXPLAIN SELECT * FROM users WHERE phone = 13800138000;

-- 关键输出:
-- +----+-------+------+---------------+------+---------+------+---------+-------------+
-- | id | type  | key  | possible_keys | ...  | rows    | ...  | Extra   |
-- +----+-------+------+---------------+------+---------+------+---------+-------------+
-- |  1 | ALL   | NULL | idx_phone     | ...  | 4582013 | ...  | Using where |
-- +----+-------+------+---------------+------+---------+------+---------+-------------+
--        ↑全表扫描  ↑没用索引!         ↑虽然"可能用"idx_phone  ↑扫了458万行!

-- type = ALL        → 全表扫描(最差)
-- key  = NULL       → 实际没有使用任何索引!
-- possible_keys 里有 idx_phone, 但 key 是 NULL → 有索引却没用上!
-- rows = 4582013    → 扫描了几百万行

我盯着那个 type=ALL、key=NULL,彻底懵了。possible_keys 里明明列着 idx_phone(说明数据库知道有这个索引、本来"可能"用它),可 key 却是 NULL——它最终没有使用这个索引,而是老老实实地把 458 万行全表扫了一遍!索引就在那里,数据库却"故意"不用它,这到底是为什么?

第一件事:看清真相——对索引列做隐式类型转换,等于对每行做函数运算,索引就废了

我反复对比能走索引和不能走索引的查询,终于锁定了元凶——就是那个我没加引号的 13800138000。深究下去,才明白这背后"隐式类型转换"的机关。

索引失效的真相: 隐式类型转换

# phone 字段是 varchar(字符串), 而我查询时传的 13800138000 是【数字】。
# 类型不匹配! MySQL 必须做【类型转换】才能比较。

# 关键: MySQL 的转换规则是——当字符串和数字比较时, 把【字符串转成数字】。
#   所以 WHERE phone = 13800138000  实际变成了:
#       WHERE CAST(phone AS 数字) = 13800138000
#                ↑ 对【每一行的 phone 字段】都做一次 CAST 转换!

# 为什么这样索引就废了?
#   - 索引里存的, 是 phone 字段的【原始字符串值】(如 "13800138000")
#   - 索引是按这些【原始值】排好序、建好树的, 所以能快速查找原始值
#   - 但现在要比较的, 是【CAST(phone)之后的值】——索引里根本没有"转换后的值"!
#   - 数据库无法用"原始值的索引"去查"转换后的值"
#   - → 只能【逐行取出 phone、逐行 CAST、逐行比较】→ 全表扫描!

# 同理, 在索引列上【用函数】也是一样的道理:
#   WHERE DATE(created_at) = '2024-01-01'   -- 对索引列 created_at 用了 DATE()
#   WHERE YEAR(birth) = 1990                -- 用了 YEAR()
#   WHERE SUBSTRING(name,1,3) = 'abc'       -- 用了 SUBSTRING()
#   WHERE amount * 2 > 100                  -- 对索引列做了运算
#   → 索引里存的是"列的原始值", 不是"函数处理后的值", 全部用不上索引!

# 本质: 索引 = 对【列的原始值】排好序的查找结构;
#   一旦你对列【套了函数/做了转换/参与了运算】, 你要找的就不再是"原始值",
#   而是"加工后的值"——而索引里没有"加工后的值", 索引自然就用不上了。

# 核心: 在索引列上用函数、做(隐式)类型转换、参与运算, 都会让索引失效, 退化成全表扫描;
#   因为索引是按列的原始值建的, 加工后的值不在索引里。

真相大白,我又惊又懊恼。原来罪魁祸首是那个没加引号的数字:phone 是 varchar(字符串),我传的 13800138000 是数字,类型不匹配,MySQL 必须做类型转换——而它的规则是"字符串和数字比较时,把字符串转成数字",于是 WHERE phone = 13800138000 实际变成了 WHERE CAST(phone AS 数字) = 13800138000,也就是对每一行的 phone 都做一次 CAST!而这正是索引失效的根本:索引里存的是 phone 的原始字符串值(并按原始值排好序建好树),可现在要比较的是 CAST 之后的值——索引里根本没有"转换后的值",数据库没法用"原始值的索引"去查"转换后的值",只能逐行取出、逐行 CAST、逐行比较,退化成全表扫描。同理,在索引列上用函数(DATE(created_at)、YEAR(birth))、做运算(amount*2)也是一样——索引是按"列的原始值"建的,一旦你对列套了函数/做了转换/参与了运算,你要找的就不再是原始值、而是"加工后的值",索引里没有它,索引自然就废了。

第二件事:正解——别动索引列,把"加工"挪到常量一侧

搞懂了原理,正解的总原则就清晰了:保持索引列"裸露"(原样出现在条件里),把类型转换、函数运算等"加工"统统挪到常量那一侧去做。

-- ====== 正解一: 类型对齐, 给字符串列传字符串(加引号) ======
SELECT * FROM users WHERE phone = '13800138000';   -- ✓ 传字符串, 类型匹配, 走索引!
-- 索引列 phone 原样出现, 不需要 CAST → EXPLAIN 里 type=ref, key=idx_phone ✓

-- ====== 正解二: 日期范围查询代替对列用函数 ======
-- ✗ 错误: 对索引列 created_at 用 DATE()
SELECT * FROM orders WHERE DATE(created_at) = '2024-01-01';
-- ✓ 正确: 改成范围查询, created_at 列裸露
SELECT * FROM orders
WHERE created_at >= '2024-01-01 00:00:00'
  AND created_at <  '2024-01-02 00:00:00';   -- ✓ 走索引!

-- ====== 正解三: 把运算挪到常量侧 ======
-- ✗ WHERE amount * 2 > 100      (对索引列运算)
-- ✓ WHERE amount > 100 / 2      (即 amount > 50, 索引列裸露)
-- ✗ WHERE YEAR(birth) = 1990
-- ✓ WHERE birth >= '1990-01-01' AND birth < '1991-01-01'

-- ====== 正解四: 确实需要"函数后的值"建索引, 用函数索引/生成列 ======
-- MySQL 8.0+ 支持函数索引:
CREATE INDEX idx_phone_num ON users ((CAST(phone AS UNSIGNED)));
-- 或用生成列(generated column)+ 在生成列上建索引
-- → 这样"函数处理后的值"也被索引了, 但通常优先用前几种"不加工列"的办法。

-- ====== 排查工具: 永远用 EXPLAIN 验证是否走索引 ======
EXPLAIN SELECT ...;
-- 看 type(ref/range 好, ALL 差)、key(是否用了你期望的索引)、rows(扫描行数)
-- 慢查询第一步永远是 EXPLAIN, 别猜。

-- 核心: 让索引列保持"裸露"原样出现在条件里, 把类型转换/函数/运算挪到常量侧;
--   类型要对齐(字符串传字符串)、日期用范围代替DATE(); 改完用 EXPLAIN 验证走了索引。

修复的核心,是"让索引列保持裸露,把加工挪到常量一侧"。正解一:类型对齐——给 varchar 的 phone 传字符串('13800138000' 加引号),类型匹配就不需要 CAST,索引列原样出现,EXPLAIN 里 type 变 ref、key 是 idx_phone。正解二:日期范围查询代替函数——把 DATE(created_at)='2024-01-01' 改成 created_at >= '2024-01-01 00:00:00' AND created_at < '2024-01-02 00:00:00',让 created_at 列裸露。正解三:把运算挪到常量侧——amount*2>100 改成 amount>50。正解四:确实需要函数后的值就用函数索引/生成列(MySQL 8.0+),但通常优先"不加工列"。还有最重要的排查工具:永远用 EXPLAIN 验证——看 type(ref/range 好、ALL 差)、key(是否用了期望的索引)、rows(扫描行数),慢查询第一步永远是 EXPLAIN,别猜。归根结底:让索引列裸露原样出现,把类型转换/函数/运算挪到常量侧,类型要对齐,改完用 EXPLAIN 验证。

第三件事:其他常见的"索引失效"场景

排查后我把其他常见的索引失效场景也系统梳理了一遍,它们一样会让你"建了索引却用不上"。

其他常见的索引失效场景

# 1. 索引列上用函数/类型转换/运算(本文)。→ 保持列裸露, 加工挪到常量侧。

# 2. LIKE 以 % 开头: WHERE name LIKE '%abc'  (前缀模糊)
#    → 用不上索引(索引是按前缀排序的)。LIKE 'abc%' 才能走。→ 全文检索/搜索引擎。

# 3. 联合索引不满足"最左前缀": idx(a,b,c), 但 WHERE b=? (没用a)
#    → 用不上。联合索引要从最左列开始用。→ 调整查询或索引列顺序。

# 4. OR 连接的条件有列没索引: WHERE a=1 OR b=2 (b没索引)
#    → 可能整个查询都不走索引。→ 给b也建索引, 或用 UNION。

# 5. 隐式字符集/排序规则不一致: 关联两表的字段字符集不同
#    → join时隐式转换, 索引失效。→ 统一字符集。

# 6. != / <> / NOT IN / NOT EXISTS: 否定条件常用不上索引(选择性差)。

# 7. 数据分布导致优化器放弃索引: 某值占了大半行, 走索引还不如全表扫
#    → 这种"不走"其实是优化器的合理选择, 不是bug。

# 8. WHERE 中 IS NULL: 看情况, 取决于索引和数据(MySQL一般可走)。

# 共同根源(对前几条): 索引是"按列原始值、按特定顺序"组织的查找结构;
#   任何让查询"无法利用这个有序结构"的写法(加工列、破坏前缀、前缀模糊), 都会让索引失效。

# 核心: 索引失效场景多(函数/转换/前导%/非最左前缀/OR无索引列/字符集不一致/否定条件);
#   根源是破坏了"按原始值有序"的可利用性; 遇慢查询先EXPLAIN, 对照这些场景排查。

排查让我把索引失效的其他场景也梳理清了。一、索引列上用函数/转换/运算(本文)。二、LIKE 以 % 开头(前缀模糊用不上索引,'abc%' 才能走)。三、联合索引不满足最左前缀(idx(a,b,c) 但只用 b)。四、OR 连接的条件有列没索引。五、隐式字符集/排序规则不一致(join 时隐式转换)。六、否定条件(!= / NOT IN 选择性差)。七、数据分布导致优化器主动放弃索引(这其实是合理选择)。八、IS NULL(看情况)。它们(前几条)的共同根源是:索引是"按列原始值、按特定顺序"组织的查找结构;任何让查询无法利用这个有序结构的写法(加工列、破坏前缀、前缀模糊),都会让索引失效。核心是:遇慢查询先 EXPLAIN,对照这些场景排查。下面这张图,是这次索引失效全表扫描的成因与解法:

第四件事:EXPLAIN 关键字段速查表

这次踩坑后,我把 EXPLAIN 里最该看的几个字段整理成一张表,排查慢查询时对照看。

这张表把 EXPLAIN 该看什么钉死了。核心是:看 type 判断访问方式(出现 ALL 全表扫描就要警惕)、看 key 确认到底用没用上你期望的索引(NULL 就是没用)、看 rows 估算扫描量、看 Extra 有没有 filesort/temporary 这类额外开销。它给我的最大启发是:面对性能问题,最重要的能力不是"凭经验猜哪里慢",而是"有没有一个能让你看见系统实际在做什么的工具,并看懂它";EXPLAIN 之于 SQL,就是这样一个"透视镜"——它把数据库"打算怎么执行这条查询"这个原本黑盒的决策,清清楚楚地展示给你看。这其实是性能优化的第一性原理:"测量,而非猜测(Measure, don't guess)";无论是 SQL 的 EXPLAIN、前端的 Performance 面板、后端的 profiler、还是系统的各种监控,它们的共同价值,都是把"系统内部实际发生了什么"从黑盒变成可观测;而高效排查性能问题的人,和盲目乱试的人,最大的区别,就是前者先用工具看清事实,再有的放矢地优化,后者凭感觉东改西改。我当初要是一上来就 EXPLAIN,而不是先愣在那"怎么可能",就能省下大半天。先测量、看清事实,再优化——是这个慢查询教给我的最重要的方法论。

第五件事:索引不是越多越好——它的代价

这次也让我重新审视了对索引的认知。索引不是免费的、更不是越多越好。

这张表把"索引的双面性"讲清了。核心是:索引用空间和"写入变慢"为代价,换取"读取变快";它不是免费的,更不是越多越好——每多一个索引,所有的增删改都要多维护一份,过多的索引还会拖慢写入、浪费存储、甚至让优化器选错。它给我的启发,超越了索引本身:几乎所有的"优化",本质都是一种"权衡(trade-off)"——你用一种资源(空间、写入性能、复杂度)的增加,去换取另一种资源(读取性能)的提升;天下没有免费的优化。这让我对"优化"有了更成熟的认识:做优化,不能只盯着"我想提升的那个指标"(查询速度),而要同时问"这个优化的代价是什么?我牺牲了什么?这个代价我承受得起吗?在我的场景下,这笔交易划算吗?";对一个读多写少的表,多建索引很划算;对一个写多读少、或频繁批量写入的表,过多索引可能得不偿失。脱离具体场景(读写比例、数据量、查询模式)谈"该不该建索引、建几个",都是耍流氓。带着"优化即权衡、要算总账"的意识去做每一个优化决策——是这个索引坑,在"怎么用好索引"之上,教给我的"怎么思考优化"的更深一层功课。

第六件事:写查询、遇到慢查询时,我现在的判断习惯

现在每当我写一条查询、或遇到慢查询,我都会按这张图先想清楚:

这张图的精髓,是"慢查询先 EXPLAIN,没走索引就对照常见失效场景排查、改完再 EXPLAIN 验证"。看 type 是不是 ALL、key 是不是 NULL;没走索引就查 WHERE 条件:索引列被套了函数就裸露出来、类型不匹配就对齐类型、前缀模糊就改写、没建索引就评估后建。关键是改完一定再 EXPLAIN 验证真的走了索引。这套习惯,让我面对慢查询时,从"凭感觉瞎改/迷信'我建了索引就该快'"变成了"用 EXPLAIN 看清事实、对照场景精准修复"——核心始终是:索引按原始值有序,别在索引列上加工;慢查询先测量(EXPLAIN)再优化,改完再验证。

我立下的几条规矩

这场"有索引却全表扫描"的事故,换来了我写 SQL 时,刻进骨子里的几条铁律:

1. 慢查询第一步永远是 EXPLAIN。先看清事实,别猜、别瞎改。
2. 别在索引列上用函数/运算/转换。让索引列裸露,加工挪到常量侧。
3. 查询条件类型必须和列对齐。字符串列传字符串(加引号),防隐式转换。
4. 日期查询用范围代替 DATE()。>= 当天 AND < 次日。
5. LIKE 别以 % 开头。前缀模糊用不上索引,需要就上全文检索。
6. 联合索引遵守最左前缀。从最左列开始用。
7. 索引不是越多越好。它有写入和存储代价,优化即权衡、要算总账。

补充:一个用 EXPLAIN 亲眼对比索引走与不走的实操

口说无凭。这次踩坑后,我养成了一个习惯:对拿不准的查询,用 EXPLAIN 把"走索引"和"不走索引"两种写法并排对比,让数据库自己把差距告诉我。下面是我常用的对比实操:

第一步,造一张有索引的表、灌一批数据,然后对"有问题的写法"和"修复后的写法"分别 EXPLAIN。比如对本文的 phone 查询:对 WHERE phone = 13800138000(传数字)EXPLAIN,你会看到 type=ALL、key=NULL、rows 是几百万;而对 WHERE phone = '13800138000'(传字符串)EXPLAIN,你会看到 type=ref、key=idx_phone、rows=1。同一个查询意图、仅仅差了一对引号,EXPLAIN 给出的 rows 从几百万变成 1——这个对比,比任何文字都更能让你记住"类型对齐"的重要。

第二步,对日期查询也如法炮制:WHERE DATE(created_at) = '2024-01-01' vs WHERE created_at >= '2024-01-01' AND created_at < '2024-01-02',你会亲眼看到前者全表扫、后者走 range 索引。这种"同一意图、两种写法、EXPLAIN 并排对比"的实操,是我把"索引失效"这个抽象知识,真正变成肌肉记忆的方法。

这个实操习惯,给我的价值远超 SQL 本身。它的核心,是一种"用可观测的证据驱动决策"的工作方式:我不再凭"我觉得这样写应该会走索引"的主观判断去写 SQL,而是用 EXPLAIN 这个客观工具,去验证我的每一个关于性能的假设。这让我领悟到一个普遍的工程素养:在任何有"可观测工具"的领域(SQL 有 EXPLAIN、代码有 profiler、网络有抓包、前端有 DevTools),都应该养成"先观测、用数据说话"的习惯,而不是依赖直觉和经验主义;因为系统(尤其是数据库优化器这种复杂的、有自己决策逻辑的系统)的实际行为,常常超出我们的直觉;只有亲眼看到它实际怎么做,我们的优化才是对症的,而非碰运气的。更进一步,这种习惯还能主动地、在问题发生前就发现问题:与其等慢查询告警了再去 EXPLAIN 救火,不如在写下关键查询时就顺手 EXPLAIN 一下,把"索引没走"这类问题,扼杀在它上线、在它拖垮生产之前。把"EXPLAIN 验证"从"出事后的排查手段",变成"写查询时的日常习惯"——这是这个慢查询坑,推动我养成的、最有价值的一个工程习惯。让证据,而非直觉,来指导你和数据库的每一次对话。

写在最后

回头看,这场由"一个没加引号的数字"引发的、索引集体失效的事故,真正教给我的,远不止"查询要注意类型、别在索引列上用函数"这些技巧。它让我对"索引"这个我曾经半懂不懂、甚至有点迷信的东西,有了一次脱胎换骨的、回归本质的理解。我之前对索引的认知,停留在一种"魔法"的层面:"给字段建个索引,查询就会变快"——我把它当成一个一劳永逸的"加速咒语",却从没真正理解它为什么能加速、以及在什么条件下才能加速。所以当查询"有索引却不快"时,我才会彻底懵掉。直到我理解了索引的本质——它不过是一个"按列的原始值排好序的查找结构(B+树)",它能加速,仅仅是因为"有序"让查找可以二分、可以跳跃,而非逐行扫描——一切才豁然开朗。这让我领悟到一个深刻的认知:对一个技术,"知道它能做什么(它能加速查询)"和"理解它为什么能、以及它的边界在哪(它靠'原始值有序'加速,所以一旦你破坏了'对原始值的直接比较',它就失效)",是两个层次的掌握;停留在前者,你只能在"它正常工作"时使用它,一旦它"不按预期工作",你就束手无策、只能迷信和瞎试;只有抵达后者,你才能预判它何时会失效、理解它为何失效、并知道如何让它重新生效。这其实是从"使用者"到"掌握者"的分水岭:对你深度依赖的关键技术(索引、缓存、事务、GC……),别满足于"会用它的 happy path",而要花时间去理解它"底层是怎么实现的、它的能力来自哪个原理、这个原理在什么情况下会不成立";因为真正考验你的,从来不是它正常工作的时候,而是它"诡异地不工作"的时候——而那个时候,唯有对原理的理解能救你。透过"会用"抵达"理解其所以然"——这,是我用一次索引失效的事故,换来的、关于数据库、也关于如何真正掌握一门技术的、最朴素也最深刻的领悟。如果这篇复盘,能让你下次写下一个 WHERE 条件时,先想想"这样写,索引那棵有序的树还用得上吗?",那我对着那个 type=ALL 排查的这大半天,就值了。

—— 别看了 · 2026