【OceanBase系列】——OceanBase SQL执行计划深度解析与优化实战

1. 从SQL慢查询到执行计划优化记得去年双十一大促前我们电商系统突然出现大量订单查询超时。当时DBA团队抓取到的慢SQL看起来平平无奇就是个简单的多表关联查询。直到用OceanBase的EXPLAIN命令分析执行计划才发现这个查询竟然走了全表扫描原本应该使用的索引完全没生效。这次经历让我深刻体会到——看懂执行计划是数据库优化的必修课。OceanBase作为原生分布式数据库其执行计划比单机数据库复杂得多。一条SQL可能在几十个计算节点上并行执行每个算子OPERATOR的成本估算都影响着整体性能。比如上个月我们遇到个典型案例某报表查询在测试环境跑3秒到了生产环境却要30秒。后来通过对比执行计划发现生产环境数据分布不均匀导致HASH JOIN算子负载倾斜调整分区键后才解决问题。2. 解密OceanBase执行计划的三把钥匙2.1 EXPLAIN命令的三重境界OceanBase提供了三种执行计划查看方式就像修仙小说的不同境界筑基期EXPLAIN BASIC只展示算子骨架EXPLAIN BASIC SELECT * FROM orders WHERE user_id10086;输出像地铁线路图只告诉你经过哪些站点算子但不知道换乘距离成本。金丹期普通EXPLAIN会显示关键指标|ID|OPERATOR |NAME |EST.ROWS|COST| |0 |TABLE SCAN |orders|5 |2834|这里的EST.ROWS是优化器预估的行数COST是微秒级执行成本。我常遇到COST值很低但实际跑很慢的情况往往是统计信息过时导致的。元婴期EXPLAIN EXTENDED会暴露所有细节Outputs filters: ------------------------------------- 0 - output([orders.order_id], [orders.amount]), filter([orders.user_id 10086]), access([orders.user_id], [orders.order_id], [orders.amount]), partitions(p5)这里能看到具体的输出字段、过滤条件、访问的分区。有次排查发现filter条件里居然有隐式类型转换导致索引失效。2.2 执行计划的族谱分析法看OceanBase执行计划要像查家谱找最深的缩进缩进最深的算子最先执行比如嵌套循环连接的内表扫描看兄弟节点顺序同缩进级别的从左到右执行关注关键算子这几个是性能杀手HASH GROUP BY大数据量时内存可能爆炸MERGE JOIN需要排序临时表空间可能不足SUBPLAN SCAN子查询物化开销大去年我们有个订单统计SQL跑15分钟分析执行计划发现有个SUBPLAN SCAN处理了800万行数据。改成CTE写法后降到28秒。2.3 计划缓存里的薛定谔的猫通过v$plan_cache_plan_explain查到的才是真实执行计划。有次我EXPLAIN显示走索引实际查询却全表扫描。后来发现是计划缓存里存的是历史版本用这个命令才抓到真凶-- 先查plan_id SELECT plan_id FROM v$plan_cache_plan_stat WHERE statement LIKE %SELECT * FROM orders%; -- 再查真实计划 SELECT * FROM v$plan_cache_plan_explain WHERE plan_id 12345 AND tenant_id 1001;3. 分布式执行计划的调优实战3.1 并行执行的交通管制OceanBase的并行执行就像城市交通系统PX COORDINATOR交警指挥中心EXCHANGE OUT主干道收费站SQC区域调度站有次我们做用户画像分析20亿数据量的JOIN查询启用并行后反而更慢。通过EXPLAIN发现是DOP并行度设置过高导致协调节点成瓶颈。调整公式供参考合理DOP min(CPU核数 × 0.8, 分区数 × 2)3.2 分区策略的风水学分区键选不好就像把厕所建在厨房旁边。我们踩过的坑用user_id做HASH分区热门用户导致数据倾斜用order_date做RANGE分区冷数据占用大量存储后来改用user_id的后四位做HASH分区配合order_date做二级分区均匀性提升70%。3.3 索引设计的读心术OceanBase的索引选择逻辑很特别。有次建了联合索引(a,b,c)但查询WHERE b? AND c?就是不生效。后来发现OceanBase的索引前缀匹配规则比MySQL更严格。现在我们的设计原则高频查询条件必须放在索引最左区分度高的字段靠前避免在索引列上用函数-- 反例索引失效 CREATE INDEX idx_name ON users(UPPER(user_name)); SELECT * FROM users WHERE UPPER(user_name)TOM; -- 正解函数索引 CREATE INDEX idx_upper_name ON users(UPPER(user_name)) AS FUNCTIONAL;4. 从执行计划反推SQL优化4.1 执行计划中的报警信号这些是需要立即处理的危险信号EST.ROWS与实际行数差异超过10倍 → 更新统计信息ANALYZE TABLE orders UPDATE HISTOGRAM ON user_id;COST占比超过50%的算子 → 重点优化RESERVE扫描 → 反向扫描索引效率低4.2 执行计划驱动的改写技巧看到这些算子可以尝试对应优化看到TEMPORARY TABLE原SQLSELECT * FROM (SELECT ... LIMIT 100)改写SELECT * FROM ... LIMIT 100看到MATERIALIZED原SQLWHERE id IN (SELECT ...)改写WHERE EXISTS (SELECT ...)看到WINDOW SORT原SQLSELECT RANK() OVER(ORDER BY score)改写/* NO_USE_HASH_AGGREGATION */强制流式计算4.3 执行计划对比工具链我的优化三板斧用EXPLAIN生成原始计划通过OUTLINE注入HINTEXPLAIN OUTLINE INTO outline_id FOR SELECT ...; SHOW OUTLINE FROM outline_id;用EXPLAIN EXTENDED对比优化前后计划最近处理的一个案例某分页查询从12秒降到0.8秒就是通过对比计划发现优化器漏掉了LIMIT下推优化手动添加/* LIMIT_PUSH_DOWN */提示解决的。