本文的目的是介绍何时在MySQL上使用STRAIGHT_JOIN的详细情况，我们将通过专业的研究、有关数据的分析等多种方式，同时也不会遗漏关于(转载)Mysql----Join用法(Innerjoin

本文的目的是介绍何时在MySQL上使用STRAIGHT_JOIN的详细情况，我们将通过专业的研究、有关数据的分析等多种方式，同时也不会遗漏关于(转载) Mysql----Join用法(Inner join,Left join,Right join, Cross join, Union模拟Full join)及---性能优化、JPA或HIBERNATE中的straight_join、LEFT JOIN 与 RIGHT JOIN 简明教程(ORACLE & MYSQL)、MySQL HINT：Straight_JOIN的知识。

本文目录一览：

• 何时在MySQL上使用STRAIGHT_JOIN
• (转载) Mysql----Join用法(Inner join,Left join,Right join, Cross join, Union模拟Full join)及---性能优化
• JPA或HIBERNATE中的straight_join
• LEFT JOIN 与 RIGHT JOIN 简明教程(ORACLE & MYSQL)
• MySQL HINT：Straight_JOIN

何时在MySQL上使用STRAIGHT_JOIN

我刚遇到一个非常复杂的查询，要花8秒钟才能运行。EXPLAIN显示了一个奇怪的表顺序，即使使用FORCE
INDEX提示也没有全部使用我的索引。我遇到了STRAIGHT_JOIN连接关键字，并开始用它替换一些INNER
JOIN关键字。我注意到速度有了很大提高。最终，我为此查询将所有INNER JOIN关键字替换为STRAIGHT_JOIN，它现在可以在0.01秒内运行。

我的问题是何时使用STRAIGHT_JOIN，何时使用INNER JOIN？如果您编写良好的查询，是否有任何理由不使用STRAIGHT_JOIN？

答案1

小编典典

我不建议在没有充分理由的情况下使用STRAIGHT_JOIN。我自己的经验是，MySQL查询优化器选择不良的查询计划的频率比我想要的要多，但不足以使您通常绕开它，如果您始终使用STRAIGHT_JOIN，这就是您要做的。

我的建议是将所有查询保留为常规联接。如果发现一个查询正在使用次优查询计划，建议您先尝试重写或重新组织查询，以查看优化程序是否会选择更好的查询计划。另外，至少对于innodb，请确保不仅仅是索引统计信息已过期（ANALYZE
TABLE）。这可能会导致优化器选择不良的查询计划。优化器提示通常应该是您的最后选择。

不使用查询提示的另一个原因是，随着表的增长，您的数据分布可能会随时间变化，或者索引选择性可能会发生变化等。您的查询提示现在是最佳的，随着时间的流逝可能会变得不理想。但是由于您现在已经过时的提示，优化器将无法适应查询计划。如果允许优化器做出决策，则可以保持更大的灵活性。

(转载) Mysql----Join用法(Inner join,Left join,Right join, Cross join, Union模拟Full join)及---性能优化

http://blog.csdn.net/ochangwen/article/details/52346610

 

前期数据准备

CREATE TABLE  atable(
aID int( 1 ) AUTO_INCREMENT PRIMARY KEY ,
aNum char( 20 ));

CREATE TABLE btable(
bID int( 1 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
bName char( 20 ) );

INSERT INTO atable
VALUES ( 1, ''a20050111'' ) , ( 2, ''a20050112'' ) , ( 3, ''a20050113'' ) , ( 4, ''a20050114'' ) , ( 5, ''a20050115'' ) ;

INSERT INTO btable
VALUES ( 1, '' 2006032401'' ) , ( 2, ''2006032402'' ) , ( 3, ''2006032403'' ) , ( 4, ''2006032404'' ) , ( 8, ''2006032408'' ) ;

-------------------------------------------------------------------------------------------

atable:左表；btable:右表。
JOIN 按照功能大致分为如下三类：
  1).inner join(内连接,或等值连接)：取得两个表中存在连接匹配关系的记录。
  2).left  join(左连接)：取得左表（atable）完全记录，即是右表（btable）并无对应匹配记录。
  3).right join(右连接)：与 LEFT JOIN 相反，取得右表（btable）完全记录，即是左表（atable）并无匹配对应记录。
注意：mysql不支持Full join,不过可以通过 union 关键字来合并 left join 与 right join来模拟full join.

一、Inner join

  内连接，也叫等值连接，inner join产生同时符合A和B的一组数据。
  接下来给出一个列子用于解释下面几种分类。如下两个表(A,B)

 

二、Left join

  left join,（或left outer join:在Mysql中两者等价，推荐使用left join.）左连接从左表(A)产生一套完整的记录,与匹配的记录(右表(B)) .如果没有匹配,右侧将包含null。

 

------------------------------------------------------------------------------------------------------------

 

  2).如果想只从左表(A)中产生一套记录，但不包含右表(B)的记录，可以通过设置where语句来执行，如下

 

-----------------------------------------------------------------------------------------

 

同理，还可以模拟inner join. 如下：

 

------------------------------------------------------------------------------------------

 

三、Right join

  同Left join

 

 

四、差集

 

-----------------------------------------------------------------------------------

 

五.Cross join

  交叉连接，得到的结果是两个表的乘积，即笛卡尔积

    笛卡尔（Descartes）乘积又叫直积。假设集合A={a,b}，集合B={0,1,2}，则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1), (b,2)}。可以扩展到多个集合的情况。类似的例子有，如果A表示某学校学生的集合，B表示该学校所有课程的集合，则A与B的笛卡尔积表示所有可能的选课情况。

 

 

    实际上，在 MySQL 中（仅限于 MySQL） CROSS JOIN 与 INNER JOIN 的表现是一样的，在不指定 ON 条件得到的结果都是笛卡尔积，反之取得两个表完全匹配的结果。    inner join 与 cross join 可以省略 inner 或 cross关键字，因此下面的 SQL 效果是一样的：

 

六.union实现Full join

    全连接产生的所有记录（双方匹配记录）在表A和表B。如果没有匹配,则对面将包含null。与差集类似。

 

--------------------------------------------------------------------------------------------------------

 

七.性能优化

  1.显示(explicit) inner join VS 隐式(implicit) inner join

 

VS

    数据库中比较(10w数据)得之，它们用时几乎相同，第一个是显示的inner join，后一个是隐式的inner join。
2.left join/right join VS inner join
    尽量用inner join.避免 left join 和 null.

 

    在使用left join（或right join）时，应该清楚的知道以下几点：

(1). on与 where的执行顺序
    ON 条件（“A LEFT JOIN B ON 条件表达式”中的ON）用来决定如何从 B 表中检索数据行。如果 B 表中没有任何一行数据匹配 ON 的条件,将会额外生成一行所有列为 NULL 的数据,在匹配阶段 WHERE 子句的条件都不会被使用。仅在匹配阶段完成以后，WHERE 子句条件才会被使用。它将从匹配阶段产生的数据中检索过滤。
    所以我们要注意：在使用Left (right) join的时候，一定要在先给出尽可能多的匹配满足条件，减少Where的执行。如：

下面这种写法更省时

(2).注意ON 子句和 WHERE 子句的不同

    从上可知，第一条查询使用 ON 条件决定了从 LEFT JOIN的 product_details表中检索符合的所有数据行。第二条查询做了简单的LEFT JOIN，然后使用 WHERE 子句从 LEFT JOIN的数据中过滤掉不符合条件的数据行。
(3).尽量避免子查询，而用join
    往往性能这玩意儿，更多时候体现在数据量比较大的时候，此时，我们应该避免复杂的子查询。如下：

 

 

下面这个更好

JPA或HIBERNATE中的straight_join

如何select straight_join ... from ... 在Hibernate / JPA中使用？

答案1

小编典典

JPQL / JPA没有Straight_join。

您将需要在NativeQuery中使用它。

entityManager.createNativeQuery（…）;

LEFT JOIN 与 RIGHT JOIN 简明教程(ORACLE & MYSQL)

Oracle的左连接和右连接 在Oracle PL-SQL中，左连接和右连接以如下方式来实现 查看如下语句： SELECT emp_name,dept_name FORMEmployee,Department WHERE Employee.emp_deptid( + ) = Department.deptid 此SQL文使用了右连接，即(+)所在位置的另一侧为连接的

 

Oracle的左连接和右连接

在Oracle PL-SQL中，左连接和右连接以如下方式来实现 查看如下语句： select emp_name, dept_name  form employee, department where employee.emp_deptid(+) = department.deptid 此SQL文使用了右连接，即“(+)”所在位置的另一侧为连接的方向，右连接说明等号右侧的所有记录均会被显示，无论其在左侧是否得到匹配，也就是说上例中无论会不会出现某个部门没有一个员工的情况，这个部门的名字都会在查询结果中出现。 反之： SELECT emp_name, dept_name  FORM Employee, Department WHERE Employee.emp_deptid = Department.deptid(+) 则是左连接，无论这个员工有没有一个能在Department表中得到匹配的部门号，这个员工的记录都会被显示

 

 

 ++++++++++++++++++++++++++++++++++++++++++MYSQL

A left join B 的连接的记录数与A表的记录数同
A right join B 的连接的记录数与B表的记录数同
A left join B 等价B right join A


table A:
Field_K, Field_A
1          a
3          b
4          c

table B:
Field_K, Field_B
1          x
2          y
4          z

select a.Field_K, a.Field_A, b.Field_K, b.Field_B
from a left join b on a.Field_K=b.Field_K

Field_K    Field_A    Field_K    Field_B   
---------- ---------- ---------- ----------
1          a          1          x       
3          b          NULL      NULL
4          c          4          z       

select a.Field_K, a.Field_A, b.Field_K, b.Field_B
from a right join b on a.Field_K=b.Field_K

Field_K    Field_A    Field_K    Field_B   
---------- ---------- ---------- ----------
1          a          1          x       
NULL       NULL       2          y       
4          c          4          z 

 

 

 

++++++++++++++++++++++++++++++++++++++

 

 

 

这样的。
   table1            table2
   id,sex1           id   sex2
   a     1          a       4
   b     0
select id,sex1,sex2 from table1 left join table2 on table1.id=table2.id
  则，
  id  sex1  sex2
  a     1    4
  b     0    null
也就是说left join 则连接左边表中所有记录都会出现，如果根据连接条件在table2中找不到相关记录，则显示为null
  right join 则显示右边表中的全部记录。
  inner join 则只有符合条件的记录才会出现在结果集中

MySQL HINT：Straight_JOIN

来自生产环境的朋友、可能都会碰到： 原本运行良好的查询语句，过了一段时间后，可能会突然变得很糟糕 一个很大可能的原因就是数据分布情况发生了变化 从而导致MySQL优化器对驱动表的选择发生了变化，进而出现索引失效的情况 所以、闲着蛋疼喝咖啡的时候、应

     来自生产环境的朋友、可能都会碰到：
     
     原本运行良好的查询语句，过了一段时间后，可能会突然变得很糟糕
     一个很大可能的原因就是数据分布情况发生了变化
     从而导致MySQL优化器对驱动表的选择发生了变化，进而出现索引失效的情况
     所以、闲着蛋疼喝咖啡的时候、应该多收集两下表的统计信息
     
     
     这个时候、Straight_JOIN 闪亮登场
     
     
     MySQL 只支持 Nested Loop Join、关于这个Nested JOIN的详细用法请参阅偶之前blog：点击打开链接
     和Oracle对比下、不然得知、Straight_JOIN相当于Oracle里面的：USE_NL、所以、原理和适用上大概都是相同的、
     不过、对于驱动表的选择、MySQL 优化器可能没有Oracle那般智能、MySQL采用简单粗暴的方法：
     哪个表的结果集小，就以哪个表为驱动表
     
     
     
     偶赶脚有2 种原因可令你选择 Straight_JOIN 
     
     ① MySQL 优化器不给力、错误选择驱动表
     ② Nested Loop Join 的适用场景：
        ==>一般用在连接的表中有索引，并且索引选择性较好（也就是Selectivity接近1）的时候
        ==>也就是驱动表的记录集比较小（
        
        
        
        
     一般的优化操作：
     
     
     ① show full processlist;
     ② explain + TOP-SQL ;
     
     注意：在EXPLAIN结果中，第一行出现的表就是驱动表
     
     
     
     
     一个经典优化例子：
     
     当explian输出结果中含：「Using filesort」，甚至「Using temporary」
     我们就该擦亮双眼、像打了鸡血一样、保持时刻优化的姿态

     此刻的优化就容易多了、尽可能保证排序字段在驱动表中

 

 

今天关于何时在MySQL上使用STRAIGHT_JOIN的讲解已经结束，谢谢您的阅读，如果想了解更多关于(转载) Mysql----Join用法(Inner join,Left join,Right join, Cross join, Union模拟Full join)及---性能优化、JPA或HIBERNATE中的straight_join、LEFT JOIN 与 RIGHT JOIN 简明教程(ORACLE & MYSQL)、MySQL HINT：Straight_JOIN的相关知识，请在本站搜索。

本文标签：

• STRAIGHT_JOIN
• MySQL
• JOIN语句
• 数据库优化