# 实验 6：Optimizer Hints 与执行计划管理 (SPM) ## 概述 + 在本实验中，你将使用 SQL hints 和 执行计划管理 (SPM) 来选择特定的执行计划。 > [!TIP] > 实验室环境部署完成后，点击底部栏上的 **前进** 进入 **模块 1**。 ## 模块 1：Optimizer Hints 与执行计划管理 (SPM) + 在本模块中，你将使用 SQL hints 和执行计划管理 (SPM) 来选择特定的执行计划。 ### 步骤 1：打开终端，SSH 登录到 TiUP 控制机 + 点击这里 - [终端 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 ```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. 使用 Hint 检查的以下查询的执行计划 + 使用 SQL Hint，以便能使用索引 `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. 使用执行计划管理 (SPM) + 使用执行计划管理 (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. 打开 [终端 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'; DROP SESSION 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) ``` ## 实验结束