使用SPM固定执行计划

lemonvita

发布于 2022-4-21 20:27

浏览

0收藏

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内容：

使用SPM固定执行计划-鸿蒙开发者社区 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列索引。

使用SPM固定执行计划-鸿蒙开发者社区创建绑定并查看，首先使用不同的SQL对原始SQL进行绑定，因为去除HINT后的标准化SQL与原始SQL不一致所以绑定失败。

使用SPM固定执行计划-鸿蒙开发者社区执行原始SQL，可以看到SQL执行计划使用了强制的索引

使用SPM固定执行计划-鸿蒙开发者社区通过查询当前会话的last_plan_from_binding变量可以进一步确认是否使用了binding。

使用SPM固定执行计划-鸿蒙开发者社区 5.2版本后通过explain format = ‘verbose’ 查看执行计划后可使用show warnings查看使用了哪个bind_sql。

使用SPM固定执行计划-鸿蒙开发者社区另在information_schema. STATEMENTS_SUMMARY表中PLAN_IN_BINDING记录了SQL是否有绑定。

4、删除SPM

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

使用SPM固定执行计划-鸿蒙开发者社区当删除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缓存。