摘要: 你是否在项目中用过 MySQL 的
JSON
字段?它灵活好用,但稍有不慎就可能埋下巨大的性能地雷。本文以“默语”博主的身份,复盘一次因JSON
字段索引设计不当,导致线上百万级数据查询变慢,最终引发连锁反应导致服务雪崩的真实事故。文章将从现象、排查、原理到最终解决,手把手带你走一遍排查流程,深入浅出地讲解 MySQLJSON
索引的核心知识点,让你彻底搞懂这个“最熟悉的陌生人”,避免重蹈覆辙。
博主 默语带您 Go to New World.
✍ 个人主页—— 默语 的博客👦🏻 优秀内容
《java 面试题大全》
《java 专栏》
《idea技术专区》
《spring boot 技术专区》
《MyBatis从入门到精通》
《23种设计模式》
《经典算法学习》
《spring 学习》
《MYSQL从入门到精通》数据库是开发者必会基础之一~
🍩惟余辈才疏学浅,临摹之作或有不妥之处,还请读者海涵指正。☕🍭
🪁 吾期望此文有资助于尔,即使粗浅难及深广,亦备添少许微薄之助。苟未尽善尽美,敬请批评指正,以资改进。!💻⌨
默语是谁?
大家好,我是 默语,别名默语博主,擅长的技术领域包括Java、运维和人工智能。我的技术背景扎实,涵盖了从后端开发到前端框架的各个方面,特别是在Java 性能优化、多线程编程、算法优化等领域有深厚造诣。
目前,我活跃在CSDN、掘金、阿里云和 51CTO等平台,全网拥有超过15万的粉丝,总阅读量超过1400 万。统一 IP 名称为 默语 或者 默语博主。我是 CSDN 博客专家、阿里云专家博主和掘金博客专家,曾获博客专家、优秀社区主理人等多项荣誉,并在 2023 年度博客之星评选中名列前 50。我还是 Java 高级工程师、自媒体博主,北京城市开发者社区的主理人,拥有丰富的项目开发经验和产品设计能力。希望通过我的分享,帮助大家更好地了解和使用各类技术产品,在不断的学习过程中,可以帮助到更多的人,结交更多的朋友.
我的博客内容涵盖广泛,主要分享技术教程、Bug解决方案、开发工具使用、前沿科技资讯、产品评测与使用体验。我特别关注云服务产品评测、AI 产品对比、开发板性能测试以及技术报告,同时也会提供产品优缺点分析、横向对比,并分享技术沙龙与行业大会的参会体验。我的目标是为读者提供有深度、有实用价值的技术洞察与分析。
默语:您的前沿技术领航员
👋 大家好,我是默语!
📱 全网搜索“默语”,即可纵览我在各大平台的知识足迹。📣 公众号“默语摸鱼”,每周定时推送干货满满的技术长文,从新兴框架的剖析到运维实战的复盘,助您技术进阶之路畅通无阻。
💬 微信端添加好友“Solitudemind”,与我直接交流,不管是项目瓶颈的求助,还是行业趋势的探讨,随时畅所欲言。
📅 最新动态:2025 年 6 月 16 日
快来加入技术社区,一起挖掘技术的无限潜能,携手迈向数字化新征程!
文章目录
文章目录
记一次 MySQL JSON 字段索引失效,导致的百万级数据查询雪崩事故复盘
大家好,我是默语,一个喜欢在代码世界里沉思的开发者。
今天想跟大家聊聊一个有点“惊心动魄”的线上事故。故事的主角,是我们既爱又恨的 MySQL JSON
字段。那天下午,我们的监控系统突然警报声四起,核心服务大量请求超时,接口响应慢如蜗牛,系统仿佛陷入了一场突如其来的“雪崩”。而这一切的罪魁祸首,竟然是一个被我们“自以为”加上了索引的 JSON
字段。
希望通过这次复盘,能帮助和曾经的我一样,对 JSON
索引一知半解的小白朋友们,彻底搞清楚其中的门道。
一、事故背景:风平浪静下的暗流
我们的系统里有一张业务日志表 operation_logs
,数据量早已突破千万。为了方便记录各种动态变化的业务参数,我们使用了 JSON
类型的字段 details
来存储操作详情。
一个典型的 details
字段内容如下:
{
"user_id": "123456",
"order_id": "order-xyz-789",
"ip_address": "192.168.1.1",
"action_type": "CREATE_ORDER"
}
随着业务发展,我们需要增加一个后台功能:根据 user_id
查询某个用户的所有操作记录。这看起来是个非常简单的需求,后端同学很快就写出了对应的查询语句:
SELECT * FROM operation_logs WHERE details->"$.user_id" = '123456';
考虑到 operation_logs
表的数据量很大,这位同学很有经验地“创建”了索引,然后就自信地发布上线了。
二、事故爆发:一场突如其来的“雪崩”
功能上线初期,一切正常。但随着使用该功能的用户增多,特别是在业务高峰期,我们开始收到大量的数据库慢查询告警。紧接着,应用服务器的 CPU 和内存占用率飙升,接口超时错误频发,最终导致部分依赖该核心服务的其他应用也出现连锁反应,一场查询“雪崩”就此爆发。
运维同学紧急介入,抓取了当时的慢查询日志,发现罪魁祸首正是那条根据 user_id
查询日志的 SQL。执行时间长达数十秒,而当时的 QPS (每秒查询率) 已经达到了一个危险的量级。
奇怪,不是已经加了索引吗?为什么还会这么慢?
三、排查之路:用 EXPLAIN
让真相浮出水面
带着疑问,我们开始了排查。在 MySQL 中,EXPLAIN
命令是我们的“照妖镜”,它可以分析出一条 SQL 语句的执行计划,告诉我们 MySQL 到底有没有使用索引。
我们对那条慢查询 SQL 执行了 EXPLAIN
:
EXPLAIN SELECT * FROM operation_logs WHERE details->"$.user_id" = '123456';
结果让我们大吃一惊:
+----+-------------+------------------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| 1 | SIMPLE | operation_logs | NULL | ALL | NULL | NULL | NULL | NULL | 15728640 | 10.00 | Using where |
+----+-------------+------------------+------------+------+---------------+------+---------+------+----------+----------+-------------+
关键信息解读:
type: ALL
: 这是最坏的情况,代表着全表扫描 (Full Table Scan)。MySQL 忽略了所有可能的索引,老老实实地一行一行扫描了整张operation_logs
表(当时已经有1500多万行!),然后再去匹配WHERE
条件。在千万级的数据表上进行全表扫描,性能灾难是必然的。possible_keys: NULL
和key: NULL
: 这两个字段更是直接告诉我们,MySQL 认为没有可用的索引,实际上也没有使用任何索引。
真相大白了:我们加的索引,根本没有生效!
四、深入根源:为什么 JSON 字段的索引会失效?
为什么索引会失效?这里必须先了解 MySQL 是如何为 JSON
字段建立索引的。
4.1 无法直接索引的 JSON
首先要明确一个核心知识点:MySQL 无法直接在 JSON
类型的列上创建常规索引。因为 JSON
内部结构复杂,无法像整数、字符串那样简单地进行比较和排序。
正确的做法是,利用“生成列 (Generated Column)”或“函数索引 (Functional Index)”技术,将 JSON
中需要索引的特定键值(必须是标量,即单个值,如字符串、数字)提取到一个“虚拟”的列上,然后为这个新列创建索引。
4.2 错误的索引创建方式(事故重现)
当时我们那位同学,很可能是这样“错误地”创建索引的:
-- 错误的尝试:直接在 JSON 表达式上创建索引,但忽略了类型和排序规则
ALTER TABLE operation_logs ADD INDEX idx_user_id( (details->"$.user_id") );
这条语句在某些 MySQL 版本中可能会报错,或者即使成功创建,也无法被查询正确使用。因为它忽略了两个致命的细节:数据类型和字符集排序规则。
4.3 索引失效的“两大元凶”
元凶一:数据类型不匹配
MySQL 的 ->>
操作符 (等价于 JSON_UNQUOTE(JSON_EXTRACT(...))
),它从 JSON
中提取值并去除引号,返回的数据类型是 LONGTEXT
。
而常规的 B-Tree 索引是不能直接建立在 LONGTEXT
或 BLOB
这种大型文本字段上的。就算想办法加上了,如果查询条件中的值(比如 '123456'
)和索引列的类型(LONGTEXT
)不匹配,MySQL 为了进行正确的比较,往往会放弃使用索引,退化为全表扫描。
元凶二:字符集排序规则 (Collation) 冲突
这是个更隐蔽的杀手!JSON_UNQUOTE()
函数返回的字符串,其默认的字符集排序规则 (Collation) 是 utf8mb4_bin
。而我们的表、甚至整个数据库,默认的排序规则可能是 utf8mb4_general_ci
或 utf8mb4_0900_ai_ci
(不区分大小写)。
当查询 WHERE details->"$.user_id" = '123456'
执行时,等号右边的字符串 '123456'
会使用表的默认排序规则 utf8mb4_general_ci
。而索引列的排序规则是 utf8mb4_bin
。两者不一致,MySQL 无法匹配,只能放弃索引。
五、力挽狂澜:正确的 JSON 索引姿势
搞清楚了原因,我们就能对症下药了。正确的做法是创建一个生成列,并明确指定它的数据类型和排序规则。
5.1 使用“生成列”创建索引 (推荐)
这是最通用和稳妥的方法,适用于 MySQL 5.7 及以上版本。
第一步:添加一个生成列 (Generated Column)
我们为 user_id
添加一个虚拟的生成列 v_user_id
。VIRTUAL
关键字表示这一列的值不会真实存储在磁盘上,而是在查询时动态计算,从而节省存储空间。
-- 增加一个虚拟生成列,用于提取 user_id
ALTER TABLE operation_logs
ADD COLUMN v_user_id VARCHAR(100) AS (details->"$.user_id") VIRTUAL;
-- 单行注释: 使用 VARCHAR(100) 明确指定了数据类型,避免了 LONGTEXT 问题
第二步:在生成列上创建索引
现在,v_user_id
就像一个普通的 VARCHAR
列,我们可以直接在它上面创建索引。
-- 在新的生成列上创建索引
CREATE INDEX idx_v_user_id ON operation_logs (v_user_id);
请注意: 这里我们没有显式指定 COLLATE
,因为生成列会自动继承表的默认排序规则,从而保证了查询时的一致性。如果表的默认排序规则不符合需求,可以在创建生成列时指定,例如 ... AS (details->"$.user_id") COLLATE utf8mb4_bin VIRTUAL;
。
5.2 修改查询语句
相应的,我们的查询语句也需要调整为对生成列进行查询:
SQL
-- 正确的查询语句
SELECT * FROM operation_logs WHERE v_user_id = '123456';
5.3 再次验证
我们将修复后的方案在预发环境执行 EXPLAIN
,看到了期待已久的结果:
+----+-------------+------------------+------------+------+-----------------+---------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------------+------------+------+-----------------+---------------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | operation_logs | NULL | ref | idx_v_user_id | idx_v_user_id | 403 | const | 15 | 100.00 | Using where |
+----+-------------+------------------+------------+------+-----------------+---------------+---------+-------+------+----------+-------------+
关键信息解读:
type: ref
: 太棒了!这表示 MySQL 使用了非唯一性索引进行查找。性能远高于ALL
。key: idx_v_user_id
: 明确显示出idx_v_user_id
这个索引被成功用上了!rows: 15
: 预估扫描行数只有15行,而不是之前的1500万行!
性能对比天差地别!我们将此修复方案紧急上线后,系统的慢查询告警立刻消失,接口响应恢复丝滑,一场由 JSON
索引引发的“雪崩”就此消弭。
六、总结与反思
这次事故给我们敲响了警钟,也让我们对 MySQL JSON
索引有了更深刻的理解:
- 敬畏之心:不要想当然地认为
ADD INDEX
就万事大吉。对于JSON
这种特殊字段,必须了解其背后的索引机制。 EXPLAIN
是你的挚友:对于任何涉及到大表的复杂查询,上线前务必使用EXPLAIN
进行分析,确保索引被正确使用。JSON
索引三要素:创建JSON
字段索引时,请牢记三要素:生成列 (或函数索引)、显式CAST
或指定列类型、确保COLLATE
一致。- 权衡利弊:虽然
JSON
提供了极大的灵活性,但如果某个JSON
内的键需要被频繁、高性能地查询,或许将其设计为独立的常规列是更好的选择。
技术的道路上没有捷径,每一个坑都是一次成长的机会。希望我的这次“惨痛”经历,能成为你未来路上的垫脚石。
如果你在开发中也遇到过类似的数据库难题,或者对文章内容有任何疑问,都非常欢迎在评论区交流。当然,也可以加我的微信一起探讨技术,共同进步。
我的微信:Solitudemind
参考资料
- MySQL 8.0 Reference Manual :: 13.5 The JSON Data Type
- MySQL 8.0 Reference Manual :: 15.1.20.8 CREATE TABLE and Generated Columns
- Oracle MySQL Blog: Indexing JSON Data in MySQL
如对本文内容有任何疑问、建议或意见,请联系作者,作者将尽力回复并改进📓;( 联系微信:Solitudemind )
点击下方名片,加入 IT 技术核心学习团队。一起探索科技的未来,共同成长。
为了让您拥有更好的交互体验,特将这行文字设置为可点击样式:点击下方名片,加入 IT
技术核心学习团队。一起探索科技的未来,共同成长。