内容简介:我试图在 MariaDB(MySQL)上运行一个简单的连接查询,但性能简直糟糕透了。下面将介绍我是如何通过两个简单的 Unix 命令,将查询时间从 380 小时降到 12 小时以下的。下面就是这个查询,它是 GHTorrent 分析的一部分,我使用了关系在线分析处理框架 simple-rolap 来实现这个分析。两个连接字段都有索引。不过,MariaDB 是通过对 project_commits 进行全表扫描和对 commits 进行索引查找来实现连接的。这可以从 EXPLAIN 的输出看出来。
我试图在 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 命令就可以解决这个问题。
以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持 码农网
猜你喜欢:- 一两个节点、一两条命令,轻松让 TiDB 分析场景无痛提速十倍
- 主题模型工具包 Gensim 3.4.0 发布,提速再提速
- 亚洲数字身份识别技术发展提速
- 优化总结:有哪些 APP 启动提速方法?
- 部署Envoy代理来为Monzo提速
- 互联网大提速的悬念:一路向前的WebAssembly
本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们。
程序员的职业素养
Robert C.Martin / 章显洲、余晟 / 人民邮电出版社 / 2012-9-1 / 49.00元
本书是编程大师Bob 大叔40 余年编程生涯的心得体会, 讲解成为真正专业的程序员需要什么样的态度、原则,需要采取什么样的行动。作者以自己以及身边的同事走过的弯路、犯过的错误为例,意在为后来人引路,助其职业生涯迈上更高台阶。 本书适合所有程序员,也可供所有想成为具备职业素养的职场人士参考。一起来看看 《程序员的职业素养》 这本书的介绍吧!