MySQL JSON字段隐式转换引发的索引失效问题排查实录

原创

远方诗人

发布于 2025-08-27 13:35:13

770

技术环境

MySQL版本：8.0.28
存储引擎：InnoDB
连接方式：JDBC + MyBatis
项目框架：Spring Boot 2.7.4

Bug现象

在用户行为分析模块中，我们使用JSON字段存储用户的行为事件数据。某个查询接口在生产环境中响应时间从平均50ms突然延长到5-8秒，且CPU使用率异常升高。

具体表现为：通过JSON字段中的事件类型（event_type）查询时，速度极慢，但其他条件查询正常。

排查步骤

1. 初步定位慢查询

首先开启MySQL慢查询日志，定位到问题SQL：

SELECT * FROM user_events 
WHERE app_id = 'app_001' 
  AND JSON_EXTRACT(event_data, '$.event_type') = 'page_view'
ORDER BY created_at DESC 
LIMIT 20;

2. 分析执行计划

使用EXPLAIN分析查询性能：

EXPLAIN SELECT * FROM user_events 
WHERE app_id = 'app_001' 
  AND JSON_EXTRACT(event_data, '$.event_type') = 'page_view'
ORDER BY created_at DESC 
LIMIT 20;

结果发现：

possible_keys显示有app_id索引
key实际使用的也是app_id索引
rows扫描行数达到28万行
Extra显示"Using where"

3. 检查索引情况

查看表结构发现，我们确实为event_data字段中的event_type创建了虚拟列和索引：

-- 表结构摘要
CREATE TABLE user_events (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    app_id VARCHAR(32) NOT NULL,
    event_data JSON NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    -- 虚拟列和索引
    event_type VARCHAR(32) GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(event_data, '$.event_type'))) VIRTUAL,
    KEY idx_app_id (app_id),
    KEY idx_event_type (event_type),
    KEY idx_created_at (created_at)
);

4. 深入分析索引失效原因

进一步检查发现，虽然创建了虚拟列和索引，但查询时仍然没有使用event_type索引。通过EXPLAIN FORMAT=JSON详细分析：

EXPLAIN FORMAT=JSON
SELECT * FROM user_events 
WHERE app_id = 'app_001' 
  AND event_type = 'page_view'
ORDER BY created_at DESC 
LIMIT 20;

输出结果显示优化器选择了app_id索引但评估需要扫描大量行。

根本原因

经过仔细排查，发现问题在于字符集和排序规则的隐式转换：

虚拟列event_type使用的字符集是utf8mb4，排序规则是utf8mb4_0900_ai_ci
查询条件中的'page_view'字符串被MySQL识别为utf8mb4字符集
但是JSON_EXTRACT函数返回的实际上是utf8mb4_bin排序规则的值
这种排序规则的不匹配导致索引无法使用

验证这一猜测：

-- 检查虚拟列的字符集和排序规则
SHOW FULL COLUMNS FROM user_events LIKE 'event_type';

-- 查看JSON_EXTRACT的实际返回类型
SELECT CHARSET(JSON_EXTRACT(event_data, '$.event_type')),
       COLLATION(JSON_EXTRACT(event_data, '$.event_type'))
FROM user_events LIMIT 1;

解决方案

方案一：修改查询方式（推荐）

直接使用虚拟列进行查询，避免使用JSON_EXTRACT函数：

-- 修改前的问题查询
SELECT * FROM user_events 
WHERE app_id = 'app_001' 
  AND JSON_EXTRACT(event_data, '$.event_type') = 'page_view'

-- 修改后的优化查询
SELECT * FROM user_events 
WHERE app_id = 'app_001' 
  AND event_type = 'page_view'

方案二：强制排序规则一致

如果必须使用JSON_EXTRACT，确保排序规则一致：

SELECT * FROM user_events 
WHERE app_id = 'app_001' 
  AND JSON_EXTRACT(event_data, '$.event_type') COLLATE utf8mb4_bin = 'page_view'

方案三：调整虚拟列定义

创建虚拟列时指定正确的排序规则：

-- 删除原有虚拟列
ALTER TABLE user_events DROP COLUMN event_type;

-- 重新创建虚拟列，指定排序规则
ALTER TABLE user_events 
ADD COLUMN event_type VARCHAR(32) 
GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(event_data, '$.event_type'))) COLLATE utf8mb4_bin VIRTUAL;

-- 重新创建索引
ALTER TABLE user_events ADD INDEX idx_event_type (event_type);

代码实现

在MyBatis中的具体实现：

<!-- 修改前的XML配置 -->
<select id="findByEventType" resultMap="userEventMap">
    SELECT * FROM user_events 
    WHERE app_id = #{appId}
      AND JSON_EXTRACT(event_data, '$.event_type') = #{eventType}
    ORDER BY created_at DESC 
    LIMIT #{limit}
</select>

<!-- 修改后的XML配置 -->
<select id="findByEventType" resultMap="userEventMap">
    SELECT * FROM user_events 
    WHERE app_id = #{appId}
      AND event_type = #{eventType}
    ORDER BY created_at DESC 
    LIMIT #{limit}
</select>

或者在Java实体类中直接使用虚拟列：

@Getter
@Setter
@TableName("user_events")
public class UserEvent {
    private Long id;
    private String appId;
    
    @TableField("event_data")
    private String eventData;
    
    // 虚拟列，不需要持久化到数据库
    @TableField(exist = false)
    private String eventType;
    
    private Date createdAt;
}

避坑总结

JSON字段索引的最佳实践：使用虚拟列+索引的方式，而不是直接对JSON字段创建函数索引
注意字符集和排序规则：MySQL中字符集和排序规则的不匹配是索引失效的常见原因
统一查询方式：尽量使用虚拟列进行查询，避免在WHERE条件中使用JSON函数
执行计划分析：定期使用EXPLAIN分析关键查询的执行计划，及时发现索引问题
测试环境复现：在生产环境遇到性能问题时，尝试在测试环境复现并分析

性能对比

优化前后的性能对比：

指标	优化前	优化后
查询时间	5-8秒	50-100ms
扫描行数	280,000+	200-500
CPU使用率	高	正常
索引使用情况	仅使用app_id索引	使用联合索引

这个案例提醒我们，即使创建了合适的索引，细微的字符集和排序规则差异也可能导致索引完全失效，需要在实际开发中格外注意。

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

腾讯技术创作特训营S15#debug日志

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

腾讯技术创作特训营S15#debug日志

登录后参与评论

0 条评论

热度