本文主要介绍 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 资源管理的异同如下:
CPU 管理 | IO 管理 | Memory 管理 | 按 session 指定 | 按用户指定 | 按语句指定 | |
---|---|---|---|---|---|---|
MySQL 8.0 | ✅ | ❌ | ❌ | ✅ | ❌ | ✅ 通过 hint 指定 |
OceanBase | ✅ | ✅ | ❌ | ❌ | ✅ | ✅ 通过列访问规则指定 |
写在最后
除了本文介绍到的特性外,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/
**粗体** _斜体_ [链接](http://example.com) `代码` - 列表 > 引用
。你还可以使用@
来通知其他用户。