主键
当将随机生成的值用作InnoDB表中的主键时，请尽可能在其前面加上一个升值，例如当前日期和时间。当连续的主值在物理上彼此靠近存储时，InnoDB可以更快地插入和检索它们
不要在主键中指定太多或太长的列，因为这些列值在每个辅助索引中都是重复的。当索引包含不必要的数据时，用于读取此数据的I/O和用于对其进行缓存的内存将降低服务器的性能和可伸缩性
数据表
InnoDB最多允许40亿张表
通常，对于大于1TB的表，建议将表划分为多个表空间文件。
MySQL对每个表有4096列的硬限制，但是对于给定的表，有效最大值可能会更少。确切的列限制取决于几个因素：
- 一个表的最大行大小限制了列的数量（可能还有大小），因为所有列的总长度不能超过该大小。MySQL表的内部表示具有65,535字节的最大行大小限制（列设置为TEXT可避免行数限制为65,535字节，并允许操作成功，因为BLOB和TEXT列仅对行大小贡献9到12个字节。）
- 单个列的存储要求限制了给定最大行大小内的列数。某些数据类型的存储要求取决于存储引擎，存储格式和字符集等因素。
- 存储引擎可能会施加其他限制表列计数的限制。例如，InnoDB每个表最多只能有1017列
主要优势
DML操作遵循ACID模型(原子性、一致性、隔离、耐久性)，并具有提交，回滚和 崩溃恢复功能的事务以保护用户数据。
行级锁定和Oracle风格的一致读取可提高多用户并发性和性能。
InnoDB表格将您的数据排列在磁盘上，以优化基于主键的查询 。每个InnoDB表都有一个称为聚集索引的主键索引，用于组织数据以最小化主键查找的I/O
为了保持数据的完整性，InnoDB支持外键约束。使用外键时，将检查插入、更新和删除，以确保它们不会导致不同表之间的不一致。
好处
InnoDB存储引擎有自己的缓冲池，在访问数据时可以在主存中缓存表和索引数据。经常使用的数据直接从内存中处理。此缓存适用于许多类型的信息，并加快处理速度。在专用数据库服务器上，通常会将高达80%的物理内存分配给缓冲池。
当为每个表使用适当的主键列设计数据库时，涉及这些列的操作将自动优化。在WHERE子句、ORDER BY子句、GROUP BY子句和join操作中引用主键列非常快
插入、更新和删除由一种称为更改缓冲的自动机制进行优化。InnoDB不仅允许对同一个表并发读和写访问，它还缓存修改后的数据以简化磁盘I/O。
对于BLOB和logtext字段使用DYNAMIC行格式的表数据的存储布局更为有效 。
性能收益不仅限于具有长期运行查询的巨型表。当从表中一遍又一遍地访问相同的行时，称为“自适应哈希索引”的功能将使这些查找变得更快，就像它们是从哈希表中出来一样。
如果服务器由于硬件或软件问题而意外退出，无论当时数据库中发生了什么，在重新启动数据库后都不需要执行任何特殊操作。InnoDB崩溃恢复自动结束崩溃前提交的所有更改，并撤销所有正在进行但未提交的更改。只需重新启动，然后从上次中断的地方继续即可。
最佳实践
不使用LOCK TABLES语句。InnoDB可以同时处理对同一个表进行读写的多个会话，而不会牺牲可靠性或高性能。要获得对一组行的排他性写访问权限，使用SELECT ... FOR UPDATE语法仅锁定要更新的行。
启用innodb_file_per_table或使用常规表空间将表的数据和索引放入单独的文件中，而不是系统表空间中。默认启用。
评估你的数据和访问模式是否受益于InnoDB表或页面压缩特性。你可以压缩InnoDB表而不牺牲读/写能力。
缓冲池LRU算法
使用最近最少使用（LRU）算法的变体，将缓冲池作为列表进行管理。当需要空间以将新页面添加到缓冲池时，将驱逐最近使用最少的页面，并将新页面添加到列表的中间。
该算法将常用页面保留在新的子列表中。旧的子列表包含不常用的页面；这些页面是驱逐的候选对象
可以控制LRU列表中的插入点，并选择是否对InnoDB表或索引扫描带入缓冲池的块应用相同的优化
配置参数innodb_old_blocks_pct控制LRU列表中“ old ”块的百分比。innodb_old_blocks_pct的默认值是37，对应于3/8原固定比率。值的范围是5（缓冲池中的新页面很快老化）到95 （仅5％的缓冲池留给热页面使用，这使得该算法接近于熟悉的LRU策略）。
防止缓冲池被预读搅动的优化可以避免由于表或索引扫描而引起的类似问题。在这些扫描中，通常快速连续地访问数据页面几次，而再也不会被访问。配置参数 innodb_old_blocks_time 指定第一次访问页面后的时间窗口（以毫秒为单位），在该时间窗口内可以访问页面而无需将其移到LRU列表的最前面（最近使用的末尾）。默认值 innodb_old_blocks_time是 1000。增大此值会使越来越多的块从缓冲池中更快地老化。
监视缓冲池,提供有关缓冲池操作的度量
SHOW ENGINE INNODB STATUS
更改缓冲区
更改缓冲区是一种特殊的数据结构，当二级索引页不在缓冲池中时，它们会缓存这些更改。当页面通过其他读取操作加载到缓冲池中时，可能由INSERT， UPDATE或 DELETE操作（DML）导致的缓冲更改将在以后合并。
与聚簇索引不同，二级索引通常是不唯一的，并且二级索引中的插入以相对随机的顺序发生。
监视变更缓冲区：
SHOW ENGINE INNODB STATUS\G
自适应哈希索引
自适应哈希索引功能可以在不牺牲InnoDB事务功能或可靠性的情况下，在工作负载和缓冲池有足够内存的适当组合系统上，更像是内存数据库。自适应哈希索引功能由innodb_adaptive_hash_index变量启用 默认为on
哈希索引是根据对经常访问的索引页面的需求而建立的。
如果表几乎完全适合主内存，则哈希索引可以通过启用直接查找任何元素的功能来加速查询，从而将索引值转换为某种指针。InnoDB具有监视索引搜索的机制。如果InnoDB发现查询可以从构建哈希索引中受益，它会自动这样做。
在某些工作负载下，哈希索引查找的速度大大超过了监视索引查找和维护哈希索引结构的额外工作。在繁重的工作负载（例如多个并发连接）下，访问自适应哈希索引有时可能会成为争用的源。LIKE运算符和%通配符也往往不会受益。对于无法从自适应哈希索引功能中受益的工作负载，将其关闭可减少不必要的性能开销。由于很难预先预测自适应哈希索引功能是否适合特定的系统和工作负载，因此请考虑启用和禁用该功能时运行基准测试，MySQL 5.6中的体系结构更改使它更适合禁用自适应哈希索引功能。
在MySQL 5.7中，自适应哈希索引功能已分区。每个索引都绑定到一个特定的分区，并且每个分区都受到单独的锁存器的保护。分区由innodb_adaptive_hash_index_parts变量控制 。在较早的版本中，自适应哈希索引功能受单个闩锁的保护，这可能成为繁重工作负载下的争用点。innodb_adaptive_hash_index_parts变量默认设置为8。最大设置为512。
您可以在SHOW ENGINE INNODB STATUS输出的SEMAPHORES部分中 监视自适应哈希索引的使用和争用。如果在中创建的RW锁上有许多线程在等待btr0sea.c，请考虑增加自适应哈希索引分区的数量或禁用自适应哈希索引功能。
日志缓冲区
日志缓冲区是存储区域，用于保存要写入磁盘上的日志文件的数据。日志缓冲区大小由innodb_log_buffer_size变量定义 ,默认大小为16MB。日志缓冲区的内容定期刷新到磁盘。较大的日志缓冲区使大型事务可以运行，而无需在事务提交之前将重做日志数据写入磁盘。因此，如果您有更新，插入或删除许多行的事务，则增加日志缓冲区的大小可以节省磁盘I / O。
表
一个InnoDB表及其索引可以创建在系统表空间，也可以在每个表文件表空间中创建，也可以在一般表空间中创建。当innodb_file_per_table启用（默认）将在每表空间中创建表，否则创建在系统表空间
MySQL将表的数据字典信息存储在数据库目录中的 .frm文件中。与其他MySQL存储引擎不同， InnoDB它还在系统表空间内的自身内部数据字典中编码有关表的信息。MySQL删除表或数据库时，将删除一个或多个.frm文件以及InnoDB数据字典中的相应条目。不能仅通过移动.frm 文件在数据库之间移动表。
创建InnoDB表时，MySQL在MySQL数据目录下的数据库目录中创建一个.frm文件。对于在每表空间中创建的表，默认情况下，MySQL还在数据库目录中创建一个.ibd表空间文件。
在外部目录中创建表
原因
空间管理，I/O优化或将表放置在具有特定性能或容量特性的存储设备上。
CREATE TABLE t1 (c1 INT PRIMARY KEY) DATA DIRECTORY = '/external/directory';
DATA DIRECTORY在每个表文件表空间中创建的表时支持该子句；请确保您选择的目录位置，因为DATA DIRECTORY子句的位置不能用ALTER TABLE更改。
使用DATA DIRECTORY时，将在指定目录下创建表的数据文件，并在MySQL数据目录下创建一个包含数据文件路局的.isl文件（功能上与符号链接相似）
当源和副本位于同一主机上的复制环境中，不支持该子句。因为复制路径将导致源和副本在同一位置创建表。
在常规表空间外部目录创建表
仅在每个表文件表空间中创建的表支持此方法。
mysql> CREATE TABLE t2 (c1 INT PRIMARY KEY) TABLESPACE = innodb_file_per_table
DATA DIRECTORY = '/external/directory';
移动复制表
要将二进制格式的数据库从Unix移到Windows或从Windows移到Unix，请使用小写名称创建所有数据库和表，在my.cnf中添加 lower_case_table_names=1
复制数据文件（冷备份方法）
当您移动或复制每个表文件的.ibd文件时，源系统和目标系统上的数据库目录名称必须相同。存储在InnoDB共享表空间中的表定义包括数据库名。存储在表空间文件中的事务id和日志序列号在数据库之间也有所不同
要将.ibd文件和关联的表从一个数据库移动到另一个数据库，请使用以下RENAME TABLE语句：
RENAME TABLE db1.tbl_name TO db2.tbl_name;
如果您有“干净”备份的 .ibd文件，则可以将其还原到其原始来源的MySQL安装中，如下所示：
自复制.ibd文件以来，该表一定不能删除或截断，因为这样做会更改存储在表空间中的表ID。ALTER TABLE ... IMPORT TABLESPACE功能不对导入的数据施加外键约束
删除当前.ibd文件：
ALTER TABLE tbl_name DISCARD TABLESPACE;
将备份.ibd文件复制到正确的数据库目录。
告诉InnoDB您将新.ibd文件用于表：
ALTER TABLE tbl_name IMPORT TABLESPACE;
"干净"备份的要求：
.ibd文件中没有未提交修改的事务。
.ibd文件中没有未合并的插入缓冲区条目。
清除已从.ibd文件中删除所有删除标记的索引记录。
mysqld已将.ibd文件的所有修改页从缓冲池刷新到文件。
制作"干净"备份的方法
停止mysqld服务器上的所有活动并提交所有事务。
等待直到SHOW ENGINE INNODB STATUS显示数据库中没有活动的事务，并且主线程状态为Waiting for server activity。然后，您可以复制该 .ibd文件。
双写缓冲区
doublewrite缓冲区是一个存储区域，在该区域中，InnoDB在将页面写到InnoDB数据文件中的适当位置之前，先从缓冲池中刷新页面 。如果在页面写入过程中存在操作系统，存储子系统或mysqld进程意外退出，则InnoDB可以在崩溃恢复期间从doublewrite缓冲区中找到页面的良好副本。
尽管数据被写入两次，但双写缓冲区并不需要两倍的I/O开销或两倍的I/O操作。只需一次调用操作系统fsync()即可将数据按较大的顺序块写入doublewrite缓冲区
重做日志（REDO_LOG）
重做日志是基于磁盘的数据结构，在崩溃恢复期间用于纠正不完整事务写入的数据。
默认情况下，重做日志在磁盘上由名为ib_logfile0和ib_logfile1的两个文件物理表示。
要更改InnoDB重做日志文件的数量或大小，请执行以下步骤：
- 停止MySQL服务器，并确保它关闭且没有错误。
- 编辑my.cnf。innodb_log_file_size更改日志文件的大小，innodb_log_files_in_group增加日志文件的数量。
- 再次启动MySQL服务器
- 如果InnoDB检测到innodb_log_file_size与重做日志文件大小不同，它将编写日志检查点，关闭并删除旧的日志文件，以请求的大小创建新的日志文件，然后打开新的日志文件。
InnoDB像任何其他符合ACID的数据库引擎一样，在提交事务之前刷新事务的重做日志。InnoDB使用组提交功能将多个此类刷新请求分组在一起，以避免每次提交都进行一次刷新。使用组提交， InnoDB可以对日志文件进行一次写入操作，以对大约同时提交的多个用户事务执行提交操作，从而显着提高了吞吐量。
让你的重做日志文件变大，甚至和缓冲池一样大。当InnoDB写满重做日志文件时，它必须将缓冲池中修改的内容写到检查点的磁盘上。小的重做日志文件会导致很多不必要的磁盘写操作。虽然历史上大的重做日志文件会导致较长的恢复时间，但现在恢复速度快得多，您可以放心地使用大的重做日志文件
如果您有更新，插入或删除许多行的事务，则增加日志缓冲区可以节省磁盘I/O
配置
重做日志所在目录，默认在数据目录中创建两个5M的ib_logfile0和ib_logfile1文件
innodb_log_group_home_dir = /my_redo_log_dir/
重做日志组中的日志文件数,默认值为2
innodb_log_files_in_group
重做日志组中每个日志文件的大小（以字节为单位）默认为48MB；
值越大，缓冲池中需要的检查点刷新活动越少，从而节省了磁盘I/O；
日志组的总大小（innodb_log_file_size* innodb_log_files_in_group）不能超过512GB；
innodb_log_file_size
撤销日志（undo_log）
撤消日志是与单个读写事务关联的撤消日志记录的集合。撤消日志记录包含有关如何撤消事务对聚集索引记录的最新更改的信息。如果另一个事务需要在一致的读操作中查看原始数据，则将从撤消日志记录中检索未修改的数据。
撤消日志存在于撤消日志段中，而撤消日志段包含在回滚段中。回滚段驻留在系统表空间、undo表空间和临时表空间中。
驻留在临时表空间中的撤消日志用于修改用户定义的临时表中的数据的事务。这些撤消日志不是重做日志，因为崩溃恢复不需要它们。它们仅在服务器运行时用于回滚。这种类型的撤消日志通过避免重做日志I/O来提高性能。
InnoDB最多支持128个回滚段，其中32个分配给临时表空间。剩下的96个回滚段可以分配给修改常规表中数据的事务。innodb_rollback_segments变量定义使用回滚段的数量。
回滚段中撤消插槽的数量根据InnoDB页面大小而有所不同（为页面大小/16）
一个事务最多可以分配四个撤消日志，以下每种操作类型都可以分配一个：
用户定义表上的INSERT操作
用户定义表上的UPDATE和DELETE操作
用户定义的临时表上INSERT的操作
用户定义的临时表上UPDATE和DELETE的操作
配置
撤消日志表空间的路径，默认在系统表空间
innodb_undo_directory
撤消日志回滚段数量，默认128
innodb_rollback_segments
导入inndo表
先决条件
必须启用innodb_file_per_table，默认启用。
表空间的页面大小必须与目标MySQL服务器实例的页面大小匹配。 InnoDB页面大小由innodb_page_size定义，该变量在初始化MySQL服务器实例时配置。
如果表具有外键关系，则必须在执行DISCARD TABLESPACE之前禁用foreign_key_checks。另外应在同一逻辑时间点导出所有与外键相关的表，因为ALTER TABLE ... IMPORT TABLESPACE不会对导入的数据施加外键约束。为此，请停止更新相关表，提交所有事务，获取表上的共享锁，然后执行导出操作。
从另一个MySQL服务器实例导入表时，两个MySQL服务器实例都必须具有通用状态（GA），并且必须具有相同的版本。否则，必须在要导入表的同一MySQL服务器实例上创建表。
如果表是通过CREATE TABLE……DATA DIRECTORY语句在外部目录创建的，则在目标实例上替换的表必须使用相同的DATA DIRECTORY子句定义。如果子句不匹配，则报告架构不匹配错误。
如果未在表定义中明确定义ROW_FORMAT或使用ROW_FORMAT=DEFAULT，则源实例和目标实例上的innodb_default_row_format设置必须相同。否则，当您尝试导入操作时，将报告架构不匹配错误。
示例
在目标实例上
创建一个与要导入的表具有相同定义的表。
mysql> USE test;
mysql> CREATE TABLE t1 (c1 INT) ENGINE=INNODB;
在导入之前必须丢弃刚创建的表的表空间。
mysql> ALTER TABLE t1 DISCARD TABLESPACE;
在源实例上
运行 FLUSH TABLES ... FOR EXPORT以静默要导入的表。此时表上仅允许只读事务。
mysql> USE test;
mysql> FLUSH TABLES t1 FOR EXPORT;
FLUSH TABLES ... FOR EXPORT确保对命名表的更改已刷新到磁盘，以便可以在服务器运行时进行二进制表副本的复制。
当FLUSH TABLES ... FOR EXPORT运行时，InnoDB生成表的.cfg架构目录元数据文件。该.cfg文件包含在导入操作期间用于架构验证的元数据。
将.ibd文件和.cfg元数据文件从源实例复制到目标实例，如果从加密的表空间导入表，InnoDB会生成一个.cfp文件和一个.cfg元数据文件。必须将.cfp文件与.cfg文件一起复制到目标实例 .cfp文件包含一个传输密钥和一个加密的表空间密钥。在导入时，InnoDB使用传输密钥来解密表空间密钥
shell> scp /path/to/datadir/test/t1.{ibd,cfg} destination-server:/path/to/datadir/test
复制后，使用UNLOCK TABLES释放锁
mysql> UNLOCK TABLES;
在目标实例上，导入表空间
mysql> USE test;
mysql> ALTER TABLE t1 IMPORT TABLESPACE;
导入分区表示例
在目标实例上
使用与要导入的分区表相同的定义创建一个分区表。
mysql> USE test;
mysql>CREATE TABLE t1 (i int) ENGINE = InnoDB PARTITION BY KEY (i) PARTITIONS 3;
在数据目录中，每个分区都有一个表空间.ibd文件。
mysql> \! ls /path/to/datadir/test/
db.opt t1.frm t1#P#p0.ibd t1#P#p1.ibd t1#P#p2.ibd
在导入前必须丢弃分区表的表空间
mysql> ALTER TABLE t1 DISCARD TABLESPACE;
丢弃后数据目录只剩下
mysql> \! ls /path/to/datadir/test/
db.opt t1.frm
在源示例上，运行 FLUSH TABLES ... FOR EXPORT以静默要导入的分区表，FLUSH TABLES ... FOR EXPORT只能在表上运行，不能在单个表分区上运行，FLUSH TABLES ... FOR EXPORT会为每个分区生成一个.cfg文件
mysql> USE test;
mysql>FLUSH TABLES t1 FOR EXPORT;
将.ibd和 .cfg文件从源实例架构目录复制到目标实例架构目录。
shell>scp /path/to/datadir/test/t1*.{ibd,cfg} destination-server:/path/to/datadir/test
复制后使用UNLOCK TABLES释放锁
mysql>UNLOCK TABLES;
在目标实例上，导入分区表的表空间：
mysql> USE test;
mysql>ALTER TABLE t1 IMPORT TABLESPACE;
导入表分区示例
导入了表的四个分区中的两个分区（p2 和p3）
在目标实例上
创建与您要从中导入分区的分区表定义相同的分区表
mysql> USE test;
mysql>CREATE TABLE t1 (i int) ENGINE = InnoDB PARTITION BY KEY (i) PARTITIONS 4;
在数据目录中， 每个分区都有一个表空间文件
mysql> \! ls /path/to/datadir/test/
db.opt t1.frm t1#P#p0.ibd t1#P#p1.ibd t1#P#p2.ibd t1#P#p3.ibd
导入前必须丢弃要从源实例导入的分区，丢弃后两个分区的.idb文件将被删除；在子分区表上运行，分区和子分区表名都允许。当指定分区名称时，该分区的子分区将包括在操作中。
mysql> ALTER TABLE t1 DISCARD PARTITION p2, p3 TABLESPACE;
在源实例上
运行 FLUSH TABLES ... FOR EXPORT以静默分区表
mysql> USE test;
mysql>FLUSH TABLES t1 FOR EXPORT;
将用于分区p2和分区p3的.ibd和.cfg文件从源实例架构目录复制到目标实例架构目录
shell> scp t1#P#p2.ibd t1#P#p2.cfg t1#P#p3.ibd t1#P#p3.cfg destination-server:/path/to/datadir/test
复制后使用UNLOCK TABLES释放锁
mysql>UNLOCK TABLES;
在目标实例上，导入分区表的表空间：
mysql> USE test;
mysql>ALTER TABLE t1 IMPORT PARTITION p2, p3 TABLESPACE;
局限性
仅支持每个表表空间的表。驻留在系统表空间或常规表空间中的表不支持此功能。共享表空间中的表不能被静默。
FLUSH TABLES ... FOR EXPORT不支持带有FULLTEXT索引的表，因为无法刷新全文搜索辅助表。导入带有FULLTEXT索引的表后，运行OPTIMIZE TABLE以重建FULLTEXT索引。或者，在导出操作之前删除FULLTEXT索引，并在目标实例上导入表后重新创建索引。
由于.cfg元数据文件的限制，在导入分区表时，不会针对分区类型或分区定义差异报告架构不匹配。报告列差异
ALTER TABLE ... IMPORT TABLESPACE不需要.cfg元数据文件来导入表。但是，不带.cfg文件导入时不会执行元数据检查，会发出警告
缓冲池
配置
缓冲池是用于保存InnoDB表，索引和其他辅助缓冲区的缓存数据的内存区域。建议设置为系统内容的50%-75%，默认为128M。可以在服务运行时修改
在32位GNU/Linux x86上，如果分配给mysqld进程的全局和每个线程缓冲区和缓存的内存接近或超过2GB，则存在风险
innodb_buffer_pool_size
缓冲池数量，可以设置多个来提高并发性，默认为1。可以在服务运行时修改
byinnodb_buffer_pool_instances
配置InnoDB缓冲池块大小，默认值128MB
innodb_buffer_pool_chunk_size
innodb_buffer_pool_size必须始终等于innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances，否则innodb_buffer_pool_size将自动调整为innodb_buffer_pool_chunk_size*innodb_page_cleaners则会自动设置为与innodb_buffer_pool_instances相同的值。
为避免潜在的性能问题，innodb_buffer_pool_size/ innodb_buffer_pool_chunk_size的数量不应超过1000
定义写入磁盘上的日志文件的缓冲区的大小（以字节为单位）。默认大小为16MB。较大的日志缓冲区使大型事务可以运行，而无需在事务提交之前将日志写入磁盘。
innodb_log_buffer_size
计算MySQL的全局和每个线程的内存分配，估算MySQL的内存使用情况
innodb_buffer_pool_size + key_buffer_size + max_connections*(sort_buffer_size + read_buffer_size + binlog_cache_size) + max_connections*2MB
除插入操作外缓冲池还缓冲更新和删除操作
innodb_change_buffering=all
监控缓冲池调整大小进度
SHOW STATUS WHERE Variable_name='InnoDB_buffer_pool_resize_status';
对于缓冲池在数千兆字节范围内的系统，通过减少不同线程读写缓存页面的争用，将缓冲池划分为多个单独的实例可以提高并发性。
此功能通常用于缓冲池大小在数GB范围内的系统。使用innodb_buffer_pool_instances置了多个缓冲池实例 ，也可以调整该 nnodb_buffer_pool_size值。
要启用多个缓冲池实例，请将innodb_buffer_pool_instances配置选项设置为 大于1（默认）的值，最大为64（最大）。仅当您将innodb_buffer_pool_size大小设置为1GB或更大时，此选项才生效 。
您指定的总大小在所有缓冲池之间分配。为了获得最佳效率，指定的组合innodb_buffer_pool_instances和innodb_buffer_pool_size，使得每个缓冲池实例是至少为1GB
缓冲池预读
一个预读请求是缓冲池请求预取多个页面的异步I/O，期待这些网页很快就需要。这些请求将所有页面集中到一个范围。 InnoDB使用两种预读算法来提高I/O性能：
- 线性预读
- 该技术根据缓冲池中顺序访问的页面来预测很快可能需要哪些页面。可以使用配置参数innodb_read_ahead_threshold调整触发异步读取请求所需的顺序页面访问次数来控制何时执行预读操作。在添加此参数之前，InnoDB仅在读取当前扩展区的最后一页时，才计算是否对整个下一个扩展区发出异步预取请求。
- innodb_read_ahead_threshold控制InnoDB检测顺序页面访问模式时的敏感度。如果从扩展区顺序读取的页面数大于或等于innodb_read_ahead_threshold，则 InnoDB启动整个后续扩展区的异步预读操作。
- 随机预读
- 它根据缓冲池中已有的页面来预测何时可能需要使用页面，而不管这些页面的读取顺序如何。如果在缓冲池中找到相同范围的13个连续页面，则InnoDB异步发出请求以预取该范围的其余页面。要启用此功能，请将配置变量innodb_random_read_ahead为ON
缓冲池刷新
InnoDB在后台执行某些任务，包括从缓冲池中清除脏页。脏页是指已被修改但尚未写入磁盘上数据文件的那些页。
在MySQL 5.7中，缓冲池刷新是由页面清理程序线程执行的。页面清理程序线程的数量由innodb_page_cleaners变量控制，默认值为4。但是，如果页面清理程序线程的数量超过缓冲池实例的数量，innodb_page_cleaners则会自动设置为与innodb_buffer_pool_instances相同的值。
当脏页的百分比达到innodb_max_dirty_pages_pct_lwm变量定义的低水位标记值时，将启动缓冲池刷新。默认的低水位标记为0，这会它禁止这种早期冲洗行为。
内存分配器
InnoDB通过在MySQL配置文件中设置innodb_use_sys_malloc的值，可以控制使用自己的内存分配器还是操作系统的分配器。如果设置为ON或 1（默认值），则InnoDB使用基础系统的malloc和free功能，而不是管理内存池本身。该参数不是动态的，仅在系统启动时才生效。要使用InnoDB内存分配器，请设置 innodb_use_sys_malloc为0。
线程并发
设置innodb_thread_concurrency配置参数来限制并发线程数。一旦执行线程的数量达到此限制，其他线程就会休眠数微秒（由参数innodb_thread_sleep_delay设置），然后再放入队列。
以前，需要进行实验以找到innodb_thread_sleep_delay的最佳值，并且该最佳值可能会根据工作负载而变化。在MySQL 5.6.3及更高版本中，您可以将配置选项innodb_adaptive_max_sleep_delay和innodb_thread_sleep_delay设置为允许的最大值，InnoDB根据当前线程调度活动自动将innodb_thread_sleep_delay向上或向下调整 。这种动态调整有助于线程调度机制在系统轻负载和接近满负荷运行时平稳运行。
当新的SQL语句启动时，线程没有票证，并且必须遵守innodb_thread_concurrency。线程有权进入InnoDB后，将为其分配许多票证，可将其用于后续进入InnoDB以执行行操作。
如果票证用完，线程将被逐出，并innodb_thread_concurrency再次被观察到，这可能会将线程放回到等待线程的先进先出队列中。当线程再次有权进入时InnoDB，将再次分配票证。
分配的票证数量由全局选项指定innodb_concurrency_tickets，默认情况下为5000。一旦锁可用，等待锁的线程将获得一张票证。
I/O线程
InnoDB使用后台线程来处理各种类型的I/O请求。可以使用innodb_read_io_threads和innodb_write_io_threads配置参数配置服务于数据页上的I/O读写的后台线程数。这些参数分别表示用于读取和写入请求的后台线程数。它们在所有受支持的平台上均有效。值不能动态更改。这些参数的默认值为4，允许值的范围为1-64。
这些配置选项的目的是使InnoDB在高端系统上更具可伸缩性。
每个后台线程最多可以处理256个挂起的I/O请求。后台I/O的一个主要来源是预读请求。InnoDB尝试以大多数后台线程均等地共享工作的方式平衡传入请求的负载。InnoDB还尝试将读取请求从相同的范围分配到同一线程，以增加合并请求的机会。如果您具有高端I/O子系统，并且innodb_read_io_threads在SHOW ENGINE INNODB STATUS输出中看到超过64×待处理的读取请求，则可以通过增加innodb_read_io_threads的值来提高性能。
I/O容量
innodb_io_capacity定义提供给InnoDB整体的I/O容量。应该将其设置为大约系统每秒可以执行的I/O操作数（IOPS）。当innodb_io_capacity被设置时，InnoDB会根据这个设置值来估计后台任务可用的I/O带宽
可以将innodb_io_capacity值设置为100或更大。默认值为200。通常，大约100的值适用于消费级存储设备，例如最高7200RPM的硬盘驱动器。
通常，建议不要使用大于20000的值，除非您确定较低的值不足以满足您的工作量。
innodb_io_capacity_max不能将其设置为小于innodb_io_capacity值的值。
innodb_io_capacity_max默认值为innodb_io_capacity值的两倍，最小值为2000
Linux处理异步I/O
InnoDB在Linux上使用异步I/O子系统（本机AIO）对数据文件页面执行预读和写请求。此行为由innodb_use_native_aio配置选项控制，该选项仅适用于Linux系统，并且默认情况下处于启用状态。在其他类似Unix的系统上， InnoDB仅使用同步I/O。历史上， InnoDB仅在Windows系统上使用异步I/O。在Linux上使用异步I/O子系统需要该libaio库
清除配置
InnoDB使用SQL语句删除行时，不会立即从数据库中物理删除行。仅当InnoDB丢弃为删除而编写的撤消日志记录时，才物理删除行及其索引记录。这种删除操作仅发生在不再需要多版本并发控制（MVCC）或回滚的行之后，这称为清除。
清除将定期运行
清除操作由一个或多个清除线程在后台执行。清除线程的数量由innodb_purge_threads变量控制。缺省值为4。如果DML操作集中在单个表或几个表上，请将设置保持较低，以使线程彼此之间不争用访问繁忙的表。如果DML操作分散在许多表中，请增加设置。清除线程的最大数量为32。
innodb_purge_batch_size变量定义从历史记录列表中批量清除解析和处理的撤消日志页面的数量。默认值是300
索引页合并阈值
MERGE_THRESHOLD默认值为50，这是以前的硬编码值。最小值为1，最大值为50。
当索引页面的“页面已满”百分比降至默认MERGE_THRESHOLD设置的50％以下时，InnoDB尝试将索引页面与相邻页面合并。如果两个页面都接近50％充满，合并页面后很快就会发生页面拆分。如果此合并拆分行为频繁发生，则可能会对性能产生不利影响。为了避免频繁的合并拆分，可以降低该MERGE_THRESHOLD值，以便InnoDB尝试以较低的“页面已满”百分比进行页面合并 。以较低的页面占满百分比合并页面会在索引页面中留出更多空间，并有助于减少合并分割行为。
优化器
持久性优化器统计信息在服务器重新启动后会保留下来，从而提高了计划的稳定性和查询性能的一致性。持久性优化器统计信息还具有以下额外好处，从而提供了控制和灵活性：
- 使用 innodb_stats_auto_recalc 配置选项来控制在对表进行实质性更改后是否自动更新统计信息
- 将STATS_PERSISTENT，STATS_AUTO_RECALC和STATS_SAMPLE_PAGES子句与CREATE TABLE和ALTER TABLE语句一起使用，以配置各个表的优化器统计信息。
- 在mysql.innodb_table_stats和 mysql.innodb_index_stats表中查询优化器统计数据 。
- 查看mysql.innodb_table_stats和mysql.innodb_index_stats表的last_update列，以查看统计信息的最新更新时间。
- 手动修改mysql.innodb_table_stats和mysql.innodb_index_stats表，以强制执行特定的查询优化计划或测试替代计划，而无需修改数据库
默认情况下启用了持久性优化器统计信息功能（innodb_stats_persistent=ON）。
非持久性优化器统计信息将在每次服务器重新启动时以及执行某些其他操作后清除，并在下次访问表时重新计算。因此，在重新计算统计信息时可能产生不同的估计，从而导致执行计划的不同选择和查询性能的变化
性能模式
查看相关工具
SELECT * FROM performance_schema.setup_instruments WHERE NAME LIKE '%innodb%';
监视InnoDB表的ALTER TABLE进度
按照发生的顺序，ALTER TABLE 阶段事件包括
- stage/innodb/alter table (read PK and internal sort)：处于ALTER TABLE读取主键阶段时，此阶段处于活动状态。它从WORK_COMPLETED=0开始，WORK_ESTIMATED设置为主键中的估计页数。阶段完成后，WORK_ESTIMATED将更新为主键中的实际页面数。
- stage/innodb/alter table (merge sort)：对于该ALTER TABLE操作添加的每个索引都会重复此阶段。
- stage/innodb/alter table (insert)：对于该ALTER TABLE操作添加的每个索引重复此阶段。
- stage/innodb/alter table (log apply index)：此阶段包括ALTER TABLE运行时生成的DML日志的应用程序。
- stage/innodb/alter table (flush)：在此阶段开始之前，WORK_ESTIMATED根据刷新列表的长度更新为更精确的估计。
- stage/innodb/alter table (log apply table)：此阶段包括ALTER TABLE在运行时生成的并发DML日志的应用程序。此阶段的持续时间取决于表更改的程度。如果未在表上运行任何并发DML，则此阶段是即时的。
- stage/innodb/alter table (end)：包括刷新阶段之后出现的所有剩余工作，例如重新应用ALTER TABLE运行时在表上执行的DML。
监视InnoDB表的ALTER TABLE示例
启用stage/innodb/alter%工具
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME LIKE 'stage/innodb/alter%';
启用事件使用者表，其中包括events_stages_current，events_stages_history和events_stages_history_long
UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE '%stages%';
运行一个ALTER TABLE操作
ALTER TABLE employees.employees ADD COLUMN middle_name varchar(14) AFTER first_name;
通过查询性能架构events_stages_current表来检查ALTER TABLE操作的进度。显示的阶段事件根据ALTER TABLE当前正在进行的阶段而有所不同。WORK_COMPLETED列显示已完成的工作。WORK_ESTIMATED列提供了剩余工作的估计
SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED FROM performance_schema.events_stages_current;
+------------------------------------------------------+----------------+----------------+
| EVENT_NAME | WORK_COMPLETED | WORK_ESTIMATED |
+------------------------------------------------------+----------------+----------------+
| stage/innodb/alter table (read PK and internal sort) | 280 | 1245 |
+------------------------------------------------------+----------------+----------------+
如果ALTER TABLE操作完成，events_stages_current表将返回一个空集。在这种情况下，您可以检查events_stages_history表以查看事件数据以了解完成的操作
在ALTER TABLE处理过程中修改了WORK_ESTIMATED值。初始阶段完成之后的估计工时为1213。ALTER TABLE处理完成后，将WORK_ESTIMATED其设置为实际值1981
SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED FROM performance_schema.events_stages_history;
+------------------------------------------------------+----------------+----------------+
| EVENT_NAME | WORK_COMPLETED | WORK_ESTIMATED |
+------------------------------------------------------+----------------+----------------+
| stage/innodb/alter table (read PK and internal sort) | 886 | 1213 |
| stage/innodb/alter table (flush) | 1213 | 1213 |
| stage/innodb/alter table (log apply table) | 1597 | 1597 |
| stage/innodb/alter table (end) | 1597 | 1597 |
| stage/innodb/alter table (log apply table) | 1981 | 1981 |
+------------------------------------------------------+----------------+----------------+
监视InnoDB Mutex等待
互斥锁是代码中使用的一种同步机制，用于强制在给定时间只有一个线程可以访问公共资源。当服务器中执行的两个或多个线程需要访问同一资源时，这些线程会相互竞争。在互斥锁上获得锁的第一个线程导致其他线程等待，直到锁被释放。对于InnoDB已检测到的互斥锁，可以使用Performance Schema监视互斥锁等待
查看互斥锁工具
SELECT * FROM performance_schema.setup_instruments WHERE NAME LIKE '%wait/synch/mutex/innodb%';
一些InnoDB互斥锁实例是在服务器启动时创建的，只有在服务器启动时也启用了关联的工具的情况下，才进行检测。为确保InnoDB已检测并启用所有互斥体实例，请将以下performance-schema-instrument添加到您的MySQL配置文件中
performance-schema-instrument='wait/synch/mutex/innodb/%=ON'
如果不需要所有InnoDB互斥锁的等待事件数据，则可以通过向MySQL配置文件添加performance-schema-instrument的其他规则来禁用特定工具。例如，要禁用InnoDB与全文搜索相关的互斥锁等待事件工具，请添加以下规则
performance-schema-instrument='wait/synch/mutex/innodb/fts%=OFF'
INFORMATION_SCHEMA库
三个表监视事务并诊断潜在的锁定问题
INNODB_TRX：提供有关当前内部正在执行的每个事务的信息，包括事务状态（例如，它是正在运行还是正在等待锁），事务何时开始以及事务正在执行的SQL语句。
INNODB_LOCKS：InnoDB中等待另一个事务释放锁（INNODB_TRX.TRX_STATE is LOCK WAIT）的每个事务正好被一个阻塞锁定请求阻塞。该阻塞锁请求是针对另一事务以不兼容模式持有的行锁或表锁。阻止事务的锁始终处于与请求的锁模式不兼容的模式（读与写，共享与排他）。在其他事务提交或回滚之前，被阻塞的事务不能继续进行，从而释放请求的锁。对于每个被阻塞的事务，INNODB_LOCKS包含一行描述事务请求的每个锁，以及它正在等待的锁。INNODB_LOCKS也为每个阻塞另一个事务的锁包含一行，无论持有锁的事务处于什么状态(INNODB_TRX.TRX_STATE)正在运行、锁等待、回滚或提交
INNODB_LOCK_WAITS：此表指示哪些事务正在等待给定的锁，或者给定事务正在等待的锁。该表为每个被阻止的事务包含一个或多个行，指示它已请求的锁以及正在阻止该请求的所有锁。REQUESTED_LOCK_ID值是指事务请求的锁，BLOCKING_LOCK_ID值是指防止第一个事务继续进行的锁（由另一个事务持有）。对于任何给定的阻止交易，INNODB_LOCK_WAITS中的所有行都具有的相同值REQUESTED_LOCK_ID和的不同值BLOCKING_LOCK_ID。
系统表
INNODB_SYS_TABLES:提供有关InnoDB表的元数据
INNODB_SYS_COLUMNS:提供有关InnoDB表列的元数据
INNODB_SYS_INDEXES:提供有关InnoDB索引的元数据
INNODB_SYS_FIELDS:提供有关InnoDB索引键列（字段）的元数据
INNODB_SYS_TABLESTATS:提供有关InnoDB从内存数据结构派生的表的低级状态信息的视图
INNODB_SYS_DATAFILES:提供InnoDB每表文件和常规表空间的数据文件路径信息
INNODB_SYS_TABLESPACES:提供有关InnoDB每表文件和常规表空间的元数据
INNODB_SYS_FOREIGN:提供有关在InnoDB表上定义的外键的元数据
INNODB_SYS_FOREIGN_COLS:提供有关在InnoDB表上定义的外键列的元数据
缓冲池
INNODB_BUFFER_PAGE:保存有关InnoDB缓冲池中每个页面的信息。
INNODB_BUFFER_PAGE_LRU:保存有关InnoDB缓冲池中页面的信息，特别是LRU列表中的页面顺序，LRU列表确定在缓冲池已满时应从缓冲池中退出哪些页面
INNODB_BUFFER_POOL_STATS:提供缓冲池状态信息。
在线DLL
优点
比表复制方法更少的磁盘空间使用和I/O开销。
使用LOCK子句在DDL操作期间调整性能和并发之间的平衡的能力
示例
ALTER TABLE tbl_name ADD PRIMARY KEY (column), ALGORITHM=INPLACE, LOCK=NONE;
LOCK子句可用于微调对表的并发访问程度。ALGORITHM子句主要用于性能比较，并在遇到任何问题时作为对旧表复制行为的后备。
为避免意外使表不可用于读取/写入或同时读写，请在ALTER TABLE语句上指定一个子句， 例如LOCK=NONE（允许读取和写入）或 LOCK=SHARED（允许读取）。如果请求的并发级别不可用，该操作将立即停止。
要比较算法之间的性能，请使用ALGORITHM=INPLACE或ALGORITHM=COPY运行语句。或者在old_alter_table禁用和启用配置选项的情况下运行语句 。
为避免使用ALTER TABLE复制表的操作绑定服务器，请包括ALGORITHM=INPLACE。如果该语句不能使用就地机制，则会立即暂停。
要评估DDL操作的相对性能，您可以将使用ALGORITHM=INPLACE的结果与使用的结果进行比较ALGORITHM=COPY
在大表上运行DDL操作之前，请按照以下步骤检查操作是快速还是慢速：
- 克隆表结构
- 用少量数据填充克隆表
- 在克隆表上运行DDL操作
- 检查“受影响的行”值是否为零。非零值表示该操作将复制表数据，这可能需要进行特殊规划。
在线DDL操作可以视为具有三个阶段
阶段1：初始化
在初始化阶段，服务器将考虑存储引擎功能，语句中指定的操作以及用户指定的ALGORITHM和LOCK选项，以确定在操作期间允许多少并发。在此阶段，将使用共享的可升级元数据锁来保护当前表定义。
阶段2：执行
在此阶段，准备并执行该语句。元数据锁是否升级到排他锁取决于初始化阶段评估的因素。如果需要排他元数据锁定，则仅在语句准备期间进行短暂锁定。
阶段3：提交表定义
在提交表定义阶段，将元数据锁升级为排他锁，以退出旧表定义并提交新表定义。一旦被授予，排他元数据锁定的持续时间就很短。
由于上面概述的排他性元数据锁定要求，在线DDL操作可能必须等待持有表上元数据锁定的并发事务才能提交或回滚。在DDL操作之前或期间启动的事务可以将元数据锁保存在要更改的表上。如果事务长期运行或处于非活动状态，则在线DDL操作可能会超时，等待排他元数据锁定。此外，在线DDL操作请求的待处理排他元数据锁定会阻止表上的后续事务。
LOCK子句
LOCK=NONE：允许并发查询和DML，例如，对于涉及客户注册或购买的表，请使用此子句，以避免在冗长的DDL操作期间使这些表不可用
LOCK=SHARED：允许并发查询，但阻止DML，例如，在数据仓库表上使用此子句，您可以在其中延迟数据加载操作直到DDL操作完成，但是查询不能长时间延迟
LOCK=DEFAULT：允许尽可能多的并发（并发查询，DML或两者）。省略该LOCK子句与指定LOCK=DEFAULT相同，当您知道DDL语句的默认锁定级别不会导致表的可用性问题时，请使用此子句
LOCK=EXCLUSIVE：阻止并发查询和DML，如果主要的关注点是在尽可能短的时间内完成DDL操作，并且不需要并发查询和DML访问，请使用此子句。如果服务器应处于空闲状态，也可以使用此子句，以避免意外的表访问
限制
长时间运行的联机DDL操作可能导致复制滞后。联机DDL操作必须先在源上运行，然后才能在副本上运行。另外，在源上并发处理的DML仅在副本上的DDL操作完成后才在副本上处理
InnoDB崩溃恢复
崩溃恢复是自动完成的，包括以下几个步骤
表空间发现
表空间发现是InnoDB用于识别需要重做日志应用程序的表空间的过程
重做日志应用程序
重做日志应用程序在初始化期间执行，然后接受任何连接。如果在关闭或崩溃时将所有更改从缓冲池刷新到了表空间 （ibdata*和*.ibd文件），则将跳过重做日志应用程序。InnoDB如果启动时缺少重做日志文件，还将跳过重做日志应用程序。
不建议删除重做日志以加快恢复速度，即使有些数据丢失是可以接受的。仅应在将innodb_fast_shutdown设置为0或干净关闭后，再考虑删除重做日志 1。
回滚未完成的事务
不完整的事务是在意外退出或快速关闭时处于活动状态的任何事务 。回滚不完整的事务所花费的时间可能是事务在被中断之前处于活动状态的时间的三到四倍，具体取决于服务器负载。
您无法取消正在回滚的事务。在极端情况下，如果预计回滚事务将花费非常长的时间，则将innodb_force_recovery设置为3开始可能会更快。
更改缓冲区合并
将更改从更改缓冲区（系统表空间的一部分 ）应用于辅助索引的叶页，因为将索引页读取到缓冲池中。
清除
删除已删除标记的记录，这些记录不再对活动事务可见
限制
一个表最多可以包含1017列。虚拟生成的列包含在此限制中。
一个表最多可以包含64个二级索引
如果innodb_large_prefix启用（默认值），则InnoDB使用DYNAMIC或COMPRESSED行格式的表的索引键前缀限制为3072字节。如果禁用innodb_large_prefix，则任何行格式的表的索引关键字前缀限制为767个字节。innodb_large_prefix不推荐使用，将在未来版本中删除
多列索引最多允许16列。超过限制将返回错误。
对于4KB，8KB，16KB和32KB的页面大小，最大的行大小（不包括页面外存储的任何可变长度列）都小于页面的一半。例如，默认16KB的innodb_page_size的最大行大小约为8000个字节。但是，对于InnoDB 64KB的页面大小，最大行大小约为16000字节。
LONGBLOB和 LONGTEXT 列必须小于4GB，并且总行大小（包括BLOB和 TEXT列）必须小于4GB。
如果一行的长度小于一页的一半，则所有行都将存储在本地页面内。如果它超过一页的一半，那么将选择可变长度列用于外部页外存储，直到该行适合半页为止，
尽管InnoDB内部支持大于65,535字节的行大小，但MySQL本身对所有列的组合大小强加了65,535的行大小限制
InnoDB日志文件的 最大总大小为512GB。
在Windows 32位系统上，表空间文件不能超过4GB。
其他存储引擎
InnoDB：MySQL 5.7中的默认存储引擎。 InnoDB是用于MySQL的事务安全（符合ACID）的存储引擎，具有提交，回滚和崩溃恢复功能以保护用户数据。 InnoDB行级锁定（无需升级为更粗粒度的锁定）和Oracle风格的一致非锁定读取可提高多用户并发性和性能。InnoDB将用户数据存储在聚集索引中，以减少基于主键的常见查询的I/O。为了保持数据完整性， InnoDB还支持FOREIGN KEY外键约束。
MyISAM：表占用的空间很小。表级锁定限制了读/写工作负载中的性能，因此它通常用于Web和数据仓库配置中的只读或只读工作负载中。
Memory：将所有数据存储在RAM中，以便在需要快速查找非关键数据的环境中进行快速访问。该存储引擎以前称为HEAP存储引擎。它的用例正在减少；InnoDB借助其缓冲池内存区域，提供了一种通用且持久的方式来将大多数或所有数据保留在内存中，并且NDBCLUSTER为大型分布式数据集提供了快速的键值查找。
CSV：实际上是带有逗号分隔值的文本文件。CSV表使您可以导入或转储CSV格式的数据，以便与读取和写入相同格式的脚本和应用程序交换数据。由于CSV表未建立索引，所以通常在正常操作期间会将数据保存在InnoDB表中，仅在导入或导出阶段使用CSV表。
Archive：这些紧凑的未索引的表旨在用于存储和检索大量很少引用的历史，归档或安全审核信息。
Blackhole：Blackhole存储引擎可以接受但不存储数据，类似于Unix/dev/null设备。查询总是返回一个空集。这些表可用于将DML语句发送到副本服务器的复制配置中，但源服务器不保留其自己的数据副本。
NDB（也称为 NDBCLUSTER）：此集群数据库引擎特别适合于需要尽可能高的正常运行时间和可用性的应用程序。
Merge：使MySQL DBA或开发人员能够逻辑地将一系列相同的MyISAM表分组并将它们作为一个对象引用。适用于VLDB环境，例如数据仓库。
Federated：提供了链接不同的MySQL服务器以从许多物理服务器创建一个逻辑数据库的能力。非常适合于分布式或数据集市环境。
Example：此引擎作为MySQL源代码中的示例，说明了如何开始编写新的存储引擎。它主要是开发人员感兴趣的。存储引擎是什么都不做的 “存根”。您可以使用此引擎创建表，但是不能在其中存储数据或从中检索数据
myisam
为了帮助MySQL更好地优化查询，请在表加载数据后对表使ANALYZE TABLE或运行myisamchk --analyze。这将为每个索引部分更新一个值，该值指示具有相同值的平均行数