黄东旭解析 TiDB 的核心优势
676
2023-05-11
两类非常隐蔽的全表扫描,不能命中索引(一分钟系列)
《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。
戳这里,看该作者更多好文
版权声明:本文内容由网络用户投稿,版权归原作者所有,本站不拥有其著作权,亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容,请联系我们jiasou666@gmail.com 处理,核实后本网站将在24小时内删除侵权内容。