MySQL 字符集不一致導致索引失效的一個真實案例

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






文章目錄

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

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

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

為了便于說明問題，我們對表結(jié)構(gòu)進行了簡化：

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 是用戶的推薦人。

實際查詢涉及了很多表，經(jīng)過簡化之后存在性能問題的語句如下：

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


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

了解問題之后，首先我讓他給我發(fā)來了 explain 執(zhí)行計劃：

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é)果可以看出，有一個全表掃描（type = ALL）的操作，顯然這是因為 recommender 字段上缺少索引。

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

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

create index idx_customer_cname on customer(recommender);



之后再次查看了執(zhí)行計劃，結(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)



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

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



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

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

接下來就是修改字段的字符集了：

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


然后，再次查看執(zhí)行計劃的結(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|



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

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