OBCP第四章 OceanBase SQL 调优学习笔记：通俗解读与实践指南
OceanBase SQL 调优学习笔记：通俗解读与实践指南
本文基于OceanBase OBCP V3.0培训教材整理，重点解读SQL调优的核心技术点
一、SQL调优核心方法论
1.1 性能问题来源
1.2 调优基本流程
-- 示例：查看SQL执行信息
SELECT * FROM gv$sql_audit WHERE sql_text LIKE '%your_sql%';-- 示例：分析执行计划
EXPLAIN SELECT * FROM orders WHERE user_id = 1001;
优化三步走：
- 定位瓶颈：通过审计表和SQL Trace找耗时操作
- 分析计划：使用EXPLAIN查看逻辑执行计划
- 实施优化：改写SQL/调整索引/修改连接方式
二、分区表优化实践
2.1 分区类型对比
| 分区类型 | 适用场景 | 示例 |
|---|---|---|
| HASH分区 | 用户ID等离散值 | PARTITION BY HASH(user_id) PARTITIONS 8 |
| RANGE分区 | 时间序列数据 | PARTITION BY RANGE(gmt_create) |
| KEY分区 | 非整型字段 | PARTITION BY KEY(username) |
| LIST分区 | 枚举类型（如地区） | PARTITION BY LIST(region_code) |
| 二级分区 | 多维度数据（用户+时间） | PARTITION BY RANGE(gmt_create) SUBPARTITION BY HASH(user_id) |
2.2 分区管理操作
-- 增加分区（仅Range分区支持）
ALTER TABLE sales ADD PARTITION (PARTITION p2024 VALUES LESS THAN ('2025-01-01')
);-- 删除分区
ALTER TABLE sales DROP PARTITION p2020;-- 分区裁剪示例（仅扫描特定分区）
SELECT * FROM sales PARTITION(p2023)
WHERE amount > 10000;
分区裁剪的作用详见：OceanBase 分区裁剪（Partition Pruning）原理解读
2.3 分区使用建议
- 热点打散：用HASH分区分散写入压力
- 历史归档：用RANGE分区快速删除旧数据
- 分区键选择：必须是主键子集，常用在WHERE条件
- 分区数量：单表不超过8192分区（此处指mysql租户，Oracle租户单表分区最多65536），
单机推荐<3万
三、索引优化策略
3.1 索引创建原则
-索引表与普通数据表一样都是实体表，在数据表进行更新的时候会先更新索引表然后再更新数据表
-索引要全部包含所查询的列：包含的列越全越好，这样可以尽可能的减少回表的行数
-选择具有高选择性、频繁在where 从句中出现、频繁在join关联字段中的字段
-不对函数或表达式中的字段建索引，要么就建函数索引
-创建一个索引时，评估该索引给查询带来的性能优化是否比因其而引起INSERT，UPDATE，DELETE操作的性能下降以及索引占用的空间更要值得
-在常被修改到字段上建索引需要进行评估
-- 创建覆盖索引（避免回表）
CREATE INDEX idx_orders_user ON orders(user_id, amount);-- 创建函数索引
CREATE INDEX idx_name_lower ON users(LOWER(username));
-覆盖索引：需要显式创建，将查询所需的列包含在索引中，避免回表。
-二级索引的默认行为 ：在OceanBase中，二级索引（非聚簇索引）默认只包含索引列和主键列。
-回表操作：如果查询的列不在二级索引中，数据库需要通过索引找到主键，再根据主键到主索引（聚簇索引）中查找完整数据行，这个过程就是回表。
通过合理设计覆盖索引，可以避免回表操作，提升查询性能。
索引设计黄金法则：
- 等值条件前置：
WHERE a=? AND b>?索引列a在前 - 避免索引失效：不在索引列做运算
WHERE YEAR(create_time)=2023 - 高选择性优先：选择区分度高的列（如用户ID）
- 控制索引数量：每个写操作需更新所有索引
3.2 索引选择机制
四、局部索引 vs 全局索引
4.1 核心区别
| 特性 | 局部索引 | 全局索引 |
|---|---|---|
| 分区方式 | 与主表分区一致 | 独立分区规则 |
| 唯一约束 | 需包含所有分区键 | 可独立保证唯一性 |
| 跨分区查询 | 需扫描所有分区 | 直接定位目标分区 |
| DML性能 | 影响小（同机事务） | 影响大（跨机分布式事务） |
| 适用场景 | 分区键常出现在WHERE条件 | 需要非分区键的唯一约束 |
4.2 创建示例
-- 局部索引（自动继承分区）
CREATE INDEX local_idx ON sales(product_id) LOCAL;-- 全局分区索引
CREATE INDEX global_idx ON sales(store_id) GLOBAL
PARTITION BY HASH(store_id) PARTITIONS 4;
⚠️ 选型建议：高频精准查询用全局索引，范围扫描用局部索引
五、Hint强制优化
Hint举例
强一致/弱一致： /*+READ_CONSISTENCY(STRONG)*/， /*+READ_CONSISTENCY(WEAK)*/
Server端执行语句超时时间：/*+query_timeout(100000000)*/ 单位微秒
强制使用索引：/*+INDEX(表名 索引名) */
采用并行：/*+PARALLEL(N)*/指定语句级别的并发度。
- 当该hint指定时，会忽略系统变量ob_stmt_parallel_degree的设置
- ob_stmt_parallel_degree 用于设置查询的并行度，即可以并行运行的任务数。
/*+ leading(table_name_list)*/
- 指定表的连接顺序
- 如果发现hint指定的table_name不存在，leading hint失效；
- 如果发现hint中存在重复table，leading hint失效
更多hint，参考官网OB 文档:Hint
5.1 常用Hint语法
/*+ 强制索引 */
SELECT /*+ INDEX(orders idx_user) */ *
FROM orders WHERE user_id=1001;/*+ 指定连接顺序 */
SELECT /*+ LEADING(t1 t2) */ *
FROM t1 JOIN t2 ON t1.id=t2.id;/*+ 弱一致性读 */
SELECT /*+ READ_CONSISTENCY(WEAK) */ balance
FROM accounts;
5.2 执行计划绑定
-- 创建Outline绑定计划
CREATE OUTLINE fix_plan ON
SELECT /*+ USE_HASH(t1 t2) */ * FROM t1,t2...
USING HINT 'USE_HASH(t1 t2)';-- 查看Outline生效情况
SELECT * FROM gv$outline
WHERE outline_name='fix_plan';
六、性能监控技巧
6.1 实时诊断工具
-- 开启SQL Trace
SET ob_enable_trace_log=1;-- 执行待监控SQL
SELECT COUNT(*) FROM large_table;--(示例大表)-- 查看各阶段耗时
SHOW TRACE;
+-------------------+--------------------------------------------+-------+
| Title | KeyValue | Time |
+-------------------+--------------------------------------------+-------+
| query start | trace_id: "[Y3B6C6451982C-3E9627]"; | 0 |
| parse start | stmt: "select count(*) from large_table"; | 99 |
| pc get plan start | | 16 |
| pc get plan end | | 50 |
| resolve start | | 62 |
| resolve end | | 355 |
| transform start | | 105 |
| transform end | | 107 |
| optimizer start | | 3 |
| optimizer end | | 623 |
| CG start | | 1 |
| CG end | | 156 |
| execution start | | 87 |
| execution end | | 28364 |
| query end | | 166 |
+-------------------+--------------------------------------------+-------+
15 rows in set (0.01 sec)
6.2 (g)v$sql_audit关键字段
| 字段 | 诊断意义 |
|---|---|
| RETRY_CNT | >0表示锁冲突或切主 |
| QUEUE_TIME | 过大表示CPU资源不足 |
| GET_PLAN_TIME | 很长表示未命中plan cache |
| EXECUTE_TIME | 结合等待事件分析慢SQL根因 |
七、实战调优案例
7.1 电商订单查询优化
原始SQL：
SELECT * FROM orders
WHERE user_id=1001 AND status='PAID'
ORDER BY create_time DESC;
优化措施：
- 创建覆盖索引：
CREATE INDEX idx_user_status ON orders(user_id, status, create_time); - 使用逆序扫描：
SELECT /*+ INDEX(orders idx_user_status) */ * FROM orders WHERE user_id=1001 AND status='PAID' ORDER BY create_time DESC; -- 自动使用逆序索引
通过合理运用分区策略、索引优化和Hint控制，OceanBase的SQL性能可提升数倍至数十倍。建议在复杂查询上线前使用EXPLAIN分析执行计划，并通过gv$sql_audit持续监控生产环境SQL性能。
附录：OceanBase 分区类型速查表
📌 一、一级分区类型
| 分区类型 | 描述 | 语法示例 | 适用场景 | 注意事项 |
|---|---|---|---|---|
| HASH | 根据分区键的哈希值将数据均匀分布到各个分区。 | PARTITION BY HASH(c1) PARTITIONS 5; | 按用户ID等键值均匀分布数据，消除热点查询。 | 分区表达式结果必须为INT类型；不支持向量（如 HASH(c1, c2)）。 |
| KEY | 与HASH类似，但使用内置哈希函数，支持任意类型。 | PARTITION BY KEY(c1) PARTITIONS 5;PARTITION BY KEY() PARTITIONS 5; (使用主键) | 无需指定具体哈希计算，支持多列和非INT类型。 | 分区键可不指定（默认为主键）；不支持表达式；支持多列向量。 |
| RANGE | 根据分区键的范围划分数据。 | PARTITION BY RANGE(unix_timestamp(gmt_create)) (...); | 按时间范围管理数据，便于历史数据归档和范围查询。 | 支持ADD/DROP分区；最后一个分区不建议使用MAXVALUE。 |
| RANGE COLUMNS | 支持多列和非INT类型（如VARCHAR, DATETIME）的范围分区。 | PARTITION BY RANGE COLUMNS(gmt_create) (...); | 多列组合范围查询；非INT类型的时间或字符串范围。 | 不支持表达式；支持多列向量。 |
| LIST | 根据枚举值列表划分分区。 | PARTITION BY LIST(c1) (PARTITION p0 VALUES IN (1,2,3)); | 按地区、状态等离散值分类存储。 | 分区表达式必须为INT类型；不支持向量。 |
| LIST COLUMNS | 支持多列和非INT类型的列表分区。 | PARTITION BY LIST COLUMNS(region) (...); | 多列组合的离散值分类。 | 不支持表达式；支持多列向量。 |
| 生成列分区 | 使用生成列（由其他列计算而得）作为分区键。 | PARTITION BY KEY(gc_user_id) ... GENERATED ALWAYS AS (SUBSTRING(t_key,1,4)) VIRTUAL; | 需要对字段处理后作为分区键的场景。 | 分区键为虚拟生成列。 |
🔗 二、二级分区组合
二级分区先按一级分区键划分，再按二级分区键细分。常见组合方式包括：
| 一级分区类型 | 二级分区类型 | 示例场景 |
|---|---|---|
| HASH / KEY | RANGE / RANGE COLUMNS | 先按user_id散列，再按gmt_create时间范围细分。 |
| RANGE / RANGE COLUMNS | HASH / KEY | 先按时间范围划分，再按user_id散列。 |
| LIST / LIST COLUMNS | RANGE / RANGE COLUMNS | 先按地区列表划分，再按时间范围细分。 |
| RANGE / RANGE COLUMNS | LIST / LIST COLUMNS | 先按时间范围划分，再按状态列表细分。 |
| HASH / KEY | LIST / LIST COLUMNS | 先按user_id散列，再按状态列表细分。 |
| LIST / LIST COLUMNS | HASH / KEY | 先按地区列表划分，再按user_id散列。 |
💡 建议：若需对RANGE分区进行
ADD/DROP操作，建议将RANGE分区作为一级分区（如RANGE + HASH），而非HASH + RANGE。
⚙️ 三、分区管理操作
| 操作 | 语法示例 | 说明 |
|---|---|---|
| 增加分区 | ALTER TABLE members ADD PARTITION (PARTITION p3 VALUES LESS THAN(2000)); | 仅RANGE分区支持以APPEND方式在末尾添加分区。 |
| 删除分区 | ALTER TABLE members DROP PARTITION p3; | 仅RANGE分区支持删除任意一级分区。 |
✂️ 四、分区裁剪原理
| 分区类型 | 裁剪条件 | 示例 |
|---|---|---|
| HASH/LIST | 等值查询；分区表达式作为整体出现在等值条件中。 | WHERE c1 + c2 = 1 可裁剪。 |
| RANGE | 查询条件与分区范围有交集；等值查询可裁剪，非等值范围查询可能无法裁剪。 | WHERE c1 = 150 可裁剪；WHERE c1 > 110 AND c1 < 150 可能无法裁剪。 |
| 二级分区 | 先按一级分区键裁剪，再按二级分区键裁剪，最终取乘积确定物理分区。 | WHERE (c1=1 OR c1=2) AND (c2>101 AND c2<150) 可确定具体物理分区。 |
💡 五、使用建议
- 分区键必须是主键的子集。
- 选择分区类型时，考虑业务SQL的条件形态（确保能触发分区裁剪）。
- 避免RANGE分区的最后一个分区使用
MAXVALUE，以便后续扩展。 - 单表分区数上限为8192，单机分区数推荐不超过3万。
- 若需在非分区键字段上建立唯一约束，应使用全局索引。
- 写入频繁的表，主键应尽量有序（如时序递增），避免随机值导致写入放大。
📊 六、附录：MySQL vs Oracle模式支持
| 分区类型 | MySQL模式 | Oracle模式 |
|---|---|---|
| HASH | ✅ | ✅ |
| KEY | ✅ | ❌ |
| RANGE | ✅ | ✅ |
| RANGE COLUMNS | ✅ | ❌ |
| LIST | ✅ | ✅ |
| LIST COLUMNS | ✅ | ❌ |
| 组合分区 | ✅ | ✅ |
此速查表基于OceanBase 3.0版本整理，具体语法和支持范围请以官方最新文档为准。