优化 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 的执行计划概要或详情：

1. 查询 GV$OB_SQL_AUDIT 视图获取 trace_id：

   obclient> 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 的执行计划信息。

2. 使用 GV$SQL_PLAN_MONITOR 视图获取执行计划详情：

   obclient> 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 的执行计划监控信息，包括每个计划行的执行情况和统计信息。

3. 还可以结合 GV$SQL_PLAN_MONITOR 获取更细粒度的执行计划细节：

   obclient> 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. 优化方案

综合上述分析，优化方案如下：

1. 统一字段数据类型
   为避免隐式转换，调整表中关联字段的数据类型保持一致。例如，将三张表的 c1 列数据类型统一为 VARCHAR(20)：

   obclient [testdb]> ALTER TABLE tbl1 MODIFY c1 VARCHAR(20);
   obclient [testdb]> ALTER TABLE tbl2 MODIFY c1 VARCHAR(20);
   

2. 添加索引
   为提高查询效率，在关联字段上添加索引：

   obclient [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);
   

3. 调整联接顺序
   根据数据量大小优化联接顺序。由于表 2 和表 3 数据量远小于表 1，遵循“小表驱动大表”的原则，可以通过 SQL Hint 修改关联顺序：

   obclient [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 性能，提升数据库整体运行效率。