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

简介: 两类隐蔽的不能利用索引的case:(1)表列类型,与where值类型,不一致;(2)join表的字符编码不同;

《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类型一致);


测试结果
image.png

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

(2)类型相同,命中索引,1条记录;

第二类相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;


测试结果
image.png

(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。作业

create table t1 (
cell varchar(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分钟大家有收获,有思考,求帮转。

本文转自“架构师之路”公众号,58沈剑提供。

目录
相关文章
|
10月前
|
存储 缓存 安全
在 Service Worker 中配置缓存策略
Service Worker 是一种可编程的网络代理,允许开发者控制网页如何加载资源。通过在 Service Worker 中配置缓存策略,可以优化应用性能,减少加载时间,提升用户体验。此策略涉及缓存的存储、更新和检索机制。
|
存储 分布式计算 监控
揭秘阿里云EMR:如何巧妙降低你的数据湖成本,让大数据不再昂贵?
【8月更文挑战第26天】阿里云EMR是一种高效的大数据处理服务,助力企业优化数据湖的成本效益。它提供弹性计算资源,支持根据需求调整规模;兼容并优化了Hadoop、Spark等开源工具,提升性能同时降低资源消耗。借助DataWorks及Data Lake Formation等工具,EMR简化了数据湖构建与管理流程,实现了数据的统一化治理。此外,EMR还支持OSS、Table Store等多种存储选项,并配备监控优化工具,确保数据处理流程高效稳定。通过这些措施,EMR帮助企业显著降低了数据处理和存储成本。
440 3
|
消息中间件 安全 中间件
中间件中与中间件集成
【6月更文挑战第12天】
291 5
|
人工智能 安全 搜索推荐
1.8B参数,阿里云首个联合DNA、RNA、蛋白质的生物大模型,涵盖16.9W物种
【6月更文挑战第14天】阿里云发布首个集成DNA、RNA和蛋白质数据的生物大模型LucaOne,拥有1.8B参数,涉及16.9万物种。LucaOne通过few-shot learning技术和streamlined downstream architecture实现多生物语言统一处理,提升生物系统理解与分析能力。该模型将加速生物信息学研究,推动生物医学应用,但同时也引发生物数据安全、预测偏差及AI伦理法律等问题的讨论。[论文链接](https://www.biorxiv.org/content/10.1101/2024.05.10.592927v1)
804 3
|
监控 数据可视化 搜索推荐
ERP系统中的财务预测与预算编制解析
【7月更文挑战第25天】 ERP系统中的财务预测与预算编制解析
496 0
|
安全 算法 网络安全
网络安全与信息安全:漏洞、加密技术与安全意识
随着信息技术的快速发展,网络安全和信息安全问题日益凸显。本文将就网络安全漏洞、加密技术以及安全意识等方面进行深入探讨,旨在帮助读者对这些关键领域有更全面的了解,并提高对网络安全和信息安全的重视和意识。
77 0
|
人工智能 监控 数据可视化
【AI大模型应用开发】【LangSmith: 生产级AI应用维护平台】0. 一文全览Tracing功能,让你的程序运行过程一目了然
【AI大模型应用开发】【LangSmith: 生产级AI应用维护平台】0. 一文全览Tracing功能,让你的程序运行过程一目了然
679 0
三维海浪模型建模与matlab仿真,并在海浪中加入浮标
三维海浪模型建模与matlab仿真,并在海浪中加入浮标
|
SQL Java Spring
MyBatisPlus(八)自动填充字段(createTime/updateTime)
我们在项目中会用到createBy、createTime、updateBy、updateTime字段,来记录数据的插入和更新历史。若要实现这些字段的自动填充,MyBatisPlus需要增加一些配置,下面看具体内容吧。
1346 0
|
Linux
Linux 下 使用点阵在LCD上显示汉字,字符
这篇文章主要讲一下如何在 LCD 上使用点阵显示汉字,字符 ,修改颜色 及效果展示。其中包含了几个核心函数,我们需要了解。
317 0

热门文章

最新文章