EXPLAIN
ウォークスルー
SQL は宣言型言語であるため、クエリが効率的に実行されているかどうかを自動的に判断することはできません。現在の実行計画を知るには、最初にEXPLAIN
ステートメントを使用する必要があります。
バイクシェア サンプル データベースからの次のステートメントは、2017 年 7 月 1 日に行われた旅行の数をカウントします。
バイクシェア サンプル データベースからの次のステートメントは、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 組み込み関数を実行できます。そのうちのcount
はその 1 つです。 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
オペレーターのコストを大幅に削減するのにも役立ちます。