MySQL 字符集不一致導(dǎo)致索引失效的一個(gè)真實(shí)案例

作者: 不剪發(fā)的Tony老師
畢業(yè)于北京航空航天大學(xué)，十多年數(shù)據(jù)庫(kù)管理與開發(fā)經(jīng)驗(yàn)，目前在一家全球性的金融公司從事數(shù)據(jù)庫(kù)架構(gòu)設(shè)計(jì)。CSDN學(xué)院簽約講師以及GitChat專欄作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net






文章目錄

        問(wèn)題描述
        問(wèn)題分析
        總結(jié)

大家好，我是只談技術(shù)不剪發(fā)的 Tony 老師。今天給大家分析一個(gè)由于 MySQL 字符集不一致導(dǎo)致索引失效的案例。
問(wèn)題描述

有個(gè)朋友給我發(fā)來(lái)一個(gè)問(wèn)題，說(shuō)是他們的系統(tǒng)有幾十萬(wàn)用戶，某個(gè)查詢需要 5 秒以上的時(shí)間才能返回，同時(shí)服務(wù)器 CPU 資源占用率將近 100%。這個(gè)對(duì)于用戶的線上操作影響非常大，那么我們就來(lái)看看如何分析和解決這個(gè)慢查詢問(wèn)題。

為了便于說(shuō)明問(wèn)題，我們對(duì)表結(jié)構(gòu)進(jìn)行了簡(jiǎn)化：

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 上有一個(gè)唯一索引；recommender 是用戶的推薦人。

實(shí)際查詢涉及了很多表，經(jīng)過(guò)簡(jiǎn)化之后存在性能問(wèn)題的語(yǔ)句如下：

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();


大意是查找通過(guò)某人推薦，在指定時(shí)間段內(nèi)注冊(cè)的用戶。
問(wèn)題分析

了解問(wèn)題之后，首先我讓他給我發(fā)來(lái)了 explain 執(zhí)行計(jì)劃：

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|



從結(jié)果可以看出，有一個(gè)全表掃描（type = ALL）的操作，顯然這是因?yàn)?recommender 字段上缺少索引。

    ??關(guān)于 MySQL 執(zhí)行計(jì)劃的詳細(xì)介紹，可以參考這篇文章。

所以，我們首先為 recommender 字段創(chuàng)建了一個(gè)索引：

create index idx_customer_cname on customer(recommender);



之后再次查看了執(zhí)行計(jì)劃，結(jié)果沒有任何變化，創(chuàng)建的索引沒有生效。然后我們使用了 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 <cache>((now() - interval 1 day)) and <cache>(now())) and (convert(`hrdb`.`c`.`recommender` using utf8mb4) = '張三'))
1 row in set (0.00 sec)



這里有一個(gè)問(wèn)題，就是存在字符集轉(zhuǎn)換：

convert(`hrdb`.`c`.`recommender` using utf8mb4) = '張三')



recommender 需要轉(zhuǎn)換為 utf8mb4 字符集，查看表結(jié)構(gòu)之后發(fā)現(xiàn)它的字符集是 utf8，和表中的其他字段字符集不一樣。原來(lái)他們是從之前的版本遷移過(guò)來(lái)的表結(jié)構(gòu)，不知怎么會(huì)導(dǎo)致遺留一個(gè)字段的字符集忘記了調(diào)整。

    ??MySQL 支持?jǐn)?shù)據(jù)庫(kù)、表以及字段級(jí)別的字符集（Character Set）和排序規(guī)則（Collation）。不同字符集支持的字符種類和數(shù)量不同，例如 ASCII 字符集只能存儲(chǔ)字母、數(shù)字和常見的符號(hào)，GB2312 和 GB18030 可以支持中文，Unicode 字符集能夠支持多國(guó)語(yǔ)言；排序規(guī)則定義了字符的排序順序，例如是否區(qū)分大小寫、是否區(qū)分重音、中文按照拼音還是偏旁進(jìn)行排序等。

接下來(lái)就是修改字段的字符集了：

alter table customer modify column recommender varchar(50) character set utf8mb4;


然后，再次查看執(zhí)行計(jì)劃的結(jié)果如下：

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|



在實(shí)際環(huán)境中優(yōu)化之后的查詢需要 0.1 秒左右，已經(jīng)完全可以滿足業(yè)務(wù)的需求了。
總結(jié)

本文分析了一個(gè)由于字符集不一致，導(dǎo)致增加了索引但是無(wú)法使用的案例。通過(guò)索引進(jìn)行查找時(shí)需要進(jìn)行數(shù)據(jù)的比較，字符集不一致時(shí)需要使用 convert 函數(shù)進(jìn)行轉(zhuǎn)換，從而導(dǎo)致索引失效。通常在遷移遺留系統(tǒng)時(shí)需要特別小心，對(duì)于 Unicode 推薦使用最新的 utf8mb4 字符集。