划线反馈

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` 的结果会自动实现保序输出

测试如下：

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 (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 (4,'d');
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 (2,'b');
Query OK, 1 row affected (0.00 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 列有序输出，但不做保证，以下情况输出依然可能会乱序：

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

测试如下：

$ 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.

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

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 (6,'f');
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 (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 (2,'b');
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.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 表达式升序的方式排序。

例如：

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

-- 改写为 =>

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

使用示例：

-- 普通 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;

测试如下：

$ 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)

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

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

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)

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 变量查询异常问题分析

行存，列存与优化器选择 DOP 相关 FAQs

OceanBase 数据库