先交代个背景。我接手过一个订单系统，单表几千万行，create_time上明明建了索引，结果每天凌晨跑一次“按日汇总”的统计，直接把主库 CPU 拉到 90%。开发同学甩过来一条 SQL：
SELECT 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-01'，B+ 树里存的是完整的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_date，MySQL 会根据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 = ALL+key = NULL+rows = 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';结果：
| type | key | rows | Extra |
|---|---|---|---|
| ALL | NULL | 1000000 | Using 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';结果：
| type | key | rows | Extra |
|---|---|---|---|
| range | idx_create_time | 1965 | Using index condition |
type从ALL变成了range，key是idx_create_time，代价完全不是一个量级。注意rows变成 1965，这是因为 MySQL 根据索引统计信息估算出半开区间[2024-05-20, 2024-05-21)大约有近两千条数据，只扫这两千行，有什么理由不选这个方案？
-- 情况 C：MySQL 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';结果：
| type | key | rows | Extra |
|---|---|---|---|
| ref | idx_orders_create_date | 1965 | Using where |
type = 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()这类非确定性函数，函数索引不允许建立，或者建了也用不了，因为同一行每次计算结果不同。
排查这类问题，最直接的办法就是EXPLAIN+SHOW 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 temporary+Using 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。type=ALL且key=NULL，基本可以认定索引失效，然后再看是否涉及函数、隐式转换、排序规则的坑。
加分项：慢查询日志配合mysqldumpslow；performance_schema看语句执行统计；SHOW WARNINGS看优化器重写结果。
真实故障案例复盘
有一次线上用户表登录查询慢，SQL 长这样：
SELECT * FROM user WHERE username = 'admin' AND password = MD5('123456');username有唯一索引，但执行计划显示type=ALL，查了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。如果是，先检查有没有函数/隐式转换，大概率就能直接定位问题。多排查几次，这套“索引失效修复”的肌肉记忆就练出来了。
