## 模块 2: 跨数据库执行计划绑定 在 SaaS 工作负载中，通常在租户之间会发生查询模式变化，导致执行计划不一致。TiDB 的 SQL 执行计划管理(SPM)支持全局执行计划绑定，以确保跨不同数据库的稳定和最优查询性能。 ### 步骤 1. 启用跨数据库执行计划绑定 + 通过将系统变量 [`tidb_opt_enable_fuzzy_binding`](https://docs.pingcap.com/tidb/stable/system-variables/#tidb_opt_enable_fuzzy_binding-new-in-v760) 设置为 `ON`，同时为全局和当前会话启用跨数据库执行计划绑定。 ```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 | +-------------------------------+-------+ ``` ### 步骤 2. 创建跨数据库执行计划绑定 + 在模块 1 中，我们创建了 1000 个数据库，它们都有名为 `sbtest1` 和 `sbtest2` 的相同定义的表。我们将选择数据库 `sbtest1` 和 `sbtest999` 作为执行计划绑定的目标数据库。 ```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) ``` + 对跨多个数据库的某种类型的 SQL 进行执行计划采样，在我们的例子中是 `sbtest1` 和 `sbtest999`。 ```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 | +-------------------------------+---------+-----------+---------------+---------------------------------------------------------------------------+ ``` + 我们观察到，上述查询在 `sbtest1` 和 `sbtest999` 数据库中的默认执行计划是相同的，使用 `MergeJoin` 进行表连接。 + 现在 DBA 决定在所有数据库中使用 `HashJoin` 方法而不是 `MergeJoin` 来提高上述查询的性能。在 `CREATE GLOBAL BINDING` 语句中，`*.sbtest2` 和 `*.sbtest1` 中的 `*` 代表所有数据库，意味着执行计划绑定将在跨所有 schema 执行此查询时生效。 ```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) ``` + 验证绑定设置。观察到 `Default_db` 为空，表示此执行计划在所有数据库中生效。 ```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) ``` + 验证绑定结果。在来自三个数据库的示例查询中，`sbtest1`、`sbtest666` 和 `sbtest999`，CBO 选择了绑定指定的 `HashJoin`。 ```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 | +-------------------------------+---------+-----------+---------------+--------------------------------------------------------------------+ ``` ### 步骤 3. 删除跨数据库执行计划绑定 + 检查现有的绑定。注意绑定的 SQL digest 供后续使用，它显示在 `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: ``` + 删除绑定 ```sql DROP GLOBAL BINDING FOR SQL DIGEST '4ea75bf0f868417b7a8536ed2c2a9e016197db36d38697e69a15533768fc9910'; ``` ```output Query OK, 1 row affected (0.01 sec) ``` + 验证绑定已被删除。 ```sql SHOW BINDINGS\G ``` ```output Empty set (0.00 sec) ``` + 通过跨数据库执行计划绑定，你可以使用单个绑定定义来提高多个数据库的查询性能，在我们的示例中是针对 1000 个数据库。 + 保持 mysql 客户端连和 TiDB 服务器的链接，继续进行下一个模块。