MariaDB简介

MariaDB是由MySQL创始人基于MySQL一个fork开发创建的,两者在语法以及函数的使用方面基本上高度兼容,但两款RDBMS在功能性和性能上有一些细微的区别,具体区别可以自行搜索了解一下。除此之外两款产品的开源许可证并不相同,MySQL使用的是GPL,许多进阶功能和插件都需要使用企业版,而MariaDB使用的是GPLv2,其相比与MySQL在开源性和商业化友好性方面更加开放和自由。

问题场景

场景一:

1
SELECT 5 + NULL; -- 输出结果:NULL

在实际的业务场景中,上面的数字和NULL都会被业务字段所代替,如果你此时需要对某个字段进行求和,而其中的某个字段恰好为NULL,这就会导致最终结果为NULL,如果不清楚这个特性的话,后续在检查数据时就会耗费大量的时间。类似这种特性具体还有如下所示:
1、算数运算相关

1
2
SELECT NULL * 10;  -- 输出: NULL
SELECT NULL / 3; -- 输出: NULL

2、字符串处理函数

  • CONCAT
1
SELECT CONCAT('a', NULL);  -- 输出: NULL
  • REPLACE
1
SELECT REPLACE('abc', NULL, 'd'); -- 输出: NULL
  • INSERT
1
SELECT INSERT('hello', 1, 2, NULL); -- 输出: NULL

3、日期和时间相关函数

  • ADDDATE / DATE_ADD 和 SUBDATE / DATE_SUB
1
2
SELECT ADDDATE(NULL, INTERVAL 1 DAY); -- 输出: NULL
SELECT SUBDATE('2024-01-01', NULL); -- 输出: NULL

类似特性的还有很多,这里我就不一一举例了。

场景二:

1
select product_id, quantity, sum(price) from sales GROUP BY product_id;

在目前高版本的MySQL处理分组查询时,GROUP BY子句通常用来将结果按照某个字段进行分组,并且配合聚合函数来处理每个分组中的其他字段。通常情况下,SELECT中的字段如果未出现在GROUP BY中,就必须用聚合函数(例如 SUM(), AVG(),COUNT())来处理。高版本MySQL中运行以上SQL会报以下错误:

1
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'sales.quantity' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

而在MariaDB则支持这种语法,当查询中出现不在GROUP BY中的字段时,MariaDB会默认返回该字段在分组中的第一条数据,而不是抛出错误。

场景三:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
DROP TEMPORARY TABLE IF EXISTS a_${uuid};
CREATE TEMPORARY TABLE a_${uuid} AS
select t1.x, t2.Y, t3.z
from a t1
left join b t2 on t1.x = t2.x
left join c t3 on t1.x = t3.x
where t2.ETL_END_DATE = '9999-12-31' and t2.ETL_START_DATE between #{firstDayOfMonth} and #{lastDayOfMonth};

CREATE INDEX idx_y on a_${uuid}(y ASC);

DROP TEMPORARY TABLE IF EXISTS b_${uuid};
CREATE TEMPORARY TABLE b_${uuid} AS
select t1.x, t2.y
from a_${uuid} t1
inner join (SELECT DISTINCT y FROM b ) t2 on t1.x = t2.y;

CREATE INDEX idx_x on b_${uuid}(x ASC);

insert into c(xxxx)
SELECT xxxx
from a_${uuid} t
left join b_${uuid} t1 on t.x = t1.y;

DROP TEMPORARY TABLE IF EXISTS a_${uuid};
DROP TEMPORARY TABLE IF EXISTS b_${uuid};

在一些较为复杂的数据处理场景中,尤其是当数据量非常大时,执行SQL查询时往往会遇到性能瓶颈。为了优化SQL的执行速度和避免查询过慢,我们通常会采取以下措施:

  • 使用临时表存储中间结果,以便进行后续操作
  • 在临时表上创建索引,以加速数据的关联查询。
  • 使用合适的连接方式,避免不必要的全表扫描。

在本示例中,我创建了两个临时表 a_${uuid}b_${uuid},并为它们创建了索引来加速查询,但遇到的一个问题是,当数据量非常大时,索引的创建反而可能导致性能下降。
img.png
数据量达到107万条时,直接在查询中创建临时表并添加索引的过程会非常耗时(如约14秒)。为了提高效率,当前的优化方案是通过直接在数据库中创建中间表(例如 a_${uuid}b_${uuid}),并在这些表上创建必要的索引,从而避免在查询过程中动态创建临时表和索引。这种方式能够大幅度减少建表和创建索引的时间,显著提高数据处理的速度。