集計を使用してステートメントを説明する

データを集計する場合、SQL オプティマイザーはハッシュ集計またはストリーム集計演算子のいずれかを選択します。クエリの効率を向上させるために、集計はコプロセッサと TiDB レイヤーの両方で実行されます。次の例を検討してください。

CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY auto_increment, pad1 BLOB, pad2 BLOB, pad3 BLOB); INSERT INTO t1 SELECT NULL, RANDOM_BYTES(1024), RANDOM_BYTES(1024), RANDOM_BYTES(1024) FROM dual; INSERT INTO t1 SELECT NULL, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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;

SHOW TABLE REGIONSの出力から、このテーブルが複数のリージョンに分割されていることがわかります。

SHOW TABLE t1 REGIONS;
+-----------+--------------+--------------+-----------+-----------------+-------+------------+---------------+------------+----------------------+------------------+ | REGION_ID | START_KEY | END_KEY | LEADER_ID | LEADER_STORE_ID | PEERS | SCATTERING | WRITTEN_BYTES | READ_BYTES | APPROXIMATE_SIZE(MB) | APPROXIMATE_KEYS | +-----------+--------------+--------------+-----------+-----------------+-------+------------+---------------+------------+----------------------+------------------+ | 64 | t_64_ | t_64_r_31766 | 65 | 1 | 65 | 0 | 1325 | 102033520 | 98 | 52797 | | 66 | t_64_r_31766 | t_64_r_63531 | 67 | 1 | 67 | 0 | 1325 | 72522521 | 104 | 78495 | | 68 | t_64_r_63531 | t_64_r_95296 | 69 | 1 | 69 | 0 | 1325 | 0 | 104 | 95433 | | 2 | t_64_r_95296 | | 3 | 1 | 3 | 0 | 1501 | 0 | 81 | 63211 | +-----------+--------------+--------------+-----------+-----------------+-------+------------+---------------+------------+----------------------+------------------+ 4 rows in set (0.00 sec)

EXPLAIN次の集計ステートメントで使用すると、最初に TiKV 内の各リージョンで└─StreamAgg_8実行されることがわかります。次に、各 TiKVリージョンは 1 行を TiDB に送り返し、TiDB は各リージョンからのデータをStreamAgg_16で集計します。

EXPLAIN SELECT COUNT(*) FROM t1;
+----------------------------+-----------+-----------+---------------+---------------------------------+ | id | estRows | task | access object | operator info | +----------------------------+-----------+-----------+---------------+---------------------------------+ | StreamAgg_16 | 1.00 | root | | funcs:count(Column#7)->Column#5 | | └─TableReader_17 | 1.00 | root | | data:StreamAgg_8 | | └─StreamAgg_8 | 1.00 | cop[tikv] | | funcs:count(1)->Column#7 | | └─TableFullScan_15 | 242020.00 | cop[tikv] | table:t1 | keep order:false | +----------------------------+-----------+-----------+---------------+---------------------------------+ 4 rows in set (0.00 sec)

これはEXPLAIN ANALYZEで最も簡単に確認できます。ここでは、 TableFullScanが使用されており、セカンダリ インデックスがないため、 actRows SHOW TABLE REGIONSのリージョン数と一致しています。

EXPLAIN ANALYZE SELECT COUNT(*) FROM t1;
+----------------------------+-----------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------+-----------+------+ | id | estRows | actRows | task | access object | execution info | operator info | memory | disk | +----------------------------+-----------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------+-----------+------+ | StreamAgg_16 | 1.00 | 1 | root | | time:12.609575ms, loops:2 | funcs:count(Column#7)->Column#5 | 372 Bytes | N/A | | └─TableReader_17 | 1.00 | 4 | root | | time:12.605155ms, loops:2, cop_task: {num: 4, max: 12.538245ms, min: 9.256838ms, avg: 10.895114ms, p95: 12.538245ms, max_proc_keys: 31765, p95_proc_keys: 31765, tot_proc: 48ms, rpc_num: 4, rpc_time: 43.530707ms, copr_cache_hit_ratio: 0.00} | data:StreamAgg_8 | 293 Bytes | N/A | | └─StreamAgg_8 | 1.00 | 4 | cop[tikv] | | proc max:12ms, min:12ms, p80:12ms, p95:12ms, iters:122, tasks:4 | funcs:count(1)->Column#7 | N/A | N/A | | └─TableFullScan_15 | 242020.00 | 121010 | cop[tikv] | table:t1 | proc max:12ms, min:12ms, p80:12ms, p95:12ms, iters:122, tasks:4 | keep order:false | N/A | N/A | +----------------------------+-----------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------+-----------+------+ 4 rows in set (0.01 sec)

ハッシュ集計

ハッシュ集計アルゴリズムは、集約の実行中にハッシュ テーブルを使用して中間結果を保存します。複数のスレッドを使用して並列に実行されますが、ストリーム集計よりも多くのメモリを消費します。

以下はHashAgg演算子の例です。

EXPLAIN SELECT /*+ HASH_AGG() */ count(*) FROM t1;
+---------------------------+-----------+-----------+---------------+---------------------------------+ | id | estRows | task | access object | operator info | +---------------------------+-----------+-----------+---------------+---------------------------------+ | HashAgg_9 | 1.00 | root | | funcs:count(Column#6)->Column#5 | | └─TableReader_10 | 1.00 | root | | data:HashAgg_5 | | └─HashAgg_5 | 1.00 | cop[tikv] | | funcs:count(1)->Column#6 | | └─TableFullScan_8 | 242020.00 | cop[tikv] | table:t1 | keep order:false | +---------------------------+-----------+-----------+---------------+---------------------------------+ 4 rows in set (0.00 sec)

operator info 、データを集約するために使用されるハッシュ関数がfuncs:count(1)->Column#6であることを示します。

ストリーム集計

ストリーム集計アルゴリズムは通常、ハッシュ集計よりもメモリ消費量が少なくなります。ただし、この演算子では、データが到着するとストリームして集約を適用できるように、データが順序どおりに送信されることが必要です。

次の例を考えてみましょう。

CREATE TABLE t2 (id INT NOT NULL PRIMARY KEY, col1 INT NOT NULL); INSERT INTO t2 VALUES (1, 9),(2, 3),(3,1),(4,8),(6,3); EXPLAIN SELECT /*+ STREAM_AGG() */ col1, count(*) FROM t2 GROUP BY col1;
Query OK, 0 rows affected (0.11 sec) Query OK, 5 rows affected (0.01 sec) Records: 5 Duplicates: 0 Warnings: 0 +------------------------------+----------+-----------+---------------+---------------------------------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +------------------------------+----------+-----------+---------------+---------------------------------------------------------------------------------------------+ | Projection_4 | 8000.00 | root | | test.t2.col1, Column#3 | | └─StreamAgg_8 | 8000.00 | root | | group by:test.t2.col1, funcs:count(1)->Column#3, funcs:firstrow(test.t2.col1)->test.t2.col1 | | └─Sort_13 | 10000.00 | root | | test.t2.col1 | | └─TableReader_12 | 10000.00 | root | | data:TableFullScan_11 | | └─TableFullScan_11 | 10000.00 | cop[tikv] | table:t2 | keep order:false, stats:pseudo | +------------------------------+----------+-----------+---------------+---------------------------------------------------------------------------------------------+ 5 rows in set (0.00 sec)

この例では、 col1にインデックスを追加することで└─Sort_13演算子を削除できます。インデックスが追加されると、データが順番に読み取られ、 └─Sort_13演算子が削除されます。

ALTER TABLE t2 ADD INDEX (col1); EXPLAIN SELECT /*+ STREAM_AGG() */ col1, count(*) FROM t2 GROUP BY col1;
Query OK, 0 rows affected (0.28 sec) +------------------------------+---------+-----------+----------------------------+----------------------------------------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +------------------------------+---------+-----------+----------------------------+----------------------------------------------------------------------------------------------------+ | Projection_4 | 4.00 | root | | test.t2.col1, Column#3 | | └─StreamAgg_14 | 4.00 | root | | group by:test.t2.col1, funcs:count(Column#4)->Column#3, funcs:firstrow(test.t2.col1)->test.t2.col1 | | └─IndexReader_15 | 4.00 | root | | index:StreamAgg_8 | | └─StreamAgg_8 | 4.00 | cop[tikv] | | group by:test.t2.col1, funcs:count(1)->Column#4 | | └─IndexFullScan_13 | 5.00 | cop[tikv] | table:t2, index:col1(col1) | keep order:true, stats:pseudo | +------------------------------+---------+-----------+----------------------------+----------------------------------------------------------------------------------------------------+ 5 rows in set (0.00 sec)

ROLLUP による多次元データ集約

v7.4.0 以降、TiDB のGROUP BY句はWITH ROLLUP修飾子をサポートします。

GROUP BY句では、1 つ以上の列をグループ リストとして指定し、リストの後にWITH ROLLUP修飾子を追加できます。すると、TiDB はグループ リスト内の列に基づいて多次元降順グループ化を実行し、出力で各グループの要約結果を提供します。

注記:

現在、TiDB は Cube 構文をサポートしておらず、TiDB はTiFlash MPP モードでのみWITH ROLLUP構文の有効な実行プランの生成をサポートしています。

explain SELECT year, month, grouping(year), grouping(month), SUM(profit) AS profit FROM bank GROUP BY year, month WITH ROLLUP; +----------------------------------------+---------+--------------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +----------------------------------------+---------+--------------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | TableReader_44 | 2.40 | root | | MppVersion: 2, data:ExchangeSender_43 | | └─ExchangeSender_43 | 2.40 | mpp[tiflash] | | ExchangeType: PassThrough | | └─Projection_8 | 2.40 | mpp[tiflash] | | Column#6->Column#12, Column#7->Column#13, grouping(gid)->Column#14, grouping(gid)->Column#15, Column#9->Column#16 | | └─Projection_38 | 2.40 | mpp[tiflash] | | Column#9, Column#6, Column#7, gid | | └─HashAgg_36 | 2.40 | mpp[tiflash] | | group by:Column#6, Column#7, gid, funcs:sum(test.bank.profit)->Column#9, funcs:firstrow(Column#6)->Column#6, funcs:firstrow(Column#7)->Column#7, funcs:firstrow(gid)->gid, stream_count: 8 | | └─ExchangeReceiver_22 | 3.00 | mpp[tiflash] | | stream_count: 8 | | └─ExchangeSender_21 | 3.00 | mpp[tiflash] | | ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: Column#6, collate: binary], [name: Column#7, collate: utf8mb4_bin], [name: gid, collate: binary], stream_count: 8 | | └─Expand_20 | 3.00 | mpp[tiflash] | | level-projection:[test.bank.profit, <nil>->Column#6, <nil>->Column#7, 0->gid],[test.bank.profit, Column#6, <nil>->Column#7, 1->gid],[test.bank.profit, Column#6, Column#7, 3->gid]; schema: [test.bank.profit,Column#6,Column#7,gid] | | └─Projection_16 | 3.00 | mpp[tiflash] | | test.bank.profit, test.bank.year->Column#6, test.bank.month->Column#7 | | └─TableFullScan_17 | 3.00 | mpp[tiflash] | table:bank | keep order:false, stats:pseudo | +----------------------------------------+---------+--------------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 10 rows in set (0.05 sec)

前のステートメントのGROUP BY year, month WITH ROLLUP構文に従って、このステートメントの SQL 集計結果は、それぞれ{year, month}{year}{} 3 つのグループに計算され、連結されます。

詳細についてはGROUP BY 修飾子参照してください。

このページは役に立ちましたか?