ClickHouse ORDER BY 排序优化 optimize_read_in_order 参数

起因

排查到线上的一条 SQL 执行的非常慢，SQL 语句如下，其中 prewhere 的限制条件有许多，这里省略了：

SELECT UUIDNumToString(info_id) as uid, * FROM people PREWHERE ((location_id = 111 AND capture_time >= 1632312295 AND capture_time <= 1632312415) 
name in ("zhenng","zheng","zhang") ORDER BY capture_time DESC

共查询了 2亿数据

其他 SQL 语句都至多 2、3s 即可返回结果，但这条语句需要 20、30s 才能执行结束。

其中name in() 中的 name 是通过如下 SQL 查出来的集合并拼到上面 SQL中的

SELECT name, count() c FROM people PREWHERE name <> 'zhenng' AND name <> 'zheng' AND ((location_id = 111 AND capture_time >= 1632312295 AND capture_time <= 1632312415) 
GROUP BY name HAVING c >= 1 ORDER BY c DESC

为什么这两条语句的查询范围相同，但是查询时间却差距这么大？

在我将第一条 SQL 的 ORDER BY 去掉之后，执行速度快了一倍还多，因此定位到可能是 SQL 语句中排序的问题。

优化 ORDER BY 排序

经过查阅资料找到如下内容：

https://github.com/ClickHouse/ClickHouse/pull/5042#issuecomment-518001463

https://clickhouse.com/docs/en/sql-reference/statements/select/order-by/#optimization-of-data-reading

主要原因就是 SQL 中 ORDER BY 排序的字段是数据表的主键，并且查询结果没有 LIMIT 子句导致的。

解决办法是通过参数 optimize_read_in_order=0 设置来关闭按照顺序读。

这个参数是排序查询优化的意思，默认情况这个参数是开启的，开启后当查询时SQL中ORDER BY的前缀和建表时的ORDER BY主键前缀一致，那么会按照索引的顺序读取数据，因为数据本身就是有序的，不需要再全局排序了，如果此时存在 LIMIT，那么只要达到LIMIT条数读取就会停止，可以看成流式的方式来完成分析，同时本来数据也是有序的，也能省掉一部分排序的占用，以此来优化性能，注意这个参数的开关与否并不影响WHERE条件中对主键的使用，大部门情况下开启参数都能起到优化的作用，但是有些情况需要注意：

• 符合条件的查询结果据最新的ORDER BY非常远，这种情况加不加优化性能相近。
• 不存在LIMIT限制，即查询全部的数据，这种情况如果关掉参数不按照顺序读则会按照大量part并行读取，反而比按照顺序读并行更高，因此性能上可能更出色，这种情况是可以关闭参数的。 所以建议在全局情况下默认保持该参数的开启，否则特定场景的SQL可以根据性能情况关闭参数，即在每个SQL后面传入即可：

SELECT ... FROM table PREWHERE ... ORDER BY <primary key> <ASC|DESC> 
SETTINGS optimize_read_in_order=0

这样就可以仅在当前SQL中关闭排序优化。