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

我试图在 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; 

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

这两个表中的记录较量多：project_commits 有 50 亿行记录，commits 有 8.47 亿行记录。处事器的内存较量小，只有 16GB。以是很也许是由于内存放不下那么大的索引，必要读取磁盘，因此严峻影响到了机能。从 pmonitor 对姑且表的说明功效来看，这个查询已经运行半天了，还必要 373 个小时才气运行完。

/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 中。

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_allfields terminated by ‘ ‘; 

结 语

抱负环境下，MariaDB 应该支持排序归并毗连，而且在猜测到备用计策的运行时刻过长时，优化器应该行使排序归并毗连。但在此之前，行使 70 年月计划的 Unix 呼吁就可以办理这个题目。

【编辑保举】

1. Linux vs. Unix：有什么差异？
2. Sosreport：网络Unix体系日记和诊断信息的器材
3. 【保举】一个免费在线测试100多个Linux和Unix体系的网站
4. Linux基本常识之Linux和Unix有什么差异
5. 进步UNIX/Linux事变服从的十个小贴士

（编辑：河北网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!