維護(hù)一個交易系統(tǒng)，交易記錄表tradelog包含交易流水號(tradeid)、交易員id(operator)、交易時間(t_modified)等字段。

create table 'tradelog' ('id' int(11) not null,'tradeid' varchar(32) default null,'operator' int(11) default null,'t_modified' datetime default null,primary key ('id'),key 'tradeid' ('tradeid'),key 't_modified' ('t_modified') ) engine = InnoDB default charset = utf8mb4;

隱式類型轉(zhuǎn)換

select * from tradelog where tradeid = 110717;

交易編號tradeid字段上本身就有索引。但是explain這條語句后顯示，這條語句走的時全表掃描。
tradeid 的字段類型是varchar(32)，而輸入的參數(shù)卻是整型，所以需要做類型轉(zhuǎn)換

字符串與數(shù)字做比較的話，將字符串轉(zhuǎn)換成數(shù)字
所以之前的語句被轉(zhuǎn)換成：

select * from tradelog where CAST(tradeid as signed int) = 110717;

這個會觸發(fā)規(guī)則：對索引字段做函數(shù)操作，優(yōu)化器會放棄走樹搜索功能。
如果我們的語句是如下的：

select * from tradelog where id = "83126";

此時不會觸發(fā)規(guī)則。因?yàn)樽址蛿?shù)字比較，會將字符串轉(zhuǎn)換成數(shù)字。現(xiàn)在字符串在比較符右邊，只會把右邊轉(zhuǎn)換成數(shù)字，所以不會發(fā)生轉(zhuǎn)換，所以仍然走樹搜索。

隱式字符編碼轉(zhuǎn)換

假設(shè)系統(tǒng)里有表trade_detail，用于記錄交易細(xì)節(jié)。
我們往交易日志表tradelog和交易詳情表trade_detail這兩個表里插入一些數(shù)據(jù)。

mysql> CREATE TABLE `trade_detail` (`id` int(11) NOT NULL,`tradeid` varchar(32) DEFAULT NULL,`trade_step` int(11) DEFAULT NULL, /*操作步驟*/`step_info` varchar(32) DEFAULT NULL, /*步驟信息*/PRIMARY KEY (`id`),KEY `tradeid` (`tradeid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;insert into tradelog values(1, 'aaaaaaaa', 1000, now()); insert into tradelog values(2, 'aaaaaaab', 1000, now()); insert into tradelog values(3, 'aaaaaaac', 1000, now());insert into trade_detail values(1, 'aaaaaaaa', 1, 'add'); insert into trade_detail values(2, 'aaaaaaaa', 2, 'update'); insert into trade_detail values(3, 'aaaaaaaa', 3, 'commit'); insert into trade_detail values(4, 'aaaaaaab', 1, 'add'); insert into trade_detail values(5, 'aaaaaaab', 2, 'update'); insert into trade_detail values(6, 'aaaaaaab', 3, 'update again'); insert into trade_detail values(7, 'aaaaaaab', 4, 'commit'); insert into trade_detail values(8, 'aaaaaaac', 1, 'add'); insert into trade_detail values(9, 'aaaaaaac', 2, 'update'); insert into trade_detail values(10, 'aaaaaaac', 3, 'update again'); insert into trade_detail values(11, 'aaaaaaac', 4, 'commit');

調(diào)用該sql語句：

select d.* from tradelog l,trade_detail d where d.tradeid = l.tradeid and l.id = 2;

其執(zhí)行步驟為：
1、根據(jù)id在tradelog表里找到L2這一行
2、從L2中取出tradeid字段的值
3、根據(jù)tradeid值到trade_detail表中查找條件匹配的行。該過程是通過遍歷主鍵索引的方式，一個一個地判斷tradeid地值是否匹配。

這里的第3步不符合我們的預(yù)期，因?yàn)閠rade_detail里tradeid字段是有索引的，我們本來是希望通過使用tradeid索引能夠快速定位到等值的行。但是這里顯然不是這樣做的。
這是因?yàn)閮蓚€表的字符集不同，一個是utf8，一個是utf8mb4，所以做表連接查詢的時候用不上關(guān)聯(lián)字段的索引。
將執(zhí)行步驟的第三步單獨(dú)拎出來改成SQL語句：

select * from trade_detail where tradeid = $L2.tradeid.value;

$L2.tradeid.value的字符集是utf8mb4;
utf8mb4是utf8的超集，所以當(dāng)這兩個類型的字符串在做比較的時候，MySQL內(nèi)部的操作是先將utf8轉(zhuǎn)換成utf8mb4字符集，再做比較。
也相當(dāng)于：

select * from trade_detail where convert(tradeid using utf8mb4) = $L2.tradeid.value;

這就會觸發(fā)：對索引字段做函數(shù)操作，優(yōu)化器會放棄樹搜索功能。
這就是不同字符集連接查詢，關(guān)聯(lián)字段不走索引的原因。

總結(jié)

以上是生活随笔為你收集整理的《MySQL tips:隐式类型转换与隐式字符编码转换对查询效率的影响》的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。