MySQL 的 JSON 字段怎么建索引？生成列和函数索引有什么区别？

知识点详解：把 JSON 路径变成可以比较的值

先明确我们要查什么

假设 profile 保存扩展资料，里面有 status、城市和偏好等信息。

这次查询条件是 status = active，所以索引对象不是整个 profile，而是其中 status 对应的值。

还要确定它到底是什么：字符串、数字，还是可能出现数组？如果数据类型混乱，索引定义和查询比较都可能出现意外。

应先约定资料结构与字段含义，再选表达式，不能把任意 JSON 结构全部索引化。JSON 类型说明

生成列让索引值的类型更清楚

下面仅是教学表结构：

CREATE TABLE user_profile (
    id BIGINT PRIMARY KEY,
    profile JSON NOT NULL,
    status VARCHAR(20)
        GENERATED ALWAYS AS (
            JSON_UNQUOTE(JSON_EXTRACT(profile, '$.status'))
        ) STORED,
    INDEX idx_status (status)
);

JSON_EXTRACT 取得路径值，JSON_UNQUOTE 用于去掉 JSON 字符串表示中的引号，再交给声明好的 VARCHAR 列。

查询可以直接使用：

SELECT id
FROM user_profile
WHERE status = 'active';

生成列表达了提取规则，索引则作用于相应值。STORED 不是生成列唯一形式，支持条件下也可以使用虚拟生成列并建立索引，需要按版本和实际需求选择。生成列索引说明

函数索引减少显式列，不消除表达式限制

MySQL 8.0.13 起支持函数索引。它可以基于表达式建立索引，在内部使用相应机制维护表达式结果，不必为每次使用都显式暴露一个业务列。

但 JSON 提取后的类型仍然重要。例如字符串表达式可能需要明确转换为可索引的长度和类型，不能拿一个没有适当界限的结果直接套用普通索引预期。

字符排序规则、转换长度和表达式限制都应核对。查询看起来语义相同，也不保证优化器一定匹配索引表达式。CREATE INDEX 文档

因此，当团队更重视字段可见性和查询易读性时，生成列可能更直观；函数索引则适合明确、受控的表达式需求。不能只按“少写一列”选择。

缺字段、JSON null 和数组需要单独测试

路径不存在、值是 JSON null、值是字符串 “null”，并不是同一种数据情况。类型转换还可能影响最终列值和比较结果。

应该实际放入这些边界数据，看生成列与查询结果是否符合预期，尤其不能只测试一条标准对象。

数组成员查询又是不同问题。MySQL 8.0.17 起支持多值索引，但有表达式和查询适用限制，不是给一个 JSON 标量索引就自动支持所有数组检索。

本文是 SQL 与数据库机制，SQL 不需要复制成 TypeScript 和 Python 两种“实现”。应用层可以使用不同语言调用，但索引规则由数据库执行。

面试官继续追问

函数索引一定比生成列更快吗？

不能这样判断。它们都维护表达式相关的索引，效果要看定义、数据和查询计划。语法简洁不等于查询性能更高。

查询写 JSON 提取表达式，优化器一定用 status 的索引吗？

不一定。表达式匹配、类型和排序规则有条件。使用明确的生成列可以降低误解，但最终仍需查看执行计划。

所有扩展字段都应该加索引吗？

不应该。索引增加存储与写入维护成本。优先根据真实查询与选择性设计，经常参与关键关系和约束的字段，也可能更适合普通列。

面试速记卡

• 索引对象：JSON 中明确的标量路径值，不是整份对象。
• 生成列：提取并声明类型，再在对应列上建立索引。
• 函数索引：基于表达式维护索引，仍受类型与表达式限制。
• 命中条件：查询表达式、长度、排序规则和实际计划都要核对。
• 边界测试：缺路径、JSON null、错误类型与数组成员分别验证。