OceanBase OLAP collation utf8mb4_bin 优先

本文主要是介绍OceanBase OLAP collation utf8mb4_bin 优先，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

在大数据系统中，如无特别需要，建议 collation 指定为 utf8mb4_bin。

utf8mb4_bin是一种二进制的排序规则，比较字符串时直接比较字符串的二进制值，不需要进行复杂的字符比较和排序运算，这样可以有效减少CPU的使用，提高查询效率，特别是在涉及到大量数据操作时，性能优势更为明显。

在 AP 场景使用 OceanBase 时你可以在租户级别设置这两个值作为默认配置，以获得最佳的默认性能：

set global collation_connection = utf8mb4_bin;
set global collation_server = utf8mb4_bin;

utf8mb4 是编码格式，设置 collation 为 bin 不会影响内存的存储格式，不会造成字符集不兼容等问题，它仅仅影响排序过程中的排序规则。

使用 bin 排序和不使用 bin 排序的详细对比如下：

OceanBase(admin@test)>show variables like 'collation_server';
+------------------+--------------------+
| Variable_name    | Value              |
+------------------+--------------------+
| collation_server | utf8mb4_general_ci |
+------------------+--------------------+
1 row in set (0.007 sec)OceanBase(admin@test)>create table t1 (c1 bigint primary key, c2 varchar(10));
Query OK, 0 rows affected (0.102 sec)OceanBase(admin@test)>create table t2 (c1 bigint primary key, c2 varchar(10)) charset=utf8mb4 collate=utf8mb4_bin;
Query OK, 0 rows affected (0.089 sec)OceanBase(admin@test)>show create table t1\G
*************************** 1. row ***************************Table: t1
Create Table: CREATE TABLE `t1` (`c1` bigint(20) NOT NULL,`c2` varchar(10) DEFAULT NULL,PRIMARY KEY (`c1`)
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 2 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
1 row in set (0.011 sec)OceanBase(admin@test)>show create table t2\G
*************************** 1. row ***************************Table: t2
Create Table: CREATE TABLE `t2` (`c1` bigint(20) NOT NULL,`c2` varchar(10) COLLATE utf8mb4_bin DEFAULT NULL,PRIMARY KEY (`c1`)
) DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 2 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
1 row in set (0.011 sec)OceanBase(admin@test)>insert /*+ append enable_parallel_dml parallel(4) */ into t1 select random(), randstr(1, 6) from table(generator(1000000));
Query OK, 1000000 rows affected (8.629 sec)
Records: 1000000  Duplicates: 0  Warnings: 0OceanBase(admin@test)>insert /*+ append enable_parallel_dml parallel(4) */ into t2 select * from t1;
Query OK, 1000000 rows affected (7.115 sec)
Records: 1000000  Duplicates: 0  Warnings: 0OceanBase(admin@test)>select * from t1 order by c2 limit 999998, 1;
+----------------------+------+
| c1                   | c2   |
+----------------------+------+
| -8614823888367694260 | 5    |
+----------------------+------+
1 row in set (1.050 sec)OceanBase(admin@test)>select * from t2 order by c2 limit 999998, 1;
+----------------------+------+
| c1                   | c2   |
+----------------------+------+
| -8614823888367694260 | 5    |
+----------------------+------+
1 row in set (0.876 sec)OceanBase(admin@test)>select * from t1 order by c2 limit 999998, 1;
+----------------------+------+
| c1                   | c2   |
+----------------------+------+
| -8614823888367694260 | 5    |
+----------------------+------+
1 row in set (1.053 sec)OceanBase(admin@test)>select * from t2 order by c2 limit 999998, 1;
+----------------------+------+
| c1                   | c2   |
+----------------------+------+
| -8614823888367694260 | 5    |
+----------------------+------+
1 row in set (0.844 sec)

这篇关于OceanBase OLAP collation utf8mb4_bin 优先的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---