MySQL基础之STRAIGHT JOIN用法简介
引用mysql官方手册的说法:
翻译过来就是: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表为驱动表的
ps:对于执行计划的知识可以参考我之前博客: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,是有走索引的