OceanBase 4.1解读：我们支持MySQL 8.0哪些新增功能特性？

MySQL 在业内是最受欢迎的关系数据库之一，不少用户将 MySQL 作为刚开始使用数据库的首选。OceanBase 的一大重要特性即是与 MySQL 完全兼容，用户无需修改代码即可完成数据库的升级迁移，也大幅降低了开发者的学习成本。作为完全自主研发的数据库，OceanBase 从 1.0 版本开始便在 MySQL 兼容能力研发上投入了大量工作，并实现了 SQL 语法、数据类型、系统函数等全面的 MySQL 兼容能力。

随着 MySQL 的发展和用户使用需求的变化，OceanBase 的 MySQL 兼容版本也从 5.6 发展到 5.7 再到 8.0。始终为用户带来简单友好的开发体验。尽管目前 5.7 版本依然是许多 MySQL 用户业务的主力版本，但随着官方对 5.7 版本的支持终止，也将会有更多 MySQL 用户将业务更换至 MySQL 8.0。

* MySQL 5.7 将于 2023 年 10 月结束官方支持，from:

​​https://www.oracle.com/us/support/library/lifetime-support-technology-069183.pdf​​​

OceanBase 4.1 对 MySQL 的兼容策略是完全兼容 5.7，同时支持 8.0 功能。相较于 MySQL 5.7，8.0 版本在性能、安全性、可用性等方面都有显著提升，同时也新增了许多功能特性。在笔者看来，MySQL 8.0 最重要的新增功能特性有如下几项，本文也将介绍 OceanBase 在 4.1 版本发布之际对这部分功能的支持情况：

• 窗口函数（Window Function）
• 公用表表达式（Common table expression）
• Hash Join
• 索引管理
• 资源管理（Resource management）

窗口函数（Window Function）是 MySQL 8.0 的新增功能，它可以对某个窗口内的数据行执行聚合操作，而不是对整个结果集进行聚合，能更简单直观地进行时间序列分析、累积统计、排名等操作，帮助用户更深入地发现数据价值。与聚合函数不同的是，窗口函数主要通过窗口控制参与计算的行，它不会将多行查询结果合并为一行，而是将结果放回多行当中。而在窗口中的计算，既支持所有的聚合函数 (如：count, sum, min, max, avg, stddev ...)，也支持部分窗口函数，包含：cume_dist, dense_rank, first_value, lag, last_value, lead, nth_value, ntile, percent_rank, rank, row_number。

窗口函数主要应用于数据分析，例如计算数据趋势变化、基于不同指标排序数据、复杂统计指标计算等。举例来说，假设某一场景中我们要按项目对运动员得分进行排名，我们可以创建一个名为 athlete_scores 的表，设定运动类别（sport_type），运动员名字（athlete_name），分数（score），并使用如下函数进行排名操作：

SELECT sport_type, athlete_name, score,
       RANK() OVER (PARTITION BY sport_type ORDER BY score DESC) as `rank`
FROM athlete_scores
ORDER BY sport_type, `rank`

由于窗口函数功能在 OLAP 使用场景广泛，OceanBase 从 1.x 版本开始就提供了窗口函数功能。相比使用子查询或连接操作，窗口函数往往能提供更好的性能。同时，数据库优化器可以有效地处理窗口函数，降低查询计算的成本。值得一提的是，即使用户在 SQL 编写中没有使用窗口函数，在部分场景中，OceanBase 也会将部分 SQL 自动改写为包含窗口函数的语句，以提高性能。

公共表表达式（Common table expression，简称 CTE）是由 SQL1999 标准引入的 SQL 功能，后续在 2000 年初各数据库厂商陆续推出此功能，MySQL 在 8.0 推出了完备的 CTE 实现。CTE 通过 WITH 关键字，允许在 SQL 语句中定义一个/多个临时表（视图），并在查询中应用，通常应用在多个子查询、分层查询以及递归查询中。用户借助 CTE 可以将复杂的查询拆分为多个逻辑部分，使复杂查询的结构更清晰、SQL 代码的逻辑更直观，使得查询更容易理解和维护。

用户还可以通过递归 CTE 实现更复杂的功能。如在 MySQL 中，可以通过如下 SQL 语句生成一个带自增列（id）及一个随机数（rand_val）的 1000 行测试数：

WITH RECURSIVE test_data (id, rand_val) AS (
    SELECT 1, RAND()
    UNION ALL
    SELECT id + 1, RAND()
    FROM test_data
    WHERE id < 1000
)
SELECT *
FROM test_data;

OceanBase 可以自动识别公共子查询，并抽取为公共表表达式。OceanBase 从 2.0 版本开始支持 CTE 功能，即使用户在 SQL 编写中未使用 CTE，OceanBase 也会在改写阶段进行识别并抽取，通过减少子查询执行次数以优化性能。举例来说，在如下 SQL 和计划中，SELECT c1, MIN(c2) c2, MAX(c3) c3 FROM t1 GROUP BY c1 子查询用了两次，便可抽取成为公共子查询。

SELECT *
FROM t2
WHERE EXISTS (
        SELECT *
        FROM (
            SELECT c1, MIN(c2) c2, MAX(c3) c3
            FROM t1
            GROUP BY c1
            ) a
        WHERE t2.c1 = a.c1 AND t2.c2 = a.c2
        )
    AND EXISTS (
        SELECT *
        FROM (
            SELECT c1, MIN(c2) c2, MAX(c3) c3
            FROM t1
            GROUP BY c1
            ) b
        WHERE t2.c3 = b.c1 AND t2.c4 = b.c3
        );

=================================================================
|ID|OPERATOR                 |NAME        |EST.ROWS|EST.TIME(us)|
-----------------------------------------------------------------
|0 |TEMP TABLE TRANSFORMATION|            |1       |5           |   
|1 | TEMP TABLE INSERT       |TEMP1       |1       |3           |   
|2 |  HASH GROUP BY          |            |1       |3           |   
|3 |   TABLE SCAN            |t1          |1       |2           |   
|4 | HASH JOIN               |            |1       |3           |   
|5 |  TEMP TABLE ACCESS      |VIEW2(TEMP1)|1       |1           |   
|6 |  HASH JOIN              |            |1       |3           |   
|7 |   TEMP TABLE ACCESS     |VIEW1(TEMP1)|1       |1           |   
|8 |   TABLE SCAN            |t2          |1       |2           |   
=================================================================

可以看到，OceanBase 在用户未使用 CTE 情况下给出的执行计划，与下方用户自己使用 CTE 编写 SQL 的执行计划一致。在这个示例中，OceanBase 的 TEMP TABLE TRANSFORMATION 算子会将子查询结果存起来（TEMP 1），并在后续多次读取，减少子查询执行次数。

WITH tmp AS (
    SELECT c1, MIN(c2) c2, MAX(c3) c3
    FROM t1
    GROUP BY c1
)
SELECT *
FROM t2
WHERE EXISTS (
        SELECT *
        FROM tmp a
        WHERE t2.c1 = a.c1 AND t2.c2 = a.c2
        )
    AND EXISTS (
        SELECT *
        FROM tmp b
        WHERE t2.c3 = b.c1 AND t2.c4 = b.c3
        );

===========================================================
|ID|OPERATOR                 |NAME  |EST.ROWS|EST.TIME(us)|
-----------------------------------------------------------
|0 |TEMP TABLE TRANSFORMATION|      |1       |5           |   
|1 | TEMP TABLE INSERT       |tmp   |1       |3           |   
|2 |  HASH GROUP BY          |      |1       |3           |   
|3 |   TABLE SCAN            |t1    |1       |2           |   
|4 | HASH JOIN               |      |1       |3           |   
|5 |  TEMP TABLE ACCESS      |b(tmp)|1       |1           |   
|6 |  HASH JOIN              |      |1       |3           |   
|7 |   TEMP TABLE ACCESS     |a(tmp)|1       |1           |   
|8 |   TABLE SCAN            |t2    |1       |2           |   
===========================================================

Hash Join 是数据库中广泛实现的连接（JOIN）算法，用于处理两个或多个表之间的连接操作。在 Hash Join 算法中，首先基于其中一个表构建 Hash 表（一般选择数据量小的表构建），然后对另一张表每一行进行Hash表探测，找到与之匹配的行并生成结果集，在数据量较大时通常有性能优势。

MySQL 在 8.0 版本之前支持的连接算法只有 Nested Loop Join，这种连接算法在 Web 应用和 OLTP 场景中的性能已相对较好，因此未对 Hash Join 有很高的优先级。Nested Loop Join 尤其适用于索引可以有效使用的情况下，其做法是将其中一个表作为外表，另一个表作为内表，对外表中的每一行遍历内部所有行（如有合适索引，可利用索引）找到符合条件的行。然而，当内表数据量较大且没有适合索引时，Nested Loop Join 性能较差。随着用户对大数据量连接需求的增加，MySQL 从 8.0 版本开始支持 Hash Join 算法。

OceanBase 一直以来都支持 Hash Join 算法。与单机数据库不同，OceanBase 面向的应用场景很多都涉及海量数据处理。此外，分布式场景下索引（全局）往往需要跨机访问，而通过建索引优化 Nested Loop Join 较难达到预期效果。而 Hash Join 在分布式、大数据量场景通常性能表现更优，因此我们从研发初期就进行了支持。同时，我们也支持 Nested Loop Join 以及 Merge Join，这两个 Join 算法在能利用索引或利用序的场景有性能优势。OceanBase 的查询优化器会根据代价模型判断，为用户自动选择最适合的算法。

MySQL 8.0 引入了设置索引可见性（visiable/invisiable）的功能，这一功能允许用户在不删除索引的前提下，将索引设置为不可见。在不可见状态下，查询优化器会忽略该索引，不将其用于查询计划，但其索引数据仍将正常维护。这样做的好处是可以在保留索引结构的情况下，测试索引对查询性能的影响。如果之后需要重新启用索引，只需将其设置为可见即可。

例如当我们想删除一个索引时，可将其设置为 invisiable，等待业务运行一段时间确认该索引确实不被用到后再删除它。如果发现某个业务 SQL 依赖于该索引，便可将该索引改回 visiable。由于索引数据仍然被正常维护，设置索引可见性的速度非常快，避免了先前删除索引后发现业务依赖，又重新创建索引的开销。

MySQL 8.0 中设置索引可见性的语法如下：

-- 将索引设置为不可见
ALTER TABLE table_name ALTER INDEX index_name INVISIBLE;

-- 将索引设置为可见
ALTER TABLE table_name ALTER INDEX index_name VISIBLE;

OceanBase 从 1.x 版本开始支持索引可见性设置。通过将索引设置为不可见，用户可以在现有数据库环境中测试调整索引策略对查询性能的影响，而无需删除或禁用索引。同时，索引本身也需要维护和消耗资源，遗留的冗余索引也可能会拖慢性能，通过设置索引可见性，用户可以安全地验证索引对查询性能的影响，从而避免资源浪费。此外，通过设置索引可见性，可以在不重建索引的情况下快速恢复索引，从而避免误删关键索引导造成性能下降的风险。综合来看，索引可见性设置可以帮助用户更灵活地管理索引策略、优化查询性能并降低资源浪费，从而提升数据库管理效率。

此外，MySQL 8.0 还新增了对逆序索引的支持，逆序索引是指索引列可指定降序，这样一些按降序的排序可利用此索引。如下所示，ORDER BY c1 DESC, c2 的查询即可利用此索引。

CREATE TABLE t1 (c1 INT, c2 INT,INDEX i1 (c1 DESC, c2 ASC));

由于过去用户对逆序索引需求较少，OceanBase 目前暂不支持逆序索引功能。对于逆序排序的场景，OceanBase 优化器会利用正序索引（逆序扫描），并支持前缀排以及通过并行(PX)加速排序，我们也即将在后续版本中支持此特性。对单列的逆序排序，OceanBase 可以用逆序扫描索引的方式利用索引。对混合了正逆序的多列排序场景，OceanBase 会尽可能的利用索引，在排序时只对不能利用索引的列进行排序。如索引为 index1 (c1, c2, c3)，排序为 ORDER BY c1 desc, c2 asc, c3 asc，OceanBase 会选择对 index1 进行逆序扫描，然后利用前缀排序仅对 c2, c3 排序。此外，如果建索引后此类排序性能仍不满足要求，可以尝试通过并行执行（PX）加速排序。

MySQL 8.0 开始支持资源组（resource group）功能，此功能可以指定资源组所使用的 CPU 资源以及任务调度优先级，从而对数据库查询的执行进行管理和控制。同时，可以通过 SET RESOURCE GROUP 语法为线程指定资源组，或通过 /*+ RESOURCE_GROUP(xxx) */ hint 为某条语句指定资源组。通过上述方式 ，DBA 可控制不同任务的资源（CPU）使用、灵活地调整资源分配，从而提高资源的整体利用率。

OceanBase 支持更丰富的资源管理策略。从 3.x 版本开始，OceanBase 在 Oracle 模式中支持了 Oracle 的资源管理（DBMS_RESOURCE_MANAGER 包），与 MySQL 的 resource management 主要针对 CPU 资源分配不同，OceanBase 提供了丰富、灵活的资源管理策略，可以设置多种不同的资源计划，根据消耗情况动态调整资源分配，具备更完备的功能和更多的定制可能性。

OceanBase 也在 3.2 和 4.1 版本中，将资源管理能力开放到了 OceanBase MySQL 模式。用户可通过 DBMS_RESOURCE_MANAGER 包中的 CREATE_CONSUMER_GROUP, CREATE_PLAN,  CREATE_PLAN_DIRECTIVE 过程创建资源组指定管理计划（CPU、IO资源），然后通过 SET_CONSUMER_GROUP_MAPPING 接口按用户或数据列访问规则为 SQL 指定资源组。

当前，OceanBase 资源管理与 MySQL 8.0 资源管理的异同如下：

除了本文介绍到的特性外，MySQL 8.0 还带来了如 JSON 类型增强、支持 INTERSECT, EXPECT 等功能特性，也解决了如 Atomic DDL、自增值持久化等长期存在的不足，以及默认字符集由 latin1 变为 utf8mb4, group by 列不再支持 asc/desc 等行为变化。当用户考虑将业务从低版本迁到 MySQL 8.0 时，需要充分评估这些变化的影响。我认为，MySQL 8.0 是更好的 MySQL，而 OceanBase 则不仅仅是 MySQL，我们会不断优化对 MySQL 8.0 的兼容性，同时也欢迎大家体验 OceanBase 4.1，分享你的使用感受。

文章转载自公众号：OceanBase