写点什么

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

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

关注

评论 1 条评论

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

视角 | 麻省理工学院提出出温度计校准法,专治AI大模型过度自信

硅纪元

职场<火焰杯>测试开发大赛证书已开放下载!

霍格沃兹测试开发学社

微软Azure遭遇DDoS攻击,全球云服务再次敲响警钟!

网络安全服务

微软 azure 云服务 DDoS DDoS 攻击

什么情况下你能接受 996

王中阳Go

Go 工作 面经

GraphPad Prism科技绘图与数据分析

Rose

GraphPad Prism破解版 科学绘图和统计分析 GraphPad Prism下载

Magnet for mac v2.12.0中文免激活版 支持多种不同方式的窗口布局模式

Rose

Magnet破解版 Magnet Mac下载 苹果电脑分屏软件 macOS窗口管理

IntelliJ IDEA 2019 mac破解版 含IDEA 2019永久密钥 附IDEA 2019中文版设置教程

Rose

IntelliJ IDEA 2019 IntelliJ IDEA 2019密钥 编程软件 IDEA 2019

品牌与分类:解析京东商品详情API中的品牌与分类信息

技术冰糖葫芦

API 安全 API 文档 API 测试 API 优先

PDF解析,还能做得更好

合合技术团队

PDF 大模型 文档解析

Downie 4 v4.7.24最新中文版下载 永久使用 支持M1.M2.M3芯片安装

Rose

Downie 4许可证 Mac视频下载器 Downie 4下载 Downie 4使用教程

BetterTouchTool for mac触摸板增强神器下载 bettertouchtool破解版详细使用教程

Rose

BetterTouchTool破解 BetterTouchTool教程 Mac触控板增强工具

LED异型显示屏:创新设计与市场需求的完美融合

Dylan

设计 应用 LED LED显示屏 市场

科学文献管理软件EndNote 21功能介绍 含(EndNote 21激活码)

Rose

文献管理工具 EndNote 21下载 EndNote 21破解版 EndNote 21授权码

如何定量分析 Llama 3,大模型系统工程师视角的 Transformer 架构

百度Geek说

大模型 Llama3

担心异构数据库迁移踩“坑”?听听大咖们怎么说

YashanDB

yashandb 崖山数据库 崖山DB

告别手动错误,NineData自动化平台保障数据库变更零失误

NineData

数据库 dba NineData 变更一致性 多环境结构

职场<火焰杯>测试开发大赛证书已开放下载!

测试人

软件测试

喜加一!望繁信科技再摘「2023年度新锐技术品牌奖」

望繁信科技

流程挖掘 流程资产 流程智能 数字北极星 望繁信科技

链路级资损防控之资损字段防控实践|得物技术

得物技术

最佳实践 测试 资损防控 企业号2024年7月PK榜

flowjo 10 for mac流式细胞分析 详细安装破解教程 支持m1/m2/intel

Rose

流式细胞分析软件 FlowJo 10破解版 FlowJo Mac版 FlowJo 安装教程

最高可管理 10K 实例的 InstanceSet 是什么?

小猿姐

数据库 Kubernetes 云原生

Microsoft PowerPoint 2019 for Mac永久使用版 支持Inte.m1.m2.m3安装

Rose

PowerPoint 2019 ppt2019下载 PowerPoint 2019 破解

豆包,大模型的磁力三重奏

脑极体

AI

ChemDraw可以绘制哪些物质?ChemDraw破解补丁版下载安装 Mac/win

Rose

ChemDraw破解版 ChemDraw化学绘图 ChemDraw下载安装

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