关于数据库:OceanBase-41解读我们支持MySQL-80哪些新增功能特性

本文次要介绍 OceanBase 在 4.1 版本公布之际，对 MySQL 8.0 新增性能个性的反对状况。文章作者刘彬，OceanBase 高级技术专家。曾参加 OceanBase RS、存储模块研发，目前是 SQL 执行组负责人。

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 5.7 将于 2023 年 10 月完结官网反对，from: https://www.oracle.com/us/support/library/lifetime-support-te…），也将会有更多 MySQL 用户将业务更换至 MySQL 8.0。

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 主动改写为蕴含窗口函数的语句，以进步性能。

公共表表达式：加强 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 算法：晋升大数据连贯性能

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 官网获取更多信息：https://www.oceanbase.com/