一场由order by和group by引发的说走就走的学习之旅

一场由order by和group by引发的说走就走的学习之旅
前两天优化SQL偶然发现了一个问题。连表查询，order by无论使用驱动表还是被驱动表的索引字段作为排序条件，执行效果都是一样的，而group by的执行效果则不一样。具体的如下图：
Order by效果一样
（1）驱动表字段为排序条件


（2）被驱动表字段为排序条件


Group by效果不一样
（1）驱动表字段为分组条件

（2）被驱动表字段为分组条件

当时觉得group by也是需要排序的，执行效果出来为什么和order by不一样呢？带着疑问分析了一下。其实最终原因很简单，就是SQL语句执行顺序以及count函数引起的。不过还是想记录下这次的学习之旅。

一、Mysql的表连接原理及优化
具体的Nested Loop Join算法可以看：https://www.cnblogs.com/JohnABC/p/7150921.html

以下内容参考：http://huoding.com/2013/06/04/261
首先MySQL优化器要确定以谁为驱动表，也就是说以哪个表为基准，在处理此类问题时，MySQL优化器采用了简单粗暴的解决方法：哪个表的结果集小，就以哪个表为驱动表，当然MySQL优化器实际的处理方式会复杂许多。上面的执行语句中，see_pay_middle_order的结果集小，所以被作为了驱动表（在EXPLAIN结果中，第一行出现的表就是驱动表）。
对于语句

的执行结果如下，

把inner join改成right join

后的执行结果如下，

比较两次执行结果，第二次ROWS更大了（本例区别不大），扫描行数更多，但是少了Using temporary和 Using filesort，效果可能反而更好，所以要尽可能的保证排序字段在驱动表中。如果不知道设置哪个表为驱动表，可以使用inner join让Mysql自己决定。
原本运行良好的查询语句，过了一段时间后，可能会突然变得很糟糕。一个很大可能的原因就是数据分布情况发生了变化，从而导致MySQL优化器对驱动表的选择发生了变化，进而出现索引失效的情况，所以没事最好多查查，关注一下这些情况。
关于BNLJ、BCK、MRR的优化可以看：https://yq.aliyun.com/articles/397850
二、Order by原理
Order by获取排序主要有两种：一种是利用索引的有序获取结果，一种是利用内存/文件排序获取结果。
（1）利用索引。利用索引是有条件的
Select 返回字段有影响，具体怎么影响还不清楚
explain select spo.send_time,spo.id from see_pay_order spo order by spo.status 是using index的
explain select spo.send_time,spo.act_id from see_pay_order spo order by spo.status是using filesort
ORDER BY中所有的列必须包含在相同的索引，并且索引的顺序和order by子句中的顺序完全一致，并且所有列的排序方向（升序或者降序）一样才有，（混合使用ASC模式和DESC模式则不使用索引）
where 语句与ORDER BY语句组合满足最左前缀
如果查询联接了多个表，只有在order by子句的所有列引用的是驱动表的列才可以
（2）利用内存/文件排序。有传统的双路排序、单路排序（减少回表操作）和针对 Order by limit M，N 语句，在空间层面做了优化的优先队列排序。优先队列排序采用了堆排序，堆排序是不稳定的，所以可能出现分页数据重复的问题，如果想在Limit存在或不存在的情况下，都保证排序结果相同，可以额外加一个排序条件。例如id字段是唯一的，可以考虑在排序字段中额外加个id排序去确保顺序稳定。

对于order by的优化主要是使其利用索引，不能利用索引的主要针对sort_buffer优化，避免因为sort buffer不够而导致需要临时文件进行归并排序的问题。

三、group by 原理
在MySQL 中，GROUP BY 的实现同样有三种：使用松散索引、使用紧凑索引和使用临时表（无索引）。具体看：http://tech.it168.com/a2009/0324/2694/000000269430_1.shtml
Group by优化点也在于尽量走索引。

四、问题解答
Select from join on group by order by 的执行顺序为from join on group by select order by。
Select 语句中包含了count函数，执行完后只有了一行，所以order by无论用驱动表还是非驱动表的字段作为条件一样。去掉count函数后如下图。使用被驱动表字段作为排序条件出现using temporary和using filesort，并且速度满慢很多。
（1）驱动表字段作为排序条件


（2）被驱动表字段作为排序条件