OceanBase中简单JOIN查询性能低下的原因排查

环境 #

• OceanBase社区版4.2.1 - MySQL模式，3个可用区

我正在为一个需要复杂关联的项目评估OceanBase。在相同schema和数据下，某查询在MySQL 5.7中仅需200ms，在OceanBase中却耗时超15秒。

1. 表结构

-- OceanBase/MySQL
CREATE TABLE users (
    id BIGINT PRIMARY KEY,
    name VARCHAR(100),
    created_at DATETIME
) PARTITION BY HASH(id) PARTITIONS 8;

CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    user_id BIGINT,
    amount DECIMAL(10,2),
    INDEX idx_user (user_id)
) PARTITION BY HASH(id) PARTITIONS 16;

两张表索引配置：

• users.id为主键
• orders.user_id拥有二级索引idx_user

2. 存在性能问题的查询

SELECT u.name, SUM(o.amount) 
FROM users u 
JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2023-01-01'
GROUP BY u.id;

相同数据量（50万条用户数据、500万条订单数据）下，MySQL 5.7（InnoDB）执行该查询约200ms，OceanBase耗时超15秒；EXPLAIN显示二者执行计划差异明显。

我已尝试使用优化器提示：

SELECT /*+ LEADING(u o) USE_NL(o) */ u.name, SUM(o.amount)
FROM users u JOIN orders o ON u.id = o.user_id...;

OceanBase的执行计划如下：

-- OceanBase EXPLAIN显示全分区扫描
| ===========================================
| ID | OPERATOR    | NAME  | EST. ROWS |
| -------------------------------------------
| 0  | HASH GROUP BY       |       | 100000    |
| 1  | HASH JOIN           |       | 5000000   |
| 2  | TABLE SCAN (PART)   | u     | 100000    |
| 3  | TABLE SCAN (PART)   | o     | 5000000   |

问题 #

为何该简单JOIN查询在OceanBase中性能表现如此糟糕？

问题分析与解决方案 #

1. 核心原因：分区策略与索引利用不匹配

当前的HASH分区策略是性能暴跌的关键：

• users表按id分区，但查询过滤条件是created_at，无法触发分区裁剪，必须扫描所有8个分区；
• orders表按id分区，关联条件是user_id，导致OceanBase无法通过分区定位关联数据，只能扫描全部16个分区的全表数据；
• 优化器选择了效率更低的HASH JOIN+全分区扫描，而非MySQL中基于索引的嵌套循环连接，进一步放大了性能差距。

2. 针对性优化方案

（1）调整orders表分区策略，对齐关联字段

将orders的分区键改为user_id，让关联时能直接通过用户ID定位对应分区，避免全分区扫描：

ALTER TABLE orders PARTITION BY HASH(user_id) PARTITIONS 8;

建议users和orders的分区数量保持一致，减少跨分区关联开销。

（2）给users表添加覆盖索引，优化过滤效率

当前users仅靠主键索引，过滤created_at需要全表扫描，添加覆盖索引可直接从索引获取所需数据：

CREATE INDEX idx_users_created_at ON users(created_at, id, name);

（3）精准优化器提示，强制使用索引与嵌套循环连接

若暂时无法调整分区，可通过提示强制优化器使用orders的二级索引和嵌套循环连接：

SELECT /*+ LEADING(u o) USE_NL(o) INDEX(o idx_user) */ u.name, SUM(o.amount)
FROM users u 
JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2023-01-01'
GROUP BY u.id;

（4）更新统计信息，确保优化器决策准确

OceanBase优化器依赖准确的统计信息，手动更新统计信息：

ANALYZE TABLE users, orders;

内容的提问来源于stack exchange，提问作者user30195804