MySQL复杂where条件分析-阿里云开发者社区

开发者社区> 程序员历小冰> 正文

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 信息判断死锁和解决死锁。请大家关注,转发和点赞三连走起。

个人博客,欢迎来玩

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

相关文章
分区表查询条件使用浅析
如果有个分区表,分区列为A、B、C三列,我要查询B列为某些值的数据,MaxCompute还能充分发挥分区表的优势吗?答案是肯定的,MaxCompute依然能发挥分区列的优势!在底层解析SQL执行计划时,只会将符合条件的分区纳入计算,而不是进行全表扫描。
1816 0
mysql too many connections 分析
# 现象 实例出现too many connections ``` my 3093 Entry Port ==== 3093 ERROR 1040 (08004): Too many connections ``` gdb修改max_connections后查看processlist,
3114 0
mysql full text全文索引必要条件
show variables like 'ft_m%' 'ft_max_word_len', '84''ft_min_word_len', '4' 对于英文来说, ft_min_word_len=4是可以的。但是对于中文,需要为1. 要不就没有办法全文搜索了。
607 0
使用OpenApi弹性释放和设置云服务器ECS释放
云服务器ECS的一个重要特性就是按需创建资源。您可以在业务高峰期按需弹性的自定义规则进行资源创建,在完成业务计算的时候释放资源。本篇将提供几个Tips帮助您更加容易和自动化的完成云服务器的释放和弹性设置。
11700 0
线程同步:条件变量的使用细节分析
如同互斥量和读写锁一样,条件变量也需要初始化和回收#includeint pthread_cond_init(pthread_cond_t *restrict cond, pthread_condattr_t *restrict attr);int pthread_cond_destroy(pthread_cond_t *cond);互斥量和读写锁解决了多线程访问共享变量产生的竞争问题,那么条件变量的作用何在呢。
703 0
78
文章
4
问答
来源圈子
更多
+ 订阅
文章排行榜
最热
最新
相关电子书
更多
《2021云上架构与运维峰会演讲合集》
立即下载
《零基础CSS入门教程》
立即下载
《零基础HTML入门教程》
立即下载