先讲一个我实际遇到过的场景线上有个订单表数据量过百万create_time上明明建了索引结果某天一跑月度报表接口直接超时。把 SQL 捞出来一看WHERE DATE_FORMAT(create_time, %Y-%m) 2024-01再一看执行计划type 是 ALL索引 key 直接为空全表扫。这种案例我见过太多次了问题就出在“函数”这两个字上。MySQL 的 B 树索引是按照原始列值排序存储的一旦你在查询条件里对索引列套了函数索引本身的有序性就帮不上忙了退化成全表扫描几乎是必然。这篇文章就围绕“函数索引”这个主题聊透什么写法会让索引失效为什么失效以及 MySQL 8.0 的函数索引到底该怎么用才能救人而不是“坑上加坑”。如果你是后端开发、兼职 DBA或者正在准备 MySQL 性能优化相关的面试题这篇内容应该能帮你在排查慢 SQL 时少走不少弯路。1. 先搞清楚索引为什么这么怕函数1.1 有序结构遇上“加工过程”MySQL 最常见的 InnoDB 索引是 B 树它的核心优势有两点叶子节点有序查找时能二分快速定位同时叶子节点之间是串联的做范围扫描也很快。这个结构可以类比一本按拼音排好序的字典——如果你要查“mysql”这个单词直接翻到 m 的区域就能找到但如果你的问题是“把单词倒过来之后哪些词以 l 开头”那你只能把整本字典从头到尾翻一遍因为字典是按原始词条排的倒过来的内容完全没有对应的排序存储。索引列也是一样的。MySQL 只存储原始列值比如create_time字段存的就是2024-01-15 08:30:00这样一个 datetime。当你在 WHERE 条件里写DATE_FORMAT(create_time, %Y-%m) 2024-01时数据库面对的其实是两个完全不同的东西左边是“经过格式化后的字符串”右边是“字符串常量”。而索引里并没有存这个格式化结果所以 MySQL 只能把每一行的原始值取出来计算一遍函数再和右边的常量比对。这个行为就叫全表扫描数据量一大性能自然雪崩。1.2 优化器为什么不能自动反向推导范围有些人会问DATE_FORMAT(create_time, %Y-%m) 2024-01和create_time BETWEEN 2024-01-01 AND 2024-01-31逻辑上不是几乎等价吗优化器为什么不自动做这个转换原因有两个。第一SQL 表达式等价性判断的成本非常高。优化器忙于估算执行计划的代价根本没精力去证明“这个函数条件可以改写成某个范围条件”而且很多函数本身不是单调函数比如MONTH(create_time) 1你能直接反推出日期范围吗显然不能它会覆盖每年的一月范围根本不是一个连续区间改写难度陡增。第二函数计算的不确定性。MySQL 允许调用很多非确定性函数像NOW()、UUID()这类函数结果随时间变化无法预计算索引自然无从谈起。虽然在 8.0 之后像YEAR(date_col) 2024这类查询在某些情况下可以被优化器识别配合生成的隐藏列但这属于“能力范围之内的特例”绝对不能作为业务上线时依赖的既定行为。1.3 索引杀手的常见谱系结合我自己的排查经验和见过的代码下面这些写法几乎是“每建一个虚拟索引就死一个索引”的典型写法示例为什么失效日期格式化WHERE DATE_FORMAT(create_time, %Y-%m) 2024-01日期函数处理原始列索引无匹配截取操作WHERE SUBSTRING(name, 1, 3) ABC截取函数处理列字段变换WHERE UPPER(code) ABC大小写转换处理列算术运算WHERE amount 1 100列参与运算前置模糊WHERE name LIKE %ABC以%开头的 LIKE 无法使用索引的有序前缀隐式转换WHERE mobile 13800001111mobile为字符串列字符串列被隐式转换为数字排序/分组ORDER BY DATE(create_time)排序列经过函数计算完全绕过索引排序这些写法里大部分是“显式的函数调用”一眼就能看出问题但隐式转换比较隐蔽下个章节单独聊。总之判断一条 SQL 能否用上索引你可以建立一条简单直觉索引是给原始列值准备的任何“先加工、再比较”的条件本质都是在要求数据库另建一套存储结构。所以接下来说的“函数索引”本质上就是帮你把这套存储结构真正建出来。2. 三个真实翻车现场2.1 月度报表查询DATE_FORMAT 让百万表全表扫我曾经接手过一个订单查询接口表order_info有 800 多万行核心查询是SELECT id, order_no, amount FROM order_info WHERE DATE_FORMAT(create_time, %Y-%m) 2024-01 ORDER BY create_time DESC LIMIT 20;表上有idx_create_time按理说按时间排序加过滤很轻松。但EXPLAIN的结果吓我一跳typeALLkeyNULLrows872万额外信息里还有一个Using filesort。这就意味着数据库把整张表扫了一遍逐行算出格式化的月份再去做排序。慢查询日志里这个语句直接成了 TOP 1接口响应从几十毫秒涨到四五秒。问题根源不难定位就是DATE_FORMAT套在了索引列上。我当时的处理方式是先改成范围条件SELECT id, order_no, amount FROM order_info WHERE create_time 2024-01-01 00:00:00 AND create_time 2024-02-01 00:00:00 ORDER BY create_time DESC LIMIT 20;改造后执行计划变成了typerangekeyidx_create_time额外信息消失扫描行数骤降到一个月的量级。这个思路对日期类函数非常有效因为日期本质是连续的任何月份、年份查询都可以转换成一段半开区间。2.2 手机号查询一个引号引发的“隐式转换”第二个案例更加隐蔽。某用户表user_base里mobile是VARCHAR(11)类型也建了普通索引。某天有人写了一句SELECT id, name FROM user_base WHERE mobile 13800001111;注意右边的 13800001111 是数字没加引号。MySQL 在做类型比较时会把字符串列和数字常量一起转成浮点数再比较也就是说列上的字符串值被隐式调用了 CAST 函数相当于对mobile列做了一次加工。结果就是索引被废执行计划又变成全表扫描。这类隐式转换在EXPLAIN的输出里不太显眼你只看typeALL、keyNULL但字段上明明有索引就容易一脸懵。我的排查习惯是打完 EXPLAIN 之后紧跟一句SHOW WARNINGS;方便看到类似“Cannot use index due to type or collation conversion on column”的提示直接从根上定位原因。修复方式极其简单SQL 里把数字常量加上引号变成字符串SELECT id, name FROM user_base WHERE mobile 13800001111;执行计划立刻回到typeref索引生效。这里要特别提醒如果你写WHERE id 123其中id是 INT 列MySQL 反而会把你传入的字符串转成数字列本身不参与转换索引不受影响。隐式转换到底毁不毁索引关键要看是“列被变形”还是“常量被变形”。2.3 大小写与排序规则别小瞧 collation 的坑说到UPPER(name) ABC这类写法很多新人会问既然查询时要忽略大小写为什么不直接建一个函数索引答案是可以但首先要搞清楚你的字段排序规则。如果字段用的是utf8mb4_0900_ai_ciMySQL 8.0 的默认 collation这个_ci后缀就代表大小写不敏感。也就是说WHERE name abc已经能匹配ABC、Abc了你完全没必要再写UPPER(name)加了反而帮倒忙。但是如果字段被设置成utf8mb4_bin或者utf8mb4_0900_bin排序规则变成大小写敏感WHERE name abc就不会匹配ABC此时有人图省事写了WHERE UPPER(name) ABC索引被废全表扫描又来了。我印象里有一个项目就栽在这里。某业务要求“会员码不区分大小写但历史存量数据又有大小写混用”开发为了兼容所有查询都写成WHERE UPPER(vip_code) ...。结果随着数据上涨查询越来越慢。最后我给的表结构设计是加一个基于生成列的函数索引或者干脆把字段改造成统一的_bin排序规则再配合函数索引一起用。这类问题在排查时最难的不是写语句而是判断“到底需不需要这个函数”。很多情况下删掉函数本身就是最优解。3. MySQL 8.0 函数索引到底是救星还是坑如果你的查询场景确实无法改写比如必须用UPPER(name)的历史写法、必须按月分组、必须截取字符串那么 MySQL 8.0 的函数索引就是为这种情况准备的。8.0.13 版本之后MySQL 官方直接支持在表达式上建索引。3.1 函数索引的本质一张隐藏的生成列索引所谓函数索引官方文档叫 “functional key parts”底层思路其实是我们老早就用的“生成列 普通索引”的组合方案。数据库在表的每一行里根据表达式算出一个值把这个值单独存成一份索引数据。比如你建一个LOWER(name)的函数索引相当于表里藏了一个虚拟列LOWER(name)然后对这个列建 B 树索引。创建语法并不复杂注意函数表达式外面必须加一层括号CREATE INDEX idx_user_lower_name ON user_info ((LOWER(name)));加完这个索引之后原来的查询SELECT id FROM user_info WHERE LOWER(name) abc;执行计划就会从typeALL变成typeref走idx_user_lower_name。你如果执行SHOW CREATE TABLE user_info\G会看到表定义里多了一行类似KEY idx_user_lower_name ((lower(name)))的东西这就是函数索引。在 8.0 之前的老版本里你想达到同样效果要手动加生成列ALTER TABLE user_info ADD COLUMN name_lower VARCHAR(64) GENERATED ALWAYS AS (LOWER(name)) STORED, ADD INDEX idx_name_lower (name_lower);这段语句在 MySQL 5.7 里也支持。所以函数索引并不是什么黑科技它只是把“生成列索引”变成了一个直接的语法糖。3.2 使用函数索引前必须看清四个限制函数索引不是万能膏药踩坑之前先看这四个约束限制说明版本依赖函数索引语法需要 MySQL 8.0.13 及以上版本低于这个版本只能走生成列方案确定性要求表达式必须返回确定性结果不允许使用NOW()、UUID()、用户自定义函数等不支持范围查找函数索引只支持等值匹配不支持、、BETWEEN之类的范围扫描这是和普通索引最关键的差异索引类型限制函数索引用不了 FULLTEXT、SPATIAL 索引函数表达式也不能用于前缀索引的声明最后一点尤其值得记住。很多人以为函数索引建完之后WHERE LOWER(name) abc也能走索引实际上官方明确表示 functional key parts 不支持 range lookups建了也白建。所以我的建议是能用范围改写先用范围改写无法改写的等值场景才考虑函数索引。3.3 从 0 到 1 实测一轮函数索引为了验证“到底能不能救火”我经常在测试环境做这样的模拟。先建一张订单表CREATE TABLE order_info ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, order_no VARCHAR(32) NOT NULL, create_time DATETIME NOT NULL, amount DECIMAL(10,2) NOT NULL, PRIMARY KEY (id), KEY idx_create_time (create_time) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;再用一个存储过程灌入一百万行测试数据时间随机分布在一年里DELIMITER $$ CREATE PROCEDURE sp_gen_order(IN total INT) BEGIN DECLARE i INT DEFAULT 1; SET autocommit0; WHILE i total DO INSERT INTO order_info(order_no, create_time, amount) VALUES ( CONCAT(NO, LPAD(i, 8, 0)), TIMESTAMP(2024-01-01 00:00:00) INTERVAL (RAND() * 300) DAY, ROUND(RAND() * 1000, 2) ); SET i i 1; IF i % 5000 0 THEN COMMIT; END IF; END WHILE; COMMIT; END$$ DELIMITER ; CALL sp_gen_order(1000000);这时执行EXPLAIN SELECT id, order_no, create_time FROM order_info WHERE DATE_FORMAT(create_time, %Y-%m) 2024-01;结果大概率是typeALL、rows100万、ExtraUsing where。接下来建函数索引CREATE INDEX idx_order_month ON order_info ((DATE_FORMAT(create_time, %Y-%m)));再跑一遍同样的 EXPLAIN你会看到typerefkeyidx_order_month扫描行数从一百万掉到约八万一年十二个月每个月差不多八万行。这说明函数索引在等值场景下确实能显著减少扫描量。但别忘了ORDER BY create_time的排序问题——执行计划里很可能还是会带着Using filesort因为查询使用的索引是函数索引不是idx_create_time。如果你同时有排序需求更推荐的做法是把它改回范围查询或者额外建生成列来覆盖排序。3.4 函数索引也有隐性成本函数索引的代价经常被低估。第一每次写入INSERT / UPDATE时数据库都需要额外计算函数表达式并更新索引页写入性能肯定会受影响如果你在核心高频插入表上加了五六个函数索引写入时延的上升是非常明显的。第二索引空间占用是实打实的函数结果本身需要存储空间如果表达式结果很长比如CONCAT多个字段索引体积可能比原表还大。第三函数索引的选择性需要评估如果函数结果只有少数几个值比如按月份生成的值最多 12 种索引选择性差优化器很可能直接放弃索引改成全表扫特别是在数据量只有几万行的小表上这种表现尤其明显。我在生产环境给查询加函数索引之前一定会先用一条统计 SQL 看分布SELECT DATE_FORMAT(create_time, %Y-%m) AS ym, COUNT(*) AS cnt FROM order_info GROUP BY ym ORDER BY cnt DESC;如果结果集中某个值占比超过 20%这个函数索引走了也可能变成“扫半个索引”收益有限。此时不如老老实实做 SQL 改写或者把数据预聚合到单独的表里。4. 更稳妥的兜底方案SQL 改写与冗余派生列4.1 万能套路把“函数条件”换成“范围条件”对于日期和数值类函数最优雅、最稳定、最不消耗额外存储空间的方案永远是改写 SQL把函数条件转换成对原始列的范围比较。以下是我常用的几个改写模板原始写法推荐改写DATE_FORMAT(create_time, %Y-%m) 2024-01create_time 2024-01-01 AND create_time 2024-02-01YEAR(create_time) 2024create_time 2024-01-01 AND create_time 2025-01-01MONTH(create_time) 6create_time 2024-06-01 AND create_time 2024-07-01如果限定年份DATE(create_time) 2024-01-15create_time 2024-01-15 AND create_time 2024-01-16amount 1 100amount 99amount * 2 10amount 5注意符号方向不变写范围条件的核心是“半开区间”思维下限包含上限不包含。这个习惯还能顺带消灭 23:59:59.999 这类边界问题。要注意改写后 SQL 的语义必须仔细核对尤其遇到MONTH(create_time) 6这种跨年份函数时你必须在应用层明确“要查哪一年”否则改写会改变需求边界。我见过不少因为偷懒直接BETWEEN 2024-06-01 AND 2024-06-30的写法最后 6 月 30 号的数据被漏掉因为 6 月 30 号 23:59:59 之后还有一整天的时间戳。所以统一用 下边界 AND 上边界就不会错。4.2 生成列冗余让索引保存“加工后的数据”如果业务实在没法改 SQL比如大量历史代码写了UPPER(vip_code)你不可能把几十个接口全部改完那就可以采用生成列冗余方案。逻辑很简单把函数结果存成一列再对这一列加普通索引。这样查询条件里的函数可以等价替换为直接列比较索引也恢复正常。ALTER TABLE user_info ADD COLUMN vip_code_upper VARCHAR(64) GENERATED ALWAYS AS (UPPER(vip_code)) STORED, ADD INDEX idx_vip_code_upper (vip_code_upper);之后查询写成SELECT id FROM user_info WHERE vip_code_upper ABC123;注意两点生成列的数据由数据库自动维护你往表里显式插入vip_code_upper会直接报错生成列可以选择VIRTUAL或STORED其中VIRTUAL不占用额外的物理存储空间但每次读的时候需要计算实际使用中我给生成列建索引通常倾向于STORED因为写入时算一次查询时直接读对 InnoDB 的可用性更友好踩坑更少。这个方法不仅适用于等值函数还能处理排序需求。比如报表经常要按天分组SELECT DATE_FORMAT(create_time, %Y-%m-%d) AS day, SUM(amount) FROM order_info GROUP BY day;可以加一列order_day并建索引然后改写为GROUP BY order_day不仅过滤能走索引排序也能利用索引顺序直接绕开临时表和 filesort。这个思路在 BI 报表库里非常实用。4.3 排序与分组场景的处理函数索引解决不了排序问题的原因我在 3.3 节提过函数索引只保存函数结果并不会按原始列的物理顺序存储。如果业务频繁出现ORDER BY DATE(create_time)与其纠结索引不如直接把DATE(create_time)这个中间结果物化成列。另一种常见场景是GROUP BY UPPER(status)本质也是对加工后的值分组。此时你如果不想改表结构就只能接受临时表开销但要记住临时表意味着数据要落盘数据量大时再叠加文件排序性能会非常难看。我自己处理这类问题第一选择永远是“物化中间产物”。你永远不要跟 B 树的结构较劲而是顺着它的脾气把加工后的结果变成一个新的可排序、可哈希的列。衍生列、生成列、汇总表本质都是这一条路。4.4 编码规范与评审环节的兜底技术方案再好也扛不住同事持续写“毁索引”的 SQL。所以我在团队里推动了一套简单的上线自检机制所有新增或变更的核心 SQL必须附带EXPLAIN截图并且评审时重点看key字段是否为空、type是否为 ALL。更进一步可以在 CI 环节引入简单的文本扫描重点检查WHERE、ORDER BY、GROUP BY后面的条件里索引列是否被常见的DATE_FORMAT、UPPER、LOWER、SUBSTRING、YEAR、MONTH、LEFT等函数直接包裹。这种扫描不能替代人工评审但能拦住 80% 的低级问题。5. 排查实录与上线自检清单5.1 三步定位“索引杀手 SQL”排查这类性能问题我的思路长期稳定在三步。第一步打开慢查询日志先把问题 SQL 捞出来SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1;线上环境建议把阈值设得更低比如 0.5 秒日志目录留意别把磁盘塞满排查完记得关掉或者改回原配置。第二步对嫌犯 SQL 执行EXPLAIN不要只看type要把key、rows、Extra三个字段一起看。如果keyNULL、rows很大、Extra里有Using where甚至Using filesort基本可以锁定是索引使用出了问题。第三步执行SHOW WARNINGS;它会直接告诉你 MySQL 在优化过程中遇到的转换问题比如隐式类型转换导致列被强制 CAST这是自动检查里最省事的一招。这三个步骤做完绝大多数“明明有索引却不走”的问题都能找到方向。剩下小概率是统计信息过旧、优化器误判这时候再考虑ANALYZE TABLE或者用FORCE INDEX做临时验证。5.2 常见问题速查表我把日常被问到最多的几个问题整理成一个速查表方便快速定位现象大概率原因处理建议建了函数索引EXPLAIN 还是不走版本低于 8.0.13表达式不一致选择性差条件含范围比较确认版本严格对照表达式验证数据分布函数索引在SHOW CREATE TABLE里看不到列函数索引不是真实列只是索引定义里的表达式正常现象不影响使用字符串列和数字常量比较导致全表扫列被隐式转为数字常量加引号保证类型一致LIKE %abc走不了索引前置通配符倒序冗余列、全文索引或改造搜索方案ORDER BY DATE(col)产生 filesort函数处理排序列生成列 索引或应用层排序WHERE YEAR(col) 2024用了函数索引但仍慢函数索引只支持等值分钟级别的范围可能还要靠普通索引改写为范围条件5.3 上线前自检清单给核心索引做变更之前建议按这份清单过一遍备份表结构记录变更前的EXPLAIN结果方便变更后对比。DDL 放低峰期执行。虽然 MySQL 8.0 的在线 DDL 对普通索引支持得不错但函数索引涉及表达式的计算表越大重建时间越长并且 DDL 期间仍然会占用额外 IO 和空间。如果建的是生成列 索引务必验证 INSERT / UPDATE 的延迟影响可以先在压测环境跑一轮写入压测再看平均时延是否在可接受范围。上线后观察慢查询日志和performance_schema里的语句统计确认扫描行数、执行次数都符合预期。如果优化器依然没有选择新索引先用ANALYZE TABLE更新统计信息然后再考虑FORCE INDEX但FORCE INDEX最好只作为临时手段长期依赖很容易在数据分布变化后出问题。5.4 一条重要的工作心得优化器是基于成本的它不会因为你“辛辛苦苦建了索引”就一定要用。函数索引创建之后如果数据分布、常量类型、表达式形态和预期有任何一点不一致它都可能弃用索引转而全表扫。所以我在项目里把“函数索引”当成“最后手段”而不是“第一手段”。第一手段永远是改写 SQL用原始列的范围条件压榨普通索引的潜力第二手段才是冗余生成列把加工后的值物化出来第三手段才轮到 8.0 函数索引。按照这个顺序能避免掉绝大多数性能返工。这篇内容聊到的坑都是我线上踩过、或看到同事踩过的。说点个人体会你很难完全阻止别人写出DATE_FORMAT套索引列的 SQL但你可以把排查流程、改写模板、生成列方案做成一套团队公约。毕竟索引不是越多越好函数索引更是如此——它像是在数据模型上额外开了一条侧路开得好能救命开多了侧路本身也会变成新的拥堵点。
MySQL函数索引实战：从索引失效到SQL优化全解析
先讲一个我实际遇到过的场景线上有个订单表数据量过百万create_time上明明建了索引结果某天一跑月度报表接口直接超时。把 SQL 捞出来一看WHERE DATE_FORMAT(create_time, %Y-%m) 2024-01再一看执行计划type 是 ALL索引 key 直接为空全表扫。这种案例我见过太多次了问题就出在“函数”这两个字上。MySQL 的 B 树索引是按照原始列值排序存储的一旦你在查询条件里对索引列套了函数索引本身的有序性就帮不上忙了退化成全表扫描几乎是必然。这篇文章就围绕“函数索引”这个主题聊透什么写法会让索引失效为什么失效以及 MySQL 8.0 的函数索引到底该怎么用才能救人而不是“坑上加坑”。如果你是后端开发、兼职 DBA或者正在准备 MySQL 性能优化相关的面试题这篇内容应该能帮你在排查慢 SQL 时少走不少弯路。1. 先搞清楚索引为什么这么怕函数1.1 有序结构遇上“加工过程”MySQL 最常见的 InnoDB 索引是 B 树它的核心优势有两点叶子节点有序查找时能二分快速定位同时叶子节点之间是串联的做范围扫描也很快。这个结构可以类比一本按拼音排好序的字典——如果你要查“mysql”这个单词直接翻到 m 的区域就能找到但如果你的问题是“把单词倒过来之后哪些词以 l 开头”那你只能把整本字典从头到尾翻一遍因为字典是按原始词条排的倒过来的内容完全没有对应的排序存储。索引列也是一样的。MySQL 只存储原始列值比如create_time字段存的就是2024-01-15 08:30:00这样一个 datetime。当你在 WHERE 条件里写DATE_FORMAT(create_time, %Y-%m) 2024-01时数据库面对的其实是两个完全不同的东西左边是“经过格式化后的字符串”右边是“字符串常量”。而索引里并没有存这个格式化结果所以 MySQL 只能把每一行的原始值取出来计算一遍函数再和右边的常量比对。这个行为就叫全表扫描数据量一大性能自然雪崩。1.2 优化器为什么不能自动反向推导范围有些人会问DATE_FORMAT(create_time, %Y-%m) 2024-01和create_time BETWEEN 2024-01-01 AND 2024-01-31逻辑上不是几乎等价吗优化器为什么不自动做这个转换原因有两个。第一SQL 表达式等价性判断的成本非常高。优化器忙于估算执行计划的代价根本没精力去证明“这个函数条件可以改写成某个范围条件”而且很多函数本身不是单调函数比如MONTH(create_time) 1你能直接反推出日期范围吗显然不能它会覆盖每年的一月范围根本不是一个连续区间改写难度陡增。第二函数计算的不确定性。MySQL 允许调用很多非确定性函数像NOW()、UUID()这类函数结果随时间变化无法预计算索引自然无从谈起。虽然在 8.0 之后像YEAR(date_col) 2024这类查询在某些情况下可以被优化器识别配合生成的隐藏列但这属于“能力范围之内的特例”绝对不能作为业务上线时依赖的既定行为。1.3 索引杀手的常见谱系结合我自己的排查经验和见过的代码下面这些写法几乎是“每建一个虚拟索引就死一个索引”的典型写法示例为什么失效日期格式化WHERE DATE_FORMAT(create_time, %Y-%m) 2024-01日期函数处理原始列索引无匹配截取操作WHERE SUBSTRING(name, 1, 3) ABC截取函数处理列字段变换WHERE UPPER(code) ABC大小写转换处理列算术运算WHERE amount 1 100列参与运算前置模糊WHERE name LIKE %ABC以%开头的 LIKE 无法使用索引的有序前缀隐式转换WHERE mobile 13800001111mobile为字符串列字符串列被隐式转换为数字排序/分组ORDER BY DATE(create_time)排序列经过函数计算完全绕过索引排序这些写法里大部分是“显式的函数调用”一眼就能看出问题但隐式转换比较隐蔽下个章节单独聊。总之判断一条 SQL 能否用上索引你可以建立一条简单直觉索引是给原始列值准备的任何“先加工、再比较”的条件本质都是在要求数据库另建一套存储结构。所以接下来说的“函数索引”本质上就是帮你把这套存储结构真正建出来。2. 三个真实翻车现场2.1 月度报表查询DATE_FORMAT 让百万表全表扫我曾经接手过一个订单查询接口表order_info有 800 多万行核心查询是SELECT id, order_no, amount FROM order_info WHERE DATE_FORMAT(create_time, %Y-%m) 2024-01 ORDER BY create_time DESC LIMIT 20;表上有idx_create_time按理说按时间排序加过滤很轻松。但EXPLAIN的结果吓我一跳typeALLkeyNULLrows872万额外信息里还有一个Using filesort。这就意味着数据库把整张表扫了一遍逐行算出格式化的月份再去做排序。慢查询日志里这个语句直接成了 TOP 1接口响应从几十毫秒涨到四五秒。问题根源不难定位就是DATE_FORMAT套在了索引列上。我当时的处理方式是先改成范围条件SELECT id, order_no, amount FROM order_info WHERE create_time 2024-01-01 00:00:00 AND create_time 2024-02-01 00:00:00 ORDER BY create_time DESC LIMIT 20;改造后执行计划变成了typerangekeyidx_create_time额外信息消失扫描行数骤降到一个月的量级。这个思路对日期类函数非常有效因为日期本质是连续的任何月份、年份查询都可以转换成一段半开区间。2.2 手机号查询一个引号引发的“隐式转换”第二个案例更加隐蔽。某用户表user_base里mobile是VARCHAR(11)类型也建了普通索引。某天有人写了一句SELECT id, name FROM user_base WHERE mobile 13800001111;注意右边的 13800001111 是数字没加引号。MySQL 在做类型比较时会把字符串列和数字常量一起转成浮点数再比较也就是说列上的字符串值被隐式调用了 CAST 函数相当于对mobile列做了一次加工。结果就是索引被废执行计划又变成全表扫描。这类隐式转换在EXPLAIN的输出里不太显眼你只看typeALL、keyNULL但字段上明明有索引就容易一脸懵。我的排查习惯是打完 EXPLAIN 之后紧跟一句SHOW WARNINGS;方便看到类似“Cannot use index due to type or collation conversion on column”的提示直接从根上定位原因。修复方式极其简单SQL 里把数字常量加上引号变成字符串SELECT id, name FROM user_base WHERE mobile 13800001111;执行计划立刻回到typeref索引生效。这里要特别提醒如果你写WHERE id 123其中id是 INT 列MySQL 反而会把你传入的字符串转成数字列本身不参与转换索引不受影响。隐式转换到底毁不毁索引关键要看是“列被变形”还是“常量被变形”。2.3 大小写与排序规则别小瞧 collation 的坑说到UPPER(name) ABC这类写法很多新人会问既然查询时要忽略大小写为什么不直接建一个函数索引答案是可以但首先要搞清楚你的字段排序规则。如果字段用的是utf8mb4_0900_ai_ciMySQL 8.0 的默认 collation这个_ci后缀就代表大小写不敏感。也就是说WHERE name abc已经能匹配ABC、Abc了你完全没必要再写UPPER(name)加了反而帮倒忙。但是如果字段被设置成utf8mb4_bin或者utf8mb4_0900_bin排序规则变成大小写敏感WHERE name abc就不会匹配ABC此时有人图省事写了WHERE UPPER(name) ABC索引被废全表扫描又来了。我印象里有一个项目就栽在这里。某业务要求“会员码不区分大小写但历史存量数据又有大小写混用”开发为了兼容所有查询都写成WHERE UPPER(vip_code) ...。结果随着数据上涨查询越来越慢。最后我给的表结构设计是加一个基于生成列的函数索引或者干脆把字段改造成统一的_bin排序规则再配合函数索引一起用。这类问题在排查时最难的不是写语句而是判断“到底需不需要这个函数”。很多情况下删掉函数本身就是最优解。3. MySQL 8.0 函数索引到底是救星还是坑如果你的查询场景确实无法改写比如必须用UPPER(name)的历史写法、必须按月分组、必须截取字符串那么 MySQL 8.0 的函数索引就是为这种情况准备的。8.0.13 版本之后MySQL 官方直接支持在表达式上建索引。3.1 函数索引的本质一张隐藏的生成列索引所谓函数索引官方文档叫 “functional key parts”底层思路其实是我们老早就用的“生成列 普通索引”的组合方案。数据库在表的每一行里根据表达式算出一个值把这个值单独存成一份索引数据。比如你建一个LOWER(name)的函数索引相当于表里藏了一个虚拟列LOWER(name)然后对这个列建 B 树索引。创建语法并不复杂注意函数表达式外面必须加一层括号CREATE INDEX idx_user_lower_name ON user_info ((LOWER(name)));加完这个索引之后原来的查询SELECT id FROM user_info WHERE LOWER(name) abc;执行计划就会从typeALL变成typeref走idx_user_lower_name。你如果执行SHOW CREATE TABLE user_info\G会看到表定义里多了一行类似KEY idx_user_lower_name ((lower(name)))的东西这就是函数索引。在 8.0 之前的老版本里你想达到同样效果要手动加生成列ALTER TABLE user_info ADD COLUMN name_lower VARCHAR(64) GENERATED ALWAYS AS (LOWER(name)) STORED, ADD INDEX idx_name_lower (name_lower);这段语句在 MySQL 5.7 里也支持。所以函数索引并不是什么黑科技它只是把“生成列索引”变成了一个直接的语法糖。3.2 使用函数索引前必须看清四个限制函数索引不是万能膏药踩坑之前先看这四个约束限制说明版本依赖函数索引语法需要 MySQL 8.0.13 及以上版本低于这个版本只能走生成列方案确定性要求表达式必须返回确定性结果不允许使用NOW()、UUID()、用户自定义函数等不支持范围查找函数索引只支持等值匹配不支持、、BETWEEN之类的范围扫描这是和普通索引最关键的差异索引类型限制函数索引用不了 FULLTEXT、SPATIAL 索引函数表达式也不能用于前缀索引的声明最后一点尤其值得记住。很多人以为函数索引建完之后WHERE LOWER(name) abc也能走索引实际上官方明确表示 functional key parts 不支持 range lookups建了也白建。所以我的建议是能用范围改写先用范围改写无法改写的等值场景才考虑函数索引。3.3 从 0 到 1 实测一轮函数索引为了验证“到底能不能救火”我经常在测试环境做这样的模拟。先建一张订单表CREATE TABLE order_info ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, order_no VARCHAR(32) NOT NULL, create_time DATETIME NOT NULL, amount DECIMAL(10,2) NOT NULL, PRIMARY KEY (id), KEY idx_create_time (create_time) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;再用一个存储过程灌入一百万行测试数据时间随机分布在一年里DELIMITER $$ CREATE PROCEDURE sp_gen_order(IN total INT) BEGIN DECLARE i INT DEFAULT 1; SET autocommit0; WHILE i total DO INSERT INTO order_info(order_no, create_time, amount) VALUES ( CONCAT(NO, LPAD(i, 8, 0)), TIMESTAMP(2024-01-01 00:00:00) INTERVAL (RAND() * 300) DAY, ROUND(RAND() * 1000, 2) ); SET i i 1; IF i % 5000 0 THEN COMMIT; END IF; END WHILE; COMMIT; END$$ DELIMITER ; CALL sp_gen_order(1000000);这时执行EXPLAIN SELECT id, order_no, create_time FROM order_info WHERE DATE_FORMAT(create_time, %Y-%m) 2024-01;结果大概率是typeALL、rows100万、ExtraUsing where。接下来建函数索引CREATE INDEX idx_order_month ON order_info ((DATE_FORMAT(create_time, %Y-%m)));再跑一遍同样的 EXPLAIN你会看到typerefkeyidx_order_month扫描行数从一百万掉到约八万一年十二个月每个月差不多八万行。这说明函数索引在等值场景下确实能显著减少扫描量。但别忘了ORDER BY create_time的排序问题——执行计划里很可能还是会带着Using filesort因为查询使用的索引是函数索引不是idx_create_time。如果你同时有排序需求更推荐的做法是把它改回范围查询或者额外建生成列来覆盖排序。3.4 函数索引也有隐性成本函数索引的代价经常被低估。第一每次写入INSERT / UPDATE时数据库都需要额外计算函数表达式并更新索引页写入性能肯定会受影响如果你在核心高频插入表上加了五六个函数索引写入时延的上升是非常明显的。第二索引空间占用是实打实的函数结果本身需要存储空间如果表达式结果很长比如CONCAT多个字段索引体积可能比原表还大。第三函数索引的选择性需要评估如果函数结果只有少数几个值比如按月份生成的值最多 12 种索引选择性差优化器很可能直接放弃索引改成全表扫特别是在数据量只有几万行的小表上这种表现尤其明显。我在生产环境给查询加函数索引之前一定会先用一条统计 SQL 看分布SELECT DATE_FORMAT(create_time, %Y-%m) AS ym, COUNT(*) AS cnt FROM order_info GROUP BY ym ORDER BY cnt DESC;如果结果集中某个值占比超过 20%这个函数索引走了也可能变成“扫半个索引”收益有限。此时不如老老实实做 SQL 改写或者把数据预聚合到单独的表里。4. 更稳妥的兜底方案SQL 改写与冗余派生列4.1 万能套路把“函数条件”换成“范围条件”对于日期和数值类函数最优雅、最稳定、最不消耗额外存储空间的方案永远是改写 SQL把函数条件转换成对原始列的范围比较。以下是我常用的几个改写模板原始写法推荐改写DATE_FORMAT(create_time, %Y-%m) 2024-01create_time 2024-01-01 AND create_time 2024-02-01YEAR(create_time) 2024create_time 2024-01-01 AND create_time 2025-01-01MONTH(create_time) 6create_time 2024-06-01 AND create_time 2024-07-01如果限定年份DATE(create_time) 2024-01-15create_time 2024-01-15 AND create_time 2024-01-16amount 1 100amount 99amount * 2 10amount 5注意符号方向不变写范围条件的核心是“半开区间”思维下限包含上限不包含。这个习惯还能顺带消灭 23:59:59.999 这类边界问题。要注意改写后 SQL 的语义必须仔细核对尤其遇到MONTH(create_time) 6这种跨年份函数时你必须在应用层明确“要查哪一年”否则改写会改变需求边界。我见过不少因为偷懒直接BETWEEN 2024-06-01 AND 2024-06-30的写法最后 6 月 30 号的数据被漏掉因为 6 月 30 号 23:59:59 之后还有一整天的时间戳。所以统一用 下边界 AND 上边界就不会错。4.2 生成列冗余让索引保存“加工后的数据”如果业务实在没法改 SQL比如大量历史代码写了UPPER(vip_code)你不可能把几十个接口全部改完那就可以采用生成列冗余方案。逻辑很简单把函数结果存成一列再对这一列加普通索引。这样查询条件里的函数可以等价替换为直接列比较索引也恢复正常。ALTER TABLE user_info ADD COLUMN vip_code_upper VARCHAR(64) GENERATED ALWAYS AS (UPPER(vip_code)) STORED, ADD INDEX idx_vip_code_upper (vip_code_upper);之后查询写成SELECT id FROM user_info WHERE vip_code_upper ABC123;注意两点生成列的数据由数据库自动维护你往表里显式插入vip_code_upper会直接报错生成列可以选择VIRTUAL或STORED其中VIRTUAL不占用额外的物理存储空间但每次读的时候需要计算实际使用中我给生成列建索引通常倾向于STORED因为写入时算一次查询时直接读对 InnoDB 的可用性更友好踩坑更少。这个方法不仅适用于等值函数还能处理排序需求。比如报表经常要按天分组SELECT DATE_FORMAT(create_time, %Y-%m-%d) AS day, SUM(amount) FROM order_info GROUP BY day;可以加一列order_day并建索引然后改写为GROUP BY order_day不仅过滤能走索引排序也能利用索引顺序直接绕开临时表和 filesort。这个思路在 BI 报表库里非常实用。4.3 排序与分组场景的处理函数索引解决不了排序问题的原因我在 3.3 节提过函数索引只保存函数结果并不会按原始列的物理顺序存储。如果业务频繁出现ORDER BY DATE(create_time)与其纠结索引不如直接把DATE(create_time)这个中间结果物化成列。另一种常见场景是GROUP BY UPPER(status)本质也是对加工后的值分组。此时你如果不想改表结构就只能接受临时表开销但要记住临时表意味着数据要落盘数据量大时再叠加文件排序性能会非常难看。我自己处理这类问题第一选择永远是“物化中间产物”。你永远不要跟 B 树的结构较劲而是顺着它的脾气把加工后的结果变成一个新的可排序、可哈希的列。衍生列、生成列、汇总表本质都是这一条路。4.4 编码规范与评审环节的兜底技术方案再好也扛不住同事持续写“毁索引”的 SQL。所以我在团队里推动了一套简单的上线自检机制所有新增或变更的核心 SQL必须附带EXPLAIN截图并且评审时重点看key字段是否为空、type是否为 ALL。更进一步可以在 CI 环节引入简单的文本扫描重点检查WHERE、ORDER BY、GROUP BY后面的条件里索引列是否被常见的DATE_FORMAT、UPPER、LOWER、SUBSTRING、YEAR、MONTH、LEFT等函数直接包裹。这种扫描不能替代人工评审但能拦住 80% 的低级问题。5. 排查实录与上线自检清单5.1 三步定位“索引杀手 SQL”排查这类性能问题我的思路长期稳定在三步。第一步打开慢查询日志先把问题 SQL 捞出来SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1;线上环境建议把阈值设得更低比如 0.5 秒日志目录留意别把磁盘塞满排查完记得关掉或者改回原配置。第二步对嫌犯 SQL 执行EXPLAIN不要只看type要把key、rows、Extra三个字段一起看。如果keyNULL、rows很大、Extra里有Using where甚至Using filesort基本可以锁定是索引使用出了问题。第三步执行SHOW WARNINGS;它会直接告诉你 MySQL 在优化过程中遇到的转换问题比如隐式类型转换导致列被强制 CAST这是自动检查里最省事的一招。这三个步骤做完绝大多数“明明有索引却不走”的问题都能找到方向。剩下小概率是统计信息过旧、优化器误判这时候再考虑ANALYZE TABLE或者用FORCE INDEX做临时验证。5.2 常见问题速查表我把日常被问到最多的几个问题整理成一个速查表方便快速定位现象大概率原因处理建议建了函数索引EXPLAIN 还是不走版本低于 8.0.13表达式不一致选择性差条件含范围比较确认版本严格对照表达式验证数据分布函数索引在SHOW CREATE TABLE里看不到列函数索引不是真实列只是索引定义里的表达式正常现象不影响使用字符串列和数字常量比较导致全表扫列被隐式转为数字常量加引号保证类型一致LIKE %abc走不了索引前置通配符倒序冗余列、全文索引或改造搜索方案ORDER BY DATE(col)产生 filesort函数处理排序列生成列 索引或应用层排序WHERE YEAR(col) 2024用了函数索引但仍慢函数索引只支持等值分钟级别的范围可能还要靠普通索引改写为范围条件5.3 上线前自检清单给核心索引做变更之前建议按这份清单过一遍备份表结构记录变更前的EXPLAIN结果方便变更后对比。DDL 放低峰期执行。虽然 MySQL 8.0 的在线 DDL 对普通索引支持得不错但函数索引涉及表达式的计算表越大重建时间越长并且 DDL 期间仍然会占用额外 IO 和空间。如果建的是生成列 索引务必验证 INSERT / UPDATE 的延迟影响可以先在压测环境跑一轮写入压测再看平均时延是否在可接受范围。上线后观察慢查询日志和performance_schema里的语句统计确认扫描行数、执行次数都符合预期。如果优化器依然没有选择新索引先用ANALYZE TABLE更新统计信息然后再考虑FORCE INDEX但FORCE INDEX最好只作为临时手段长期依赖很容易在数据分布变化后出问题。5.4 一条重要的工作心得优化器是基于成本的它不会因为你“辛辛苦苦建了索引”就一定要用。函数索引创建之后如果数据分布、常量类型、表达式形态和预期有任何一点不一致它都可能弃用索引转而全表扫。所以我在项目里把“函数索引”当成“最后手段”而不是“第一手段”。第一手段永远是改写 SQL用原始列的范围条件压榨普通索引的潜力第二手段才是冗余生成列把加工后的值物化出来第三手段才轮到 8.0 函数索引。按照这个顺序能避免掉绝大多数性能返工。这篇内容聊到的坑都是我线上踩过、或看到同事踩过的。说点个人体会你很难完全阻止别人写出DATE_FORMAT套索引列的 SQL但你可以把排查流程、改写模板、生成列方案做成一套团队公约。毕竟索引不是越多越好函数索引更是如此——它像是在数据模型上额外开了一条侧路开得好能救命开多了侧路本身也会变成新的拥堵点。
