clickhouse的order by执行计划以及优化方式
一、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排序条件 )
