《MySQL死鎖分析的兩個(gè)工具》中，舉了一個(gè)強(qiáng)制類型轉(zhuǎn)換導(dǎo)致死鎖的例子，有朋友詢問(wèn)是不是類型轉(zhuǎn)換都不能命中索引，花1分鐘細(xì)說(shuō)一下。

第一類：“列類型”與“where值類型”不符，不能命中索引，會(huì)導(dǎo)致全表掃描(full table scan)。

數(shù)據(jù)準(zhǔn)備：

create table t1 ( 
cell varchar(3) primary key 
)engine=innodb default charset=utf8; 
 
insert into t1(cell) values ('111'),('222'),('333'); 

• cell屬性為varchar類型;
• cell為主鍵，即聚簇索引(clustered index);
• t1插入3條測(cè)試數(shù)據(jù);

測(cè)試語(yǔ)句：

explain select * from t1 where cell=111; 
explain select * from t1 where cell='111'; 

• 第一個(gè)語(yǔ)句，where后的值類型是整數(shù)(與表cell類型不符);
• 第二個(gè)語(yǔ)句，where后的值類型是字符串(與表cell類型一致);

測(cè)試結(jié)果：

• 強(qiáng)制類型轉(zhuǎn)換，不能命中索引，需要全表掃描，即3條記錄;
• 類型相同，命中索引，1條記錄;

畫外音：關(guān)于explain，詳見《MySQL死鎖分析的兩個(gè)工具》。

第二類：相join的兩個(gè)表的字符編碼不同，不能命中索引，會(huì)導(dǎo)致笛卡爾積的循環(huán)計(jì)算(nested loop)。

數(shù)據(jù)準(zhǔn)備：

create table t2 ( 
cell varchar(3) primary key 
)engine=innodb default charset=latin1; 
 
insert into t2(cell) values ('111'),('222'),('333'),('444'),('555'),('666'); 
 
create table t3 ( 
cell varchar(3) primary key 
)engine=innodb default charset=utf8; 
 
insert into t3(cell) values ('111'),('222'),('333'),('444'),('555'),('666'); 

• t2和t1字符集不同，插入6條測(cè)試數(shù)據(jù);
• t3和t1字符集相同，也插入6條測(cè)試數(shù)據(jù);
• 除此之外，t1，t2，t3表結(jié)構(gòu)完全相同;

測(cè)試語(yǔ)句：

explain select * from t1,t2 where t1.cell=t2.cell; 
explain select * from t1,t3 where t1.cell=t3.cell; 

• 第一個(gè)join，連表t1和t2(字符集不同)，關(guān)聯(lián)屬性是cell;
• 第一個(gè)join，連表t1和t3(字符集相同)，關(guān)聯(lián)屬性是cell;

測(cè)試結(jié)果：

• t1和t2字符集不同，存儲(chǔ)空間不同;
• t1和t2相join時(shí)，遍歷了t1的所有記錄3條，t1的每一條記錄又要遍歷t2的所有記錄6條，實(shí)際進(jìn)行了笛卡爾積循環(huán)計(jì)算(nested loop)，索引無(wú)效;
• t1和t3相join時(shí)，遍歷了t1的所有記錄3條，t1的每一條記錄使用t3索引，即掃描1行記錄;

畫外音：圖片請(qǐng)放大。

總結(jié)

兩類隱蔽的不能利用索引的case：

• 表列類型，與where值類型，不一致;
• join表的字符編碼不同;

畫外音：本文測(cè)試于MySQL5.6。

【本文為51CTO專欄作者“58沈劍”原創(chuàng)稿件，轉(zhuǎn)載請(qǐng)聯(lián)系原作者】

戳這里，看該作者更多好文