MySQL：JSON字段上建了函数索引，WHERE ext->>'$.city'='杭州'还是全表扫描，表达式不一致和字符集不匹配哪个更常见？

摘要与核心结论

在 MySQL 8.0.13 引入函数索引（Functional Index，官方称 functional key parts）之后，围绕 JSON 字段做等值检索的工程实践显著增加。但生产环境中最常见的现象是：索引确实建了，EXPLAIN 却依旧显示 type=ALL。本文的主线判断是：表达式不一致是更常见、也更隐蔽的第一类根因；字符集与排序规则不匹配是第二类根因，发生频率较低但排查成本更高，且往往与“隐式转换”纠缠在一起。

这一判断并非经验直觉，而是由优化器的匹配机制决定的：函数索引在数据字典中以隐藏虚拟列的形式存在，优化器只有在解析后的表达式树与索引定义结构等价时才会建立映射。任何语法糖差异、括号差异、常量折叠时机差异，都会让两棵树“看起来一样、实际不同”。字符集问题则发生在更下游——当索引列与比较常量分属不同 collation 时，优化器为避免语义偏差会主动放弃索引。本文按“先表达式、后字符集”的顺序给出可复现的诊断脚本与修复清单。

目录

一、问题定义：一条慢查询背后的三层语义

1.1 现象描述与最小复现

假设业务表结构如下，ext 为 JSON 列，存储商家扩展属性，其中 $.city 为城市名。开发者按官方文档建立了函数索引：

ALTER TABLE merchant
  ADD INDEX idx_city ((CAST(ext->>'$.city' AS CHAR(32))));

随后执行查询，期望命中索引，实际却得到全表扫描：

EXPLAIN SELECT id, name FROM merchant
WHERE ext->>'$.city' = '杭州';
-- type: ALL, key: NULL, rows: 约 120 万

这条 SQL 同时涉及三层语义：语法层（->> 是 JSON_UNQUOTE(JSON_EXTRACT(...)) 的简写）、类型层（JSON 提取结果默认是 utf8mb4 的字符串，但 CAST 的目标类型与长度必须与索引一致）、比较层（常量 '杭州' 的连接字符集与排序规则）。任何一层不吻合，优化器都可能放弃索引。

1.2 为什么“建了索引却不用”值得单独讨论

普通 B+ 树索引的匹配规则相对直观：列名对上、前缀对上、最左前缀满足即可。函数索引则不同，它本质上是在隐藏虚拟列上建索引，而虚拟列的表达式由 DDL 定义，查询侧必须提供“可证明等价”的表达式。MySQL 优化器在 JOIN::optimize 阶段进行条件重写与索引选择，其中 Item_func 的等价判定依赖 eq() 系列方法，比较的是表达式树结构而非字符串文本。本文评述：这意味着“人眼看起来一样”与“优化器认为一样”之间存在系统性落差，而这个落差正是线上故障的高发区。

从工程视角看，函数索引的失效是“静默”的：DDL 成功、无告警、无错误码，只有执行计划悄悄退化为全表扫描。相比语法错误，这类问题的平均发现时间（MTTD）更长，往往在数据量增长到阈值后才被慢查询日志捕获。因此有必要把“表达式一致性”当作一项可验证的工程契约来管理，而不是依赖开发者记忆。

二、原理层：函数索引如何被优化器匹配

2.1 隐藏虚拟列与数据字典

MySQL 8.0 的函数索引在 information_schema.INNODB_COLUMNS 与内部数据字典中表现为一个不可见的虚拟生成列（virtual generated column），其 HIDDEN 属性为 SE（system-generated）。索引建在该虚拟列上，虚拟列的值在读取时按表达式即时计算。可通过如下语句观察：

SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_SET_NAME,
       COLLATION_NAME, EXTRA
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA='biz' AND TABLE_NAME='merchant'
  AND COLUMN_NAME LIKE '%city%';
-- 可见隐藏列，EXTRA 含 'VIRTUAL GENERATED'

关键在于：该虚拟列的数据类型、字符集、排序规则、长度都由 DDL 中的表达式决定。若 DDL 写的是 CAST(... AS CHAR(32))，则虚拟列为 char(32)；若查询侧只写 ext->>'$.city' 而不带 CAST，则表达式树中缺少 Item_func_cast 节点，等价判定失败。

2.2 等价判定的三个层次

笔者将优化器的匹配过程归纳为三个递进层次，任一层次失败即导致索引不可用：

本文评述：三层判定中，结构等价失败最容易被忽视，因为它不产生任何 warning；类型等价失败有时会在 SHOW WARNINGS 中留下“隐式转换”痕迹；比较等价失败则与 collation 强相关，需要结合 SHOW CREATE TABLE 与 @@character_set_connection 联合判断。

2.3 优化器追踪：让判定过程可见

MySQL 8.0.18 起提供 optimizer_trace，可观察条件重写与索引候选。更直接的方式是使用 8.0.16 引入的 EXPLAIN FORMAT=TREE 与 EXPLAIN ANALYZE：

SET optimizer_trace='enabled=on';
SELECT id FROM merchant WHERE ext->>'$.city'='杭州';
SELECT TRACE FROM information_schema.OPTIMIZER_TRACE\G
SET optimizer_trace='enabled=off';

在 trace 的 considered_access_paths 段落中，若看到 "usable": false 且 "cause": "not_equivalent" 一类描述，基本可锁定为表达式不一致；若出现 "cause": "collation_mismatch" 或与转换相关的条目，则指向字符集问题。需要说明的是，trace 输出字段随小版本演进，具体键名以实际版本为准，本文给出的是判别思路而非固定字符串。

三、根因一：表达式不一致的七种典型形态

3.1 形态一：CAST 缺失或长度不同

最常见的一类。索引定义为 CAST(... AS CHAR(32))，查询写 ext->>'$.city'，缺少 CAST 节点。或者索引写 CHAR(32)，查询写 CHAR(64)，长度不同同样不等价。修复方式是让 DDL 与 SQL 共用同一份表达式模板，最好通过视图或应用层常量固化。

3.2 形态二：JSON_EXTRACT 与 ->> 混用

->> 在解析阶段会被展开为 JSON_UNQUOTE(JSON_EXTRACT(doc, path))。理论上与显式写法等价，但若查询侧写成 JSON_UNQUOTE(JSON_EXTRACT(ext,'$.city')) 而索引侧写 ->>，多数版本可正确归一化；但若路径字符串写法不同（如 '$.city' 与 '$."city"'），常量节点值不同，等价判定失败。本文评述：路径字符串应视为“表达式的一部分”，必须逐字符一致，不能依赖语义等价。

3.3 形态三：常量折叠与函数包裹

查询侧若写成 TRIM(ext->>'$.city') = '杭州'，或 LOWER(ext->>'$.city') = 'hangzhou'，外层函数使表达式树多出节点，与索引定义不再同构。这类写法在业务代码中很常见，尤其是从其他数据库迁移过来、习惯做防御性清洗的团队。

3.4 形态四：括号与运算优先级差异

索引定义 ((CAST(ext->>'$.city' AS CHAR(32)))) 与查询 CAST((ext->>'$.city') AS CHAR(32)) 在语义上一致，解析树通常也能归一化。但涉及 COALESCE、IFNULL 嵌套时，参数顺序与括号层级会直接影响树结构。实践建议是：索引表达式尽量扁平，避免多层嵌套。

3.5 形态五：NULL 处理语义差异

当 JSON 路径不存在时，->> 返回 SQL NULL。若索引定义为 COALESCE(ext->>'$.city','')，而查询用 ext->>'$.city' = '杭州'，两者对 NULL 的处理不同，优化器无法证明等价。这类问题在数据质量参差、路径缺失率高的表中尤其突出。

3.6 形态六：大小写与排序规则函数

部分团队为支持不区分大小写检索，在索引中写入 LOWER(...)，但查询侧忘记加 LOWER，或反之。这属于典型的“契约漂移”：DDL 与 SQL 由不同人、在不同时间编写，缺乏统一模板。

3.7 形态七：版本升级导致的解析差异

MySQL 8.0 各小版本对 JSON 函数的解析与常量折叠有细微调整。跨版本迁移（如 8.0.20 到 8.0.33）后，原本命中的查询可能失效。本文评述：这提示我们把“函数索引可用性”纳入回归测试，而不是一次性配置。可参考 MySQL 官方文档中关于函数索引的限制说明（MySQL 8.0 Reference: CREATE INDEX）以及优化器相关章节。

实践提示：把索引表达式抽成应用层常量或数据库视图，是消除“形态漂移”的最低成本手段。视图写法见 CREATE VIEW 文档。

四、根因二：字符集与排序规则不匹配

4.1 JSON 提取结果的字符集来源

JSON 列在 MySQL 中固定以 utf8mb4 存储，JSON_UNQUOTE 返回的字符串继承 utf8mb4 及默认排序规则（通常为 utf8mb4_0900_ai_ci）。若索引定义中显式指定了 COLLATE utf8mb4_bin，而查询常量来自连接字符集为 utf8mb4_general_ci 的会话，比较层就可能出现不兼容。

4.2 隐式转换的触发条件

当比较双方 collation 不同且无法通过“可 coercibility”规则归一化时，MySQL 会进行隐式转换。转换方向取决于 coercibility 等级：常量通常低于列，因此常量会被转换到列的 collation。若转换涉及字符集变化（如 utf8mb4 到 gbk），索引列的有序性被破坏，优化器只能放弃索引。本文评述：字符集问题的隐蔽性在于它往往“时好时坏”——同一 SQL 在不同连接池、不同驱动版本下表现不同，因为会话字符集由连接参数决定。

4.3 诊断脚本

SHOW VARIABLES LIKE 'character_set_%';
SHOW VARIABLES LIKE 'collation_%';
SELECT TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA='biz' AND TABLE_NAME='merchant';

-- 检查常量在会话中的字符集
SELECT CHARSET('杭州'), COLLATION('杭州');

若 CHARSET('杭州') 返回 utf8mb4 而索引列 collation 为 utf8mb4_bin，则需进一步确认 collation_connection。修复方式包括：统一连接字符集、在 DDL 与 SQL 中显式声明 COLLATE，或在应用层确保常量以目标 collation 发送。字符集相关规则可参考 Connection Character Sets and Collations。

五、两类根因的发生率对比与判别流程

5.1 发生率的结构性分析

本文不虚构抽样统计，而是从机制出发做结构性推断，并标注为模拟推断。表达式不一致的触发面更宽：它涉及 CAST、路径字符串、外层函数、NULL 语义、括号层级等至少七种形态，每一种都可能由不同开发者、不同时间点引入。字符集不匹配的触发面相对窄：需要索引列与查询常量 collation 不同，且转换方向不利。因此，在“已建索引但未命中”的故障中，表达式不一致应作为首选假设。

5.2 五步判别流程

笔者建议按以下顺序排查，前两步即可覆盖多数场景：

1. 取 DDL 原文：SHOW CREATE TABLE，逐字符比对索引表达式与 SQL 表达式。
2. 看 EXPLAIN 的 key 与 filtered：确认是否真的未选索引，排除统计信息导致的误判。
3. 开 optimizer_trace：定位失败层次（结构/类型/比较）。
4. 检查会话字符集：character_set_connection、collation_connection。
5. 最小化复现：在测试库用完全相同 DDL 与 SQL 复现，再逐步简化表达式定位差异点。

拓展学习可参考 Percona 博客中关于函数索引与 JSON 检索的实践文章（Percona Blog），以及 MySQL 官方优化器追踪文档（Optimizer Tracing）。视频类资料可参考 MySQL 官方频道的索引优化讲解（MySQL YouTube 频道）。

六、工程实践：从建索引到上线的操作路径

6.1 表达式模板化

把索引表达式固化为团队规范中的“唯一写法”，例如统一使用 CAST(ext->>'$.city' AS CHAR(32))，并在代码评审清单中列为必检项。更进一步，可创建生成列（stored generated column）替代函数索引，让查询直接引用列名，从根本上消除表达式匹配问题。生成列与函数索引的取舍见 Generated Columns 文档。

6.2 上线前的验证清单

6.3 监控与告警

把慢查询日志与 performance_schema.events_statements_summary_by_digest 结合，对涉及 JSON 提取的 SQL 摘要做定期采样，发现 ROWS_EXAMINED/ROWS_SENT 比值异常时触发人工复核。本文评述：函数索引失效的监控不应依赖“是否报错”，而应依赖“扫描行数是否与预期量级匹配”。

七、边界条件、局限与前沿预判

7.1 函数索引的固有边界

函数索引不支持前缀索引以外的复杂组合，对 JSON 数组的多值检索（如 JSON_CONTAINS、JSON_OVERLAPS）支持有限；虚拟列的计算开销在写入与读取路径上均存在，需结合数据分布评估。本文评述：JSON 字段适合“读多写少、结构松散”的场景，若检索维度固定且频繁，正规化建模仍是更稳妥的选择。

7.2 版本演进与替代方案

MySQL 8.0 后续版本持续增强 JSON 与优化器能力；同时，MariaDB 在 JSON 处理上采用不同实现路径，PostgreSQL 的 GIN 索引对 JSONB 支持更成熟。跨库选型时，应把“表达式匹配的可预测性”纳入评估维度。可参考 PostgreSQL 官方文档中 JSONB 索引章节（PostgreSQL JSON Types）做横向对照。

7.3 前沿预判

笔者认为，未来数据库在“表达式索引”上的演进方向是语义等价而非结构等价的匹配，即通过规范化（normalization）把语法糖差异消解掉。这一方向在学术界已有关于查询重写与索引匹配的研究积累，工程落地仍需时间。短期内，团队应把“表达式契约”当作与表结构同等级别的资产来管理。

八、总结

回到标题的问题：表达式不一致与字符集不匹配，哪个更常见？基于机制分析，表达式不一致是更常见的第一类根因，它形态多、触发面宽、且完全静默；字符集不匹配发生频率较低，但排查链路更长、更依赖环境复现。工程上的应对策略是明确的：先固化表达式模板，再统一字符集与排序规则，最后用执行计划做上线门禁。三者结合，才能让函数索引从“建了”变成“真的用上了”。

主要参考文献

1. MySQL 8.0 Reference Manual, "CREATE INDEX Statement"（函数索引与功能键部件说明），Oracle，2024。
2. MySQL 8.0 Reference Manual, "Optimizer Tracing"，Oracle，2024。
3. MySQL 8.0 Reference Manual, "Connection Character Sets and Collations"，Oracle，2024。
4. MySQL 8.0 Reference Manual, "CREATE TABLE and Generated Columns"，Oracle，2024。
5. MySQL 8.0 Reference Manual, "JSON Functions"，Oracle，2024。
6. Percona Blog, "Functional Indexes in MySQL 8.0: Practical Notes"，Percona，2023。
7. PostgreSQL Documentation, "JSON Types and Indexing"，PostgreSQL Global Development Group，2024。
8. MySQL Server Blog, "Optimizer Improvements in MySQL 8.0"，Oracle，2023。