在 InnoDB中更加快速的全表掃描
 一般來(lái)講,大多數(shù)應(yīng)用查詢(xún)的時(shí)候都會(huì)用索引，查找很少的幾行數(shù)據(jù)（主鍵查找或百行內(nèi)的查詢(xún)），但有時(shí)候我們需要全表查詢(xún)。典型的全表掃描就是邏輯備份  (mysqldump) 和 online schema changes（ 注：在線上對(duì)大表 schema 的操作，也是 facebook 的一個(gè)開(kāi)源項(xiàng)目） (SELECT ... INTO OUTFILE).

 在 Facebook我們用 mysqldump 來(lái)備份數(shù)據(jù)庫(kù). 正如你所知MySql提供兩種備份方式，提供了物理備份和邏輯備份的命令和工具. 相對(duì)物理備份，邏輯備份有一定的優(yōu)勢(shì)，例如:

•     邏輯備份備份數(shù)據(jù)要小得多. 3x-10x 尺寸差異并不少見(jiàn)。
•     更容易解析備份數(shù)據(jù)庫(kù). 在物理備份中,在出現(xiàn)嚴(yán)重問(wèn)題時(shí)候，如校驗(yàn)失敗。如果我們不能將數(shù)據(jù)庫(kù)恢復(fù) ，想知道InnoDB內(nèi)部數(shù)據(jù)結(jié)構(gòu)，或者修復(fù)損壞是十分困難的。比起物理備份我們更加相邏輯備份。

邏輯備份的主要缺點(diǎn)是數(shù)據(jù)庫(kù)的完全備份和完全還原比物理的備份恢復(fù)慢得多。

緩慢的完全邏輯備份往往會(huì)導(dǎo)致問(wèn)題.如果數(shù)據(jù)庫(kù)中存在很多大小支離破碎的表，它可能需要很長(zhǎng)的時(shí)間。在 臉書(shū)，我們面臨 mysqldump 的性能問(wèn)題，導(dǎo)致我們不能在合理的時(shí)間內(nèi)對(duì)一些(基于HDD和Flashcache的)服務(wù)器完成完整邏輯備份。我們知道 InnoDB做全表掃描并不高效，因?yàn)?InnoDB 實(shí)際上并沒(méi)有順序讀取,在大多情況下是在隨機(jī)讀取。這是一個(gè)已知多年的老問(wèn)題了。我們的數(shù)據(jù)庫(kù)存儲(chǔ)容量一直在增長(zhǎng)，緩慢的全表掃描問(wèn)題給我們?cè)斐闪藝?yán)重的影響,因此,我們決定加強(qiáng) InnoDB 做順序讀取的速度。最后我們的數(shù)據(jù)庫(kù)攻堅(jiān)工程師團(tuán)隊(duì)在InnoDB 中實(shí)現(xiàn)了"Logical Readahead"功能。應(yīng)用"Logical readahead"，在通常生產(chǎn)工作負(fù)載下，我們?nèi)頀呙杷俦戎畯那岸忍岣?9 ~ 10 倍。在超負(fù)荷生產(chǎn)中，全表掃描速度達(dá)到 15 ~ 20 倍的速度甚至更快。

全表掃描在大的、碎片化數(shù)據(jù)表上的問(wèn)題
做全表掃描時(shí),InnoDB 會(huì)按主鍵順序掃描頁(yè)面和行。這應(yīng)用于所有的InnoDB 表，包括碎片化的表。如果主鍵頁(yè)表沒(méi)有碎片（存儲(chǔ)主鍵和行的頁(yè)表),全表掃描是相當(dāng)快，因?yàn)樽x取順序接近物理存儲(chǔ)順序。這是類(lèi)似于讀取文件的操作系統(tǒng)命令(dd/cat/etc) 像下面。
 

不幸的是，在許多情況下主要關(guān)鍵頁(yè)表存在碎片。例如，如果您需要管理 user_id 和 object_id 映射，主鍵將會(huì)是(user_id,object_id)。插入排序與 user_id并不一致，那么新插入/更新往往導(dǎo)致頁(yè)拆分。新的拆分頁(yè)將被分配在遠(yuǎn)離當(dāng)前頁(yè)的位置。這意味著頁(yè)面將會(huì)碎片化。

如果主鍵頁(yè)是碎片化的，全表掃描將會(huì)變得極其緩慢。圖1闡釋了這個(gè)問(wèn)題。在InnoDB讀取葉子頁(yè)#3之后，它需要讀取頁(yè)#5230，在那之后還要讀頁(yè)#4。頁(yè)#5230位置離頁(yè)#3和頁(yè)#4很遠(yuǎn)，所以磁盤(pán)讀操作順序開(kāi)始變得幾乎是隨機(jī)的，而不是連續(xù)的。大家都知道HDD上的隨機(jī)讀要比連續(xù)讀慢得多。一個(gè)有效的改進(jìn)隨機(jī)讀性能的辦法是使用SSD。不過(guò)SSD每個(gè)GB的價(jià)錢(qián)要比HDD昂貴的多，所以使用SSD通常是不可能的。

圖 1.全表掃描實(shí)際沒(méi)有連續(xù)讀

線性預(yù)讀取真的有意義嗎？
InnoDB支持預(yù)讀取特性，稱(chēng)作“線性預(yù)讀取”（ Linear Read Ahead）。擁有線性預(yù)讀取，如果N個(gè)page可以順序訪問(wèn)（N可以通過(guò)innodb_read_ahead_threshold參數(shù)進(jìn)行配置，默認(rèn)為56），InnoDB可以一次讀取一個(gè)extent(64個(gè)連續(xù)的page，如果不壓縮每個(gè)page為1MB）。但是，實(shí)際來(lái)說(shuō)這么做的意義不大。一個(gè)extent(64個(gè)page)非常小。對(duì)于一個(gè)支離破碎的較大的數(shù)據(jù)庫(kù)表來(lái)說(shuō)，下一個(gè)page不一定在同一個(gè)extent當(dāng)中。上面圖1就是一個(gè)很好的例子。讀取page#3之后，InnoDB需要讀取page#5230。page#3和page#5230并不在同一個(gè)extent當(dāng)中，所以線性預(yù)讀取技術(shù)在這里用處不大。這對(duì)于大表來(lái)說(shuō)是非常常見(jiàn)的情況，所以這也解釋了線性預(yù)讀取技術(shù)為什么不能有效改善全表掃描的性能。
 
物理預(yù)讀取
正如上面描述的，全表掃描速度較慢的主要原因是InnoDB主要進(jìn)行隨機(jī)讀取。為了加速全表掃描，需要使InnoDB進(jìn)行順序讀取。我想到的第一個(gè)方法就是創(chuàng)建一個(gè)UDF（user defined function)順序的讀取ibd文件(InnoDB的數(shù)據(jù)文件）。UDF執(zhí)行完成后，ibd文件的page應(yīng)當(dāng)保存在InnoDB的緩存池當(dāng)中，所以在進(jìn)行全表掃描時(shí)無(wú)需再進(jìn)行隨機(jī)讀取。下面是一個(gè)示例用法：
 

mysql> SELECT buf_warmup ("db1", "large_table"); /* loading into buf pool */
mysql> SELECT * FROM large_application_table; /* in-memory select */

buf_warmup() 是一個(gè)用戶(hù)自定義函數(shù)，用來(lái)讀取數(shù)據(jù)庫(kù)“db1"的表”large_table"的整個(gè)ibd文件。該函數(shù)需要花費(fèi)時(shí)間將ibd文件從硬盤(pán)讀取，但因?yàn)槭琼樞蜃x取的，所以比隨機(jī)讀取要快的多。在我的測(cè)試當(dāng)中，比普通的線性預(yù)讀取快差不多5倍左右。

這證明ibd文件的順序讀取能夠有效的改善吞吐率，但也存在一些缺點(diǎn)：

•     如果table的大小超過(guò)InnoDB緩存池的大小，這種方法就不能工作
•     在全表掃描過(guò)程中，讀取整個(gè)的ibd文件就意味著不但需要讀取primary key page還需要讀取二級(jí)索引page以及一些其他不需要的page，并將其保存在緩存池，盡管只有primary key page是實(shí)際需要的。如果擁有大量的二級(jí)索引，這種方法就不能有效的工作
•     應(yīng)用需要做出一定的修改以便調(diào)用UDF

這看起來(lái)是一個(gè)足夠好的解決方案，但我們的數(shù)據(jù)庫(kù)設(shè)計(jì)團(tuán)隊(duì)想出了一個(gè)更好的解決方法叫做“邏輯預(yù)讀取”（Logical Read Ahead)，所以我們并不選擇UDF的方法。

邏輯預(yù)讀取
邏輯預(yù)讀?。↙RA）的工作流程如下：

•     讀取主鍵的一些分支page
•     計(jì)算葉子page的數(shù)量
•     以page number的順序（大多數(shù)是順序磁盤(pán)讀取）依次讀取一些（通過(guò)配置控制數(shù)量的多少）葉子page
•     以主鍵的順序讀取行

整個(gè)流程如圖2所示：

Fig 2: Logical Read Ahead

邏輯預(yù)讀取解決了物理預(yù)讀取所存在的問(wèn)題。LRA使InnoDB僅讀取主鍵page（不需要讀取二級(jí)索引頁(yè)面),并且每一次預(yù)讀取頁(yè)面的數(shù)量是可以控制的。除此之外，LRA對(duì)SQL語(yǔ)法不需要做任何修改。

為了使LRA工作，我們需要增加兩個(gè)session變量。一個(gè)是"innodb_lra_size"，用來(lái)控制預(yù)讀取葉子頁(yè)面(page)大小。另外一個(gè)是"innodb_lra_sleep"，用來(lái)控制每一次預(yù)讀取之間休眠多長(zhǎng)時(shí)間。我們用512MB～4096MB的大小以及50毫秒的休眠時(shí)間來(lái)進(jìn)行測(cè)試，到目前為止我們還沒(méi)有遇到任何嚴(yán)重問(wèn)題（例如崩潰/阻塞/不一致等）。這些session變量?jī)H在需要進(jìn)行全表的時(shí)候進(jìn)行設(shè)置。在我們的應(yīng)用中，mysqldump以及其他一些輔助腳本啟用了邏輯預(yù)讀取。

一次提交多個(gè)async I/O請(qǐng)求

我們注意到，另外一個(gè)導(dǎo)致性能問(wèn)題的原因是InnoDB 每次i/o僅讀取一個(gè)頁(yè)面，即使開(kāi)啟了預(yù)讀取技術(shù)。每次僅讀取16KB對(duì)于順序讀取來(lái)說(shuō)實(shí)在是太小了，效率相比大的讀取單元要低很多。

在版本5.6中，InnoDB默認(rèn)使用Linux本地I/O。如果一次提交多個(gè)連續(xù)的16KB讀請(qǐng)求，Linux在內(nèi)部會(huì)將這些請(qǐng)求合并，讀操作能夠更有效的執(zhí)行。不幸的是，InnoDB一次只會(huì)提交一個(gè)頁(yè)面的i/o請(qǐng)求。我提交了一個(gè)bug report#68659.正如bug report中所寫(xiě)，在一個(gè)當(dāng)代的HDD RAID 1+0環(huán)境中，如果我一次性提交64個(gè)連續(xù)的頁(yè)面讀取請(qǐng)求，我可以獲得超過(guò)1000MB/s的硬盤(pán)讀取速度；如果每次只提交一個(gè)頁(yè)面讀取請(qǐng)求，我們僅可以獲得160MB/s的硬盤(pán)讀取速度。

為了使LRA在我們的應(yīng)用環(huán)境中更好的工作，我們修正了這個(gè)問(wèn)題。在我們的MySQl中，InnoDB在調(diào)用io_submit()之前會(huì)提交多個(gè)頁(yè)面i/o請(qǐng)求。

基準(zhǔn)測(cè)試
在所有的測(cè)試中，我們使用的都是生產(chǎn)環(huán)境下的數(shù)據(jù)庫(kù)表（分頁(yè)的表）。

1. 純HDD環(huán)境全表掃描 (基礎(chǔ)的基準(zhǔn)測(cè)試, 沒(méi)有其他的工作負(fù)載)

2. Online schema change under heavy workload

* dump time only, not counting data loading time
 源碼
 我們做出的所有增強(qiáng)修改都可以在GitHub上獲取。

•  - 邏輯預(yù)讀取實(shí)現(xiàn) : diff
•  - 一次提交多個(gè)i/o請(qǐng)求:diff
•  - 在mydqldump中啟用邏輯預(yù)讀取 :diff

結(jié)論

對(duì)于全表掃描來(lái)說(shuō)InnoDB的工作效率不高，所以我們對(duì)它做了一定的修改。我在兩方面進(jìn)行了改進(jìn)，一是實(shí)現(xiàn)了邏輯預(yù)讀??；一是實(shí)現(xiàn)了一次提交多個(gè)async read i/o請(qǐng)求。對(duì)于我們生產(chǎn)環(huán)境中的數(shù)據(jù)庫(kù)表來(lái)說(shuō)，我們獲得了8-18倍的性能提高，這對(duì)于減少備份時(shí)間、模式修改時(shí)間等來(lái)說(shuō)是非常有用的。我希望這些特性能夠在InnoDB中獲得Oracle官方支持，至少是主要的MySQL分支。

最新評(píng)論