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

栏目: 数据库 · 发布时间: 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分钟大家有收获,有思考,求 帮转


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

查看所有标签

猜你喜欢:

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

ASP.NET 2.0入门经典

ASP.NET 2.0入门经典

哈特 / 清华大学出版社 / 2006-8 / 78.00元

该书是Wrox红皮书中的畅销品种, 从初版、1.0版、1.1版到目前的2.0版,已经3次升级,不仅内容更加完善、实用,还展现了.NET 2.0的最新技术和ASP.NET 2.0最新编程知识,是各种初学者学习ASP.NET的优秀教程,也是Web开发人员了解ASP.NET 2.0新技术的优秀参考书。该书与《ASP.NET 2.0高级编程(第4版)》及其早期版本,曾影响到无数中国Web程序员。一起来看看 《ASP.NET 2.0入门经典》 这本书的介绍吧!

SHA 加密
SHA 加密

SHA 加密工具

UNIX 时间戳转换
UNIX 时间戳转换

UNIX 时间戳转换

HEX CMYK 转换工具
HEX CMYK 转换工具

HEX CMYK 互转工具