MySQL基础之STRAIGHT JOIN用法简介

心已赠人 2021-08-19 16:52 496阅读 0赞

引用[mysql官方手册][mysql]的说法：

> STRAIGHT\_JOIN is similar to JOIN, except that the left table is always read before the right table. This can be used for those (few) cases for which the join optimizer processes the tables in a suboptimal order.

翻译过来就是：STRAIGHT\_JOIN与 JOIN 类似，只不过左表始终在右表之前读取。这可用于联接优化器以次优顺序处理表的那些（少数）情况。

**注意：总的来说STRAIGHT\_JOIN只适用于内连接，因为left join、right join已经知道了哪个表作为驱动表，哪个表作为被驱动表，比如left join就是以左表为驱动表，right join反之，而STRAIGHT\_JOIN就是在内连接中使用，而强制使用左表来当驱动表，所以这个特性可以用于一些调优，强制改变mysql的优化器选择的执行计划**

ok，写个例子进行实验

创建部门表dept

/*Table structure for table `dept` */
    
    DROP TABLE IF EXISTS `dept`;
    
    CREATE TABLE `dept` (
      `DEPTNO` int(2) NOT NULL,
      `DNAME` varchar(14) DEFAULT NULL,
      `LOC` varchar(13) DEFAULT NULL,
      PRIMARY KEY (`DEPTNO`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    /*Data for the table `dept` */
    # 往dept表写数据
    insert  into `dept`(`DEPTNO`,`DNAME`,`LOC`) values (10,'ACCOUNTING','NEW YORK'),(20,'RESEARCH','DALLAS'),(30,'SALES','CHICAGO'),(40,'OPERATIONS','BOSTON');

创建员工emp表

DROP TABLE IF EXISTS `emp`;
    
    CREATE TABLE `emp` (
      `EMPNO` int(4) NOT NULL,
      `ENAME` varchar(10) DEFAULT NULL,
      `JOB` varchar(9) DEFAULT NULL,
      `MGR` int(4) DEFAULT NULL,
      `HIREDATE` date DEFAULT NULL,
      `SAL` int(7) DEFAULT NULL,
      `COMM` int(7) DEFAULT NULL,
      `DEPTNO` int(2) DEFAULT NULL,
      PRIMARY KEY (`EMPNO`),
      KEY `FK_DEPTNO` (`DEPTNO`),
      CONSTRAINT `FK_DEPTNO` FOREIGN KEY (`DEPTNO`) REFERENCES `dept` (`DEPTNO`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    /*Data for the table `emp` */
    # 往员工emp表写数据
    insert  into `emp`(`EMPNO`,`ENAME`,`JOB`,`MGR`,`HIREDATE`,`SAL`,`COMM`,`DEPTNO`) values (7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20),(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30),(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30),(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20),(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30),(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30),(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10),(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20),(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10),(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30),(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20),(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30),(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20),(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);

先用inner join的看看

EXPLAIN 
    SELECT 
      a.`EMPNO`,
      a.`ENAME`,
      a.`JOB`,
      a.`SAL`,
      b.`DNAME`  
    FROM
      emp a 
      INNER JOIN dept b 
        ON a.`DEPTNO` = b.`DEPTNO` 
    ORDER BY a.`EMPNO` ;

查看执行计划，可以看出是以dept表为驱动表的  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9zbWlsZW5pY2t5LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70]

ps：对于执行计划的知识可以参考我之前博客:[MySQL调优之Explain执行计划学习笔记][MySQL_Explain]

用STRAIGHT\_JOIN连接的写法：

EXPLAIN 
    SELECT 
      a.`EMPNO`,
      a.`ENAME`,
      a.`JOB`,
      a.`SAL`,
      b.`DNAME` 
    FROM
      emp a 
      STRAIGHT_JOIN dept b 
        ON a.`DEPTNO` = b.`DEPTNO` 
    ORDER BY a.`EMPNO` ;

从执行计划可以看出强制用emp表做驱动表了，而且不走索引，全表扫描了，之前的还是ref，是有走索引的  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9zbWlsZW5pY2t5LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 1]

[mysql]: https://dev.mysql.com/doc/refman/8.0/en/join.html
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9zbWlsZW5pY2t5LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70]: /images/20210813/4ba8ad0955f84ae79f67333804367b30.png
[MySQL_Explain]: https://smilenicky.blog.csdn.net/article/details/100853310
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9zbWlsZW5pY2t5LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 1]: /images/20210813/ec0f6809815148028007afc6ff9af1dd.png