mysql 磁盘分区优化io_从优化磁盘IO来看MySQL的InnoDB引擎

MySQL是目前最流行的开源关系型数据库，作为其默认数据库引擎的InnoDB，在设计上必然有过人之处，本文尝试从磁盘IO优化的角度来对InnoDB做一下解读。其解决性能瓶颈的思路，也值得我们参考。

存储器的性能瓶颈

我们常用的计算机系统里的存储器，主要有内存和硬盘，其中硬盘又可分为机械硬盘与固态硬盘，构造的不同使得他们有不同的特性和用途。仅比较读写性能，市面上常见产品的数据如下表：

读

写

内存

20+GB/s

20+GB/s

固态硬盘(顺序)

550MB/s

525MB/s

机械硬盘(顺序)

200MB/s

200MB/s

在随机读写的场景下，机械硬盘由于其磁头的物理构造的限制，在寻址上浪费了大量时间，导致IOPS限制在100+的数量级上，进一步了扩大与内存的速度差距。预读，硬盘缓存等技术在一定程度上改善了机械硬盘的性能，但面对大并发的OLAP应用，硬盘IO还是容易成为性能瓶颈所在。但目前来看最适合存储大体积数据库文件的存储器非机械硬盘莫属，那数据引擎该如何优化其性能呢。

InnoDB的解决方案

总体来说，就是用空间换时间：建立索引；减少随机读写次数，或者把随机读写转为顺序读写—缓冲池，读、写合并。

B+树索引

如果数据库表没有索引，那就像汉语字典没有查字表一样，每次想查一条记录，都得从到尾翻一遍，直到找到为止。所以字典会多出几十页来放查字表，而数据库也会用额外的存储空间记录一些辅助信息，来方便查找记录。InnoDB的索引是通过B+树的数据结构来实现的。

B+树是一个平衡的n叉排序树，其定义和维护方法可以很容易在数据结构的教材上找到。一个简单的B+树的例子如下图所示。

这是一个高度为2的B+树。InnoDB要求每个表都要有一个主键，作为B+树里节点排序的依据，称为聚簇索引，在聚簇索引的叶子节点里存放表的行记录。因为聚簇索引在逻辑上是有序、连续的，因此取出某个叶子节点所需要的读取次数与树的高度一致。在InnoDB中B+树的高度一般为2-4层，也就是2-4次硬盘IO。同时由于非叶子节点里只存放索引数据，减少了占用空间大小，因此与所有节点同质的排序树相比，在同一个物理存储单元里可以放下更多的非叶子节点，那么一次硬盘读取就能得到更多的索引信息，这就是B+树索引的高扇出性。

在实际应用中，我们往往还需要对业务需要的列添加辅助索引，来应对根据非主键字段查找记录的需求。在InnoDB里，每个辅助索引都是一个独立的B+树来维护的，与聚簇索引相比，辅助索引的叶子节点不直接存放对应的行记录，而是行记录对应的聚簇索引键，再到聚簇索引中查找。也就是说假如在一个高度为2的辅助索引中查找记录，对应聚簇索引的高度为3，最终取出行记录需要5次硬盘读取的操作。

同时辅助索引还支持对表上多个列进行联合索引，如图所示

假定两个键名是a和b，那么除了在查找条件是: a=xxx and b=yyy的情形外，a = xxx的查找条件也可以利用联合索引，因为会先根据a来排序；容易想到b= xxx的条件就不能用到这个联合索引了。

由B+树索引的实现方式可知，索引字段的值最好具有高选择性，也就是该字段的所有不重复取值的总数除以总记录数，值越接近1就越能通过索引快速定位到记录。当选择性比较低，通过辅助索引取到的聚簇索引键数量太大时，查询优化器可能就选择直接使用聚簇索引查询了，因为硬盘顺序读的性能要远好于随机读。

哈希索引和全文检索

InnoDB的自适应哈希索引是由InnoDB自动维护的，在B+树2-4次磁盘IO的基础上，进一步把读操作减少为1次，提升了效率。

B+树索引支持前缀查询，针对like ‘xxx%’的查询条件，可以利用这个列字段的索引快速查找xxx开头的记录。但是形如like ’%xxx%’的查询除了全表遍历就无能为力了，这种情况下可以为该列添加全文检索。

内存缓冲池

类似其他基于磁盘的数据库系统的做法，InnoDB也利用内存来弥补磁盘速度和CPU速度之间的巨大鸿沟。从数据库里进行读取数据页的操作，会先查找是否存在于缓冲池中，如果存在则直接从缓冲池中读取，否则再从磁盘中读取，然后根据LRU算法按照规则加入到缓冲池中。对数据库中的数据页进行修改，也会先修改缓冲池中的页，然后通过checkpoint机制触发刷新到磁盘中。

对于辅助索引来说，插入缓冲是非常有意义的。因为B+树的结构决定了顺序插入键的性能最好，但是作为辅助索引键的字段在一般情况下做不到这一点，每次插入都需要随机读写。因此对非唯一的辅助索引来说，把多次插入操作缓存起来，在适当的时机合并成一次操作就能显著减少离散IO操作请求。

使用SSD时的InnoDB参数优化

InnoDB的众多特性都是针对机械硬盘的IO来优化的，由于SSD拥有较好的随机读写性能，因此可以在使用SSD时调整InnoDB的参数，获得更好的性能，同时保护SSD，延长使用寿命。根据Mysql的官方文档，理论上推荐更改的参数如下：

1.nnodb_flush_neighbors刷新邻接页，刷新脏页到硬盘时一起刷新同一个extend的脏页。在使用SSD时应该关闭，减少不必要的写操作。

2.innodb_io_capacity和innodb_io_capacity_max这两个参数会影响InnoDB刷新脏页的时机和数量，应该根据硬盘的实际io能力进行设置，官方文档推荐值分别为2000和2500

3.innodb_page_size应该与SSD的最小块的大小一致，防止写未更改的数据

4.innodb_log_compressed_pages在确定zlib库不会更改的情况下应该关闭，减少不必要的写。

5.binlog_row_image减少，innodb_log_file_size增大目的都是减少对SSD的随机写操作

6.innodb_read_io_threads和innodb_write_io_threads是InnoDB负责IO的线程。如果在查看InnoDB状态时发现有超过64*nnodb_read_io_threads的请求在等待，就应该提高innodb_read_io_threads的值了。

在实际使用中，上述参数并没有一个普适的最优值。根据DBA们分享的实际操作经验，在新版本的Mysql中某些值的开关并没有引起性能的变化，官方推荐值也不是最佳的选择，应该根据应用的特点，动态调整至最优。

找到最适用的技术

目前InnoDB的实现，应该是限于传统机械硬盘的性能特点下的权宜之计。InnoDB的局限性体现在很多方面，例如InnoDB的B+树实现就没有充分利用固态硬盘的能力，而类似Redis这种基于内存的NoSQL干脆就不使用B+树的数据结构，但这都不应该是工程人员需要关注的内容。一方面，我们应该针对使用的硬件设备情况，利用InnoDB提供的参数进行调优；另一方面，针对具体的场景，选择合适的工具。找到问题瓶颈，才是提升自我，获得成长的关键。

图片来源&参考资料：

《MySQL技术内幕：InnoDB存储引擎》姜承尧著

https://dev.mysql.com/doc/refman/5.6/en/optimizing-innodb-diskio.html