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_18stats:pseudo表示されますが、これはテーブルに実際の統計情報が存在しないことを意味します。11ANALYZE TABLE trips実行して統計情報を収集すると、統計の精度が向上することが期待されます。 - 選択基準を満たす行には、関数
countが適用されます。これも演算子StreamAgg_9内で完了しますが、演算子 3 も TiKV 内にあります (cop[tikv])。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 ）が不正確であることを示しています（1万行と予想していたのに1900万行が検出された）。これは└─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に達した後、バックグラウンド操作として統計情報を自動的に再生成します。5 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ですべての行が読み込まれ、その後選択が適用されます。5 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)
注記：
DDLジョブの進行状況は、
ADMIN SHOW DDL JOBSコマンドを使用して監視できます。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 秒に短縮されました。
注記：
ここで適用されるもう一つの最適化は、コプロセッサキャッシュです。インデックスを追加できない場合は、 コプロセッサキャッシュ有効にすることを検討してください。有効にすると、演算子が最後に実行されてからリージョンが変更されていない限り、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ステートメントが完了しない可能性があり、オンライントラブルシューティングに影響する可能性があります。
TiDB v7.3.0以降、システム変数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_subqueryEXPLAINステートメントの動作にのみ影響し、EXPLAIN ANALYZEステートメントは引き続きサブクエリを事前に実行します。