优化 SQL 性能指南
本指南介绍了如何在 OceanBase 环境下定位与优化 SQL 性能问题。通过使用 SQL 查询定位资源消耗较高的语句、分析执行计划以及针对性地优化 SQL，可以显著提升系统性能。以下内容涵盖了从登录集群、定位 Top SQL，到获取执行计划、分析执行瓶颈，再到最终的 SQL 改写和优化过程的完整步骤和案例分析。
一、前提准备
1. 登录集群
使用 root 用户登录集群的 sys 租户：
obclient -h127.0.0.1 -uroot@sys -P2881 -Doceanbase -A说明：此处 root 用户未设置密码，仅供体验使用。在实际环境中，请根据需要配置相关用户密码。
sys租户仅用于演示，实际环境中请切换至对应租户进行操作。
二、定位 Top SQL
1. 查询特定时间范围内的 Top SQL
以下 SQL 查询定位了租户 ID 为 1002，服务器 IP 为 127.0.0.1，在 2023 年 9 月 3 日 14:00:46 至 2023 年 9 月 4 日 14:01:46 之间执行时间最长的前 10 条 SQL：
obclient [oceanbase]> SELECT
/*+READ_CONSISTENCY(WEAK), QUERY_TIMEOUT(100000000), PARALLEL(4)*/
sql_id,
COUNT(1),
AVG(elapsed_time),
AVG(execute_time),
AVG(total_wait_time_micro),
AVG(return_rows),
AVG(affected_rows),
query_sql,
ret_code
FROM
v$OB_SQL_AUDIT
WHERE
tenant_id = 1002
AND svr_ip IN ('127.0.0.1')
AND (
CAST(USEC_TO_TIME(request_time) AS DATETIME) BETWEEN CAST('2023-09-03 14:00:46' AS DATETIME)
AND CAST('2023-09-04 14:01:46' AS DATETIME)
)
GROUP BY
sql_id
ORDER BY
AVG(elapsed_time) DESC
LIMIT
10\G该查询结果显示了在指定时间范围内，按平均执行时间排序的 top 10 SQL。
2. 查询最近 1 分钟的 Top SQL
以下 SQL 查询了最近 1 分钟内的 SQL 审计信息，并按照执行时间排序，找出 top SQL：
obclient [oceanbase]> SELECT
/*+ READ_CONSISTENCY(WEAK), QUERY_TIMEOUT(100000000), PARALLEL(4)*/
sql_id,
AVG(elapsed_time),
AVG(execute_time),
AVG(total_wait_time_micro),
AVG(return_rows),
AVG(affected_rows),
SUBSTR(query_sql, 1, 50) AS query_sql,
ret_code
FROM
v$OB_SQL_AUDIT
WHERE TIME_TO_USEC(NOW(6)) - request_time < 60000000
GROUP BY sql_id, query_sql, ret_code
ORDER BY 3 DESC
LIMIT 10;说明：此查询将返回最近 1 分钟内平均执行时间较长的前 10 条 SQL 及其相关信息。根据返回结果，可以识别性能瓶颈最高的 SQL。
三、获取 Top SQL 与执行计划分析
1. 获取 Top SQL 用于问题定位
使用如下 SQL 获取消耗资源较高的 Top SQL：
obclient [oceanbase]> SELECT
SQL_ID,
AVG(ELAPSED_TIME),
AVG(QUEUE_TIME),
AVG(ROW_CACHE_HIT + BLOOM_FILTER_CACHE_HIT + BLOCK_CACHE_HIT + DISK_READS) AS avg_logical_read,
AVG(execute_time) AS avg_exec_time,
COUNT(*) AS cnt,
AVG(execute_time - TOTAL_WAIT_TIME_MICRO) AS avg_cpu_time,
AVG(TOTAL_WAIT_TIME_MICRO) AS avg_wait_time,
WAIT_CLASS,
AVG(retry_cnt)
FROM v$OB_SQL_AUDIT
GROUP BY SQL_ID
ORDER BY avg_exec_time * cnt DESC
LIMIT 10;该查询通过综合考虑 SQL 的执行时间和执行次数，定位出消耗大量资源的 Top SQL。根据查询结果，可筛选出真正需要优化的 SQL，而非仅执行时间长但执行次数少的语句。
2. 获取 SQL 执行计划
a. 使用 EXPLAIN 获取执行计划
在 MySQL 模式下，可以直接使用 EXPLAIN 命令查看 SQL 的执行计划。例如：
obclient [oceanbase]> EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id;说明：上面的 SQL 仅为示例。在实际操作时，请确保表
t1和t2存在。执行EXPLAIN后，可以看到优化器选择的访问路径和联接方式，从而分析 SQL 性能瓶颈。
b. 分析执行计划
从 EXPLAIN 结果中，可以观察到以下关键点：
- 扫描方式（如全表扫描、索引范围扫描等）。
- 联接算法（如 Nested Loop Join、Hash Join、Merge Join）。
- 估算的行数和时间代价。
- 访问路径是否使用了索引以及是否存在数据类型转换等。
通过分析这些信息，可识别出 SQL 中可能存在的性能问题，例如全表扫描、笛卡尔积、不匹配的关联字段数据类型导致的隐式转换等。
c. 结合视图查询执行计划详情
除了 EXPLAIN 之外，还可以结合系统视图查询执行计划相关的详细信息。例如，通过以下步骤获取某 SQL 的执行计划概要或详情：
查询
GV$OB_SQL_AUDIT视图获取trace_id：sqlobclient> SELECT trace_id FROM oceanbase.GV$OB_SQL_AUDIT WHERE query_sql LIKE '%insert into%' ORDER BY REQUEST_TIME DESC LIMIT 1;说明：通过指定查询关键字获取
trace_id，以便后续查询对应 SQL 的执行计划信息。使用
GV$SQL_PLAN_MONITOR视图获取执行计划详情：sqlobclient> SELECT PROCESS_NAME, PLAN_LINE_ID, PLAN_OPERATION, COUNT(*) AS PARALLEL, AVG(LAST_REFRESH_TIME - FIRST_REFRESH_TIME) AS AVG_REFRESH_TIME, MAX(LAST_REFRESH_TIME - FIRST_REFRESH_TIME) AS MAX_REFRESH_TIME, MIN(LAST_REFRESH_TIME - FIRST_REFRESH_TIME) AS MIN_REFRESH_TIME, AVG(LAST_CHANGE_TIME - FIRST_CHANGE_TIME) AS AVG_CHANGE_TIME, MAX(LAST_CHANGE_TIME - FIRST_CHANGE_TIME) AS MAX_CHANGE_TIME, MIN(LAST_CHANGE_TIME - FIRST_CHANGE_TIME) AS MIN_CHANGE_TIME, SUM(OUTPUT_ROWS) AS TOTAL_OUTPUT_ROWS, SUM(STARTS) AS TOTAL_RESCAN_TIMES FROM oceanbase.GV$SQL_PLAN_MONITOR WHERE trace_id = '指定的_TRACE_ID' GROUP BY PLAN_LINE_ID ORDER BY PLAN_LINE_ID ASC;说明：将上述查询中的
'指定的_TRACE_ID'替换为实际获取到的 trace_id。此查询返回指定 SQL 的执行计划监控信息，包括每个计划行的执行情况和统计信息。还可以结合
GV$SQL_PLAN_MONITOR获取更细粒度的执行计划细节：sqlobclient> SELECT SVR_IP, SVR_PORT, PROCESS_NAME, PLAN_LINE_ID, PLAN_OPERATION, FIRST_REFRESH_TIME, LAST_REFRESH_TIME, LAST_REFRESH_TIME - FIRST_REFRESH_TIME AS REFRESH_TIME, FIRST_CHANGE_TIME, LAST_CHANGE_TIME, LAST_CHANGE_TIME - FIRST_CHANGE_TIME AS CHANGE_TIME, OUTPUT_ROWS, STARTS AS RESCAN_TIMES FROM oceanbase.GV$SQL_PLAN_MONITOR WHERE trace_id = '指定的_TRACE_ID' ORDER BY PLAN_LINE_ID ASC, PROCESS_NAME ASC, FIRST_REFRESH_TIME ASC;说明：此查询返回指定 SQL 的每一执行步骤详细信息，帮助分析各操作的耗时和资源消耗。
四、SQL 优化实践案例
以下通过一个优化案例来演示如何基于执行计划进行 SQL 调优。
1. 案例背景
在 testdb 数据库中创建了 3 个测试表：tbl1、tbl2、tbl3。表定义如下：
tbl1(c1 INT, c2 VARCHAR(50))tbl2(c1 CHAR(1), c2 VARCHAR(20))tbl3(c1 VARCHAR(20), c2 VARCHAR(20))
并通过存储过程向这 3 张表插入大量数据：
tbl1插入 1,000,000 条记录；tbl2插入 1,000 条记录；tbl3插入 100 条记录。
随后执行如下 SQL：
SELECT tbl1.c2, tbl2.c2, tbl3.c2
FROM tbl1, tbl2, tbl3
WHERE tbl1.c1 = tbl2.c1 AND tbl1.c1 = tbl3.c1 AND tbl2.c1 = '3';执行时间平均约为 0.467 秒，属于慢 SQL。
2. 问题排查思路
a. 定位高资源消耗的 SQL
通过查询 GV$OB_SQL_AUDIT 视图，以 SQL 文本筛选出相关 SQL：
obclient [testdb]> SELECT SQL_ID, query_sql,
AVG(ELAPSED_TIME),
AVG(QUEUE_TIME),
AVG(ROW_CACHE_HIT + BLOOM_FILTER_CACHE_HIT + BLOCK_CACHE_HIT + DISK_READS) AS avg_logical_read,
AVG(execute_time) AS avg_exec_time,
COUNT(*) AS cnt,
AVG(execute_time - TOTAL_WAIT_TIME_MICRO) AS avg_cpu_time,
AVG(TOTAL_WAIT_TIME_MICRO) AS avg_wait_time,
WAIT_CLASS,
AVG(retry_cnt)
FROM oceanbase.V$OB_SQL_AUDIT
WHERE query_sql LIKE '%tbl1.c1%';查询结果显示某条 SQL 多次执行并消耗较高资源。
b. 获取该 SQL 的执行计划
对查询出的慢 SQL 使用 EXPLAIN 分析执行计划：
obclient [testdb]> EXPLAIN SELECT tbl1.c2, tbl2.c2, tbl3.c2
FROM tbl1, tbl2, tbl3
WHERE tbl1.c1 = tbl2.c1 AND tbl1.c1 = tbl3.c1 AND tbl2.c1 = '3';从 EXPLAIN 结果中注意到：
- 三个表都走了全表扫描。
- 存在隐式数据类型转换（如
cast(tbl1.c1, DECIMAL(11, 0))），表明关联字段数据类型不一致。 - 没有使用索引，导致大量不必要的扫描和高昂的联接代价。
3. 优化方案
综合上述分析，优化方案如下：
统一字段数据类型
为避免隐式转换，调整表中关联字段的数据类型保持一致。例如，将三张表的c1列数据类型统一为VARCHAR(20)：sqlobclient [testdb]> ALTER TABLE tbl1 MODIFY c1 VARCHAR(20); obclient [testdb]> ALTER TABLE tbl2 MODIFY c1 VARCHAR(20);添加索引
为提高查询效率，在关联字段上添加索引：sqlobclient [testdb]> ALTER TABLE tbl1 ADD INDEX tbl1_c1 (c1); obclient [testdb]> ALTER TABLE tbl2 ADD INDEX tbl2_c1 (c1); obclient [testdb]> ALTER TABLE tbl3 ADD INDEX tbl3_c1 (c1);调整联接顺序
根据数据量大小优化联接顺序。由于表 2 和表 3 数据量远小于表 1，遵循“小表驱动大表”的原则，可以通过 SQL Hint 修改关联顺序：sqlobclient [testdb]> SELECT /*+ LEADING(tbl2 tbl1 tbl3) */ tbl1.c2, tbl2.c2, tbl3.c2 FROM tbl1, tbl2, tbl3 WHERE tbl1.c1 = tbl2.c1 AND tbl1.c1 = tbl3.c1 AND tbl2.c1 = '3';说明：Hint
LEADING(tbl2 tbl1 tbl3)指示优化器从表tbl2开始驱动联接。
4. 优化效果对比
a. 执行时间对比
优化前：
SELECT tbl1.c2, tbl2.c2, tbl3.c2
FROM tbl1, tbl2, tbl3
WHERE tbl1.c1 = tbl2.c1 AND tbl1.c1 = tbl3.c1 AND tbl2.c1 = '3';平均执行时间约 0.467 秒。
优化后：
SELECT /*+ LEADING(tbl2 tbl1 tbl3) */
tbl1.c2, tbl2.c2, tbl3.c2
FROM tbl1, tbl2, tbl3
WHERE tbl1.c1 = tbl2.c1 AND tbl1.c1 = tbl3.c1 AND tbl2.c1 = '3';执行时间明显缩短至约 0.006 秒。
b. 执行计划变化
优化前的执行计划显示全表扫描和高成本的 Nested-Loop Join 操作。而优化后，执行计划如下：
obclient [testdb]> EXPLAIN SELECT /*+ LEADING(tbl2 tbl1 tbl3)*/
tbl1.c2, tbl2.c2, tbl3.c2
FROM tbl1, tbl2, tbl3
WHERE tbl1.c1 = tbl2.c1 AND tbl1.c1 = tbl3.c1 AND tbl2.c1 = '3';优化后的执行计划特点：
- 使用了范围扫描（TABLE RANGE SCAN）而非全表扫描。
- 联接顺序调整为以小表驱动大表，成本大幅降低。
- 总体估算成本显著下降（从约 50191 降至 19），性能得到极大提升。
五、查询改写与查询优化概述
1. 查询改写
- 基于规则的查询改写：如将子查询改写为联接，以便优化器选择更高效的联接算法（如 Hash Join 或 Merge Join）。
- 基于代价的查询改写：基于执行计划分析高成本步骤，针对性地重写 SQL 以降低代价，提高执行效率。
2. 查询优化方向
- 访问路径优化：合理利用索引，减少全表扫描，优化过滤条件以减少扫描行数，避免不必要的回表操作。
- 联接算法优化：优化表的联接顺序和关联条件，尽可能减少笛卡尔积的产生，选择合适的联接算法以降低成本。
通过以上方法和思路，结合实际查询和系统执行计划分析，可以针对性地优化 SQL 性能，提升数据库整体运行效率。