表结构信息：mysql> show create table tb\G*************************** 1. row *************************** Table: tbCreate Table: CREATE TABLE `tb` ( `c` int(11) DEFAULT NULL, `d` int(4) DEFAULT NULL, `e` varchar(1000) DEFAULT NULL, KEY `c` (`c`), KEY `c_2` (`c`,`d`)) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec)
表中的数据：mysql> select * from tb;+------+------+------+| c | d | e |+------+------+------+| 2 | 40 | b || 1 | 10 | a || 2 | 30 | a || 1 | 10 | a || 3 | 30 | a || 1 | 10 | c || 1 | 50 | c || 2 | 50 | c |+------+------+------+8 rows in set (0.00 sec)
测试order by，竟然没有走上索引mysql> explain select * from tb where c > 1 order by c;+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+| 1 | SIMPLE | tb | ALL | c,c_2 | NULL | NULL | NULL | 8 | Using where; Using filesort |+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+1 row in set (0.00 sec)
使用强制索引，情况是下面这样的。这里应该是MySQL优化器的bugmysql> explain select * from tb force index (c) where c > 1 order by c;+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+| 1 | SIMPLE | tb | range | c | c | 5 | NULL | 4 | Using where |+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+1 row in set (0.00 sec)
关于 filesort 排序算法：光看 filesort 字面意思，可能以为是要利用磁盘文件进行排序，实则不全然。当MySQL不能使用索引进行排序时，就会利用自己的排序算法(快速排序算法)在内存(sort buffer)中对数据进行排序，如果内存装载不下，它会将磁盘上的数据进行分块，再对各个数据块进行排序，然后将各个块合并成有序的结果集（实际上就是外排序）。
对于filesort，MySQL有两种排序算法：1、两遍扫描算法(Two passes)实现方式是先将须要排序的字段和可以直接定位到相关行数据的指针信息取出，然后在设定的内存（通过参数 sort_buffer_size 设定）中进行排序，完成排序之后再次通过行指针信息取出所需的列。注：该算法是4.1之前只有这种算法，它需要两次访问数据，尤其是第二次读取操作会导致大量的随机I/O操作。不过，这种方法内存开销较小。
2、一次扫描算法(single pass)该算法一次性将所需的列全部取出，在内存中排序后直接将结果输出。
注：从 MySQL 4.1 版本开始支持该算法。它减少了I/O的次数，效率较高，但是内存开销也较大。如果我们将并不需要的列也取出来，就会极大地浪费排序过程所需要的内存。在 MySQL 4.1 之后的版本中，可以通过设置 max_length_for_sort_data 参数来控制 MySQL 选择第一种排序算法还是第二种。当取出的所有大字段总大小大于 max_length_for_sort_data 的设置时，MySQL 就会选择使用第一种排序算法，反之，则会选择第二种。为了尽可能地提高排序性能，我们自然更希望使用第二种排序算法，所以在SQL中仅仅取出需要的列是非常有必要的。
当对连接操作进行排序时，如果ORDER BY仅仅引用第一个表的列，MySQL对该表进行filesort操作，然后进行连接处理，此时，EXPLAIN输出“Using filesort”；否则，MySQL必须将查询的结果集生成一个临时表，在连接完成之后进行filesort操作，此时，EXPLAIN输出“Using temporary;Using filesort”。
order by 优化的一些规则：
• KEY(A,B)• Will use Index for Sorting– ORDER BY A - sorting by leading column– A=5 ORDER BY B - EQ filtering by 1st and sorting by 2nd– ORDER BY A DESC, B DESC - Sorting by 2 columns in same order – A>5 ORDER BY A - Range on the column, sorting on the same
• Will NOT use Index for Sorting– ORDER BY B - Sorting by second column in the index– A>5 ORDER BY B – Range on first column, sorting by second – A IN(1,2) ORDER BY B - In-Range on first column– ORDER BY A ASC, B DESC - Sorting in the different order
转载于:https://www.cnblogs.com/yuyue2014/p/4337956.html
