• [高清组图]环广西赛:参赛车队赛前适应训练 2018-08-31
  • [高清组图]特谢拉复出吴曦失单刀 苏宁0-0平斯威 2018-08-31
  • [高清组图]潜水偶遇座头鲸 亲密互动玩起“水中击掌” 2018-08-31
  • [高清组图]法拉利拍定妆照 维特尔KIMI准备好了 2018-08-31
  • [高清组图]毛剑卿伤退莫雷诺捅射 申花1-0一方 2018-08-31
  • [高清组图]比埃拉双响巴坎布建功 国安5-1富力 2018-08-31
  • [高清组图]武磊世界波胡尔克点射 上港2-0胜申花 2018-08-31
  • [高清组图]武磊2球吕文君建功 上港3-1富力 2018-08-31
  • [高清组图]欧超杯-科斯塔2球 皇马加时赛2-4马竞 2018-08-31
  • [高清组图]格里芬赤膊骑行 休赛期享受二人世界 2018-08-31
  • [视频]【深化改革 重在实效】精准扶贫 四川彝区要拔掉“穷根” 2018-08-31
  • [视频]【深化改革 重在实效】破藩篱促合力 体制创新粘合“两张皮” 2018-08-31
  • [视频]【深化改革 重在实效】激发活力 实现市场准入全程便利化 2018-08-31
  • [视频]【深化改革 重在实效】打通简政放权的“最后一公里” 2018-08-31
  • [视频]【深化改革 重在实效】广东:户籍改革为外来工打开一扇门 2018-08-31
  • 手机版
    你好,游客 登录 注册 搜索
    背景:
    阅读新闻

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

    [日期:2018-08-13] 来源:infoq.com  作者:spinellis ,译者 无明 [字体: ]

    奥门新萄京官方正版 www.arianalance.com 我试图在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的输出看出来。

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

    这两个表中的记录比较多: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/

    Linux公社的RSS地址:http://www.arianalance.com/rssFeed.aspx

    本文永久更新链接地址http://www.arianalance.com/Linux/2018-08/153479.htm

    linux
    相关资讯       SQL提速 
    本文评论   查看全部评论 (0)
    表情: 表情 姓名: 字数

           

    评论声明
    • 尊重网上道德,遵守中华人民共和国的各项有关法律法规
    • 承担一切因您的行为而直接或间接导致的民事或刑事法律责任
    • 本站管理人员有权保留或删除其管辖留言中的任意内容
    • 本站有权在网站内转载或引用您的评论
    • 参与本评论即表明您已经阅读并接受上述条款