mysql的order by 排序

mysql 的排序

1.indexsort  利用有序索引获取有序数据

原理：

我们知道，mysql的基础数据结构是B+树，任何的一个表都是一颗B+树，你在表上建的索引也是一颗B+树，B+树的特别是在叶子节点上是有序，且前一个节点存在指向相邻节点的指针。

那么在写SQL中的ORDER BY语句时候，若是ORDER BY的条件和返回的数据都在一颗树上，那么就可以利用B+树自身的特点来天然排序了，自然效率会比较高。

 

使用条件：

1)查询的WHERE子句和ORDER BY子句中查询的字段在同一颗索引树上，

2)ORDER BY 字段的顺序是跟建立索引的顺序是一致的。

3)查询的字段也在同一颗索引树

以上三个条件必须同时满足 

 

2.filesort 文件排序

原理：

这里的文件排序并不是字面那表示的意思，利用了磁盘IO来进行排序，不过是优化器告诉你，进行了一个排序操作，具体排序的地方还是内存，相对应的参数是sort_buffer_size 设定的大小

1)filesort不一定会产生临时表

2)filesort 与临时表数据写入磁盘是没有任何直接联系

 

使用情景：

任何非索引排序的场景下

 

 

3.实战：

CREATE TABLE `A_index` (

`c1` INT(11) NOT NULL AUTO_INCREMENT ,

`c2` SMALLINT(2)  ,

`c3` VARCHAR(16)  ,

`c4` VARCHAR(16)  ,

`c5` VARCHAR(16)  ,

PRIMARY KEY  (`c1`),

KEY `c2_ind` (`c2`),

KEY `c3_c4_c5_ind` (`c3`,`c4`,`c5`)

) ENGINE=INNODB DEFAULT CHARSET=utf8

 

1)查询的字段和ORDER BY 字段在同一个索引树中：

mysql> EXPLAIN SELECT c3 FROM A_index ORDER BY c3 ,c4 ,c5 ;

+----+-------------+---------+-------+---------------+--------------+---------+------+------+-------------+

| id | select_type | table   | type  | possible_keys | key          | key_len | ref  | rows | Extra       |

+----+-------------+---------+-------+---------------+--------------+---------+------+------+-------------+

|  1 | SIMPLE      | A_index | index | NULL          | c3_c4_c5_ind | 153     | NULL |    1 | Using index |

+----+-------------+---------+-------+---------------+--------------+---------+------+------+-------------+

1 row in set (0.02 sec)

 

2)查询的字段和where 及 order by 中的字段在同一颗树中

mysql> EXPLAIN SELECT c3 FROM A_index where c3="1" ORDER BY c3 ,c4 ,c5 ;

+----+-------------+---------+------+---------------+--------------+---------+-------+------+--------------------------+

| id | select_type | table   | type | possible_keys | key          | key_len | ref   | rows | Extra                    |

+----+-------------+---------+------+---------------+--------------+---------+-------+------+--------------------------+

|  1 | SIMPLE      | A_index | ref  | c3_c4_c5_ind  | c3_c4_c5_ind | 51      | const |    1 | Using where; Using index |

+----+-------------+---------+------+---------------+--------------+---------+-------+------+--------------------------+

1 row in set (0.00 sec)

对于上面的差别是做了一次回表

 

3)where 及 order by 中的字段不在同一颗树中

mysql> EXPLAIN SELECT c3 FROM A_index where c2=1 ORDER BY c3 ,c4 ,c5 ;

+----+-------------+---------+------+---------------+--------+---------+-------+------+-----------------------------+

| id | select_type | table   | type | possible_keys | key    | key_len | ref   | rows | Extra                       |

+----+-------------+---------+------+---------------+--------+---------+-------+------+-----------------------------+

|  1 | SIMPLE      | A_index | ref  | c2_ind        | c2_ind | 3       | const |    1 | Using where; Using filesort |

+----+-------------+---------+------+---------------+--------+---------+-------+------+-----------------------------+

1 row in set (0.01 sec)

在两颗索引树中后，就走的是filesort了。

 

4)查询的字段和ORDER BY 字段不在同一个索引树中：

mysql> EXPLAIN SELECT c2 FROM A_index  ORDER BY c3 ,c4 ,c5 ;

+----+-------------+---------+------+---------------+------+---------+------+------+----------------+

| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra          |

+----+-------------+---------+------+---------------+------+---------+------+------+----------------+

|  1 | SIMPLE      | A_index | ALL  | NULL          | NULL | NULL    | NULL |    1 | Using filesort |

+----+-------------+---------+------+---------------+------+---------+------+------+----------------+

1 row in set (0.00 sec)

 

 

5)查询的字段和ORDER BY 字段在同一个索引树中，但是order by 顺序不同

mysql> EXPLAIN SELECT c3 FROM A_index  ORDER BY c4 , c3  ,c5 ;

+----+-------------+---------+-------+---------------+--------------+---------+------+------+-----------------------------+

| id | select_type | table   | type  | possible_keys | key          | key_len | ref  | rows | Extra                       |

+----+-------------+---------+-------+---------------+--------------+---------+------+------+-----------------------------+

|  1 | SIMPLE      | A_index | index | NULL          | c3_c4_c5_ind | 153     | NULL |    1 | Using index; Using filesort |

+----+-------------+---------+-------+---------------+--------------+---------+------+------+-----------------------------+

1 row in set (0.00 sec)

看到排序使用的是filesort，对于c3的查找用的是索引

 

 

4.优化filesort

从上面的索引排序使用限制上可以看出，我们大部分的业务场景应该是在filesort下，那么就该想着如何优化filesort。

filesort的两种排序算法：

双路排序

1)取出所有排序的字段及对应行数据的指针信息(指向具体的叶子节点数据)

2)在内存中进行排序

3)再通过指针取得具体的数据

 

单路排序

1)去除所有排序的字段及所有行信息

2)在内存中进行排序

 

两种的差别在于第一种需要两次磁盘IO，而第二种只需要一次，但第二种需要更大的内存开销。

 

优化器如何选择主要是根据 max_length_for_sort_data 系统参数设置的大小

mysql> show variables like '%max_length%';

+--------------------------+-------+

| Variable_name            | Value |

+--------------------------+-------+

| max_length_for_sort_data | 1024  |

+--------------------------+-------+

1 row in set (0.00 sec)

 

若是查询出来的数据大小 > max_length_for_sort_data ，那么使用双路排序

若是查询出来的数据大小 < max_length_for_sort_data ，那么使用单路排序