mysql8 翻译系列 三十八

10.5.1 优化InnoDB表的存储布局

当你的数据量达到稳定规模，或者一个不断增长的表已经增加了几十或几百兆字节时，可以考虑使用OPTIMIZE TABLE语句来重组表并压缩任何浪费的空间。经过重组的表在执行全表扫描时需要更少的磁盘I/O操作。当诸如改进索引使用或调整应用程序代码等其他技术不切实际时，这是一种简单直接的性能提升方法。

OPTIMIZE TABLE语句会复制表的数据部分并重建索引。其优势在于优化了索引内的数据存储方式，减少了表空间和磁盘上的碎片化。具体的优化效果因每张表的数据而异。你可能会发现，某些表能获得显著的性能提升，而另一些表则不然；或者随着时间推移，性能提升效果逐渐减弱，直到你再次对表进行优化。如果表很大，或者正在重建的索引无法完全放入缓冲池，这个操作可能会比较慢。在向表中添加大量数据后的首次运行，往往比后续运行要慢得多。

在InnoDB中，过长的PRIMARY KEY（无论是具有长值的单个列，还是由多个列组成的长复合值）会浪费大量磁盘空间。一行的主键值会在所有指向同一行的二级索引记录中重复（请参阅17.6.2.1节 “聚簇索引和二级索引”）。如果主键很长，可以创建一个AUTO_INCREMENT列作为主键；或者对长VARCHAR列的前缀进行索引，而不是对整个列进行索引。

存储可变长度字符串或有很多NULL值的列时，使用VARCHAR数据类型而非CHAR。CHAR(N)列在存储数据时，即使字符串较短或值为NULL，也始终占用N个字符的空间。较小的表能更好地适应缓冲池，减少磁盘I/O操作。

当使用COMPACT行格式（InnoDB的默认格式）和可变长度字符集（如utf8mb4或sjis）时，CHAR(N)列会占用可变数量的空间，但至少仍为N字节。

对于大表，或者包含大量重复文本或数字数据的表，可以考虑使用COMPRESSED行格式。使用这种格式，将数据读入缓冲池或执行全表扫描时，所需的磁盘I/O操作更少。在做出永久性决定之前，对比使用COMPRESSED和COMPACT行格式的压缩效果，衡量可实现的压缩程度。

10.5.2 优化InnoDB事务管理

为了优化InnoDB的事务处理，需要在事务特性带来的性能开销与服务器的工作负载之间找到理想的平衡。例如，一个应用程序如果每秒提交数千次事务，可能会遇到性能问题；而如果每2 - 3小时才提交一次事务，又可能会出现其他性能问题。

MySQL的默认设置AUTOCOMMIT=1可能会对繁忙的数据库服务器的性能造成限制。在可行的情况下，通过执行SET AUTOCOMMIT=0或START TRANSACTION语句，将多个相关的数据更改操作包装到一个事务中，在完成所有更改后再执行COMMIT语句。

如果事务对数据库进行了修改，InnoDB必须在每次事务提交时将日志刷新到磁盘。当每次更改后都进行提交（如默认的自动提交设置）时，存储设备的I/O吞吐量会限制每秒的潜在操作数量。

另外，对于仅由单个SELECT语句组成的事务，开启AUTOCOMMIT有助于InnoDB识别只读事务并对其进行优化。相关要求请参见10.5.3节 “优化InnoDB只读事务”。

应避免在插入、更新或删除大量行之后执行回滚操作。如果一个大型事务导致服务器性能下降，回滚操作可能会使问题更加严重，回滚所需的时间可能是原始数据更改操作的数倍。终止数据库进程也无济于事，因为服务器启动时回滚操作会再次开始。

为了最大程度减少此类问题的发生：

• 增大缓冲池的大小，以便所有数据更改都能被缓存，而不是立即写入磁盘。
• 设置innodb_change_buffering=all，这样除了插入操作外，更新和删除操作也会被缓存。
• 考虑在大数据更改操作期间定期执行COMMIT语句，可能将单个删除或更新操作分解为多个对较少行数进行操作的语句。

一旦出现失控的回滚操作，为了摆脱这种情况，可以增大缓冲池，使回滚操作受限于CPU处理能力，从而快速运行；或者终止服务器并使用innodb_force_recovery=3重新启动，如17.18.2节 “InnoDB恢复” 中所述。

在默认设置innodb_change_buffering=all的情况下，预计这类问题很少发生。该设置允许将更新和删除操作缓存在内存中，这不仅使这些操作一开始执行得更快，而且在需要回滚时也能更快完成。在处理包含大量插入、更新或删除操作的长时间运行事务的服务器上，务必使用此参数设置。

如果在意外退出时能够承受丢失一些最新提交的事务，可以将innodb_flush_log_at_trx_commit参数设置为0。InnoDB仍会尝试每秒刷新一次日志，不过无法保证一定会刷新。

当对行进行修改或删除时，这些行及相关的撤销日志不会立即被物理删除，甚至在事务提交后也不会立即删除。旧数据会一直保留，直到更早或并发启动的事务完成，以便这些事务能够访问被修改或删除行的先前状态。因此，一个长时间运行的事务可能会阻止InnoDB清除其他事务更改的数据。

在长时间运行的事务中对行进行修改或删除时，使用READ COMMITTED（读已提交）和REPEATABLE READ（可重复读）隔离级别的其他事务，如果读取相同的行，就必须做更多的工作来重建旧数据。

当一个长时间运行的事务修改了一个表时，其他事务针对该表的查询将无法使用覆盖索引技术。通常可以从二级索引中检索所有结果列的查询，此时反而需要从表数据中查找相应的值。

如果发现二级索引页的PAGE_MAX_TRX_ID太新，或者二级索引中的记录被标记为删除，InnoDB可能需要使用聚簇索引来查找记录。