Mysql优化（二） -- order by及limit优化

数据：

给age和salary添加组合索引：

1. order by

1.1 通过索引排序内部流程是什么？

select name,id  from user where  name like '%明' order by name；

select name,id，age  from user where  name like '%明'

关键配置：

• sort_buffer可供排序的内存缓冲区大小
• max_length_for_sort_data 单行所有字段总和限制，超过这个大小启动双路排序

1. 通过索引检过滤筛选条件索到需要排序的字段+其他字段（如果是符合索引）
2. 判断索引内容是否覆盖select的字段
   1. 如果覆盖索引，select的字段和排序都在索引上，那么在内存中进行排序，排序后输出结果
   2. 如果索引没有覆盖查询字段，接下来计算select的字段是否超过max_length_for_sort_data限制，如果超过，启动双路排序，否则使用单路

1.2 order by的两种排序方式：

1. FileSort：通过对返回数据进行排序，所有不是通过索引直接返回排序结果的排序都叫FileSort排序
2. using index：通过有序索引顺序扫描直接返回有序数据，不需要额外排序，操作效率高

1.3 FileSort优化：

通过创建合适的索引能够减少FileSort的出现，但是在某些情况下，条件限制不能让FileSort消失，那就需要加快FileSort的排序操作。对于FileSort，Mysql有两种排序算法：

1. 两次扫描算法（双路排序）：
   • MySQL 4.1之前是使用双路排序，字面意思就是两次扫描磁盘，最终得到数据， 读取行指针和order by列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出
   • 从磁盘取排序字段，在buffer进行排序，再从磁盘取其他字段。
   • 取一批数据，要对磁盘进行两次扫描，众所周知，I\O是很耗时的，所以在mysql4.1之后，出现了第二种改进的算法，就是单路排序
2. 一次扫描算法（单路排序）：
   • 一次性取出满足条件的所有字段，然后再排序区sort buffer中排序后直接输出结果集。排序时内存开销较大，但是排序效率比两次扫描算法高

mysql通过比较系统变量max_length_for_sort_data的大小和Query语句取出的字段总大小，来判定使用哪种算法，如果max_length_for_sort_data更大，那么优先使用第二种优化后的算法，否则使用第一种。

可以适当提高sort_buffer_size和max_length_for_sort_data系统变量，来增大排序区的大小，提高排序的效率。

1.3.1 单路排序的问题：

在sort_buffer中，单路比多路要多占用很多空间，因为单路是把所有字段都取出, 所以有可能取出的数据的总大小超出了sort_buffer的容量，导致每次只能取sort_buffer容量大小的数据，进行排序（创建tmp文件，多路合并），排完再取sort_buffer容量大小，再排……从而多次I/O。

单路本来想省一次I/O操作，反而导致了大量的I/O操作，反而得不偿失。

1.4 orderby优化策略：

1. Order by时select * 是一个大忌。只Query需要的字段， 这点非常重要。在这里的影响是：

   • 当Query的字段大小总和小于max_length_for_sort_data 而且排序字段不是 TEXT|BLOB 类型时，会用改进后的算法——单路排序， 否则用老算法——多路排序。
   • 两种算法的数据都有可能超出sort_buffer的容量，超出之后，会创建tmp文件进行合并排序，导致多次I/O，但是用单路排序算法的风险会更大一些，所以要提高sort_buffer_size。

2. 尝试提高 sort_buffer_size

   • 不管用哪种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每个进程（connection）的 1M-8M之间调整。 MySQL5.7和8.0，InnoDB存储引擎默认值是1048576字节，1MB。

SHOW VARIABLES LIKE '%sort_buffer_size%';

3. 尝试提高 max_length_for_sort_data
   • 提高这个参数， 会增加用改进算法的概率。
   • 但是如果设的太高，数据总容量超出sort_buffer_size的概率就增大，明显症状是高的磁盘I/O活动和低的处理器使用率。如果需要返回的列的总长度大于max_length_for_sort_data，使用双路算法，否则使用单路算法。1024-8192字节之间调整

SHOW VARIABLES LIKE '%max_length_for_sort_data%'; 
--5.7默认1024字节
--8.0默认4096字节

1.5 orderby使用情况示例：

1.5.1 情况1：

虽然在order by上加了索引，但是还没有用到索引，因为select了非索引字段，根据我们的BTree树排序原则，这里发生了回表

解决：尽量查询覆盖索引中的字段

1.5.2 情况2：

order by后面的多个排序字段，尽量保证排序方式相同：

1.5.3 情况3：

排序字段尽量和索引排序顺序一致，即符合最左匹配原则

limit

如果我们查询select * from t_user limit 1000000,10;，这样子会查询前100万条数据，非常浪费性能。

优化思路1：

在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容

此时我们可以使用：通过id来进行优化

select * from t_user where id in （select id from t_user order by id limit 1000000,100）

优化思路2：

该方案适用于主键自增的表，可以把limit查询转换成某个位置的查询