使用 SPM 固定执行计划

2022 年 7 月 11 日
本文字数：2455 字
阅读完需：约 8 分钟

作者： h5n1 原文来源：https://tidb.net/blog/83b454f1

【是否原创】是

【首发渠道】TiDB 社区

【正文】

1 前言

错误的 SQL 执行计划是生产系统中经常碰到的问题，如未使用正确的索引、表关联方式或顺序错误等，在 CBO 的优化器下大部分问题是由于统计信息陈旧导致执行计划评估不准确，一般定期收集统计信息即可解决该问题。有较多的时候需要固定执行计划，普遍的做法就是通过 SQL 内添加 HINT 强制指定优化器的行为，该方式可以很好的达到控制执行计划的效果，但存在的问题是如果使用的话则必须修改应用程序，而且后续强制指定的执行计划不在适用时，删除 HINT 同样需要再次修改程序，对系统运维带来极大的不便。

基于此需要一种在不修改原有 SQL 的情况下，能够控制或改变 SQL 的执行计划方式，以使那些不能自动选择最优执行计划的 SQL 在人为干预情况下使用正确执行计划，同时不需要对应用程序作出任何修改，SPM(SQL Plan Management 或类似功能) 由此而生，通过 SPM 绑定现有 SQL 的执行计划，极大的方便了 DBA 对 SQL 的优化调整。

2 SPM 原理

说到 SPM 则必须要说说 oracle 数据库的执行计划管理，对于执行计划的固定，oracle 经历了 outeline、profile、SPM 几个功能 (profile 并不能完全固定执行计划)，所有的功能都是基于 HINT 实现，下面是一个 SQL 使用 SPM 绑定后展示的 HINT 内容：

TiDB4.0 版本中开始支持 SPM 功能，在 TiDB 中使用 SPM 绑定执行计划时需要指定原始 SQL 和使用 HINT 的 SQL，首先对原始 SQL 进行标准化处理，处理内容包括：

(1) 多空格、换行符等替换为单空格，比较符前后添加空格等。

(2) SQL 条件中的单值替换为变量参数?。

(3) SQL 条件中的逗号分隔的多值替换为‘…’。

(4) 对查询指定表显示的指定库名。

如原始 SQL 为： select * from t where region_id>0 limit 2,10

在进行标准格式化后： select * from test . t where region_id > ? limit …

绑定时指定 HINT 的 SQL 在去除 HINT 并标准化后必须与原始 SQL 标准化后一致，否则绑定失败 (即只能在原 SQL 上添加 HINT 后绑定)。

在 SQL 绑定完成后会将 binding 信息写入到 mysql.bind_info 表中, 同时会缓存在 tidb server 实例内 bindinfo cache，可通过 show [global|session] bindings 展示 cache 中的 bind 信息，当执行 SQL 时会将 SQL 进行标准化然后和 original_sql 进行比对，如果匹配并且变量参数 tidb_use_plan_baselines=on 则会使用 bind_sql 的 HINT 执行，如果存在多个绑定的话则会使用代价最小的。

创建 SQL 绑定时可指定绑定的作用域为 Global 或 Session，当 session 绑定和 global 绑定的 SQL 相同时会忽略 global 绑定，使用 session 绑定，session 绑定在会话退出后清理。

3 使用 SPM

TiDB 中创建 SPM 非常简单，只需使用 create global|session bindings for 原始 SQL using HINT_SQL 即可。

下面以一张 15 万的表进行演示，表健康度为 100，region_id 列有索引，通过 explain analyze 可以看到优化器评估后使用全表扫描，因为 region_id 的值全部 >0 因此测试 SQL 的值计划走全表扫描实际是正确的选择，本演示中将使该 SQL 强制走 region_id 列索引。

创建绑定并查看，首先使用不同的 SQL 对原始 SQL 进行绑定，因为去除 HINT 后的标准化 SQL 与原始 SQL 不一致所以绑定失败。

执行原始 SQL，可以看到 SQL 执行计划使用了强制的索引

通过查询当前会话的 last_plan_from_binding 变量可以进一步确认是否使用了 binding。

5.2 版本后通过 explain format = ‘verbose’ 查看执行计划后可使用 show warnings 查看使用了哪个 bind_sql。

另在 information_schema. STATEMENTS_SUMMARY 表中 PLAN_IN_BINDING 记录了 SQL 是否有绑定。

4 删除 SPM

删除 SPM 使用 drop [global|session ] binding for xxxx 语法，直接使用 show bindings 输出的 Original_sql 即可，对于标准化为‘…’的 SQL 需要使用随意的逗号列表进行替换，否则因为语法错误而报错。如下所示：

当删除 SPM 后仅是删除 tidb server 中缓存的绑定信息，并未实际删除 mysql.bind_info 系统表的内容，只是将相应条目状态设置为 deleted。对于状态为 deleted 的记录，后台线程每隔 100 个 performance.bind-info-lease（默认值为 3s，即 300 秒）会触发一次对 update_time 在 10 个 bind-info-lease （即 30 秒）以前的绑定的回收清除操作，以确保所有 tidb-server 实例已经读取过这个 ‘deleted’ 状态并更新完本实例内的 SPM 缓存。