---
title: OceanBase 数据库中 group by 聚合查询结果保序功能说明-OceanBase数据库使用指南
description: 了解OceanBase数据库在实际应用中关于OceanBase 数据库中 group by 聚合查询结果保序功能说明相关的常见问题和使用技巧，帮助您快速解决OceanBase 数据库中 group by 聚合查询结果保序功能说明的难题。
---
切换语言

- 中文站 - 简体中文
- International - English
- 日本站 - 日本語

划线反馈

# OceanBase 数据库中 group by 聚合查询结果保序功能说明

更新时间：2026-05-26 09:46

适用版本： V4.2.x、V4.3.x 内容类型：TechNote  

原生 MySQL 5.7 版本中仅支持 `Merge Group By` 算子，所以 `select` 查询中 `group by` 聚合查询结果表现为有序的（即会自动按 `group by` 列有序输出）。不少客户业务依赖 MySQL 的这一保序行为，而 OceanBase 数据库优化器除了支持 `Merge Group By`，还支持 `Hash Group By`，并且会自动为分区表开启并行执行来加速运算，因此对 select 查询中 `group by` 聚合查询结果不保证有序。在 OceanBase 数据库 V4.2.5 BP4（oceanbase-4.2.5.4-104000082025052817）、V4.3.5 BP2（oceanbase-4.3.5.2-102000162025051417） 版本中新增了一个租户级别的隐藏配置项 `_preserve_order_for_groupby`，开启该配置后，对于所有包含 `group by` 但不含 `order by` 的 SQL 查询语句，优化器在改写预处理时会自动将 `order by` 表达式添加到 `group by` 中，从而达到跟原生 MySQL 同样的效果。

## 详细说明

### 原生 MySQL 对 `group by` 的结果会自动实现保序输出

**测试如下：**

```shell
root@xxxxx:/# mysql -uroot -pxxx -A -c test
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14264
Server version: 5.7.32-log MySQL Community Server (GPL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table t1 (id int, name varchar(10)) partition by hash(id) partitions 3;
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t1 values (1,'a');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t1 values (2,'b');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values (3,'c');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values (4,'d');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values (5,'e');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values (6,'f');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values (5,'e');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t1 values (1,'a');
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1;
+------+------+
| id   | name |
+------+------+
|    3 | c    |
|    6 | f    |
|    6 | f    |
|    3 | c    |
|    1 | a    |
|    4 | d    |
|    4 | d    |
|    1 | a    |
|    2 | b    |
|    5 | e    |
|    5 | e    |
|    2 | b    |
+------+------+
12 rows in set (0.00 sec)

mysql> select id,count(*) from t1 group by id;
+------+----------+
| id   | count(*) |
+------+----------+
|    1 |        2 |
|    2 |        2 |
|    3 |        2 |
|    4 |        2 |
|    5 |        2 |
|    6 |        2 |
+------+----------+
6 rows in set (0.01 sec)

mysql> select id,count(*) from t1 group by id;
+------+----------+
| id   | count(*) |
+------+----------+
|    1 |        2 |
|    2 |        2 |
|    3 |        2 |
|    4 |        2 |
|    5 |        2 |
|    6 |        2 |
+------+----------+
6 rows in set (0.00 sec)

mysql> select id, count(*) from t1 group by id;
+------+----------+
| id   | count(*) |
+------+----------+
|    1 |        2 |
|    2 |        2 |
|    3 |        2 |
|    4 |        2 |
|    5 |        2 |
|    6 |        2 |
+------+----------+
6 rows in set (0.00 sec)

```

### OceanBase 数据库 MySQL 模式租户中对 `group by` 的结果不保证有序输出

#### 说明

OceanBase 数据库 MySQL 模式租户中，当使用 `group by` 进行聚合查询时，查询结果有时可以按 `group by` 列有序输出，但不做保证，以下情况输出依然可能会乱序：

1. 并行查询/分布式查询，即使指定 `parallel(1)`，如果表为分区表，输出结果仍可能是乱序。
 2. `Merge Group By` 算子下方使用 `PARTITION SORT` 排序算子。
 3. `group by` 算子使用了 `HASH GROUP BY` 算法。

**测试如下：**

```shell
$ mysql -hxx.xxx.80.111 -P2881 -uroot@mysqlt -pxxx -A -c test
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 3221533299
Server version: 5.7.25 OceanBase 4.2.5.4 (r104020022025062018-b1433bac8a0d2b186e76956d5f6a8c4b9ae1632c) (Built Jun 20 2025 18:48:58)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

MySQL [test]> select @@version_comment from dual;
+---------------------------------------------------------------------------------------------------------------+
| @@version_comment                                                                                             |
+---------------------------------------------------------------------------------------------------------------+
| OceanBase 4.2.5.4 (r104020022025062018-b1433bac8a0d2b186e76956d5f6a8c4b9ae1632c) (Built Jun 20 2025 18:48:58) |
+---------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

MySQL [test]> create table t1 (id int, name varchar(10)) partition by hash(id) partitions 3;
Query OK, 0 rows affected (0.11 sec)

MySQL [test]> insert into t1 values (1,'a');
Query OK, 1 row affected (0.02 sec)

MySQL [test]> insert into t1 values (2,'b');
Query OK, 1 row affected (0.00 sec)

MySQL [test]> insert into t1 values (3,'c');
Query OK, 1 row affected (0.01 sec)

MySQL [test]> insert into t1 values (4,'d');
Query OK, 1 row affected (0.00 sec)

MySQL [test]> insert into t1 values (5,'e');
Query OK, 1 row affected (0.00 sec)

MySQL [test]> insert into t1 values (6,'f');
Query OK, 1 row affected (0.00 sec)

MySQL [test]> insert into t1 values (4,'d');
Query OK, 1 row affected (0.01 sec)

MySQL [test]> insert into t1 values (3,'c');
Query OK, 1 row affected (0.00 sec)

MySQL [test]> insert into t1 values (1,'a');
Query OK, 1 row affected (0.00 sec)

MySQL [test]> commit;
Query OK, 0 rows affected (0.00 sec)

MySQL [test]> select * from t1;
+------+------+
| id   | name |
+------+------+
|    3 | c    |
|    6 | f    |
|    6 | f    |
|    3 | c    |
|    1 | a    |
|    4 | d    |
|    4 | d    |
|    1 | a    |
|    2 | b    |
|    5 | e    |
|    5 | e    |
|    2 | b    |
+------+------+
12 rows in set (0.01 sec)

MySQL [test]> select id,count(*) from t1 group by id;
+------+----------+
| id   | count(*) |
+------+----------+
|    3 |        2 |
|    6 |        2 |
|    1 |        2 |
|    4 |        2 |
|    2 |        2 |
|    5 |        2 |
+------+----------+
6 rows in set (0.00 sec)

MySQL [test]> select id, count(*) from t1 group by id;
+------+----------+
| id   | count(*) |
+------+----------+
|    3 |        2 |
|    6 |        2 |
|    1 |        2 |
|    4 |        2 |
|    2 |        2 |
|    5 |        2 |
+------+----------+
6 rows in set (0.01 sec)

MySQL [test]> explain select id,count(*) from t1 group by id;
+----------------------------------------------------------------------------------+
| Query Plan                                                                       |
+----------------------------------------------------------------------------------+
| =============================================================                    |
| |ID|OPERATOR                 |NAME    |EST.ROWS|EST.TIME(us)|                    |
| -------------------------------------------------------------                    |
| |0 |PX COORDINATOR           |        |12      |27          |                    |
| |1 |└─EXCHANGE OUT DISTR     |:EX10000|12      |24          |                    |
| |2 |  └─PX PARTITION ITERATOR|        |12      |15          |                    |
| |3 |    └─HASH GROUP BY      |        |12      |15          |                    |
| |4 |      └─TABLE FULL SCAN  |t1      |12      |12          |                    |
| =============================================================                    |
| Outputs & filters:                                                               |
| -------------------------------------                                            |
|   0 - output([INTERNAL_FUNCTION(t1.id, T_FUN_COUNT(*))]), filter(nil), rowset=16 |
|   1 - output([INTERNAL_FUNCTION(t1.id, T_FUN_COUNT(*))]), filter(nil), rowset=16 |
|       dop=1                                                                      |
|   2 - output([t1.id], [T_FUN_COUNT(*)]), filter(nil), rowset=16                  |
|       partition wise, force partition granule                                    |
|   3 - output([t1.id], [T_FUN_COUNT(*)]), filter(nil), rowset=16                  |
|       group([t1.id]), agg_func([T_FUN_COUNT(*)])                                 |
|   4 - output([t1.id]), filter(nil), rowset=16                                    |
|       access([t1.id]), partitions(p[0-2])                                        |
|       is_index_back=false, is_global_index=false,                                |
|       range_key([t1.__pk_increment]), range(MIN ; MAX)always true                |
+----------------------------------------------------------------------------------+
22 rows in set (0.00 sec)

```

### 开启 `_preserve_order_for_groupby` 后，OceanBase 数据库 MySQL 模式租户中对 `group by` 的结果保证有序输出

#### 说明

OceanBase 数据库 V4.2.5 BP4（oceanbase-4.2.5.4-104000082025052817）、V4.3.5 BP2（oceanbase-4.3.5.2-102000162025051417）新增了一个 `group by` 结果保序功能开关：

- `_preserve_order_for_groupby`，可以保证 `group by` 的结果表现为总是按 `group by` 表达式排序。
 - 该隐藏配置项默认为 `False`，当设置为 `True` 时，如果包含 `group by` 的查询不包含 `order by`，那么该查询会按照 `group by` 表达式升序的方式排序。

**例如：**

```shell
select c1, c2, count(*) from t1 group by c1, c2;

-- 改写为 =>

select c1, c2, count(*) from t1 group by c1, c2 order by c1, c2;

```

**使用示例：**

```shell
-- 普通 MySQL 业务租户下
alter system set _preserve_order_for_groupby = true;

-- SYS 系统租户下
alter system set _preserve_order_for_groupby=true tenant=xxx;
alter system set _preserve_order_for_groupby=false tenant=xxx;

```

**测试如下：**

```shell
$ mysql -hxx.xxx.80.111 -P2881 -uroot@mysqlt -pxxx -A -c test
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 3221568928
Server version: 5.7.25 OceanBase 4.2.5.4 (r104020022025062018-b1433bac8a0d2b186e76956d5f6a8c4b9ae1632c) (Built Jun 20 2025 18:48:58)

MySQL [test]> alter system set _preserve_order_for_groupby=true;
Query OK, 0 rows affected (0.00 sec)

MySQL [test]> alter system flush plan cache;
Query OK, 0 rows affected (0.00 sec)

MySQL [test]> select id,count(*) from t1 group by id;
+------+----------+
| id   | count(*) |
+------+----------+
|    1 |        2 |
|    2 |        2 |
|    3 |        2 |
|    4 |        2 |
|    5 |        2 |
|    6 |        2 |
+------+----------+
6 rows in set (0.00 sec)

MySQL [test]> select id,count(*) from t1 group by id;
+------+----------+
| id   | count(*) |
+------+----------+
|    1 |        2 |
|    2 |        2 |
|    3 |        2 |
|    4 |        2 |
|    5 |        2 |
|    6 |        2 |
+------+----------+
6 rows in set (0.01 sec)

MySQL [test]> select id, count(*) from t1 group by id;
+------+----------+
| id   | count(*) |
+------+----------+
|    1 |        2 |
|    2 |        2 |
|    3 |        2 |
|    4 |        2 |
|    5 |        2 |
|    6 |        2 |
+------+----------+
6 rows in set (0.01 sec)

```

### 备注

对于小于 OceanBase 数据库 V4.2.5 BP4（oceanbase-4.2.5.4-104000082025052817）、V4.3.5 BP2（oceanbase-4.3.5.2-102000162025051417）的版本，如果查询数据为非分区表，使用 Hint `/*+NO_USE_HASH_AGGREGATION(NO_PARTITION_SORT) PARALLEL(1)*/` 也可以得到按 `group by` 列有序的输出结果。

## 适用版本

- **OceanBase 数据库 V4.2.x 系列：** V4.2.5 BP4（oceanbase-4.2.5.4-104000082025052817）及更高的版本（仅 MySQL模式租户）。
 - **OceanBase 数据库 V4.3.x 系列：** V4.3.5 BP2（oceanbase-4.3.5.2-102000162025051417）及更高的版本（仅 MySQL 模式租户）。

上一篇

[SQL 使用 @@server_id 变量查询异常问题分析](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000003791273)

下一篇

[行存，列存与优化器选择 DOP 相关 FAQs](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000002896426) ![有帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*y6ocSqN8cqsAAAAAAAAAAAAAARQnAQ)![无帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*BG9IQJyLHF8AAAAAAAAAAAAAARQnAQ)![反馈](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*eTWdQKCRKHwAAAAAAAAAAAAAARQnAQ)[AI](https://www.oceanbase.com/obi) 咨询热线