做 MySQL 优化的朋友应该都遇到过这种场景明明在某列上建了索引EXPLAIN 一看type 却是 ALLrows 几十万甚至上百万Extra 里只有孤零零的 Using where。再一看 SQL好家伙WHERE 条件里给索引列套了个函数。函数索引在 MySQL 里是个老生常谈又特别容易踩的坑今天从原理、现象到几种破解方案完整过一遍顺便把那些网上没讲透的细节也补上。1. 一次真实的慢查询索引看着在其实已经废了先说一个我实际处理过的案例。客服系统有一张工单表work_order大概 300 多万行表上给create_time建了索引DBA 日常巡检看索引列表还挺齐全。结果某天客服反馈按天查工单量的页面直接超时页面转圈转了十几秒。我当时拿到 SQL 是这样的SELECT COUNT(*), DATE_FORMAT(create_time, %Y-%m-%d) AS day FROM work_order WHERE DATE_FORMAT(create_time, %Y-%m-%d) 2024-11-25 GROUP BY day;这段 SQL 的执行计划完全不出所料-------------------------------------------------------------------------------------------------------------------- | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | -------------------------------------------------------------------------------------------------------------------- | 1 | SIMPLE | work_order | NULL | ALL | idx_create_time| NULL | NULL | NULL | 3387422 | 100.00 | Using where | --------------------------------------------------------------------------------------------------------------------注意看possible_keys显示idx_create_time是可用的但key是 NULL优化器最终没用这个索引直接扫了 338 万行。这就是典型索引看着在其实废了的情况。1.1 现场还原客服系统按天统计卡到怀疑人生这个页面属于低并发但重计算的统计页每次点开都扫全表刚开始数据量小还没事等工单表积累到几百万行问题就彻底爆发了。我翻 Slow Query Log这条 SQL 平均执行时间在 9 到 14 秒之间浮动高峰期能飙到 20 秒以上。排查的时候先确认了一件事不是索引没建成功。单独执行SHOW INDEX FROM work_orderidx_create_time老老实实挂在create_time上。那问题就出在 SQL 写法本身。DATE_FORMAT(create_time, %Y-%m-%d) 2024-11-25这个条件等于对create_time列先做了一次格式化再跟字符串比较。索引里存的是原始的create_time值也就是一串 DATETIME 类型的排序键MySQL 不可能知道DATE_FORMAT(create_time, %Y-%m-%d)的结果在索引里哪个位置。它只有一个选择把每一行取出来对create_time做格式化再比对结果。1.2 EXPLAIN 里的关键线索为什么没走 idx_create_time很多人看到possible_keys有索引就以为索引没生效是优化器的锅。实际上优化器算过一笔账就算走idx_create_time也只能拿到所有键然后逐个算函数压根做不到快速定位回表次数还可能是全表数据量。与其这样不如直接扫全表至少顺序读在机械盘和 SSD 上都有不错的吞吐。所以执行计划里rows显示 338 万type显示ALL就是优化器在告诉你这个查询真的没法用索引。这里也要纠正一个网上常说的误区函数一出现索引就失效不完全准确更严谨的说法是对列本身做函数计算会让 B 树的键值查找能力失效。如果函数作用在参数上比如WHERE create_time DATE_SUB(NOW(), INTERVAL 1 DAY)索引照样活得好好的。这个区别后面专门展开。2. 索引为啥怕函数B 树与索引条目的底层逻辑想彻底搞懂函数索引这个坑不能只停留在会失效这个结论上得去 B 树里看看索引到底存了什么。2.1 B 树索引的字典页是怎么存的InnoDB 的聚簇索引大家都熟叶子节点直接存整行数据。二级索引的叶子节点存的是索引列的值 主键值。比如idx_create_time它的每个索引条目就是(create_time, id)这样一对值按照create_time从小到大排序然后组织成一棵 B 树。我把 B 树理解成一本人名电话簿。普通索引相当于按姓名拼音排好序的字典页你要查张三翻到 Z 开头的区域直接定位。你要查一个函数的结果相当于要求电话簿按姓名字数的奇偶性重新排一版现有字典页当然做不到只能从头到尾把每个人名字数一遍。这也就是为什么 MySQL 遇到WHERE DATE_FORMAT(create_time, %Y-%m-%d) 2024-11-25时无法利用 B 树的二分查找能力。B 树只能处理键本身的等值或范围比较一旦表达式出现在列这一侧键的原始语义就变了。2.2 函数把字典撕了为什么优化器宁可扫全表有人可能会问优化器笨吗为什么不走索引逐条判断其实优化器算过。走idx_create_time有两个致命问题第一索引中保存的键是原始时间不是格式化后的日期字符串所以无法做任何范围裁剪只能Index Full Scan也就是把整棵索引树从根到叶子扫一遍第二每扫到一个叶子节点还要回聚簇索引拿整行再对create_time做DATE_FORMAT计算。这个成本通常比直接全表顺序扫描还高。这里的核心矛盾是索引的有序性建立在原始值的可比较性上函数一旦介入有序性就失效了。2.3 反直觉参数上的函数不背锅这个点很多文章一笔带过但我发现不少读者真正困惑的是这里。同样是函数为什么WHERE create_time DATE_SUB(NOW(), INTERVAL 1 DAY)就能走索引因为DATE_SUB(NOW(), INTERVAL 1 DAY)的结果是一个具体的 DATETIME 值它不依赖当前行的任何数据MySQL 在优化阶段就能把它算出来得到一个常数。然后这个查询就变成了WHERE create_time 2024-11-24 15:30:00这是典型的范围查询B 树可以从索引中直接定位。所以判断标准非常朴素函数操作的是列本身还是操作的是传入参数前者毁索引后者不背锅。3. 高频翻车场景盘点不止 DATE_FORMAT 这一种日常开发中毁索引的函数远不止DATE_FORMAT一种。这里把高频场景按类别盘一遍每个都给错误写法和正确改法。3.1 日期函数的全套坑YEAR、MONTH、DATE_FORMAT日期类函数是重灾区尤其是统计报表需求。错误写法-- 查 2024 年全年订单 SELECT * FROM orders WHERE YEAR(create_time) 2024; -- 查某月订单 SELECT * FROM orders WHERE MONTH(create_time) 11; -- 查某天订单 SELECT * FROM orders WHERE DATE_FORMAT(create_time, %Y-%m-%d) 2024-11-25;正确写法-- 查 2024 年全年订单半开区间 SELECT * FROM orders WHERE create_time 2024-01-01 00:00:00 AND create_time 2025-01-01 00:00:00; -- 查 2024 年 11 月订单 SELECT * FROM orders WHERE create_time 2024-11-01 00:00:00 AND create_time 2024-12-01 00:00:00; -- 查 2024 年 11 月 25 日订单 SELECT * FROM orders WHERE create_time 2024-11-25 00:00:00 AND create_time 2024-11-26 00:00:00;这里有一个细节用AND create_time 2024-11-26 00:00:00查某天时如果create_time列本身带时分秒这个写法刚好覆盖到23:59:59之后的所有数据不会漏掉2024-11-25 23:59:59.999这样的记录。我见过有人图省事直接写 2024-11-25 23:59:59如果表里存在微秒级时间23:59:59.5这条记录就被漏了。3.2 字符串函数坑LIKE 模糊匹配与 LOWER字符串列的索引失效最常见的是LOWER、UPPER、CONCAT、LEFT这类函数以及LIKE %xx这种前缀不确定的模糊查询。错误写法-- 大小写不敏感查用户 SELECT * FROM users WHERE LOWER(user_name) tom; -- 按手机号后四位查 SELECT * FROM users WHERE RIGHT(phone, 4) 5678; -- 查名字包含 son 的用户 SELECT * FROM users WHERE user_name LIKE %son%;正确写法-- 如果业务允许直接按原始值查配合 utf8mb4 的排序规则 SELECT * FROM users WHERE user_name tom; -- 手机号后四位加冗余列建普通索引 ALTER TABLE users ADD COLUMN phone_last4 CHAR(4) GENERATED ALWAYS AS (RIGHT(phone, 4)) STORED; CREATE INDEX idx_phone_last4 ON users(phone_last4); SELECT * FROM users WHERE phone_last4 5678; -- 模糊查询尽量改成前缀匹配 SELECT * FROM users WHERE user_name LIKE son%;手机号后四位这个场景网上讨论得很多。有人问能不能用函数索引MySQL 8.0 可以但 5.7 就得靠冗余列。冗余列有两种实现方式后面第 5 节专门说。3.3 数值运算坑WHERE price * 0.9 100数值列上做四则运算同样会让索引失效。这个坑多见于优惠计算、单位换算错误写法-- 找出折扣后价格大于 100 的商品 SELECT * FROM products WHERE price * 0.9 100; -- 按金额区间筛选但列是分单位存储的 SELECT * FROM accounts WHERE amount / 100 5000;正确写法-- 等值变形把计算移到参数侧 SELECT * FROM products WHERE price 100 / 0.9; -- 同样把除法转移到常量侧 SELECT * FROM accounts WHERE amount 5000 * 100;这里要注意price * 0.9 100等价于price 100 / 0.9数学上没问题但浮点数计算可能带来精度问题。商品价格通常走定点数 DECIMAL100 / 0.9会得到一个无限循环小数MySQL 在 DECIMAL 下能处理但如果你谨慎一点可以直接把临界值算好在应用层传进来。这个场景我倾向于在代码里算好值SQL 里只写裸列名。3.4 隐式转换最常见的看不见的函数隐式转换是坑中之坑因为它不带任何函数字样但背后确实会发生类型转换。最常见的是VARCHAR列跟数字比较-- phone 列是 VARCHAR右边是数字 SELECT * FROM users WHERE phone 13800138000;MySQL 看到字符串列跟数字比较会把字符串转成数字来比。这个过程相当于对phone列调用了CAST(phone AS SIGNED)。一旦发生隐式 CAST该列上的索引就废了。正确写法SELECT * FROM users WHERE phone 13800138000;把数字写成字符串字面量等号两侧类型一致隐式转换就不会发生。还有一种隐式转换是DATETIME列跟日期字符串比较-- create_time 是 DATETIME右边是字符串 2024-11-25 SELECT * FROM orders WHERE create_time 2024-11-25;这种情况 MySQL 会把字符串转成 DATETIME 再比较因为是参数侧转换索引不会失效。但要注意语义这条 SQL 只匹配到2024-11-25 00:00:00这一瞬间的数据如果表里存在2024-11-25 09:30:00的单子它不会被查出来。所以这里语法上没问题业务上反而是个坑应该用范围查询。3.5 联表查询里的函数暗坑排序规则不一致这个坑一般出现在两个表 JOIN 的时候字段类型一样但字符集排序规则不同比如一张表是utf8mb4_general_ci另一张是utf8mb4_unicode_ci。MySQL 为了比较会在其中一列上加隐式转换函数经常是CONVERT结果就是那列索引失效。SELECT * FROM a JOIN b ON a.user_name b.user_name;如果a.user_name和b.user_name的排序规则不一致执行计划里可能看到Using join buffer (Block Nested Loop)或者明明两边都有索引却走不了。排查方法直接在 EXPLAIN 里看Extra字段或者用SHOW FULL COLUMNS FROM a看两边列的编码和排序规则。解决办法是做 DDL 统一排序规则ALTER TABLE b MODIFY COLUMN user_name VARCHAR(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;最好在表设计阶段就统一规则上线后再改成本高还容易锁表。4. 破解手段一把条件改写成范围让优化器重新认出索引第 3 节的正确写法本质上都是在做同一件事把对列做运算改成对参数做运算。这个方法成本最低不需要动表结构我遇到慢查询时第一反应都是先尝试改写。4.1 日期类的标准改写 与 半开区间日期类改写我已经写成肌肉记忆了见到YEAR(x) 2024就立刻翻译成x 2024-01-01 AND x 2025-01-01。这个写法既安全又高效索引能派上用场而且不会漏掉边界时间。写一个通用的日期区间生成逻辑某天 [2024-11-25 00:00:00, 2024-11-26 00:00:00) 某月 [2024-11-01 00:00:00, 2024-12-01 00:00:00) 某年 [2024-01-01 00:00:00, 2025-01-01 00:00:00) 最近 N 天 [NOW() - INTERVAL N DAY, NOW())用半开区间[start, end)而不是闭区间 end是为了避免 DATETIME 精度问题。MySQL 8.0 的 DATETIME 支持微秒6 位小数如果用 2024-11-25 23:59:59恰好落在23:59:59.500的记录就会漏掉。4.2 手机号后四位/用户名模糊搜索等场景怎么改写业务上查手机号后四位的需求很常见比如后台客服根据用户报的手机尾号找人。改写 SQL 可以直接用LIKE加通配符SELECT * FROM users WHERE phone LIKE %5678;但注意LIKE %5678不以固定前缀开头索引同样用不上。这种场景正确做法有两种第一如果号码位数固定先查出符合条件的手机号范围再交给代码处理。手机号是 11 位后四位是5678时手机号范围是138****5678到199****5678可以写区间SELECT * FROM users WHERE phone 13800005678 AND phone 19999995678;但这样查出来的结果集里可能混有后四位不是5678的号码还需要在应用层二次过滤。第二直接用第 4.3 节讲的虚拟列/函数索引方案。如果这个查询是高频核心路径加冗余列是最稳妥的方案如果只是低频临时查一下让代码过滤一次也无妨。用户名模糊搜索、左右模糊匹配的坑类似LIKE %son%无法利用索引LIKE son%可以。业务如果一定需要中间模糊匹配要么用全文索引要么用搜索引擎要么忍受全表扫描没有银弹。4.3 改写后的 EXPLAIN 对比type 从 ALL 恢复成 range改写完一定要用 EXPLAIN 验证不能凭感觉。我处理客服系统那个案例时把 SQL 改完后的执行计划是这样的------------------------------------------------------------------------------------------------------------------------------------------- | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered| Extra | ------------------------------------------------------------------------------------------------------------------------------------------- | 1 | SIMPLE | work_order | NULL | range | idx_create_time | idx_create_time | 5 | NULL | 812 | 100.00| Using index condition | -------------------------------------------------------------------------------------------------------------------------------------------type从ALL变成了rangerows从 338 万降到 812key_len显示 5说明索引键被用起来了。这个查询从十几秒降到了几十毫秒客服页面瞬间流畅。5. 破解手段二用函数索引/虚拟列预计算让索引适配表达式改写 SQL 不一定永远可行。比如业务逻辑要求必须按DATE_FORMAT(create_time, %Y-%m)做分组统计而且查询模式千变万化你不可能让开发每条都改成区间。这时候就要考虑让索引去适配表达式。5.1 MySQL 8.0 表达式索引的正确语法与限制MySQL 8.0.13 开始支持函数索引官方叫 Functional Key Parts语法是给索引项包两层括号CREATE INDEX idx_create_day ON work_order ((DATE_FORMAT(create_time, %Y-%m-%d)));查询时 SQL 必须跟索引表达式完全一致MySQL 的优化器对函数索引表达式的匹配非常死板差别一个空格、一个函数别名都可能不走SELECT * FROM work_order WHERE DATE_FORMAT(create_time, %Y-%m-%d) 2024-11-25;这条就能命中idx_create_day。但如果写成WHERE DATE_FORMAT(create_time, %Y%m%d) 20241125表达式变了索引直接失效。使用函数索引有几个限制MySQL 8.0.13 以上才支持索引表达式必须用括号括起来((expr))两层括号不能少表达式不能引用其他列只能基于当前列只能使用STORED的生成列语法不能是VIRTUAL部分函数有确定性要求RAND()、NOW()这类非确定性函数不能建索引我用 8.0 版本实测下来函数索引在 ORDER BY 子句中也能生效这是个容易被忽略的好处SELECT * FROM work_order ORDER BY DATE_FORMAT(create_time, %Y-%m-%d) DESC LIMIT 10;只要排序表达式和索引表达式完全一致优化器就能顺利用函数索引避免 filesort。5.2 用虚拟列手动实现函数索引5.7 也能用的老方案MySQL 5.7 没有函数索引但你有更通用的手段生成列Generated Column。建一个虚拟列或者存储列把函数结果物化到列上再对这个列建普通索引。MySQL 5.7 开始支持的语法是ALTER TABLE users ADD COLUMN phone_last4 CHAR(4) GENERATED ALWAYS AS (RIGHT(phone, 4)) STORED, ADD INDEX idx_phone_last4 (phone_last4);这里STORED表示列值实际存储占存储空间如果改成VIRTUAL列不占存储但建不了普通索引5.7 有虚拟列索引实验特性8.0 才稳定建议直接用 STORED。日常工作我把手机尾号、日期格式化月、首字母这种高频查询需求都做成 STORED 生成列一劳永逸。虚拟列方案还有额外的好处STORED列在写入时自动计算应用层完全无感知不用改业务代码查询时只需要把原来函数表达式的位置替换成新列名-- 之前SELECT * FROM users WHERE RIGHT(phone, 4) 5678; -- 之后SELECT * FROM users WHERE phone_last4 5678;5.3 函数索引何时生效表达式必须字面量一致函数索引和普通索引有个显著差异普通索引哪怕查询条件换个写法只要能推导出相同语义优化器就能用函数索引要求查询中的表达式和索引定义的表达式在字面上保持一致。这个特性比较死板得特别提醒团队。举个例子你建了((DATE_FORMAT(create_time, %Y-%m-%d)))索引下面这些写法全都不会用这个索引WHERE DATE_FORMAT(create_time, %Y%m%d) 20241125 -- 格式串变了 WHERE DATE_FORMAT(create_time, %Y-%m-%d) DATE_FORMAT(NOW(), %Y-%m-%d) -- 右侧多了函数但左侧表达式其实一致实测部分版本仍可走索引 WHERE DATE_FORMAT(create_time, %Y-%m-%d ) 2024-11-25 -- 多了空格第二行有些人会觉得优化器能识别但 MySQL 对函数索引的匹配并不做语义等价推演最稳妥的做法是让查询里的表达式和索引定义一字不差。这得从开发规范上约束最好在代码评审里加一条检查。所以函数索引方案适合的是同一套函数表达式被反复使用的高频场景比如所有报表页都用DATE_FORMAT(create_time, %Y-%m)分组不适合那种每个查询函数写法千变万化的情况那样索引建了也白搭。5.4 代价提醒存储、写入性能、统计信息动表结构之前一定要搞清楚代价。第一STORED 生成列和函数索引都要占存储空间。一个 300 万行的表加一个 CHAR(4) 的 STORED 列额外占用约 12MB看着不大但如果加五六个冗余列存储和备份恢复时间都会叠加。第二写入性能下降。每次 INSERT 或 UPDATE 都要重新计算生成列的值并更新对应的二级索引相当于多维护一棵 B 树。写多读少的表要慎重我见过有人给流水表加了八个生成列索引写入 qps 直接掉了一半。建议只给读多写少且查询高频的列加函数索引写多读少的表优先考虑改写 SQL。第三统计信息收集。MySQL 的优化器依赖统计信息判断索引使用函数索引上的数据分布如果倾斜严重优化器可能会误判。必要时手动ANALYZE TABLE刷新统计信息或者用FORCE INDEX临时强制指定索引但FORCE INDEX是应急手段不要写进业务代码常态跑。6. 排查口诀与终极清单以后遇到慢查询怎么步步反推把前面的经验浓缩成一套可复用的排查流程遇到疑似函数毁索引的慢查询时按这个顺序来基本不会漏。6.1 三步走EXPLAIN 看 type 后反查 WHERE 和 JOIN 条件第一步看执行计划。type是ALL或indexpossible_keys里有索引但key是 NULL这时候优先检查 WHERE 和 JOIN 条件里有没有对列本身使用函数。第二步逐个检查 SELECT、WHERE、ORDER BY、GROUP BY 里出现的列。对每一列问三个问题有没有被DATE_FORMAT、YEAR、MONTH、LOWER、UPPER、RIGHT、LEFT、CONCAT等显式函数包裹有没有隐式类型转换比如 VARCHAR 列跟数字比、字符集排序规则不一致有没有数值运算比如price * 0.9 100第三步检查参数侧函数。如果函数作用在常量或参数上就不背锅问题往往在别处。6.2 optimizer_trace 怎么用看优化器到底在算什么有些时候 EXPLAIN 信息不够用特别是优化器明明可以选索引却不选你会很想知道它内部在纠结什么。MySQL 提供optimizer_trace可以记录优化器的一举一动SET optimizer_traceenabledon; -- 执行慢 SQL SELECT * FROM work_order WHERE DATE_FORMAT(create_time, %Y-%m-%d) 2024-11-25; -- 查看跟踪信息 SELECT * FROM information_schema.OPTIMIZER_TRACE\G -- 用完关掉 SET optimizer_traceenabledoff;在OPTIMIZER_TRACE的输出里搜索considered_execution_plans或cause字段能看到优化器为什么拒绝某个索引。常见原因是index_condition无法使用或者成本评估更高。这个工具不能常开生产环境开完立刻关只作为疑难杂症的诊断手段。6.3 一张避坑自查表列上的函数、隐式转换、排序规则三兄弟做开发规范或者代码评审时可以直接拿这张表当检查清单。输运模式典型写法是否走索引推荐改法日期格式化WHERE DATE_FORMAT(create_time, %Y-%m-%d) 2024-11-25否改写为范围查询或建函数索引日期提取年WHERE YEAR(create_time) 2024否改写为 2024-01-01 AND 2025-01-01或函数索引字符串大小写WHERE LOWER(user_name) tom否改写为原始列比较或建表达式索引右模糊匹配WHERE phone LIKE %5678否加生成列后建索引或区间二次过滤中模糊匹配WHERE name LIKE %son%否改前缀匹配LIKE son%或全文索引数值运算WHERE price * 0.9 100否改写为WHERE price 100 / 0.9隐式转换数值WHERE phone 13800138000否改成字符串WHERE phone 13800138000函数作用在参数上WHERE create_time DATE_SUB(NOW(), INTERVAL 1 DAY)是无需改排序规则不一致a.user_name b.user_name可能否统一 DDL 排序规则前模糊匹配WHERE name LIKE son%是无需改等值比较裸列WHERE status 1是无需改这张表贴在我们团队内部文档里有一年多了代码评审时直接对着看省了很多来回沟通。最后再分享一个排查经验遇到慢查询不要急着加索引先看 SQL 写法。我统计过自己经手的线上问题因为写法导致索引失效的比例比真正缺索引的比例要高。函数索引是 MySQL 8.0 之后才有的新武器但改写 SQL 永远是最便宜、最不占存储、最不拖累写入的方案。先用范围和裸列比较解决不了再加函数索引或生成列这个顺序不要反过来。等你踩过一次 DATE_FORMAT 的坑就会理解为什么老 DBA 看到函数出现在 WHERE 里会条件反射地皱眉头了。