主頁 > 知識庫 > 我是如何用2個(gè)Unix命令給MariaDB SQL提速的

我是如何用2個(gè)Unix命令給MariaDB SQL提速的

熱門標(biāo)簽:地圖標(biāo)注專員入駐 外呼系統(tǒng)怎樣才能不封號 西藏地圖標(biāo)注改進(jìn)點(diǎn) 神行者百貨商場地圖標(biāo)注 地圖標(biāo)注百度競價(jià) 如何辦理400客服電話 安陽手機(jī)自動(dòng)外呼系統(tǒng)原理是什么 地圖標(biāo)注什么軟件好用 外呼系統(tǒng)線路經(jīng)常出問題嗎

譯者 | 薛命燈

我試圖在 MariaDB(MySQL)上運(yùn)行一個(gè)簡單的連接查詢,但性能簡直糟糕透了。下面將介紹我是如何通過兩個(gè)簡單的 Unix 命令,將查詢時(shí)間從 380 小時(shí)降到 12 小時(shí)以下的。

下面就是這個(gè)查詢,它是 GHTorrent 分析的一部分,我使用了關(guān)系在線分析處理框架 simple-rolap 來實(shí)現(xiàn)這個(gè)分析。

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;

兩個(gè)連接字段都有索引。不過,MariaDB 是通過對 project_commits 進(jìn)行全表掃描和對 commits 進(jìn)行索引查找來實(shí)現(xiàn)連接的。這可以從 EXPLAIN 的輸出看出來。

這兩個(gè)表中的記錄比較多:project_commits 有 50 億行記錄,commits 有 8.47 億行記錄。服務(wù)器的內(nèi)存比較小,只有 16GB。所以很可能是因?yàn)閮?nèi)存放不下那么大的索引,需要讀取磁盤,因此嚴(yán)重影響到了性能。從 pmonitor 對臨時(shí)表的分析結(jié)果來看,這個(gè)查詢已經(jīng)運(yùn)行半天了,還需要 373 個(gè)小時(shí)才能運(yùn)行完。

/home/mysql/ghtorrent/project_commits#P#p0.MYD 6.68% ETA 373:38:11

在我看來,這個(gè)太過分了,因?yàn)榕判蚝喜⑦B接(sort-merge join)所需的 I/O 時(shí)間應(yīng)該要比預(yù)計(jì)的執(zhí)行時(shí)間要低一個(gè)數(shù)量級。我在 dba.stackexchange.com 上尋求幫助,有人給出了一些建議讓我嘗試,但我沒有信心它們能夠解決我的問題。我嘗試了第一個(gè)建議,結(jié)果并不樂觀。嘗試每個(gè)建議都需要至少半天的時(shí)間,后來,我決定采用一種我認(rèn)為可以有效解決這個(gè)問題的辦法。

我將這兩個(gè)表導(dǎo)出到文件中,使用 Unix 的 join 命令將它們連接在一起,將結(jié)果傳給 uniq,把重復(fù)的行移除掉,然后將結(jié)果導(dǎo)回到數(shù)據(jù)庫。導(dǎo)入過程(包括重建索引)從 20:41 開始,到第二天的 9:53 結(jié)束。以下是具體操作步驟。

1. 將數(shù)據(jù)庫表導(dǎo)出為文本文件

我先導(dǎo)出連接兩個(gè)表需要用到的字段,并按照連接字段進(jìn)行排序。為了確保排序順序與 Unix 工具的排序順序兼容,我將字段轉(zhuǎn)換為字符類型。

我將以下 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;

這樣就生成了以下兩個(gè)文件。

-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

為了避免內(nèi)存不足,我使用 –quick 選項(xiàng)來運(yùn)行 mysql 客戶端,否則客戶端會在輸出結(jié)果之前嘗試收集所有的記錄。

2. 使用 Unix 命令行工具處理文件

接下來,我使用 Unix 的 join 命令來連接這兩個(gè)文本文件。這個(gè)命令線性掃描兩個(gè)文件,并將第一個(gè)字段相同的記錄組合在一起。由于文件中的記錄已經(jīng)排好序,因此整個(gè)過程完成得很快,幾乎就是 I/O 的速度。我還將連接的結(jié)果傳給 uniq,用以消除重復(fù)記錄,這就解決了原始查詢中的 distinct 問題。同樣,在已經(jīng)排好序的輸出結(jié)果上,可以通過簡單的線性掃描完成去重。

這是我運(yùn)行的 Unix 命令。

join commits_week.txt project_commits.txt | uniq >joined_commits.txt

經(jīng)過一個(gè)小時(shí)的處理,我得到了想要的結(jié)果。

-rw-r–r– 1 dds dds 133G Aug 5 01:40 joined_commits.txt

3. 將文本文件導(dǎo)回?cái)?shù)據(jù)庫

最后,我將文本文件導(dǎo)回?cái)?shù)據(jù)庫。

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 ‘ ‘;

結(jié)語

理想情況下,MariaDB 應(yīng)該支持排序合并連接,并且在預(yù)測到備用策略的運(yùn)行時(shí)間過長時(shí),優(yōu)化器應(yīng)該使用排序合并連接。但在此之前,使用 70 年代設(shè)計(jì)的 Unix 命令就可以解決這個(gè)問題。

標(biāo)簽:酒泉 衡水 雞西 AXB 阜陽 萍鄉(xiāng) 張掖 貴港

巨人網(wǎng)絡(luò)通訊聲明:本文標(biāo)題《我是如何用2個(gè)Unix命令給MariaDB SQL提速的》,本文關(guān)鍵詞  我是,如,何用,2個(gè),Unix,命令,;如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題,煩請?zhí)峁┫嚓P(guān)信息告之我們,我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò),涉及言論、版權(quán)與本站無關(guān)。
  • 相關(guān)文章
  • 下面列出與本文章《我是如何用2個(gè)Unix命令給MariaDB SQL提速的》相關(guān)的同類信息!
  • 本頁收集關(guān)于我是如何用2個(gè)Unix命令給MariaDB SQL提速的的相關(guān)信息資訊供網(wǎng)民參考!
  • 推薦文章