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:001946
用户头像

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

关注

评论 1 条评论

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

React-Hooks源码深度解读

goClient1992

React

深入react源码看setState究竟做了什么?

flyzz177

React

2023算云融合产业大会-行业先驱!算力云服务领航者计划评选结果即将揭晓

中国IDC圈

算力 云算力 云计算,

如何用好免费的chatGPT

劼哥stone

人工智能 openai ChatGPT

精华推荐 |【深度挖掘RocketMQ底层源码】「生产故障分析系列」深度挖掘RocketMQ底层那些导致消息丢失的汇总盘点透析(TIMEOUT_CLEAN_QUEUE)broker busy

码界西柚

RocketMQ 消息队列 3月日更 原理分析 底层源码

React源码分析(一)Fiber

goClient1992

React

细说react源码中的合成事件

flyzz177

React

用javascript分类刷leetcode3.动态规划(图文视频讲解)

js2030code

JavaScript LeetCode

Matlab常用图像处理命令108例(五)

timerring

图像处理

Java线程池执行器的饱和策略

Janwee

线程池 java

mybatis plus怎么使用注解的方式执行原生sql

俊俊哥

mybatis Mybatis-Plus 原生sql

深入探究 Go log 标准库

江湖十年

Go 后端 日志 log

一天吃透分布式事务八股文

程序员大彬

Java 分布式事务

前端工程师leetcode算法面试必备-二分搜索算法(下)

js2030code

JavaScript LeetCode

运维训练营第17周作业

好吃不贵

A-Ops 数据库场景在线应用性能诊断案例

openEuler

数据库 Linux 操作系统 openEuler 性能测评

Java中让两个线程交替执行的实现方式

Janwee

并发 信号量 java 多线程、

提示工程在AI绘画领域中的应用

FinFish

AI绘画 提示工程

看透react源码之感受react的进化

goClient1992

React

使用Nginx作为MySQL的TCP负载均衡器

Janwee

nginx 负载均衡 :MySQL 数据库

什么大模型?我是时尚产业“大模王”!

脑极体

AI

Amazon Detective 支持 Amazon EKS 上的 Kubernetes 工作负载以进行安全调查

亚马逊云科技 (Amazon Web Services)

前端工程师leetcode算法面试必备-二分搜索算法(上)

js2030code

JavaScript LeetCode

毕业设计

张贺

马克·安德森:纯粹产品护城河在硅谷少见,SaaS公司护城河是“分销”

B Impact

微信小程序管理软件助力企业数字化转型,实现智能化升级

FinFish

私有小程序技术 小程序管理平台 小程序技术 小程序管理

PyTorch深度学习实战 | 搭建卷积神经网络进行图像分类与图像风格迁移

TiAmo

PyTorch 图像处理

React Context源码是怎么实现的呢

flyzz177

React

天工人巧日争新:昇腾AI的绚丽今日

脑极体

AI

Nautilus Chain 现已推出测试网“Triton ”,有哪些潜在的机会?

股市老人

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