MySQL复杂where条件分析

本文涉及的产品
云数据库 RDS MySQL,集群版 2核4GB 100GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用版 2核4GB 50GB
简介: 的加锁原理并具体分析了大部分的简单 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的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
2月前
|
关系型数据库 MySQL 索引
mysql 分析5语句的优化--索引添加删除
mysql 分析5语句的优化--索引添加删除
25 0
|
2月前
|
关系型数据库 MySQL 数据挖掘
轻松入门MySQL:利用MySQL时间函数优化产品销售数据统计与分析(9)
轻松入门MySQL:利用MySQL时间函数优化产品销售数据统计与分析(9)
|
2月前
|
关系型数据库 MySQL 数据库
轻松入门MySQL:精准查询,巧用WHERE与HAVING,数据库查询如虎添翼(7)
轻松入门MySQL:精准查询,巧用WHERE与HAVING,数据库查询如虎添翼(7)
|
2月前
|
SQL 关系型数据库 MySQL
【MySQL技术专题】「问题实战系列」深入探索和分析MySQL数据库的数据备份和恢复实战开发指南(8.0版本升级篇)
【MySQL技术专题】「问题实战系列」深入探索和分析MySQL数据库的数据备份和恢复实战开发指南(8.0版本升级篇)
191 0
|
2月前
|
SQL 缓存 关系型数据库
mysql性能优化-慢查询分析、优化索引和配置
mysql性能优化-慢查询分析、优化索引和配置
169 1
|
2月前
|
缓存 关系型数据库 MySQL
MySQL 查询优化:提速查询效率的13大秘籍(索引设计、查询优化、缓存策略、子查询优化以及定期表分析和优化)(中)
MySQL 查询优化:提速查询效率的13大秘籍(索引设计、查询优化、缓存策略、子查询优化以及定期表分析和优化)(中)
151 0
|
24天前
|
存储 缓存 监控
MySQL 8.0中查询缓存的废弃与原因分析
MySQL 8.0中查询缓存的废弃与原因分析
38 1
|
26天前
|
SQL 关系型数据库 MySQL
蓝易云 - Mysql join加多条件与where的区别
总的来说,JOIN和WHERE都是SQL查询的重要部分,但它们用于处理不同的问题:JOIN用于连接表,而WHERE用于过滤结果。
12 2
|
28天前
|
SQL 关系型数据库 MySQL
MySQL数据库基础练习系列8、成绩录入与分析系统
MySQL数据库基础练习系列8、成绩录入与分析系统
14 1
|
1月前
|
SQL 关系型数据库 MySQL
基于Hive的天气情况大数据分析系统(通过hive进行大数据分析将分析的数据通过sqoop导入到mysql,通过Django基于mysql的数据做可视化)
基于Hive的天气情况大数据分析系统(通过hive进行大数据分析将分析的数据通过sqoop导入到mysql,通过Django基于mysql的数据做可视化)