MySQL 字符集不一致导致索引失效的真实案例-凡亿课堂

大家好，我是阿Q。今天给大家分析一个由于 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 c
join customer r on (c.recommender = r.cname )
where r.cid = 1
and c.register_time between now() - interval 1 day and now();

大意是查找通过某人推荐，在指定时间段内注册的用户。

问题分析

了解问题之后，首先我让他给我发来了 explain 执行计划：

explain
select c.*
from customer c
join 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: 1003
Message: /* 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 秒左右，已经完全可以满足业务的需求了。