## Module 1: Optimizer Hints and SQL Plan Management (SPM) + In this exercise, you use SQL hints and SQL Plan Management (SPM) to selection a specific execution plan. ### Step 1. Log in to the remote Linux + Click here - [Terminal 1](ssh://monitor1-ssh#1) to open a new terminal connected to the virtual machine assigned for your hands-on lab. ### Step 2. Log into TiDB and change to `test` database ```bash mysql -h ${HOST_DB1_PRIVATE_IP} --port 4000 -u root ``` ```sql USE test; ``` ```output Database changed ``` ### Step 3. Create tables and build indexes ```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 ``` ### Step 4. Insert the data ```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; ``` ### Step 5. Connect to TiDB ```bash mysql -h ${HOST_DB1_PRIVATE_IP} --port 4000 -u root ``` ```sql USE test; ``` ```output Database changed ``` ### Step 6. Collect the statistic for table `t1` and `t2` ```sql ANALYZE TABLE t1; ANALYZE TABLE t2; ``` ```output Query OK, 0 rows affected, 1 warning (1.41 sec) ``` ### Step 7. Check the execution plan for the following query ```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) ``` ### Step 8. Check the execution plan for the following query with Hint + Use SQL Hint, so that index `t2_idx_1` and `IndexJoin` can be used, instead of `t2_idx_2` and `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) ``` ### Step 9. Use SQL Plan Management (SPM) + Use SQL Plan Management (SPM) to let all SQL to use index `t2_idx_1` and use `HashJoin` all queries similar to the previous one without changing the SQL statements in the running program. ```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) ``` ### Step 10. Check the execution plan for the query in Step 7 again ```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) ``` ### Step 11. Open [Terminal 2](ssh://monitor1-ssh#2) and connect to TiDB ```bash mysql -h ${HOST_DB1_PRIVATE_IP} --port 4000 -u root ``` ### Step 12. In [Terminal 2](ssh://monitor1-ssh#2), make `test` the current database ```sql USE test; ``` ```output Database changed ``` ### Step 13. In [Terminal 2](ssh://monitor1-ssh#2), check the execution plan for the query in Step 7 again ```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) ``` ### Step 14. In any linux terminal, check the global bindings ```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) ``` ### Step 15. In any linux terminal, drop the global bindings ```sql DROP GLOBAL BINDING FOR SQL DIGEST 'fc14be07f408211ab8bcba115f45f2c602250e16e3d540960fceb7238c4e9d87'; DROP SESSION BINDING FOR SQL DIGEST 'fc14be07f408211ab8bcba115f45f2c602250e16e3d540960fceb7238c4e9d87'; ``` ```output Query OK, 1 row affected (0.01 sec) ``` ### Step 16. In any linux terminal, check the execution plan for the query in Step 7 again ```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) ``` ### Step 17. Clean up the tables ```sql DROP TABLE t1,t2; ``` ```output Query OK, 0 rows affected (1.06 sec) ```