📣

TiDB Cloud Serverless 现已更名为
Starter
!此页面由 AI 自动翻译,英文原文请见
此处。

使用分区解释语句

EXPLAIN 语句显示 TiDB 在执行查询时需要访问的分区。由于 partition pruning,显示的分区通常只是全部分区的一个子集。本文档描述了一些针对常见分区表的优化方法,以及如何解读 EXPLAIN 的输出。

本文档中使用的示例数据:

CREATE TABLE t1 ( id BIGINT NOT NULL auto_increment, d date NOT NULL, pad1 BLOB, pad2 BLOB, pad3 BLOB, PRIMARY KEY (id,d) ) PARTITION BY RANGE (YEAR(d)) ( PARTITION p2016 VALUES LESS THAN (2017), PARTITION p2017 VALUES LESS THAN (2018), PARTITION p2018 VALUES LESS THAN (2019), PARTITION p2019 VALUES LESS THAN (2020), PARTITION pmax VALUES LESS THAN MAXVALUE ); INSERT INTO t1 (d, pad1, pad2, pad3) VALUES ('2016-01-01', RANDOM_BYTES(1024), RANDOM_BYTES(1024), RANDOM_BYTES(1024)), ('2016-06-01', RANDOM_BYTES(1024), RANDOM_BYTES(1024), RANDOM_BYTES(1024)), ('2016-09-01', RANDOM_BYTES(1024), RANDOM_BYTES(1024), RANDOM_BYTES(1024)), ('2017-01-01', RANDOM_BYTES(1024), RANDOM_BYTES(1024), RANDOM_BYTES(1024)), ('2017-06-01', RANDOM_BYTES(1024), RANDOM_BYTES(1024), RANDOM_BYTES(1024)), ('2017-09-01', RANDOM_BYTES(1024), RANDOM_BYTES(1024), RANDOM_BYTES(1024)), ('2018-01-01', RANDOM_BYTES(1024), RANDOM_BYTES(1024), RANDOM_BYTES(1024)), ('2018-06-01', RANDOM_BYTES(1024), RANDOM_BYTES(1024), RANDOM_BYTES(1024)), ('2018-09-01', RANDOM_BYTES(1024), RANDOM_BYTES(1024), RANDOM_BYTES(1024)), ('2019-01-01', RANDOM_BYTES(1024), RANDOM_BYTES(1024), RANDOM_BYTES(1024)), ('2019-06-01', RANDOM_BYTES(1024), RANDOM_BYTES(1024), RANDOM_BYTES(1024)), ('2019-09-01', RANDOM_BYTES(1024), RANDOM_BYTES(1024), RANDOM_BYTES(1024)), ('2020-01-01', RANDOM_BYTES(1024), RANDOM_BYTES(1024), RANDOM_BYTES(1024)), ('2020-06-01', RANDOM_BYTES(1024), RANDOM_BYTES(1024), RANDOM_BYTES(1024)), ('2020-09-01', RANDOM_BYTES(1024), RANDOM_BYTES(1024), RANDOM_BYTES(1024)); INSERT INTO t1 SELECT NULL, a.d, RANDOM_BYTES(1024), RANDOM_BYTES(1024), RANDOM_BYTES(1024) FROM t1 a JOIN t1 b JOIN t1 c LIMIT 10000; INSERT INTO t1 SELECT NULL, a.d, RANDOM_BYTES(1024), RANDOM_BYTES(1024), RANDOM_BYTES(1024) FROM t1 a JOIN t1 b JOIN t1 c LIMIT 10000; INSERT INTO t1 SELECT NULL, a.d, RANDOM_BYTES(1024), RANDOM_BYTES(1024), RANDOM_BYTES(1024) FROM t1 a JOIN t1 b JOIN t1 c LIMIT 10000; INSERT INTO t1 SELECT NULL, a.d, RANDOM_BYTES(1024), RANDOM_BYTES(1024), RANDOM_BYTES(1024) FROM t1 a JOIN t1 b JOIN t1 c LIMIT 10000; SELECT SLEEP(1); ANALYZE TABLE t1;

以下示例展示了对新建分区表的查询语句:

EXPLAIN SELECT COUNT(*) FROM t1 WHERE d = '2017-06-01';
+------------------------------+---------+-----------+---------------------------+-------------------------------------------+ | id | estRows | task | access object | operator info | +------------------------------+---------+-----------+---------------------------+-------------------------------------------+ | StreamAgg_21 | 1.00 | root | | funcs:count(Column#8)->Column#6 | | └─TableReader_22 | 1.00 | root | | data:StreamAgg_10 | | └─StreamAgg_10 | 1.00 | cop[tikv] | | funcs:count(1)->Column#8 | | └─Selection_20 | 8.87 | cop[tikv] | | eq(test.t1.d, 2017-06-01 00:00:00.000000) | | └─TableFullScan_19 | 8870.00 | cop[tikv] | table:t1, partition:p2017 | keep order:false | +------------------------------+---------+-----------+---------------------------+-------------------------------------------+ 5 rows in set (0.01 sec)

从最内层(└─TableFullScan_19)操作符开始,逐步向根操作符(StreamAgg_21)回溯:

  • TiDB 成功识别出只需要访问一个分区(p2017)。这在 access object 中有所体现。
  • 该分区在操作符 └─TableFullScan_19 中被扫描,然后 └─Selection_20 被应用,用于筛选出开始日期为 2017-06-01 00:00:00.000000 的行。
  • 匹配 └─Selection_20 的行随后在协处理器中进行流式聚合,协处理器原生支持 count 函数。
  • 每个协处理请求返回一行数据到 TiDB 内部的 └─TableReader_22,然后在 StreamAgg_21 中进行流式聚合,最后将一行结果返回给客户端。

在以下示例中,分区修剪未能排除任何分区:

EXPLAIN SELECT COUNT(*) FROM t1 WHERE YEAR(d) = 2017;
+------------------------------------+----------+-----------+---------------------------+----------------------------------+ | id | estRows | task | access object | operator info | +------------------------------------+----------+-----------+---------------------------+----------------------------------+ | HashAgg_20 | 1.00 | root | | funcs:count(Column#7)->Column#6 | | └─PartitionUnion_21 | 5.00 | root | | | | ├─StreamAgg_36 | 1.00 | root | | funcs:count(Column#9)->Column#7 | | │ └─TableReader_37 | 1.00 | root | | data:StreamAgg_25 | | │ └─StreamAgg_25 | 1.00 | cop[tikv] | | funcs:count(1)->Column#9 | | │ └─Selection_35 | 6000.00 | cop[tikv] | | eq(year(test.t1.d), 2017) | | │ └─TableFullScan_34 | 7500.00 | cop[tikv] | table:t1, partition:p2016 | keep order:false | | ├─StreamAgg_55 | 1.00 | root | | funcs:count(Column#11)->Column#7 | | │ └─TableReader_56 | 1.00 | root | | data:StreamAgg_44 | | │ └─StreamAgg_44 | 1.00 | cop[tikv] | | funcs:count(1)->Column#11 | | │ └─Selection_54 | 14192.00 | cop[tikv] | | eq(year(test.t1.d), 2017) | | │ └─TableFullScan_53 | 17740.00 | cop[tikv] | table:t1, partition:p2017 | keep order:false | | ├─StreamAgg_74 | 1.00 | root | | funcs:count(Column#13)->Column#7 | | │ └─TableReader_75 | 1.00 | root | | data:StreamAgg_63 | | │ └─StreamAgg_63 | 1.00 | cop[tikv] | | funcs:count(1)->Column#13 | | │ └─Selection_73 | 3977.60 | cop[tikv] | | eq(year(test.t1.d), 2017) | | │ └─TableFullScan_72 | 4972.00 | cop[tikv] | table:t1, partition:p2018 | keep order:false | | ├─StreamAgg_93 | 1.00 | root | | funcs:count(Column#15)->Column#7 | | │ └─TableReader_94 | 1.00 | root | | data:StreamAgg_82 | | │ └─StreamAgg_82 | 1.00 | cop[tikv] | | funcs:count(1)->Column#15 | | │ └─Selection_92 | 20361.60 | cop[tikv] | | eq(year(test.t1.d), 2017) | | │ └─TableFullScan_91 | 25452.00 | cop[tikv] | table:t1, partition:p2019 | keep order:false | | └─StreamAgg_112 | 1.00 | root | | funcs:count(Column#17)->Column#7 | | └─TableReader_113 | 1.00 | root | | data:StreamAgg_101 | | └─StreamAgg_101 | 1.00 | cop[tikv] | | funcs:count(1)->Column#17 | | └─Selection_111 | 8892.80 | cop[tikv] | | eq(year(test.t1.d), 2017) | | └─TableFullScan_110 | 11116.00 | cop[tikv] | table:t1, partition:pmax | keep order:false | +------------------------------------+----------+-----------+---------------------------+----------------------------------+ 27 rows in set (0.00 sec)

从上述输出可以看出:

  • TiDB 认为需要访问所有分区(p2016..pMax)。这是因为谓词 YEAR(d) = 2017 被认为是 non-sargable。这个问题并非 TiDB 独有。
  • 每个分区在扫描时,Selection 操作符会筛选出年份不为 2017 的行。
  • 对每个分区进行流式聚合,统计匹配的行数。
  • 操作符 └─PartitionUnion_21 将访问每个分区的结果合并。

文档内容是否有帮助?