# 演習 6: オプティマイザーヒントとSQLプランマネジメント (SPM) ## 概要 この演習では、SQLヒントとSQLプランマネジメント (SPM) を使用して、特定の実行計画を選択します。 > [!TIP] > 演習環境がデプロイされたら、下のバーの**次のモジュール**をクリックして**モジュール 1**を開始します。 ## モジュール1: オプティマイザーヒントとSQLプランマネジメント (SPM) + この演習では、SQLヒントとSQLプランマネジメント (SPM) を使用して、特定の実行計画を選択します。 ### 手順 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` データベースに変更 ```bash mysql -h ${HOST_DB1_PRIVATE_IP} --port 4000 -u root ``` ```sql USE test; ``` ```output Database changed ``` ### 手順 3. テーブルの作成とインデックスの構築 ```sql CREATE TABLE t1(a int, b int, c varchar(10), d varchar(20)); CREATE TABLE t2(a int, b int, c varchar(10), d varchar(20)); CREATE INDEX t1_idx_1 ON t1(a); CREATE INDEX t1_idx_2 ON t1(a,b,c); CREATE INDEX t2_idx_1 ON t2(a); CREATE INDEX t2_idx_2 ON t2(a,b,c); ``` ```output Query OK, 0 rows affected (1.03 sec) ``` ```sql exit ``` ```output Bye ``` ### 手順 4. データの挿入 ```bash for i in `seq 10000`; do mysql -h${HOST_DB1_PRIVATE_IP} -P4000 -uroot -e "INSERT INTO test.t1 VALUES($i, FLOOR(RAND()*10000000), SUBSTRING(MD5(RAND()), 1, 5), SUBSTRING(MD5(RAND()), 1, 10))"; done; for i in `seq 10000`; do mysql -h${HOST_DB1_PRIVATE_IP} -P4000 -uroot -e "INSERT INTO test.t2 VALUES($i, FLOOR(RAND()*10000000), SUBSTRING(MD5(RAND()), 1, 5), SUBSTRING(MD5(RAND()), 1, 10))"; done; ``` ### 手順 5. TiDBに接続 ```bash mysql -h ${HOST_DB1_PRIVATE_IP} --port 4000 -u root ``` ```sql USE test; ``` ```output Database changed ``` ### 手順 6. テーブル `t1` と `t2` の統計を収集 ```sql ANALYZE TABLE t1; ANALYZE TABLE t2; ``` ```output Query OK, 0 rows affected, 1 warning (1.41 sec) ``` ### 手順 7. 次のクエリの実行計画を確認 ```sql EXPLAIN SELECT t1.a, t1.b, t1.c, t2.b FROM t1, t2 WHERE t1.a = t2.a AND t1.b > t2.b ORDER BY t1.b, t2.b; ``` ```output +--------------------------------+----------+-----------+-----------------------------------+------------------------------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +--------------------------------+----------+-----------+-----------------------------------+------------------------------------------------------------------------------------------+ | Sort_8 | 10000.00 | root | | test.t1.b, test.t2.b | | └─MergeJoin_11 | 10000.00 | root | | inner join, left key:test.t1.a, right key:test.t2.a, other cond:gt(test.t1.b, test.t2.b) | | ├─IndexReader_42(Build) | 10000.00 | root | | index:Selection_41 | | │ └─Selection_41 | 10000.00 | cop[tikv] | | not(isnull(test.t2.b)) | | │ └─IndexFullScan_40 | 10000.00 | cop[tikv] | table:t2, index:t2_idx_2(a, b, c) | keep order:true | | └─IndexReader_39(Probe) | 10000.00 | root | | index:Selection_38 | | └─Selection_38 | 10000.00 | cop[tikv] | | not(isnull(test.t1.b)) | | └─IndexFullScan_37 | 10000.00 | cop[tikv] | table:t1, index:t1_idx_2(a, b, c) | keep order:true | +--------------------------------+----------+-----------+-----------------------------------+------------------------------------------------------------------------------------------+ 8 rows in set (0.02 sec) ``` ### 手順 8. ヒント付きで次のクエリの実行計画を確認 + SQLヒントを使用して、インデックス `t2_idx_1` と `IndexJoin` が使用されるようにします。 `t2_idx_2` と `MergeJoin` ではありません。 ```sql EXPLAIN SELECT t1.a, t1.b, t1.c, t2.b FROM t1, t2 USE INDEX(t2_idx_1) WHERE t1.a = t2.a AND t1.b > t2.b ORDER BY t1.b, t2.b; ``` ```output +---------------------------------+----------+-----------+-----------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +---------------------------------+----------+-----------+-----------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------+ | Sort_8 | 10000.00 | root | | test.t1.b, test.t2.b | | └─IndexJoin_18 | 10000.00 | root | | inner join, inner:IndexLookUp_17, outer key:test.t1.a, inner key:test.t2.a, equal cond:eq(test.t1.a, test.t2.a), other cond:gt(test.t1.b, test.t2.b) | | ├─IndexReader_52(Build) | 10000.00 | root | | index:Selection_51 | | │ └─Selection_51 | 10000.00 | cop[tikv] | | not(isnull(test.t1.b)) | | │ └─IndexFullScan_50 | 10000.00 | cop[tikv] | table:t1, index:t1_idx_2(a, b, c) | keep order:false | | └─IndexLookUp_17(Probe) | 10000.00 | root | | | | ├─Selection_15(Build) | 10000.00 | cop[tikv] | | not(isnull(test.t2.a)) | | │ └─IndexRangeScan_13 | 10000.00 | cop[tikv] | table:t2, index:t2_idx_1(a) | range: decided by [eq(test.t2.a, test.t1.a)], keep order:false | | └─Selection_16(Probe) | 10000.00 | cop[tikv] | | not(isnull(test.t2.b)) | | └─TableRowIDScan_14 | 10000.00 | cop[tikv] | table:t2 | keep order:false | +---------------------------------+----------+-----------+-----------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------+ 10 rows in set (0.00 sec) ``` ### 手順 9. SQLプランマネジメント (SPM) を使用 + SQLプランマネジメント (SPM) を使用して、すべてのSQLがインデックス `t2_idx_1` と `HashJoin` を使用するようにし、実行中のプログラム内のSQLステートメントを変更せずに、このようなクエリを使用します。 ```sql CREATE GLOBAL BINDING FOR SELECT t1.a, t1.b, t1.c, t2.b FROM t1, t2 WHERE t1.a = t2.a AND t1.b > t2.b ORDER BY t1.b, t2.b USING SELECT t1.a, t1.b, t1.c, t2.b FROM t1, t2 USE INDEX(t2_idx_1) WHERE t1.a = t2.a AND t1.b > t2.b ORDER BY t1.b, t2.b; ``` ```output Query OK, 0 rows affected (0.01 sec) ``` ### 手順 10. 手順 7でのクエリの実行計画を再度確認 ```sql EXPLAIN SELECT t1.a, t1.b, t1.c, t2.b FROM t1, t2 WHERE t1.a = t2.a AND t1.b > t2.b ORDER BY t1.b, t2.b; ``` ```output +---------------------------------+----------+-----------+-----------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +---------------------------------+----------+-----------+-----------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------+ | Sort_8 | 10000.00 | root | | test.t1.b, test.t2.b | | └─IndexJoin_18 | 10000.00 | root | | inner join, inner:IndexLookUp_17, outer key:test.t1.a, inner key:test.t2.a, equal cond:eq(test.t1.a, test.t2.a), other cond:gt(test.t1.b, test.t2.b) | | ├─IndexReader_52(Build) | 10000.00 | root | | index:Selection_51 | | │ └─Selection_51 | 10000.00 | cop[tikv] | | not(isnull(test.t1.b)) | | │ └─IndexFullScan_50 | 10000.00 | cop[tikv] | table:t1, index:t1_idx_2(a, b, c) | keep order:false | | └─IndexLookUp_17(Probe) | 10000.00 | root | | | | ├─Selection_15(Build) | 10000.00 | cop[tikv] | | not(isnull(test.t2.a)) | | │ └─IndexRangeScan_13 | 10000.00 | cop[tikv] | table:t2, index:t2_idx_1(a) | range: decided by [eq(test.t2.a, test.t1.a)], keep order:false | | └─Selection_16(Probe) | 10000.00 | cop[tikv] | | not(isnull(test.t2.b)) | | └─TableRowIDScan_14 | 10000.00 | cop[tikv] | table:t2 | keep order:false | +---------------------------------+----------+-----------+-----------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------+ 10 rows in set (0.00 sec) ``` ### 手順 11. 別の新しいLinux [ターミナル 2](ssh://monitor1-ssh#2) を開き、TiDBに接続 ```bash mysql -h ${HOST_DB1_PRIVATE_IP} --port 4000 -u root ``` ### 手順 12. [ターミナル 2](ssh://monitor1-ssh#2) で、 `test` を現在のデータベースに設定 ```sql USE test; ``` ```output Database changed ``` ### 手順 13. [ターミナル 2](ssh://monitor1-ssh#2) で、手順 7でのクエリの実行計画を再度確認 ```sql EXPLAIN SELECT t1.a, t1.b, t1.c, t2.b FROM t1, t2 WHERE t1.a = t2.a AND t1.b > t2.b ORDER BY t1.b, t2.b; ``` ```output +---------------------------------+----------+-----------+-----------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +---------------------------------+----------+-----------+-----------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------+ | Sort_8 | 10000.00 | root | | test.t1.b, test.t2.b | | └─IndexJoin_18 | 10000.00 | root | | inner join, inner:IndexLookUp_17, outer key:test.t1.a, inner key:test.t2.a, equal cond:eq(test.t1.a, test.t2.a), other cond:gt(test.t1.b, test.t2.b) | | ├─IndexReader_52(Build) | 10000.00 | root | | index:Selection_51 | | │ └─Selection_51 | 10000.00 | cop[tikv] | | not(isnull(test.t1.b)) | | │ └─IndexFullScan_50 | 10000.00 | cop[tikv] | table:t1, index:t1_idx_2(a, b, c) | keep order:false | | └─IndexLookUp_17(Probe) | 10000.00 | root | | | | ├─Selection_15(Build) | 10000.00 | cop[tikv] | | not(isnull(test.t2.a)) | | │ └─IndexRangeScan_13 | 10000.00 | cop[tikv] | table:t2, index:t2_idx_1(a) | range: decided by [eq(test.t2.a, test.t1.a)], keep order:false | | └─Selection_16(Probe) | 10000.00 | cop[tikv] | | not(isnull(test.t2.b)) | | └─TableRowIDScan_14 | 10000.00 | cop[tikv] | table:t2 | keep order:false | +---------------------------------+----------+-----------+-----------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------+ 10 rows in set (0.00 sec) ``` ### 手順 14. いずれかのLinuxターミナルで、グローバルバインディングを確認 ```sql SHOW GLOBAL BINDINGS WHERE ORIGINAL_SQL LIKE '%t1%t2%'\G ``` ```output *************************** 1. row *************************** Original_sql: select `t1` . `a` , `t1` . `b` , `t1` . `c` , `t2` . `b` from ( `test` . `t1` ) join `test` . `t2` where `t1` . `a` = `t2` . `a` and `t1` . `b` > `t2` . `b` order by `t1` . `b` , `t2` . `b` Bind_sql: SELECT `t1`.`a`,`t1`.`b`,`t1`.`c`,`t2`.`b` FROM (`test`.`t1`) JOIN `test`.`t2` USE INDEX (`t2_idx_1`) WHERE `t1`.`a` = `t2`.`a` AND `t1`.`b` > `t2`.`b` ORDER BY `t1`.`b`,`t2`.`b` Default_db: test Status: enabled Create_time: 2023-12-04 12:22:50.927 Update_time: 2023-12-04 12:22:50.927 Charset: utf8 Collation: utf8_general_ci Source: manual Sql_digest: fc14be07f408211ab8bcba115f45f2c602250e16e3d540960fceb7238c4e9d87 Plan_digest: 1 row in set (0.00 sec) ``` ### 手順 15. いずれかのLinuxターミナルで、グローバルバインディングを削除 ```sql DROP GLOBAL BINDING FOR SQL DIGEST 'fc14be07f408211ab8bcba115f45f2c602250e16e3d540960fceb7238c4e9d87'; ``` ```output Query OK, 1 row affected (0.01 sec) ``` ### 手順 16. いずれかのLinuxターミナルで、手順 7でのクエリの実行計画を再度確認 ```sql EXPLAIN SELECT t1.a, t1.b, t1.c, t2.b FROM t1, t2 WHERE t1.a = t2.a AND t1.b > t2.b ORDER BY t1.b, t2.b; ``` ```output +--------------------------------+----------+-----------+-----------------------------------+------------------------------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +--------------------------------+----------+-----------+-----------------------------------+------------------------------------------------------------------------------------------+ | Sort_8 | 10000.00 | root | | test.t1.b, test.t2.b | | └─MergeJoin_11 | 10000.00 | root | | inner join, left key:test.t1.a, right key:test.t2.a, other cond:gt(test.t1.b, test.t2.b) | | ├─IndexReader_42(Build) | 10000.00 | root | | index:Selection_41 | | │ └─Selection_41 | 10000.00 | cop[tikv] | | not(isnull(test.t2.b)) | | │ └─IndexFullScan_40 | 10000.00 | cop[tikv] | table:t2, index:t2_idx_2(a, b, c) | keep order:true | | └─IndexReader_39(Probe) | 10000.00 | root | | index:Selection_38 | | └─Selection_38 | 10000.00 | cop[tikv] | | not(isnull(test.t1.b)) | | └─IndexFullScan_37 | 10000.00 | cop[tikv] | table:t1, index:t1_idx_2(a, b, c) | keep order:true | +--------------------------------+----------+-----------+-----------------------------------+------------------------------------------------------------------------------------------+ 8 rows in set (0.00 sec) ``` ### 手順 17. テーブルのクリーンアップ ```sql DROP TABLE t1,t2; ``` ```output Query OK, 0 rows affected (1.06 sec) ``` ## 演習終了