《MySQL死鎖分析的兩個工具》中，舉了一個強制類型轉換導致死鎖的例子，有朋友詢問是不是類型轉換都不能命中索引，花1分鐘細說一下。

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

數據準備：

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條測試數據;

測試語句：

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

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

測試結果：

• 強制類型轉換，不能命中索引，需要全表掃描，即3條記錄;
• 類型相同，命中索引，1條記錄;

畫外音：關于explain，詳見《MySQL死鎖分析的兩個工具》。

第二類：相join的兩個表的字符編碼不同，不能命中索引，會導致笛卡爾積的循環計算(nested loop)。

數據準備：

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條測試數據;
• t3和t1字符集相同，也插入6條測試數據;
• 除此之外，t1，t2，t3表結構完全相同;

測試語句：

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

• 第一個join，連表t1和t2(字符集不同)，關聯屬性是cell;
• 第一個join，連表t1和t3(字符集相同)，關聯屬性是cell;

測試結果：

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

畫外音：圖片請放大。

總結

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

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

畫外音：本文測試于MySQL5.6。

【本文為51CTO專欄作者“58沈劍”原創稿件，轉載請聯系原作者】

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