MySQL 切换字符集引发的思考（二）

2023-08-28
上海
本文字数：1745 字
阅读完需：约 6 分钟

背景

故事是从一个生产系统 SQL 变更开始的，我们需要将一张表的字符集从 utf8 变更为 utf8mb4，起初的想法是 utf8mb4 能存储更多类型如 emoji 表情类的数据，这个变更应该没什么大的影响。但仔细思考一番，再结合与团队成员讨论、网上查的资料，变更可能会带来如下影响

如果在 varchar 字段上有索引，utf8 可支持最大 255 长度，而 utf8mb4 只能支持到 191 长度，故 SQL 可能执行失败。还有另外一种说法是 SQL 执行不会失败，但最终创建成功的是前缀索引。
相同字符集做关联查询的性能优于不同字符集，故存在性能下降风险。
变更字符集会导致索引重建，故存在性能下降风险。

出于技术人的好奇，且网上资料质量良莠不齐，决定接下来对上述问题一一进行论证。

本次将对第二个问题做验证

验证环境

数据库：阿里云 RDS MySQL5.7，小版本 5.7.42-log

SQL 工具：DataGrip

验证过程

整体思路为创建两个表做关联，在一定数据量的情况下做 join，对比字符集相同和字符集不同时的性能。

同时在验证前也查了一些资料，有说字符集不同导致被驱动表无法使用索引进而导致查询性能下降的，对于这个 case 过程中也会去重点验证。

首先创建两个测试表

CREATE TABLE `t_user_test` (  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,  `name` varchar(50) DEFAULT NULL COMMENT '客户名字',  `user_id` varchar(50) DEFAULT NULL COMMENT '客户user_id',  PRIMARY KEY (`ID`),  KEY `idx_user_id` (`user_id`)) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4
CREATE TABLE `t_user_event_test` (  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,  `user_id` varchar(50) DEFAULT NULL COMMENT '客户user_id',  `event` varchar(64) DEFAULT NULL COMMENT '事件名称',  PRIMARY KEY (`id`),  KEY `idx_user_id` (`user_id`)) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4

复制代码

为测试效果明显往两张表导入了一定量的数据,t_user_test 表数据量为 20W+,t_user_event_test 表的数据量为 130W+。

同时为保证 join 的时候驱动表固定，这里选择了使用 straight_join 来指定不同的表作为驱动表。

执行多次如下 SQL 进行测试，两张表分别作为驱动表的情况响应时间相差不多，基本维持在 50ms 左右

SELECT u.name,ue.event FROM t_user_test u straight_join  t_user_event_test ue ON u.user_id = ue.user_id limit 100100 rows retrieved starting from 1 in 67 ms (execution: 51 ms, fetching: 16 ms)

SELECT u.name,ue.event FROM t_user_event_test ue straight_join t_user_test u  ON u.user_id = ue.user_id limit 100100 rows retrieved starting from 1 in 64 ms (execution: 55 ms, fetching: 9 ms)

复制代码

对上述 SQL 进行 expalin 可发现，两条 SQL 都使用到了被驱动表上的索引

接下来执行 SQL 将表 t_user_event_test 的字符集改为 utf8，并重复以上验证步骤

执行多次如下 SQL 进行测试，发现 t_user_event_test 表（改成 utf8 的表）作为被驱动表的时候，响应时间明显变成，为 2s+，t_user_test 作为被驱动表的情况则响应时间无明显变化

SELECT u.name,ue.event FROM t_user_test u straight_join  t_user_event_test ue ON u.user_id = ue.user_id limit 100100 rows retrieved starting from 1 in 2 s 307 ms (execution: 2 s 298 ms, fetching: 9 ms)

SELECT u.name,ue.event FROM t_user_event_test ue straight_join t_user_test u  ON u.user_id = ue.user_id limit 100100 rows retrieved starting from 1 in 61 ms (execution: 46 ms, fetching: 15 ms)

复制代码

对上述 SQL 进行 expalin 可发现，响应时间变长的没有使用到被驱动表的索引

结论

数据库：阿里云 RDS MySQL5.7，小版本 5.7.42-log

在当前验证环境下，utf8 和 utf8mb4 的表做关联，uft8 的表作为被驱动表的情况下性能会有所下降，下降的原因是没有使用到被驱动表上的索引，而如果 uft8 的表作为驱动表，则基本对性能没有影响。

待验证

uft8 的表作为被驱动表的情况下索引为什么无法使用？查询一些资料得出 utf8 编码的这张表 join 的字段在查询的时候转成了 utf8mb4,但索引仍然是 utf-8，所以无法使用索引，不过目前没有找到好的验证方法。

有说执行查询之后使用 show warnings 命令查看会有信息提示发生了隐式转换，在我的环境下并没有得到验证。

发布于: 刚刚阅读数: 8

原文链接:【http://xie.infoq.cn/article/e586b3c93c290f49dbfec51c7】。

石小天

关注

还未添加个人签名 2018-11-07 加入

还未添加个人简介

发布

暂无评论

创作场景

MySQL 切换字符集引发的思考（二）

背景

验证环境

验证过程

结论

待验证

石小天

评论