月份：2022年2月

InnoDB页面大小配置
innodb_page_size选项指定了MySQL实例的所有InnoDB表空间的页面大小。这个值是在创建实例时设置的，之后保持不变。有效值为64K,32K,16K(默认值 ),8k和4k。另外，也可以以字节为单位来指定页面大小(65536,32768,16384,8192,4096)。

默认页面大小为16k适合于各种工作负载，特别是涉及表扫描的查询和涉及批量更新的DML操作。对于涉及许多小写的OLTP工作负载来说，较小的页面大小可能更有效，在这种情况下，当单个页面包含许多行时，争用可能是一个问题。对于SSD存储设备，较小的页面可能也很有效，SSD存储设备通常使用较小的块大小。保持InnoDB页面大小接近存储设备块大小，可以最大限度地减少被重写到磁盘的未更改数据量。

InnoDB内存配置
MySQL将内存分配给各种缓存和缓冲区，以提高数据库操作的性能。当为InnoDB分配内存时，总是考虑操作系统需要的内存，分配给其他应用程序的内存，以及分配给其他MySQL缓冲区和缓存的内存。例如，如果您使用MyISAM表，请考虑分配给键缓冲区的内存量(key_buffer_size)。

InnoDB临时表空间配置
默认情况下，InnoDB将在innodb_data_home_dir目录中创建一个名为ibtmp1且自动扩展大小略大小12MB的临时表侬间数据文件。默认的临时表空间数据文件配置可以在启动时使用innodb_temp_data_file_path配置选项来进行修改。

innodb_temp_data_file_path选项指定InnoDB临时表空间数据文件的路径，文件名和文件大小。一个文件的完整目录路径是由innodb_data_home_dir和innodb_temp_data_file_path拼接而成的。文件大小以KB,MG或GB为单位进行指定，表示方式为K,M或G。文件的总大小要略大于12MB。

innodb_data_home_dir默认值为MySQL数据目录(datadir)。

InnoDB Undo表空间配置
默认情况下,InnoDB undo日志是系统表空间的一部分。然后，可以选择在一个或多个单独的undo表空间来存储(表空间通常存储在不同的存储设备上)InnoDB undo日志。

innodb_undo_directory配置选项定义了InnoDB为存储undo日志所创建单独表空间的目录路径。这个选项通常与innodb_rollback_segments和innodb_undo_tablespaces选项联合使用，它决定了unod日志在系统表空间这外的磁盘布局。

注意:innodb_undot_tablespaces将在未来的版本中被丢弃和删除。

InnoDB日志文件配置
默认情况下，InnoDB将在MySQL数据目录(datadir)中创建大小为48MB名为ib_logfile0和ib_logfile1的两个日志文件。

下面的选项可以被用来修改默认配置:
.innodb_log_group_home_dir 定义InnoDB日志文件（重做日志）的目录路径。如果这个选项没有被配置，InnoDB日志文件将会在MySQL数据目录（datadir)中创建。

可以使用这个选项将InnodDB日志文件与InnoDB数据文件存储在不同的物理存储位置来避免潜在的I/O资源冲突。例如:

[mysqld]
innodb_log_group_home_dir=/dr3/iblogs

注意:InnoDB不会创建目录，因此要确保在启动MySQL服务器之前日志目录已经存在。要确保MySQL服务器对日志目录有正确权限来创建日志文件。

.innodb_log_files_in_group定义日志组中的日志文件数。默认与建议值为2.

.innodb_log_file_size定义日志组中每个日志文件的大小以字节为单位。日志文件的总大小(innodb_log_file_size*innodb_log_files_in_group)不能超过最大值512GB，也就是略小于512GB。一对大小255GB大小的日志文件，就是略接近最大值但不超过它。默认的日志文件大小为48MB。通常，日志文件的合并大小应该足够大，以便服务器能够平滑工作负载活动的高峰和低谷，这通常意味着有足够的重做日志空间来处理超过一个小时的写活动。该值越大，缓冲池中需要的检查点刷新活动就越少，从而节省磁盘I/O。

系统表空间数据文件配置
系统表空间数据文件是通过innodb_data_file_path和innodb_data_home_dir配置选项来进行配置的。

innodb_data_file_path配置选项被用来配置InnoDB系统表空间数据文件。innodb_data_file_path应该是一个或多个数据文件的规范列表。如果要命名多个数据文件，使用分号（;)来进行分隔：
innodb_data_file_path=datafile_spec1[;datafile_spec2]…

例如，下面的设置创建一个最小大小的系统表空间：

[mysqld]
innodb_data_file_path=ibdata1:12M:autoextend

上面的设置配置一个12MB大小命名为ibdata1且自动扩展的数据文件。这里没有指定文件路径，因此，InnoDB将在MySQL数据目录中创建该文件。

数据文件大小可以通过K,M,或G后缀来指示KB，MG或GB。

如果一个表空间包含一个名为ibdata1固定大小为50MB和一个名为ibdata2大小为50MB自动扩展的数据文件可以进行以下配置：

[mysqld]
innodb_data_file_path=ibdata1:50;ibdata2:50:autoextend

数据文件规范的完整语法包括文件名，文件大小和多个选项属性：

file_name:file_size[:autoextend[:max:max_file_size]]

autoexten和max属性只能用于innodb_data_file_path行中的最后一个数据文件

如果对最后一个数据文件指定了autoextend，当表空间中没有可用空间时InnoDB会扩展数据文件大小。默认情况下是一次扩展64MB。为了修改增量，可以修改innodb_autoextend_increment系统变量。

如果存储表空间数据文件的磁盘空间填满，可以在其实磁盘上给表空间增加数据文件。

InnoDB不知道文件系统的最大文件大小，因此要注意文件系统的最大文件大小，比如2GB。为了给自动扩展的数据文件指定一个最大大小，在autoextend属性后面使用max属性。只在限制磁盘使用至关重要的情况下使用max属性，因为超过最大大小会导致致命的错误，可能包括崩溃。下面的设置允许ibdata1增长到限制所指定的500MB：

[mysqld]
innodb_data_file_path=ibdata1:12M:autoextend:max:500M

默认情况下InnoDB在MySQL数据目录(datadir)中创建表空间文件。为了显式指定存储位置，使用innodb_data_home_dir选项。例如，为了在名为myibdata目录中创建两个名为ibdata1和ibdata2的数据文件，使用如下配置：

[mysqld]
innodb_data_home_dir=/path/to/myibdata/
innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend

注意:在给innodb_data_home_dir指定值时需要使用斜杆。

InnoDB不会创建目录，因此确保在启动MySQL服务器之前指定的myibdata目录已经存在。还要确保MySQL服务器对所指定的目录有正确的权限来创建文件。更通俗地说，服务器必须在需要创建数据文件的任何目录中具有访问权限。

InnoDB通过将innodb_data_home_dir的值与数据文件名进行文本连接来形成每个数据文件的目录路径。如果在my.cnf中没有指定innodb_data_home_dir选项，那么默认值是“dot”目录./，意思是MySQL数据目录。（MySQL服务器在开始执行时将其当前工作目录更改为数据目录。）

如果将innodb_data_home_dir指定为空字符串，则可以为innodb_data_file_path值中列出的数据文件指定绝对路径。下面的示例等价于上面的设置：

[mysqld]
innodb_data_home_dir=
innodb_data_file_path=/path/to/myibdata/ibdata1:50M;/path/to/myibdata/ibdata2:50M:autoextend

Deadlocks in InnoDB
死锁是指不同的事务无法进行处理的情况，因为每个事务都持有对方需要的锁。因为这两个事务都在等待资源可用， 他们都没有释放过它所持有的锁。

当事务锁定多个表中的行（通过update或select … for update等语句），但顺序相反时会发生死锁。当这样的语句锁定索引条目和间隙范围时，每个事务由于时间问题获得一些锁定，而不获得其他锁定也会发生死锁。

为了减少死锁的可能性，请使用事务处理，而不是锁定表语句；保持插入或更新数据的事务处理足够小，以免长时间保持开放，当不同的事务更新多个表或大范围的行时，请在每个事务中使用相同的操作顺序（例如select … for update,在select … for update和update … where语句所使用的列上创建索引。死锁的可能性不受隔离级别的影响，因为隔离级别改变了读取操作的行为，列锁是由于写操作引起的。

当启用死锁检测（默认）并发生死锁时，InnoDB会检测触发条件并回滚其中一个事务（受害者）。如果使用innodb_deadlock_detect配置选项禁用死锁检测，InnoDB依赖于innodb_lock_wait_timeout设置来在发生死锁时回滚事务。因此，即使您的应用程序逻辑是正确的， 您仍然必须处理必须重试事务的情况。若要查看InnoDB用户事务中的最后一个死锁，请使用show engine innodb status命令。如果频繁的出现死锁这就突出表时事务结构或应用程序错误处理存在问题，可以启用innodb_print_all_deadlocks设置将所有死锁的信息打印到mysqld错误日志。

一个InnoDB死锁示例
下面的例子将演示当一个锁请求将造成死锁时可能出现的错误。这个例子将调用两个客户端A和B。

首先客户端A创建一个表并插入一行记录，然后开始一个事务。在这个事务中通过请求共享模式锁的查询语句来对行获得共享锁：

mysql> create table t(i int) engine=innodb;
Query OK, 0 rows affected (0.11 sec)

mysql> insert into t values(1);
Query OK, 1 row affected (0.02 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t where i=1 lock in share mode;
+------+
| i    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

客户端B开始一个事务并试图从表中删除这行记录：

mysql> start transaction;
Query OK, 0 rows affected (0.01 sec)

mysql> delete from t where i=1;

删除操作请求一个X锁。因它它与客户端A所持有的S锁不兼容，因此请求将会在锁请求队列中进行大排队并且客户端B会被阻塞

最后，客户端A也试图删除这行记录：

mysql> delete from t where i=1;

客户端B出现错误信息：

mysql> delete from t where i=1;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

这里会发生死锁，因为客户端A需要一个X锁来删除该行。但是，不能授予该锁定请求，因为客户端B已经有了一个关于X锁定的请求，并且正在等待客户端A 来释放它的S锁。由于B事先请求使用X锁，也不能将A持有的S锁升级为X锁。因此，InnoDB会为其中一个客户端通过成错误信息并释放它所持有的锁。客户端将返回以下错误：

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

在这时，客户端A可以被授予锁请求并从表中删除记录

mysql> delete from t where i=1;
Query OK, 1 row affected (0.00 sec)

死锁检测与回滚
当死锁检测被启用时（默认值），InnoDB会自动检测事务的死锁并回滚其中的一个事务或多个事务来打破死锁。InnoDB尝试选择小事务来进行回滚，而事务的大小是由插入，更新或删除的行记录数来判断的。

如果innodb_table_locks=1（默认）和autocommit=0，而且MySQL层知道行级锁，那么InnoDB就知道表锁。否则，InnoDB无法检测到由MySQL locktables语句设置的表锁或由InnoDB以外的存储引擎设置的锁。通过设置innodb_lock_wait_timeout系统的值来解决这些情况。

当InnoDB执行完一个事务的回滚，由该事务所设置的锁都会被释放。但是，如果由于错误只导致一个SQL语句被回滚，则该语句设置的一些锁可能会被保留。这是因为InnoDB以一种格式存储行锁，这样之后就不能知道哪个语句设置了哪个锁。

如果一个事务中的SELECT调用一个存储函数，并且函数中的语句执行失败，这个语句被回滚。之后，如果执行回滚，那么整个事务将会被回滚。

如果InnoDB监控输出的LATEST DETECTED DEADLOCK部分包含一条”TOO DEEP OR LONG SEARCH IN THE LOCK TABLE WAITS-FOR GRAPH, WE WILL ROLL BACKFOLLOWING TRANSACTION”信息，这指示等待列表中的事务数据已经超过了200的限制。当等待列表超过200个事务时就会当作死锁并且试图检查等待列表的事务将被回滚。如果锁定线程必须查看超过1000000个锁是由等待列表中事务所持有也会出现同样的错误。

禁用死锁检测
在高并发系统中，当多线程等待同一个锁时，死锁检测可能会导致速度减慢。这时禁用死锁检测并依赖innodb_lock_wait_timeout在出现死锁时进行事务的回滚可能更有效。可以通过innodb_deadlock_detect配置选项来禁用死锁检测。

如何减少和处理死锁
下面介绍如何组织数据库操作来减少死锁以及处理死锁。

死锁是事务型数据库中一个非常经典的问题，但它们并不危险，除非频繁出现死锁且不能运行特定的事务。正常来说如果事务因为死锁被回滚那么你必须编写你的应用程序来准备重新执行事务。

InnoDB使用自动行级锁。那么可能在只有插入或删除单行记录时也会出现死锁。这是因为这些操作不是真正原子的，它们自动在被插入或删除的行记录所对应的索引记录在设锁。

您可以通过以下技术来处理死锁，并减少其发生的可能性：
.的任何时候，可以执行show engine innodb status命令来判断最近出现死锁的情况。这可以帮助你优化程序来避免死锁。

.如果经常出现死锁警告引起关注，请通过启用innodb_print_all_deadlocks配置选项来收集更广泛的调试信息。关于每个死锁的信息，而不仅仅是被记录在MySQL错误日志中最新的一个,调试后禁用此选项。

.如果事务由于死锁而失败，请随时准备重新执行它。死锁并不危险。再试一次。

.保持事务持续时间小和短，使它们不容易发生碰撞。

.在进行一组相关更改后立即提交事务，使它们不容易发生碰撞。特别是，不要让一个交互式mysql会话长期打开一个未已提交的事务。

.如果使用锁定读取(select … for update 或select … lock in share mode)，那么尝试使用低隔离级别比如read committed。

.当修改事务中的多个表或同一表中的不同行集时，每次都按照一致的顺序执行这些操作。然后事务形成定义良好的队列且不会死锁。例如，将数据库操作组织成应用程序中的函数，或调用存储例程，而不是在不同的地方编码多个插入、更新和删除语句。

.向表中添加选择良好的索引。然后，您的查询需要扫描更少的索引记录，从而设置更少的锁。使用explain select来判断MySQL服务器认为哪些索引最适合您的查询。

.使用更少的锁定。如果您可以允许select语句从旧快照返回数据，请不要向语句中添加for update或lock in share mode子句。使用read committed读提交的隔离级别是不错的选择， 因为同一事务中的每个一致性读取都是从它自己的新快照中读取的。

.如果没有其他帮助，请使用表级锁序列化你的事务。对事务表使用lock tables，如InnoDB表,使用set autocommit=0开始事务（而不是start transaction），接着使用lock tables，并且在显式提交事务之前不要unlock tables。例如，如果您需要写入表t1并从表t2中读取，可以执行如下操作：

set autocommit=0;
lock tables t1 write,t2 read,...;
... do something with tables t1 and t2 here ..
commit;
unlock tables;

表级锁阻止对表的并发更新，以牺牲繁忙系统的响应能力为代价避免死锁。

.另一种序列化事务的方法是创建一个仅包含一行的辅助“信号量”表。在访问其他表之前，让每个事务都要更新该行。通过这种方式，所有事务以一种串行的方式发生。请注意，InnoDB即时死锁检测算法在这种情况下也可以工作，因为序列化锁是一个行级锁。使用MySQL表级锁时，必须使用超时方法来解决死锁。