项目三：OceanBase性能优化项目

OceanBase 性能优化项目详细方案

项目概述

本项目旨在系统性地优化存在性能问题的OceanBase应用系统，通过识别瓶颈、分析执行计划、优化SQL和索引等手段，全面提升系统性能。

一、性能瓶颈识别

1.1 系统级性能监控

1.1.1 CPU使用率监控

-- 查看租户CPU使用情况
SELECT tenant_name, 
       cpu_total, 
       cpu_assigned, 
       cpu_max_assigned,
       cpu_min_assigned
FROM oceanbase.DBA_OB_TENANTS;

-- 查看服务器CPU使用率
SELECT svr_ip, 
       cpu_capacity, 
       cpu_assigned, 
       cpu_assigned_percent
FROM oceanbase.GV$OB_SERVERS;

1.1.2 内存使用监控

-- 查看租户内存使用情况
SELECT tenant_name,
       memory_size,
       max_memory_size,
       min_memory_size
FROM oceanbase.DBA_OB_TENANTS;

-- 查看内存详细分配
SELECT tenant_id,
       ctx_name,
       hold,
       used
FROM oceanbase.GV$OB_MEMORY
ORDER BY used DESC;

1.1.3 磁盘I/O监控

-- 查看磁盘使用情况
SELECT svr_ip,
       total_size,
       used_size,
       free_size,
       used_percent
FROM oceanbase.GV$OB_SERVERS;

-- 查看I/O等待事件
SELECT event,
       total_waits,
       time_waited,
       average_wait
FROM oceanbase.GV$SYSTEM_EVENT
WHERE event LIKE '%I/O%'
ORDER BY time_waited DESC;

1.2 SQL性能分析

1.2.1 慢查询识别

-- 查看慢查询TOP 10
SELECT /*+ QUERY_TIMEOUT(30000000) */
       sql_id,
       query_sql,
       elapsed_time,
       execute_count,
       avg_elapsed_time
FROM oceanbase.GV$OB_SQL_AUDIT
WHERE elapsed_time > 1000000  -- 超过1秒
ORDER BY elapsed_time DESC
LIMIT 10;

1.2.2 高频SQL分析

-- 查看执行次数最多的SQL
SELECT sql_id,
       query_sql,
       execute_count,
       total_elapsed_time,
       avg_elapsed_time
FROM oceanbase.GV$OB_SQL_AUDIT
ORDER BY execute_count DESC
LIMIT 20;

1.3 等待事件分析

-- 查看系统等待事件
SELECT event_name,
       wait_count,
       total_wait_time,
       avg_wait_time
FROM oceanbase.GV$SESSION_WAIT_HISTORY
GROUP BY event_name
ORDER BY total_wait_time DESC;

-- 查看会话等待事件
SELECT sid,
       event,
       state,
       wait_time,
       seconds_in_wait
FROM oceanbase.GV$SESSION_WAIT
WHERE state = 'WAITING';

参考文档：OceanBase性能监控指南

二、执行计划分析

2.1 获取执行计划

2.1.1 使用EXPLAIN分析

-- 基础EXPLAIN
EXPLAIN SELECT * FROM orders WHERE order_date > '2024-01-01';

-- 详细执行计划
EXPLAIN EXTENDED SELECT * FROM orders WHERE order_date > '2024-01-01';

-- 格式化输出
EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE order_date > '2024-01-01';

2.1.2 实际执行计划获取

-- 开启执行计划收集
SET ob_enable_plan_cache = 0;
SET ob_enable_sql_audit = 1;

-- 执行SQL
SELECT * FROM orders WHERE order_date > '2024-01-01';

-- 查看实际执行计划
SELECT plan_id,
       operation,
       object_name,
       cost,
       cardinality,
       bytes
FROM oceanbase.GV$OB_PLAN_CACHE_PLAN_EXPLAIN
WHERE sql_id = 'YOUR_SQL_ID';

2.2 执行计划优化点识别

2.2.1 索引使用分析

• 全表扫描识别：Operation为"TABLE SCAN"
• 索引扫描效率：检查Cardinality是否准确
• 回表次数：评估是否需要覆盖索引

2.2.2 连接方式优化

• Nested Loop Join：适合小数据量
• Hash Join：适合大数据量等值连接
• Merge Join：适合已排序数据

2.3 统计信息更新

-- 手动收集表统计信息
CALL DBMS_STATS.GATHER_TABLE_STATS('schema_name', 'table_name');

-- 收集索引统计信息
CALL DBMS_STATS.GATHER_INDEX_STATS('schema_name', 'index_name');

-- 设置自动收集
ALTER SYSTEM SET enable_sql_audit = TRUE;

参考文档：OceanBase执行计划详解

三、SQL语句优化

3.1 查询重写技巧

3.1.1 子查询优化

-- 优化前：使用IN子查询
SELECT * FROM orders 
WHERE customer_id IN (
    SELECT customer_id FROM customers WHERE city = 'Beijing'
);

-- 优化后：使用JOIN
SELECT o.* FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
WHERE c.city = 'Beijing';

3.1.2 OR条件优化

-- 优化前：使用OR
SELECT * FROM orders 
WHERE status = 'pending' OR status = 'processing';

-- 优化后：使用IN
SELECT * FROM orders 
WHERE status IN ('pending', 'processing');

-- 或使用UNION ALL（当索引不同时）
SELECT * FROM orders WHERE status = 'pending'
UNION ALL
SELECT * FROM orders WHERE status = 'processing';

3.1.3 分页查询优化

-- 优化前：大偏移量分页
SELECT * FROM orders 
ORDER BY order_id 
LIMIT 1000000, 20;

-- 优化后：使用延迟关联
SELECT o.* FROM orders o
INNER JOIN (
    SELECT order_id FROM orders 
    ORDER BY order_id 
    LIMIT 1000000, 20
) t ON o.order_id = t.order_id;

3.2 批量操作优化

3.2.1 批量插入

-- 使用批量INSERT
INSERT INTO orders (order_id, customer_id, amount) VALUES
(1, 101, 1000),
(2, 102, 2000),
(3, 103, 3000);

-- 设置批量参数
SET ob_batch_insert_size = 1000;

3.2.2 批量更新

-- 使用CASE WHEN批量更新
UPDATE orders 
SET status = CASE order_id
    WHEN 1 THEN 'completed'
    WHEN 2 THEN 'cancelled'
    WHEN 3 THEN 'pending'
END
WHERE order_id IN (1, 2, 3);

3.3 函数使用优化

-- 避免在WHERE子句中使用函数
-- 优化前
SELECT * FROM orders WHERE DATE(order_time) = '2024-01-01';

-- 优化后
SELECT * FROM orders 
WHERE order_time >= '2024-01-01 00:00:00' 
  AND order_time < '2024-01-02 00:00:00';

参考文档：OceanBase SQL优化最佳实践

四、索引设计优化

4.1 索引创建策略

4.1.1 单列索引

-- 创建普通索引
CREATE INDEX idx_order_date ON orders(order_date);

-- 创建唯一索引
CREATE UNIQUE INDEX uk_order_no ON orders(order_no);

4.1.2 组合索引

-- 遵循最左前缀原则
CREATE INDEX idx_customer_status_date 
ON orders(customer_id, status, order_date);

4.1.3 覆盖索引

-- 包含查询所需的所有列
CREATE INDEX idx_covering 
ON orders(customer_id, order_date, amount, status);

4.2 索引维护

4.2.1 索引使用情况分析

-- 查看索引使用统计
SELECT table_name,
       index_name,
       used_count,
       last_used_time
FROM oceanbase.DBA_OB_INDEX_USAGE
ORDER BY used_count DESC;

4.2.2 无效索引清理

-- 识别未使用的索引
SELECT index_name 
FROM oceanbase.DBA_INDEXES i
WHERE NOT EXISTS (
    SELECT 1 FROM oceanbase.DBA_OB_INDEX_USAGE u
    WHERE u.index_name = i.index_name
    AND u.used_count > 0
);

4.3 索引重建

-- 在线重建索引
ALTER INDEX idx_order_date REBUILD ONLINE;

-- 并行重建
ALTER INDEX idx_order_date REBUILD PARALLEL 4;

参考文档：OceanBase索引设计指南

五、并行参数调整

5.1 并行查询配置

5.1.1 系统级并行参数

-- 设置并行度
ALTER SYSTEM SET parallel_servers_target = 64;
ALTER SYSTEM SET parallel_max_servers = 128;

-- 自动并行度
ALTER SYSTEM SET enable_parallel_query = TRUE;
ALTER SYSTEM SET parallel_degree_policy = 'AUTO';

5.1.2 会话级并行设置

-- 会话级设置
SET parallel_degree = 8;
SET parallel_min_scan_time_threshold = 10;

5.2 并行DML配置

-- 启用并行DML
ALTER SESSION ENABLE PARALLEL DML;

-- 并行INSERT
INSERT /*+ PARALLEL(8) */ INTO orders_archive
SELECT /*+ PARALLEL(8) */ * FROM orders 
WHERE order_date < '2023-01-01';

-- 并行UPDATE
UPDATE /*+ PARALLEL(4) */ orders 
SET status = 'archived' 
WHERE order_date < '2023-01-01';

5.3 并行DDL操作

-- 并行创建索引
CREATE INDEX idx_large_table PARALLEL 8 
ON large_table(column1, column2);

-- 并行表分析
ANALYZE TABLE large_table COMPUTE STATISTICS PARALLEL 8;

参考文档：OceanBase并行执行指南

六、资源分配优化

6.1 租户资源调整

6.1.1 CPU资源分配

-- 调整租户CPU资源
ALTER RESOURCE TENANT tenant_name 
RESOURCE_POOL_LIST = ('pool1') 
SET CPU = 16;

-- 设置CPU最小保证
ALTER RESOURCE TENANT tenant_name 
SET MIN_CPU = 8;

6.1.2 内存资源分配

-- 调整租户内存
ALTER RESOURCE TENANT tenant_name 
SET MEMORY_SIZE = '32G';

-- 设置内存上下限
ALTER RESOURCE TENANT tenant_name 
SET MAX_MEMORY_SIZE = '64G',
    MIN_MEMORY_SIZE = '16G';

6.2 资源池管理

-- 创建资源池
CREATE RESOURCE POOL pool_high_performance
UNIT = 'unit_8c32g',
UNIT_NUM = 3,
ZONE_LIST = ('zone1', 'zone2', 'zone3');

-- 修改资源池
ALTER RESOURCE POOL pool_high_performance 
UNIT_NUM = 4;

6.3 工作区内存优化

-- 设置排序区大小
ALTER SYSTEM SET ob_sort_area_size = '256M';

-- 设置哈希区大小  
ALTER SYSTEM SET ob_hash_area_size = '512M';

-- 设置临时表空间
ALTER SYSTEM SET ob_temp_tablespace_size = '10G';

参考文档：OceanBase资源管理

七、优化效果验证

7.1 性能基准测试

7.1.1 建立基准

-- 记录优化前性能指标
CREATE TABLE performance_baseline (
    test_date DATE,
    metric_name VARCHAR(100),
    metric_value DECIMAL(20,2),
    test_type VARCHAR(50)
);

-- 插入基准数据
INSERT INTO performance_baseline 
SELECT CURRENT_DATE, 'avg_response_time', 
       AVG(elapsed_time)/1000000, 'before_optimization'
FROM oceanbase.GV$OB_SQL_AUDIT
WHERE sql_id IN ('SQL_ID_1', 'SQL_ID_2');

7.1.2 压力测试

# 使用sysbench进行压力测试
sysbench oltp_read_write \
  --mysql-host=127.0.0.1 \
  --mysql-port=2881 \
  --mysql-user=root \
  --mysql-password=password \
  --mysql-db=test \
  --tables=10 \
  --table-size=1000000 \
  --threads=32 \
  --time=300 \
  --report-interval=10 \
  run

7.2 优化效果对比

7.2.1 响应时间对比

-- 对比平均响应时间
SELECT 
    'Before' AS phase,
    AVG(elapsed_time)/1000000 AS avg_response_time_sec,
    MAX(elapsed_time)/1000000 AS max_response_time_sec,
    MIN(elapsed_time)/1000000 AS min_response_time_sec
FROM oceanbase.GV$OB_SQL_AUDIT
WHERE sql_id = 'TARGET_SQL_ID'
  AND request_time < '2024-01-01'
UNION ALL
SELECT 
    'After' AS phase,
    AVG(elapsed_time)/1000000,
    MAX(elapsed_time)/1000000,
    MIN(elapsed_time)/1000000
FROM oceanbase.GV$OB_SQL_AUDIT  
WHERE sql_id = 'TARGET_SQL_ID'
  AND request_time >= '2024-01-01';

7.2.2 吞吐量对比

-- 计算TPS/QPS
SELECT 
    DATE_FORMAT(request_time, '%Y-%m-%d %H:%i:00') AS time_window,
    COUNT(*) / 60 AS qps,
    SUM(CASE WHEN is_hit_plan = 1 THEN 1 ELSE 0 END) / COUNT(*) AS plan_cache_hit_rate
FROM oceanbase.GV$OB_SQL_AUDIT
WHERE request_time >= DATE_SUB(NOW(), INTERVAL 1 HOUR)
GROUP BY time_window
ORDER BY time_window;

7.3 持续监控

7.3.1 设置告警规则

-- 创建性能告警表
CREATE TABLE performance_alerts (
    alert_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    alert_time DATETIME,
    metric_name VARCHAR(100),
    threshold_value DECIMAL(20,2),
    actual_value DECIMAL(20,2),
    alert_level VARCHAR(20)
);

-- 定期检查并告警
DELIMITER //
CREATE PROCEDURE check_performance_alerts()
BEGIN
    DECLARE avg_response_time DECIMAL(20,2);
    
    SELECT AVG(elapsed_time)/1000000 INTO avg_response_time
    FROM oceanbase.GV$OB_SQL_AUDIT
    WHERE request_time >= DATE_SUB(NOW(), INTERVAL 5 MINUTE);
    
    IF avg_response_time > 1.0 THEN
        INSERT INTO performance_alerts 
        (alert_time, metric_name, threshold_value, actual_value, alert_level)
        VALUES (NOW(), 'avg_response_time', 1.0, avg_response_time, 'WARNING');
    END IF;
END//
DELIMITER ;

参考文档：OceanBase性能测试指南

八、优化报告模板

8.1 执行摘要

项目名称：OceanBase应用系统性能优化
优化周期：2024年1月1日 - 2024年1月31日
优化团队：数据库优化小组

8.2 优化前问题分析

8.2.1 性能瓶颈汇总

问题类型	影响范围	严重程度	具体表现
慢查询	订单查询模块	高	平均响应时间>3秒
索引缺失	用户表	中	全表扫描频繁
资源不足	整体系统	高	CPU使用率>90%

8.2.2 根因分析

• SQL语句未优化，存在大量子查询
• 索引设计不合理，未覆盖高频查询
• 并行度设置过低，未充分利用资源

8.3 优化措施实施

8.3.1 SQL优化清单

SQL ID	优化前耗时	优化后耗时	优化方法	改善率
SQL_001	3.2s	0.3s	改写子查询为JOIN	90.6%
SQL_002	2.8s	0.5s	添加覆盖索引	82.1%
SQL_003	5.1s	0.8s	优化分页查询	84.3%

8.3.2 索引优化清单

-- 新增索引列表
CREATE INDEX idx_order_customer_date ON orders(customer_id, order_date);
CREATE INDEX idx_user_city_status ON users(city, status);
CREATE INDEX idx_product_category ON products(category_id, price);

-- 删除冗余索引
DROP INDEX idx_redundant_1;
DROP INDEX idx_redundant_2;

8.4 优化效果总结

8.4.1 性能指标对比

指标项	优化前	优化后	改善幅度
平均响应时间	2.5秒	0.4秒	84%
P99响应时间	8.2秒	1.2秒	85.4%
TPS	1200	5800	383%
CPU使用率	92%	65%	29.3%
内存使用率	85%	70%	17.6%

8.4.2 业务价值

• 用户体验显著提升，页面加载时间减少80%
• 系统处理能力提升4倍，支持更多并发用户
• 资源利用率优化，降低硬件成本30%

8.5 后续建议

1. 定期优化计划

   • 每月进行SQL审查
   • 季度索引评估
   • 半年度架构回顾

2. 自动化监控

   • 部署自动化性能监控系统
   • 设置性能基线和告警阈值
   • 建立性能问题快速响应机制

3. 知识库建设

   • 整理优化案例库
   • 制定SQL编写规范
   • 定期培训开发团队

九、常见问题与解决方案

9.1 内存溢出问题

问题表现：

ERROR 4030 (HY000): OceanBase out of memory

解决方案：

-- 1. 增加租户内存
ALTER RESOURCE TENANT tenant_name SET MEMORY_SIZE = '64G';

-- 2. 优化工作区内存
SET ob_sort_area_size = '512M';
SET ob_hash_area_size = '1G';

-- 3. 限制查询内存使用
SET ob_query_timeout = 30000000;
SET ob_sql_work_area_percentage = 50;

9.2 锁等待超时

问题表现：

ERROR 4012 (HY000): Timeout waiting for lock

解决方案：

-- 1. 查看锁等待情况
SELECT * FROM oceanbase.GV$OB_LOCKS 
WHERE block_session_id IS NOT NULL;

-- 2. 调整锁超时时间
SET ob_query_timeout = 60000000;
SET ob_trx_timeout = 100000000;

-- 3. 优化事务处理
-- 缩短事务时间，避免大事务

9.3 执行计划不稳定

解决方案：

-- 1. 固定执行计划
CREATE OUTLINE outline_name 
ON SELECT * FROM orders WHERE order_date > '2024-01-01'
USING HINT INDEX(orders idx_order_date);

-- 2. 更新统计信息
CALL DBMS_STATS.GATHER_TABLE_STATS('schema', 'table', 
     method_opt => 'FOR ALL COLUMNS SIZE AUTO');

-- 3. 清理计划缓存
ALTER SYSTEM FLUSH PLAN CACHE;

十、参考资源

官方文档

• OceanBase官方文档中心
• OceanBase性能调优指南
• OceanBase SQL参考
• OceanBase最佳实践

社区资源

• OceanBase社区论坛
• OceanBase GitHub
• OceanBase技术博客

工具下载

• OBClient下载
• OceanBase开发者工具
• 性能诊断工具集

项目评估检查清单

✅ 项目完成度

• 完成性能瓶颈识别
• 完成执行计划分析
• 完成SQL语句优化
• 完成索引设计优化
• 完成并行参数调整
• 完成资源分配优化
• 完成优化效果验证
• 完成优化报告撰写

✅ 技术方案合理性

• 分析方法科学全面
• 优化策略针对性强
• 方案具有可实施性
• 考虑系统整体影响

✅ 操作规范性

• 遵循操作规范流程
• 做好变更前备份
• 记录详细操作日志
• 执行充分测试验证

✅ 问题解决能力

• 独立分析问题根因
• 制定有效解决方案
• 处理突发异常情况
• 总结经验教训

✅ 文档完整性

• 文档结构清晰完整
• 技术细节描述准确
• 包含必要图表说明
• 提供参考资料链接

✅ 团队协作能力

• 有效沟通协调
• 合理分工协作
• 知识共享传承
• 按时完成任务

---