开发者社区> 程序员历小冰> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

MySQL复杂where条件分析

简介: 的加锁原理并具体分析了大部分的简单 SQL 语句,但是实际业务场景中 SQL 语句往往及其复杂,包含多个条件,此时就需要具体分析SQL 使用到的索引,并了解 where 条件的判断逻辑。 我们可以直接使用 explain 或者 optimizer_trace 来分析 SQL 语句执行使用了哪些索引,具体使用可以看本系列文章的前两篇文章。但是,今天我们讲一下具体 Where 语句的条件的拆分和使用,即复杂 Where 条件是如何生效的。
+关注继续查看

《MySQL 常见语句加锁分析》一文中,我们详细讲解了 SQL 语句的加锁原理并具体分析了大部分的简单 SQL 语句,但是实际业务场景中 SQL 语句往往及其复杂,包含多个条件,此时就需要具体分析SQL 使用到的索引,并了解 where 条件的判断逻辑。

我们可以直接使用 explain 或者 optimizer_trace 来分析 SQL 语句执行使用了哪些索引,具体使用可以看本系列文章的前两篇文章。但是,今天我们讲一下具体 Where 语句的条件的拆分和使用,即复杂 Where 条件是如何生效的。

用何登成大神的原话,就是

给定一条SQL,where条件中的每个子条件,在SQL执行的过程中有分别起着什么样的作用?

具体场景

我们使用下面这张 book 表作为实例,其中 id 为主键,ISBN(书号)为二级唯一索引,Author(作者)为二级非唯一索引,score(评分)无索引。

img

Index Key 和 Table Filter

基于上述表,我们具体分析一下如下拥有复杂 Where 条件的 SQL 语句。

mysql> UPDATE book SET score = 9.0 WHERE Author = 'Tom' AND ISBN > 'N0004' AND ISBN < 'N0007';

上述 SQL 语句的 Where 条件使用了两个索引,分别是二级唯一索引 ISBN 和二级非唯一索引 Author。MySQL 会根据索引选择性等指标选择其中一个索引来使用,而另外一个没有被使用的 Where 条件就被当做普通的过滤条件,一般称被用到的索引称为 Index Key,而作为普通过滤的条件则被称为 Table Filter。比如上面这条SQL 使用 ISBN索引来查询,则 ISBN 就是 Index Key,而 Author = 'Tom' 这个条件就是 Table Filter。

所以,该 SQL 执行的过程就是依次将 Index Key 范围内的索引记录读取,然后回表读取完整数据记录,然后返回给MySQL的服务层按照 Table Filter 进行过滤。 至于加锁,如下图所示则需要将涉及的 Index Key 对应的索引记录都进行加锁。

lock8_1

但是当使用的索引是复合索引时,则还可能出现 Index Filter,利用它可以减少回表次数和返回给 MySQL 服务层的记录的数量,降低存储引擎和服务层的交互开销,提高 SQL 的执行效率。

Index Filter

假设我们在 book 表的 ISBN 和 Author 列上建立了联合索引,并且上述 SQL 执行时选择了该复合索引。

对于这个场景,MySQL 依然使用 ISBN > 'N0004' AND ISBN < 'N0007' 条件来确定 SQL 查询在索引中的连续位置,但是 Author = 'Tom' 可以用来直接过滤索引,即该条件可以使用复合索引来直接过滤条件,不需要读取所有数据后由MySQL 服务层根据 Table Filter 来过滤。这就是传说中的 ICP(Index Condition Pushdown,索引下推)技术,使用 Index Filter 过滤不满足条件的记录,无需加锁

lock8_11

根据 Index Key 判断查询返回和根据 Index Filter 进行初步过滤后,存储引擎将剩下的数据记录返回给服务层,再由服务层根据 Table Filter 进行过滤。

ICP (索引下推)技术

MySQL 5.6 推出的 ICP 技术其实就是 Index Filter 技术,只不过是因为 MySQL 分为服务层和存储引擎层,而 Index Filter 将原本服务层做的过滤操作“下推”到存储引擎层处理。将原来的在服务层进行的Table Filter中可以进行Index Filter的部分,在引擎层面使用 Index Filter 进行处理,不再需要回表进行 Table Filter。

这样做的好处就是减少了加锁的记录数,减少了回表查询的数量,提高了 SQL 的执行效率。

终于要到系列的最后一篇了,下一篇,我们将讲解如何根据 MySQL 信息判断死锁和解决死锁。请大家关注,转发和点赞三连走起。

个人博客,欢迎来玩

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
安装 MySql | 学习笔记
快速学习安装 MySql。
56 0
卧槽,安装完MySQL竟然提示数据表不存在!!
本来想基于MySQL 8.0研究Seata源码,结果我不小心把MySQL 8.0的源码安装包删除了,我这的网又很慢,下载MySQL 8.0源码安装包下载了半天,没下载下来。只好安装我电脑上存在的MySQL 5.6来研究Seata源码了。安装完MySQL 5.6竟然提示我数据表不存在!! 今天,就给小伙伴们分享下我是如何解决这个问题的。
199 0
MySQL 5.7安装图文教程
本文目录 1. 背景 2. 安装流程 2.1 同意安装协议 2.2 选择安装类型 2.3 选择产品及特征 2.4 检查必备条件 2.5 安装 2.6 开始配置 2.7 集群配置 2.8 数据库类型和网络配置 2.9 设置ROOT账户密码 2.10 mysql安装为windows服务 2.11 插件和拓展 2.12 应用配置 2.13 搞定
78 0
MySQL 超新手入门(2) 资料库概论与 MySQL 安装
储存与管理资料一直是资讯应用上最基本、也是最常见的技术。在还没有使用电脑来管理你的资料时,你可能会使用这样的方式来保存世界上所有的国家资料:
92 0
Linux环境下Mysql++安装及操作深入详解
题记: 之前项目中使用OTL连接操作Oracle数据库,对于Mysql有用,但没有总结。目前常用的两种连接方式:
142 0
腾讯云 ubuntu服务器mysql安装和外网访问
1 腾讯云 购买ubuntu 默认账户是ubuntu(由于winscp 使用ubuntu没有权限写文件)
124 0
云服务器安装mysql小白操作
多数新人在使用云服务器之前对此都是一无所知的,所以再次分享一篇详细的使用云服务器mysql的教程
294 0
小白使用ECS安装mysql远程连接的心酸历程
在使用阿里云ECS服务器中遇到的心酸事件!
237 0
Windows环境下安装及配置MySQL
本文主要讲解在Windows环境下MySQL的安装、配置
3380 0
76
文章
4
问答
来源圈子
更多
+ 订阅
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载