先交代个背景。我接手过一个订单系统单表几千万行create_time上明明建了索引结果每天凌晨跑一次“按日汇总”的统计直接把主库 CPU 拉到 90%。开发同学甩过来一条 SQLSELECT COUNT(*), SUM(amount) FROM orders WHERE DATE(create_time) 2024-06-01;我一看就明白了不是索引建得不对而是DATE(create_time)这个函数把索引“废”掉了。这条 SQL 走了全表扫描几千万行硬扫不慢才怪。这篇就专门聊聊 MySQL 里“函数 索引”这个组合的坑为什么函数一上索引就失效日常最常踩的函数式写法有哪些怎么在不改表、少改表的前提下把查询救回来以及 MySQL 8.0.13 之后的函数索引到底怎么用才靠谱。不管是开发、DBA还是准备 MySQL 面试的同学看完都能直接拿去用。1. 先说结论函数一上索引为什么就“废”了1.1 B树索引只认识“原始的列值”很多人对索引的理解停留在“建了索引就能加速”但从来没想过索引到底长什么样、为什么能加速。InnoDB 的二级索引底层是 B 树叶子节点按索引列的值排好序每个索引项里存的是“索引列的值 主键值”。查询时优化器要做的核心操作是在 B 树里按值的大小进行二分查找快速定位到目标区间。现在问题来了如果查询条件是WHERE DATE(create_time) 2024-06-01B 树里存的是完整的create_time比如2024-06-01 14:23:45。MySQL 想用索引就必须先对索引里每一个create_time都算一遍DATE()再拿结果和2024-06-01比较。也就是说索引里存的值和查询条件里要比较的值根本不是同一种东西B 树的有序性瞬间失效。拿生活中的例子类比新华字典是按拼音排序的你按“拼音”查一个字很快但如果你问“笔画数是 12 的字有哪些”字典的拼音顺序就帮不上忙了你只能从第一页翻到最后一页把每个字数一遍笔画。MySQL 的索引就是那本按拼音排的字典WHERE DATE(create_time)就是在要求它“按笔画数查”它只能全表翻。1.2 MySQL 怎么“硬着头皮”全表扫描有人可能会问MySQL 难道不能聪明一点先全量扫描索引算出DATE(create_time)的结果再比较吗理论上可以但实际优化器不会这么做原因是成本太高。如果走二级索引根据DATE(create_time) 2024-06-01这个条件MySQL 无法从索引中直接定位到有序区间——因为索引叶子节点是按create_time排的不是按DATE(create_time)排的。它只能对索引做全量遍历对每个索引项都调用一次DATE()函数算出结果后再判断是否匹配。匹配到符合条件的记录后还要根据主键回表读取整行数据。这个过程的成本比直接扫聚簇索引主键索引叶子节点就是整行数据还要高扫二级索引要额外回表扫聚簇索引反而一次搞定。优化器用成本模型一算type直接选ALL也就是全表扫描。所以结论很明确在索引列上套函数包括隐式类型转换和隐式排序规则转换绝大多数情况下会导致索引失效。这不是因为 MySQL 笨而是从成本模型和索引结构上看全表扫描反而是更优解。搞清楚底层原理比死记“函数会导致索引失效”这句话重要得多。因为后面讲的所有改写方案、函数索引原理都是围绕“怎么把索引恢复成有序结构”展开的。2. 这些“隐形杀索引”的写法你可能每天都在写2.1 日期函数是最经典的坑DATE()、DATE_FORMAT()、YEAR()日期函数是日常开发中造成索引失效的头号杀手开局那个订单查询就是典型。我见过太多次这种写法-- 慢DATE() 套在索引列上 SELECT * FROM orders WHERE DATE(create_time) 2024-06-01; -- 慢DATE_FORMAT() 也一样 SELECT * FROM orders WHERE DATE_FORMAT(create_time, %Y-%m-%d) 2024-06-01; -- 慢YEAR() 甚至更隐蔽 SELECT * FROM orders WHERE YEAR(create_time) 2024;这些写法在逻辑上没错开发同学写起来也非常顺手但性能就是一个字慢。YEAR(create_time) 2024尤其隐蔽因为看起来是“按年份过滤”范围很大很容易默认走索引没问题。实际上它同样无法利用create_time的 B 树有序性只能把所有行的create_time都算一遍年份再做筛选。真正要命的是这种查询在测试环境数据量小的时候根本感觉不出来。等上了生产数据量到千万级、亿级一次全表扫描就是几十秒慢查询日志直接报警。2.2 字符串函数坑LEFT()、UPPER()、SUBSTRING()、TRIM()字符串函数同样常见而且往往是在业务代码里“顺手”写的。-- 慢UPPER() 导致索引失效 SELECT * FROM user WHERE UPPER(name) ZHANGSAN; -- 慢LEFT() 处理列值不会走索引 SELECT * FROM user WHERE LEFT(phone, 3) 138; -- 慢SUBSTRING()、TRIM() 同理 SELECT * FROM product WHERE TRIM(product_code) ABC123;UPPER(name)的问题在于索引里存的是原始大小写的名字比如ZhangSan。如果你想让查ZHANGSAN也能命中就得对索引列做UPPER()这一套函数索引排序又废了。正确的做法是上索引前就把值统一转成大写应用层处理或者在name本身上建函数索引/生成列索引后面会讲。LEFT(phone, 3) 138这类写法也特别常见想查“手机号前三位”但LEFT()同样让索引失效。老实说我工作中还真见过有人用LEFT()做前缀匹配来优化模糊查询想法很好可惜方向反了——LIKE 138%反而能走索引LEFT(phone, 3)走不了。2.3 隐式类型转换和排序规则比函数更隐蔽的两个“刺客”这一节是容易被忽略的坑因为它们根本没有显式的“函数”但 MySQL 在内部做了隐式转换效果等同于在列上套了函数。隐式类型转换最常见的场景字段是varchar查询条件却传了数字。-- 表结构phone VARCHAR(20) 且有索引 SELECT * FROM user WHERE phone 13812345678;MySQL 比较时发现一个是字符串列、一个是数字会把字符串列转成数字再比较相当于执行了CAST(phone AS SIGNED)。列上有了隐式 CAST索引失效全表扫描。反过来如果字段是数字传入字符串通常是把字符串转成数字索引还能用。这个差异我在指导新人时经常强调写 SQL 时字段是什么类型条件就传什么类型别依赖隐式转换。排序列规则不一致还有一个更隐蔽的隐形函数字段的collation和连接/查询上下文的排序规则不一致时MySQL 也可能无法使用索引。比如一张表name字段的排序规则是utf8mb4_general_ci另一张表name是utf8mb4_0900_ai_ci两张表 JOIN 或者做 UNION 时MySQL 必须统一排序规则才能比较内部可能就会做转换导致索引失效。这在老库迁移、新老系统联查时经常莫名其妙踩到。排查这类问题的方法很简单EXPLAIN输出如果key为NULL但 SQL 从语义上明明该走索引先检查字段类型和排序规则是否在“跨类型比较”。3. 别只骂函数解决方案才是重点三种改写姿势3.1 方案一范围查询改写最推荐、零成本对于日期函数这类场景最实用、改动最小的方案就是把等值函数查询改写为范围查询。开头那个订单统计改写成-- 快利用 create_time 索引做范围扫描 SELECT COUNT(*), SUM(amount) FROM orders WHERE create_time 2024-06-01 00:00:00 AND create_time 2024-06-02 00:00:00;原理非常简单create_time上本来就按时间排好序了你直接给了一个“从零点到 24 点前”的区间B 树可以快速定位到区间起点然后顺序扫描区间内所有记录。这就是type range配合索引覆盖的话性能还能再上一个台阶。这里有一个细节必须注意区间要用“左闭右开”。create_time 2024-06-01 00:00:00 AND create_time 2024-06-02 00:00:00正确姿势精确覆盖 6 月 1 日一整天。如果写成create_time 2024-06-01 00:00:00 AND create_time 2024-06-01 23:59:59也能用但存在精度隐患。万一某条记录是23:59:59.500即 23:59:59.5 2024-06-01 23:59:59就会漏掉这条数据。日期时间类型的精度越高这类边界问题越容易发生。同理按年查写成create_time 2024-01-01 AND create_time 2025-01-01按小时查写成create_time 2024-06-01 14:00:00 AND create_time 2024-06-01 15:00:00。这个方案本质上是“把列上的计算改成条件的范围化”。列不动、索引不动、SQL 改动小收益立竿见影是我日常处理慢查询时最先考虑的手段。3.2 方案二生成列 索引MySQL 5.7 的通用方案如果你没办法改 SQL比如 SQL 是第三方平台生成的或者是历史遗留系统里写死的另一个方案是在表上增加一个生成列generated column把函数计算结果物化到新列里再对新列建索引。-- 建一张测试表 CREATE TABLE orders ( id INT PRIMARY KEY AUTO_INCREMENT, create_time DATETIME NOT NULL, amount DECIMAL(10,2) NOT NULL, create_date DATE GENERATED ALWAYS AS (DATE(create_time)) STORED, INDEX idx_create_date (create_date) ); -- 或者对已有表做修改 ALTER TABLE orders ADD COLUMN create_date DATE GENERATED ALWAYS AS (DATE(create_time)) STORED, ADD INDEX idx_create_date (create_date);创建之后插入数据时不需要手动维护create_dateMySQL 会根据create_time自动计算索引也自动维护。以后查询直接写SELECT * FROM orders WHERE create_date 2024-06-01;这条 SQL 就能走idx_create_date索引了因为create_date是一个真实存在的列索引里存的是这个列的值B 树可以做有序查找。这里有个选择需要说清楚STORED和VIRTUAL的区别。STORED计算结果真实存储在磁盘上占空间但查询时直接读值不需要计算。VIRTUAL不占额外存储查询时才计算但 MySQL 8.0 对在虚拟列上加二级索引的支持才更完整稳定5.7 如果想用生成列索引建议直接STORED稳妥。说实话生成列 索引是“旧版本环境”下的主力方案。但代价也是明显的表结构变更、DML 时多写一个列的计算和索引维护写入性能会有折损尤其适合“读多写少、按天/按月统计”的场景。3.3 方案三MySQL 8.0.13 起的函数索引最优雅MySQL 8.0.13 开始支持函数索引functional index语法上直接对表达式建索引省掉了手动建生成列的步骤CREATE INDEX idx_orders_create_date ON orders ((DATE(create_time)));注意表达式外面套了两层括号((DATE(create_time)))。这个双层括号是 MySQL 函数索引的固定语法漏掉会报错。这个索引在 SQL 里不需要显式生成列字段直接写SELECT * FROM orders WHERE DATE(create_time) 2024-06-01;这样一个原来的“危险 SQL”就能命中idx_orders_create_date走索引扫描业务代码一行都不用改。但它的本质是什么很多人不太清楚。MySQL 的函数索引底层实现其实就是虚拟生成列 索引。优化器会把表达式DATE(create_time)当作一个不可见的生成列然后对这个隐藏列建立二级索引。查询时即使你没写生成列的字段优化器也能识别出WHERE DATE(create_time) ...可以匹配函数索引。不过函数索引有使用限制必须值得注意索引表达式必须支持确定性计算不能使用随机函数、时间函数比如NOW()、RAND()。优化器不会自动对任意函数表达式都应用函数索引只有查询条件里的表达式和你建索引的表达式完全一致时才能命中。比如你建了((DATE(create_time)))查询写YEAR(create_time) 2024依然是全表扫描。函数索引本质上也是索引会占用存储空间表达式计算的结果要存下来每次INSERT/UPDATE都要维护写入开销是实打实存在的。所以它适合的其实是SQL 已经无法修改、且运行在 MySQL 8.0.13 环境里的存量场景。如果是新做的系统我还是建议优先走“方案一范围改写”简单直接没有存储和写入上的额外消耗。3.4 方案四设计层面的釜底抽薪最后提一个偏设计层面的思路与其在查询时针对函数做补救不如在设计表结构时就把“查询需要的维度”直接建模成字段。比如订单场景业务上经常按天统计那就在订单表或者对应的宽表/数仓层上直接加一个order_date字段业务在写入时同步填充DATE(create_time)的值并建立索引。查询的时候直接WHERE order_date 2024-06-01索引天然命中完全不需要函数、生成列和函数索引那些绕弯的操作。这算不算冗余严格说算。order_date的信息和create_time有重复但“冗余”在适度的情况下反而是性能利器。尤其是互联网业务里常见的“牺牲一点写放大换取查询简单和快”是非常普遍的设计权衡。我个人建议的顺序是优先改写 SQL 为范围查询方案一如果 SQL 不可改MySQL 8.0 用函数索引方案三、老版本用生成列索引方案二如果这个查询是长期核心查询且业务可以接受写放大那就直接加冗余字段方案四。4. 实操验证EXPLAIN 教你读懂索引失效4.1 EXPLAIN 关键字段怎么读在讨论改写方案之前得先教会大家怎么“眼见为实”。MySQL 的EXPLAIN是排查索引失效问题的第一工具重点看四个字段type访问类型。从好到差大致是system const eq_ref ref range index ALL。ALL是全表扫描基本可以认定这个查询有性能问题range是索引上的范围扫描正常ref是等值匹配很好。如果看到ALL说明这个 SQL 很可能没吃到索引。key实际使用到的索引名。如果key是NULL说明全表扫描显示索引名说明走了索引。rows优化器估算需要扫描的行数这个数字越小越好。全表扫描时它会直接显示整个表的总行数非常夸张。Extra额外的扩展信息常见的有Using where回表后继续过滤、Using index索引覆盖不回表、Using filesort需要额外的排序操作性能隐患、Using temporary使用临时表。看到Using temporary或Using filesort通常意味着排序/分组环节出了性能问题。举个例子看type ALLkey NULLrows 5000000不用多想这条 SQL 一定扫了全表。4.2 同一查询改写前后的执行计划对比我直接跑一个实际场景演示大家可以在自己的环境复现。先建一张测试表插入一百万条数据CREATE TABLE orders ( id INT PRIMARY KEY AUTO_INCREMENT, create_time DATETIME NOT NULL, amount DECIMAL(10,2) NOT NULL, INDEX idx_create_time (create_time) ); -- 这里我用存储过程灌数据 DELIMITER $$ CREATE PROCEDURE insert_orders() BEGIN DECLARE i INT DEFAULT 1; WHILE i 1000000 DO INSERT INTO orders (create_time, amount) VALUES ( TIMESTAMP(2023-01-01) INTERVAL (RAND() * 500) DAY, RAND() * 1000 ); SET i i 1; END WHILE; END$$ DELIMITER ; CALL insert_orders();然后分别跑三个查询的执行计划-- 情况 A函数套在索引列上 EXPLAIN SELECT * FROM orders WHERE DATE(create_time) 2024-05-20;结果typekeyrowsExtraALLNULL1000000Using where全表扫描key为NULL预计扫一百万行。这个Using where意味着 MySQL 把每一行的create_time取出来套上DATE()再和2024-05-20比较。慢是必然的。-- 情况 B改写为范围查询 EXPLAIN SELECT * FROM orders WHERE create_time 2024-05-20 00:00:00 AND create_time 2024-05-21 00:00:00;结果typekeyrowsExtrarangeidx_create_time1965Using index conditiontype从ALL变成了rangekey是idx_create_time代价完全不是一个量级。注意rows变成 1965这是因为 MySQL 根据索引统计信息估算出半开区间[2024-05-20, 2024-05-21)大约有近两千条数据只扫这两千行有什么理由不选这个方案-- 情况 CMySQL 8.0.13 直接建函数索引 CREATE INDEX idx_orders_create_date ON orders ((DATE(create_time))); EXPLAIN SELECT * FROM orders WHERE DATE(create_time) 2024-05-20;结果typekeyrowsExtrarefidx_orders_create_date1965Using wheretype ref说明函数条件匹配上了函数索引按索引内的DATE(create_time)等值定位回表获取整行。性能同样能接受。这套对比做完我相信“函数一上索引就废”和“改写后重生”的差别大家心里应该有数了。5. 高频问题与避坑实录5.1 为什么我建了函数索引执行计划还是不走有同学会碰到明明按官方文档建了函数索引((DATE(create_time)))EXPLAIN却还是ALL气得想摔键盘。我排查过几次原因通常来自这几个方向查询表达式和索引表达式不完全一致。这是最高频的原因。你建索引用的是DATE(create_time)查询写的是CAST(create_time AS DATE)或者DATE_FORMAT(create_time, %Y-%m-%d)看起来结果一样但 MySQL 优化器认定这两个表达式不是同一个函数索引直接废掉。字符集/排序规则不一致。如果你建的函数索引表达式里包含LOWER(name)而查询上下文排序规则和建表时不一样也可能匹配不上。这个坑尤其出现在不同库 JOIN 的时候。直方图缺失导致成本估算错误。优化器判断走函数索引成本更高的时候它会“毅然决然”放弃索引。解决办法是ANALYZE TABLE更新统计信息或者调整optimizer_switch相关代价参数。函数/表达式本身是非确定性的。比如表达式里带了NOW()、RAND()这类非确定性函数函数索引不允许建立或者建了也用不了因为同一行每次计算结果不同。排查这类问题最直接的办法就是EXPLAINSHOW WARNINGS看优化器到底重写了什么、为什么没匹配。5.2 排序、分组、JOIN 里的函数也会毁索引前面讲的主要是WHERE条件实际上函数对索引的影响不止限于过滤条件ORDER BY、GROUP BY里同样存在。-- 按年份排序无法利用 create_time 索引最后会做 filesort SELECT * FROM orders ORDER BY YEAR(create_time) DESC; -- 按日分组统计无法用索引做 loose index scan经常触发临时表 SELECT DATE(create_time) AS day, COUNT(*) FROM orders GROUP BY DATE(create_time);ORDER BY YEAR(create_time)的问题和WHERE YEAR(create_time)一样索引里页节点的顺序是按完整create_time排的而不是年份MySQL 只能把所有行都取出来再在内存或磁盘上做排序。那ORDER BY create_time DESC能走索引吗一定可以而且如果是倒序场景配合ORDER BY create_time DESC的降序索引直接用。GROUP BY DATE(create_time)这个就更典型了我见过太多报表 SQL 这么写按天分组统计却对函数的返回值做分组索引的顺序帮不上忙Using temporaryUsing filesort双双出现。优化思路其实很朴素如果业务确实要按天统计要么把统计维度做成独立字段方案四要么缩小查询范围到一天内再分组。JOIN 场景同理如果关联条件里有一边是函数表达式比如ON DATE(a.create_time) b.day那这条关联想走b.day的索引都难因为 MySQL 需要把a.create_time计算之后再去匹配。5.3 面试题与真实故障案例速查最后给一份速查面试和实战都能用上。面试题 1为什么在索引列上加函数会导致索引失效答索引底层是 B 树叶子节点按原始索引列的值排序。当查询条件对列做函数处理时索引中存储的值无法直接与函数处理后的结果做有序比较优化器无法定位到目标区间只能全表扫描或全索引扫描成本高于走索引所以优化器放弃索引。加分项提到隐式类型转换的本质也是“列上隐式套了 CAST 函数”提到函数索引/生成列的解决方案提到优化器成本估算和直方图。面试题 2如何排查一条 SQL 是否走索引答使用EXPLAIN看type、key、rows、Extra。typeALL且keyNULL基本可以认定索引失效然后再看是否涉及函数、隐式转换、排序规则的坑。加分项慢查询日志配合mysqldumpslowperformance_schema看语句执行统计SHOW WARNINGS看优化器重写结果。真实故障案例复盘有一次线上用户表登录查询慢SQL 长这样SELECT * FROM user WHERE username admin AND password MD5(123456);username有唯一索引但执行计划显示typeALL查了SHOW CREATE TABLE才发现username字段排序规则是utf8mb4_bin而查询连接排序规则走的是utf8mb4_general_ci跨排序规则比较导致索引失效这在老系统非常典型。后来统一了排序规则Index 立刻就命中了。还有一次同事写了个查询WHERE amount 1000 5000他以为amount上建了索引就能用结果优化器还是全表扫。虽然amount 1000只是简单算术但它同样是“在列上做计算”B 树的有序性只对amount本身有效对amount 1000无效。这类案例见多了你会发现所有索引失效问题都能归结到一个朴素的道理索引的有序性是建立在“列本身的值”上的任何一层包裹函数、隐式转换、计算都会打破这种有序性。根据我个人的经验处理“函数毁索引”这类问题的第一原则是优先考虑改写 SQL而不是引入函数索引。范围查询改写简单、不占额外存储、不增加写入开销应该成为默认选项生成列和函数索引是补救手段适合 SQL 改不动、存量问题多的场景加冗余字段则适合核心高频查询的长期方案。如果你手头正好有慢查询拿EXPLAIN跑一下看看是不是有一行key NULL。如果是先检查有没有函数/隐式转换大概率就能直接定位问题。多排查几次这套“索引失效修复”的肌肉记忆就练出来了。