70+专家分享实战经验,2024年度AI最佳实践都在AICon北京 了解详情
写点什么

我是如何用 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:001597
用户头像

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

关注

评论 1 条评论

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

容器化 | 使用 Alpine 构建 Redis 镜像

RadonDB

redis 镜像 RadonDB 数据库·

【计算讲谈社】第十讲|当云计算遇上碳中和

大咖说

云计算 碳中和

2分钟了解什么是实时渲染

3DCAT实时渲染

云计算 元宇宙 实时渲染 实时云渲染 云VR

芒果TV创新研究院联合腾讯云发布“虚拟人直播互动平台”,支持千人沉浸式体验

科技热闻

如何把thinkphp5的项目迁移到阿里云函数计算来应对流量洪峰?

Serverless Devs

阿里云 k8s 微服务框架

实时云渲染有哪些特点,主要优势有哪些

3DCAT实时渲染

云计算 元宇宙 实时渲染 实时云渲染 云VR

HR拥抱人工智能 沃丰科技AI助力星巴克、泰康保险打造智慧HR中心

sofiya

多人沉浸式音乐互动,3DCAT实时云渲染新业务场景来袭

3DCAT实时渲染

云计算 元宇宙 实时渲染云

兆骑科创创新创业服务平台——创新创业的联通之桥

兆骑科创凤阁

NFT链游系统开发链游Dapp前景

薇電13242772558

dapp NFT

Node.js | 从前端到全栈的必经之路

海底烧烤店ai

node.js 前端 全栈 8月月更

敏捷Scrum在中小型企业的落地实施方案

爱吃小舅的鱼

自动化运维体系必不可少的系统

穿过生命散发芬芳

自动化运维 8月月更

新书上市 | 关于推荐系统,这本书包含了你想知道的一切!

图灵教育

什么是实时渲染,实时渲染是如何工作的

3DCAT实时渲染

云计算 元宇宙 实时渲染 实时云渲染 云VR

华为云“828 B2B企业节”,积木易搭云速3D云展与您不见不散

sofiya

携手共建云原生生态 阿里云云原生加速器第二次集结圆满结营

阿里巴巴云原生

阿里云 云原生加速器

数据编排的音乐解法

Alluxio

科普 Alluxio 数据编排 8月月更

2022亚洲视博会圆满落幕,3DCAT荣获“优秀沉浸式视觉解决方案”奖

3DCAT实时渲染

实时云渲染如何助力虚拟展厅

3DCAT实时渲染

云计算 元宇宙 实时渲染 实时云渲染 云VR

“外卖式”售后服务体验来袭 沃丰科技ServiceGo让售后服务更智能

sofiya

信息化赋能,移动办公系统WorkPlus助推智慧检务工作安全高效发展

WorkPlus

云原生赋能智能网联汽车消息处理基础框架构建|车联网系列专题08

EMQ映云科技

车联网 物联网 IoT emq 8月月更

2022秋招面试题,至今已有672名学长靠这套Java八股文成功入职大厂

退休的汤姆

Java 程序员 面经 Java工程师 秋招

【温氏集团】流程驱动的运维自动化在温氏集团的实践

嘉为蓝鲸

运维 AIOPS

新书上市 | 关于推荐系统,这本书包含了你想知道的一切!

图灵社区

瑞云科技荣获全国电子信息行业专精特新“最具创新价值 TOP20”!

3DCAT实时渲染

Salesforce解散中国团队,国产SaaS软件如何完美替代

sofiya

【8.19-8.26】写作社区精彩技术博文回顾

InfoQ写作社区官方

优质创作周报

室外LED显示屏要如何进行合理的散热呢?

Dylan

LED显示屏 户外LED显示屏 led显示屏厂家

关于游戏中的实时渲染

3DCAT实时渲染

云计算 元宇宙 实时渲染 实时云渲染 云VR

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