发布在即!企业 AIGC 应用程度测评,3 步定制专属评估报告。抢首批测评权益>>> 了解详情
写点什么

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

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

关注

评论 1 条评论

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

如何进行存储容量规划?

ScaleFlux

存储成本 数据压缩 企业级SSD NVMeSSD

ScaleFlux企业级SSD通过VMware IOVP认证

ScaleFlux

vmware 固态硬盘 企业SSD 存储解决方案

小程序容器与PWA的完美结合:提升应用性能与用户体验

FinFish

私有小程序技术 小程序容器 PWA 小程序化 小程序技术

「2023最新版」Java基础、中级、高级面试题总结(1000道题含答案解析)

采菊东篱下

java面试

牛客网 2023 最新 1100道 Java 面试题来袭,面面俱到,太全了!

架构师之道

java面试

软件设计中你考虑过重试了吗?

做梦都在改BUG

Java 软件设计 重试机制

艾媒咨询 | 2023年中国信创产业发展白皮书

亚信AntDB数据库

数据库 AntDB AntDB数据库

双非渣硕,开发两年,苦刷算法47天,四面字节斩获offer

做梦都在改BUG

Java 数据结构 算法 LeetCode

太赞了,京东研发一哥力荐的高可用网站构建技术

做梦都在改BUG

Java 架构 京东

线程池是如何执行的?任务太多会怎样?

做梦都在改BUG

Java 线程池

阿里一面凉凉,幸获内推华为技术四面,offer到手!

程序知音

Java java面试 Java进阶 八股文 Java面试八股文

活动回顾丨首期阿里云 Serverless 技术创新实战营上海开讲(含 PPT 下载)

Serverless Devs

内核调试环境搭建

郑州埃文科技

网络安全 网络环境

阿里P8大佬的1800页计算机基础知识总结与操作系统,太强了!

做梦都在改BUG

Java 程序员 操作系统

MegEngine 使用小技巧:如何解读 MegCC 编译模型几个阶段 Pass 的作用

MegEngineBot

深度学习 编译器 MegEngine Pass

旅游景区如何寻找共享电单车厂家

共享电单车厂家

共享电动车厂家 景区共享电单车 共享电动车投放 景区共享电动车

火山引擎DataLeap的Catalog系统搜索实践(一):背景与功能需求

字节跳动数据平台

探索 PlanetIX:解读区块链游戏运营的奥秘

Footprint Analytics

区块链游戏 web3 Planet IX

财务标准化建设进程中,财务共享能起到什么作用?

用友BIP

财务共享

狂刷三遍398道java最新MySQL笔记后,我四面阿里研发部,成功定级P7

做梦都在改BUG

Java MySQL 数据库

基于 Log 的通用增量 Checkpoint

Apache Flink

大数据 flink 实时计算

精准快速搜索文件:Find Any File 激活版

真大的脸盆

Mac 办公效率 文件搜索 搜索工具 搜索文件

Hybrid Shuffle 测试分析和使用建议

Apache Flink

大数据 flink 实时计算

如何用ReadWriteLock实现一个通用的缓存中心?

华为云开发者联盟

Java 开发 华为云 华为云开发者联盟 企业号 5 月 PK 榜

空降攻略!一文带你玩转2023开放原子全球开源峰会

开放原子开源基金会

开源 开放原子开源基金会 攻略 开放原子全球开源峰会

一文吃透基于低代码平台的开发优势

这我可不懂

低代码 零代码 应用开发 JNPF

电商行业实践专栏上线|阿里巴巴风控实战如何解决大规模风控的技术难点?

Apache Flink

大数据 flink 实时计算

开源边缘计算项目【FabEdge v0.8.0】配置connector公开端口操作说明

BoCloud博云

边缘计算 容器网络 FabEdge #开源项目体验 边缘网络

今天又和Redis超时杠上了

华为云开发者联盟

数据库 后端 华为云 华为云开发者联盟 企业号 5 月 PK 榜

艾媒金榜|2023年中国信创企业百强榜

亚信AntDB数据库

数据库 AntDB AntDB数据库

用友BIP新一代全球司库,重塑企业资金管理新价值

用友BIP

全球司库

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