MySql 内连接，外连接查询方式区别

客官°小女子只卖身不卖艺 2021-12-19 04:29 278阅读 0赞

#### MySql 内连接，外连接查询方式 ####

CREATE TABLE `question_test` (
      `q_id` int(11) DEFAULT NULL,
      `q_name` varchar(10) DEFAULT NULL,
      `q_part` varchar(10) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    
    CREATE TABLE `answer_test` (
      `a_id` int(11) DEFAULT NULL,
      `a_name` varchar(10) DEFAULT NULL,
      `a_part` varchar(10) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    
    INSERT INTO `zhenai_qa`.`answer_test` (`a_id`, `a_name`, `a_part`) VALUES (2, '回答二', '答案二');
    INSERT INTO `zhenai_qa`.`answer_test` (`a_id`, `a_name`, `a_part`) VALUES (3, '回答三', '答案三');
    INSERT INTO `zhenai_qa`.`answer_test` (`a_id`, `a_name`, `a_part`) VALUES (5, '回答五', '答案五');
    INSERT INTO `zhenai_qa`.`answer_test` (`a_id`, `a_name`, `a_part`) VALUES (6, '回答六', '答案六');
    
    INSERT INTO `zhenai_qa`.`question_test` (`q_id`, `q_name`, `q_part`) VALUES (1, '问题一', '描述一');
    INSERT INTO `zhenai_qa`.`question_test` (`q_id`, `q_name`, `q_part`) VALUES (2, '问题二', '描述二');
    INSERT INTO `zhenai_qa`.`question_test` (`q_id`, `q_name`, `q_part`) VALUES (3, '问题三', '描述三');
    INSERT INTO `zhenai_qa`.`question_test` (`q_id`, `q_name`, `q_part`) VALUES (4, '问题四', '描述四');

*  建如上数据库，初始化测试数据。

1.  内连接

![\[外链图片转存失败(img-wAT4WHmo-1562061066256)(E:\\learn\\learn笔记\\技术相关\\mysql1.jpg)\]][img-wAT4WHmo-1562061066256_E_learn_learn_mysql1.jpg]

*  内连接会查询出answer\_test表和question\_test表中交集部分的数据如下

![\[外链图片转存失败(img-G7w9OhGL-1562061066257)(E:\\learn\\learn笔记\\技术相关\\mysql2.jpg)\]][img-G7w9OhGL-1562061066257_E_learn_learn_mysql2.jpg]

1.  左外连接

![\[外链图片转存失败(img-S9VJlfEf-1562061066257)(E:\\learn\\learn笔记\\技术相关\\mysql3.jpg)\]][img-S9VJlfEf-1562061066257_E_learn_learn_mysql3.jpg]

*  以左表为基准查右表的数据： 也就是左表的数据全查询出，并且会查询出右表中符合要求的数据，不存在的数据补null

![\[外链图片转存失败(img-wqsQqhXh-1562061066257)(E:\\learn\\learn笔记\\技术相关\\mysql4.jpg)\]][img-wqsQqhXh-1562061066257_E_learn_learn_mysql4.jpg]

1.  右外连接

![\[外链图片转存失败(img-mNOIhl7j-1562061066258)(E:\\learn\\learn笔记\\技术相关\\mysql5.jpg)\]][img-mNOIhl7j-1562061066258_E_learn_learn_mysql5.jpg]

*  和左外相反，以右表为基准查左表数据，右表的数据会全查询出，并且查询出左表中符合条件的数据，

![\[外链图片转存失败(img-2fuWLUpD-1562061066258)(E:\\learn\\learn笔记\\技术相关\\mysql6.jpg)\]][img-2fuWLUpD-1562061066258_E_learn_learn_mysql6.jpg]

1.  mysql如何执行关联查询以及优化

*  mysql关联查询执行的策略是比较简单的，mysql对任何关联查询执行嵌套循环关联操作，即：mysql先在一个表中循环取出单条数据，然后在嵌套循环到关联表中寻找匹配的行，一次找下去，一直到找到所有表中匹配的行为止。然后更具各个表匹配出的行，返回查询中需要的各个列，如果mysql在最后一个关联表无法找到更多的行，他将返回上一层关联表，看能否找到更多的匹配数据，一次类推执行。
 *  按照这种方式，mysql查找第一个表的记录，再嵌套查询下一个关联表，然后回溯到上一个表，这正如其名——“嵌套循环关联”。看一下下面的例子：

SELECT
        t1.column1,
        t2.column2
    FROM
        tb1 t1
    INNER JOIN tb2 t2 ON t1.column3 = t2.column3
    WHERE
        t1.column1 IN (4, 6)

outer_iter = iterator over t1 WHERE    column3 IN (4, 6) 
    outer_row = outer_iter.next
    WHILE outer_row 
        inner_iter = iterator over t2    WHERE    column3 = outer_row.column3
        inner_row = inner_iter.next
        WHILE inner_row 
            output [ outer_row.column1,inner_row.column2 ] 
            inner_row = inner_iter.next
        END 
        outer_row = outer_iter.next
    END

*  假设mysql按照查询中表顺序进行关联操作，我们可以用伪代码标识这个过程。
 *  我们用表格的形式来表示关联查询过程，从左到右

<table> 
 <thead> 
  <tr> 
   <th>t1</th> 
   <th>t2</th> 
   <th>结果行</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>column1=4,column3=1</td> 
   <td>column3=1,column2=1</td> 
   <td>column1=4,column3=1</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>column3=1,column2=2</td> 
   <td>column1=4,column3=2</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>column3=1,column2=3</td> 
   <td>column1=4,column3=3</td> 
  </tr> 
  <tr> 
   <td>column1=6,column3=2</td> 
   <td>column3=2,column2=1</td> 
   <td>column1=6,column3=1</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>column3=2,column2=2</td> 
   <td>column1=6,column3=2</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>column3=2,column2=3</td> 
   <td>column1=6,column3=3</td> 
  </tr> 
 </tbody> 
</table>

*  t1中匹配到的第一行，4和1， 接着会去关联表中查找关联数据，得到一二三条，第二台数据类似。

1.  关联查询的优化：

EXPLAIN select * from QuestionAnswerContentDetailVerify q STRAIGHT_JOIN Questions a 
    ON q.questionID = a.questionID where a.questionType != 4

*  如上sql，mysql优化器最重要的一个部分就是关联查询的优化，他决定了多个表关联时候的顺序，通常多表关联的时候，可以有多重关联的顺序，来获取相同的结果，关联查询优化器通过评估不同的顺序时候的执行成本来选择一个最小代价的关联顺序。
 *  按照上面的案例中执行顺序，mysql先user表，通过user\_company的user\_id得到company\_id，通过company表主键对应的记录，我们执行mysql的explain得出结果如下：

![\[外链图片转存失败(img-Z2S33Fjb-1562061066258)(E:\\learn\\learn笔记\\技术相关\\mysql7.jpg)\]][img-Z2S33Fjb-1562061066258_E_learn_learn_mysql7.jpg]

*  我们修改join顺序在来执行以下sql

EXPLAIN select * from Questions a STRAIGHT_JOIN QuestionAnswerContentDetailVerify q
    ON q.questionID = a.questionID where a.questionType != 4

![\[外链图片转存失败(img-frt62Xty-1562061066259)(E:\\learn\\learn笔记\\技术相关\\mysql8.jpg)\]][img-frt62Xty-1562061066259_E_learn_learn_mysql8.jpg]

*  mysql会将查询尽可能往第二中方式去靠近，对比如上两个，第一种查询中第一个表q扫码出45447条记录，第二种扫出61条数据，如果我们第一层查询返回更少的数据记录，然后在镜像嵌套循环查询，这种情况我们嵌套循环和回溯操作的次数会变得更少。
 *  通过这个例子我们也可以得出在关联查询时候讲数据流较少的表放在关联查询的首表的原因也是如此。而mysql重新定义关联顺序也是优化器的一个重要功能，他尝试在顺序查询中选择一个成本最小的来生产执行计划树。

[img-wAT4WHmo-1562061066256_E_learn_learn_mysql1.jpg]: /images/20211218/9d09fef9be464a208fff4fe7bcb34a77.png
[img-G7w9OhGL-1562061066257_E_learn_learn_mysql2.jpg]: /images/20211218/bfb94bb57c984c79b1c8a8b8a5ade797.png
[img-S9VJlfEf-1562061066257_E_learn_learn_mysql3.jpg]: /images/20211218/68b06a719c974ec482e2a030af6aee7d.png
[img-wqsQqhXh-1562061066257_E_learn_learn_mysql4.jpg]: /images/20211218/39a8002c06004a5381a0a3c52b197e1c.png
[img-mNOIhl7j-1562061066258_E_learn_learn_mysql5.jpg]: /images/20211218/2a616905afa54a33bc7aa074c30f77ea.png
[img-2fuWLUpD-1562061066258_E_learn_learn_mysql6.jpg]: /images/20211218/9b5d0063df924a0fbd6d08ef5bc35ee1.png
[img-Z2S33Fjb-1562061066258_E_learn_learn_mysql7.jpg]: /images/20211218/4a9352b73c954bae84b99c87dccd9c60.png
[img-frt62Xty-1562061066259_E_learn_learn_mysql8.jpg]: /images/20211218/6f6b85cfbe7349cfa6931d88088a7854.png