Mysql性能优化 - 使用半连接优化子查询,派生表和视图引用

举报
简简单单Onlinezuozuo 发表于 2022/02/18 23:07:12 2022/02/18
【摘要】 Mysql性能优化 - 使用半连接优化子查询,派生表和视图引用 1.半连接策略 两个表之间进行inner join 从另外一个表返回指定条件的一些匹配数据,他可能是1对1的,也可能是1对多的。 这样会...

Mysql性能优化 - 使用半连接优化子查询,派生表和视图引用

1.半连接策略

两个表之间进行inner join
从另外一个表返回指定条件的一些匹配数据,他可能是11的,也可能是1对多的。
这样会导致一些不必要的麻烦,但是,最主要的是我们能够通过join获取到匹配项,而不是到底匹配了多少数据。

// 例如
有两个表: class和roster(就读于每个班级的学生)课程中的课程,列表类和类名册,类别。 要列出实际注册学生的课程,使用如下的代码:

SELECT class.class_num, class.class_name
FROM class INNER JOIN roster
WHERE class.class_num = roster.class_num;

如我们所预期的那样,结果列出了每个注册学生的每个课程一次(我们得到了重复的匹配)

如果class_num是class表中的主键,则可以通过使用重复抑制 SELECT DISTINCT,
但是首先生成所有匹配的行仅在以后消除重复是无效的。

为了获取相同的无重复结果,我们可以使用下述的方式(使用子查询)。
SELECT class_num, class_name
FROM class
WHERE class_num IN (SELECT class_num FROM roster);
优化器可以识别该 IN子句要求子查询从roster表中只返回每个类号的一个实例 。
在这种情况下,查询可以使用半连接 ; 也就是说,一个操作只返回每行中class与行匹配的 一个实例 roster。

外部查询规范允许外连接和内部连接语法,表引用可以是基表,派生表或视图引用。


  
 
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26

2.使用半连接的条件

1.它必须是出现在 or 子句顶层的IN(或 =ANY)子查询,可能是表达式中的一个术语 。例如: WHERE ON AND
    SELECT ...
    FROM ot1, ...
    WHERE (oe1, ...) IN (SELECT ie1, ... FROM it1, ... WHERE ...);

2.他必须是一个没有union的select
3.他不能包含having和group by子句
4.他不能被隐式的分组(也就是使用聚合函数)
5.他不能进行order by排序或者limit限制
6.不能STRAIGHT_JOIN在外部查询中使用 连接类型
7.STRAIGHT_JOIN修改必须不存在
8.外表和内表的数量必须小于连接中允许的最大表数

// 半连接允许distinct
// 如果要使用limit必须同时使用group by

如果一个子查询符合上述条件,mysql会把他转化成半连接,并基于最优执行策略执行,有以下几种方式

将子查询转换为连接,或使用表拉出,并将查询作为子查询表和外部表之间的内部连接运行。表拉出将子表中的表从外部查询中拉出。

1.重复消除:运行半连接,并使用临时表删除重复的记录。

2.第一个匹配:扫描内部表的行组合,并且有多个实例的给定值组时,选择一个而不是全部返回。这种“快捷方式”扫描并消除了不必要的行的生成。

3.LooseScan:使用能够从每个子查询的值组中选择单个值的索引来扫描子查询表。

4.将子查询实现为用于执行连接的索引临时表,其中索引用于删除重复项。当将临时表与外部表连接时,索引也可能稍后用于查找; 如果没有,表被扫描。

5.可以使用以下optimizer_switch 系统变量标志启用或禁用策略:
    semijoin标志控制是否使用半连接。
    如果semijoin开启, firstmatch, loosescan, duplicateweedout,和 materialization能够在允许的半连接策略中进行更精细的控制。
    如果duplicateweedout半连接策略被禁用,除非所有其他适用的策略也被禁用,否则不使用它。
    如果duplicateweedout被禁用,有时优化器可能会生成远离最优的查询计划。
    这可以通过设置optimizer_prune_level=0来避免 。


  
 
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36

文章来源: wretchant.blog.csdn.net,作者:简简单单OnlineZuozuo,版权归原作者所有,如需转载,请联系作者。

原文链接:wretchant.blog.csdn.net/article/details/78012312

【版权声明】本文为华为云社区用户转载文章,如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。