# 演習 3: コストベースのオプティマイザー ## 概要 この演習では、TiDBのオプティマイザーの実行計画を複数の異なるSELECTクエリに対してEXPLAINおよびEXPLAIN ANALYZEコマンドを使用して調査します。 ### 所要時間 + この演習は約 60 分程度かかります。 > [!TIP] > 演習環境がデプロイされたら、下のバーの**次のモジュール**をクリックして**モジュール 1**を開始します。 ## モジュール 1: データ準備 + この演習では、後続の実験のためにTiDBデータベースにより多くの実験データをインポートします。universeというデータベースを作成します。 ### 手順 1. リモート Linux にログインします + ここをクリック - [ターミナル 1](ssh://monitor1-ssh#1) で、ハンズオン演習用に割り当てられた仮想マシンに接続された新しいターミナルを開きます。 + ログイン後に表示されるバナーは以下のようになります。 ```output , #_ ~\_ ####_ Amazon Linux 2 ~~ \_#####\ ~~ \###| ~~ \#/ ___ ~~ V~' '-> ~~~ / A newer version of Amazon Linux is available! ~~._. _/ _/ _/ Amazon Linux 2023, GA and supported until 2028-03-15. _/m/' https://aws.amazon.com/linux/amazon-linux-2023/ ``` ### 手順 2. TiDB への接続と `test` データベースの使用 ```sql cd universe/ ``` ```sql mysql -h ${HOST_DB1_PRIVATE_IP} --port 4000 -u root ``` ```sql use test; ``` ```output Database changed ``` ### 手順 3. データのインポート ```sql source universeV2.sql ``` ```output Query OK, 0 rows affected, 1 warning (2.03 sec) ``` ### 手順 4. `universe` を現在のデータベースにする ```sql use universe; ``` ```output Database changed ``` ### 手順 5. インポートしたデータの確認 ```sql SHOW TABLES; SELECT COUNT(*) FROM stars; SELECT COUNT(*) FROM planets; SELECT COUNT(*) FROM moons; SELECT COUNT(*) FROM planet_categories; SELECT COUNT(*) FROM discoveryFacility; SELECT COUNT(*) FROM parameterReference; SELECT COUNT(*) FROM planetarySystem; ``` ```output tidb>show tables; +--------------------+ | Tables_in_universe | +--------------------+ | discoveryFacility | | moons | | parameterReference | | planet_categories | | planetarySystem | | planets | | stars | +--------------------+ 7 rows in set (0.00 sec) tidb>select count(*) from stars; +----------+ | count(*) | +----------+ | 2 | +----------+ 1 row in set (0.00 sec) tidb>select count(*) from planets; +----------+ | count(*) | +----------+ | 10 | +----------+ 1 row in set (0.00 sec) tidb>select count(*) from moons; +----------+ | count(*) | +----------+ | 28 | +----------+ 1 row in set (0.00 sec) tidb>select count(*) from planet_categories; +----------+ | count(*) | +----------+ | 3 | +----------+ 1 row in set (0.00 sec) tidb>select count(*) from discoveryFacility; +----------+ | count(*) | +----------+ | 70 | +----------+ 1 row in set (0.00 sec) tidb>select count(*) from parameterReference; +----------+ | count(*) | +----------+ | 2055 | +----------+ 1 row in set (0.00 sec) tidb>select count(*) from planetarySystem; +----------+ | count(*) | +----------+ | 34713 | +----------+ 1 row in set (0.02 sec) ``` ## モジュール 2: EXPLAIN コマンドの使用 + この演習では、TiDBのオプティマイザーの実行計画を複数の異なるSELECTクエリに対してEXPLAINコマンドを使用して調査します。 ### 手順 1. 次のステートメントを実行してEXPLAIN出力を表示 ```sql EXPLAIN SELECT * FROM parameterReference WHERE id=100; ``` ```output tidb>EXPLAIN SELECT * FROM parameterReference WHERE id=100; +-------------+---------+------+--------------------------+---------------+ | id | estRows | task | access object | operator info | +-------------+---------+------+--------------------------+---------------+ | Point_Get_1 | 1.00 | root | table:parameterReference | handle:100 | +-------------+---------+------+--------------------------+---------------+ 1 row in set (0.00 sec) ``` + このクエリのアクセス方法は何ですか？ + 答え: `Point_Get` + TiDBはこのクエリを実行するために何行を読む必要がありますか？ + 答え: 1 + このアクセス戦略は良好なパフォーマンスをもたらしますか？理由を述べてください。 + 答え: はい。`Point_Get` タイプは、`PRIMARY KEY` または `UNIQUE Index` のすべての部分を定数値と比較するときに適用されます。この例では、テーブルには最大で1つの一致する行が存在します。オプティマイザーはこの行のカラム値を定数として扱い、それらを一度だけ読み取ります。TiDBでは、`Point_Get` は実行計画を生成するためのコンパイラを必要とせず、データがTiKVにキーバリュー形式で格納されているため、`WHERE` 条件がプライマリキーまたはユニークインデックスである場合に `Point_Get` を使用して直接データを取得することができます。 ### 手順 2. 次のステートメントを実行してEXPLAIN出力を表示 ```sql EXPLAIN SELECT * FROM parameterReference WHERE id=100+5; ``` ```output tidb>EXPLAIN SELECT * FROM parameterReference WHERE id=100+5; +-------------+---------+------+--------------------------+---------------+ | id | estRows | task | access object | operator info | +-------------+---------+------+--------------------------+---------------+ | Point_Get_5 | 1.00 | root | table:parameterReference | handle:105 | +-------------+---------+------+--------------------------+---------------+ 1 row in set (0.00 sec) ``` + 手順1で見たクエリと比較して、このクエリの実行計画に違いはありますか？理由を述べてください。 + 答え: いいえ。このオプティマイザーは式`100+5`をクエリを実行する前に評価します。 ### 手順 3. 次のステートメントを実行してEXPLAIN出力を表示 ```sql EXPLAIN SELECT * FROM parameterReference WHERE id-5=100; ``` ```output tidb>EXPLAIN SELECT * FROM parameterReference WHERE id-5=100; +-------------------------+---------+-----------+--------------------------+---------------------------------------------------+ | id | estRows | task | access object | operator info | +-------------------------+---------+-----------+--------------------------+---------------------------------------------------+ | TableReader_7 | 1644.00 | root | | data:Selection_6 | | └─Selection_6 | 1644.00 | cop[tikv] | | eq(minus(universe.parameterreference.id, 5), 100) | | └─TableFullScan_5 | 2055.00 | cop[tikv] | table:parameterReference | keep order:false | +-------------------------+---------+-----------+--------------------------+---------------------------------------------------+ 3 rows in set (0.00 sec) ``` + 手順1と2で見たクエリと比較して、このクエリの実行計画に違いはありますか？理由を述べてください。 + 答え: はい。このオプティマイザーは式`ID-5=100`をクエリを実行する前に単一の値に還元できません。そのため、フルテーブルスキャン（タイプ: TableFullScan）を行う必要があります。行数は統計に基づく推定値であり、実際の`parameterReference`テーブルの行数と一致するとは限りません。 ### 手順 4. JSON形式でクエリのオプティマイザーコストを表示 ```sql CREATE INDEX idx_host_name_ref ON planetarySystem(hostname, pl_name, ref_id); EXPLAIN FORMAT = "tidb_json" SELECT pl_name, sy_snum, sy_pnum FROM planetarySystem WHERE pl_name='11 Com b' AND hostname='11 Com b' AND ref_id=71\G DROP INDEX idx_host_name_ref on planetarySystem; ``` ```output *************************** 1. row *************************** TiDB_JSON: [ { "id": "Projection_4", "estRows": "1.00", "taskType": "root", "operatorInfo": "universe.planetarysystem.pl_name, universe.planetarysystem.sy_snum, universe.planetarysystem.sy_pnum", "subOperators": [ { "id": "IndexLookUp_10", "estRows": "1.00", "taskType": "root", "subOperators": [ { "id": "IndexRangeScan_8(Build)", "estRows": "1.00", "taskType": "cop[tikv]", "accessObject": "table:planetarySystem, index:idx_host_name_ref(hostname, pl_name, ref_id)", "operatorInfo": "range:[\"11 Com b\" \"11 Com b\" 71,\"11 Com b\" \"11 Com b\" 71], keep order:false" }, { "id": "TableRowIDScan_9(Probe)", "estRows": "1.00", "taskType": "cop[tikv]", "accessObject": "table:planetarySystem", "operatorInfo": "keep order:false" } ] } ] } ] 1 row in set (0.01 sec) ``` + このクエリに対してオプティマイザーが計算したコストはいくらですか？ + 答え: 1.0 (JSON出力の"estRows"フィールドの値。このコストInfo JSONオブジェクトは、より詳細なコストの内訳を表示します。） + このクエリを解決するために、TiDBはどのカラムにアクセスする必要がありますか？ + 答え: `universe.planetarysystem.pl_name`、`universe.planetarysystem.sy_snum`、`universe.planetarysystem.sy_pnum`カラム（JSON "operatorInfo" 配列フィールドの要素） + TiKVでどの操作が行われますか？ + 答え: `IndexRangeScan` と `TableRowIDScan` がTiKVで行われます（JSON出力の`taskType`フィールドの値が`cop[tikv]`です）。 + TiDBサーバでどの操作が行われますか？ + 答え: `IndexLookUp` がTiDBサーバで行われます（JSON出力の "taskType" フィールドの値が "root" です）。 ## モジュール 3: EXPLAIN ANALYZE コマンドの使用 + この演習では、TiDBのオプティマイザーの実行計画を複数の異なるSELECTクエリに対してEXPLAIN ANALYZEコマンドを使用して調査します。 ### 手順 1. テーブルの作成 + クラスタ化プライマリキーテーブル`T1`と非クラスタ化プライマリキーテーブル`T2`を作成します。 ```sql DROP TABLE IF EXISTS `T1`; CREATE TABLE `T1` ( `id` bigint AUTO_INCREMENT, `name` varchar(20) NOT NULL DEFAULT '', PRIMARY KEY (`id`), KEY (`name`) ); DROP TABLE IF EXISTS `T2`; CREATE TABLE `T2` ( `id` bigint AUTO_INCREMENT, `name` varchar(20) NOT NULL DEFAULT '', PRIMARY KEY (`id`) NONCLUSTERED, KEY (`name`) ); ``` ```output Query OK, 0 rows affected (0.53 sec) ``` ### 手順 2. テーブルにデータを挿入 ```sql INSERT INTO T1 VALUES(NULL, 'Tom'); INSERT INTO T1 VALUES(NULL, 'Jack'); INSERT INTO T1 VALUES(NULL, 'Frank'); INSERT INTO T2 VALUES(NULL, 'Tom'); INSERT INTO T2 VALUES(NULL, 'Jack'); INSERT INTO T2 VALUES(NULL, 'Frank'); ``` ```output Query OK, 1 row affected (0.00 sec) ``` ### 手順 3. `T1` テーブルに対する `EXPLAIN ANALYZE` の出力を表示 ```sql SELECT id, name from T1; EXPLAIN ANALYZE SELECT * FROM T1 WHERE id=1\G ``` ```output +----+-------+ | id | name | +----+-------+ | 1 | Tom | | 2 | Jack | | 3 | Frank | +----+-------+ 3 rows in set (0.00 sec) *************************** 1. row *************************** id: Point_Get_1 estRows: 1.00 actRows: 1 task: root access object: table:T1 execution info: time:474.1µs, loops:2, RU:0.273086, Get:{num_rpc:1, total_time:443.5µs}, total_process_time: 67.5µs, total_wait_time: 56µs, tikv_wall_time: 149.5µs, scan_detail: {total_process_keys: 1, total_process_keys_size: 39, total_keys: 1, get_snapshot_time: 18.2µs, rocksdb: {block: {cache_hit_count: 2}}} operator info: handle:1 memory: N/A disk: N/A 1 row in set (0.00 sec) ``` + このクエリのアクセス方法は何ですか？ + 答え: Point_Get + このクエリの実際の実行時間はどれくらいですか？ + 答え: time:474.1µs + オペレーター（アクセス方法）は何回実行されましたか？ + 答え: 2回 ### 手順 4. `T1` テーブルに対する `EXPLAIN ANALYZE` の出力を表示 ```sql SELECT id, name from T1; EXPLAIN ANALYZE SELECT * FROM T1 WHERE id=4\G ``` ```output +----+-------+ | id | name | +----+-------+ | 1 | Tom | | 2 | Jack | | 3 | Frank | +----+-------+ 3 rows in set (0.00 sec) *************************** 1. row *************************** id: Point_Get_1 estRows: 1.00 actRows: 0 task: root access object: table:T1 execution info: time:473.8µs, loops:1, RU:0.272975, Get:{num_rpc:1, total_time:446.6µs}, total_process_time: 68.9µs, total_wait_time: 51.7µs, tikv_wall_time: 151.9µs, scan_detail: {total_keys: 1, get_snapshot_time: 17.3µs, rocksdb: {block: {cache_hit_count: 2}}} operator info: handle:4 memory: N/A disk: N/A 1 row in set (0.00 sec) ``` + 以前の手順のクエリと比較して、このクエリの実行計画に差はありますか？なぜですか？ 答え: このクエリの実行時間は473.8µsで、オペレーター（アクセス方法）が実行された回数は1回です。これは、クラスタ化プライマリキーテーブル内に`id=4`の行がないことが確認できると、必要なデータがテーブルに格納されていないことがわかるためです。しかし、以前の手順では、`id=3`の行を見つけた後でも、主キーが`id`であるにもかかわらず、条件を満たす行が存在するかどうかをもう一度確認する必要があります。 ### 手順 5. `T2` テーブルに対する `EXPLAIN ANALYZE` の出力を表示 ```sql SELECT _tidb_rowid, id, name from T2; EXPLAIN ANALYZE SELECT * FROM T2 WHERE id=1\G ``` ```output +-------------+----+-------+ | _tidb_rowid | id | name | +-------------+----+-------+ | 2 | 1 | Tom | | 4 | 3 | Jack | | 6 | 5 | Frank | +-------------+----+-------+ 3 rows in set (0.00 sec) *************************** 1. row *************************** id: Point_Get_1 estRows: 1.00 actRows: 1 task: root access object: table:T2, index:PRIMARY(id) execution info: time:592.9µs, loops:2, RU:0.520601, Get:{num_rpc:2, total_time:565.6µs}, total_process_time: 57.8µs, total_wait_time: 53.1µs, tikv_wall_time: 147.1µs, scan_detail: {total_process_keys: 2, total_process_keys_size: 87, total_keys: 2, get_snapshot_time: 12.6µs, rocksdb: {block: {cache_hit_count: 8}}} operator info: memory: N/A disk: N/A 1 row in set (0.00 sec) ``` + 手順3と4で見たクエリと比較して、このクエリの実行計画に違いはありますか？理由を述べてください。 + 答え: `Get:{num_rpc:2, total_time:565.6µs}` フィールドから確認できるように、TiKVに送信されたGet RPCリクエストの数（num_rpc）は2です。これは、`T2` が非クラスタ化プライマリキーテーブルであり、まずプライマリキー `id` から `_tidb_rowid` を取得するために `Point_Get` を使用し、次に `_tidb_rowid` を使用して必要な行をテーブルから取得する必要があるためです。したがって、2つのRPCリクエストが必要です。 ### 手順 6. `T2` テーブルに対する `EXPLAIN ANALYZE` の出力を表示 ```sql SELECT _tidb_rowid, id, name from T2; EXPLAIN ANALYZE SELECT * FROM T2 WHERE _tidb_rowid=2\G ``` ```output +-------------+----+-------+ | _tidb_rowid | id | name | +-------------+----+-------+ | 2 | 1 | Tom | | 4 | 3 | Jack | | 6 | 5 | Frank | +-------------+----+-------+ 3 rows in set (0.00 sec) *************************** 1. row *************************** id: Point_Get_1 estRows: 1.00 actRows: 1 task: root access object: table:T2 execution info: time:518.2µs, loops:2, RU:0.274932, Get:{num_rpc:1, total_time:487.5µs}, total_process_time: 72.8µs, total_wait_time: 53.5µs, tikv_wall_time: 158.9µs, scan_detail: {total_process_keys: 1, total_process_keys_size: 43, total_keys: 1, get_snapshot_time: 16.6µs, rocksdb: {block: {cache_hit_count: 4}}} operator info: handle:2 memory: N/A disk: N/A 1 row in set (0.00 sec) ``` + 手順5で見たクエリと比較して、このクエリの実行計画に違いはありますか？理由を述べてください。答え: `Get:{num_rpc:1, total_time:487.5µs}` フィールドから、TiKVに送信されたGet RPCリクエストの数（num_rpc）は1であることがわかります。`T2` テーブルが非クラスタ化プライマリキーテーブルであっても、クエリ条件として `_tidb_rowid` を直接使用する場合は、1つのRPCリクエストだけが必要です。 ### 手順 7. テーブル `T1` と `T2` をクリーンアップ ```sql DROP TABLE T1; DROP TABLE T2; ``` ```output Query OK, 0 rows affected (0.53 sec) ``` ## 演習終了