龙空技术网

「转」两类非常隐蔽的全表扫描,不能命中索引(一分钟系列)

程序员界的彭于晏 1597

前言:

眼前你们对“数据库索引可以避免全表扫描”大致比较关切,看官们都想要了解一些“数据库索引可以避免全表扫描”的相关知识。那么小编同时在网络上搜集了一些有关“数据库索引可以避免全表扫描””的相关资讯,希望兄弟们能喜欢,小伙伴们一起来学习一下吧!

第一类:“列类型”与“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');

(1)cell属性为varchar类型;

(2)cell为主键,即聚簇索引(clustered index);

(3)t1插入3条测试数据;

测试语句:

explain select * from t1 where cell=111;

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

(1)第一个语句,where后的值类型是整数(与表cell类型不符);

(2)第二个语句,where后的值类型是字符串(与表cell类型一致);

测试结果:

(1)强制类型转换,不能命中索引,需要全表扫描,即3条记录;

(2)类型相同,命中索引,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');

(1)t2和t1字符集不同,插入6条测试数据;

(2)t3和t1字符集相同,也插入6条测试数据;

(3)除此之外,t1,t2,t3表结构完全相同;

测试语句:

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

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

(1)第一个join,连表t1和t2(字符集不同),关联属性是cell;

(2)第一个join,连表t1和t3(字符集相同),关联属性是cell;

测试结果:

(1)t1和t2字符集不同,存储空间不同;

(2)t1和t2相join时,遍历了t1的所有记录3条,t1的每一条记录又要遍历t2的所有记录6条,实际进行了笛卡尔积循环计算(nested loop),索引无效;

(3)t1和t3相join时,遍历了t1的所有记录3条,t1的每一条记录使用t3索引,即扫描1行记录;

画外音:图片请放大。

总结

两类隐蔽的不能利用索引的case:

(1)表列类型,与where值类型,不一致;

(2)join表的字符编码不同;

画外音:本文测试于MySQL5.6。

标签: #数据库索引可以避免全表扫描