`EXPLAIN`ウォークスルー

SQL は宣言型言語であるため、クエリが効率的に実行されたかどうかを自動的に判断することはできません。まずEXPLAINステートメントを使用して、現在の実行計画を確認する必要があります。

自転車シェアのサンプルデータベースの次のステートメントは、2017 年 7 月 1 日に行われた旅行の数をカウントします。

EXPLAIN SELECT count(*) FROM trips WHERE start_date BETWEEN '2017-07-01 00:00:00' AND '2017-07-01 23:59:59';

+------------------------------+----------+-----------+---------------+------------------------------------------------------------------------------------------------------------------------+
| id                           | estRows  | task      | access object | operator info                                                                                                          |
+------------------------------+----------+-----------+---------------+------------------------------------------------------------------------------------------------------------------------+
| StreamAgg_20                 | 1.00     | root      |               | funcs:count(Column#13)->Column#11                                                                                      |
| └─TableReader_21             | 1.00     | root      |               | data:StreamAgg_9                                                                                                       |
|   └─StreamAgg_9              | 1.00     | cop[tikv] |               | funcs:count(1)->Column#13                                                                                              |
|     └─Selection_19           | 250.00   | cop[tikv] |               | ge(bikeshare.trips.start_date, 2017-07-01 00:00:00.000000), le(bikeshare.trips.start_date, 2017-07-01 23:59:59.000000) |
|       └─TableFullScan_18     | 10000.00 | cop[tikv] | table:trips   | keep order:false, stats:pseudo                                                                                         |
+------------------------------+----------+-----------+---------------+------------------------------------------------------------------------------------------------------------------------+
5 rows in set (0.00 sec)

子演算子└─TableFullScan_18から遡ると、次のような実行プロセスがわかりますが、これは現時点では最適ではありません。

コプロセッサ (TiKV) は、 tripsテーブル全体をTableFullScan操作として読み取ります。次に、読み取った行を TiKV 内にあるSelection_19オペレーターに渡します。
次に、 WHERE start_date BETWEEN ..述語がSelection_19演算子でフィルタリングされます。約250行がこの選択を満たすと推定されます。この数値は統計とオペレーターのロジックに従って推定されることに注意してください。 └─TableFullScan_18演算子はstats:pseudoを示します。これは、テーブルに実際の統計情報がないことを意味します。 ANALYZE TABLE tripsを実行して統計情報を収集すると、統計はより正確になることが期待されます。
選択基準を満たす行にはcount関数が適用されます。これも、TiKV ( cop[tikv] ) 内にあるStreamAgg_9オペレーター内で完了します。 TiKV コプロセッサは、多数の MySQL 組み込み関数を実行できます。そのうちの 1 つはcountです。
StreamAgg_9の結果は、TiDBサーバー内にあるTableReader_21オペレーターに送信されます ( rootのタスク)。この演算子のestRows列の値は1です。これは、演算子がアクセスされる各 TiKV リージョンから 1 行を受け取ることを意味します。これらのリクエストの詳細については、 EXPLAIN ANALYZEを参照してください。
次に、 StreamAgg_20演算子は└─TableReader_21演算子からの各行にcount関数を適用します。これはSHOW TABLE REGIONSからわかるように、約 56 行になります。これはルートオペレーターであるため、結果をクライアントに返します。

注記：
テーブルに含まれるリージョンの概要を表示するには、 SHOW TABLE REGIONSを実行します。

現在のパフォーマンスを評価する

EXPLAINクエリ実行プランを返すだけで、クエリは実行されません。実際の実行時間を取得するには、クエリを実行するか、 EXPLAIN ANALYZE使用します。

EXPLAIN ANALYZE SELECT count(*) FROM trips WHERE start_date BETWEEN '2017-07-01 00:00:00' AND '2017-07-01 23:59:59';

+------------------------------+----------+----------+-----------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------+-----------+------+
| id                           | estRows  | actRows  | task      | access object | execution info                                                                                                                                                                                                                                    | operator info                                                                                                          | memory    | disk |
+------------------------------+----------+----------+-----------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------+-----------+------+
| StreamAgg_20                 | 1.00     | 1        | root      |               | time:1.031417203s, loops:2                                                                                                                                                                                                                        | funcs:count(Column#13)->Column#11                                                                                      | 632 Bytes | N/A  |
| └─TableReader_21             | 1.00     | 56       | root      |               | time:1.031408123s, loops:2, cop_task: {num: 56, max: 782.147269ms, min: 5.759953ms, avg: 252.005927ms, p95: 609.294603ms, max_proc_keys: 910371, p95_proc_keys: 704775, tot_proc: 11.524s, tot_wait: 580ms, rpc_num: 56, rpc_time: 14.111932641s} | data:StreamAgg_9                                                                                                       | 328 Bytes | N/A  |
|   └─StreamAgg_9              | 1.00     | 56       | cop[tikv] |               | proc max:640ms, min:8ms, p80:276ms, p95:480ms, iters:18695, tasks:56                                                                                                                                                                              | funcs:count(1)->Column#13                                                                                              | N/A       | N/A  |
|     └─Selection_19           | 250.00   | 11409    | cop[tikv] |               | proc max:640ms, min:8ms, p80:276ms, p95:476ms, iters:18695, tasks:56                                                                                                                                                                              | ge(bikeshare.trips.start_date, 2017-07-01 00:00:00.000000), le(bikeshare.trips.start_date, 2017-07-01 23:59:59.000000) | N/A       | N/A  |
|       └─TableFullScan_18     | 10000.00 | 19117643 | cop[tikv] | table:trips   | proc max:612ms, min:8ms, p80:248ms, p95:460ms, iters:18695, tasks:56                                                                                                                                                                              | keep order:false, stats:pseudo                                                                                         | N/A       | N/A  |
+------------------------------+----------+----------+-----------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------+-----------+------+
5 rows in set (1.03 sec)

上記のクエリ例の実行には1.03秒かかりますが、これは理想的なパフォーマンスです。

上記EXPLAIN ANALYZEの結果から、 actRows推定値 ( estRows ) の一部が不正確であることを示します (10,000 行を期待していましたが、1,900 万行が見つかりました)。これは└─TableFullScan_18のoperator info ( stats:pseudo ) ですでに示されています。最初にANALYZE TABLE実行し、次にEXPLAIN ANALYZE再度実行すると、推定値がかなり近くなることがわかります。

ANALYZE TABLE trips;
EXPLAIN ANALYZE SELECT count(*) FROM trips WHERE start_date BETWEEN '2017-07-01 00:00:00' AND '2017-07-01 23:59:59';

Query OK, 0 rows affected (10.22 sec)

+------------------------------+-------------+----------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------+-----------+------+
| id                           | estRows     | actRows  | task      | access object | execution info                                                                                                                                                                                                                                   | operator info                                                                                                          | memory    | disk |
+------------------------------+-------------+----------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------+-----------+------+
| StreamAgg_20                 | 1.00        | 1        | root      |               | time:926.393612ms, loops:2                                                                                                                                                                                                                       | funcs:count(Column#13)->Column#11                                                                                      | 632 Bytes | N/A  |
| └─TableReader_21             | 1.00        | 56       | root      |               | time:926.384792ms, loops:2, cop_task: {num: 56, max: 850.94424ms, min: 6.042079ms, avg: 234.987725ms, p95: 495.474806ms, max_proc_keys: 910371, p95_proc_keys: 704775, tot_proc: 10.656s, tot_wait: 904ms, rpc_num: 56, rpc_time: 13.158911952s} | data:StreamAgg_9                                                                                                       | 328 Bytes | N/A  |
|   └─StreamAgg_9              | 1.00        | 56       | cop[tikv] |               | proc max:592ms, min:4ms, p80:244ms, p95:480ms, iters:18695, tasks:56                                                                                                                                                                             | funcs:count(1)->Column#13                                                                                              | N/A       | N/A  |
|     └─Selection_19           | 432.89      | 11409    | cop[tikv] |               | proc max:592ms, min:4ms, p80:244ms, p95:480ms, iters:18695, tasks:56                                                                                                                                                                             | ge(bikeshare.trips.start_date, 2017-07-01 00:00:00.000000), le(bikeshare.trips.start_date, 2017-07-01 23:59:59.000000) | N/A       | N/A  |
|       └─TableFullScan_18     | 19117643.00 | 19117643 | cop[tikv] | table:trips   | proc max:564ms, min:4ms, p80:228ms, p95:456ms, iters:18695, tasks:56                                                                                                                                                                             | keep order:false                                                                                                       | N/A       | N/A  |
+------------------------------+-------------+----------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------+-----------+------+
5 rows in set (0.93 sec)

ANALYZE TABLEが実行されると、 └─TableFullScan_18演算子の推定行が正確で、 └─Selection_19の推定もかなり近づいていることがわかります。上記の 2 つのケースでは、実行計画 (TiDB がこのクエリを実行するために使用する一連の演算子) は変更されていませんが、古い統計が原因で次善の計画が発生することがよくあります。

ANALYZE TABLEに加えて、TiDB はしきい値tidb_auto_analyze_ratioに達した後、バックグラウンド操作として統計を自動的に再生成します。次のSHOW STATS_HEALTHYステートメントを実行すると、TiDB がこのしきい値にどの程度近づいているか (TiDB が統計がどの程度健全であるとみなしているか) を確認できます。

SHOW STATS_HEALTHY;

+-----------+------------+----------------+---------+
| Db_name   | Table_name | Partition_name | Healthy |
+-----------+------------+----------------+---------+
| bikeshare | trips      |                |     100 |
+-----------+------------+----------------+---------+
1 row in set (0.00 sec)

最適化を特定する

現在の実行計画は、次の点で効率的です。

作業のほとんどは TiKV コプロセッサ内で処理されます。処理のためにネットワーク経由で TiDB に送り返す必要があるのは 56 行だけです。これらの各行は短く、選択内容に一致するカウントのみが含まれます。
TiDB ( StreamAgg_20 ) と TiKV ( └─StreamAgg_9 ) の両方の行数の集計には、メモリ使用量が非常に効率的なストリーム集計が使用されます。

現在の実行計画の最大の問題は、述語start_date BETWEEN '2017-07-01 00:00:00' AND '2017-07-01 23:59:59'すぐに適用されないことです。最初にTableFullScan演算子を使用してすべての行が読み取られ、その後選択が適用されます。 SHOW CREATE TABLE tripsの出力から原因を見つけることができます。

SHOW CREATE TABLE trips\G

*************************** 1. row ***************************
       Table: trips
Create Table: CREATE TABLE `trips` (
  `trip_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `duration` int(11) NOT NULL,
  `start_date` datetime DEFAULT NULL,
  `end_date` datetime DEFAULT NULL,
  `start_station_number` int(11) DEFAULT NULL,
  `start_station` varchar(255) DEFAULT NULL,
  `end_station_number` int(11) DEFAULT NULL,
  `end_station` varchar(255) DEFAULT NULL,
  `bike_number` varchar(255) DEFAULT NULL,
  `member_type` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`trip_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=20477318
1 row in set (0.00 sec)

start_dateにはインデックスがありません。この述語をインデックスリーダー演算子にプッシュするにはインデックスが必要です。次のようにインデックスを追加します。

ALTER TABLE trips ADD INDEX (start_date);

Query OK, 0 rows affected (2 min 10.23 sec)

注記：
ADMIN SHOW DDL JOBSコマンドを使用して、DDL ジョブの進行状況を監視できます。 TiDB のデフォルトは、インデックスの追加が実本番ワークロードに大きな影響を与えないよう慎重に選択されています。テスト環境の場合は、 tidb_ddl_reorg_batch_sizeとtidb_ddl_reorg_worker_cnt値を増やすことを検討してください。リファレンスシステムでは、バッチサイズ10240とワーカー数32により、デフォルトと比較して 10 倍のパフォーマンス向上を達成できます。

インデックスを追加した後、 EXPLAINのクエリを繰り返すことができます。次の出力では、新しい実行プランが選択され、 TableFullScanとSelection演算子が削除されていることがわかります。

EXPLAIN SELECT count(*) FROM trips WHERE start_date BETWEEN '2017-07-01 00:00:00' AND '2017-07-01 23:59:59';

+-----------------------------+---------+-----------+-------------------------------------------+-------------------------------------------------------------------+
| id                          | estRows | task      | access object                             | operator info                                                     |
+-----------------------------+---------+-----------+-------------------------------------------+-------------------------------------------------------------------+
| StreamAgg_17                | 1.00    | root      |                                           | funcs:count(Column#13)->Column#11                                 |
| └─IndexReader_18            | 1.00    | root      |                                           | index:StreamAgg_9                                                 |
|   └─StreamAgg_9             | 1.00    | cop[tikv] |                                           | funcs:count(1)->Column#13                                         |
|     └─IndexRangeScan_16     | 8471.88 | cop[tikv] | table:trips, index:start_date(start_date) | range:[2017-07-01 00:00:00,2017-07-01 23:59:59], keep order:false |
+-----------------------------+---------+-----------+-------------------------------------------+-------------------------------------------------------------------+
4 rows in set (0.00 sec)

実際の実行時間を比較するには、再度EXPLAIN ANALYZEを使用します。

EXPLAIN ANALYZE SELECT count(*) FROM trips WHERE start_date BETWEEN '2017-07-01 00:00:00' AND '2017-07-01 23:59:59';

+-----------------------------+---------+---------+-----------+-------------------------------------------+------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------+-----------+------+
| id                          | estRows | actRows | task      | access object                             | execution info                                                                                                   | operator info                                                     | memory    | disk |
+-----------------------------+---------+---------+-----------+-------------------------------------------+------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------+-----------+------+
| StreamAgg_17                | 1.00    | 1       | root      |                                           | time:4.516728ms, loops:2                                                                                         | funcs:count(Column#13)->Column#11                                 | 372 Bytes | N/A  |
| └─IndexReader_18            | 1.00    | 1       | root      |                                           | time:4.514278ms, loops:2, cop_task: {num: 1, max:4.462288ms, proc_keys: 11409, rpc_num: 1, rpc_time: 4.457148ms} | index:StreamAgg_9                                                 | 238 Bytes | N/A  |
|   └─StreamAgg_9             | 1.00    | 1       | cop[tikv] |                                           | time:4ms, loops:12                                                                                               | funcs:count(1)->Column#13                                         | N/A       | N/A  |
|     └─IndexRangeScan_16     | 8471.88 | 11409   | cop[tikv] | table:trips, index:start_date(start_date) | time:4ms, loops:12                                                                                               | range:[2017-07-01 00:00:00,2017-07-01 23:59:59], keep order:false | N/A       | N/A  |
+-----------------------------+---------+---------+-----------+-------------------------------------------+------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------+-----------+------+
4 rows in set (0.00 sec)

上記の結果から、クエリ時間は 1.03 秒から 0.0 秒に短縮されました。

注記：
ここで適用されるもう 1 つの最適化は、コプロセッサキャッシュです。インデックスを追加できない場合は、コプロセッサキャッシュを有効にすることを検討してください。これが有効な場合、オペレーターが最後に実行されてからリージョンが変更されていない限り、TiKV はキャッシュから値を返します。これは、高価なTableFullScanおよびSelectionオペレータのコストの大幅な削減にも役立ちます。

サブクエリの早期実行を無効にする

クエリの最適化中に、TiDB は直接計算できるサブクエリを事前に実行します。例えば：

CREATE TABLE t1(a int);
INSERT INTO t1 VALUES(1);
CREATE TABLE t2(a int);
EXPLAIN SELECT * FROM t2 WHERE a = (SELECT a FROM t1);

+--------------------------+----------+-----------+---------------+--------------------------------+
| id                       | estRows  | task      | access object | operator info                  |
+--------------------------+----------+-----------+---------------+--------------------------------+
| TableReader_14           | 10.00    | root      |               | data:Selection_13              |
| └─Selection_13           | 10.00    | cop[tikv] |               | eq(test.t2.a, 1)               |
|   └─TableFullScan_12     | 10000.00 | cop[tikv] | table:t2      | keep order:false, stats:pseudo |
+--------------------------+----------+-----------+---------------+--------------------------------+
3 rows in set (0.00 sec)

前の例では、 a = (SELECT a FROM t1)サブクエリが最適化中に計算され、 t2.a=1として書き換えられます。これにより、最適化中の定数伝播やフォールディングなど、より多くの最適化が可能になります。ただし、 EXPLAINステートメントの実行時間には影響します。サブクエリ自体の実行に時間がかかる場合、 EXPLAINステートメントが完了しない可能性があり、オンライントラブルシューティングに影響を与える可能性があります。

v7.3.0 以降、TiDB はtidb_opt_enable_non_eval_scalar_subqueryシステム変数を導入します。これは、 EXPLAINでそのようなサブクエリの事前実行を無効にするかどうかを制御します。この変数のデフォルト値はOFFで、これはサブクエリが事前に計算されることを意味します。この変数をONに設定すると、サブクエリの事前実行を無効にすることができます。

SET @@tidb_opt_enable_non_eval_scalar_subquery = ON;
EXPLAIN SELECT * FROM t2 WHERE a = (SELECT a FROM t1);

+---------------------------+----------+-----------+---------------+---------------------------------+
| id                        | estRows  | task      | access object | operator info                   |
+---------------------------+----------+-----------+---------------+---------------------------------+
| Selection_13              | 8000.00  | root      |               | eq(test.t2.a, ScalarQueryCol#5) |
| └─TableReader_15          | 10000.00 | root      |               | data:TableFullScan_14           |
|   └─TableFullScan_14      | 10000.00 | cop[tikv] | table:t2      | keep order:false, stats:pseudo  |
| ScalarSubQuery_10         | N/A      | root      |               | Output: ScalarQueryCol#5        |
| └─MaxOneRow_6             | 1.00     | root      |               |                                 |
|   └─TableReader_9         | 1.00     | root      |               | data:TableFullScan_8            |
|     └─TableFullScan_8     | 1.00     | cop[tikv] | table:t1      | keep order:false, stats:pseudo  |
+---------------------------+----------+-----------+---------------+---------------------------------+
7 rows in set (0.00 sec)

ご覧のとおり、スカラーサブクエリは実行中に展開されないため、SQL の具体的な実行プロセスを理解しやすくなります。

注記：
tidb_opt_enable_non_eval_scalar_subqueryステートメントEXPLAINの動作にのみ影響し、ステートメントEXPLAIN ANALYZEは引き続き事前にサブクエリを実行します。

EXPLAINウォークスルー

現在のパフォーマンスを評価する

最適化を特定する

サブクエリの早期実行を無効にする

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

`EXPLAIN`ウォークスルー