JSON 字段按属性过滤很慢:生成列与函数索引怎么建才生效
可变属性塞进 JSON 列很省事,直到按里面的字段过滤开始全表扫。本文说明为什么 JSON 列用不上普通索引,给出生成列与函数索引的建法,以及类型、排序规则、缺失键三处失效细节。
把可变属性塞进 JSON 列很省事,直到某个按 JSON 内字段过滤的查询开始全表扫。JSON 本身建不了普通索引,正确做法是生成列或函数索引,而且写法上有几个必踩的坑。
为什么 JSON 列上的条件用不上索引
B+ 树索引要求列值有序可比。JSON 是文档,attrs->'$.channel' 这类表达式的结果没有对应的存储列,优化器没法拿它去走索引。
两条可行路径:把表达式固化成虚拟生成列再建普通索引;或者直接用函数索引(8.0.13 起支持,本质是同一件事的语法糖)。
生成列的写法
ALTER TABLE orders
ADD COLUMN channel VARCHAR(32)
GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(attrs, '$.channel'))) VIRTUAL,
ADD INDEX idx_orders_channel (channel);
VIRTUAL 不占存储,读取时按需计算,索引里存的是计算结果,日常过滤用这个就够。STORED 会真实写入磁盘,好处是可以被覆盖索引命中,代价是每次更新 JSON 都要重算。
生成列的表达式一旦建好就不能直接改,要调整只能删列重建;线上表要评估这个操作的代价,通常得走影子表或者低峰期变更。
函数索引的写法
CREATE INDEX idx_orders_channel
ON orders ((JSON_UNQUOTE(attrs->'$.channel')));
注意那两层括号——外层是索引定义,内层才是表达式,少一层直接语法错误。
函数索引命中要求「查询里的表达式和索引里的表达式一致」。->> 在语义上等价于 JSON_UNQUOTE(JSON_EXTRACT(...)),这两种写法可以互相命中;但一旦在外面套了 CAST、LOWER 或者 CONCAT,就是另一个表达式了,索引不会生效。
三个让索引白建的细节
第一是类型不一致。生成列是 VARCHAR,查询条件传的是数字 123,MySQL 会做隐式转换,索引失效。参数类型要和列类型对齐,WHERE channel = '123' 而不是 = 123。
第二是排序规则。JSON 提取出来的字符串默认带二进制排序规则,而表默认往往是 _ci 的,两者比较时会触发转换或额外排序。稳妥做法是在生成列上显式声明与查询一致的字符集和排序规则,建完后用 SHOW CREATE TABLE 核对一遍。
第三是缺失键。路径不存在时 JSON_EXTRACT 返回 NULL,索引里也存 NULL,这部分数据的选择性统计会失真,EXPLAIN 看着走了索引但回表比例很高。
验证不能只看 EXPLAIN 的 key 有没有值,那只能证明「可以考虑用」,要确认实际生效得看执行后的 Handler_read_next 增量,或者直接用 EXPLAIN ANALYZE。
适用边界与常见坑
- 高频过滤、参与排序或 join 的字段不该留在 JSON 里,应该拆成正式列。JSON 适合稀疏、低基数、结构经常变的属性。
- 生成列表达式不允许非确定性函数,
NOW()、RAND()、子查询都不行,写之前先确认表达式可确定性求值。 - 大 JSON 每次更新都是整列重写,
STORED生成列会跟着重算,写放大在高频更新表上很明显。 - 索引只解决「定位行」,如果查询还要取 JSON 里的其他字段,回表成本仍然存在,能接受的话考虑把热点字段一起做成生成列。
正在加载评论…