WHERE里包个函数索引就废?MySQL 8.0有解了,但三个坑得先搞清楚

举报
这个DBA有点耶 发表于 2026/11/11 15:56:43 2026/11/11
【摘要】 WHERE DATE(create_time) = '2026-09-01'导致索引失效,是MySQL的经典痛点。8.0.13引入的函数索引,把计算下推到索引层,让这类查询也能走索引。但函数索引不是免费的午餐:查询表达式必须与索引定义“完全一致”,字符集和类型任何一处不匹配都会让它形同虚设;写入时每次都要计算函数值并维护索引,写密集场景的吞吐代价不可忽略。本文拆解函数索引的生效机制、三个失效边界与

大家好,我是小耶,写功课只是为了我踩过的坑,你们别再踩了!

WHERE DATE(create_time) = '2026-09-01',create_time上明明有索引,EXPLAIN的key列却是NULL。

原因很直接:函数改变了字段的原始形态,破坏了B+树的有序性,优化器无法用原始索引去匹配计算后的值。MySQL 8.0.13之前,解法只有“虚拟列+普通索引”这种绕路方案;8.0.13之后,函数索引把这条路收进了语法层面。

但函数索引上线之后,很多团队发现:建了,查询还是不走;走了,写入TPS掉了。

今天把它的生效条件、失效边界和写入代价拆开讲清楚。

一、函数索引的底层:不是“给函数建索引”,是把计算前移

函数索引的本质,是在虚拟生成列上建索引。创建时,MySQL把“字段+函数”的计算结果算好,存进隐藏的虚拟列,再给这个虚拟列建B+树索引。查询时直接匹配预计算的结果,省去实时计算。

CREATE INDEX idx_date_create_time ON orders ((DATE(create_time)));

双括号是必须的。单括号会被解析为普通列索引,直接报错。

二、边界一:“完全一致”比想象中苛刻

函数索引最容易被忽略的边界是:WHERE中的表达式必须与索引定义在写法、类型、字符集上完全等价,优化器才会使用它。

几个典型的“写了但没命中”:

写法不一致。 索引建在CAST(ext->>'$.city' AS CHAR(32))上,查询写成ext->>'$.city' = '杭州',优化器判定为不同表达式,索引不认。

字符集或排序规则不一致。 索引按一种collation建,查询条件的collation不同,索引失效。

类型隐式转换。 JSON里的city有时是字符串'123',有时是数字123,->>返回的类型不同,索引按固定类型排序,转换后无法匹配。

优化器主动放弃。 表小或过滤性差时,全表扫描比走索引便宜,EXPLAIN会出现possible_keys有值但key为NULL。可以用FORCE INDEX验证:如果强行走索引确实更快,说明统计信息不准,ANALYZE TABLE再试。

一个务实的选择:如果查询表达式复杂到难以保证“完全一致”,用STORED生成列+普通索引替代函数索引,可读性和可控性都更好。

三、边界二:函数索引只解决“读”,代价在“写”

函数索引让读变快了,但索引的维护成本转嫁到了写入路径上。

每次INSERT或UPDATE,MySQL都需要计算函数表达式的结果,并更新虚拟列上的B+树索引。函数越复杂,写入路径的CPU开销越大。

一个可量化的场景:一张2000万行的订单表,DATE(create_time)的函数索引让WHERE DATE(create_time) = '2026-09-01'从全表扫描的约3秒降到约0.05秒。但在写密集的批处理时段,插入TPS下降了约15%,CPU的sys时间占比上升。收益和代价的平衡点,取决于表的读写比。读多写少的报表类表,函数索引的收益远大于代价;写密集的交易表,需要先做写入路径的压测再决定。

四、边界三:函数索引不是“万能索引”

三个它解决不了的场景:

LIKE前导通配符:WHERE name LIKE '%abc',函数索引帮不上忙,因为B+树无法优化前缀不确定的匹配。

多列不同函数的联合过滤:函数索引针对的是单个表达式。如果WHERE里同时对两列做了函数操作,且两个条件都是不等式,一个函数索引无法同时优化两者。

主键或唯一约束场景:函数索引可以建唯一索引,但它不能替代主键的聚簇索引角色。

五、小结

函数索引解决了“WHERE里用函数索引必失效”的经典问题,但它有三个清晰的边界:“完全一致”的匹配条件比想象中苛刻,写法、类型、字符集任何一处不一致都会让索引形同虚设;读的收益对应着写的代价,写密集表需要先量化TPS影响;LIKE前导通配符和多列不等式场景,函数索引无能为力。 建之前先确认这三件事,比建完再排查“为什么没用上”高效得多。

小耶在手,SQL 不愁

还有什么想了解的,欢迎留言!小耶一定知无不言言无不尽……我们下次见~

【声明】本内容来自华为云开发者社区博主,不代表华为云及华为云开发者社区的观点和立场。转载时必须标注文章的来源(华为云社区)、文章链接、文章作者等基本信息,否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0)

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。