我是如何用2个Unix命令给MariaDB SQL提速的

我试图在 MariaDB（MySQL）上运行一个简单的连接查询，但性能简直糟糕透了。下面将介绍我是如何通过两个简单的 Unix 命令，将查询时间从 380 小时降到 12 小时以下的,需要的朋友可以参考下

译者 | 薛命灯

我试图在 MariaDB（MySQL）上运行一个简单的连接查询，但性能简直糟糕透了。下面将介绍我是如何通过两个简单的 Unix 命令，将查询时间从 380 小时降到 12 小时以下的。

下面就是这个查询，它是 GHTorrent 分析的一部分，我使用了关系在线分析处理框架 simple-rolap 来实现这个分析。

select distinct
project_commits.project_id,
date_format(created_at, ‘%x%v1') as week_commit
from project_commits
left join commits
on project_commits.commit_id = commits.id;

select cast(id as char) as cid,
date_format(created_at, ‘%x%v1') as week_commit
from commits
order by cid;

然后将以下 SQL 查询的输出保存到 project_commits.txt 文件中：

select cast(commit_id as char) as cid, project_id
from project_commits
order by cid;

这样就生成了以下两个文件。

-rw-r–r– 1 dds dds 15G Aug 4 21:09 commits_week.txt

-rw-r–r– 1 dds dds 93G Aug 5 00:36 project_commits.txt

为了避免内存不足，我使用 –quick 选项来运行 mysql 客户端，否则客户端会在输出结果之前尝试收集所有的记录。

2. 使用 Unix 命令行工具处理文件

接下来，我使用 Unix 的 join 命令来连接这两个文本文件。这个命令线性扫描两个文件，并将第一个字段相同的记录组合在一起。由于文件中的记录已经排好序，因此整个过程完成得很快，几乎就是 I/O 的速度。我还将连接的结果传给 uniq，用以消除重复记录，这就解决了原始查询中的 distinct 问题。同样，在已经排好序的输出结果上，可以通过简单的线性扫描完成去重。

这是我运行的 Unix 命令。

join commits_week.txt project_commits.txt | uniq >joined_commits.txt

经过一个小时的处理，我得到了想要的结果。

-rw-r–r– 1 dds dds 133G Aug 5 01:40 joined_commits.txt

3. 将文本文件导回数据库

最后，我将文本文件导回数据库。

create table half_life.week_commits_all (
project_id INT(11) not null,
week_commit CHAR(7)) ENGINE=MyISAM;
load data local infile ‘joined_commits.txt'
into table half_life.week_commits_all
fields terminated by ‘ ‘;

结语

理想情况下，MariaDB 应该支持排序合并连接，并且在预测到备用策略的运行时间过长时，优化器应该使用排序合并连接。但在此之前，使用 70 年代设计的 Unix 命令就可以解决这个问题。