最佳实践 · 如何高效索引MySQL JSON字段
原创
概述
从MySQL 5.7.8版本开始，MySQL引入了对JSON字段的支持，这为处理半结构化数据提供了极大的灵活性。然而，MySQL原生并不支持直接对JSON对象中的字段进行索引。本文将介绍如何利用MySQL 5.7中的虚拟字段功能，对JSON字段中的数据进行高效索引，以提高查询性能。
假设我们有一个记录用户游戏数据的JSON对象，我们希望能够快速检索游戏玩家的相关信息。
{
"user_id": 101,
"username": "Alice",
"games": {
"Chess": {
"rating": 1500,
"wins": 30,
"losses": 5
},
"Poker": {
"games_played": 100,
"win_percentage": 60
},
"Tetris": {
"high_score": 85000
}
}
}表的基本结构
首先，创建一个包含JSON字段的表：
CREATE TABLE `user_games` (
`user_id` INT UNSIGNED NOT NULL,
`user_data` JSON NOT NULL,
PRIMARY KEY (`user_id`)
);在上面的表结构中，我们无法直接对JSON字段中的键进行索引。接下来，我们将演示如何使用虚拟字段对JSON字段进行索引。
增加虚拟字段
虚拟列语法如下
<type> [ GENERATED ALWAYS ] AS ( <expression> ) [ VIRTUAL|STORED ]
[ UNIQUE [KEY] ] [ [PRIMARY] KEY ] [ NOT NULL ] [ COMMENT <text> ]在MySQL 5.7中，支持两种类型的生成列（Generated Column）：虚拟生成列（Virtual Generated Column）和存储生成列（Stored Generated Column）。Virtual Column是默认选项，它只在数据字典中保存字段定义，而不将字段数据持久化到磁盘上。对于大多数应用场景，Virtual Column已足够使用，因为它节省了磁盘空间并且查询性能也很高。
虚拟生成列（Virtual Generated Column）
- 定义：虚拟生成列是一个只在数据字典中定义的列，它不会实际存储数据，而是在每次查询时动态计算。
- 优点：
- 节省磁盘空间：由于数据不被存储在磁盘上，仅在查询时计算，因此不会增加表的大小。
- 适用于不常用的计算字段：对于计算频率较低的字段，使用虚拟生成列可以减少对存储空间的需求。
- 缺点：
- 查询性能：每次查询时都需要动态计算字段值，这可能会影响查询性能，尤其是在数据量大的情况下。
CREATE TABLE example (
id INT PRIMARY KEY,
data JSON,
extracted_value VARCHAR(100) GENERATED ALWAYS AS (data->>'$.field') VIRTUAL
);存储生成列（Stored Generated Column）
- 定义：存储生成列不仅在数据字典中定义，还会将计算结果持久化到磁盘上。这意味着数据会被实际存储，并在插入或更新数据时计算。
- 优点：
- 查询性能：由于数据已被计算并存储，因此查询时不需要再次计算，提高了查询效率。
- 适用于经常查询的字段：对于需要频繁查询的计算字段，使用存储生成列可以显著提高查询性能。
- 缺点：
- 增加磁盘空间使用：由于数据被存储在磁盘上，表的大小会增加。
- 写入开销：每次插入或更新数据时，需要重新计算和存储字段值，可能会增加写入开销。
CREATE TABLE example (
id INT PRIMARY KEY,
data JSON,
extracted_value VARCHAR(100) GENERATED ALWAYS AS (data->>'$.field') STORED
);- 虚拟生成列适合那些计算开销较小且不需要频繁查询的字段，因为它不会占用额外的磁盘空间。
- 存储生成列适合需要高查询性能的场景，尤其是对查询性能要求较高的字段，因为计算结果被持久化到磁盘上。
以下是添加虚拟字段的建表语句：
CREATE TABLE `user_games` (
`user_id` INT UNSIGNED NOT NULL,
`user_data` JSON NOT NULL,
`username_virtual` VARCHAR(50) GENERATED ALWAYS AS (`user_data` ->> '$.username') VIRTUAL NOT NULL,
`chess_rating` INT GENERATED ALWAYS AS (`user_data` ->> '$.games.Chess.rating') VIRTUAL,
PRIMARY KEY (`user_id`)
);在这个例子中，我们定义了两个虚拟字段：username_virtual和chess_rating。username_virtual字段用于存储玩家的用户名，而chess_rating字段用于存储玩家在Chess游戏中的评分。
插入数据
INSERT INTO `user_games` (`user_id`, `user_data`) VALUES
(101, '{ "user_id": 101, "username": "Alice", "games": { "Chess": { "rating": 1500, "wins": 30, "losses": 5 }, "Poker": { "games_played": 100, "win_percentage": 60 }, "Tetris": { "high_score": 85000 } } }'),
(102, '{ "user_id": 102, "username": "Bob", "games": { "Chess": { "rating": 1600, "wins": 25, "losses": 10 }, "Poker": { "games_played": 80, "win_percentage": 55 }, "Tetris": { "high_score": 92000 } } }'),
(103, '{ "user_id": 103, "username": "Charlie", "games": { "Chess": { "rating": 1400, "wins": 20, "losses": 15 }, "Poker": { "games_played": 120, "win_percentage": 65 }, "Tetris": { "high_score": 80000 } } }');
查看数据
SELECT * FROM `user_games`;查看表的字段
SHOW COLUMNS FROM `user_games`;可以看到，虚拟字段username_virtual和chess_rating已成功创建，它们都在数据字典中进行存储，并未实际存储数据。
在虚拟字段上添加索引
为了提高查询性能，我们可以在虚拟字段上添加索引。首先查看当前查询的执行计划：
EXPLAIN SELECT * FROM `user_games` WHERE `username_virtual` = 'Alice';
添加索引
CREATE INDEX `username_idx` ON `user_games`(`username_virtual`);
CREATE INDEX `chess_rating_idx` ON `user_games`(`chess_rating`);
重新执行查询，将得到优化后的执行计划：
EXPLAIN SELECT * FROM `user_games` WHERE `username_virtual` = 'Alice';
EXPLAIN SELECT * FROM `user_games` WHERE `chess_rating` = 1500;
总结
在本文中，我们探讨了如何在MySQL 5.7中利用生成列来高效索引JSON字段。通过虚拟生成列和存储生成列两种方式，我们可以根据实际需求选择最适合的解决方案，平衡磁盘空间使用和查询性能。虚拟生成列在不增加存储空间的前提下，通过动态计算提升了数据存储的灵活性，而存储生成列则通过持久化计算结果显著提升了查询效率。
通过虚拟字段和索引的结合，可以显著提高对JSON字段内容的检索速度，并优化查询性能。虚拟字段不仅提供了对JSON数据的索引支持，还避免了对磁盘空间的额外消耗，是处理半结构化数据的有效工具。开发者可以更好地管理和优化JSON数据结构的查询与索引，充分发挥MySQL 5.7在现代应用中的强大能力。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
腾讯云开发者
