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

这篇文章主要介绍了大幅提升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 */