【MySQL】MySQL STRAIGHT JOIN 使用案例以及简介

数据库管理：SQL基础及MySQL操作 #生活知识# #编程教程#

1.概述

官方：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的优化器选择的执行计划

straight_join完全等同于inner join 只不过，join语法是根据“哪个表的结果集小，就以哪个表为驱动表”来决定谁先载入的，而straight_join 会强制选择其左边的表先载入。

2.案例

参考：https://www.cnblogs.com/mzq123/p/11830429.html 这个实验在MySQL 8中已经不成立了。

创建部门表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'); 123456789101112131415

创建员工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);

1234567891011121314151617181920

先用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` ; +----+-------------+-------+------------+------+---------------+-----------+---------+-----------------------+------+----------+---------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+-----------+---------+-----------------------+------+----------+---------------------------------+ | 1 | SIMPLE | b | NULL | ALL | PRIMARY | NULL | NULL | NULL | 4 | 100.00 | Using temporary; Using filesort | | 1 | SIMPLE | a | NULL | ref | FK_DEPTNO | FK_DEPTNO | 5 | explain_show.b.DEPTNO | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+-----------+---------+-----------------------+------+----------+---------------------------------+

12345678910111213141516171819

查看执行计划，可以看出是以dept表为驱动表的，id相同的时候，顺序执行.
explain规则：explain语句结果中，第一行为驱动表（该定律适用于join；子查询的话要分情况）
用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` ; 1234567891011121314

从执行计划可以看出强制用emp表做驱动表了.

+----+-------------+-------+------------+--------+---------------+---------+---------+-----------------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+--------+---------------+---------+---------+-----------------------+------+----------+-------------+ | 1 | SIMPLE | a | NULL | index | FK_DEPTNO | PRIMARY | 4 | NULL | 14 | 100.00 | Using where | | 1 | SIMPLE | b | NULL | eq_ref | PRIMARY | PRIMARY | 4 | explain_show.a.DEPTNO | 1 | 100.00 | NULL | +----+-------------+-------+------------+--------+---------------+---------+---------+-----------------------+------+----------+-------------+ 123456

网址：【MySQL】MySQL STRAIGHT JOIN 使用案例以及简介 https://www.yuejiaxmz.com/news/view/431881

相关内容

MySQL索引原理及慢查询优化(转载)
MySQL慢查询优化
MySQL上亿数据查询优化：实践与技巧
Navicat for MySQL安装及使用教程
MySQL 之压力测试工具的使用方法
日常工作中如何做MySQL优化？
MySQL——MySQL SELECT查询非分组聚合列（sql
Mysqlslap MySQL压力测试工具 简单教程
mysql （8）=====用户授权管理
MySQL实现序列（Sequence）效果以及在Mybatis中如何使用这种策略

随便看看