MySQL字符集不一致導(dǎo)致索引失效的解決辦法
問(wèn)題描述
有個(gè)朋友給我發(fā)來(lái)一個(gè)問(wèn)題，說(shuō)是他們的系統(tǒng)有幾十萬(wàn)用戶(hù)，某個(gè)查詢(xún)需要 5 秒以上的時(shí)間才能返回，同時(shí)服務(wù)器 CPU 資源占用率將近 100%。這個(gè)對(duì)于用戶(hù)的線(xiàn)上操作影響非常大，那么我們就來(lái)看看如何分析和解決這個(gè)慢查詢(xún)問(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 是用戶(hù)表，其中 cid 是主鍵；cname 上有一個(gè)唯一索引；recommender 是用戶(hù)的推薦人。
實(shí)際查詢(xún)涉及了很多表，經(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è)的用戶(hù)。
問(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 字段上缺少索引。
所以，我們首先為 recommender 字段創(chuàng)建了一個(gè)索引：
create index idx_customer_cname on customer(recommender);
之后再次查看了執(zhí)行計(jì)劃，結(jié)果沒(méi)有任何變化，創(chuàng)建的索引沒(méi)有生效。然后我們使用了 show warnings 命令看看有沒(méi)有更多的信息：
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）。不同字符集支持的字符種類(lèi)和數(shù)量不同，例如 ASCII 字符集只能存儲(chǔ)字母、數(shù)字和常見(jiàn)的符號(hào)，GB2312 和 GB18030 可以支持中文，Unicode 字符集能夠支持多國(guó)語(yǔ)言；排序規(guī)則定義了字符的排序順序，例如是否區(qū)分大小寫(xiě)、是否區(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)化之后的查詢(xún)需要 0.1 秒左右，已經(jīng)完全可以滿(mǎn)足業(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 字符集。
以上就是MySQL字符集不一致導(dǎo)致索引失效的解決辦法的詳細(xì)內(nèi)容，更多關(guān)于MySQL字符集不一致的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！
相關(guān)文章
FROM_UNIXTIME 格式化MYSQL時(shí)間戳函數(shù)
對(duì)MYSQL沒(méi)有進(jìn)行過(guò)深入的研究，基礎(chǔ)知識(shí)匱乏，一遇到問(wèn)題只能手冊(cè)，看來(lái)要把MYSQL的學(xué)習(xí)安排進(jìn)時(shí)間表了。2011-04-04
MySQL命令提示符出現(xiàn)輸入錯(cuò)誤時(shí)如何修改前面的命令
本文主要介紹了MySQL命令提示符出現(xiàn)輸入錯(cuò)誤時(shí)如何修改前面的命令，文中通過(guò)示例代碼介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2021-10-10
線(xiàn)上MYSQL同步報(bào)錯(cuò)故障處理方法總結(jié)(必看篇)
下面小編就為大家?guī)?lái)一篇線(xiàn)上MYSQL同步報(bào)錯(cuò)故障處理方法總結(jié)(必看篇)。小編覺(jué)得挺不錯(cuò)的，現(xiàn)在就分享給大家，也給大家做個(gè)參考。一起跟隨小編過(guò)來(lái)看看吧2017-03-03
這篇文章主要為大家詳細(xì)介紹了Win中安裝mysql的詳細(xì)步驟，文中安裝步驟介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2019-10-10
MySQL快速插入大量數(shù)據(jù)的解決方案和代碼示例
在這篇博客中,我們將深入探討如何高效插入大量數(shù)據(jù)到MySQL數(shù)據(jù)庫(kù),無(wú)論你是數(shù)據(jù)庫(kù)新手還是經(jīng)驗(yàn)豐富的開(kāi)發(fā)者,這篇文章都將為你提供實(shí)用的解決方案和代碼示例,幫助你解決插入3萬(wàn)條數(shù)據(jù)需要20多秒的問(wèn)題,需要的朋友可以參考下2024-08-08
mysql給id設(shè)置默認(rèn)值為UUID的實(shí)現(xiàn)方法
由于mysql并不支持默認(rèn)值為函數(shù)類(lèi)型,給id設(shè)值有兩種方式,本文主要介紹了mysql給id設(shè)置默認(rèn)值為UUID的實(shí)現(xiàn)方法,具有一定的參考價(jià)值,感興趣的可以了解一下2023-08-08
這篇文章主要給大家介紹了關(guān)于Mysql中索引的相關(guān)資料，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家學(xué)習(xí)或者使用Mysql具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2019-05-05
這篇文章主要介紹了MySql中的常用參數(shù)查詢(xún)，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教2023-02-02
使用JDBC在MySQL數(shù)據(jù)庫(kù)中如何快速批量插入數(shù)據(jù)
這篇文章主要介紹了使用JDBC在MySQL數(shù)據(jù)庫(kù)中如何快速批量插入數(shù)據(jù)，可以有效的解決一次插入大數(shù)據(jù)的方法，2016-11-11
深入講解數(shù)據(jù)庫(kù)中Decimal類(lèi)型的使用以及實(shí)現(xiàn)方法
MySQL?DECIMAL數(shù)據(jù)類(lèi)型用于在數(shù)據(jù)庫(kù)中存儲(chǔ)精確的數(shù)值,我們經(jīng)常將DECIMAL數(shù)據(jù)類(lèi)型用于保留準(zhǔn)確精確度的列,例如會(huì)計(jì)系統(tǒng)中的貨幣數(shù)據(jù),下面這篇文章主要給大家介紹了關(guān)于數(shù)據(jù)庫(kù)中Decimal類(lèi)型的使用以及實(shí)現(xiàn)方法的相關(guān)資料,需要的朋友可以參考下2022-02-02