MySql排序优化

order by优化

order by子句，尽量按照index的顺序排列，避免出现filesort

-- 建表
create table optimization(
    age int,
    birth timestamp not null default current_timestamp
)engine=innodb charset=utf8;

-- 插入数据
insert into optimization(age) values(22);

-- 建索引
create index idx_op_ab on optimization(age,birth);

-- 写几条语句，用explain分析
mysql> explain select * from optimization order by age desc;
+----+-------------+--------------+------------+-------+---------------+-----------+---------+------+------+----------+----------------------------------+
| id | select_type | table        | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                            |
+----+-------------+--------------+------------+-------+---------------+-----------+---------+------+------+----------+----------------------------------+
|  1 | SIMPLE      | optimization | NULL       | index | NULL          | idx_op_ab | 9       | NULL |    3 |   100.00 | Backward index scan; Using index |
+----+-------------+--------------+------------+-------+---------------+-----------+---------+------+------+----------+----------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from optimization order by birth desc;
+----+-------------+--------------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------------+
| id | select_type | table        | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                       |
+----+-------------+--------------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------------+
|  1 | SIMPLE      | optimization | NULL       | index | NULL          | idx_op_ab | 9       | NULL |    3 |   100.00 | Using index; Using filesort |
+----+-------------+--------------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------------+

mysql> explain select * from optimization where age=22 order by birth desc;
+----+-------------+--------------+------------+------+---------------+-----------+---------+-------+------+----------+-----------------------------------------------+
| id | select_type | table        | partitions | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra
 |
+----+-------------+--------------+------------+------+---------------+-----------+---------+-------+------+----------+-----------------------------------------------+
|  1 | SIMPLE      | optimization | NULL       | ref  | idx_op_ab     | idx_op_ab | 5       | const |    1 |   100.00 | Using where; Backward index scan; Using index |
+----+-------------+--------------+------------+------+---------------+-----------+---------+-------+------+----------+-----------------------------------------------+
1 row in set, 1 warning (0.00 sec)

分析

mysql支持两种方式的排序，filesort和index，前面讲过出现filesort基本上是翻车了，index效率高，mysql扫描索引本身完成排序。

order by满足两种情况下，会使用index方式排序：

• order by 后面满足最左前缀
• 使用where 子句与 order by 子句组合满足索引的最左前缀

filesort有两种算法-双路排序和单路排序

双路排序：mysql4.1之前是使用双路排序，就是扫描磁盘两次，得到数据；读取行指针和order by列，对他们进行排序，然后扫描已经排好序的列表，按照列表中的值重新从列表中读取对应的数据输出。

单路排序：从磁盘读取查询需要的索引列，按照order by列在buffer对他们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据，并且把随机IO变成了顺序IO，但它会使用更多的空间。

优化策略(了解)

调整mysql的参数

sort_buffer_size参数设置
max_length_for_sort_data参数的设置

提高order by的速度

• order by时select * 是一个大忌，需要什么拿什么（上面用来测试的表字段就两个，为了方便用了*）
  • 当查询的字段大小总和小于max_length_for_sort_data而且排序字段不是text|blob类型时，会用改进后的算法-单路排序
  • 两种算法的数据都有可能超出sort_buffer的容量，超出后，会创建tmp文件进行合并排序，导致多次IO，但使用单路排序算法的风险会大一些，索引要提高sort_buffer_size的大小
• 尝试提高sort_buffer_size
• 尝试提高max_length_for_sort_data

参考文章：https://www.cnblogs.com/wt645631686/p/8320525.html