记一次 MySQL JSON 字段索引失效,导致的百万级数据查询雪崩事故复盘

该文章已被构建成可运行项目,

摘要: 你是否在项目中用过 MySQL 的 JSON 字段?它灵活好用,但稍有不慎就可能埋下巨大的性能地雷。本文以“默语”博主的身份,复盘一次因 JSON 字段索引设计不当,导致线上百万级数据查询变慢,最终引发连锁反应导致服务雪崩的真实事故。文章将从现象、排查、原理到最终解决,手把手带你走一遍排查流程,深入浅出地讲解 MySQL JSON 索引的核心知识点,让你彻底搞懂这个“最熟悉的陌生人”,避免重蹈覆辙。

在这里插入图片描述

博主 默语带您 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: NULLkey: 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 索引是不能直接建立在 LONGTEXTBLOB 这种大型文本字段上的。就算想办法加上了,如果查询条件中的值(比如 '123456')和索引列的类型(LONGTEXT)不匹配,MySQL 为了进行正确的比较,往往会放弃使用索引,退化为全表扫描。

元凶二:字符集排序规则 (Collation) 冲突

这是个更隐蔽的杀手!JSON_UNQUOTE() 函数返回的字符串,其默认的字符集排序规则 (Collation) 是 utf8mb4_bin。而我们的表、甚至整个数据库,默认的排序规则可能是 utf8mb4_general_ciutf8mb4_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_idVIRTUAL 关键字表示这一列的值不会真实存储在磁盘上,而是在查询时动态计算,从而节省存储空间。

-- 增加一个虚拟生成列,用于提取 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 索引有了更深刻的理解:

  1. 敬畏之心:不要想当然地认为 ADD INDEX 就万事大吉。对于 JSON 这种特殊字段,必须了解其背后的索引机制。
  2. EXPLAIN 是你的挚友:对于任何涉及到大表的复杂查询,上线前务必使用 EXPLAIN 进行分析,确保索引被正确使用。
  3. JSON 索引三要素:创建 JSON 字段索引时,请牢记三要素:生成列 (或函数索引)显式CAST或指定列类型确保COLLATE一致
  4. 权衡利弊:虽然 JSON 提供了极大的灵活性,但如果某个 JSON 内的键需要被频繁、高性能地查询,或许将其设计为独立的常规列是更好的选择。

技术的道路上没有捷径,每一个坑都是一次成长的机会。希望我的这次“惨痛”经历,能成为你未来路上的垫脚石。

如果你在开发中也遇到过类似的数据库难题,或者对文章内容有任何疑问,都非常欢迎在评论区交流。当然,也可以加我的微信一起探讨技术,共同进步。

我的微信:Solitudemind


参考资料


如对本文内容有任何疑问、建议或意见,请联系作者,作者将尽力回复并改进📓;( 联系微信:Solitudemind )

点击下方名片,加入 IT 技术核心学习团队。一起探索科技的未来,共同成长。

为了让您拥有更好的交互体验,特将这行文字设置为可点击样式:点击下方名片,加入 IT
技术核心学习团队。一起探索科技的未来,共同成长。

该文章已被构建成可运行项目
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

默语∿

你的鼓励将是我创作的最大动力!

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值