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

2022-08-16 21:46:02 浏览数 (1)

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

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

代码语言：javascript复制
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;

两个连接字段都有索引。不过，MariaDB是通过对project_commits进行全表扫描和对commits进行索引查找来实现连接的。这可以从EXPLAIN的输出看出来。

这两个表中的记录比较多：project_commits有50亿行记录，commits有8.47亿行记录。服务器的内存比较小，只有16GB。所以很可能是因为内存放不下那么大的索引，需要读取磁盘，因此严重影响到了性能。从pmonitor对临时表的分析结果来看，这个查询已经运行半天了，还需要373个小时才能运行完。

代码语言：javascript复制
/home/mysql/ghtorrent/project_commits#P#p0.MYD 6.68% ETA 373:38:11

在我看来，这个太过分了，因为排序合并连接（sort-merge join）所需的I/O时间应该要比预计的执行时间要低一个数量级。我在dba.stackexchange.com上寻求帮助，有人给出了一些建议让我尝试，但我没有信心它们能够解决我的问题。我尝试了第一个建议，结果并不乐观。尝试每个建议都需要至少半天的时间，后来，我决定采用一种我认为可以有效解决这个问题的办法。

我将这两个表导出到文件中，使用Unix的join命令将它们连接在一起，将结果传给uniq，把重复的行移除掉，然后将结果导回到数据库。导入过程（包括重建索引）从20:41开始，到第二天的9:53结束。以下是具体操作步骤。

1. 将数据库表导出为文本文件

我先导出连接两个表需要用到的字段，并按照连接字段进行排序。为了确保排序顺序与Unix工具的排序顺序兼容，我将字段转换为字符类型。

我将以下SQL查询的输出保存到文件commits_week.txt中。

代码语言：javascript复制
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文件中：

代码语言：javascript复制
select cast(commit_id as char) as cid, project_id
  from project_commits
  order by cid;

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

代码语言：javascript复制
-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命令。

代码语言：javascript复制
join commits_week.txt project_commits.txt | uniq >joined_commits.txt

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

代码语言：javascript复制
-rw-r--r-- 1 dds dds 133G Aug  5 01:40 joined_commits.txt

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

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

代码语言：javascript复制
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命令就可以解决这个问题。

查看英文原文：https://www.spinellis.gr/blog/20180805/

unix 数据库 sql 云数据库SQLServer

0 人点赞