mysql的order by 排序

MySQL排序优化

最新推荐文章于 2024-07-31 23:58:16 发布

原创最新推荐文章于 2024-07-31 23:58:16 发布 · 458 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #数据结构与算法

MYSQL 专栏收录该内容

2 篇文章

订阅专栏

本文详细介绍了MySQL中的两种排序方式：indexsort和filesort，并探讨了它们的工作原理、使用条件及优化方法。

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 ;

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

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

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

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 ;

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

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

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

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 ;

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

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

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

1 row in set (0.01 sec)

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

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

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

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

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

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

1 row in set (0.00 sec)

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

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

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

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

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

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 ，那么使用单路排序