這篇文章主要介紹了大幅提升MySQL中InnoDB的全表掃描速度的方法,作者談到了預讀取和多次async I/O請求等方法,減小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) 像下面。
 

你可能會發現即使在商業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的緩存池當中，所以在進行全表掃描時無需再進行隨機讀取。下面是一個示例用法：
 
 

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