两类非常隐蔽的全表扫描，不能命中索引（一分钟系列）

《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沈剑”原创稿件，转载请接洽原作者】

戳这里，看该作者更多好文

【编辑保举】

1. MySQL：硬盘在24 * 7事变中停工了，我该怎么办？
2. 内心没点B树，怎能吃透数据库索引底层道理？
3. 两个小器材，MySQL死锁说明，新手艺又Get！
4. 怎样停止回表查询？什么是索引包围？ | 1分钟MySQL优化系列

（编辑：湖南网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!