Mysql中的STRAIGHT_JOIN和force index语句对sql的优化

1.首先转载下：火丁笔记的文件https://huoding.com/2013/06/04/261；来给大家介绍下STRAIGHT_JOIN的使用：

问题

通过「SHOW FULL PROCESSLIST」语句很容易就能查到问题SQL，如下：

SELECT post.*
FROM post
INNER JOIN post_tag ON post.id = post_tag.post_id
WHERE post.status = 1 AND post_tag.tag_id = 123
ORDER BY post.created DESC
LIMIT 100

说明：因为post和tag是多对多的关系，所以存在一个关联表post_tag。

试着用EXPLAIN查询一下SQL执行计划（篇幅所限，结果有删减）：

+----------+---------+-------+-----------------------------+
| table    | key     | rows  | Extra                       |
+----------+---------+-------+-----------------------------+
| post_tag | tag_id  | 71220 | Using where; Using filesort |
| post     | PRIMARY |     1 | Using where                 |
+----------+---------+-------+-----------------------------+

下面给出优化后的SQL，唯一的变化就是把连接方式改成了「STRAIGHT_JOIN」：

SELECT post.*
FROM post
STRAIGHT_JOIN post_tag ON post.id = post_tag.post_id
WHERE post.status = 1 AND post_tag.tag_id = 123
ORDER BY post.created DESC
LIMIT 100

试着用EXPLAIN查询一下SQL执行计划（篇幅所限，结果有删减）：

+----------+----------------+--------+-------------+
| table    | key            | rows   | Extra       |
+----------+----------------+--------+-------------+
| post     | status_created | 119340 | Using where |
| post_tag | post_id        |      1 | Using where |
+----------+----------------+--------+-------------+

对比优化前后两次EXPLAIN的结果来看，优化后的SQL虽然「rows」更大了，但是没有了「Using filesort」，综合来看，性能依然得到了提升。

提醒：注意两次EXPLAIN结果中各个表出现的先后顺序，稍后会解释。

解释

对第一条SQL而言，为什么MySQL优化器选择了一个耗时的执行方案？对第二条SQL而言，为什么把连接方式改成STRAIGHT_JOIN之后就提升了性能？

这一切还得从MySQL对多表连接的处理方式说起，首先MySQL优化器要确定以谁为驱动表，也就是说以哪个表为基准，在处理此类问题时，MySQL优化器采用了简单粗暴的解决方法：哪个表的结果集小，就以哪个表为驱动表，当然MySQL优化器实际的处理方式会复杂许多，具体可以参考：MySQL优化器如何选择索引和JOIN顺序。

说明：在EXPLAIN结果中，第一行出现的表就是驱动表。

继续post连接post_tag的例子，MySQL优化器有如下两个选择，分别是：

• 以post为驱动表，通过status_created索引过滤，结果集119340行
• 以post_tag为驱动表，通过tag_id索引过滤，结果集71220行

显而易见，post_tag过滤的结果集更小，所以MySQL优化器选择它作为驱动表，可悲催的是我们还需要以post表中的created字段来排序，也就是说排序字段不在驱动表里，于是乎不可避免的出现了「Using filesort」，甚至「Using temporary」。

知道了来龙去脉，优化起来就容易了，要尽可能的保证排序字段在驱动表中，所以必须以post为驱动表，于是乎必须借助「STRAIGHT_JOIN」强制连接顺序。

实际上在某些特殊情况里，排序字段可以不在驱动表里，比如驱动表结果集只有一行记录，并且在连接其它表时，索引除了连接字段，还包含了排序字段，此时连接表后，索引中的数据本身自然就是排好序的。

对于「STRAIGHT_JOIN」，我总觉得这种非标准的语法属于奇技淫巧的范畴，能不用尽量不用，毕竟多数情况下，MySQL优化器都能做出正确的选择。

2.首先转载下：http://blog.csdn.net/bruce128/article/details/46777567；来给大家介绍下force index的使用：

 今天写了一个统计sql，在一个近亿条数据的表上执行，200s都查不出结果。SQL如下：

[sql]  view plain  copy

 print ?

1. select customer,count(1) c  
2. from upv_**  
3. where created between "2015-07-06" and "2015-07-07"  
4. group by customer   
5. having c > 20  
6. order by c desc  

    执行explain,发现这个sql扫描了8000W条记录到磁盘上。然后再进行筛选。type=index说明整个索引树都被扫描了，效果显然不理想。

    拿着这个SQL去请教项目组的数据库大牛，仅仅加了一个force index，花了1s多就出结果了。修改后的SQL如下：

[sql]  view plain  copy

 print ?

1. select customer,count(1) c  
2. from upv_** force index(idx_created)  
3. where created between "2015-07-06" and "2015-07-07"  
4. group by customer   
5. having c > 15  
6. order by c desc   

[sql]  view plain  copy

 print ?

1.   

    同样执行以下explain命令，这个SQL仅仅扫描了磁盘的110W行记录。也就是上一个SQL的80分之一。大家都知道，扫描磁盘是很耗时的IO操作，比内存操作慢几个数量级。type=range,说明索引树仅仅被部分扫描，要优于前面那个SQL.

    除了磁盘扫描的行数的不一样，还有采用的索引的不用，上面的sql用的是联合索引，而下面的是单纯的created字段的索引。由于用的是created的索引，驱动条件就是created的区间，需要扫描的数据就立刻变小了，因为时间区间小。后面的SQL的key_len要远远小于前面的SQL，也就意味着要扫描的磁盘上的索引数据量要远远小于前面的SQL。

    第一个sql使用的是错误的索引，带来低效的查询。然后每条SQL只可能使用一个索引。通过上面的分析就可以发现，force index()指令可以指定本次查询使用哪个索引！这样就可以避免MySql优化器用到了一个低效的索引。

总结：这两个语句的使用都是为了指定更好的索引和以更好的表为基表进行提高查询效率