Semi-join使用条件,派生表优化 (3)—mysql基于规则优化(四十六)

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群版 2核4GB 100GB
推荐场景:
搭建个人博客
云数据库 RDS MySQL,高可用版 2核4GB 50GB
简介: Semi-join使用条件,派生表优化 (3)—mysql基于规则优化(四十六)

前面说了子查询里有no/any/all不能用limit,group by,order by等,他会被查询优化器优化掉,子查询可能会物化转成内连接semi-join查询,物化就是会吧子查询看做一个表,如果数据太大,超过系统变量tmp_table_size,则会在磁盘里创建b+树的临时表,如果比较小,则会创建内存里hash树的临时表,之后会物化表转连接,但如果直接转where 和on,则可能会出现子查询多条的情况,我们的真实需求并不需要多条,所以有了semi-join。

子查询注意事项&semi-join(2)—mysql基于规则优化(四十五)


Semi-join适用


不是所有的都适用内连接

SELECT ... FROM outer_tables
   WHERE expr IN (SELECT ... FROM inner_tables ...) AND ...
SELECT ... FROM outer_tables
   WHERE (oe1, oe2, ...) IN (SELECT ie1, ie2, ... FROM inner_tables ...) AND ...

上面两个sql适用内连接,总结下来就是:

该组合必须和in组成布尔表达式,并在外层的where和on出现。

外层也可以有其他搜索条件,in子查询搜索条件必须和and连接。

不能由若干查询union连接。

前面说的子查询不能由having和group by等。

不适用semi-join

外层的where条件有其他其他搜索条件与子查询用or连接

SELECT * FROM s1
   WHERE key1 IN (SELECT common_field FROM s2 WHERE key3 = 'a'
       OR key2 > 100;

Not in代替in也不行

SELECT * FROM s1
   WHERE key1 NOT IN (SELECT common_field FROM s2 WHERE key3 = 'a')


在select子句中的in查询情况

SELECT key1 IN (SELECT common_field FROM s2 WHERE key3 = 'a') FROM s1 ;

子查询有group by

SELECT * FROM s1
   WHERE key2 IN (SELECT COUNT(*) FROM s2 GROUP BY key1);

子查询有union

SELECT * FROM s1 WHERE key1 IN (
   SELECT common_field FROM s2 WHERE key3 = 'a'
   UNION
   SELECT common_field FROM s2 WHERE key3 = 'b'
);

当这些不适合转内连接的,就是直接物化子查询来查询数据,效率也会非常快。(注意,这里物化之后是不能转成内连接,只能先扫描s1表,看key1的值是不是在物化表内)

不管是相关查询还是不想管查询,都可以吧in转换成exists子查询,其实对于任意的in都可以转成exists,

outer_expr IN (SELECT inner_expr FROM ... WHERE subquery_where)

可以转成

EXISTS (SELECT inner_expr FROM ... WHERE subquery_where AND outer_expr=inner_expr)

但当inner_expr和outer_expr值为null的情况下比较特殊,因为null值为操作符返回的是null,比如:

mysql> SELECT NULL IN (1, 2, 3);
+-------------------+
| NULL IN (1, 2, 3) |
+-------------------+
|              NULL |
+-------------------+
1 row in set (0.00 sec)
mysql> SELECT 1 IN (1, 2, 3);
+----------------+
| 1 IN (1, 2, 3) |
+----------------+
|              1 |
+----------------+
1 row in set (0.00 sec)
mysql> SELECT NULL IN (NULL);
+----------------+
| NULL IN (NULL) |
+----------------+
|           NULL |
+----------------+
1 row in set (0.00 sec)

而如果吧上面的转成exists,返回的就不是null,而是true或者false:

mysql> SELECT EXISTS (SELECT 1 FROM s1 WHERE NULL = 1);
+------------------------------------------+
| EXISTS (SELECT 1 FROM s1 WHERE NULL = 1) |
+------------------------------------------+
|                                        0 |
+------------------------------------------+
1 row in set (0.01 sec)
mysql> SELECT EXISTS (SELECT 1 FROM s1 WHERE 1 = NULL);
+------------------------------------------+
| EXISTS (SELECT 1 FROM s1 WHERE 1 = NULL) |
+------------------------------------------+
|                                        0 |
+------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT EXISTS (SELECT 1 FROM s1 WHERE NULL = NULL);
+---------------------------------------------+
| EXISTS (SELECT 1 FROM s1 WHERE NULL = NULL) |
+---------------------------------------------+
|                                           0 |
+---------------------------------------------+
1 row in set (0.00 sec)

但我们基本都不会这么写,都会放在sql的where或者on后面,这样就不会区分null或者false,

mysql> SELECT 1 FROM s1 WHERE NULL;
Empty set (0.00 sec)
mysql> SELECT 1 FROM s1 WHERE FALSE;
Empty set (0.00 sec)

所以只要在where或者on后面,直接转成exists是没问题的

SELECT * FROM s1 WHERE key1 IN (SELECT key3 FROM s2 where s1.common_field = s2.common_field) OR key2 > 1000;

这个sql转换可以这样

SELECT * FROM s1 WHERE exists (SELECT key3 FROM s2 where s1.common_field = s2.common_field and s1.key1 = s2.key3) OR key2 > 1000;

说到底,为什么要转换呢,这样就可以使用s2.key3的索引查询了,这样不是更快吗?

ANY/ALL子查询优化

比如:

< ANY (SELECT inner_expr ...)可以转换成< (SELECT MAX(inner_expr) ...)
< ALL (SELECT inner_expr ...) 可以转换成< (SELECT MIN(inner_expr) ...)
[NOT]EXISTS子查询执行
SELECT * FROM s1
   WHERE EXISTS (SELECT 1 FROM s2 WHERE key1 = 'a')
       OR key2 > 100;

因为exists返回的是true和false,所以上面的查询返回true可以简化

SELECT * FROM s1
   WHERE TRUE OR key2 > 100;

最后进一步简化为

SELECT * FROM s1
   WHERE TRUE;

上面说的是不相关子查询,若是相关子查询的话怎么办呢?

SELECT * FROM s1
   WHERE EXISTS (SELECT 1 FROM s2 WHERE s1.common_field = s2.common_field);

这种的话就只能按最原始的方法循环查询,前面说过了,当然如果有索引的话也会快不少。


对于派生表优化

前面说的都是子查询放在where和on后面,在in里面,如果吧子查询放在from后面,就是派生表:

SELECT * FROM  (
       SELECT id AS d_id,  key3 AS d_key3 FROM s2 WHERE key1 = 'a'
   ) AS derived_s1 WHERE d_key3 = 'a';

那么我们派生表如何优化呢?

派生表物化:

这种大家肯定是最容易想到的,mysql采用的是延迟物化策略,不是直接查询的时候就物化,免得降低效率。

SELECT * FROM (
       SELECT * FROM s1 WHERE key1 = 'a'
   ) AS derived_s1 INNER JOIN s2
   ON derived_s1.key1 = s2.key1
   WHERE s2.key2 = 1;

比如上面这个,他实现判断条件是否满足,满足才吧子表物化。


将派生表和外层表合并

SELECT * FROM (SELECT * FROM s1 WHERE key1 = 'a') AS derived_s1;

其实这个本质就是看s1里满足key1=’a’吗

所以直接优化成

SELECT * FROM s1 WHERE key1 = 'a';

对于稍微复杂点的语句

SELECT * FROM (
       SELECT * FROM s1 WHERE key1 = 'a'
   ) AS derived_s1 INNER JOIN s2
   ON derived_s1.key1 = s2.key1
   WHERE s2.key2 = 1;

我们也可以优化成这样

SELECT * FROM s1 INNER JOIN s2
   ON s1.key1 = s2.key1
   WHERE s1.key1 = 'a' AND s2.key2 = 1;

这样直接消除派生的功能,可以让我们减少创建临时表的开销。但当里面有这些,就不可以合并派生表和外层表了,有聚合函数,比如max()等,比如distinct,group by,having等。

所以对于派生表,先进行外层和子表的合并,不行的话就物化子表。

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
8天前
|
存储 关系型数据库 MySQL
MySQL 上亿大表,如何深度优化?
【8月更文挑战第11天】随着大数据时代的到来,MySQL 作为广泛使用的关系型数据库管理系统,经常需要处理上亿级别的数据。当数据量如此庞大时,如何确保数据库的查询效率、稳定性和可扩展性,成为了一个亟待解决的问题。本文将围绕 MySQL 上亿大表的深度优化,分享一系列实用的技术干货,帮助你在工作和学习中应对挑战。
25 1
|
19天前
|
SQL canal 关系型数据库
(二十四)全解MySQL之主从篇:死磕主从复制中数据同步原理与优化
兜兜转转,经过《全解MySQL专栏》前面二十多篇的内容讲解后,基本对MySQL单机模式下的各方面进阶知识做了详细阐述,同时在前面的《分库分表概念篇》、《分库分表隐患篇》两章中也首次提到了数据库的一些高可用方案,但前两章大多属于方法论,并未涵盖真正的实操过程。接下来的内容,会以目前这章作为分割点,开启MySQL高可用方案的落地实践分享的新章程!
116 1
|
23天前
|
SQL 缓存 关系型数据库
MySQL 查询索引失效及如何进行索引优化
MySQL 查询索引失效及如何进行索引优化
60 1
|
3天前
|
存储 SQL 关系型数据库
探索MySQL的执行奥秘:从查询执行到数据存储与优化的深入解析
探索MySQL的执行奥秘:从查询执行到数据存储与优化的深入解析
|
10天前
|
运维 关系型数据库 MySQL
"MySQL运维精髓:深入解析数据库及表的高效创建、管理、优化与备份恢复策略"
【8月更文挑战第9天】MySQL是最流行的开源数据库之一,其运维对数据安全与性能至关重要。本文通过最佳实践介绍数据库及表的创建、管理与优化,包括示例代码。涵盖创建/删除数据库、表结构定义/调整、索引优化和查询分析,以及数据备份与恢复等关键操作,助您高效管理MySQL,确保数据完整性和系统稳定运行。
25 0
|
12天前
|
SQL 缓存 关系型数据库
MySQL配置简单优化与读写测试
MySQL配置简单优化与读写测试
|
23天前
|
分布式计算 关系型数据库 MySQL
MySQL超时参数优化与DataX高效数据同步实践
通过合理设置MySQL的超时参数,可以有效地提升数据库的稳定性和性能。而DataX作为一种高效的数据同步工具,可以帮助企业轻松实现不同数据源之间的数据迁移。无论是优化MySQL参数还是使用DataX进行数据同步,都需要根据具体的应用场景来进行细致的配置和测试,以达到最佳效果。
|
6天前
|
存储 关系型数据库 MySQL
MySQL——数据库备份上传到阿里云OSS存储
MySQL——数据库备份上传到阿里云OSS存储
20 0
|
6天前
|
缓存 NoSQL Redis
一天五道Java面试题----第九天(简述MySQL中索引类型对数据库的性能的影响--------->缓存雪崩、缓存穿透、缓存击穿)
这篇文章是关于Java面试中可能会遇到的五个问题,包括MySQL索引类型及其对数据库性能的影响、Redis的RDB和AOF持久化机制、Redis的过期键删除策略、Redis的单线程模型为何高效,以及缓存雪崩、缓存穿透和缓存击穿的概念及其解决方案。
|
8天前
|
Oracle 关系型数据库 MySQL
Mysql和Oracle数据库死锁查看以及解决
【8月更文挑战第11天】本文介绍了解决MySQL与Oracle数据库死锁的方法。MySQL可通过`SHOW ENGINE INNODB STATUS`查看死锁详情,并自动回滚一个事务解除死锁;也可手动KILL事务。Oracle则通过查询V$LOCK与V$SESSION视图定位死锁,并用`ALTER SYSTEM KILL SESSION`命令终止相关会话。预防措施包括遵循ACID原则、优化索引及拆分大型事务。