印刻数据库

外观

OceanBase 执行计划分析 ​

执行计划获取 ​

使用 EXPLAIN 命令 ​

基本语法 ​

sql
-- 获取执行计划
EXPLAIN [options] <sql-statement>;

-- 选项说明
-- EXTENDED: 显示扩展信息
-- PARTITIONS: 显示分区信息
-- FORMAT=JSON: 以JSON格式显示执行计划

示例 ​

sql
-- 基本执行计划
EXPLAIN SELECT * FROM employees WHERE department = 'IT';

-- 扩展执行计划
EXPLAIN EXTENDED SELECT * FROM employees WHERE department = 'IT';

-- 分区执行计划
EXPLAIN PARTITIONS SELECT * FROM employees WHERE department = 'IT';

-- JSON格式执行计划
EXPLAIN FORMAT=JSON SELECT * FROM employees WHERE department = 'IT';

使用 DBMS_XPLAN 包 ​

查看最后一条SQL的执行计划 ​

sql
-- 执行SQL
SELECT * FROM employees WHERE department = 'IT';

-- 查看执行计划
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

查看特定SQL的执行计划 ​

sql
-- 获取SQL ID
SELECT sql_id, sql_text FROM v$sql WHERE sql_text LIKE '%SELECT * FROM employees%';

-- 查看执行计划
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('<sql-id>', NULL, 'ALLSTATS LAST'));

实时执行计划 ​

使用 V$OB_PLAN_CACHE ​

sql
-- 查看计划缓存中的执行计划
SELECT * FROM oceanbase.GV$OB_PLAN_CACHE_PLAN_STAT WHERE plan_id = <plan-id>;

-- 查看计划缓存中的SQL文本
SELECT * FROM oceanbase.GV$OB_PLAN_CACHE_SQLTEXT WHERE plan_id = <plan-id>;

执行计划解读 ​

执行计划基本结构 ​

示例执行计划 ​

执行计划字段说明 ​

  • ID：操作的唯一标识符
  • OPERATOR：操作类型
  • NAME：表名或索引名
  • EST. ROWS：预估返回行数
  • COST：预估执行成本

常见操作符 ​

表访问操作符 ​

  • TABLE SCAN：全表扫描
  • INDEX SCAN：索引扫描
  • INDEX RANGE SCAN：索引范围扫描
  • INDEX UNIQUE SCAN：唯一索引扫描
  • INDEX FULL SCAN：索引全扫描
  • INDEX FAST FULL SCAN：索引快速全扫描

连接操作符 ​

  • NESTED LOOP JOIN：嵌套循环连接
  • HASH JOIN：哈希连接
  • MERGE JOIN：合并连接
  • NESTED LOOP OUTER JOIN：嵌套循环外连接
  • HASH OUTER JOIN：哈希外连接
  • MERGE OUTER JOIN：合并外连接

其他操作符 ​

  • SORT：排序操作
  • AGGREGATE：聚合操作
  • FILTER：过滤操作
  • LIMIT：限制返回行数
  • PROJECT：投影操作
  • SUBPLAN SCAN：子计划扫描

执行计划优化建议 ​

全表扫描优化 ​

  • 问题：全表扫描消耗大量I/O资源，执行效率低
  • 优化建议：
    • 为过滤条件创建合适的索引
    • 优化查询，减少返回行数
    • 考虑表分区，减少扫描范围

索引扫描优化 ​

  • 问题：索引选择不当，导致索引扫描效率低
  • 优化建议：
    • 选择选择性高的列创建索引
    • 考虑联合索引，覆盖查询所需的所有列
    • 避免在索引列上使用函数或表达式

连接操作优化 ​

  • 问题：连接方式选择不当，导致连接效率低
  • 优化建议：
    • 小表驱动大表，提高嵌套循环连接效率
    • 为连接列创建索引
    • 考虑使用哈希连接或合并连接

执行计划优化 ​

索引优化 ​

索引设计原则 ​

  • 选择性原则：选择选择性高的列创建索引
  • 前缀原则：考虑联合索引的前缀匹配
  • 覆盖原则：创建覆盖索引，减少回表操作
  • 最左匹配原则：遵循索引的最左匹配规则
  • 避免过度索引：避免创建过多索引，影响写入性能

索引优化示例 ​

sql
-- 原查询（可能使用全表扫描）
SELECT * FROM employees WHERE department = 'IT' AND hire_date > '2020-01-01';

-- 优化：创建联合索引
CREATE INDEX idx_employees_dept_hire ON employees(department, hire_date);

-- 优化后查询（使用索引范围扫描）
SELECT * FROM employees WHERE department = 'IT' AND hire_date > '2020-01-01';

查询重写 ​

避免使用 SELECT * ​

sql
-- 原查询
SELECT * FROM employees WHERE department = 'IT';

-- 优化：只查询需要的列
SELECT employee_id, first_name, last_name FROM employees WHERE department = 'IT';

避免在索引列上使用函数 ​

sql
-- 原查询（无法使用索引）
SELECT * FROM employees WHERE YEAR(hire_date) = 2020;

-- 优化：将函数移到常量侧
SELECT * FROM employees WHERE hire_date >= '2020-01-01' AND hire_date < '2021-01-01';

避免使用 OR 条件 ​

sql
-- 原查询（可能使用全表扫描）
SELECT * FROM employees WHERE department = 'IT' OR department = 'HR';

-- 优化：使用 IN 条件
SELECT * FROM employees WHERE department IN ('IT', 'HR');

-- 或优化：使用 UNION
SELECT * FROM employees WHERE department = 'IT'
UNION
SELECT * FROM employees WHERE department = 'HR';

连接优化 ​

小表驱动大表 ​

sql
-- 原查询（大表驱动小表）
SELECT * FROM large_table l JOIN small_table s ON l.id = s.id;

-- 优化：小表驱动大表
SELECT * FROM small_table s JOIN large_table l ON s.id = l.id;

为连接列创建索引 ​

sql
-- 原查询（连接列无索引）
SELECT * FROM orders o JOIN customers c ON o.customer_id = c.customer_id;

-- 优化：为连接列创建索引
CREATE INDEX idx_orders_customer_id ON orders(customer_id);

-- 优化后查询（使用索引加速连接）
SELECT * FROM orders o JOIN customers c ON o.customer_id = c.customer_id;

执行计划监控 ​

监控执行计划性能 ​

使用 V$OB_SQL_PLAN_MONITOR ​

sql
-- 监控正在执行的SQL执行计划
SELECT * FROM oceanbase.GV$OB_SQL_PLAN_MONITOR WHERE sql_id = '<sql-id>';

-- 查看执行计划的实际行数和预估行数
SELECT plan_line_id, operation, options, object_name, estimated_rows, actual_rows
FROM oceanbase.GV$OB_SQL_PLAN_MONITOR WHERE sql_id = '<sql-id>';

使用 V$OB_PLAN_CACHE ​

sql
-- 查看计划缓存中的执行计划性能
SELECT plan_id, sql_id, hit_count, avg_exe_usec, avg_queue_time, avg_exe_mem
FROM oceanbase.GV$OB_PLAN_CACHE_PLAN_STAT ORDER BY hit_count DESC;

执行计划稳定性 ​

绑定执行计划 ​

sql
-- 获取计划ID
SELECT plan_id, sql_id FROM oceanbase.GV$OB_PLAN_CACHE_PLAN_STAT WHERE sql_text LIKE '%SELECT * FROM employees%';

-- 绑定执行计划
ALTER SYSTEM FLUSH PLAN CACHE;
ALTER SYSTEM FLUSH PLAN CACHE FOR SQL '<sql-id>';

查看绑定的执行计划 ​

sql
-- 查看绑定的执行计划
SELECT * FROM oceanbase.DBA_OB_SQL_BIND_HISTORY;

常见问题（FAQ） ​

Q1: 如何判断执行计划是否高效？ ​

A1: 判断执行计划是否高效的方法：

  • 查看执行计划的成本（COST），成本越低越好
  • 查看访问路径，优先选择索引扫描而非全表扫描
  • 查看连接方式，优先选择合适的连接方式
  • 比较预估行数和实际行数，差距越小越好
  • 查看执行时间，执行时间越短越好

Q2: 为什么执行计划会变化？ ​

A2: 执行计划变化的原因：

  • 表统计信息变化
  • 索引创建或删除
  • 表数据量变化
  • 参数配置变化
  • 优化器版本变化

Q3: 如何固定执行计划？ ​

A3: 固定执行计划的方法：

  • 使用 SQL Plan Management (SPM) 功能
  • 使用绑定变量，减少硬解析
  • 定期收集统计信息，保持统计信息准确
  • 避免在SQL中使用动态条件

Q4: 如何分析复杂执行计划？ ​

A4: 分析复杂执行计划的方法：

  • 从最内层操作开始分析，逐层向外
  • 关注高成本的操作
  • 关注预估行数和实际行数的差距
  • 关注表访问方式和连接方式
  • 使用可视化工具辅助分析

Q5: 执行计划中预估行数和实际行数差距大怎么办？ ​

A5: 处理预估行数和实际行数差距大的方法：

  • 收集表统计信息
  • 考虑使用动态采样
  • 检查索引选择性
  • 考虑调整优化器参数
  • 考虑使用 hint 强制执行计划