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

栏目: 数据库 · 发布时间: 5年前

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');

(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的每一条记录 使用t2索引 ,即扫描1行记录;

画外音:图片请放大。

总结

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

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

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

画外音:本文测试于 MySQL 5.6。

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

架构师之路-分享技术思路

相关推荐:

MyISAM与InnoDB的索引差异 | 1分钟系列

缓冲池(buffer pool),这次彻底懂了!!!

写缓冲(change buffer),这次彻底懂了!!!

作业

create table t1 (

cell varc har (3) primary key

)engine=innodb default charset=

utf8 ;

insert into t1(cell) values ('111'),('222'),('333');

create table t2 (

cell char(3) primary key

)engine=innodb default charset=

utf8 ;

insert into t2(cell) values ('111'),('222'),('333'),('444'),('555'),('666');

create table t3 (

cell int primary key

)engine=innodb default charset=

utf8 ;

insert into t3(cell) values (111),(222),(333),(444),(555),(666);

(1)t1, t2, t3的 cell类型不同 :分别是varchar(3), char(3), int;

(2) 编码类型相同 ,均为utf8;

请问 :t1与t2,t1与t3的 join查询,能命中索引吗

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

动动手, “实际结果”与“你以为的”,未必相同

希望这1分钟大家有收获,有思考,求 帮转


以上就是本文的全部内容,希望本文的内容对大家的学习或者工作能带来一定的帮助,也希望大家多多支持 码农网

查看所有标签

猜你喜欢:

本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们

一网打尽

一网打尽

[美]布拉德·斯通 / 李晶、李静 / 中信出版社 / 2014-1-15 / 49.00元

亚马逊最早起步于通过邮购来经营图书业务。但贝佐斯却不满足于仅做一名书商,他希望缔造亚马逊万货商店的神话——能提供海量的货源,并以超低的价格提供最具吸引力的便捷服务。为了实现这一诺言,他发展了一种企业文化,这种文化蕴含着执着的雄心与难以破解 的秘诀。亚马逊的这 一文化现在依旧在发扬光大。 布拉德·斯通非常幸运地得到采访亚马逊的前任和现任高管、员工以及贝佐斯本人、家人的机会,使我们第一次有机会深......一起来看看 《一网打尽》 这本书的介绍吧!

HTML 压缩/解压工具
HTML 压缩/解压工具

在线压缩/解压 HTML 代码

URL 编码/解码
URL 编码/解码

URL 编码/解码

HEX HSV 转换工具
HEX HSV 转换工具

HEX HSV 互换工具