一、影响SQL执行性能因素
影响SQL执行性能的关键就在于磁盘，数据库优化大部分工作都是在做磁盘的优化，比如尽可能把随机读写转换为顺序读写、预读、加大buffer pool、优化SQL尽量无磁盘操作等。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
mysql> show global status like 'i%read%'; +---------------------------------------+---------+ | Variable_name | Value | +---------------------------------------+---------+ | Innodb_buffer_pool_read_ahead_rnd | 0 | | Innodb_buffer_pool_read_ahead | 0 | | Innodb_buffer_pool_read_ahead_evicted | 0 | | Innodb_buffer_pool_read_requests | 27334 | | Innodb_buffer_pool_reads | 465 | | Innodb_data_pending_reads | 0 | | Innodb_data_read | 9687552 | | Innodb_data_reads | 718 | | Innodb_pages_read | 464 | | Innodb_rows_read | 6601 | +---------------------------------------+---------+ 10 rows in set (0.04 sec) |
Innodb_buffer_pool_read_ahead_rnd：记录进行随机读的时候产生的预读的次数。
Innodb_buffer_pool_read_ahead：记录进行连续读的时候产生的预读的次数。
Innodb_buffer_pool_read_ahead_evicted：InnoDB 预读的页还没被使用就被替换的页数量，一般用来判断预读的效果。
Innodb_buffer_pool_read_requests：从 Buffer Pool 读取页的次数（逻辑读）。
Innodb_buffer_pool_reads：从物理磁盘读取页的次数。
Innodb_data_read：物理读数据字节量。
Innodb_data_reads：物理读IO请求次数。
Innodb_pages_read：物理读数据页数。
这些参数都可以作为衡量磁盘繁忙程度，或者是否是buffer pool太小。还有一些write相关的变量，写基本就是脏页刷新及日志更新。
二、磁盘I/O优化
磁盘寻道是一个巨大的性能瓶颈，当数据量剧增到使得缓存命中率减小时，这个问题会更为明显。对于或多或少会进行随机访问的大型的数据库，可以确定对于读至少需要一次磁盘寻道，而对于写需要好几次磁盘寻道。要最小化这个问题，应该用磁盘寻道时间较小的磁盘。增加可用磁盘数，这样就可以通过符号链接将不同文件存储到不同磁盘，或者可以通过磁盘分条，来降低寻道开销；这就是我们常用的RAID技术。
- 使用符号链接
这意味着，对于MyISAM表，可以将索引文件和数据文件从它们在数据目录中的通常位置符号链接到另一个磁盘（也可能是条带化的）。假设磁盘不用于其他用途，那么这会使得查找和读取时间减小。详情请参见第9.12.3节“使用符号链接”。
- 条带化
分条意味着有多个磁盘使用的情况下，将第一个数据块放在第一个磁盘上，第二个块在第二个磁盘上，第N个块在（N对磁盘数取模）磁盘上，这意味着如果一般的数据大小小于条带大小（或者完全对齐），那么你将获得更好的性能。分条的性能非常依赖于操作系统和条带大小，因此应该设置不同的条带大小分别进行基准测试。详情请参见第9.13.2节“使用自己的基准”。
条带化的速度差异十分依赖于参数配置。根据如何设置条带化参数和硬盘数量，你可能会得到数量级的差异。对于优化随机访问还是顺序访问，你必须做出选择。
为了可靠性，你可能需要使用RAID 0+1（条带加镜像），但在这种情况下，需要2×N个磁盘来N个磁盘的数据。如果你有足够的钱，这可能是最好的选择，而且，你可能还需要额外投资一些卷管理软件来有效地管理它们。
一个好的选择是根据数据类型的关键程度来改变RAID级别。 例如，可以再生的一般重要数据可以放在RAID 0上，但将真正重要的数据（如主机信息和日志）存储在RAID 0+1或 RAID N磁盘上。 如果写入较多，则由于更新奇偶校验位需要时间，RAID N可能不太合适。
你也可以设置数据库所涉及的文件系统的参数：如果您不需要知道上次访问文件的时间（这在数据库服务器上通常用途不大），则可以使用-o noatime选项来挂载文件系统。 这会跳过对文件系统上inode的最后访问时间的更新，从而避免某些磁盘寻道。在许多操作系统上，您可以通过使用-o async选项挂载文件系统以将其设为异步更新。 如果您的计算机相当稳定，这应该给你更好的性能，而不会牺牲太多的可靠性。（在Linux上，此标志默认是打开的）
三、InnoDB磁盘IO优化
如果你遵循数据库设计和调优技术的最佳做法，但由于磁盘I/O活动较大，你的数据库仍然很慢，请考虑这些磁盘I/O优化。如果top工具显示你的CPU使用率百分比低于70％，则你的工作负载可能是磁盘导致的。几种优化方式如下：
- 增加缓冲池大小
当表数据缓存在InnoDB缓冲池中时，可以通过查询重复访问，而不需要任何磁盘I/O。使用innodb_buffer_pool_size选项指定缓冲池的大小。该内存区域足够重要，通常建议将innodb_buffer_pool_size其配置为系统内存的50％到75％。有关更多信息，请参见第8.12.4.1节“MySQL如何使用内存”。
- 调整数据刷新方法
MySQL InnoDB O_DIRECT与O_DIRECT_NO_FSYNC对性能影响
- 在Linux上noop和deadline两种I/O调度程序的选择
InnoDB使用Linux上的异步I/O子系统（Naive AIO）对数据文件页执行预读和写请求。此行为由innodb_use_native_aio配置选项控制，配置选项默认启用。使用Naive AIO来说，I/O调度器（I/O Scheduler）的类型对I/O性能有更大的影响。通常，建议使用noop或deadline这两种I/O调度进行基准测试，以确定哪个I/O调度程序为你的工作负载和环境提供最佳结果。
- 考虑非旋转存储
非旋转存储通常为随机I/O操作提供更好的性能，以及用于顺序I/O操作的旋转存储。在旋转和非旋转存储设备上分发数据和日志文件时，请主要考虑对每个文件执行的I/O操作的类型。面向随机的I/O的文件通常包括每表文件和一般表空间数据文件，撤销表空间文件和临时表空间文件。面向顺序的I/O的文件包括InnoDB系统表空间文件（双重缓冲和插入缓冲）以及日志文件，如二进制日志文件和重做日志文件。
使用非旋转存储时，请查看以下配置选项的设置：
innodb_checksum_algorithm
该crc32选项使用更快的校验和算法，建议用于快速存储系统。
innodb_flush_neighbors
该选项可优化旋转存储设备的I/O，禁用它用于非旋转存储或旋转和非旋转存储的混合。
innodb_io_capacity
对于低端非旋转存储设备，默认设置200通常就足够了。对于高端，总线连接的设备，请考虑更高的设置，如1000。
innodb_io_capacity_max
默认值为2000，适用于使用非旋转存储的工作负载。对于高端，总线附加的非旋转存储设备，考虑更高的设置，如2500。
innodb_log_compressed_pages
如果重做日志在非旋转存储器上，请考虑禁用此选项以减少日志记录。请参阅禁用压缩页面的日志记录。
innodb_log_file_size
如果重做日志在非旋转存储上，请配置此选项以最大化缓存和写入组合。
innodb_page_size
考虑使用与磁盘的内部扇区大小相匹配的页面大小。早期的SSD设备通常具有4k扇区大小，一些较新的设备具有16k扇区大小。默认InnoDB页面大小为16k。保持页面大小接近存储设备块大小可将未更改的数据量重写到磁盘中。
binlog_row_image
如果二进制日志在非旋转存储上，并且所有表都具有主键，请考虑将此选项设置minimal为减少日志记录。确保为你的操作系统启用了TRIM支持。通常默认情况下启用。
- 增加I/O capacity以避免积压
如果InnoDB检查点操作导致吞吐量周期性下降，请考虑增加innodb_io_capacity配置选项的值。这个值越高，flushing操作会越频繁，以减小数据积压，避免降低吞吐量。
当log cap（指未刷新到磁盘脏页的日志大小）大于整个日志空间的75%时，系统会异步的将log cap部分的日志涉及的脏页刷到磁盘上，但是此时事务提交不会终止，也就是说还允许有redo log的继续写入。但是如果log cap继续增加，当超过整个日志空间的90%时，MySQL会停止事务的更新，此时redo log也会停止写入，必须等到刷足够的脏页时，才能允许事务再次提交。本质上说，如果事务提交的速度大于脏页刷盘的速度，最终都会触发上述日志保护的功能，即最终系统停止事务的更新，来保证日志记录的脏页能够刷新到磁盘上。也就是说更高的值会导致更频繁的flushing，避免积压的工作可能导致吞吐量下降。
- 在Fusion-io设备上存储系统表空间文件
你可以通过在支持原子写入的Fusion-io设备上存储系统表空间文件（“ ibdata文件 ”），以获得doublewrite缓冲区相关的I/O优化。在这种情况下，doublewrite缓冲（innodb_doublewrite）被自动禁用，并且Fusion-io原子写入用于所有数据文件。此功能仅在Fusion-io硬件上受支持，并且仅在Linux上启用Fusion-io NVMFS。要充分利用此功能，建议使用innodb_flush_method设置O_DIRECT。
注意：由于doublewrite buffer的设置时全局的，所以对于那些留在非Fusion-io硬件中的数据文件，doublewrite buffer依旧是无法使用的。
- 禁止记录压缩页面
当使用InnoDB表压缩功能时，对压缩数据进行更改时，会将重新压缩页面的图像写入重做日志。此行为由innodb_log_compressed_pages控制，默认情况下启用此功能，以防止zlib在恢复期间使用不同版本的压缩算法时可能会发生损坏。如果你确定zlib版本不会更改，请禁用innodb_log_compressed_pages以减少修改压缩数据的重做日志生成的工作负载。
<参考>
https://www.jianshu.com/p/5248ca67eac2
https://dev.mysql.com/doc/refman/5.7/en/disk-issues.html
https://dev.mysql.com/doc/refman/5.7/en/optimizing-innodb-diskio.html
