数据库SQL Plan的自动演进：用多臂老虎机算法在线选择最优执行计划

数据库SQL Plan的自动演进用多臂老虎机算法在线选择最优执行计划一、同一条SQL优化器昨天选A方案、今天选B方案——计划漂移的烦恼生产环境中最让DBA头疼的问题之一一条运行了半年的SQL突然变慢了。排查发现不是数据量变了不是索引变了仅仅是优化器改变主意了——可能因为统计信息的一次采样偏差可能因为一个参数的细微调整执行计划从Index Scan变成了Full Table Scan。这引出一个根本性的问题优化器是基于统计信息的一次性决策它生成一个理论上最优的计划后就执行了。但如果理论最优和实际执行效果有偏差或者环境变化导致最优选择变化优化器不会自动修正。我们需要一种在线学习机制——让数据库在执行过程中积累经验逐步淘汰效果差的执行计划收敛到真正最优的方案。多臂老虎机Multi-Armed Bandit, MAB算法恰好提供了这样一个框架将不同的执行计划视为不同的臂每次查询是一次拉臂查询的实际执行时间就是奖励的负值。算法在探索尝试不同的计划和利用使用当前最优计划之间平衡逐步收敛到最优方案。flowchart TB A[SQL查询到达] -- B{缓存检查} B --|新SQL| C[优化器生成候选计划集] B --|已有历史| D[读取Bandit状态] C -- E[初始化Bandit] D -- E E -- F{Bandit决策} F --|探索| G[随机选择计划] F --|利用| H[选择当前最优计划] G -- I[执行查询] H -- I I -- J[收集执行反馈br/耗时/行数/IO] J -- K[更新Bandit状态] K -- L[更新计划评分]二、多臂老虎机算法的工程实现问题建模。每条SQL的SQL指纹SQL Digest对应一个独立的Bandit实例。候选执行计划通常由优化器生成的Hint变体或用Plan Baseline中的计划构成老虎机的臂。每次执行的实际时间或综合代价作为该臂的奖励信号负值——执行越快奖励越高。算法选择。选择UCBUpper Confidence Bound算法或者Thompson Sampling。UCB的决策公式为选择mean_reward c × sqrt(ln(N) / n_i)最大的臂其中c是探索系数N是总执行次数n_i是该臂的执行次数。这保证了每个臂都有被尝试的机会但执行效果差的臂被选择的频率越来越低。Thompson Sampling使用贝叶斯方法——维护每个臂的奖励分布假设为高斯分布每次从后验分布中采样选择采样值最大的臂。冷启动策略。新SQL没有历史数据时所有臂的置信区间都很宽UCB和Thompson Sampling会自然地进行大量探索。这是合理的——对于新SQL系统确实需要尝试不同计划来学习。但这种探索会影响线上性能。缓解策略是探索阶段的查询使用较低的执行优先级或路由到备用资源执行。三、与MySQL Plan Baseline的协同MySQL 8.0引入的SQL Plan ManagementSPM提供了Plan Baseline功能——管理员可以固定某个执行计划防止优化器自动切换。Bandit算法与Plan Baseline形成协作而非竞争关系。Plan Baseline负责计划白名单——只有被Baseline接受的新计划才会成为候选。Bandit负责在线选择——在Baseline中的多个候选计划中选择当前最优的。两者的分工是Plan Baseline确保不会使用未经验证的糟糕计划Bandit在已验证的优秀计划中动态选择最优。-- Plan Baseline 示例捕获当前执行计划作为基线 SELECT PLAN_CAPTURE(SELECT * FROM orders WHERE user_id ?); -- 查看所有基线计划及其执行统计 SELECT sql_text, accepted, enabled, executions, elapsed_time / executions AS avg_time_us FROM performance_schema.replication_group_member_stats; -- 注此处简化示意四、生产环境的安全考量探索的风险控制。在UCB中通过降低探索系数c来减少探索频率c越小越偏向利用设定探索的保护期如同一条SQL在最近100次执行中计划都稳定就不再强制探索探索执行时监控执行时间如果超过阈值立即中止并标记该计划为差计划。计划的快速否决。如果一个计划在执行中出现严重错误超时、OOM、死锁立即将其从候选集中移除而不是等待Bandit算法慢慢淘汰。状态持久化。Bandit的状态必须在数据库重启后保持否则每次重启都要重新学习。将Bandit的状态每个臂的执行次数、累积奖励存储在MySQL的系统表中重启时恢复。五、总结用多臂老虎机算法实现SQL执行计划的自动演进本质上是用在线经验补充优化器的离线统计。优化器基于统计信息做最优猜测Bandit基于实际执行效果做在线修正。两者结合可以在保持优化器智能性的同时不断增加对实际执行环境的自适应能力。这个方案的局限性在于它更适合OLTP场景中重复执行的热点SQL有大量执行样本积累对于AD-hoc查询每条SQL只执行一次优化器的一次性决策仍然是唯一的选择。最终愿景是让数据库能像人类一样吃一堑长一智——这次执行慢了下次就知道换一种方式。