MySQL索引两类全表扫描隐患的排查与优化策略

在上篇文章里，我们举了一个因强制类型转换导致死锁的例子。有朋友问到，是不是所有类型转换都不能命中索引呢？花一分钟详细说说。

免费影视、动漫、音乐、游戏、小说资源长期稳定更新！ 👉 点此立即查看 👈

在《两个小公举，调试MySQL死锁必备！》一文中，我们曾提到过一个因强制类型转换引发死锁的案例。有朋友因此追问，是否所有类型转换都会导致索引失效？接下来，我们就花一分钟，把这个问题讲清楚。

第一类：“列类型”与“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字符串类型，同时作为主键，即聚簇索引（clustered index）。我们往t1表中插入了3条测试数据。

测试语句：

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

第一条语句中，where条件的值类型是整数（与表定义中cell的类型不符）；第二条语句，where条件的值类型是字符串（与cell类型一致）。

测试结果：

可以看到，当发生强制类型转换时，索引无法命中，查询需要扫描全表（共3条记录）；而当类型一致时，索引命中，仅扫描一条记录。

第二类：相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字符集不同，底层存储空间不同，当它们进行join时，执行计划显示需要先遍历t1的所有记录（3条），然后t1的每一条记录又要去遍历t2的所有记录（6条），实际上进行了笛卡尔积循环计算（nested loop），索引完全失效了。

而在t1与t3的join中，虽然也遍历了t1的所有记录（3条），但t1的每一条记录都可以利用t3的索引进行快速查找，即仅扫描一行记录，效率显著提升。

画外音：图片请放大查看。

总结

这里总结了两类容易被忽视、导致索引失效的情形：

表列类型，与where条件的值类型不一致；参与join的表，其字符编码不同。

知其然，更要知其所以然。

分析问题的思路，比结论本身更重要。