MySQL进阶探索--之STRAIGHT JOIN用法简介

一、发现问题的场景：

      在一次sql查询的时候，发现速度很慢，且主表都建立了索引。百思不得其解，explain sql语句看了一下，发现有的表并没有走索引。

      搜索文档和相关资料，发现了STRAIGHT_JOIN这种指定驱动表的方式来解决。本文章内容特记录。

 

二、引用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的强制指定左表作为驱动表，而不是让mysql优化器去判断和选择）

 

三、案例说明

创建部门表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表为驱动表的

 

 

用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，是有走索引的

 

 

ps：mysql常用的hint
       对于经常使用oracle的朋友可能知道，oracle的hint功能种类很多，对于优化sql语句提供了很多方法。同样，在mysql里，也有类似的hint功能。下面介绍一些常用的。
 

强制索引 FORCE INDEX 
    SELECT * FROM TABLE1 FORCE INDEX (FIELD1) …
以上的SQL语句只使用建立在FIELD1上的索引，而不使用其它字段上的索引。

忽略索引 IGNORE INDEX 
    SELECT * FROM TABLE1 IGNORE INDEX (FIELD1, FIELD2) …
在上面的SQL语句中，TABLE1表中FIELD1和FIELD2上的索引不被使用。 

关闭查询缓冲 SQL_NO_CACHE 
    SELECT SQL_NO_CACHE field1, field2 FROM TABLE1;
有一些SQL语句需要实时地查询数据，或者并不经常使用（可能一天就执行一两次）,这样就需要把缓冲关了,不管这条SQL语句是否被执行过，服务器都不会在缓冲区中查找，每次都会执行它。

强制查询缓冲 SQL_CACHE
    SELECT SQL_CALHE * FROM TABLE1;
如果在my.ini中的query_cache_type设成2，这样只有在使用了SQL_CACHE后，才使用查询缓冲。

优先操作 HIGH_PRIORITY
HIGH_PRIORITY可以使用在select和insert操作中，让MYSQL知道，这个操作优先进行。
    SELECT HIGH_PRIORITY * FROM TABLE1;

滞后操作 LOW_PRIORITY
LOW_PRIORITY可以使用在insert和update操作中，让mysql知道，这个操作滞后。
    update LOW_PRIORITY table1 set field1= where field1= …

延时插入 INSERT DELAYED
    INSERT DELAYED INTO table1 set field1= …
INSERT DELAYED INTO，是客户端提交数据给MySQL，MySQL返回OK状态给客户端。而这是并不是已经将数据插入表，而是存储在内存里面等待排队。当mysql有空余时，再插入。另一个重要的好处是，来自许多客户端的插入被集中在一起，并被编写入一个块。这比执行许多独立的插入要快很多。坏处是，不能返回自动递增的ID，以及系统崩溃时，MySQL还没有来得及插入数据的话，这些数据将会丢失。

强制连接顺序 STRAIGHT_JOIN
    SELECT TABLE1.FIELD1, TABLE2.FIELD2 FROM TABLE1 STRAIGHT_JOIN TABLE2 WHERE …
由上面的SQL语句可知，通过STRAIGHT_JOIN强迫MySQL按TABLE1、TABLE2的顺序连接表。如果你认为按自己的顺序比MySQL推荐的顺序进行连接的效率高的话，就可以通过STRAIGHT_JOIN来确定连接顺序。

强制使用临时表 SQL_BUFFER_RESULT
    SELECT SQL_BUFFER_RESULT * FROM TABLE1 WHERE … 
当我们查询的结果集中的数据比较多时，可以通过SQL_BUFFER_RESULT.选项强制将结果集放到临时表中，这样就可以很快地释放MySQL的表锁（这样其它的SQL语句就可以对这些记录进行查询了），并且可以长时间地为客户端提供大记录集。

分组使用临时表 SQL_BIG_RESULT和SQL_SMALL_RESULT
    SELECT SQL_BUFFER_RESULT FIELD1, COUNT(*) FROM TABLE1 GROUP BY FIELD1;
一般用于分组或DISTINCT关键字，这个选项通知MySQL，如果有必要，就将查询结果放到临时表中，甚至在临时表中进行排序。SQL_SMALL_RESULT比起SQL_BIG_RESULT差不多，很少使用。