InnoDB引擎之Order By排序优化

今天药忘吃喽~ 2024-04-17 05:30 9阅读 0赞

**`InnoDB`引擎有两种排序方式**

1.  对返回的数据进行排序，`Explain`的`Extra`列显示`using filesort`，所有不是通过索引直接返回排序结果的排序都称为`Filesort`
2.  通过有序索引顺序扫描直接返回有序数据，`Explain`的`Extra`列显示`using index`

**`MySQL`优化排序的思路**：尽量减少额外的排序，或者通过有序索引直接返回有序数据。通常情况下，`where`条件和`order by`使用相同的索引，并且`order by`后面的索引要和索引定义的顺序保持一致，而且要保证多字段排序都是正序或倒序，否则就会出现`Filesort`。

**`Filesort`的两种排序算法**

1.  两次扫描算法  
    `MySQL4.1`之前，默认使用这种方式。大致流程：首先根据条件取出**排序字段和行指针信息**，然后在排序区`sort buffer`中排序，如果`sort buffer`大小不够，将排序结果放入`temporary table`中，完成排序后，根据行指针回表查询数据。这算法可能会有大量的`IO`操作，效率低。
2.  一次扫描算法  
    一次性将满足条件的**所有字段**放入`sort buffer`中排序，内存开销大，但排序效率比两次扫描要高。

注意区分加粗的字，**排序字段和行指针信息** 和 **所有字段** 这算是两种算法的核心。

**Filesort 无法避免时的处理方式**

`MySQL`通过比较系统变量`max_length_for_sort_data`的大小和`Query`取出的字段总大小来判断用哪种算法排序，如果`max_length_for_sort_data`足以放下所有字段，那么使用一次扫描算法。我们可以适当增大`max_length_for_sort_data`和`sort_buffer_size`系统变量扩大排序区的大小，提高排序效率。

查看大小
    show variables like 'max_length_for_sort_data';
    show variables like 'sort_buffer_size';
    
    设置当前连接的大小
    set max_length_for_sort_data = 2048;
    set sort_buffer_size = 2097152;
    设置全局大小
    set global max_length_for_sort_data = 2048;
    set global  sort_buffer_size = 2097152;