MySQLSQL排序优化

一、MySQL排序原理

1.1 MySQL如何处理排序操作？

1）当SQL中排序字段可有效利用索引时，无论是单表查询还是多表关联查询，通过索引的有序性，我们可以快速的对order by字段进行排序检索。

2）当SQL中排序字段无法有效利用索引时：

  a.对于单表查询，根据具体情况选择“一次扫描算法”或者“两遍扫描算法”进行扫描并排序

  b.对于多表关联查询，若排序字段为驱动表，则优先将驱动表结果集进行排序，然后将有序的结果集与被驱动表进行关联查询并返回结果集；若排序字段为被驱动表，只能等待驱动表与被驱动表关联得到结果集后，对该结果集进行排序。

3）对于所有的排序操作，MySQL会优先使用内存进行排序，内存排序缓存大小由参数sort_buffer_size控制，当需要排序结果集超过sort_buffer_size限制时，MySQL会将结果集分批读取到排序缓存进行排序，然后将排序后的结果集存储在磁盘临时表中，如此往复，最终将所有的排序结果集进行合并排序并返回给客户端，这种对排序的处理方式也叫做“多路合并”，可观察数据库状态参数Sort_merge_passes判断其使用消耗。

1.2 两种排序扫描算法：

1）一次扫描算法

  一次性将查询列与排序列全部读取到排序缓冲中进行排序，并将结果集返回给客户端。

2）两遍扫描算法

  首先读取排序列与其主键列到排序缓存中进行排序，根据排序后的主键列再次回表查询需要的其他列信息，并将结果集返回给客户端。

1.3 排序优化方法论

• 优先利用索引、覆盖索引的方式，利用索引的有序性进行排序；
• 多表关联排序中，尽量优先利用索引对驱动表进行排序；
• 对被驱动表字段进行排序的效果往往是最差的，此时无法通过索引进行排序优化，尽量考虑如何最大程度减少需要排序结果集大小；
• 当SQL查询数据库中Sort_merge_passes状态参数不断呈上涨趋势，可适当调整参数sort_buffer_size大小，减少多路合并对磁盘临时表的资源消耗。

二、排序优化手段

2.1 利用索引优化order by limit

1、待优化SQL

SELECT yongHuID, zhengJianLX, shenFenZH, yongHuKL, yongHuMing
	, yongHuLB, xueXiaoID, kaoDianID, zhuCeFS, zhuCeLY
	, shouJiHao, qQ, email, weiXinHao, zhuCeSJ
	, wenTi, daAn, xinXiYT, freezeFlag, mobileAuthFlag
	, fingerPasswd, useFlag, enableFlag, noAuthmobileNo, artCardFlag
	, extStr, idCardNoAuthFlag, postAuth, tipPhone, kaoShiID
	, kaoShiMC, isDeleted, createdBy, createdOn, modifiedBy
	, modifiedOn
FROM us_user_users
WHERE isDeleted = 0
ORDER BY createdOn DESC
LIMIT 15

2、优化方式以及优化效果

在排序字段createdOn字段建立索引，idx_email(createdOn)

优化后的执行计划，可以看到SQL可有效利用索引，且扫描行相对比较小。

3、总结归纳

对于一些单表查询的order by limit查询，MySQL数据库优化器一般会优先考虑通过order by字段索引进行排序优化。当where条件不具有较大过滤性时，这种做法往往是比较好的；当where条件过滤性较好时，优先通过where条件进行条件过滤优化。从MySQL 5.7.33版本之后，这种行为可以通过optimizer_switch参数中的prefer_ordering_index进行控制对于order by limit的优化。prefer_ordering_index默认打开，表示MySQL会优先考虑通过order by列索引进行排序优化。

2.2 只对驱动表进行排序

1、待优化SQL

SELECT p.product_id, p.sale_at
FROM snapshot_product snap
    STRAIGHT_JOIN product p
    ON snap.product_id = p.product_id
        AND p.status = 3
        AND p.enabled = 1
WHERE snap.promotion_id = 504
GROUP BY snap.`product_id`
ORDER BY p.product_id ASC
LIMIT 12800, 200 

执行计划可以看到，当前SQL查询是将表关联后的结果集进行排序，并且产生了磁盘排序，整体资源消耗是很大的。

2、优化方案以及优化效果

由于snap.product_id = p.product_id，所以我们将order by p.product_id asc 变更为 order by snap.product_id asc进行优化，让SQL对驱动表进行索引排序。

优化后SQL：

SELECT p.product_id, p.sale_at
FROM snapshot_product snap
    STRAIGHT_JOIN product p
    ON snap.product_id = p.product_id
        AND p.status = 3
        AND p.enabled = 1
WHERE snap.promotion_id = 504
GROUP BY snap.`product_id`
ORDER BY snap.product_id ASC 
LIMIT 12800, 200

执行计划可以看到，我们将SQL的排序列变更为驱动表后，由于驱动表中存在(promotion_id,product_id)的复合索引，可以有效利用索引进行排序，SQL完全避免了排序产生的资源消耗，SQL执行性能会有较大程度提高。

3、总结归纳

在对表关联排序的优化中，考虑到的优化点主要有：

1）利用索引优化选择小表作为驱动表；
2）尽量考虑对驱动表列进行排序，排序可有效利用索引进行排序优化；当然，即使驱动表无法有效利用索引进行排序，一般情况下也会比将驱动表与被驱动表关联后的临时表结果集进行排序的效果要好。

2.3 临时表排序

临时表排序一般是指，SQL查询必须将驱动表与被驱动表关联后的结果集进行排序，若结果集超出sort_buffer_size的大小，将会出现磁盘排序的高消耗操作，是我们SQL优化中需要尽量避免的一种情况。

1、待优化SQL

SELECT macm.version
FROM b2b_mhc_area_city_map macm
	LEFT JOIN b2b_mhc_area ma
	ON macm.area_id = ma.area_id
		AND ma.status = 1
WHERE macm.area_id > 23
	AND ma.biz_type = 1
ORDER BY ma.version DESC

2、优化方式以及优化效果

对于临时表排序，我们只能通过where过滤条件，尽量减少需要排序结果集大小进行优化，其执行计划如下：

3、总结归纳

只有当涉及到多表关联排序时才会出现使用临时表进行排序的情况。当查询是对被驱动表某字段进行排序，此时必须将表关联结果集获取到放置在临时表中，使用临时表进行排序，这种排序的方式效率是最差的。我们可以优化的只能尽可能通过过滤条件，减少临时表的中需要进行排序的数据量。

2.4 利用覆盖索引优化排序

1、测试数据构建

如表中存在复合索引(a1,a2,a3)
CREATE TABLE `tt5` (
  `a1` int(11) DEFAULT NULL,
  `a2` int(11) DEFAULT NULL,
  `a3` int(11) DEFAULT NULL,
  `a4` int(11) DEFAULT NULL,
  KEY `idx_a123` (`a1`,`a2`,`a3`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

mysql> select * from tt5;
+------+------+------+------+
| a1   | a2   | a3   | a4   |
+------+------+------+------+
|    1 |    1 |    2 |    3 |
|    1 |    2 |    3 |    4 |
|    1 |    2 |    4 |    1 |
|    2 |    2 |    4 |    1 |
|    2 |    4 |    1 |    5 |
|    4 |    3 |    1 |    5 |
|    4 |    4 |    1 |    2 |
|    3 |    4 |    1 |    2 |
|    3 |    4 |    8 |    2 |
|    5 |    4 |    8 |    2 |
|    5 |    2 |    1 |    2 |
|    5 |    2 |    7 |    0 |
|    5 |    3 |    7 |    0 |
+------+------+------+------+
13 rows in set (0.00 sec)

2、可以利用覆盖索引进行排序的情况：

Select a1 from tt5 order by a1;
Select a2 from tt5 where a1>1 order by a1;
Select a2 from tt5 where a1=1 order by a2;
Select a2 from tt5 where a1=1 order by a2,a3;
Select a2 from tt5 where a1=1 and a2>3 order by a2;
Select a2 from tt5 where a1=1 and a2>3 order by a2,a3;

3、不可以利用覆盖索引进行优化的情况：

Select a2 from tt5 where a1=2 order by a3;                  //不满足(a1,a2,a3)
Select a2 from tt5 where a1=2 and a2>3 order by a3;
Select a2 from tt5 where a1=1 order by a2 asc,a3 desc;
Select a2 from tt5 where a1=1 order by a2,a4;
Select a2 from tt5 where a1=1 and a2 in (1,2) order by a3;
Select a2 from tt5 where a1>1 order by a2;