MySQL字符集不一致导致的性能问题分析与优化

1. 案例背景

春节长假期间，客户从自建MySQL迁移到云上RDS后，在执行高并发业务时出现大量锁等待问题。客户升级实例到最高规格后问题依旧存在。

2. 问题表现

• 并发执行特定存储过程时性能极差
• 500并发时执行时间超过8分钟（自建环境为秒级完成）
• 慢查询日志中批量出现该存储过程调用
• CPU使用率达到100%

3. 存储过程逻辑

存储过程主要包含两个SQL操作：

1. 根据meeting_id查询记录
2. 根据meeting_id和查询出的phone_id更新记录（更新为phone_id+3）

表数据量约40万条。

4. 初步诊断与优化

4.1 索引缺失问题

• 发现meeting_id和phone_id上没有索引
• 解决方案：在两个字段上创建索引，meeting_id设为主键
• 效果：200并发最高执行时间40秒左右（有所改善但不够）

4.2 并发线程参数调整

• 参数innodb_thread_concurrency默认值为0（无限制）
• 调整为32（与客户自建环境一致）
• 效果：500并发在3分钟内完成，不再hang住

5. 深入诊断

5.1 执行计划分析

通过EXPLAIN和trace发现：

• SQL没有走meeting_id主键索引
• 实际走了phone_id二级索引
• 第一个查询走了全索引扫描（扫描行数397399，接近全表）
• 第二个更新走了正常索引扫描（rows=2）

5.2 隐式类型转换问题

trace显示优化器对meeting_id做了隐式转换：

convert('meeting_id' using utf8mb4)

导致无法使用meeting_id索引。

6. 字符集不一致问题

6.1 字符集配置现状

• 表和列：utf8
• 数据库：utf8mb4
• server：utf8
• 客户端/连接/结果集：utf8mb4

6.2 问题原因

存储过程字符型参数默认使用数据库字符集（utf8mb4），与表字段字符集（utf8）不一致，导致比较时进行隐式转换。

7. 解决方案

7.1 修改表字符集

ALTER TABLE zm_meeting CONVERT TO CHARACTER SET utf8mb4;

效果：500并发秒级完成，执行计划正确使用主键索引。

7.2 替代方案：显式指定参数字符集

CREATE PROCEDURE `zm_sp_next_phone_id`(IN `p_meeting_id` VARCHAR(36) CHARACTER SET utf8)

8. 经验总结与最佳实践

1. 字符集一致性原则：

   • 确保server、database、table字符集一致
   • 推荐统一使用utf8mb4

2. 存储过程参数处理：

   • 注意参数默认使用数据库字符集
   • 可显式指定参数字符集与表字段一致

3. 索引使用检查：

   • 通过EXPLAIN和trace验证实际执行计划
   • 警惕隐式类型转换导致的索引失效

4. 高并发环境调优：

   • 合理设置innodb_thread_concurrency
   • 监控CPU使用率和上下文切换

5. 迁移注意事项：

   • 检查源和目标环境的配置差异
   • 特别注意字符集、排序规则等设置

9. 技术要点总结

1. MySQL优化器在字符集不一致时会进行隐式转换，导致索引失效
2. 存储过程参数字符集继承自数据库字符集，而非表字符集
3. 高并发场景下，适当的并发线程限制可以改善性能
4. 完整的诊断需要结合执行计划分析和trace工具
5. 预防此类问题的最佳方法是保持字符集配置的一致性