MySQL 字符集不一致導(dǎo)致索引失效的一個真實案例
文章目錄
問題描述
問題分析
總結(jié)
大家好，我是只談技術(shù)不剪發(fā)的 Tony 老師。今天給大家分析一個由于 MySQL 字符集不一致導(dǎo)致索引失效的案例。
問題描述
有個朋友給我發(fā)來一個問題，說是他們的系統(tǒng)有幾十萬用戶，某個查詢需要 5 秒以上的時間才能返回，同時服務(wù)器 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(), '李四');
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();
問題分析
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|————————————————
create index idx_customer_cname on customer(recommender);之后再次查看了執(zhí)行計劃，結(jié)果沒有任何變化，創(chuàng)建的索引沒有生效。然后我們使用了 show warnings 命令看看有沒有更多的信息：
show warnings\G*************************** 1. row ***************************Level: NoteCode: 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 <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)，不知怎么會導(dǎo)致遺留一個字段的字符集忘記了調(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è)務(wù)的需求了。
總結(jié)
本文分析了一個由于字符集不一致，導(dǎo)致增加了索引但是無法使用的案例。通過索引進行查找時需要進行數(shù)據(jù)的比較，字符集不一致時需要使用 convert 函數(shù)進行轉(zhuǎn)換，從而導(dǎo)致索引失效。通常在遷移遺留系統(tǒng)時需要特別小心，對于 Unicode 推薦使用最新的 utf8mb4 字符集。
————————————————
版權(quán)聲明：本文為CSDN博主「不剪發(fā)的Tony老師」的原創(chuàng)文章，遵循CC 4.0 BY-SA版權(quán)協(xié)議，轉(zhuǎn)載請附上原文出處鏈接及本聲明。
原文鏈接：https://blog.csdn.net/horses/article/details/107243447