OceanBase查询优化器实战调优执行计划、优化器Trace与慢SQL定位【免费下载链接】oceanbaseOceanBase is the unified distributed database for the AI era — open-source, multi-model, one engine for your most demanding workloads.项目地址: https://gitcode.com/GitHub_Trending/oc/oceanbase高峰期一条报表查询从200ms变3s计划从索引扫描退化成全表扫描。OceanBase的查询优化器靠改写规则加代价估算来生成执行计划想搞清计划为什么变差就得把规则匹配与代价计算的完整过程抓出来——这就是优化器Traceoptimizer trace的用途。 先看效果EXPLAIN与Trace最小示例不谈原理先把两条命令跑起来。对任何可疑SQL先拍一张计划快照-- 基本执行计划算子树、代价、连接方式 EXPLAIN SELECT * FROM orders o WHERE o.status 1 AND o.cdate 2025-01-01; -- 增强执行计划附带估计行数与实际行数 EXPLAIN EXTENDED SELECT * FROM orders o WHERE o.status 1;输出里重点看三处访问算子是TABLE SCAN还是INDEX SCAN、多表查询的连接顺序、估计行数与实际行数的偏差。偏差越大越可能是统计信息过时。再准备重武器——优化器Trace-- 开启优化器跟踪sql_id可传空跟踪全部identifier是文件名后缀 CALL DBMS_XPLAN.ENABLE_OPT_TRACE(, slow_query, 2); -- 重跑那条慢SQL SELECT * FROM orders WHERE status 1; -- 跟踪完成关闭避免日志文件继续膨胀 CALL DBMS_XPLAN.DISABLE_OPT_TRACE();跟踪结果不是查询某张系统表而是直接落在observer进程的log/目录下文件名形如optimizer_trace_xxxxxx_slow_query.trac单个文件上限256MB。实现见 src/sql/ob_optimizer_trace_impl.cpp文件命名逻辑在LogFileAppender::generate_log_file_name。 原理速览优化器怎么做出决策上图是OceanBase的整体架构SQL请求经OBProxy进入OBServer。每个OBServer内部优化器完成三件事RBO基于规则的优化按预定义规则改写逻辑计划如谓词移动把过滤条件尽量挪到靠近数据源的位置、子查询展开、外连接消除等。每条规则对应 src/sql/rewrite/ 下一个ob_transform_*.cpp文件CBO基于代价的优化对每张表枚举候选访问路径主表、二级索引、全表扫描用代价模型算出IO与CPU开销选最便宜的一条代价计算逻辑在 src/sql/optimizer/ 的ob_opt_est_cost_model.cpp生成物理计划把最优逻辑计划转成物理算子树交给执行引擎。优化器Trace的埋点就穿插在这三个阶段里src/sql/ob_optimizer_trace_impl.h 里的OPT_TRACE宏族按系统环境→会话信息→优化器参数→计划统计信息→改写后SQL→耗时/内存逐节输出缩进层级对应优化过程的嵌套深度。顺带说明optimizer_trace、optimizer_trace_features等系统变量在 src/share/system_variable/ob_system_variable_init.json 中有定义默认空串用于兼容MySQL语义当前版本实际可用的跟踪入口是DBMS_XPLAN包。 慢SQL全链路捕捉把level调到够用场景SQL突然变慢分不清是改写阶段还是代价估算阶段出了岔子。ENABLE_OPT_TRACE的level参数控制信息粒度与源码trace_level_的判断一一对应参数作用推荐值sql_id只跟踪指定SQL空串表示当前会话全部SQL慢SQL的sql_ididentifier.trac文件名后缀区分多次抓取业务标识如slow_querylevel 0基础章节环境、会话、参数、计划统计信息0level 1追加每章节耗时与内存占用1level 2追加改写后SQL看规则是否生效2level 3追时代价模型与估算细节3用level 2打开.trac文件章节结构是------------------------------------------------------ SYSTEM ENVIRONMENT ------------------------------------------------------ SESSION INFO ... OPTIMIZER PARAMETERS ------------------------------------------------------如果是大查询或多表连接变慢重点看连接顺序选择与访问路径枚举部分代价估计出错的第一个环节通常就是计划走坏的起点。 核对索引访问路径统计信息三连场景明明建了二级索引计划却仍走全表扫描怀疑优化器不用我的索引。索引用不用不是优化器意愿问题是代价问题代价模型按统计信息行数、distinct值、数据量给每条候选路径算分选最小值。统计信息过时代价就算错路径就选错。处理三连-- 1. 先更新表统计信息 ANALYZE TABLE orders COMPUTE STATISTICS; -- 2. 对比更新前后的执行计划 EXPLAIN SELECT * FROM orders WHERE status 1; -- 3. 仍不满意时用Hint强制指定索引 SELECT /* INDEX(orders idx_status) */ * FROM orders WHERE status 1;对代价计算仍存疑时用level 3开Trace可看到各候选访问路径的代价明细确认优化器认为哪条路径更便宜、便宜在哪里。访问路径的代价估算入口是 src/sql/optimizer/ 下的ob_access_path_estimation.cpp。 确认改写规则是否生效看改写后SQL场景SQL里有视图或子查询想确认视图合并、子查询展开这些改写规则到底转没转。改写规则集中在 src/sql/rewrite/例如谓词移动是ob_transform_predicate_move_around.cpp子查询展开是ob_transform_subquery_unnest.cpp。规则有没有生效最直接的证据就是改写后的SQL-- level2 会输出 TRANSFORMED SQL 章节 CALL DBMS_XPLAN.ENABLE_OPT_TRACE(, rewrite_check, 2); -- 带子查询的示例SQL SELECT * FROM (SELECT id FROM orders WHERE status 1) t; CALL DBMS_XPLAN.DISABLE_OPT_TRACE();如果.trac里改写后的SQL仍是原来的子查询结构、预期中的拍平没发生说明该规则的前置条件不满足比如表达式里含非确定性函数。这时对照对应规则的源码看判定条件或先用Hint如NO_UNNEST兜住性能。 排障对照表现象可能原因处理方法计划走全表扫描索引已存在统计信息过时索引路径代价被算高执行ANALYZE TABLE ... COMPUTE STATISTICS后重新EXPLAIN开了Trace却找不到文件开启与执行SQL不在同一会话或找错了目录同会话操作到observer工作目录log/下找optimizer_trace_*.trac估计行数与实际行数偏差大数据分布倾斜或统计采样不充分更新统计信息必要时用Hint或outline固化计划.trac文件过大难读level过高或sql_id为空跟踪了全部SQLsql_id收窄到目标语句level降到1~2升级后同一SQL换了计划代价模型参数或版本行为变化对比Trace中OPTIMIZER PARAMETERS章节的参数差异 进阶延伸代价模型细节与内存跟踪想再深一层DBMS_XPLAN包里还有两个工具实现都在 src/pl/sys_package/ob_dbms_xplan.cpp代价模型跟踪level 3下启用enable_trace_cost_model输出每项代价的计算明细适合验证代价模型本身内存性能跟踪DBMS_XPLAN.ENABLE_MEM_PERF(标识)记录SQL处理各阶段的内存分配回调逻辑在 src/observer/mysql/obmp_query.cpp适合定位优化阶段的内存尖峰。如果你是修改优化器规则的开发者改动需要过仓库的单元测试CIsrc/sql/下的优化器与改写规则有完整测试覆盖✅ 动手前检查清单对慢SQL执行过EXPLAIN/EXPLAIN EXTENDED记录访问方式与连接顺序执行ANALYZE TABLE ... COMPUTE STATISTICS确认统计信息更新后计划是否变化用DBMS_XPLAN.ENABLE_OPT_TRACE以level 2抓了.trac文件读过OPTIMIZER PARAMETERS章节对比过改写后SQL与原始SQL确认了哪些改写规则生效用Hint如INDEX(t idx)或outline固化了目标计划作为临时方案【免费下载链接】oceanbaseOceanBase is the unified distributed database for the AI era — open-source, multi-model, one engine for your most demanding workloads.项目地址: https://gitcode.com/GitHub_Trending/oc/oceanbase创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考