转载 SQL优化之order by 和 group by
order by示例
示例数据：
Case 1
Case 2
Case 3
Case 4
结论：order by子句，尽量使用Index方式排序，在索引列上遵循索引的最佳左前缀原则。
复合（联合）索引形如 key (‘A1’,’A2’,’A3’ )，排序的思路一般是，先按照A1来排序，A1相同，然后按照A2排序，以此类推，这样对于（A1）,(A1,A2), (A1,A2,A3)的索引都是有效的，但是对于（A2,A3）这样的索引就无效了。尽量避免因索引字段的缺失 或 索引字段顺序的不同 引起的FileSort排序。
order by 总结
FileSort排序算法
算法一：双路排序算法
只利用ORDERBY子句中包括的列对象进行排序（适用于有BLOB、TEXT类型的列对象参与的排序）
MySQL4.1之前的排序算法，完整实现过程如下：
1) 按索引键或全表扫描的方式，读取所有的元组，不匹配WHERE子句的元组被跳过；第一步需要从存储读入数据，引发I/O操作。
2) 对于每一行，在缓冲区中存储一对值（对值，包括排序关键字和元组指针）。缓冲区的大小是系统变量的sort_buffer_size设定的值。
3) 当缓冲区已满，运行快排算法（快速排序，qsort）对一个块中的数据进行排序，将结果存储在一个临时文件。保存一个指向排序后的块的指针（如果第二步所说的对值都能被缓冲区容纳，则不会创建临时文件）。
4) 重复上述步骤，直到所有的行已经被读取。
5) 执行一个多路归并操作（操作对象是第三步生成的每一个有序的块）汇集到“MERGEBUFF域”，然后存放到在第二个临时文件中。重复操作，直到第一个文件的所有块归并后存入到第二个文件；“MERGEBUFF域”是代码sql_sort.h中定义的宏，值为7。
6) 重复以下操作（第7步和第8步），直到留下少于“MERGEBUFF2域”标明的块数为止；“MERGEBUFF2域”是代码sql_sort.h中定义的宏，值为15。
7) 在最后一次多路归并操作中，把元组的指针（排序关键字的最后部分）写入到一个结果文件。
8) 在结果文件中，按照排列的顺序使用元组指针读取元组（为了优化这项操作，MySQL读入元组指针进入一个大的块，对块中元组指针进行排序而不是直接对数据排序，然后再用有序的元组指针获取元组到元组缓存，元组缓冲区的大小由read_rnd_buffer_size参数控制）。第8步需要从存储读入数据，引发I/O操作。
算法二：单路排序算法
除利用ORDERBY子句中包括的列对象外，还利用查询目标列中的所有列对象进行排序（适用于除BLOB、TEXT类型外的所有的其他类型的排序）
MySQL4.1之后出现的改进算法，减少一次I/O，需要增加缓冲区大小容纳更多信息。其具体实现过程如下：
1) 获取与WHERE子句匹配的元组。这一步需要从存储读入数据，引发I/O操作。
2) 对于每一个元组，记录排序键值、行的位置值、查询所需的列。这一步记录更多内容，需要更大缓存，内存存储一条元组的信息的长度比算法一的“对值”大许多，这可能引发排序速度问题（排序对象的长度变长，但是内存有限，所以就需把一次内存排序变为多次，进而影响排序的速度），为了控制这个问题，MySQL引入一个参数“max_length_for_sort_data”，如果这一步得到的元组长度大于这个值，则不使用算法二。需要MySQL的使用者特别注意的是，在排序中，如果存在“很高磁盘I/O和很低的CPU利用率”的现象，则需要考虑调整“max_length_for_sort_data”的大小以变更换排序算法。
3) 按照排序的键值，对元组（元组是第二步的结果）进行排序。
算法二直接从缓冲区中的排序的元组中获取有序的列信息等（查询的目的对象），而不是第二次访问该表读取所需的列。相比算法一减少一次I/O。
FileSort优化策略
当无法使用索引列排序时，为了提高Order By的速度，应该尝试一下优化：
1、避免使用 “select * ” 。查询的字段越多导致元组长度总合可能
超过max_length_for_sort_data的设置，导致无法使用单路排序算法，只能用双路排序算法。
超过sort_buffer_size的设置，超出后会创建tmp文件进行合并，导致多次IO
2、适当增大sort_buffer_size参数的设置
3、适当增大max_length_for_sort_data参数的设置
group by 示例
示例：
group by 总结
group by与order by的索引优化基本一样，group by实质是先排序后分组，也就是分组之前必排序，遵照索引的最佳左前缀原则可以大大提高group by的效率。
当无法使用索引列排序时，适当增大sort_buffer_size参数 + 适当增大max_length_for_sort_data参数可以提高filesort排序的效率。注意：可能会出现Using temporary，也就是说mysql在对查询结果排序时使用了临时表。
where高于having，能写在where限定条件中的就尽量写在where中。
今天学习了下关于索引的最左前缀的原理，小有成就感，在这里做一个学习记录，以后学习的时候可以直接找出来复习。
相信熟悉数据库的大佬们跟索引达人们肯定都了解最索引的左前缀原理，我在这里还是再重复一下吧，文章还会结合实际例子来说明最左前缀的原理。
实验工具；mysql 5.5 + sqlyog
索引的最左前缀原理：
通常我们在建立联合索引的时候，也就是对多个字段建立索引，相信建立过索引的同学们会发现，无论是oralce还是mysql都会让我们选择索引的顺序，比如我们想在a,b,c三个字段上建立一个联合索引，我们可以选择自己想要的优先级，a、b、c,或者是b、a、c 或者是c、a、b等顺序。为什么数据库会让我们选择字段的顺序呢？不都是三个字段的联合索引么？这里就引出了数据库索引的最左前缀原理。
比如：索引index1:(a,b,c)有三个字段，我们在使用sql语句来查询的时候，会发现很多情况下不按照我们想象的来走索引。
select * from table where c = '1' 这个sql语句是不会走index1索引的，select * from table where b =‘1’ and c ='2' 这个语句也不会走index1索引。
什么语句会走index1索引呢？
答案是：
select * from table where a = '1'
select * from table where a = '1' and b = ‘2’
select * from table where a = '1' and b = ‘2’ and c='3'
我们可以发现一个共同点，就是所有走索引index1的sql语句的查询条件里面都带有a字段，那么问题来了，index1的索引的最左边的列字段是a，是不是查询条件中包含a就会走索引呢？
例如：select * from table where a = '1' and c= ‘2’这个sql语句，按照之前的理解，包含a字段，会走索引，但是是不是所有字段都走了索引呢？
我们来做个实验：
我这里有一个表：
建立了一个联合索引，prinIdAndOrder里面有三个字段 PARENT_ID, MENU_ORDER, MENU_NAME
接下来测试之前的语句：
ELECT
t.*
FROM
sys_menu t
WHERE t.`PARENT_ID` = '0'
AND t.`MENU_NAME` = '系统工具'
这一句sql就相当于之前的select * from table where a = '1' and c= ‘2’这个sql语句了，我们来看看解释计划：
可以看到走了索引prinIdAndOrder,但是旁边的key_len=303,但道理key_len应该是大于303的，为什么呢？因为PARENT_ID字段的类型是varchar(100) NULL，所以key_len=100*3+2+1=303,但是还有MENU_NAME呢！具体的key_len的计算方法，大家可以百度，我的表的字符集是utf-8，不同字符集的表的计算方式不一样。这里的解释计划显示key_len只有303，说明只是走了字段PARENT_ID的索引，没有走MENU_NAME的索引。
这也是最左前缀原理的一部分，索引index1:(a,b,c)，只会走a、a,b、a,b,c 三种类型的查询，其实这里说的有一点问题，a,c也走，但是只走a字段索引，不会走c字段。
另外还有一个特殊情况说明下，select * from table where a = '1' and b > ‘2’ and c='3' 这种类型的也只会有a与b走索引，c不会走。
原因如下：
索引是有序的，index1索引在索引文件中的排列是有序的，首先根据a来排序，然后才是根据b来排序，最后是根据c来排序，
像select * from table where a = '1' and b > ‘2’ and c='3' 这种类型的sql语句，在a、b走完索引后，c肯定是无序了，所以c就没法走索引，数据库会觉得还不如全表扫描c字段来的快。不知道我说明白没，感觉这一块说的始终有点牵强。
文中如果出现有误的地方麻烦大佬们指点。
</article>
