MySQL优化系列15-优化Innodb表

ゞ浴缸里的玫瑰 2022-08-30 05:18 204阅读 0赞

备注:测试数据库版本为MySQL 8.0

### 文章目录 ###

*  一. 优化InnoDB表的存储布局
 *  二.优化InnoDB事务管理
 *  三.优化InnoDB只读事务
 *  四. 优化InnoDB重做日志
 *  五. InnoDB表的批量数据加载
 *  六. 优化InnoDB查询
 *  七. 优化InnoDB DDL操作
 *  八. 优化InnoDB磁盘I/O
 *  九. 优化InnoDB配置变量
 *  十. 优化InnoDB系统与许多表
 *  参考:

# 一. 优化InnoDB表的存储布局 #

一旦您的数据达到稳定的大小，或者一个不断增长的表增加了几十或几百兆字节，请考虑使用OPTIMIZE table语句来重新组织表并压缩任何浪费的空间。重组后的表需要更少的磁盘I/O来执行全表扫描。这是一种简单的技术，可以在其他技术(如改进索引使用或调优应用程序代码)不实用时提高性能。

OPTIMIZE TABLE复制表的数据部分并重新构建索引。其好处在于改进了索引内的数据打包，减少了表空间和磁盘上的碎片。好处因每个表中的数据而异。您可能会发现一些人获得了显著的收益，而另一些人却没有，或者收益会随着时间的推移而减少，直到下一次优化表为止。如果表很大，或者正在重建的索引不适合缓冲池，则此操作可能会很慢。向表添加大量数据后的第一次运行通常比以后的运行要慢得多。

在InnoDB中，如果主键很长(一个列有一个很长的值，或者几个列组成一个很长的复合值)会浪费大量的磁盘空间。一行的主键值在指向同一行的所有辅助索引记录中重复。

使用VARCHAR数据类型而不是CHAR来存储可变长度的字符串或具有许多NULL值的列。一个CHAR(N)列总是使用N个字符来存储数据，即使这个字符串更短或者它的值是NULL。较小的表更适合缓冲池，并减少磁盘I/O。

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

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

# 二.优化InnoDB事务管理 #

要优化InnoDB事务处理，需要在事务特性的性能开销和服务器的工作负载之间找到理想的平衡。例如，如果应用程序每秒提交数千次，就可能遇到性能问题;如果应用程序每2-3小时提交一次，就可能遇到不同的性能问题。

默认MySQL设置AUTOCOMMIT=1可能会对繁忙的数据库服务器施加性能限制。在实际情况下，通过发出SET AUTOCOMMIT=0或START transaction语句，将几个相关的数据更改操作封装到一个事务中，然后在完成所有更改后执行COMMIT语句。

InnoDB必须在每个事务提交时将日志刷新到磁盘，如果该事务对数据库进行了修改。当每个更改之后都有一个提交(与默认的自动提交设置一样)时，存储设备的I/O吞吐量将为每秒的潜在操作数设置一个上限。

另外，对于只包含一个SELECT语句的事务，打开AUTOCOMMIT可以帮助InnoDB识别只读事务并优化它们。

避免在插入、更新或删除大量行之后执行回滚。如果一个大事务正在降低服务器性能，那么回滚它可能会使问题变得更糟，可能需要比原始数据更改操作多几倍的时间来执行。终止数据库进程没有帮助，因为回滚将在服务器启动时再次启动。

为了尽量减少这个问题的发生:

1.  增加buffer pool的大小，以便所有数据更改都可以缓存，而不是立即写入磁盘。
2.  设置innodb\_change\_buffering=all以便在插入操作之外缓冲更新和删除操作。
3.  考虑在大数据更改操作期间周期性地发出COMMIT语句，可能会将单个删除或更新分解为多个语句，这些语句对较小的行进行操作。

为了避免发生失控回滚，可以增加缓冲池，使回滚变成cpu绑定的，并快速运行，或者终止服务器并使用innodb\_force\_recovery=3重启。

对于默认设置innodb\_change\_buffering=all，这个问题预计不会很常见，因为默认设置允许将更新和删除操作缓存在内存中，这使得它们在执行时更快，在需要时回滚时也更快。请确保在处理具有许多插入、更新或删除的长时间运行的事务的服务器上使用此参数设置。

如果您能够承受在意外退出时丢失一些最新提交的事务，那么可以将innodb\_flush\_log\_at\_trx\_commit参数设置为0。InnoDB尝试每秒刷新一次日志，尽管不能保证刷新。

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

当在长时间运行的事务中修改或删除行时，使用READ COMMITTED和REPEATABLE READ隔离级别的其他事务如果读取相同的行，则必须做更多的工作来重构旧的数据。

当一个长期运行的事务修改一个表时，来自其他事务的对该表的查询不会使用覆盖索引技术。通常可以从二级索引检索所有结果列的查询，而不是从表数据中查找适当的值。

如果次要索引页的PAGE\_MAX\_TRX\_ID太新，或者次要索引中的记录被删除，InnoDB可能需要使用聚集索引来查找记录。

# 三.优化InnoDB只读事务 #

InnoDB可以避免为只读事务设置事务ID (TRX\_ID字段)的开销。事务ID只适用于可能执行写操作或锁定读操作(如SELECT…FOR UPDATE)的事务。消除不必要的事务id可以减少每次查询或数据更改语句构造读视图时需要咨询的内部数据结构的大小。

InnoDB在以下情况下检测只读事务:

1.  事务由START transaction READ ONLY语句启动。在这种情况下，试图对数据库(InnoDB, MyISAM，或其他类型的表)进行更改会导致错误，事务继续处于只读状态:

ERROR 1792 (25006): Cannot execute statement in a READ ONLY transaction.

您仍然可以在只读事务中更改特定于会话的临时表，或者对它们发出锁定查询，因为这些更改和锁定对任何其他事务都是不可见的。

1.  自动提交设置是打开的，这样事务就保证是单个语句，组成事务的单个语句是一个“非锁定”SELECT语句。也就是说，不使用FOR UPDATE或LOCK IN SHARED MODE子句的SELECT。
2.  事务启动时没有READ ONLY选项，但是还没有执行显式锁定行的更新或语句。在需要更新或显式锁之前，事务保持只读模式。

因此,报告等操作应用程序生成器,您可以调整一系列InnoDB查询通过分组内启动事务只读和提交,或通过将autocommit设置在运行SELECT语句之前,或者只是通过避免任何数据变化穿插的查询语句。

# 四. 优化InnoDB重做日志 #

考虑以下优化重做日志的指导原则:

1.  让你的重做日志文件变大，甚至和缓冲池一样大。InnoDB写满重做日志文件后，必须在检查点中将缓冲池中修改过的内容写入磁盘。小的重做日志文件会导致很多不必要的磁盘写操作。虽然历史上大的重做日志文件会导致很长的恢复时间，但现在恢复速度快得多，您可以放心地使用大的重做日志文件。
2.  重做日志文件的大小和数量通过innodb\_log\_file\_size和innodb\_log\_files\_in\_group配置选项进行配置。有关修改现有重做日志文件配置的信息，请参见更改重做日志文件的数量或大小。
3.  考虑增加日志缓冲区的大小。大型日志缓冲区使大型事务能够运行，而无需在事务提交之前将日志写入磁盘。因此，如果您有更新、插入或删除许多行的事务，那么使日志缓冲区更大可以节省磁盘I/O。日志缓冲区的大小是通过innodb\_log\_buffer\_size配置选项来配置的，可以在MySQL 8.0中动态配置。
4.  配置innodb\_log\_write\_ahead\_size配置选项，避免“read-on-write”。此选项定义重做日志的预写块大小。设置innodb\_log\_write\_ahead\_size与操作系统或文件系统缓存块大小匹配。当重做日志的预写块大小与操作系统或文件系统缓存块大小不匹配时，就会发生“写时读”。
5.  innodb\_log\_write\_ahead\_size的取值为InnoDB日志块大小(2n)的倍数。最小值为InnoDB日志文件块大小(512)。当指定了最小值时，不会发生预写。最大值等于innodb\_page\_size的值。如果设置的innodb\_log\_write\_ahead\_size大于innodb\_page\_size，则innodb\_log\_write\_ahead\_size会被截断为innodb\_page\_size。
6.  innodb\_log\_write\_ahead\_size相对于操作系统或文件系统缓存块大小设置过低，会导致读写。设置过高的值可能会对日志文件写入的fsync性能有轻微的影响，因为会同时写入多个块。
7.  MySQL 8.0.11引入了专门的日志写线程，用于将重做日志记录从日志缓冲区写到系统缓冲区，并将系统缓冲区刷新到重做日志文件中。以前，单个用户线程负责这些任务。从MySQL 8.0.22开始，你可以使用innodb\_log\_writer\_threads变量来启用或禁用日志写线程。专用日志写入线程可以提高高并发系统的性能，但对于低并发系统，禁用专用日志写入线程可以提供更好的性能。
8.  通过用户线程等待刷新重做来优化spin延迟的使用。旋转延迟有助于减少延迟。在低并发的时期，减少延迟的优先级可能更低，并且在这些时期避免使用旋转延迟可以减少能源消耗。在高并发性期间，您可能希望避免在旋转延迟上耗费处理能力，以便将其用于其他工作。以下系统变量允许设置高水位和低水位值，这些值定义了使用自旋延迟的边界。
9.  innodb\_log\_wait\_for\_flush\_spin\_hwm:定义用户线程在等待刷新重做时不再旋转的最大平均日志刷新时间。缺省值是400微秒。
10. innodb\_log\_spin\_cpu\_abs\_lwm:定义了在等待刷新时用户线程不再旋转的最小CPU占用量。该值表示为CPU核心使用率的总和。例如，“80”的默认值为单个CPU核的80%。在使用多核处理器的系统上，值150表示一个CPU核的100%使用率加上第二个CPU核的50%使用率。
11. innodb\_log\_spin\_cpu\_pct\_hwm:定义在等待刷新时用户线程不再旋转的最大CPU使用率。该值表示为所有CPU核的总处理能力之和的百分比。缺省值是50%。例如，在具有4个CPU核的服务器上，2个CPU核的100%使用率是总CPU处理能力的50%。
12. innodb\_log\_spin\_cpu\_pct\_hwm配置选项尊重处理器亲和性。例如，如果一个服务器有48个核，但是mysqld进程被固定为只有4个CPU核，那么其他44个CPU核将被忽略。

# 五. InnoDB表的批量数据加载 #

当将数据导入InnoDB时，关闭自动提交模式，因为每次插入都会对磁盘执行日志刷新。要在导入操作期间禁用自动提交，请使用SET autocommit和COMMIT语句包围它:

SET autocommit=0;
    ... SQL import statements ...
    COMMIT;

mysqldump选项–opt创建的转储文件可以快速导入到InnoDB表中，即使不使用SET autocommit和COMMIT语句包装它们。

如果你对辅助键有UNIQUE约束，你可以通过在导入会话期间暂时关闭唯一性检查来加快表导入:

SET unique_checks=0;
    ... SQL import statements ...
    SET unique_checks=1;

对于大表，这节省了大量的磁盘I/O，因为InnoDB可以使用它的更改缓冲区来批量写入二级索引记录。确保数据不包含重复的键。

如果你的表中有FOREIGN KEY约束，你可以通过关闭导入会话期间的外键检查来加速表导入:

SET foreign_key_checks=0;
    ... SQL import statements ...
    SET foreign_key_checks=1;

这个技巧适用于任何表的插入，而不仅仅是InnoDB表。

当对具有自动增量列的表进行批量插入时，将innodb\_autoinc\_lock\_mode设置为2(交叉插入)而不是1(连续插入)。

在执行批量插入时，按照PRIMARY KEY顺序插入行更快。InnoDB表使用聚集索引，这使得按PRIMARY KEY的顺序使用数据相对较快。对于不完全适合缓冲池的表，按照PRIMARY KEY顺序执行批量插入特别重要。

为了在InnoDB FULLTEXT索引中加载数据时获得最佳性能，请遵循以下步骤:

1.  在表创建时定义列FTS\_DOC\_ID，类型为BIGINT UNSIGNED NOT NULL，具有唯一索引FTS\_DOC\_ID\_INDEX。例如:

CREATE TABLE t1 (
    FTS_DOC_ID BIGINT unsigned NOT NULL AUTO_INCREMENT,
    title varchar(255) NOT NULL DEFAULT '',
    text mediumtext NOT NULL,
    PRIMARY KEY (`FTS_DOC_ID`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    CREATE UNIQUE INDEX FTS_DOC_ID_INDEX on t1(FTS_DOC_ID);

1.  将数据加载到表中。
2.  加载数据后创建FULLTEXT索引。

# 六. 优化InnoDB查询 #

要调优InnoDB表的查询，请在每个表上创建一组适当的索引。以下是InnoDB索引的指导原则:

1.  因为每个InnoDB表都有一个主键(无论请求与否)，所以为每个表指定一组主键列，这些列将用于最重要和时间紧迫的查询。
2.  不要在主键中指定太多或太长的列，因为这些列值会在每个辅助索引中重复。当索引包含不必要的数据时，读取该数据的I/O和缓存该数据的内存会降低服务器的性能和可伸缩性。
3.  不要为每个列创建单独的辅助索引，因为每个查询只能使用一个索引。很少测试的列或只有几个不同值的列上的索引可能对任何查询都没有帮助。如果对同一个表有许多查询，并测试不同的列组合，则尝试创建少量的连接索引，而不是创建大量的单列索引。如果索引包含结果集所需的所有列(称为覆盖索引)，则查询可能完全可以避免读取表数据。
4.  如果索引列不能包含任何NULL值，在创建表时将其声明为NOT NULL。当优化器知道每个列是否包含NULL值时，它可以更好地确定对查询使用哪个索引最有效。
5.  你可以使用InnoDB只读事务的技术来优化InnoDB表的单查询事务。

# 七. 优化InnoDB DDL操作 #

对表和索引的许多DDL操作(CREATE、ALTER和DROP语句)都可以在线执行。

添加二级索引的在线DDL支持意味着，通过创建没有二级索引的表，然后在数据加载后添加二级索引，通常可以加快创建和加载表及其关联索引的过程。

使用TRUNCATE TABLE清空表，而不是DELETE FROM tbl\_name。外键约束可以使TRUNCATE语句像常规的DELETE语句一样工作，在这种情况下，像DROP TABLE和CREATE TABLE这样的命令序列可能是最快的。

因为主键是不可或缺的每个InnoDB表的存储布局,和改变主键的定义涉及重组整个表,总是设置主键作为CREATE table语句的一部分,和提前计划,这样你不需要改变或删除主键。

# 八. 优化InnoDB磁盘I/O #

如果您遵循SQL操作的数据库设计和调优技术的最佳实践，但由于大量磁盘I/O活动，您的数据库仍然很慢，请考虑这些磁盘I/O优化。如果Unix top工具或Windows任务管理器显示工作负载中的CPU使用率低于70%，则工作负载可能是磁盘绑定的。

1.  增加缓冲池大小  
    当表数据缓存在InnoDB缓冲池中时，可以通过查询重复访问它，而不需要任何磁盘I/O。使用innodb\_buffer\_pool\_size选项指定缓冲池的大小。这个内存区域非常重要，因此通常建议将innodb\_buffer\_pool\_size配置为系统内存的50%到75%。
2.  调整平齐方法  
    在某些版本的GNU/Linux和Unix中，使用Unix的fsync()调用(InnoDB默认使用)和类似的方法将文件刷新到磁盘是非常慢的。如果数据库写性能有问题，可以将innodb\_flush\_method参数设置为O\_DSYNC进行基准测试。
3.  配置fsync阈值  
    默认情况下，当InnoDB创建一个新的数据文件(比如新的日志文件或表空间文件)时，文件在被刷新到磁盘之前会被完全写入到操作系统缓存中，这可能会导致一次发生大量的磁盘写活动。要从操作系统缓存强制更小的、定期的数据刷新，可以使用innodb\_fsync\_threshold变量定义一个阈值(以字节为单位)。当达到字节阈值时，操作系统缓存的内容将被刷新到磁盘。默认值0强制执行默认行为，即只有在将文件完全写入缓存后才将数据刷新到磁盘。  
    在多个MySQL实例使用相同存储设备的情况下，指定一个阈值强制更小的定期刷新可能是有益的。例如，创建一个新的MySQL实例及其相关的数据文件可能会导致磁盘写活动激增，从而阻碍使用相同存储设备的其他MySQL实例的性能。配置阈值有助于避免写入活动的激增。
4.  在Linux上使用带有原生AIO的noop或deadline I/O调度程序  
    InnoDB使用Linux上的异步I/O子系统(本机AIO)来执行对数据文件页的预读和写请求。此行为由innodb\_use\_native\_aio配置选项控制，该配置选项默认启用。对于本机AIO, I/O调度器的类型对I/O性能的影响更大。通常，推荐使用noop和deadline I/O调度器。执行基准测试以确定哪个I/O调度器为您的工作负载和环境提供了最佳结果。
5.  在Solaris 10的x86\_64架构上使用直接I/O  
    在Solaris 10 for x86\_64架构(AMD Opteron)上使用InnoDB存储引擎时，建议对InnoDB相关的文件使用直接I/O，以避免InnoDB性能下降。如果要对整个UFS文件系统使用直接I/O，用于存储innodb相关的文件，请使用forcedirectio选项挂载它;看到mount\_ufs(1米)。(Solaris 10/x86\_64上的默认设置是不使用此选项。)设置innodb\_flush\_method = O\_DIRECT，可以使innodb\_flush\_method只对InnoDB文件进行直接I/O操作，而不对整个文件系统进行直接I/O操作。使用这个设置，InnoDB调用directio()来代替fcntl()来处理I/O到数据文件(而不是I/O到日志文件)。
6.  在Solaris 2.6或更高版本中，为数据和日志文件使用原始存储  
    当使用InnoDB存储引擎,通过innodb\_buffer\_pool\_size价值任何Solaris 2.6的发布,和任何平台(sparc / x86 / x64 / amd64),进行基准InnoDB数据文件和日志文件原始设备或在一个单独的直接I / O UFS文件系统,使用forcedirectio挂载选项如前所述。(如果您希望对日志文件进行直接I/O，那么必须使用挂载选项，而不是设置innodb\_flush\_method。)Veritas文件系统VxFS的用户应该使用convosync=direct mount选项。  
    不要将其他MySQL数据文件(如MyISAM表)放在直接I/O文件系统上。可执行文件或库不能放在直接I/O文件系统上。
7.  使用其他存储设备  
    可以使用其他存储设备来设置RAID配置。  
    InnoDB表空间的数据文件和日志文件也可以放在不同的物理磁盘上。
8.  考虑non-rotational存储  
    非旋转存储通常为随机I/O操作提供更好的性能;以及用于顺序I/O操作的旋转存储。在跨旋转和非旋转存储设备分布数据和日志文件时，请考虑主要在每个文件上执行的I/O操作的类型。

随机的面向I/ o的文件通常包括每个表的文件和一般的表空间数据文件、undo表空间文件和临时表空间文件。顺序I/ o导向的文件包括InnoDB系统表空间文件(由于MySQL 8.0.20之前的doublewrite缓冲和change缓冲)，MySQL 8.0.20引入的doublewrite文件，以及日志文件，如二进制日志文件和重做日志文件。

1.  使用非旋转存储时，请检查以下配置选项的设置:  
    1)innodb\_checksum\_algorithm  
    crc32选项使用更快的校验和算法，推荐用于快速存储系统。  
    2)innodb\_flush\_neighbors  
    为旋转存储设备优化I/O。禁用非旋转存储或混合旋转和非旋转存储。默认关闭。  
    3)innodb\_idle\_flush\_pct  
    允许在空闲期间限制页面刷新，这有助于延长非旋转存储设备的寿命。在MySQL 8.0.18中介绍。  
    4)innodb\_io\_capacity  
    对于低端非旋转存储设备，缺省值200就足够了。对于更高端的总线连接设备，请考虑更高的设置，例如1000。  
    5)innodb\_io\_capacity\_max  
    默认值2000用于使用非旋转存储的工作负载。对于一个高端的，总线连接的非旋转存储设备，考虑一个更高的设置，如2500。  
    6)innodb\_log\_compressed\_pages  
    如果重做日志在非旋转存储上，请考虑禁用此选项以减少日志记录。参见禁用压缩页面的日志记录。  
    7)innodb\_log\_file\_size  
    如果重做日志在非旋转存储上，则配置此选项以最大化缓存和写入组合。  
    8)innodb\_page\_size  
    考虑使用与磁盘内部扇区大小匹配的页大小。早期的SSD设备通常有4KB的扇区大小。一些较新的设备有16KB的扇区大小。InnoDB页面的默认大小是16KB。保持页面大小接近存储设备块大小可以最小化重写到磁盘的未更改数据量。  
    9)binlog\_row\_image  
    如果二进制日志是非旋转存储并且所有表都有主键，请考虑将此选项设置为最小以减少日志记录。

确保为您的操作系统启用TRIM支持。它通常是默认启用的。

1.  增加I/O容量以避免积压  
    如果由于InnoDB检查点操作导致吞吐量周期性下降，可以考虑增加innodb\_io\_capacity配置选项的值。较高的值会导致更频繁的冲洗，避免可能导致吞吐量下降的工作积压。
2.  如果刷新没有落后，则会降低I/O容量  
    如果系统没有在InnoDB刷新操作上落后，可以考虑降低innodb\_io\_capacity配置选项的值。通常情况下，您应该尽可能地降低该选项的值，但不要低到像前面提到的那样导致吞吐量周期性下降。在一个典型的场景中，你可以降低选项的值，你可能会在SHOW ENGINE INNODB STATUS的输出中看到这样的组合:

历史名单长度低，几千以下。  
插入缓冲区合并靠近插入的行。  
缓冲池中修改的页面始终低于缓冲池的innodb\_max\_dirty\_pages\_pct。(在服务器不进行批量插入时进行测量;在批量插入期间，被修改的页面百分比通常会显著上升。)  
日志序号-最后的检查点小于7/8，理想情况下小于InnoDB日志文件总大小的6/8。

1.  在Fusion-io设备上存储系统表空间文件  
    通过将包含doublewrite存储区域的文件存储在支持原子写的Fusion-io设备上，您可以利用doublewrite缓冲区相关的I/O优化。(在MySQL 8.0.20之前，doublewrite缓冲区存储在系统表空间数据文件中。从MySQL 8.0.20开始，存储区域位于doublewrite文件中。参见15.6.4节，“Doublewrite Buffer”。)当doublewrite存储区域文件放置在支持原子写的Fusion-io设备上时，自动关闭doublewrite缓冲区，所有数据文件都使用Fusion-io原子写。该特性仅支持Fusion-io硬件，仅支持Linux下的Fusion-io nvvmfs。为了充分利用这个特性，建议使用O\_DIRECT的innodb\_flush\_method设置。
2.  禁用压缩页面的日志记录  
    当使用InnoDB表压缩特性时，当对压缩数据进行更改时，重新压缩页面的映像会被写入重做日志。这种行为由innodb\_log\_compressed\_pages控制，默认情况下是启用的，以防止在恢复过程中使用不同版本的zlib压缩算法时发生损坏。如果你确定zlib版本不会改变，禁用innodb\_log\_compressed\_pages来减少修改压缩数据的工作负载的重做日志生成。

# 九. 优化InnoDB配置变量 #

不同的设置最适合于具有轻、可预测负载的服务器，而不是始终几乎满负荷运行的服务器，或者经历高活动峰值的服务器。

因为InnoDB存储引擎会自动执行许多优化，所以很多性能调优任务都涉及监控以确保数据库运行良好，以及在性能下降时更改配置选项。

可以执行的主要配置步骤包括:

1.  控制数据更改操作的类型，InnoDB会为这些更改的数据进行缓冲，以避免频繁的小磁盘写操作。参见配置更改缓冲。因为默认是缓冲所有类型的数据更改操作，所以只有在需要减少缓冲量时才更改此设置。
2.  使用innodb\_adaptive\_hash\_index选项打开和关闭自适应散列索引特性。
3.  设置InnoDB进程并发线程数的限制，如果上下文切换是一个瓶颈。innodb\_thread\_concurrency
4.  InnoDB通过预读操作来控制预取的数量。当系统有未使用的I/O容量时，更多的预读可以提高查询的性能。在负载沉重的系统上，过多的预读可能会导致周期性的性能下降。innodb\_read\_ahead\_threshold.
5.  如果您有一个未被默认值充分利用的高端I/O子系统，那么将增加用于读或写操作的后台线程的数量。innodb\_read\_io\_threads and innodb\_write\_io\_threads .
6.  控制InnoDB在后台执行多少I/O。如果您观察到周期性的性能下降，您可以缩减此设置。innodb\_io\_capacity .
7.  控制算法，决定InnoDB何时执行某些类型的后台写操作。该算法适用于某些类型的工作负载，但不适用于其他类型的工作负载，因此如果您观察到周期性的性能下降，可以禁用此特性。innodb\_page\_cleaners .
8.  利用多核处理器及其缓存内存配置，以最小化上下文切换的延迟。innodb\_spin\_wait\_delay .
9.  防止一次性操作(如表扫描)干扰InnoDB缓存中频繁访问的数据。innodb\_old\_blocks\_pct .
10. 将日志文件调整到对可靠性和崩溃恢复有意义的大小。InnoDB的日志文件通常都很小，以避免崩溃后启动时间过长。MySQL 5.5中引入的优化加快了崩溃恢复过程中的某些步骤。特别是，由于改进了内存管理算法，扫描重做日志和应用重做日志的速度更快。如果您人为地保持日志文件的小，以避免长时间的启动时间，那么现在可以考虑增加日志文件的大小，以减少由于重做日志记录回收而产生的I/O。
11. 为InnoDB缓冲池配置实例的大小和数量，这对于具有多gb缓冲池的系统尤为重要。innodb\_buffer\_pool\_instances .
12. 增加并发事务的最大数量，这极大地提高了最繁忙数据库的可伸缩性。innodb\_rollback\_segments .
13. 将清除操作(一种垃圾收集)移动到后台线程中。要有效地度量此设置的结果，请首先调优其他I/ o相关和线程相关的配置设置。innodb\_purge\_threads .
14. 减少InnoDB在并发线程之间的切换量，使繁忙服务器上的SQL操作不会排队并形成“阻塞”。为innodb\_thread\_concurrency选项设置一个值，对于高性能的现代系统，最高设置为32。将innodb\_concurrency\_tickets选项的值增加到5000左右。这种选项的组合为InnoDB进程的线程数量设置了上限，并允许每个线程在被换出之前做大量的工作，这样等待线程的数量保持在较低的水平，操作可以在不过度上下文切换的情况下完成。

# 十. 优化InnoDB系统与许多表 #

如果你配置了非持久性优化器统计数据(非默认配置)，InnoDB会在启动后第一次访问一个表时计算索引基数值，而不是将这些值存储在表中。对于将数据划分到多个表的系统，这个步骤可能会花费大量时间。因为这个开销只适用于初始的表打开操作，为了“预热”一个表以供以后使用，可以在启动后立即访问它，通过发出一个语句，例如SELECT 1 FROM tbl\_name LIMIT 1。

默认情况下，优化器统计信息被持久化到磁盘，这是通过innodb\_stats\_persistent配置选项启用的。

# 参考: #

1.  https://dev.mysql.com/doc/refman/8.0/en/optimizing-innodb.html