写点什么

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

  • 2018-08-12
  • 本文字数:2726 字

    阅读完需:约 9 分钟

我试图在 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_all
fields terminated by ' ';

结语

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

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

2018-08-12 19:001792
用户头像

发布了 731 篇内容, 共 460.2 次阅读, 收获喜欢 2004 次。

关注

评论 1 条评论

发布
暂无评论
发现更多内容

一个非常强大和友好的nginx基于lua-nginx-module(openresty)

Java 程序员 后端

一口气说出 Redis 16 个常见使用场景,rxjava原理

Java 程序员 后端

一文带你理解Spring Cloud高并发微服务架构核心理念的五脏六腑

Java 程序员 后端

一招教你搞定微信小程序-登录+支付(后台Java,windows内核编程全套视频教程

Java 程序员 后端

三、Redis在SpringBoot中使用案例,java程序员面试笔试真题与解析

Java 程序员 后端

一个专科生和云计算的故事,java注解处理器工作原理及过程

Java 程序员 后端

一个即将从《蚂蚁金服》离职的Java工程师个人经历与总结

Java 程序员 后端

一篇文章带你深入了解MySQL 索引相关,linux视频教程下载

Java 程序员 后端

一篇文章!彻底弄透Java处理GMT-UTC日期时间,java百度天气接口api

Java 程序员 后端

七、Redis持久化的两种方式RDB和AOF理解,mybatis接口实现原理

Java 程序员 后端

【阿里Java岗的魔鬼三面】狠心刷完这6份pdf,Java开发经验谈

Java 程序员 后端

一个项目了解 SpringBoot 集成 MyBatis(1),面试必备知识点

Java 程序员 后端

一夜之间火爆GitHub的好文!!阿里资深架构师整理分享,疯狂膜拜

Java 程序员 后端

一文参透:缓存一致性策略以及雪崩、穿透等问题,java系统架构设计详解

Java 程序员 后端

一线互联网大厂面经分享:阿里三面+头条四面,Java面试心得必备技能储备详解

Java 程序员 后端

一辈子也不容错过学习的微服务网关与用户身份识别,Zuul过滤器

Java 程序员 后端

上线GitHub七天后就标星87,并发知识体系大全

Java 程序员 后端

【金九银十冲刺】Java岗面试题核心每日知识点,kafka原理图

Java 程序员 后端

一见面就脱裤子?这份Spring Cloud微服务笔记应对面试怎么这么骚?

Java 程序员 后端

三年Java开发每天增删改查,终于靠着这份面试题,成功上岸京东

Java 程序员 后端

一元稀疏多项式计算器 【 数据结构课设作业 】 带界面

Java 程序员 后端

一文读懂 spring MVC 请求处理流程,java程序设计教程第三版

Java 程序员 后端

一篇文章带你快速理解JVM运行时数据区 、程序计数器详解 (手画详图

Java 程序员 后端

一篇神文让你“一夜封神“Mycat 中间件 (最详细讲解),linux操作系统实用教程文东戈课后答案

Java 程序员 后端

三年Java开发经验,四面阿里成功斩获offer,分享面经,java面试问项目部署

Java 程序员 后端

一文带你吃透Spring Cloud相关微服务组件及Spring Cloud Config框架

Java 程序员 后端

一文彻底弄懂如何选择抽象类还是接口,java序列化和反序列化面试

Java 程序员 后端

一文看透Java高并发:Synchronized锁的性质、原理及其缺陷

Java 程序员 后端

一线互联网大厂面经分享:阿里三面+头条四面(1),java基础入门第二版电子版

Java 程序员 后端

三面美团Java岗,HR现场直接发offer,他是横着走出来的

Java 程序员 后端

三面阿里被灵魂追问,出门就被Java面试官逼哭!,java虚拟机的运行原理

Java 程序员 后端

我是如何用2个Unix命令给SQL提速的_语言 & 开发_spinellis_InfoQ精选文章