MySQL JSON字段的正确使用：虚拟列索引与查询性能
大约 7 分钟
MySQL JSON字段的正确使用：虚拟列索引与查询性能
适读人群：Java后端开发、DBA、对MySQL JSON功能感兴趣的工程师 | 阅读时长：约22分钟
开篇故事
2022年，我们的商品系统有个需求：商品的「扩展属性」因品类不同而不同（手机有内存、颜色，衣服有尺码、材质），不能固定为几列。
有人提议用VARCHAR存JSON字符串，有人提议用EAV模型（属性名/属性值表），还有人说用MongoDB。
最终我们选择了MySQL的原生JSON类型（5.7+），用虚拟列索引解决了查询性能问题。
但上线后发现，不懂JSON字段的使用姿势，同样会踩大坑。
一、MySQL JSON类型的存储原理
MySQL 5.7+原生支持JSON类型，不是简单地存字符串：
JSON类型的存储特点：
1. 数据以Binary JSON格式存储（不是文本JSON）
2. 写入时自动验证JSON格式（格式错误会报错）
3. 随机读取：不需要反序列化整个JSON，可以直接按路径读取特定字段
4. 支持路径表达式（JSON Path）：$.field_name, $.array[0]
存储大小：
VARCHAR(1000)存JSON字符串 vs JSON类型
两者存储的原始数据量类似，但JSON类型额外存储了结构信息（便于随机访问）
JSON类型通常比等价的VARCHAR略大，但查询某个字段时更快二、底层原理：虚拟列如何为JSON建索引
2.1 为什么JSON字段默认不能建索引
-- JSON字段本身不能直接建索引
ALTER TABLE products ADD INDEX idx_json (extra_attrs);
-- ERROR: JSON column 'extra_attrs' cannot be used in key specification
-- 但可以通过虚拟列间接建索引2.2 虚拟列（Generated Column）
-- 虚拟列：在表定义中声明，值由表达式计算得出
-- VIRTUAL：不存储，查询时实时计算
-- STORED：存储在磁盘（可以建索引，但占用额外空间）
ALTER TABLE products
ADD COLUMN brand_vc VARCHAR(100)
GENERATED ALWAYS AS (JSON_UNQUOTE(extra_attrs->'$.brand')) VIRTUAL;
-- 在虚拟列上建索引（虚拟列需要是STORED才能直接建索引，或用函数索引）
-- MySQL 5.7.x：VIRTUAL列不支持直接建索引，需要用STORED
-- MySQL 8.0+：支持函数索引，可以直接对表达式建索引
-- MySQL 8.0+ 函数索引（推荐）
ALTER TABLE products
ADD INDEX idx_brand ((CAST(extra_attrs->>'$.brand' AS CHAR(100))));三、完整解决方案与代码
3.1 商品扩展属性的JSON设计
CREATE TABLE products (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(200) NOT NULL,
category_id INT NOT NULL,
price DECIMAL(10,2) NOT NULL,
status TINYINT NOT NULL DEFAULT 1,
-- JSON字段存储扩展属性
extra_attrs JSON,
-- 虚拟列（MySQL 8.0+函数索引的替代方案）
brand VARCHAR(100) GENERATED ALWAYS AS
(JSON_UNQUOTE(extra_attrs->>'$.brand')) STORED,
memory_size INT GENERATED ALWAYS AS
(extra_attrs->>'$.memory') STORED,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
-- 在虚拟列上建索引
INDEX idx_brand (brand),
INDEX idx_category_brand (category_id, brand),
INDEX idx_memory (memory_size)
) ENGINE=InnoDB;
-- 插入商品数据
INSERT INTO products (name, category_id, price, extra_attrs) VALUES
('iPhone 14 Pro', 1, 7999.00,
'{"brand":"Apple","memory":256,"color":"深空黑","os":"iOS16"}'),
('小米13 Ultra', 1, 5999.00,
'{"brand":"Xiaomi","memory":512,"color":"陶瓷白","os":"MIUI14"}'),
('优衣库连衣裙', 2, 299.00,
'{"brand":"Uniqlo","size":"M","material":"棉","color":"蓝色"}');3.2 JSON查询的各种姿势
-- JSON路径查询（->是列操作符，->>提取不带引号的字符串）
SELECT id, name,
extra_attrs->'$.brand' AS brand_with_quotes, -- 带引号: "Apple"
extra_attrs->>'$.brand' AS brand_no_quotes, -- 不带引号: Apple
JSON_EXTRACT(extra_attrs, '$.memory') AS memory -- 等价于->
FROM products WHERE category_id = 1;
-- 用虚拟列索引查询（走索引，高效）
EXPLAIN SELECT * FROM products WHERE brand = 'Apple';
-- type=ref, key=idx_brand, rows=1
-- 对比：直接用JSON路径查询（不走索引，全表扫描）
EXPLAIN SELECT * FROM products WHERE extra_attrs->>'$.brand' = 'Apple';
-- type=ALL, rows=全表
-- 注意：在MySQL 8.0+创建函数索引后，这条查询也能走索引
-- JSON数组查询
-- extra_attrs: {"tags": ["手机", "5G", "旗舰"]}
SELECT * FROM products
WHERE JSON_CONTAINS(extra_attrs->'$.tags', '"5G"');
-- JSON范围查询（需要虚拟列+索引）
SELECT * FROM products WHERE memory_size >= 256;
-- 走idx_memory索引
-- JSON修改操作
UPDATE products
SET extra_attrs = JSON_SET(extra_attrs, '$.color', '星光白')
WHERE id = 1;
UPDATE products
SET extra_attrs = JSON_INSERT(extra_attrs, '$.storage', '256GB')
WHERE id = 1;
UPDATE products
SET extra_attrs = JSON_REMOVE(extra_attrs, '$.os')
WHERE id = 1;3.3 Java中操作MySQL JSON字段
// MyBatis的TypeHandler，自动处理JSON序列化/反序列化
@MappedTypes({Map.class})
@MappedJdbcTypes(JdbcType.OTHER)
public class JsonTypeHandler extends BaseTypeHandler<Map<String, Object>> {
private static final ObjectMapper MAPPER = new ObjectMapper();
@Override
public void setNonNullParameter(PreparedStatement ps, int i,
Map<String, Object> parameter,
JdbcType jdbcType) throws SQLException {
try {
ps.setString(i, MAPPER.writeValueAsString(parameter));
} catch (JsonProcessingException e) {
throw new SQLException("JSON序列化失败", e);
}
}
@Override
public Map<String, Object> getNullableResult(ResultSet rs, String columnName)
throws SQLException {
String json = rs.getString(columnName);
return parseJson(json);
}
@Override
public Map<String, Object> getNullableResult(ResultSet rs, int columnIndex)
throws SQLException {
return parseJson(rs.getString(columnIndex));
}
@Override
public Map<String, Object> getNullableResult(CallableStatement cs, int columnIndex)
throws SQLException {
return parseJson(cs.getString(columnIndex));
}
@SuppressWarnings("unchecked")
private Map<String, Object> parseJson(String json) {
if (json == null) return null;
try {
return MAPPER.readValue(json, Map.class);
} catch (JsonProcessingException e) {
throw new RuntimeException("JSON解析失败: " + json, e);
}
}
}
// 实体类
@Data
public class Product {
private Long id;
private String name;
private Integer categoryId;
private BigDecimal price;
@TableField(typeHandler = JsonTypeHandler.class) // MyBatis-Plus注解
private Map<String, Object> extraAttrs;
// 便捷方法
public String getBrand() {
return extraAttrs != null ? (String) extraAttrs.get("brand") : null;
}
public Integer getMemory() {
return extraAttrs != null ? (Integer) extraAttrs.get("memory") : null;
}
}
// Mapper XML
// <resultMap>
// <result property="extraAttrs" column="extra_attrs"
// typeHandler="com.example.handler.JsonTypeHandler"/>
// </resultMap>3.4 MySQL 8.0的JSON_TABLE函数（JSON转关系表）
-- JSON_TABLE：将JSON数组转换为关系表，可以JOIN
SELECT p.id, p.name, jt.tag
FROM products p
JOIN JSON_TABLE(
p.extra_attrs->'$.tags',
'$[*]' COLUMNS (
tag VARCHAR(50) PATH '$'
)
) AS jt
WHERE p.category_id = 1;
-- 结果：
-- id=1, name='iPhone 14 Pro', tag='手机'
-- id=1, name='iPhone 14 Pro', tag='5G'
-- id=1, name='iPhone 14 Pro', tag='旗舰'四、踩坑实录
坑1：JSON字段过大，导致行溢出
-- MySQL InnoDB默认页大小16KB，一行数据不能超过65535字节（实际限制更复杂）
-- 如果JSON字段存了大量数据（如商品详细描述），会导致行溢出
-- 查看行溢出情况
SELECT TABLE_NAME, ROW_FORMAT, AVG_ROW_LENGTH, DATA_LENGTH
FROM information_schema.TABLES
WHERE TABLE_NAME = 'products';
-- 如果AVG_ROW_LENGTH异常大，可能是JSON字段太大解决方案：
-- 方案1：将大JSON字段移到独立的扩展表
CREATE TABLE product_detail (
product_id BIGINT PRIMARY KEY,
detail_json JSON
);
-- 主表产品只存核心字段，扩展详情按需查询
-- 方案2：限制JSON字段的大小
-- 在应用层检查JSON大小，超过阈值拒绝写入或截断坑2：JSON字段查询漏写->>'转义导致类型不匹配
-- 错误：-> 返回带引号的字符串 "Apple"
SELECT * FROM products WHERE extra_attrs->'$.brand' = 'Apple';
-- 结果：0行！因为 "Apple"（带双引号的字符串）!= Apple
-- 正确：->> 或 JSON_UNQUOTE 返回不带引号的字符串
SELECT * FROM products WHERE extra_attrs->>'$.brand' = 'Apple';
-- 结果：正确
-- 验证区别
SELECT
extra_attrs->'$.brand', -- 返回: "Apple"（带引号）
extra_attrs->>'$.brand', -- 返回: Apple（不带引号）
JSON_UNQUOTE(extra_attrs->'$.brand') -- 等价于->>
FROM products WHERE id = 1;坑3：虚拟列上的索引在某些情况下不被优化器使用
-- 虚拟列定义
ALTER TABLE products
ADD COLUMN brand VARCHAR(100) GENERATED ALWAYS AS
(JSON_UNQUOTE(extra_attrs->>'$.brand')) STORED;
ALTER TABLE products ADD INDEX idx_brand (brand);
-- 期望走idx_brand，但实际走全表扫描
EXPLAIN SELECT * FROM products
WHERE JSON_UNQUOTE(extra_attrs->>'$.brand') = 'Apple';
-- type=ALL，没有用虚拟列索引！原因：查询条件用了表达式JSON_UNQUOTE(extra_attrs->>'$.brand')，而不是直接用虚拟列brand。
解决方案：
-- 方案1：查询时直接用虚拟列名
EXPLAIN SELECT * FROM products WHERE brand = 'Apple';
-- type=ref，走索引
-- 方案2（MySQL 8.0+）：函数索引，让查询表达式也能走索引
ALTER TABLE products
ADD INDEX idx_brand_func ((CAST(extra_attrs->>'$.brand' AS CHAR(100))));
-- 此时表达式查询也能走这个索引
EXPLAIN SELECT * FROM products WHERE extra_attrs->>'$.brand' = 'Apple';
-- type=ref，走idx_brand_func五、总结与延伸
MySQL JSON字段的使用建议：
适合用JSON的场景：
- 扩展属性（不同类别的商品有不同属性）
- 配置信息（key-value结构但key不固定）
- 半结构化日志数据
- 原型开发阶段（先用JSON，后续根据需求抽取为独立列）
不适合用JSON的场景：
- 需要频繁查询的字段（应该是独立列）
- 需要多表JOIN的关联字段
- 大量数据（JSON字段太大影响行读取性能）
虚拟列索引是关键：如果JSON字段中的某个属性需要频繁查询，就创建对应的虚拟列和索引，千万别直接在JSON表达式上做等值比较（不走索引）。