OceanBase 热点表最佳实践

热点表（Hot Table）是指在 OceanBase 数据库中，某些数据行或表在高并发场景下频繁被读写，导致系统性能出现瓶颈。热点表问题通常与 SQL 业务热点相关，在读热点场景中尤为明显。

热点表问题会对数据库系统产生以下影响：

性能瓶颈：CPU、内存或磁盘 IO 过度消耗
锁争用：对同一行记录的并发更新或查询可能引发锁争用问题，增加事务等待时间
用户体验下降：由于响应时间延长，业务性能可能显著下降，甚至导致服务不可用

热点表分类

类型	特征	典型场景	优化策略
读热点表	短时间内频繁访问同一行记录，单一账号的 SQL 请求突刺	• 配置参数表 • 用户导流规则表 • 业务参数表	• 使用复制表（Replication Table） • 引入缓存机制 • 流量控制与限流 • 在业务中避免频繁访问热点数据
写热点表	频繁插入、更新、删除操作	• Buffer 表（临时数据、日志缓存） • 流水表 • 日志表	• 分区策略优化 • 批量处理 • 异步写入 • 自适应负载均衡

表结构设计：从源头避免热点表

在创建表结构时，考虑以下原则来避免热点问题，减少后续优化的复杂度：

合理选择主键
- 避免使用自增 ID 作为主键（容易形成热点）
- 使用复合主键或分布式 ID
- 考虑业务逻辑的分散性
分区策略设计
- 按时间分区：适合日志表、流水表
- 按用户 ID 分区：适合用户相关表
- 按业务维度分区：适合多租户场景
索引设计
- 避免单点热点索引
- 合理设计复合索引
- 考虑查询模式的分散性

设计示例

-- 避免热点的表设计示例
CREATE TABLE user_order (
    user_id BIGINT,           -- 分区键
    sequence_id BIGINT,       -- 序列号
    order_amount DECIMAL(10,2),
    create_time TIMESTAMP,
    PRIMARY KEY (user_id, sequence_id)  -- 复合主键
) PARTITION BY HASH(user_id) PARTITIONS 16;  -- 按用户 ID 分区

-- 创建表组
CREATE TABLEGROUP tg_user_related SHARDING = 'PARTITION';
-- 将 user_order 表加入表组
ALTER TABLEGROUP tg_user_related ADD user_order;

-- 查看表组中的表
SELECT * FROM oceanbase.DBA_OB_TABLEGROUP_TABLES WHERE tablegroup_name = 'TG_USER_RELATED';

定位热点表

在 OceanBase 数据库中，没有直接的热点表视图，但可以通过组合查询多个性能视图来识别热点表。以下是推荐的最佳实践方法，适用于开发者和 DBA 进行性能调优与资源隔离规划。

方法一：使用 GV$OB_SQL_AUDIT 系统视图诊断热点 SQL

OceanBase 数据库提供审计视图 GV$OB_SQL_AUDIT，可用于聚合分析 SQL 执行行为，识别资源消耗高的 SQL，从而定位热点表。以下查询可找出单位时间内读取数据行数最多的前 10 条 SQL，通常这些就是造成读热点的根源。

SELECT /*+READ_CONSISTENCY(WEAK), QUERY_TIMEOUT(100000000)*/
  svr_ip, sql_id,
  tenant_id, tenant_name, user_name, db_name, plan_id,
  COUNT(*) executions,
  MAX(event) event,
  MAX(table_scan) table_scan,
  SUM(CASE WHEN ret_code = 0 THEN 0 ELSE 1 END) fail_times,
  SUM(rpc_count) rpc_count,
  SUM(retry_cnt) retry_cnt,
  SUM(CASE WHEN plan_type = 2 THEN 1 ELSE 0 END) remote_plans,
  SUM(CASE WHEN is_hit_plan = 1 THEN 0 ELSE 1 END) miss_plans,
  ROUND(AVG(elapsed_time)) elapsed_time,
  ROUND(MAX(elapsed_time)) max_elapsed_time,
  ROUND(AVG(execute_time)) execute_time,
  ROUND(AVG((execute_time - total_wait_time_micro + get_plan_time))) cpu_time,
  ROUND(AVG(queue_time)) queue_time,
  ROUND(AVG(net_wait_time)) netwait_time,
  ROUND(AVG(user_io_wait_time)) iowait_time,
  ROUND(AVG(return_rows)) return_rows,
  ROUND(AVG(affected_rows)) affected_rows,
  SUM(memstore_read_row_count + ssstore_read_row_count) AS total_row_count
FROM GV$OB_SQL_AUDIT
WHERE is_inner_sql = 0
GROUP BY svr_ip, sql_id 
ORDER BY total_row_count DESC 
LIMIT 10;

关键指标说明：

total_row_count：总读取行数（memstore_read_row_count + ssstore_read_row_count），用于识别读热点。
retry_cnt：重试次数，异常高可能意味着锁冲突（写热点）。
cpu_time：实际 CPU 消耗时间，若远高于平均值说明计算密集型 SQL。
event：锁等待事件（如 row lock wait）可直接指向写热点。

方法二：使用 OCP 监控热点表

虽然上述 SQL 可手动执行，但推荐使用 OCP 进行持续监控与趋势分析，这是最直观且无需编码的方式。OCP（OceanBase Cloud Platform）提供开箱即用的热点表监控面板、自动聚合、异常检测。OCP 内置了 SQL TopN、表访问频次、等待事件等监控项，可直接定位热点表，避免手动编写复杂 SQL。更多信息，参考 OCP 文档。

方法三：使用 sql_diagnoser 自定义规则 + 脚本解析

此方法适用于批量自动化分析：

启动 sql_diagnoser 并连接目标租户
设置时间窗口（如最近 1 小时）、调高采样数（默认 10000 可改为 50000）

配置自定义诊断规则（类似 SQL WHERE 条件），例如

-- 执行次数 > 100 且单次耗时 > 100ms 的 SQL 将被标记为可疑
count > 100 AND elapsed_time > 100000

导出诊断结果（CSV 或 JSON）
使用脚本（Python/Shell）提取 SQL 中的表名，统计每张表的出现频率，识别出热点表。

更多信息，参考 sql_diagnoser 文档。

典型场景与解决方案

场景一：Buffer 表优化

场景描述：Buffer 表是用于缓存的物理表，通常在交易过程中临时存储大量数据，并在处理完成后删除。由于 OceanBase 数据库的 LSM-tree 采用追加写入的特性，Buffer 表可能会导致内存和存储空间的膨胀，从而影响查询和 DML 操作的性能。

解决方案：

设置 table_mode 属性

-- 创建 Buffer 表时指定 table_mode
CREATE TABLE buffer_table (
    id BIGINT,
    data VARCHAR(1000),
    create_time TIMESTAMP
) TABLE_MODE = 'queuing';

-- 或者修改现有表的 table_mode
ALTER TABLE buffer_table TABLE_MODE = 'queuing';

除了 NORMAL 模式外，所有其他 TABLE_MODE 值（包括 QUEUING、MODERATE、SUPER、EXTREME）都属于 QUEUING 表。

模式	转储后触发合并的概率	说明
NORMAL	极低	默认模式，几乎不会因转储自动触发合并，适用于写多读性能一致性要求不高的场景。
QUEUING	低	基础规则的 QUEUING 表，轻微促进合并流程。
MODERATE	中等	更积极地推动合并，平衡性能与资源消耗。
SUPER	高	高频合并，适合对查询延迟敏感的负载。
EXTREME	极高	最激进的合并策略，尽可能地完成合并任务。

合理设计分区策略

-- 按时间分区示例
CREATE TABLE buffer_table (
      id BIGINT,
      data VARCHAR(1000),
      create_time TIMESTAMP
) PARTITION BY RANGE (UNIX_TIMESTAMP(create_time)) (
      PARTITION p202401 VALUES LESS THAN (UNIX_TIMESTAMP('2024-02-01 00:00:00')),
      PARTITION p202402 VALUES LESS THAN (UNIX_TIMESTAMP('2024-03-01 00:00:00'))
) TABLE_MODE = 'queuing';

说明

更多关于 OceanBase 数据库分区表和热点表优化的详细信息，请参考 OceanBase 官方文档。

场景二：复制表优化

场景描述：读多写少的参数表、导流表，要求高可用、低延迟读取。这类表通常具有极高的访问量，业务对查询性能要求极致，需要尽量本地化查询，因此建议采用复制表策略。

解决方案：

创建复制表

-- 将普通表转为复制表
ALTER TABLE config_table DUPLICATE_SCOPE = 'cluster';

复制表特性
- 每个副本都存储完整数据，支持本地化查询
- 可在任意健康副本上读取最新数据，提高读并发能力
- 减少跨节点网络开销，实现极致读性能
- 牺牲少量写入性能换取更高读并发能力
- 特别适合高访问量、对查询性能要求极致的场景

注意

复制表适用于对写入延迟不敏感，但对读取延迟要求高的场景。

场景三：热点行锁冲突优化

场景描述：当业务场景涉及对热点行的高频并发更新（如用户积分、订单状态流转等），若直接采用传统单行事务或全表 DELETE 操作，极易引发严重的锁冲突与性能瓶颈。

解决方案：

V4.X 提前解行锁机制（数据库自动执行，无需用户干预）。
显式事务处理，及时 commit。
将更新操作延迟到 commit 点执行，减少持有锁的时间。
将热点行打散。增加分区键，将一行数据的竞争变为多个不同行上的竞争。

-- 显式事务处理示例
START TRANSACTION;
-- 检查用户是否存在
SELECT points INTO @points FROM user_points WHERE user_id = 123 FOR UPDATE;
-- 更新积分
UPDATE user_points SET points = points + 100 WHERE user_id = 123;
-- 更新订单状态
UPDATE order_status SET status = 'processing' WHERE order_id = 456;
-- 提交事务
COMMIT;
-- 若出错则 ROLLBACK
-- ROLLBACK;

场景四：热点表批量数据更新优化

场景描述：当需要对热点表进行大批量数据更新或删除操作时，传统的逐行操作会导致严重的性能问题和锁冲突。

解决方案：

合适的分区策略 + 按分区批量处理

-- 按用户 ID 分区示例
CREATE TABLE user_data (
    user_id BIGINT,
    data VARCHAR(1000)
) PARTITION BY RANGE(user_id) (
    PARTITION p0 VALUES LESS THAN (1000000),
    PARTITION p1 VALUES LESS THAN (2000000),
    PARTITION p2 VALUES LESS THAN (3000000),
    PARTITION p3 VALUES LESS THAN (4000000)
);

将批量删除变为 TRUNCATE 分区或 DROP 分区的操作
合适的 batch size，控制事务的大小，避免单个事务过大。

场景五：汇聚表优化

场景描述：如报表统计、BI 分析、聚合计算类表，通常包含海量数据，查询复杂度高。

解决方案：

列存表

-- 创建列存表
CREATE TABLE sales_data (
    product_id INT,
    sale_date DATE,
    amount DECIMAL
) WITH COLUMN GROUP(each column);

列存优势：

提升 OLAP 类查询性能（如 SUM、AVG、GROUP BY）。
减少 I/O，仅读取相关列数据。

并行查询

-- 手动指定并行度
SELECT /*+ENABLE_PARALLEL_DML PARALLEL(4)*/ 
       SUM(amount) 
FROM sales_data;

分区 + 表组

分区策略：
- 按时间、地域等维度进行一级或二级分区。
- 减少单次扫描的数据量，提高查询效率。
表组设计：
- 将关联性强的表放在同一台机器，利用 Partition Wise Join 减少网络传输。

物化视图 & Skip Index

物化视图：预计算聚合结果，加快复杂查询响应。

示例：

-- 创建表 sales_data
CREATE TABLE sales_data (
    product_id INT,
    sale_date DATE,
    amount DECIMAL
);

 -- 创建物化视图

 CREATE MATERIALIZED VIEW mv_sales_filtered
 ENABLE QUERY REWRITE
 AS
 SELECT s.product_id, s.amount, s.sale_date, p.product_name
 FROM sales_data s
 JOIN products p ON s.product_id = p.id
 WHERE s.sale_date >= '2024-01-01';

 -- 使用物化视图查询
 SELECT product_id, amount, sale_date, product_name 
 FROM mv_sales_filtered 
 WHERE product_id = 101;

 -- 不使用物化视图查询
 SELECT s.product_id, s.amount, s.sale_date, p.product_name
 FROM sales_data s
 JOIN products p ON s.product_id = p.id
 WHERE s.product_id = 101
 AND s.sale_date >= '2024-01-01';

Skip Index：跳过无意义数据块扫描，适用于高过滤率查询。

示例：

-- 1. 创建带有 Skip Index 的表
CREATE TABLE sales_data_skidx(
  product_id INT SKIP_INDEX(MIN_MAX),
  sale_date DATE SKIP_INDEX(MIN_MAX),
  amount DECIMAL(10,2) SKIP_INDEX(MIN_MAX, SUM),
  region VARCHAR(50) SKIP_INDEX(MIN_MAX)
);

-- 2. 插入测试数据
INSERT INTO sales_data_skidx VALUES 
  (1, '2024-01-01', 100.50, 'North'),
  (2, '2024-01-02', 200.75, 'South'),
  (3, '2024-01-01', 150.25, 'North'),
  (4, '2024-01-03', 300.00, 'East');


-- 3. 使用 Skip Index 的范围查询
SELECT /*+ TRACE_ID('skip_index_range') */ 
  COUNT(*) FROM sales_data_skidx 
  WHERE sale_date BETWEEN '2024-01-01' AND '2024-01-02';

-- 4. 使用 Skip Index 的聚合查询
SELECT /*+ TRACE_ID('skip_index_agg') */ 
  SUM(amount) FROM sales_data_skidx 
  WHERE region = 'North';

场景六：关键字限流优化

场景描述：当某些特定的关键字或参数值被频繁访问时，可以通过关键字限流来缓解热点问题。这种方法特别适用于读热点场景，能够快速降低数据库压力。

解决方案：

创建限流规则

-- 限制特定表的查询并发数
CREATE CONCURRENT_LIMITING_RULE IF NOT EXISTS `test_limit_rule`
ON test.test_table
TO 'root'@'%'
FOR SELECT
FILTER BY KEYWORD('test_table')
WITH MAX_CONCURRENCY = 10;

注意

关键字限流是一种快速缓解热点问题的有效方法，但需要合理设置限流阈值，避免影响正常业务。当超过最大并发数时，SQL 会直接失败，需要应用层实现重试机制。

热点表分类

表结构设计：从源头避免热点表

设计示例

定位热点表

方法一：使用 GV$OB_SQL_AUDIT 系统视图诊断热点 SQL

方法二：使用 OCP 监控热点表

方法三：使用 sql_diagnoser 自定义规则 + 脚本解析

典型场景与解决方案

场景一：Buffer 表优化

说明

场景二：复制表优化

注意

场景三：热点行锁冲突优化

场景四：热点表批量数据更新优化

场景五：汇聚表优化

场景六：关键字限流优化

注意

参考文档