MySQL字符集不一致导致索引失效的案例分析
问题描述有个朋友给我发来一个问题，说是他们的系统有几十万用户，某个查询需要 5 秒以上的时间才能返回，同时服务器 cpu 资源占用率将近 100%。这个对于用户的线上操作影响非常大，那么我们就来看看如何分析和解决这个慢查询问题。
为了便于说明问题，我们对表结构进行了简化：
create table customer( cid int auto_increment primary key, cname varchar(50) not null, register_time datetime not null, recommender varchar(50) character set utf8) engine=innodb default charset=utf8mb4;create unique index uk_customer_cname on customer(cname);insert into customer(cname, register_time, recommender) values('张三', now(), '');insert into customer(cname, register_time, recommender) values('李四', now(), '张三'),('王五', now(), '李四');• customer 是用户表，其中 cid 是主键；• cname 上有一个唯一索引；• recommender 是用户的推荐人。实际查询涉及了很多表，经过简化之后存在性能问题的语句如下：
select c.*from customer cjoin customer r on (c.recommender = r.cname )where r.cid = 1and c.register_time between now() - interval 1 day and now();大意是查找通过某人推荐，在指定时间段内注册的用户。
问题分析了解问题之后，首先我让他给我发来了 explain 执行计划：
explainselect c.*from customer cjoin customer r on (c.recommender = r.cname )where r.cname = '张三'and c.register_time between now() - interval 1 day and now();id|select_type|table|partitions|type |possible_keys |key |key_len|ref |rows|filtered|extra |--|-----------|-----|----------|-----|-----------------|-----------------|-------|-----|----|--------|-----------| 1|simple |r | |const|uk_customer_cname|uk_customer_cname|202 |const| 1| 100.0|using index| 1|simple |c | |all | | | | | 3| 33.33|using where|从结果可以看出，有一个全表扫描（type = all）的操作，显然这是因为 recommender 字段上缺少索引。
所以，我们首先为 recommender 字段创建了一个索引：
create index idx_customer_cname on customer(recommender);之后再次查看了执行计划，结果没有任何变化，创建的索引没有生效。然后我们使用了 show warnings 命令看看有没有更多的信息：
show warnings\\g*************************** 1. row *************************** level: note code: 1003message: /* select#1 */ select `hrdb`.`c`.`cid` as `cid`,`hrdb`.`c`.`cname` as `cname`,`hrdb`.`c`.`register_time` as `register_time`,`hrdb`.`c`.`recommender` as `recommender` from `hrdb`.`customer` `c` join `hrdb`.`customer` `r` where ((`hrdb`.`c`.`register_time` between这里有一个问题，就是存在字符集转换：
convert(`hrdb`.`c`.`recommender` using utf8mb4) = '张三')recommender 需要转换为 utf8mb4 字符集，查看表结构之后发现它的字符集是 utf8，和表中的其他字段字符集不一样。原来他们是从之前的版本迁移过来的表结构，不知怎么会导致遗留一个字段的字符集忘记了调整。
mysql 支持数据库、表以及字段级别的字符集（character set）和排序规则（collation）。不同字符集支持的字符种类和数量不同，例如 ascii 字符集只能存储字母、数字和常见的符号，gb2312 和 gb18030 可以支持中文，unicode 字符集能够支持多国语言；排序规则定义了字符的排序顺序，例如是否区分大小写、是否区分重音、中文按照拼音还是偏旁进行排序等。
接下来就是修改字段的字符集了：
alter table customer modify column recommender varchar(50) character set utf8mb4;然后，再次查看执行计划的结果如下：
id|select_type|table|partitions|type |possible_keys |key |key_len|ref |rows|filtered|extra |--|-----------|-----|----------|-----|------------------|------------------|-------|-----|----|--------|-----------| 1|simple |r | |const|uk_customer_cname |uk_customer_cname |202 |const| 1| 100.0|using index| 1|simple |c | |ref |idx_customer_cname|idx_customer_cname|203 |const| 1| 33.33|using where|在实际环境中优化之后的查询需要 0.1 秒左右，已经完全可以满足业务的需求了。
总结本文分析了一个由于字符集不一致，导致增加了索引但是无法使用的案例。通过索引进行查找时需要进行数据的比较，字符集不一致时需要使用 convert 函数进行转换，从而导致索引失效。通常在迁移遗留系统时需要特别小心，对于 unicode 推荐使用最新的 utf8mb4 字符集。
HomePod ：用真实去营造出梦幻的场景
LDO线性稳压器的工作原理及选型参数
ReactOS开源系统最新进展公布，支持64位Win应用
一站式解决方案促进无人机产业高质量可持续发展
FPC片状处理是怎样的
MySQL字符集不一致导致索引失效的案例分析
如何使用MAX96705/MAX96706 GMSL设置SerDes反向控制通道
用PLC高速计数器和电压/频率传感器测量模拟电压信号的方法
智能扫地机器人哪个牌子好？懂行人给你建议，不是科沃斯也不是石头
MAX9217/MAX9218 视频链路中的音频数据传输
armv8/armv9中断系列详解-中断示例展示
品质工艺、旗舰设计：iQOO Z5火热预售中，仅1799元起
阳光照明拟建LED照明新项目
用于汽车LED的升压-降压LED驱动器拓扑结构以低输入和输出纹波工作
医用隔离电源系统在医院中的应用
配电箱的用途、应用及功能
小米6发布会前瞻：小米6倒计时3小时发布，最新最全米粉期待功能汇总
蓝牙耳机什么牌子好，口碑最好的蓝牙耳机
模拟矩阵在智能物流规划中的应用
GPT-4是这样搞电机的
为了便于说明问题，我们对表结构进行了简化：
create table customer( cid int auto_increment primary key, cname varchar(50) not null, register_time datetime not null, recommender varchar(50) character set utf8) engine=innodb default charset=utf8mb4;create unique index uk_customer_cname on customer(cname);insert into customer(cname, register_time, recommender) values('张三', now(), '');insert into customer(cname, register_time, recommender) values('李四', now(), '张三'),('王五', now(), '李四');• customer 是用户表，其中 cid 是主键；• cname 上有一个唯一索引；• recommender 是用户的推荐人。实际查询涉及了很多表，经过简化之后存在性能问题的语句如下：
select c.*from customer cjoin customer r on (c.recommender = r.cname )where r.cid = 1and c.register_time between now() - interval 1 day and now();大意是查找通过某人推荐，在指定时间段内注册的用户。
问题分析了解问题之后，首先我让他给我发来了 explain 执行计划：
explainselect c.*from customer cjoin customer r on (c.recommender = r.cname )where r.cname = '张三'and c.register_time between now() - interval 1 day and now();id|select_type|table|partitions|type |possible_keys |key |key_len|ref |rows|filtered|extra |--|-----------|-----|----------|-----|-----------------|-----------------|-------|-----|----|--------|-----------| 1|simple |r | |const|uk_customer_cname|uk_customer_cname|202 |const| 1| 100.0|using index| 1|simple |c | |all | | | | | 3| 33.33|using where|从结果可以看出，有一个全表扫描（type = all）的操作，显然这是因为 recommender 字段上缺少索引。
所以，我们首先为 recommender 字段创建了一个索引：
create index idx_customer_cname on customer(recommender);之后再次查看了执行计划，结果没有任何变化，创建的索引没有生效。然后我们使用了 show warnings 命令看看有没有更多的信息：
show warnings\\g*************************** 1. row *************************** level: note code: 1003message: /* select#1 */ select `hrdb`.`c`.`cid` as `cid`,`hrdb`.`c`.`cname` as `cname`,`hrdb`.`c`.`register_time` as `register_time`,`hrdb`.`c`.`recommender` as `recommender` from `hrdb`.`customer` `c` join `hrdb`.`customer` `r` where ((`hrdb`.`c`.`register_time` between这里有一个问题，就是存在字符集转换：
convert(`hrdb`.`c`.`recommender` using utf8mb4) = '张三')recommender 需要转换为 utf8mb4 字符集，查看表结构之后发现它的字符集是 utf8，和表中的其他字段字符集不一样。原来他们是从之前的版本迁移过来的表结构，不知怎么会导致遗留一个字段的字符集忘记了调整。
mysql 支持数据库、表以及字段级别的字符集（character set）和排序规则（collation）。不同字符集支持的字符种类和数量不同，例如 ascii 字符集只能存储字母、数字和常见的符号，gb2312 和 gb18030 可以支持中文，unicode 字符集能够支持多国语言；排序规则定义了字符的排序顺序，例如是否区分大小写、是否区分重音、中文按照拼音还是偏旁进行排序等。
接下来就是修改字段的字符集了：
alter table customer modify column recommender varchar(50) character set utf8mb4;然后，再次查看执行计划的结果如下：
id|select_type|table|partitions|type |possible_keys |key |key_len|ref |rows|filtered|extra |--|-----------|-----|----------|-----|------------------|------------------|-------|-----|----|--------|-----------| 1|simple |r | |const|uk_customer_cname |uk_customer_cname |202 |const| 1| 100.0|using index| 1|simple |c | |ref |idx_customer_cname|idx_customer_cname|203 |const| 1| 33.33|using where|在实际环境中优化之后的查询需要 0.1 秒左右，已经完全可以满足业务的需求了。
总结本文分析了一个由于字符集不一致，导致增加了索引但是无法使用的案例。通过索引进行查找时需要进行数据的比较，字符集不一致时需要使用 convert 函数进行转换，从而导致索引失效。通常在迁移遗留系统时需要特别小心，对于 unicode 推荐使用最新的 utf8mb4 字符集。
HomePod ：用真实去营造出梦幻的场景
LDO线性稳压器的工作原理及选型参数
ReactOS开源系统最新进展公布，支持64位Win应用
一站式解决方案促进无人机产业高质量可持续发展
FPC片状处理是怎样的
MySQL字符集不一致导致索引失效的案例分析
如何使用MAX96705/MAX96706 GMSL设置SerDes反向控制通道
用PLC高速计数器和电压/频率传感器测量模拟电压信号的方法
智能扫地机器人哪个牌子好？懂行人给你建议，不是科沃斯也不是石头
MAX9217/MAX9218 视频链路中的音频数据传输
armv8/armv9中断系列详解-中断示例展示
品质工艺、旗舰设计：iQOO Z5火热预售中，仅1799元起
阳光照明拟建LED照明新项目
用于汽车LED的升压-降压LED驱动器拓扑结构以低输入和输出纹波工作
医用隔离电源系统在医院中的应用
配电箱的用途、应用及功能
小米6发布会前瞻：小米6倒计时3小时发布，最新最全米粉期待功能汇总
蓝牙耳机什么牌子好，口碑最好的蓝牙耳机
模拟矩阵在智能物流规划中的应用
GPT-4是这样搞电机的