Mysql性能优化 - 使用半连接优化子查询,派生表和视图引用
【摘要】
Mysql性能优化 - 使用半连接优化子查询,派生表和视图引用
1.半连接策略
两个表之间进行inner join
从另外一个表返回指定条件的一些匹配数据,他可能是1对1的,也可能是1对多的。
这样会...
Mysql性能优化 - 使用半连接优化子查询,派生表和视图引用
1.半连接策略
两个表之间进行inner join
从另外一个表返回指定条件的一些匹配数据,他可能是1对1的,也可能是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)