专栏首页ClickHouse的秘密基地如何在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 删除。
登录 后参与评论
0 条评论

相关文章

  • MySQL(九)|如何查看执行计划(Explain)

    我们经常会使用Explain去查看执行计划,这个众所周知。但我在面试时问面试者,你用Explain主要是看什么?对方的回答大多是“查看是否有使用到索引”,很显然...

    黄小怪
  • 如何使用 EXPLAIN 精准查看执行计划?

    在上一篇中 如何使用慢查询快速定位执行慢的 SQL?定位了查询慢的 SQL 之后,我们就可以使用 EXPLAIN 工具做针对性的分析,比如我们想要了解 prod...

    码农架构
  • 查看SQL执行计划的方法及优劣

    作者 | 胡佳伟:云和恩墨技术工程师,有多年数据库优化经验,在一线执行过多个包括通信、保险等行业的优化项目。

    数据和云
  • PostgreSQL查询当前执行中SQL的执行计划——pg_show_plans

    如果同样的SQL要执行很多遍,且每次都是同样的执行计划、每次都发生硬解析,则会消耗大量时间。类似于Oracle存放执行计划的library cache,PG也有...

    数据和云
  • 从 Clickhouse 到 Snowflake: MPP 查询层

    导语 | 伴随着Snowflake的成功,重新激活了数据分析市场,大大小小的创业公司不断创立,各种OLAP的开源产品层出不穷。其中,ClickHouse凭借优秀...

    腾讯 架构师
  • Clickhouse如何分析sql查询计划完整指南

    ClickHouse20.6之前目前并没有直接提供EXPLAIN查询,但是借助后台的服务日志,能变相实现该功能。

    公众号-利志分享
  • Mysql中explain命令查看执行计划

    使用explain命令可以查看一条查询语句的执行计划,这篇文章记录一下查询计划的各个属性的值极其含义.

    呼延十
  • Oracle之SQL优化专题03-如何看懂SQL的执行计划

    专题第一篇《Oracle之SQL优化专题01-查看SQL执行计划的方法》讲到了查看SQL执行计划的方法,并介绍了各种方法的应用场景,那么这一篇就主要介绍下如何看...

    Alfred Zhao
  • 看云上 ClickHouse 如何做计算存储分离

    12月6日-7日,由InfoQ 中国主办的综合性技术盛会QCon全球软件开发大会深圳站召开。QCon 内容源于实践并面向社区,演讲嘉宾依据热点话题,面向资深的技...

    腾讯QQ大数据
  • 看云上 ClickHouse 如何做计算存储分离

    12月6日-7日,由InfoQ 中国主办的综合性技术盛会QCon全球软件开发大会深圳站召开。QCon 内容源于实践并面向社区,演讲嘉宾依据热点话题,面向资深的技...

    腾讯云大数据
  • 如何查看Django ORM执行的SQL语句

    Django ORM对数据库操作的封装相当完善,日常大部分数据库操作都可以通过ORM实现。

    BigYoung小站
  • 【DB笔试面试599】在Oracle中,如何在不执行SQL的情况下获取执行计划?

    1、“EXPLAIN PLAN FOR SQL”不实际执行SQL语句,生成的计划未必是真实执行的计划。但是,必须要有PLAN_TABLE表,可以执行脚本“@?/...

    小麦苗DBA宝典
  • 尝鲜ClickHouse原生EXPLAIN查询功能

    在之前的一篇文章中,我曾经介绍过如何借助 ClickHouse 的服务日志查看 SQL 执行计划,从而变相达到 EXPLAIN 查询的目的。

    Nauu
  • 【DB笔试面试600】在Oracle中,如何获取SQL历史执行计划?

    历史执行计划只能从AWR中获取,如果AWR没有记录的话,那么就无法获取历史执行计划了,获取历史执行计划的命令如下所示:

    小麦苗DBA宝典
  • ClickHouse 在有赞的实践之路

    本文主要介绍了 ClickHouse 的简单原理,有赞 OLAP 相关组件以及 ClickHouse 在有赞的实践之路。

    有赞coder
  • ClickHouse 在有赞的实践之路

    本文主要介绍了 ClickHouse 的简单原理,有赞 OLAP 相关组件以及 ClickHouse 在有赞的实践之路。

    用户1278550
  • 选择适合你的开源 OLAP 引擎

    摘要:本文主要介绍了主流开源的OLAP引擎:Hive、Sparksql、Presto、Kylin、Impala、Druid、Clickhouse 等,逐一介绍了...

    大数据学习指南
  • ClickHouse原理解析与应用实践

    yeedomliu

扫码关注云+社区

领取腾讯云代金券