## Module 2: Cross-Schema Execution Plans Binding SaaS workloads often exhibit query pattern variations across tenants, leading to inconsistent execution plans. TiDB's SQL Plan Management (SPM) enables global execution plan binding to ensure stable and optimal query performance across different schemas. ### Step 1. Enable the Cross-Schema execution plan binding + Enable the Cross-Schema execution plan binding by setting system variable [`tidb_opt_enable_fuzzy_binding`](https://docs.pingcap.com/tidb/stable/system-variables/#tidb_opt_enable_fuzzy_binding-new-in-v760) to `ON`, both for global and for current session. ```sql SHOW VARIABLES LIKE 'tidb_opt_enable_fuzzy_binding'; ``` ```output +-------------------------------+-------+ | Variable_name | Value | +-------------------------------+-------+ | tidb_opt_enable_fuzzy_binding | OFF | +-------------------------------+-------+ 1 row in set (0.00 sec) ``` ```sql SET GLOBAL tidb_opt_enable_fuzzy_binding=1; SET tidb_opt_enable_fuzzy_binding=1; SHOW VARIABLES like 'tidb_opt_enable_fuzzy_binding'; ``` ```output +-------------------------------+-------+ | Variable_name | Value | +-------------------------------+-------+ | tidb_opt_enable_fuzzy_binding | ON | +-------------------------------+-------+ ``` ### Step 2. Create the Cross-Schema execution plan binding + In module 1, we created 1000 schemas and they all have tables named `sbtest1` and `sbtest2` with the same definition. We will pick up schema `sbtest1` and `sbtest999` as the target schemas for execution plan binding. ```sql SHOW CREATE TABLE sbtest1.sbtest1\G SHOW CREATE TABLE sbtest999.sbtest1\G ``` ```output *************************** 1. row *************************** Table: sbtest1 Create Table: CREATE TABLE `sbtest1` ( `id` int NOT NULL AUTO_INCREMENT, `k` int NOT NULL DEFAULT '0', `c` char(120) NOT NULL DEFAULT '', `pad` char(60) NOT NULL DEFAULT '', PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */, KEY `k_1` (`k`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=30001 1 row in set (0.00 sec) *************************** 1. row *************************** Table: sbtest1 Create Table: CREATE TABLE `sbtest1` ( `id` int NOT NULL AUTO_INCREMENT, `k` int NOT NULL DEFAULT '0', `c` char(120) NOT NULL DEFAULT '', `pad` char(60) NOT NULL DEFAULT '', PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */, KEY `k_1` (`k`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=30001 1 row in set (0.01 sec) ``` + Sample the execution plans of a certain type of SQL across multiple schemas, `sbtest1` and `sbtest999` in our case. ```sql EXPLAIN SELECT COUNT(*) AS num FROM sbtest1.sbtest1 AS t1,sbtest1.sbtest2 AS t2 WHERE t1.id=t2.id AND t1.id>10 AND t1.id<100; EXPLAIN SELECT COUNT(*) AS num FROM sbtest999.sbtest1 AS t1,sbtest999.sbtest2 AS t2 WHERE t1.id=t2.id AND t1.id>10 AND t1.id<100; ``` ```output +-------------------------------+---------+-----------+---------------+-----------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +-------------------------------+---------+-----------+---------------+-----------------------------------------------------------------------+ | StreamAgg_10 | 1.00 | root | | funcs:count(1)->Column#9 | | └─MergeJoin_44 | 3.12 | root | | inner join, left key:sbtest1.sbtest1.id, right key:sbtest1.sbtest2.id | | ├─TableReader_39(Build) | 2.50 | root | | data:TableRangeScan_38 | | │ └─TableRangeScan_38 | 2.50 | cop[tikv] | table:t2 | range:(10,100), keep order:true, stats:partial[id:unInitialized] | | └─TableReader_37(Probe) | 2.50 | root | | data:TableRangeScan_36 | | └─TableRangeScan_36 | 2.50 | cop[tikv] | table:t1 | range:(10,100), keep order:true, stats:partial[id:unInitialized] | +-------------------------------+---------+-----------+---------------+-----------------------------------------------------------------------+ +-------------------------------+---------+-----------+---------------+---------------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +-------------------------------+---------+-----------+---------------+---------------------------------------------------------------------------+ | StreamAgg_10 | 1.00 | root | | funcs:count(1)->Column#9 | | └─MergeJoin_44 | 3.12 | root | | inner join, left key:sbtest999.sbtest1.id, right key:sbtest999.sbtest2.id | | ├─TableReader_39(Build) | 2.50 | root | | data:TableRangeScan_38 | | │ └─TableRangeScan_38 | 2.50 | cop[tikv] | table:t2 | range:(10,100), keep order:true, stats:pseudo | | └─TableReader_37(Probe) | 2.50 | root | | data:TableRangeScan_36 | | └─TableRangeScan_36 | 2.50 | cop[tikv] | table:t1 | range:(10,100), keep order:true, stats:pseudo | +-------------------------------+---------+-----------+---------------+---------------------------------------------------------------------------+ ``` + Observe that the default execution plans for the above queries in the `sbtest1` and `sbtest999` schemas are the same, with table joins using the `MergeJoin`. + Now DBA decide to use `HashJoin` method instead of `MergeJoin` across all schemas to improve performance for above queries. In `CREATE GLOBAL BINDING` statement, the `*` in `*.sbtest2` and `*.sbtest1` represents all schemas, meaning that the execution plan binding will take effect when executing this query across all schemas. ```sql CREATE GLOBAL BINDING USING SELECT /*+ HASH_JOIN(t1, t2) */ COUNT(*) AS num FROM *.sbtest1 AS t1, *.sbtest2 AS t2 WHERE t1.id=t2.id AND t1.id>10 AND t1.id<100; CREATE SESSION BINDING USING SELECT /*+ HASH_JOIN(t1, t2) */ COUNT(*) AS num FROM *.sbtest1 AS t1, *.sbtest2 AS t2 WHERE t1.id=t2.id AND t1.id>10 AND t1.id<100; ``` ```output Query OK, 0 rows affected (0.02 sec) Query OK, 0 rows affected (0.00 sec) ``` + Verify the binding setting. Observe that the `Default_db` is empty, indicating that this execution plan takes effect across all schemas. ```sql SHOW BINDINGS\G ``` ```output *************************** 1. row *************************** Original_sql: select * from ( ? . `sbtest1` as `t1` ) join `*` . `sbtest2` as `t2` where `t1` . `id` = `t2` . `id` and `t1` . `id` > ? and `t1` . `id` < ? Bind_sql: SELECT /*+ HASH_JOIN(`t1`, `t2`)*/ * FROM (`*`.`sbtest1` AS `t1`) JOIN `*`.`sbtest2` AS `t2` WHERE `t1`.`id` = `t2`.`id` AND `t1`.`id` > 10 AND `t1`.`id` < 100 Default_db: Status: enabled Create_time: 2025-02-24 09:33:23.736 Update_time: 2025-02-24 09:33:23.736 Charset: utf8 Collation: utf8_general_ci Source: manual Sql_digest: 107feec0308c4e216b296a9b5ca03638bf400cb17157f3df35d5dd52460a10ef Plan_digest: 1 row in set (0.00 sec) ``` + Verify the binding result. In the sample queries from three schemas, `sbtest1`, `sbtest666`, and `sbtest999`, the CBO selects `HashJoin` specified by the bindings. ```sql EXPLAIN SELECT COUNT(*) AS num FROM sbtest1.sbtest1 AS t1,sbtest1.sbtest2 AS t2 WHERE t1.id=t2.id AND t1.id>10 AND t1.id<100; EXPLAIN SELECT COUNT(*) AS num FROM sbtest666.sbtest1 AS t1,sbtest666.sbtest2 AS t2 WHERE t1.id=t2.id AND t1.id>10 AND t1.id<100; EXPLAIN SELECT COUNT(*) AS num FROM sbtest999.sbtest1 AS t1,sbtest999.sbtest2 AS t2 WHERE t1.id=t2.id AND t1.id>10 AND t1.id<100; ``` ```output +-------------------------------+---------+-----------+---------------+----------------------------------------------------------------+ | id | estRows | task | access object | operator info | +-------------------------------+---------+-----------+---------------+----------------------------------------------------------------+ | StreamAgg_10 | 1.00 | root | | funcs:count(1)->Column#9 | | └─HashJoin_63 | 3.12 | root | | inner join, equal:[eq(sbtest1.sbtest1.id, sbtest1.sbtest2.id)] | | ├─TableReader_39(Build) | 2.50 | root | | data:TableRangeScan_38 | | │ └─TableRangeScan_38 | 2.50 | cop[tikv] | table:t2 | range:(10,100), keep order:false, stats:pseudo | | └─TableReader_37(Probe) | 2.50 | root | | data:TableRangeScan_36 | | └─TableRangeScan_36 | 2.50 | cop[tikv] | table:t1 | range:(10,100), keep order:false, stats:pseudo | +-------------------------------+---------+-----------+---------------+----------------------------------------------------------------+ +-------------------------------+---------+-----------+---------------+--------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +-------------------------------+---------+-----------+---------------+--------------------------------------------------------------------+ | StreamAgg_10 | 1.00 | root | | funcs:count(1)->Column#9 | | └─HashJoin_63 | 3.12 | root | | inner join, equal:[eq(sbtest666.sbtest1.id, sbtest666.sbtest2.id)] | | ├─TableReader_39(Build) | 2.50 | root | | data:TableRangeScan_38 | | │ └─TableRangeScan_38 | 2.50 | cop[tikv] | table:t2 | range:(10,100), keep order:false, stats:pseudo | | └─TableReader_37(Probe) | 2.50 | root | | data:TableRangeScan_36 | | └─TableRangeScan_36 | 2.50 | cop[tikv] | table:t1 | range:(10,100), keep order:false, stats:pseudo | +-------------------------------+---------+-----------+---------------+--------------------------------------------------------------------+ +-------------------------------+---------+-----------+---------------+--------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +-------------------------------+---------+-----------+---------------+--------------------------------------------------------------------+ | StreamAgg_10 | 1.00 | root | | funcs:count(1)->Column#9 | | └─HashJoin_63 | 3.12 | root | | inner join, equal:[eq(sbtest999.sbtest1.id, sbtest999.sbtest2.id)] | | ├─TableReader_39(Build) | 2.50 | root | | data:TableRangeScan_38 | | │ └─TableRangeScan_38 | 2.50 | cop[tikv] | table:t2 | range:(10,100), keep order:false, stats:pseudo | | └─TableReader_37(Probe) | 2.50 | root | | data:TableRangeScan_36 | | └─TableRangeScan_36 | 2.50 | cop[tikv] | table:t1 | range:(10,100), keep order:false, stats:pseudo | +-------------------------------+---------+-----------+---------------+--------------------------------------------------------------------+ ``` ### Step 3. Remove the Cross-Schema execution plan binding + Check the existing bindings. Pay attention to the binding's SQL digest for later use, it is displayed on the field `Sql_digest`. ```sql SHOW BINDINGS\G ``` ```output Original_sql: select count ( ? ) as `num` from ( ? . `sbtest1` as `t1` ) join `*` . `sbtest2` as `t2` where `t1` . `id` = `t2` . `id` and `t1` . `id` > ? and `t1` . `id` < ? Bind_sql: SELECT /*+ HASH_JOIN(`t1`, `t2`)*/ count(1) AS `num` FROM (`*`.`sbtest1` AS `t1`) JOIN `*`.`sbtest2` AS `t2` WHERE `t1`.`id` = `t2`.`id` AND `t1`.`id` > 10 AND `t1`.`id` < 100 Default_db: Status: enabled Create_time: 2025-02-24 09:49:15.265 Update_time: 2025-02-24 09:49:15.265 Charset: utf8 Collation: utf8_general_ci Source: manual Sql_digest: 4ea75bf0f868417b7a8536ed2c2a9e016197db36d38697e69a15533768fc9910 Plan_digest: ``` + Remove the binding ```sql DROP GLOBAL BINDING FOR SQL DIGEST '4ea75bf0f868417b7a8536ed2c2a9e016197db36d38697e69a15533768fc9910'; ``` ```output Query OK, 1 row affected (0.01 sec) ``` + Verify that the binding has been deleted ```sql SHOW BINDINGS\G ``` ```output Empty set (0.00 sec) ``` + With Cross-Schema execution plan binding, you can improve the query performance for multiple schemas with a single binding definition, in our example, it is for 1000 schemas. + Keep the mysql client connected to TiDB server instance and proceed to the next module.