OceanBase 慢SQL分析

核心概念

慢SQL是指执行时间超过预设阈值的SQL语句，是数据库性能问题的常见原因之一。慢SQL分析是指通过各种工具和方法，识别、定位和优化执行缓慢的SQL语句，以提高数据库性能和响应速度。OceanBase提供了丰富的慢SQL分析工具和功能，包括慢查询日志、执行计划分析、实时监控等，可以帮助DBA和开发人员快速定位和解决慢SQL问题。慢SQL分析是数据库性能优化的重要环节，对于提高系统整体性能和用户体验具有重要意义。

慢SQL日志配置

1. 慢SQL阈值设置

功能：设置慢SQL的时间阈值，超过该阈值的SQL将被记录到慢查询日志 适用场景：

识别执行缓慢的SQL语句
监控SQL执行性能
性能优化和问题定位

配置方法：

命令行配置：

sql

-- 设置全局慢SQL阈值为1秒
ALTER SYSTEM SET slow_query_time=1000000 GLOBAL;

-- 设置租户级慢SQL阈值为500毫秒
ALTER TENANT test_tenant SET slow_query_time=500000;

配置文件配置：

txt

# 在observer.conf中配置
slow_query_time=1000000

配置说明：

单位：微秒（μs），1秒=1,000,000微秒
支持全局和租户级配置
配置后立即生效，无需重启

2. 慢SQL日志格式

功能：配置慢SQL日志的格式和内容 适用场景：

自定义慢SQL日志内容
包含更多诊断信息
方便日志分析和处理

配置方法：

sql

-- 设置慢SQL日志格式，包含执行计划
ALTER SYSTEM SET slow_query_log_format='verbose' GLOBAL;

日志内容说明：

SQL语句：执行的SQL文本
执行时间：SQL执行的总时间
扫描行数：扫描的数据行数
返回行数：返回给客户端的数据行数
执行计划：SQL的执行计划
用户名：执行SQL的用户名
客户端信息：客户端IP和端口
会话ID：执行SQL的会话ID

3. 慢SQL日志存储

功能：配置慢SQL日志的存储方式和位置 适用场景：

管理慢SQL日志存储
便于日志查询和分析
控制日志存储成本

配置方法：

本地文件存储：

sql

-- 启用本地慢SQL日志
ALTER SYSTEM SET enable_slow_log=TRUE GLOBAL;

-- 设置慢SQL日志文件路径
ALTER SYSTEM SET slow_log_dir='/data/ob/log' GLOBAL;

-- 设置慢SQL日志文件大小限制（100MB）
ALTER SYSTEM SET slow_log_file_size_limit=104857600 GLOBAL;

分布式表存储：

sql

-- 启用分布式表存储慢SQL
ALTER SYSTEM SET enable_sql_audit=TRUE GLOBAL;

-- 设置慢SQL审计采样比例
ALTER SYSTEM SET sql_audit_sample_rate=1 GLOBAL;

配置说明：

支持本地文件和分布式表两种存储方式
分布式表存储便于集中查询和分析
可设置采样比例，减少性能影响

慢SQL查询方法

1. 查询本地慢SQL日志

功能：查看本地存储的慢SQL日志文件 适用场景：

快速查看单节点的慢SQL
无需访问分布式表
紧急问题定位

操作方法：

bash

# 使用tail命令查看最新的慢SQL
 tail -f /data/ob/log/observer_slow.log

# 使用grep命令过滤特定SQL
 grep -i "SELECT" /data/ob/log/observer_slow.log

# 使用awk命令分析慢SQL
 awk '{if($2>1000) print $0}' /data/ob/log/observer_slow.log

2. 查询分布式慢SQL表

功能：查询存储在分布式表中的慢SQL 适用场景：

分析集群级别的慢SQL
跨节点慢SQL查询
历史慢SQL分析

核心表：

GV$OB_SLOW_SQL：全局视图，包含所有节点的慢SQL
V$OB_SLOW_SQL：单节点视图，包含当前节点的慢SQL

查询示例：

sql

-- 查询最近1小时的慢SQL，按执行时间降序排序
SELECT * FROM GV$OB_SLOW_SQL 
WHERE start_time >= NOW() - INTERVAL '1' HOUR 
ORDER BY elapsed_time DESC 
LIMIT 10;

-- 查询特定租户的慢SQL
SELECT * FROM GV$OB_SLOW_SQL 
WHERE tenant_id = 1001 
AND start_time >= NOW() - INTERVAL '24' HOUR 
ORDER BY elapsed_time DESC;

-- 查询执行时间超过5秒的慢SQL
SELECT sql_id, sql_text, elapsed_time, scan_rows, return_rows 
FROM GV$OB_SLOW_SQL 
WHERE elapsed_time > 5000000 
ORDER BY elapsed_time DESC;

3. 使用OCP慢SQL分析

功能：通过OceanBase云平台（OCP）分析慢SQL 适用场景：

可视化慢SQL分析
自动生成优化建议
历史趋势分析

操作步骤：

登录OCP平台
导航到集群监控页面
选择"慢SQL分析"模块
设置查询条件（时间范围、租户、SQL类型等）
查看慢SQL列表和详细信息
分析执行计划和优化建议

慢SQL分析方法

1. 执行时间分析

功能：分析SQL执行时间的分布和构成 适用场景：

识别SQL执行的瓶颈
确定优化方向
评估优化效果

分析要点：

总执行时间：SQL执行的总耗时
解析时间：SQL解析和优化的耗时
执行时间：实际执行SQL的耗时
网络传输时间：结果返回给客户端的耗时

分析方法：

sql

-- 查询慢SQL执行时间分布
SELECT 
  sql_id, 
  sql_text, 
  elapsed_time/1000000 AS total_time_sec, 
  parse_time/1000000 AS parse_time_sec, 
  execution_time/1000000 AS exec_time_sec
FROM GV$OB_SLOW_SQL 
WHERE start_time >= NOW() - INTERVAL '1' HOUR 
ORDER BY elapsed_time DESC 
LIMIT 5;

2. 扫描行数分析

功能：分析SQL扫描的数据行数 适用场景：

识别全表扫描的SQL
评估索引使用情况
优化查询效率

分析要点：

扫描行数：SQL执行过程中扫描的数据行数
返回行数：实际返回给客户端的数据行数
扫描比例：返回行数/扫描行数，比例越低效率越低

分析方法：

sql

-- 查询扫描行数远大于返回行数的慢SQL
SELECT 
  sql_id, 
  sql_text, 
  scan_rows, 
  return_rows, 
  ROUND(return_rows/scan_rows*100, 2) AS efficiency_percent
FROM GV$OB_SLOW_SQL 
WHERE start_time >= NOW() - INTERVAL '1' HOUR 
AND scan_rows > 10000 
AND return_rows/scan_rows < 0.01 
ORDER BY scan_rows DESC 
LIMIT 10;

3. 执行计划分析

功能：分析SQL的执行计划，识别执行瓶颈 适用场景：

识别低效的执行计划
评估索引使用情况
优化查询计划

核心执行计划操作：

操作类型	描述	优化建议
TABLE FULL SCAN	全表扫描	添加合适的索引
INDEX FULL SCAN	全索引扫描	优化索引设计，使用更选择性的索引
INDEX RANGE SCAN	索引范围扫描	优化WHERE条件，减少扫描范围
JOIN	表连接	优化连接顺序，添加连接条件索引
SORT	排序操作	避免不必要的排序，添加排序索引
AGGREGATE	聚合操作	添加聚合索引，优化GROUP BY子句

分析方法：

sql

-- 查询带有执行计划的慢SQL
SELECT sql_id, sql_text, plan FROM GV$OB_SLOW_SQL 
WHERE start_time >= NOW() - INTERVAL '1' HOUR 
AND plan IS NOT NULL 
ORDER BY elapsed_time DESC 
LIMIT 5;

4. 索引使用分析

功能：分析SQL语句的索引使用情况 适用场景：

识别未使用索引的SQL
评估索引效率
优化索引设计

分析要点：

是否使用了索引
使用了哪个索引
索引的选择性如何
是否需要创建新索引

分析方法：

sql

-- 查询未使用索引的慢SQL
SELECT sql_id, sql_text, plan 
FROM GV$OB_SLOW_SQL 
WHERE start_time >= NOW() - INTERVAL '1' HOUR 
AND plan LIKE '%TABLE FULL SCAN%' 
ORDER BY elapsed_time DESC 
LIMIT 10;

慢SQL优化策略

1. 索引优化

功能：通过优化索引设计，提高SQL执行效率 适用场景：

全表扫描的SQL
索引选择性差的SQL
多表连接查询

优化方法：

1.1 添加合适的索引

示例：

sql

-- 为经常查询的列添加索引
CREATE INDEX idx_user_name ON user(name);

-- 为多列查询添加联合索引
CREATE INDEX idx_order_user_status ON orders(user_id, status, create_time);

索引设计原则：

选择选择性高的列作为索引
联合索引遵循最左前缀原则
避免创建过多索引
定期优化和清理无用索引

1.2 优化现有索引

示例：

sql

-- 修改索引，添加包含列
CREATE INDEX idx_order_user_id ON orders(user_id) INCLUDE (status, amount);

-- 删除无用索引
DROP INDEX idx_old_index ON table_name;

索引优化要点：

考虑索引的包含列，减少回表操作
优化索引顺序，提高索引利用率
定期重建碎片化索引
监控索引使用情况，清理无用索引

2. SQL重写

功能：通过改写SQL语句，提高执行效率 适用场景：

复杂的SQL语句
低效的查询逻辑
不适合索引优化的SQL

优化方法：

2.1 简化查询条件

示例：

sql

-- 优化前：使用函数操作，无法使用索引
SELECT * FROM user WHERE DATE(create_time) = '2023-01-01';

-- 优化后：直接比较，可使用索引
SELECT * FROM user WHERE create_time BETWEEN '2023-01-01 00:00:00' AND '2023-01-01 23:59:59';

2.2 避免SELECT *

示例：

sql

-- 优化前：查询所有列
SELECT * FROM user WHERE id = 1;

-- 优化后：只查询需要的列
SELECT id, name, email FROM user WHERE id = 1;

2.3 优化JOIN操作

示例：

sql

-- 优化前：笛卡尔积连接
SELECT * FROM a, b WHERE a.id = b.a_id;

-- 优化后：显式指定JOIN类型
SELECT * FROM a INNER JOIN b ON a.id = b.a_id;

2.4 拆分复杂查询

示例：

sql

-- 优化前：复杂的单条SQL
SELECT * FROM (
    SELECT * FROM a WHERE status = 1
) t1 JOIN b ON t1.id = b.a_id;

-- 优化后：拆分为两条SQL，使用临时表
CREATE TEMPORARY TABLE tmp_a SELECT * FROM a WHERE status = 1;
SELECT * FROM tmp_a JOIN b ON tmp_a.id = b.a_id;
DROP TEMPORARY TABLE tmp_a;

3. 统计信息优化

功能：更新和优化表的统计信息，提高查询优化器的准确性 适用场景：

数据分布发生变化
新创建的表
执行计划不准确

优化方法：

sql

-- 更新表的统计信息
ANALYZE TABLE user;

-- 更新特定列的统计信息
ANALYZE TABLE user COLUMNS name, email;

-- 强制更新统计信息
ANALYZE TABLE user FORCE;

统计信息说明：

表的行数和大小
列的分布信息
索引的选择性
数据的直方图

4. 配置优化

功能：调整OceanBase的配置参数，优化SQL执行性能 适用场景：

系统级性能问题
特定类型SQL的优化
资源分配不合理

优化参数：

参数名	功能	建议值
`query_timeout`	查询超时时间	根据业务需求调整
`plan_cache_enabled`	是否启用执行计划缓存	TRUE
`plan_cache_size`	执行计划缓存大小	适当增大，如128MB
`max_allowed_packet`	最大包大小	根据需要调整
`sort_buffer_size`	排序缓冲区大小	适当增大，如64MB

配置方法：

sql

-- 设置执行计划缓存大小为128MB
ALTER SYSTEM SET plan_cache_size=134217728 GLOBAL;

慢SQL监控和预警

1. 实时监控

功能：实时监控慢SQL的产生和执行情况 适用场景：

及时发现慢SQL
实时性能监控
紧急问题处理

监控方法：

1.1 使用内置视图监控

sql

-- 实时监控慢SQL数量
SELECT COUNT(*) AS slow_sql_count 
FROM GV$OB_SLOW_SQL 
WHERE start_time >= NOW() - INTERVAL '5' MINUTE;

-- 监控慢SQL的趋势
SELECT DATE_FORMAT(start_time, '%Y-%m-%d %H:%i') AS time, 
       COUNT(*) AS slow_sql_count 
FROM GV$OB_SLOW_SQL 
WHERE start_time >= NOW() - INTERVAL '1' HOUR 
GROUP BY time 
ORDER BY time;

1.2 使用OCP监控

操作步骤：

登录OCP平台
导航到集群监控页面
选择"实时监控"模块
查看慢SQL相关指标
设置实时告警

2. 告警配置

功能：配置慢SQL告警规则，及时通知相关人员 适用场景：

自动发现慢SQL问题
及时响应性能问题
减少人工监控成本

配置方法：

2.1 OCP告警配置

操作步骤：

登录OCP平台
导航到"告警管理"页面
点击"创建告警规则"
设置告警名称和描述
选择监控对象（集群、租户）
设置告警条件（如慢SQL数量超过阈值）
设置告警级别和通知方式
保存告警规则

2.2 Prometheus告警配置

示例配置：

yaml

groups:
- name: oceanbase-slow-sql-alerts
  rules:
  - alert: OceanBaseHighSlowSQLCount
    expr: sum(rate(oceanbase_slow_sql_total[5m])) by (cluster, tenant) > 10
    for: 5m
    labels:
      severity: warning
    annotations:
      summary: "High slow SQL count in OceanBase cluster {{ $labels.cluster }}"
      description: "Tenant {{ $labels.tenant }} has {{ $value }} slow SQLs in the last 5 minutes"

最佳实践

1. 慢SQL定期分析

功能：定期分析慢SQL，持续优化数据库性能 适用场景：

常规性能维护
发现潜在性能问题
持续性能优化

分析流程：

收集慢SQL日志（如过去24小时）
按执行时间排序，选择Top N慢SQL
分析每个慢SQL的执行计划和瓶颈
制定优化方案（索引优化、SQL重写等）
实施优化并验证效果
记录优化过程和结果

2. 慢SQL审计

功能：审计慢SQL的产生和处理情况 适用场景：

跟踪慢SQL处理进度
评估优化效果
建立性能优化知识库

审计内容：

慢SQL的基本信息（SQL文本、执行时间等）
分析结果和瓶颈定位
优化方案和实施情况
优化前后的性能对比
负责人和处理时间

3. 开发规范

功能：建立SQL开发规范，预防慢SQL产生 适用场景：

开发阶段预防慢SQL
提高SQL代码质量
减少生产环境性能问题

规范要点：

避免全表扫描，合理使用索引
避免SELECT *，只查询需要的列
优化WHERE条件，使用索引友好的写法
合理使用JOIN操作，避免复杂连接
避免在WHERE子句中使用函数
合理设置LIMIT，避免返回大量数据

4. 测试验证

功能：在测试环境验证SQL性能 适用场景：

新功能开发和测试
SQL优化验证
版本升级测试

测试方法：

使用真实数据进行测试
模拟生产环境的负载
测试不同并发情况下的性能
对比优化前后的执行时间

常见问题（FAQ）

Q1: 如何快速定位慢SQL的瓶颈？

A1: 快速定位慢SQL瓶颈的方法：

查看慢SQL的执行时间分布，确定主要耗时阶段
分析执行计划，识别低效操作（如全表扫描、排序等）
检查扫描行数和返回行数，评估查询效率
使用EXPLAIN命令查看执行计划详情
考虑使用PROFILE命令获取更详细的执行信息

Q2: 为什么添加了索引后SQL仍然很慢？

A2: 添加索引后SQL仍然很慢的可能原因：

索引选择性差，无法有效过滤数据
索引设计不合理，没有覆盖查询条件
执行计划没有使用预期的索引
统计信息不准确，导致优化器选择了错误的执行计划
存在锁竞争或资源瓶颈

Q3: 如何处理大量慢SQL？

A3: 处理大量慢SQL的方法：

按执行时间排序，优先处理最耗时的SQL
分类分析，找出共性问题（如相同类型的查询）
制定批量优化方案，提高优化效率
考虑使用自动化工具辅助分析
建立慢SQL处理的优先级和流程

Q4: 如何预防慢SQL产生？

A4: 预防慢SQL产生的方法：

建立SQL开发规范，提高代码质量
开发阶段进行性能测试，发现潜在问题
使用SQL审查工具，自动检查SQL质量
定期分析和优化现有SQL
监控和预警慢SQL，及时发现和处理

Q5: 如何分析复杂的慢SQL？

A5: 分析复杂慢SQL的方法：

分解复杂SQL为多个简单SQL，逐步分析
查看执行计划的每个步骤，找出瓶颈环节
使用PROFILE命令获取详细的执行统计信息
考虑使用TRACE工具跟踪SQL执行的详细过程
咨询经验丰富的DBA或开发人员

Q6: 慢SQL日志对性能有影响吗？

A6: 慢SQL日志对性能的影响：

启用慢SQL日志会带来一定的性能开销
可以通过调整慢SQL阈值减少日志量
分布式表存储的开销比本地文件存储大
可以设置采样率，减少日志记录的数量
建议根据实际情况平衡日志详细程度和性能影响

Q7: 如何评估慢SQL优化效果？

A7: 评估慢SQL优化效果的方法：

对比优化前后的执行时间
查看扫描行数和返回行数的变化
监控系统整体性能的变化
观察业务响应时间的改善
长期监控慢SQL的数量变化

Q8: 执行计划缓存对慢SQL有影响吗？

A8: 执行计划缓存对慢SQL的影响：

启用执行计划缓存可以减少SQL解析和优化的时间
对于频繁执行的相同SQL，缓存执行计划可以提高性能
但对于频繁变化的SQL，缓存可能带来额外开销
可以根据实际情况调整执行计划缓存的大小和策略

Q9: 如何处理偶发性的慢SQL？

A9: 处理偶发性慢SQL的方法：

检查当时的系统负载和资源使用情况
查看是否存在锁竞争或死锁
检查网络是否存在波动
考虑使用绑定变量，避免硬解析
监控和分析慢SQL的发生规律

Q10: 慢SQL分析需要哪些权限？

A10: 慢SQL分析需要的权限：

查询慢SQL日志的权限
查询系统视图的权限（如GV$OB_SLOW_SQL）
执行EXPLAIN命令的权限
可能需要SYSTEM或SYS租户的权限
根据具体操作和配置可能需要其他权限

工具推荐

1. 内置工具

EXPLAIN：查看SQL执行计划
EXPLAIN EXTENDED：查看更详细的执行计划
PROFILE：获取SQL执行的详细统计信息
TRACE：跟踪SQL执行的详细过程
GV$OB_SLOW_SQL：查询慢SQL信息

2. OCP工具

慢SQL分析：可视化慢SQL分析和优化建议
执行计划分析：可视化执行计划分析
性能诊断：自动诊断性能问题
监控告警：实时监控和告警

3. 第三方工具

Percona Toolkit：包含多种MySQL/OceanBase性能分析工具
pt-query-digest：分析慢查询日志
MySQL Workbench：可视化SQL开发和分析工具
Navicat：数据库管理和开发工具
Grafana：可视化监控和分析

OceanBase 慢SQL分析 ​

核心概念 ​

慢SQL日志配置 ​

1. 慢SQL阈值设置 ​

2. 慢SQL日志格式 ​

3. 慢SQL日志存储 ​

慢SQL查询方法 ​

1. 查询本地慢SQL日志 ​

2. 查询分布式慢SQL表 ​

3. 使用OCP慢SQL分析 ​

慢SQL分析方法 ​

1. 执行时间分析 ​

2. 扫描行数分析 ​

3. 执行计划分析 ​

4. 索引使用分析 ​

慢SQL优化策略 ​

1. 索引优化 ​

1.1 添加合适的索引 ​

1.2 优化现有索引 ​

2. SQL重写 ​

2.1 简化查询条件 ​

2.2 避免SELECT * ​

2.3 优化JOIN操作 ​

2.4 拆分复杂查询 ​

3. 统计信息优化 ​

4. 配置优化 ​

慢SQL监控和预警 ​

1. 实时监控 ​

1.1 使用内置视图监控 ​

1.2 使用OCP监控 ​

2. 告警配置 ​

2.1 OCP告警配置 ​

2.2 Prometheus告警配置 ​

最佳实践 ​

1. 慢SQL定期分析 ​

2. 慢SQL审计 ​

3. 开发规范 ​

4. 测试验证 ​

常见问题（FAQ） ​

Q1: 如何快速定位慢SQL的瓶颈？ ​

Q2: 为什么添加了索引后SQL仍然很慢？ ​

Q3: 如何处理大量慢SQL？ ​

Q4: 如何预防慢SQL产生？ ​

Q5: 如何分析复杂的慢SQL？ ​

Q6: 慢SQL日志对性能有影响吗？ ​

Q7: 如何评估慢SQL优化效果？ ​

Q8: 执行计划缓存对慢SQL有影响吗？ ​

Q9: 如何处理偶发性的慢SQL？ ​

Q10: 慢SQL分析需要哪些权限？ ​

工具推荐 ​

1. 内置工具 ​

2. OCP工具 ​

3. 第三方工具 ​

OceanBase 慢SQL分析

核心概念

慢SQL日志配置

1. 慢SQL阈值设置

2. 慢SQL日志格式

3. 慢SQL日志存储

慢SQL查询方法

1. 查询本地慢SQL日志

2. 查询分布式慢SQL表

3. 使用OCP慢SQL分析

慢SQL分析方法

1. 执行时间分析

2. 扫描行数分析

3. 执行计划分析

4. 索引使用分析

慢SQL优化策略

1. 索引优化

1.1 添加合适的索引

1.2 优化现有索引

2. SQL重写

2.1 简化查询条件

2.2 避免SELECT *

2.3 优化JOIN操作

2.4 拆分复杂查询

3. 统计信息优化

4. 配置优化

慢SQL监控和预警

1. 实时监控

1.1 使用内置视图监控

1.2 使用OCP监控

2. 告警配置

2.1 OCP告警配置

2.2 Prometheus告警配置

最佳实践

1. 慢SQL定期分析

2. 慢SQL审计

3. 开发规范

4. 测试验证

常见问题（FAQ）

Q1: 如何快速定位慢SQL的瓶颈？

Q2: 为什么添加了索引后SQL仍然很慢？

Q3: 如何处理大量慢SQL？

Q4: 如何预防慢SQL产生？

Q5: 如何分析复杂的慢SQL？

Q6: 慢SQL日志对性能有影响吗？

Q7: 如何评估慢SQL优化效果？

Q8: 执行计划缓存对慢SQL有影响吗？

Q9: 如何处理偶发性的慢SQL？

Q10: 慢SQL分析需要哪些权限？

工具推荐

1. 内置工具

2. OCP工具

3. 第三方工具