大幅提升MySQL中InnoDB的全表掃描速度的方法_Mysql

 在 InnoDB中更加快速的全表掃描
 一般來講,大多數應用查詢的時候都會用索引，尋找很少的幾行資料（主鍵尋找或百行內的查詢），但有時候我們需要全表查詢。典型的全表掃描就是邏輯備份  (mysqldump) 和 online schema changes（ 註：線上上對大表 schema 的操作，也是 facebook 的一個開源項目） (SELECT ... INTO OUTFILE).

 在 Facebook我們用 mysqldump 來備份資料庫. 正如你所知MySql提供兩種備份方式，提供了物理備份和邏輯備份的命令和工具. 相對物理備份，邏輯備份有一定的優勢，例如:

•     邏輯備份備份資料要小得多. 3x-10x 尺寸差異並不少見。
•     更容易解析備份資料庫. 在物理備份中,在出現嚴重問題時候，如校正失敗。如果我們不能將資料庫恢複 ，想知道InnoDB內部資料結構，或者修複損壞是十分困難的。比起物理備份我們更加相邏輯備份。

邏輯備份的主要缺點是資料庫的完全備份和完全還原比物理的備份恢複慢得多。

緩慢的完全邏輯備份往往會導致問題.如果資料庫中存在很多大小支離破碎的表，它可能需要很長的時間。在 臉書，我們面臨 mysqldump 的效能問題，導致我們不能在合理的時間內對一些(基於HDD和Flashcache的)伺服器完成完整邏輯備份。我們知道 InnoDB做全表掃描並不高效，因為 InnoDB 實際上並沒有順序讀取,在大多情況下是在隨機讀取。這是一個已知多年的老問題了。我們的資料庫儲存容量一直在增長，緩慢的全表掃描問題給我們造成了嚴重的影響,因此,我們決定加強 InnoDB 做順序讀取的速度。最後我們的資料庫攻堅工程師團隊在InnoDB 中實現了"Logical Readahead"功能。應用"Logical readahead"，在通常生產工作負載下，我們全表掃描速比之從前度提高 9 ~ 10 倍。在超負荷生產中，全表掃描速度達到 15 ~ 20 倍的速度甚至更快。

全表掃描在大的、片段化資料表上的問題
做全表掃描時,InnoDB 會按主鍵順序掃描頁面和行。這應用於所有的InnoDB 表，包括片段化的表。如果主鍵頁表沒有片段（儲存主鍵和行的頁表),全表掃描是相當快，因為讀取順序接近實體儲存體順序。這是類似於讀取檔案的作業系統命令(dd/cat/etc) 像下面。
 

複製代碼 代碼如下:

dd if=/data/mysql/dbname/large_table.ibd of=/dev/null bs=16k iflag=direct

你可能會發現即使在商業HDD伺服器上，你可以達到高於比100 MB/s 乘以"磁碟機數目"的速度。超過1GB/s並不少見。

不幸的是，在許多情況下主要關鍵頁表存在片段。例如，如果您需要管理 user_id 和 object_id 映射，主鍵將會是(user_id,object_id)。插入排序與 user_id並不一致，那麼新插入/更新往往導致頁面分割。新的拆分頁將被分配在遠離當前頁的位置。這意味著頁面將會片段化。

如果主鍵頁是片段化的，全表掃描將會變得極其緩慢。圖1闡釋了這個問題。在InnoDB讀取葉子頁#3之後，它需要讀取頁#5230，在那之後還要讀頁#4。頁#5230位置離頁#3和頁#4很遠，所以磁碟讀操作順序開始變得幾乎是隨機的，而不是連續的。大家都知道HDD上的隨機讀要比連續讀慢得多。一個有效改進隨機讀效能的辦法是使用SSD。不過SSD每個GB的價錢要比HDD昂貴的多，所以使用SSD通常是不可能的。

圖 1.全表掃描實際沒有連續讀

線性預讀取真的有意義嗎？
InnoDB支援預讀取特性，稱作“線性預讀取”（ Linear Read Ahead）。擁有線性預讀取，如果N個page可以順序訪問（N可以通過innodb_read_ahead_threshold參數進行配置，預設為56），InnoDB可以一次讀取一個extent(64個連續的page，如果不壓縮每個page為1MB）。但是，實際來說這麼做的意義不大。一個extent(64個page)非常小。對於一個支離破碎的較大的資料庫表來說，下一個page不一定在同一個extent當中。上面圖1就是一個很好的例子。讀取page#3之後，InnoDB需要讀取page#5230。page#3和page#5230並不在同一個extent當中，所以線性預讀取技術在這裡用處不大。這對於大表來說是非常常見的情況，所以這也解釋了線性預讀取技術為什麼不能有效改善全表掃描的效能。
 
物理預讀取
正如上面描述的，全表掃描速度較慢的主要原因是InnoDB主要進行隨機讀取。為了加速全表掃描，需要使InnoDB進行順序讀取。我想到的第一個方法就是建立一個UDF（user defined function)順序的讀取ibd檔案(InnoDB的資料檔案）。UDF執行完成後，ibd檔案的page應當儲存在InnoDB的緩衝池當中，所以在進行全表掃描時無需再進行隨機讀取。下面是一個樣本用法：
 

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

buf_warmup() 是一個使用者自訂函數，用來讀取資料庫“db1"的表”large_table"的整個ibd檔案。該函數需要花費時間將ibd檔案從硬碟讀取，但因為是順序讀取的，所以比隨機讀取要快的多。在我的測試當中，比普通的線性預讀取快差不多5倍左右。

這證明ibd檔案的順序讀取能夠有效改善吞吐率，但也存在一些缺點：

•     如果table的大小超過InnoDB緩衝池的大小，這種方法就不能工作
•     在全表掃描過程中，讀取整個的ibd檔案就意味著不但需要讀取primary key page還需要讀取二級索引page以及一些其他不需要的page，並將其儲存在緩衝池，儘管只有primary key page是實際需要的。如果擁有大量的二級索引，這種方法就不能有效工作
•     應用需要做出一定的修改以便調用UDF

這看起來是一個足夠好的解決方案，但我們的資料庫設計團隊想出了一個更好的解決方案叫做“邏輯預讀取”（Logical Read Ahead)，所以我們並不選擇UDF的方法。

邏輯預讀取
邏輯預讀取（LRA）的工作流程如下：

•     讀取主鍵的一些分支page
•     計算葉子page的數量
•     以page number的順序（大多數是順序磁碟讀取）依次讀取一些（通過配置控制數量的多少）葉子page
•     以主鍵的順序讀取行

整個流程如圖2所示：

Fig 2: Logical Read Ahead

邏輯預讀取解決了物理預讀取所存在的問題。LRA使InnoDB僅讀取主鍵page（不需要讀取二級索引頁面),並且每一次預讀取頁面的數量是可以控制的。除此之外，LRA對SQL文法不需要做任何修改。

為了使LRA工作，我們需要增加兩個session變數。一個是"innodb_lra_size"，用來控制預讀取葉子頁面(page)大小。另外一個是"innodb_lra_sleep"，用來控制每一次預讀取之間休眠多長時間。我們用512MB～4096MB的大小以及50毫秒的休眠時間來進行測試，到目前為止我們還沒有遇到任何嚴重問題（例如崩潰/阻塞/不一致等）。這些session變數僅在需要進行全表的時候進行設定。在我們的應用中，mysqldump以及其他一些輔助指令碼啟用了邏輯預讀取。

一次提交多個async I/O請求

我們注意到，另外一個導致效能問題的原因是InnoDB 每次i/o僅讀取一個頁面，即使開啟了預讀取技術。每次僅讀取16KB對於順序讀取來說實在是太小了，效率相比大的讀取單元要低很多。

在版本5.6中，InnoDB預設使用Linux本地I/O。如果一次提交多個連續的16KB讀請求，Linux在內部會將這些請求合并，讀操作能夠更有效執行。不幸的是，InnoDB一次只會提交一個頁面的i/o請求。我提交了一個bug report#68659.正如bug report中所寫，在一個當代的HDD RAID 1+0環境中，如果我一次性提交64個連續的頁面讀取請求，我可以獲得超過1000MB/s的硬碟讀取速度；如果每次只提交一個頁面讀取請求，我們僅可以獲得160MB/s的硬碟讀取速度。

為了使LRA在我們的應用環境中更好的工作，我們修正了這個問題。在我們的MySQl中，InnoDB在調用io_submit()之前會提交多個頁面i/o請求。

基準測試
在所有的測試中，我們使用的都是生產環境下的資料庫表（分頁的表）。

1. 純HDD環境全表掃描 (基礎的基準測試, 沒有其他的工作負載)

2. Online schema change under heavy workload

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

•  - 邏輯預讀取實現 : diff
•  - 一次提交多個i/o請求:diff
•  - 在mydqldump中啟用邏輯預讀取 :diff

結論

對於全表掃描來說InnoDB的工作效率不高，所以我們對它做了一定的修改。我在兩方面進行了改進，一是實現了邏輯預讀取；一是實現了一次提交多個async read i/o請求。對於我們生產環境中的資料庫表來說，我們獲得了8-18倍的效能提高，這對於減少備份時間、模式修改時間等來說是非常有用的。我希望這些特效能夠在InnoDB中獲得Oracle官方支援，至少是主要的MySQL分支。