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}），并在这些表上创建必要的索引，从而避免在查询过程中动态创建临时表和索引。这种方式能够大幅度减少建表和创建索引的时间，显著提高数据处理的速度。