如何在ClickHouse中查看SQL执行计划
如何在ClickHouse中查看SQL执行计划
如何在ClickHouse中查看SQL的执行计划?
这也是很多朋友经常会提到的问题，今天就尝试解答这个高频问题。
ClickHouse目前并没有直接提供EXPLAIN查询，但是借助后台的服务日志，也能变相实现EXPLAIN的功能。
例如，执行下面的语句，就能看到相应SQL的执行计划:
clickhouse-client -h ch7.nauu.com --send_logs_level=trace <<< 'SELECT * FROM hits_v1' > /dev/null本文后续部分，将包含如下内容:
1. 通过示例，解读SQL执行日志的关键信息
2. 借助SQL执行日志，进一步分析分区索引和稀疏索引，能够带来哪些优化
假设数据表 hits_v1 的关键属性如下所示:
CREATE TABLE hits_v1 ( WatchID UInt64, EventDate DATE, CounterID UInt32,...)ENGINE = MergeTree() PARTITION BY toYYYYMM(EventDate)ORDER BY CounterID SETTINGS index_granularity = 8192;其中:
分区键是 EventDate
主键是 CounterID
记住它们，后续会用到。
在写入测试数据后，这张表的数据约900w行:
SELECT COUNT(*) FROM hits_v1┌─COUNT()─┐│ 8873910 │└─────────┘
1 rows in set. Elapsed: 0.010 sec.且拥有12个分区:
SELECT partition_id ,name FROM `system`.parts WHERE `table` = 'hits_v1' AND active = 1
┌─partition_id─┬─name───────────┐│ 201403 │ 201403_1_7_1 ││ 201403 │ 201403_8_13_1 ││ 201403 │ 201403_14_19_1 ││ 201403 │ 201403_20_25_1 ││ 201403 │ 201403_26_26_0 ││ 201403 │ 201403_27_27_0 ││ 201403 │ 201403_28_28_0 ││ 201403 │ 201403_29_29_0 ││ 201403 │ 201403_30_30_0 ││ 201405 │ 201405_31_40_2 ││ 201405 │ 201405_41_41_0 ││ 201406 │ 201406_42_42_0 │└──────────────┴────────────────┘
12 rows in set. Elapsed: 0.008 sec.因为数据刚刚写入完毕，所以名为201403的分区目前存在8个，还没有最终合并成1个。
- 全字段、全表扫描
首先，执行全字段、全表扫描 SELECT * FROM hits_v，执行下面的语句:
[root@ch7 ~]# clickhouse-client -h ch7.nauu.com --send_logs_level=trace <<< 'SELECT * FROM hits_v1' > /dev/null[ch7.nauu.com] 2020.03.24 21:17:18.197960 {910ebccd-6af9-4a3e-82f4-d2291686e319} [ 45 ] <Debug> executeQuery: (from 10.37.129.15:47198) SELECT * FROM hits_v1[ch7.nauu.com] 2020.03.24 21:17:18.200324 {910ebccd-6af9-4a3e-82f4-d2291686e319} [ 45 ] <Debug> default.hits_v1 (SelectExecutor): Key condition: unknown[ch7.nauu.com] 2020.03.24 21:17:18.200350 {910ebccd-6af9-4a3e-82f4-d2291686e319} [ 45 ] <Debug> default.hits_v1 (SelectExecutor): MinMax index condition: unknown[ch7.nauu.com] 2020.03.24 21:17:18.200453 {910ebccd-6af9-4a3e-82f4-d2291686e319} [ 45 ] <Debug> default.hits_v1 (SelectExecutor): Selected 12 parts by date, 12 parts by key, 1098 marks to read from 12 ranges[ch7.nauu.com] 2020.03.24 21:17:18.205865 {910ebccd-6af9-4a3e-82f4-d2291686e319} [ 45 ] <Trace> default.hits_v1 (SelectExecutor): Reading approx. 8917216 rows with 2 streams[ch7.nauu.com] 2020.03.24 21:17:18.206333 {910ebccd-6af9-4a3e-82f4-d2291686e319} [ 45 ] <Trace> InterpreterSelectQuery: FetchColumns -> Complete[ch7.nauu.com] 2020.03.24 21:17:18.207143 {910ebccd-6af9-4a3e-82f4-d2291686e319} [ 45 ] <Debug> executeQuery: Query pipeline:Union Expression × 2 Expression MergeTreeThread
[ch7.nauu.com] 2020.03.24 21:17:46.460028 {910ebccd-6af9-4a3e-82f4-d2291686e319} [ 45 ] <Trace> UnionBlockInputStream: Waiting for threads to finish[ch7.nauu.com] 2020.03.24 21:17:46.463029 {910ebccd-6af9-4a3e-82f4-d2291686e319} [ 45 ] <Trace> UnionBlockInputStream: Waited for threads to finish[ch7.nauu.com] 2020.03.24 21:17:46.466535 {910ebccd-6af9-4a3e-82f4-d2291686e319} [ 45 ] <Information> executeQuery: Read 8873910 rows, 8.50 GiB in 28.267 sec., 313928 rows/sec., 308.01 MiB/sec.[ch7.nauu.com] 2020.03.24 21:17:46.466603 {910ebccd-6af9-4a3e-82f4-d2291686e319} [ 45 ] <Debug> MemoryTracker: Peak memory usage (for query): 340.03 MiB.现在我们分析一下，从上述日志中能够得到什么信息。
如下所示，日志中打印了该SQL的执行计划:
Union Expression × 2 Expression MergeTreeThread这条查询使用了2个线程执行，并最终通过Union合并了结果集。
该SQL没有使用主键索引:
Key condition: unknown该SQL没有使用分区索引:
MinMax index condition: unknown该SQL查询，共扫描了所有的12个分区目录，共计1098个MarkRange:
Selected 12 parts by date, 12 parts by key, 1098 marks to read from 12 ranges不明白分区目录和MarkRange的含义? 可以看看我书中的第六章MergeTree原理解析:)
该查询总共读取了8873910行数据(全表)，共8.50 G:
Read 8873910 rows, 8.50 GiB in 28.267 sec., 313928 rows/sec., 308.01 MiB/sec.该查询峰值消耗了340M内存:
MemoryTracker: Peak memory usage (for query): 340.03 MiB.接下来尝试优化查询。
- 单个字段、全表扫描
接着，还是全表扫描，但只访问1个字段 SELECT WatchID FROM hits_v，
执行下面的语句:
clickhouse-client -h ch7.nauu.com --send_logs_level=trace <<< 'SELECT WatchID FROM hits_v1' > /dev/null再次观察执行日志，你会发现该查询仍然会扫描所有的12个分区，并读取8873910行数据。
但结果集大小由之前的8.50 G降低到了现在的67.70 M:
Read 8873910 rows, 67.70 MiB in 0.195 sec., 45505217 rows/sec., 347.18 MiB/sec.且内存的峰值消耗，也从先前的340M降低为现在的17.56M:
MemoryTracker: Peak memory usage (for query): 17.56 MiB.- 使用分区索引
继续修改SQL语句，增加WHERE子句，并将分区字段EventDate作为查询条件
SELECT WatchID FROM hits_v1 WHERE EventDate = '2014-03-17',
执行下面的语句:
clickhouse-client -h ch7.nauu.com --send_logs_level=trace <<< "SELECT WatchID FROM hits_v1 WHERE EventDate = '2014-03-17'" > /dev/null这一次，你会看到执行日志发生了一些变化。
首先，WHERE子句被自动优化成了PREWHERE子句:
InterpreterSelectQuery: MergeTreeWhereOptimizer: condition "EventDate = '2014-03-17'" moved to PREWHERE关于PREWHERE，可以看看书中的第九章数据查询:)
其次，分区索引终于被启动了:
MinMax index condition: (column 0 in [16146, 16146])借助分区索引，这次查询只需要扫描9个分区目录，剪枝了3个分区:
Selected 9 parts by date, 9 parts by key, 1095 marks to read from 9 ranges总共有12个分区，还记得吗?
由于仍然没有启用主键索引，所以该查询仍然需要扫描9个分区内，所有的1095个MarkRange。
所以，最终需要读取到内存的预估数据量为8892640行:
Reading approx. 8892640 rows with 2 streams- 使用主键索引
继续修改SQL语句，在WHERE子句中，增加主键字段CounterID的过滤条件
SELECT WatchID FROM hits_v1 WHERE EventDate = '2014-03-17'
AND CounterID = 67141
执行下面的语句:
clickhouse-client -h ch7.nauu.com --send_logs_level=trace <<< "SELECT WatchID FROM hits_v1 WHERE EventDate = '2014-03-17' AND CounterID = 67141 " > /dev/null再次观察日志，你会发现在上次的基础上，主键索引也被启动了：
Key condition: (column 0 in [67141, 67141])由于启用了主键索引，需要扫描的MarkRange，由1095降低到了8个：
Selected 9 parts by date, 8 parts by key, 8 marks to read from 8 ranges所以，最终需要读取到内存的预估数据量，现在只需要65536行(8192 * 8):
Reading approx. 65536 rows with 2 streams现在你知道如何查看分析日志了吗?
好了，现在总结一下:
1. 通过将ClickHouse的服务日志，设置到DEBUG或者TRACE级别，可以变相实现EXPLAIN查询的作用，用以分析SQL的执行日志。
2. 需要真正的执行SQL查询，CH才能打印计划日志，所以如果表的数据量很大，最好借助LIMIT子句，减小查询返回的数据量。
3. 在日志中，关于分区过滤信息的部分，如下所示
Selected xxx parts by date,其中 by date 是日志固定写死的，无论我们的分区键是什么字段，这里不会变化。这是由于在早期版本，MergeTree分区键只支持日期字段。
4. 不要使用 SELECT * 全字段查询。
5. 尽可能的利用各种索引(分区索引、一级索引、二级索引)，避免全表扫描。
本文分享自 ClickHouse的秘密基地 微信公众号，前往查看
如有侵权，请联系 cloudcommunity@tencent.com 删除。
本文参与 腾讯云自媒体分享计划 ，欢迎热爱写作的你一起参与！