前言：order by 用好 性能提高了28倍
ps简书无法上传大于5m 的图片，只能用微信重新截图了一下上传，看着有点模糊谅解····
脚本如下：
优化前
SELECT
*
FROM
( (
SELECT
0 AS query_type,
td.pre_bill_id AS bill_id,
td.bill_code,
t.trade_no,
td.trade_money AS bill_money,
td.trade_money,
t.outputer_name,
td.inputer_name,
t.trade_type,
t.STATUS AS trade_status,
a.output_time,
a.expire_date,
t.create_time AS trade_date,
a.biz_type
FROM
bill b
JOIN asset a ON b.root_id = a.id
JOIN trade_detail td ON b.id = td.bill_id
JOIN trade t ON td.trade_no = t.trade_no
AND t.trade_type <> 1
AND t.STATUS IN ( 2, 3, 4, 5 )
WHERE
a.expire_date >= '2021-01-17'
AND a.expire_date <= '2021-07-17 23:59:59' AND t.create_time >= '2021-01-17'
AND t.create_time <= '2021-07-17 23:59:59' ) UNION ALL
(SELECT
0 AS query_type,
a.id AS bill_id,
a.bill_code,
t.trade_no,
td.trade_money AS bill_money,
td.trade_money,
t.outputer_name,
td.inputer_name,
t.trade_type,
t.STATUS AS trade_status,
a.output_time,
a.expire_date,
t.create_time AS trade_date,
a.biz_type
FROM
asset a
JOIN trade_detail td ON a.id = td.pre_bill_id
JOIN trade t ON td.trade_no = t.trade_no
AND t.trade_type = 1
AND t.STATUS IN ( 2, 3, 4, 5 )
WHERE
a.expire_date >= '2021-01-17'
AND a.expire_date <= '2021-07-17 23:59:59' AND t.create_time >= '2021-01-17'
AND t.create_time <= '2021-07-17 23:59:59' )
) tmp
ORDER BY
trade_date DESC
LIMIT 10
优化后
SELECT
*
FROM
( (
SELECT
0 AS query_type,
td.pre_bill_id AS bill_id,
td.bill_code,
t.trade_no,
td.trade_money AS bill_money,
td.trade_money,
t.outputer_name,
td.inputer_name,
t.trade_type,
t.STATUS AS trade_status,
a.output_time,
a.expire_date,
t.create_time AS trade_date,
a.biz_type
FROM
bill b
JOIN asset a ON b.root_id = a.id
JOIN trade_detail td ON b.id = td.bill_id
JOIN trade t ON td.trade_no = t.trade_no
AND t.trade_type IN ( 2, 3, 4, 5, 6, 7 )
AND t.STATUS IN ( 2, 3, 4, 5 )
WHERE
a.expire_date >= '2021-01-17'
AND a.expire_date <= '2021-07-17 23:59:59' AND t.create_time >= '2021-01-17'
AND t.create_time <= '2021-07-17 23:59:59' order by t.create_time desc limit 10) UNION ALL
(SELECT
0 AS query_type,
a.id AS bill_id,
a.bill_code,
t.trade_no,
td.trade_money AS bill_money,
td.trade_money,
t.outputer_name,
td.inputer_name,
t.trade_type,
t.STATUS AS trade_status,
a.output_time,
a.expire_date,
t.create_time AS trade_date,
a.biz_type
FROM
asset a
JOIN trade_detail td ON a.id = td.pre_bill_id
JOIN trade t ON td.trade_no = t.trade_no
AND t.trade_type = 1
AND t.STATUS IN ( 2, 3, 4, 5 )
WHERE
a.expire_date >= '2021-01-17'
AND a.expire_date <= '2021-07-17 23:59:59' AND t.create_time >= '2021-01-17'
AND t.create_time <= '2021-07-17 23:59:59' order by t.create_time desc limit 10)
) tmp
ORDER BY
trade_date DESC
LIMIT 10
如图所示：
该脚本存在几个问题：
1、多关联查询；2、多范围查询；3、多in 查询 等这些条件均不利于sql 命中索引；
从执行计划 结果得出A 驱动表没有命中到索引，扫描了143260 行 union 表A也基本上是一样的原因；最终查询limit 10耗时 2.93s。那如何将A 表命中索引使其扫描行变少；如何将derived2 扫描72112行降低 减少排序所占用内存 提高排序速度呢？
测试步骤1：
先把union 查询结果先分页 查询出10条 union
all 完后 在分页 即可把derived2 扫描行降低至20 查询时间也降低了400毫秒左右，但是这样的结果还远不是自己想要的，A 驱动表还是要扫描14万行的数据、且查询结果不符合预期。
测试步骤2：
为了保证查询结果符合预期，在子查询limit 前 先用同样的排序字段进行排序（该字段同时还是索引字段），执行完执行计划后发现驱动表A使用上了联合索引 并且由于使用了索引排序，extra 也不再是using where 而是using index condition 提高了排序性能；同样使用上了联合索引所扫描的行数降低为14169行
最总查询结果不变，查询时间从 2.93s降低为0.105s 性能提高了28倍左右；
Extra语义解析
详细的解读可以看这篇文章
https://www.cnblogs.com/kerrycode/p/9909093.html
我们看到derived2 做了一个using filesort 排序操作。即在MySQL Query Optimizer 所给出的执行计划(通过 EXPLAIN 命令查看)中被称为文件排序（filesort）
文件排序是通过相应的排序算法,将取得的数据在内存中进行排序: MySQL需要将数据在内存中进行排序，所使用的内存区域也就是我们通过sort_buffer_size 系统变量所设置的排序区。这个排序区是每个Thread 独享的，所以说可能在同一时刻在MySQL 中可能存在多个 sort buffer 内存区域。
在MySQL中filesort 的实现算法实际上是有两种：
双路排序：是首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行指针信息，然后在sort buffer 中进行排序。
单路排序：是一次性取出满足条件行的所有字段，然后在sort buffer中进行排序。
在MySQL4.1版本之前只有第一种排序算法双路排序，第二种算法是从MySQL4.1开始的改进算法，主要目的是为了减少第一次算法中需要两次访问表数据的 IO 操作，将两次变成了一次，但相应也会耗用更多的sortbuffer 空间。当然，MySQL4.1开始的以后所有版本同时也支持第一种算法，
MySQL主要通过比较我们所设定的系统参数 max_length_for_sort_data的大小和Query 语句所取出的字段类型大小总和来判定需要使用哪一种排序算法。如果 max_length_for_sort_data更大，则使用第二种优化后的算法，反之使用第一种算法。所以如果希望 ORDER BY 操作的效率尽可能的高，一定要主义max_length_for_sort_data 参数的设置。曾经就有同事的数据库出现大量的排序等待，造成系统负载很高，而且响应时间变得很长，最后查出正是因为MySQL 使用了传统的第一种排序算法而导致，在加大了max_length_for_sort_data 参数值之后，系统负载马上得到了大的缓解，响应也快了很多。
