|
|
51CTO旗下网站
|
|
挪动端

我是怎样用2个Unix下令给SQL提速的

我试图在 MariaDB(MySQL)上运转一个复杂的衔接盘问,但功能几乎蹩脚透了。上面将引见我是怎样经过两个复杂的 Unix 下令,将盘问工夫从 380 小时降到 12 小时以下的。

作者:薛命灯泉源:代码湾|2018-08-10 14:57

我试图在 MariaDB(MySQL)上运转一个复杂的衔接盘问,但功能几乎蹩脚透了。上面将引见我是怎样经过两个复杂的 Unix 下令,将盘问工夫从 380 小时降到 12 小时以下的。

上面便是这个盘问,它是 GHTorrent 剖析的一局部,我运用了干系在线剖析处置框架 simple-rolap 来完成这个剖析。

  1. select distinct  
  2. project_commits.project_id,  
  3. date_format(created_at, ‘%x%v1’) as week_commit 
  4.  from project_commits  
  5. left join commits  
  6. on project_commits.commit_id = commits.id; 

两个衔接字段都有索引。不外,MariaDB 是经过对 project_commits 停止全表扫描和对 commits 停止索引查找来完成衔接的。这可以从 EXPLAIN 的输入看出来。

图0:我是怎样用2个Unix下令给SQL提速的

这两个表中的记载比拟多:project_commits 有 50 亿行记载,commits 有 8.47 亿行记载。效劳器的内存比拟小,只要 16GB。以是很能够是由于内寄存不下那么大的索引,需求读取磁盘,因而严峻影响到了功能。从 pmonitor 对暂时表的剖析后果来看,这个盘问曾经运转半天了,还需求 373 个小时才干运转完。

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

  1. select cast(id as charas cid,  
  2. date_format(created_at, ‘%x%v1’) as week_commit  
  3. from commits  
  4. order by cid; 

然后将以下 SQL 盘问的输入保管到 project_commits.txt 文件中:

  1. select cast(commit_id as charas cid, project_id  
  2. from project_commits  
  3. order by cid; 

如许就天生了以下两个文件。

  1. -rw-r–r– 1 dds dds 15G Aug 4 21:09 commits_week.txt  
  2. -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 下令。

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

颠末一个小时的处置,我失掉了想要的后果。

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

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

最初,我将文本文件导回数据库。

  1. 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. 通博8888官网 vs. Unix:有什么差别?
  2. Sosreport:搜集Unix零碎日记和诊断信息的东西
  3. 【引荐】一个收费在线测试100多个通博8888官网和Unix零碎的网站
  4. 通博8888官网根底知识之通博8888官网和Unix有什么差别
  5. 进步UNIX/通博8888官网任务服从的十个小贴士
【责任编辑:未丽燕 TEL:(010)68476606】

点赞 0
分享:
各人都在看
猜你喜好

读 书 +更多

盘算机与网络根底知识——考点剖析及模仿训练

本书是依据天下盘算机技能与软件专业技能资历(程度)测验的“盘算机网络办理员测验纲要”所要求的测验范畴而编写的领导用书。全书共分10章...

订阅51CTO邮刊

点击这里检查样刊

订阅51CTO邮刊