一、MergeTree order by执行计划

1）没有order by的执行计划：

+-------------------------------------------------------------------------------------------------+
| explain                                                                                         |
+-------------------------------------------------------------------------------------------------+
| Expression ((Projection + Before ORDER BY))                                                     |
|   Aggregating                                                                                   |
|     Expression ((Before GROUP BY + Projection))                                                 |
|       SettingQuotaAndLimits (Set limits and quota after reading from storage)                   |
|         Union                                                                                   |
|           Expression ((Convert block structure for query from local replica + Before ORDER BY)) |
|             SettingQuotaAndLimits (Set limits and quota after reading from storage)             |
|               ReadFromStorage (MergeTree)                                                       |
|           ReadFromPreparedSource (Read from remote replica)                                     |
+-------------------------------------------------------------------------------------------------+
9 rows in set (0.05 sec)

流程：

第一步：从每个机器的硬盘上把数据读取出来

第二步：union一下每个机器读取的数据

第三步：求count

2）拥有order by的执行计划

+-------------------------------------------------------------------------------------------+
| explain                                                                                   |
+-------------------------------------------------------------------------------------------+
| Expression ((Projection + Before ORDER BY))                                               |
|   Aggregating                                                                             |
|     Expression ((Before GROUP BY + Projection))                                           |
|       MergingSorted (Merge sorted streams for ORDER BY)                                   |
|         SettingQuotaAndLimits (Set limits and quota after reading from storage)           |
|           Union                                                                           |
|             Expression (Convert block structure for query from local replica)             |
|               FinishSorting                                                               |
|                 Expression (Before ORDER BY)                                              |
|                   SettingQuotaAndLimits (Set limits and quota after reading from storage) |
|                     Expression (Remove unused columns after reading from storage)         |
|                       Union                                                               |
|                         MergingSorted (Merge sorting mark ranges)                         |
|                           Expression (Calculate sorting key prefix)                       |
|                             ReadFromStorage (MergeTree with order)                        |
|                         MergingSorted (Merge sorting mark ranges)                         |
|                           Expression (Calculate sorting key prefix)                       |
|                             ReadFromStorage (MergeTree with order)                        |
|                         MergingSorted (Merge sorting mark ranges)                         |
|                           Expression (Calculate sorting key prefix)                       |
|                             ReadFromStorage (MergeTree with order) 
                       ...................
　　　　　　　　　　　　　　　　--省略一摸一样的N行
　　　　　　　　　　　　　　...................

流程：

1、从硬盘读取需要的数据（部分，因为order by需要在内存里面快速排序，无法读取全部）

2、按照order by 的key进行排序

3、N多个order by排序完的数据，在做最终汇总，然后对汇总后的数据在做排序（这一步也会根据数据量分成多步完成）

4、最终做聚合求count

二、优化

1.切换引擎

采用最适合order by的引擎：*AggregatingMergeTree

1)没有order by的执行计划

+-------------------------------------------------------------------------------------------------+
| explain                                                                                         |
+-------------------------------------------------------------------------------------------------+
| Expression ((Projection + Before ORDER BY))                                                     |
|   Aggregating                                                                                   |
|     Expression ((Before GROUP BY + Projection))                                                 |
|       SettingQuotaAndLimits (Set limits and quota after reading from storage)                   |
|         Union                                                                                   |
|           Expression ((Convert block structure for query from local replica + Before ORDER BY)) |
|             SettingQuotaAndLimits (Set limits and quota after reading from storage)             |
|               ReadFromStorage (MergeTree)                                                       |
|           ReadFromPreparedSource (Read from remote replica)                                     |
+-------------------------------------------------------------------------------------------------+
9 rows in set (0.05 sec)

不带order by的执行计划MergeTree的引擎一样。

2)拥有order by的执行计划

+-----------------------------------------------------------------------------------------------+
| explain                                                                                       |
+-----------------------------------------------------------------------------------------------+
| Expression ((Projection + Before ORDER BY))                                                   |
|   Aggregating                                                                                 |
|     Expression ((Before GROUP BY + Projection))                                               |
|       MergingSorted (Merge sorted streams for ORDER BY)                                       |
|         SettingQuotaAndLimits (Set limits and quota after reading from storage)               |
|           Union                                                                               |
|             Expression (Convert block structure for query from local replica)                 |
|               MergingSorted (Merge sorted streams for ORDER BY)                               |
|                 MergeSorting (Merge sorted blocks for ORDER BY)                               |
|                   PartialSorting (Sort each block for ORDER BY)                               |
|                     Expression (Before ORDER BY)                                              |
|                       SettingQuotaAndLimits (Set limits and quota after reading from storage) |
|                         ReadFromStorage (MergeTree)                                           |
|             ReadFromPreparedSource (Read from remote replica)                                 |
+-----------------------------------------------------------------------------------------------+
14 rows in set (0.08 sec)

分机器/分区/分段的读取数据做排序，在汇总 ， 并且是按照block进行排序的

流程：

1、对每台机器，按照一定范围进行PartialSorting（不按照具体数据排序，而是按照block块排序）

2、对第一步排序好的block进行汇总排序（因为已经知道第一步block的顺序了,所以汇总的时候直接对比最大block和最小block就可以了）

3、本地的排序好了，在做分布式的排序进行最终的汇总排序

4、求count

上面的操作类似归并排序，适应大数据量分布式排序

2.进一步对sql做优化

1)进行分段sql拆分，然后结果合并

例如原来的sql是查询3个月的数据量，那么还可以进行分段查，比如每半月/一个月 做一次order by

原来的sql：

select count(1) from 
(select 
*
from
表
where  dt >= '2022-01-01' <= '2022-03-31'
order by order_key排序条件)

优化后的sql：

select count(1) from (
    select * from 表 where  dt>='2021-01-01' and dt <= '2022-01-31' order by order_key排序条件
    union all
    select * from 表 where  dt>='2021-02-01' and dt <= '2022-02-28' order by order_key排序条件
    union all
    select *from 表 where  dt>='2021-03-01' and dt <= '2022-03-31' order by order_key排序条件
)