Mysql全局优化总结
从上图可以看出SQL及索引的优化效果是最好的，而且成本最低，所以工作中我们要在这块花更多时间
服务端系统参数
官方文档：https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_max_connections
有些系统参数是可以在运行时修改的，修改之后，5.7重启之后会失效，8.0支持参数持久化
max_connections
| Dynamic | Yes |
|---|---|
| Default Value | 151 |
| Minimum Value | 1 |
| Maximum Value | 1000000 |
在MySQL中，max_connections参数用于控制数据库服务器允许同时连接的客户端数量的最大值。这个设置非常重要，因为它直接影响到数据库服务器能够处理的并发连接数。然而，实际上能够支持的最大连接数并不仅仅取决于max_connections的值。
MySQL服务器上同时打开的文件数量由open_files_limit参数控制。这包括客户端连接、日志文件、表文件等。为了保证MySQL服务器的稳定运行，实际上能够支持的最大客户端连接数会受到open_files_limit的间接限制。具体来说，最大有效的客户端连接数是open_files_limit减去810与max_connections设置值中较小的一个。这个减去810的操作是为了留出足够的文件描述符用于正常的服务器运行，比如打开新的表文件、日志文件等。
这意味着，即使你将max_connections设置得很高，如果open_files_limit设置得不够高，实际能够支持的最大连接数也会受限。因此，在配置max_connections时，你还需要考虑open_files_limit的值，确保两者之间有适当的比例，以优化数据库服务器的性能和稳定性。调整这些参数时，要注意操作系统对进程可以打开的文件数量也有限制，因此调整open_files_limit可能需要相应地调整操作系统级别的设置。此外，增加最大连接数可能会增加服务器的内存使用，因此在调整这些参数时也要考虑到服务器的硬件资源。
max_user_connections
| Dynamic | Yes |
|---|---|
| Default Value | 0 |
| Minimum Value | 0 |
| Maximum Value | 4294967295 |
max_user_connections参数在MySQL中用于限制给定用户账户允许的最大同时连接数。如果这个值设置为0（默认值），则表示没有限制。这个功能对于数据库管理员来说非常有用，因为它可以帮助控制单个用户对数据库资源的使用，防止单个用户占用过多连接导致其他用户无法连接。
参数特性
- 全局值：可以在服务器启动时或运行时设置。这意味着你可以在my.cnf配置文件中预先设置此值，或者通过SET GLOBAL max_user_connections=值;命令在服务器运行时动态调整它。
- 会话值：这是一个只读值，表示当前会话关联账户的有效的同时连接限制。会话值的初始化取决于用户账户的资源限制。
初始化逻辑
- 如果用户账户设置了非零的MAX_USER_CONNECTIONS资源限制，则会话的max_user_connections值将被设置为该限制值。
- 如果用户账户没有设置MAX_USER_CONNECTIONS资源限制，或者其资源限制值为0，则会话的max_user_connections值将被设置为全局的max_user_connections值。
设置账户资源限制
可以通过CREATE USER或ALTER USER语句来指定账户资源限制。这意味着在创建用户时或之后，你可以为特定用户设置连接数限制，例如：
CREATE USER 'username'@'host' WITH MAX_USER_CONNECTIONS 10;
或者，如果用户已存在，你可以调整其限制：
ALTER USER 'username'@'host' WITH MAX_USER_CONNECTIONS 10;
这些语句设置了username用户在任何给定时刻最多只能有10个活动连接。如果用户尝试超过此限制的连接，额外的连接尝试将被拒绝。
back_log
| Dynamic | No |
|---|---|
| Default Value | -1 |
| Minimum Value | 1 |
| Maximum Value | 65535 |
back_log 参数在 MySQL 中用于管理服务器如何处理大量短时间内的进来的连接请求。这个设置特别关键，尤其是在高负载或连接请求高峰期的情况下。
定义
- back_log 是一个 MySQL 系统变量，它决定了服务器在开始拒绝新的连接请求之前，可以排队等待处理的未决连接请求的数量。这个设置具体关联到接收 TCP/IP 连接的监听队列的大小。
功能性
- 当新的客户端连接请求到达时，如果 MySQL 的主连接处理线程忙碌，这个请求就会被放入这个队列中。一旦主线程可用，它就会处理队列中的请求。
- 处理包括主线程检查连接，并可能启动一个新线程（或进程）来处理会话。这个操作虽然通常很快，但在连接请求密集到来时，处理时间会累积显著的延迟。
系统交互
- 这个队列的实际大小也取决于操作系统的限制。大多数操作系统对 TCP/IP 监听队列的最大大小有自己的最大值设定。例如，基于 UNIX 的系统通过 listen() 系统调用参数来控制。
- MySQL 的 back_log 值不能高于操作系统支持的限制。如果设置得更高，实际有效值将被限制在系统的最大值内。
默认值与调整
- 默认值：back_log 的默认值通常设置为 max_connections 的值，允许 backlog 调整到 MySQL 配置要处理的最大连接数。
- 调整 back_log：如果你预期在短时间内会有大量的连接（例如，高流量的网站在用户活动高峰期），可能需要增加 back_log 值。然而，需要注意的是，将这个设置提高到系统能力以上将无效。
lock_wait_timeout
| Dynamic | Yes |
|---|---|
| Default Value | 31536000 |
| Minimum Value | 1 |
| Maximum Value | 31536000 |
| Unit | seconds |
lock_wait_timeout 变量在 MySQL 中是一个服务器系统变量，用于确定事务等待元数据锁的超时时间（以秒为单位）。这个设置对于控制在数据库对象访问发生并发时操作等待锁的时间非常重要。
关键特征：
- 超时范围：超时值可以设置在 1 秒到 31536000 秒（相当于 1 年）之间，这提供了基于数据库预期并发和工作负载的灵活性。
- 默认值：默认设置为 31536000 秒，实际上意味着在正常情况下，超时时间足够长，通常不会触发，前提是操作能在合理时间内完成。
- 应用范围：
-
对于直接涉及数据库对象（如表和视图）的 DML 和 DDL 操作，lock_wait_timeout 决定了一个操作在放弃前可以等待获取必要的元数据锁的最大时间。如果在这段时间内未能获取锁，操作将被终止，并返回超时错误。
-
此参数同样适用于如 LOCK TABLES 或 FLUSH TABLES WITH READ LOCK 这类需要显式锁定表的操作。
-
如果你直接对系统表执行 SELECT 或 UPDATE 等操作，这个超时设置同样有效。
- 操作影响：
- 每次尝试获取元数据锁都有其单独的倒计时，基于 lock_wait_timeout。这意味着如果一个语句需要多个锁，总等待时间可能会超过个别超时值，因为每个锁都会等待至超时限制。
- 如果发生超时，将触发 ER_LOCK_WAIT_TIMEOUT 错误，并中止操作。这有助于防止事务无限期地被阻塞，从而有助于数据库系统的整体响应性和健康。
- 特殊情况用途：
- lock_wait_timeout 还决定了 LOCK INSTANCE FOR BACKUP 语句在放弃前等待锁的时间。这在数据库备份操作中特别有用，因为此时可能会发生锁冲突。
设置 lock_wait_timeout 时，请考虑以下几点：
- 高并发环境：在并发高的数据库中，设置较低的 lock_wait_timeout 可以通过更快地失败来减少锁竞争的影响。
- 批处理操作和维护：在常见长时间运行操作（如批处理或维护任务）的环境中，可能需要更高的超时值以允许这些操作无中断地完成。
wait_timeout
| Dynamic | Yes |
|---|---|
| Default Value | 28800 |
| Minimum Value | 1 |
| Maximum Value | 31536000 |
| Unit | seconds |
wait_timeout 参数在 MySQL 中定义了服务器在关闭非交互式连接之前等待活动的时间（以秒为单位）。非交互式连接通常是指那些不通过 MySQL 命令行客户端等交互式工具建立的连接，而是通过应用程序、脚本或其他非交互式客户端建立的连接。当一个客户端连接到服务器但在一定时间内没有任何活动（即没有数据传输或查询执行）时，MySQL 会自动关闭该连接。这个时间长度就是由 wait_timeout 参数控制的。wait_timeout 的设置有助于释放那些长时间空闲的连接所占用的资源，从而优化服务器的性能和资源使用。
interactive_timeout
| Dynamic | Yes |
|---|---|
| Default Value | 28800 |
| Minimum Value | 1 |
| Maximum Value | 31536000 |
| Unit | seconds |
- 用途：控制被标记为交互式的客户端连接在无活动状态下保持打开的最长时间。这适用于通过MySQL shell或其他交互式工具建立的连接。
- 设置方式：与 wait_timeout 类似，可以在服务器全局级别或会话级别进行设置。
sort_buffer_size
| Dynamic | Yes |
|---|---|
| Default Value | 262144 |
| Minimum Value | 32768 |
| Maximum Value | 18446744073709551615 |
| Unit | bytes |
sort_buffer_size 是 MySQL 中的一个系统变量，用于为每个需要执行排序操作的会话分配一个特定大小的缓冲区。这个参数对所有存储引擎通用，并且在优化排序操作时起到关键作用。
功能描述
- sort_buffer_size 定义了单个会话在进行 ORDER BY 或 GROUP BY 操作时可用于排序的内存缓冲区大小。
- 至少需要足够大，以便能够容纳至少十五个元组。
- 如果你增加 max_sort_length（控制在排序时考虑的最大字段长度），可能也需要增加 sort_buffer_size。
性能影响
- 如果在 SHOW GLOBAL STATUS 输出中看到 Sort_merge_passes（排序合并通过次数）的值很高，说明排序操作可能正在频繁地使用临时磁盘空间，这通常表明 sort_buffer_size 设置得过小。增加这个值可能会加快那些无法通过查询优化或改进索引来优化的排序操作。
- 优化器尽管会尝试计算所需的空间大小，但在实际操作中，可能会分配更多的空间直到达到这个限制。全局设置过大的 sort_buffer_size 可能会降低大多数执行排序的查询的性能。
最大设置限制
- sort_buffer_size 的最大可设置值为 4GB 减 1 字节。
- 对于 64 位平台，允许设置更大的值（除了 64 位 Windows，对于 64 位 Windows，过大的值会被截断到 4GB 减 1 字节，并给出警告）。
join_buffer_size
| Dynamic | Yes |
|---|---|
| Default Value | 262144 |
| Minimum Value | 128 |
| Maximum Value | 18446744073709551615 |
| Unit | bytes |
在 MySQL 中，join_buffer_size 参数用来定义对于不使用索引的普通索引扫描、范围索引扫描和全表扫描的连接（JOIN）所使用的缓冲区的最小大小。从 MySQL 8.0.18 版本开始，这个变量还控制用于哈希连接的内存量。通常，获得快速连接的最佳方法是添加索引。但当添加索引不可能时，增加 join_buffer_size 的值可以加速完全连接。
功能详解
- 每进行一次两表之间的完全连接（不使用索引的连接），就会分配一个连接缓冲区。
- 对于涉及多个表且不使用索引的复杂连接，可能需要多个连接缓冲区。
默认值与最大设置
- 默认值为 256KB。
- join_buffer_size 的最大允许设置为 4GB 减 1字节。
- 对于 64 位平台（除了 64 位 Windows），允许设置更大的值。在 64 位 Windows 上，过大的值会被截断到 4GB 减 1字节，并会发出警告。
- 设置的值如果不是 128 字节块大小的整数倍，MySQL 服务器会将其向下取整到最接近的块大小的倍数。
设置建议
- 在不使用块嵌套循环（Block Nested-Loop）或批处理键访问（Batched Key Access）算法的情况下，将缓冲区设置得过大并不会带来性能提升，因为所有连接至少会分配最小尺寸的缓冲区。因此，全局设置过大的值需要谨慎，可能会因为内存分配时间而显著降低性能。
- 建议保持全局设置较小，并仅在进行大型连接的会话中将会话设置改为较大值，或者通过使用 SET_VAR 优化器提示（optimizer hint）在每个查询的基础上改变设置。
特定算法下的使用
- 使用块嵌套循环时，较大的连接缓冲区可能有益，直到第一张表中所需的所有行的所有必要列都存储在连接缓冲区中为止。这取决于查询；最佳大小可能小于容纳第一张表的所有行。
- 使用批处理键访问时，join_buffer_size 的值定义了每次请求存储引擎的键的批量大小。缓冲区越大，对连接操作右表的顺序访问就越多，这可以显著提高性能。
innodb 参数
官方文档：InnoDB Startup Options and System Variables
innodb_thread_concurrency
| Dynamic | Yes |
|---|---|
| Default Value | 0 |
| Minimum Value | 0 |
| Maximum Value | 1000 |
innodb_thread_concurrency 是一个重要的 MySQL 配置参数，它用于控制 InnoDB 存储引擎中并发运行的线程数。
参数概述
- 作用：控制在任何时刻 InnoDB 存储引擎允许运行的线程数量。
- 默认值：在早期的 MySQL 版本中，默认值通常是 0，表示没有限制。在更高版本中，这个默认值可能会有所不同。
- 动态性：这是一个动态参数，可以在运行时调整，无需重启数据库服务。
调整原则
innodb_thread_concurrency 的设置需要根据服务器的 CPU 核心数和负载情况来调整。如果设置得过高或过低，都可能影响性能：
- 设置为 0：表示没有限制，InnoDB 将尽可能地创建线程来处理工作负载。在 CPU 资源充足且数据库负载高的情况下，这可能是一个合理的设置。
- 设置为正数：如果你的服务器在高并发负载下出现性能瓶颈，例如 CPU 使用率过高或线程切换过频繁，可以通过设置这个参数为特定的正数来限制同时运行的线程数量，这有助于减少上下文切换和竞争，可能会提高性能。
性能影响
- 低并发：在低并发情况下，将 innodb_thread_concurrency 设置得太低可能会限制性能，因为不允许足够的线程同时运行以充分利用硬件资源。
- 高并发：在高并发情况下，如果不设置或设置得过高，可能会导致过多的线程竞争资源，从而增加上下文切换的成本，影响性能。
最佳实践
在实际使用中，最佳的做法是基于具体的服务器硬件配置（如 CPU 核心数）和负载情况进行调整。一般建议从一个基于 CPU 核心数的适中值开始，然后根据应用的实际表现进行调整。例如，如果服务器有 8 个 CPU 核心，你可以尝试设置为 16 或 24 来开始，然后根据观察到的性能和资源利用率进行适当调整。监控是关键，包括 CPU 使用率、等待时间以及系统的整体响应时间，都是调整 innodb_thread_concurrency 时需要考虑的指标。通过动态调整并观察系统反应，可以找到最优的设置值，以平衡系统负载和性能。
innodb_buffer_pool_size
| Dynamic | Yes |
|---|---|
| Default Value | 134217728 |
| Minimum Value | 5242880 |
| Maximum Value (64-bit platforms) | 2**64-1 |
| Maximum Value (32-bit platforms) | 2**32-1 |
| Unit | bytes |
定义：这是整个 InnoDB 缓冲池的总大小，即 InnoDB 用于缓存表数据和索引的内存总量。
配置注意事项
- 内存竞争：较大的缓冲池可能会与系统的其他部分竞争物理内存，导致操作系统出现分页。
- 额外内存预留：InnoDB 为缓冲区和控制结构预留额外内存，使得总分配空间大约比指定的缓冲池大小多出 10%。
- 连续地址空间：缓冲池的地址空间必须是连续的，在某些操作系统（如 Windows）上可能因为 DLL 在特定地址加载而成为问题。
- 初始化时间：缓冲池的初始化时间大致与其大小成正比。在拥有大型缓冲池的实例上，初始化时间可能很长。为了缩短初始化期，可以在服务器关闭时保存缓冲池状态，并在启动时恢复。
动态调整
innodb_buffer_pool_size 可以动态设置，这意味着你可以在不重启服务器的情况下调整缓冲池的大小。缓冲池大小的调整是按照块进行的，块大小由 innodb_buffer_pool_chunk_size 变量定义，默认为 128MB。调整缓冲池大小时必须确保其总大小等于或为 innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances 的倍数。
自动配置
如果启用了 innodb_dedicated_server，在未明确定义 innodb_buffer_pool_size 时，该值将自动配置。这适用于专用的 MySQL 服务器，使其可以根据服务器的硬件资源自动优化配置。
调整 innodb_buffer_pool_size 应考虑到实际的硬件资源、服务器负载以及数据库的使用模式，以实现最优的性能平衡。
innodb_buffer_pool_instances
| Dynamic | No |
|---|---|
| Default Value | 8 |
| Minimum Value | 1 |
| Maximum Value | 64 |
innodb_buffer_pool_instances 参数在 MySQL 中用于定义 InnoDB 缓冲池被划分的区域（实例）数量。当缓冲池的大小处于多吉字节范围时，将缓冲池分割成多个独立的实例可以提高并发性，因为这减少了不同线程在读写缓存页时的争用。
参数细节
- 作用：增加缓冲池实例可以提高数据库在高并发情况下的性能，每个实例管理自己的空闲列表、刷新列表、最近最少使用（LRU）列表及其他与缓冲池相关的数据结构，并且每个实例都由其自己的互斥锁（mutex）保护。
- 条件：此选项仅在 innodb_buffer_pool_size 设置为1GB或以上时生效。总的缓冲池大小会在所有的缓冲池实例之间进行分配。
- 实例大小：为了达到最佳效率，应该设置 innodb_buffer_pool_instances 和 innodb_buffer_pool_size 的组合，使得每个缓冲池实例至少为1GB。
默认值与平台依赖性
- 32位Windows系统：
- 如果 innodb_buffer_pool_size 大于1.3GB，则默认的 innodb_buffer_pool_instances 为 innodb_buffer_pool_size / 128MB。选择1.3GB作为边界是因为超过这个大小，32位Windows系统可能无法分配所需的连续地址空间。
- 否则，默认值为1。
- 其他平台：
- 当 innodb_buffer_pool_size 大于或等于1GB时，默认值为8。
- 否则，默认为1。
innodb_buffer_pool_chunk_size
| Dynamic | No |
|---|---|
| Default Value | 134217728 |
| Minimum Value | 1048576 |
| Maximum Value | innodb_buffer_pool_size / innodb_buffer_pool_instances |
| Unit | bytes |
- 定义：这个参数定义了在调整缓冲池大小时的块大小，即每次调整缓冲池大小时增加或减少的内存块的大小。
- 用途：通过以块为单位调整大小，MySQL 可以在不需要停机的情况下更灵活地调整缓冲池的大小。
- 约束：缓冲池的总大小（由 innodb_buffer_pool_size 定义）必须是 innodb_buffer_pool_chunk_size 和 innodb_buffer_pool_instances 乘积的整数倍。
关系与操作
- 当你增加或减少 innodb_buffer_pool_size 时，实际的调整将按照 innodb_buffer_pool_chunk_size 的大小进行。这意味着缓冲池的总大小调整必须以这个块大小为单位。
- 缓冲池总大小还必须均匀分配到每个实例中，这就是为什么总大小需要是 innodb_buffer_pool_chunk_size 乘以 innodb_buffer_pool_instances 的整数倍。
innodb_file_per_table
| Dynamic | Yes |
|---|---|
| Default Value | ON |
| Type | Boolean |
innodb_file_per_table 是 MySQL 中的一个配置选项，用于控制 InnoDB 表的数据存储方式。启用这个选项时，每个 InnoDB 表都会在一个独立的表空间文件中存储数据。如果禁用，所有表的数据默认存储在系统表空间中。
启用和禁用的影响
- 启用 (innodb_file_per_table=ON)：
- 每个新创建的表都会在一个单独的文件中存储，称为文件每表表空间（file-per-table tablespace）。
- 当表被截断（truncated）或删除（dropped）时，该表使用的磁盘空间可以被操作系统回收。
- 这有助于更好地管理磁盘空间，因为只有正在使用的表才会占用空间。
- 提高了数据库的维护灵活性，因为可以独立优化或备份单个表。
- 禁用 (innodb_file_per_table=OFF)：
- 新创建的表将把数据存储在系统表空间（system tablespace），这是一个共享的大文件。
- 表的截断或删除不会将空间返还给操作系统，而是在系统表空间内部释放，可以被 InnoDB 重新使用。
- 这种方式可能导致系统表空间文件不断增长，即使存储的数据量没有增加。
配置方法
- innodb_file_per_table 可以在运行时通过 SET GLOBAL 语句配置，也可以在启动时通过命令行指定，或在配置文件中设置。
- 运行时配置需要足够的权限来设置全局系统变量。
- 一旦修改，将立即影响所有新建表的操作，但不影响已存在的表。
临时表的影响
- innodb_file_per_table 的设置不会影响临时表的创建。从 MySQL 8.0.14 开始，临时表在会话临时表空间中创建，在此之前，它们是在全局临时表空间中创建的。
innodb_read/write_io_threads
| Dynamic | No |
|---|---|
| Type | Integer |
| Default Value | 4 |
| Minimum Value | 1 |
| Maximum Value | 64 |
在 MySQL 的 InnoDB 存储引擎中，innodb_read_io_threads 和 innodb_write_io_threads 是两个配置参数，用于设置后台进行读和写操作的 I/O 线程的数量。这些参数对于优化 InnoDB 的磁盘 I/O 性能至关重要。
innodb_read_io_threads
- 功能：控制 InnoDB 用于读操作的 I/O 线程数。
- 默认值：通常默认为 4。
- 配置范围：可以设置的范围通常是 1 到 64 之间，具体取决于服务器的硬件和 MySQL 的版本。
innodb_write_io_threads
- 功能：控制 InnoDB 用于写操作的 I/O 线程数。
- 默认值：同样通常为 4。
- 配置范围：也可以设置在 1 到 64 之间。
背景及重要性
InnoDB 通过使用多个后台 I/O 线程来提高读写请求的并发处理能力，这些线程独立于 SQL 查询和事务处理线程运行。通过调整这些线程的数量，可以在不同的工作负载和硬件配置下，优化数据库的响应时间和吞吐量。
调整建议
- 硬件资源：如果你的服务器有多个 CPU 核心和高性能的 I/O 系统（如 SSD），增加这些线程的数量可能会提高性能。
- 监控与调整：观察系统的 I/O 性能指标（如 I/O 等待时间和磁盘队列长度），并根据实际性能调整线程数量。
- 负载类型：如果 InnoDB 处理的是大量的并发读写操作，增加这些线程可能会有帮助。对于以读为主的负载，增加读线程数；对于写密集型应用，增加写线程数。
实施
- 可以在 MySQL 服务器的配置文件（my.cnf 或 my.ini）中设置这些参数：
[mysqld]
innodb_read_io_threads=8
innodb_write_io_threads=8
- 对于运行中的服务器，也可以动态调整这些参数，但需要注意，动态调整可能不会立即影响所有操作，特别是对于写线程，可能需要重启数据库或者进行更深入的调整。
通过精确地配置这些参数，你可以针对特定的数据库负载和服务器硬件优化 InnoDB 的 I/O 性能，从而提高整体的数据库性能和效率。在进行这些调整时，应该注意观察系统的整体性能，避免过度调整造成资源浪费或其他性能瓝图。对于一般的 I/O 调优建议，可以参考 MySQL 官方文档中有关优化 InnoDB 磁盘 I/O 的部分。
innodb_lock_wait_timeout
| Dynamic | Yes |
|---|---|
| Type | Integer |
| Default Value | 50 |
| Minimum Value | 1 |
| Maximum Value | 1073741824 |
| Unit | seconds |
innodb_lock_wait_timeout 是一个在 MySQL 的 InnoDB 存储引擎中定义的参数，它设置了事务在放弃等待一个行锁之前应该等待的时间（以秒为单位）。这个设置对于管理数据库锁竞争和确保事务能在合理时间内完成非常重要。
基本概念
- 默认值：默认情况下，innodb_lock_wait_timeout 的值是 50 秒。
- 功能：当一个事务试图访问一个被另一个 InnoDB 事务锁定的行时，它会等待最多设定的秒数来获得对该行的写入访问权限。如果超过这个时间限制，事务将收到错误提示：
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
- 行为：当发生锁等待超时时，当前的 SQL 语句会被回滚（而不是整个事务）。如果需要在超时时回滚整个事务，需要在启动服务器时使用 --innodb-rollback-on-timeout 选项。
应用场景
- 交互式应用或 OLTP 系统：可能需要减少这个值，以便快速反馈给用户或将更新放入队列中稍后处理。
- 长时间运行的后端操作：如数据仓库的转换步骤，可能需要等待其他大型插入或更新操作完成，这种情况下可以考虑增加这个值。
特殊说明
- innodb_lock_wait_timeout 仅适用于 InnoDB 的行锁。MySQL 的表锁不在 InnoDB 内部发生，因此这个超时设置不适用于等待表锁。
- 当启用 innodb_deadlock_detect（默认启用）时，此超时值不适用于死锁，因为 InnoDB 会立即检测到死锁并回滚其中一个死锁事务。如果禁用了死锁检测，InnoDB 将依赖 innodb_lock_wait_timeout 在发生死锁时回滚事务。
配置方法
- innodb_lock_wait_timeout 可以在运行时使用 SET GLOBAL 或 SET SESSION 语句设置。
- 更改全局设置需要足够的权限，并且会影响之后连接的所有客户端。
- 任何客户端都可以更改会话设置，这只会影响该客户端。
通过适当配置 innodb_lock_wait_timeout，可以根据不同应用场景的需求优化事务的响应时间和资源利用率，减少因长时间锁等待导致的性能问题。对于数据库的性能优化和异常处理策略来说，了解并合理设置此参数是非常重要的。
innodb_flush_log_at_trx_commit
| Dynamic | Yes |
|---|---|
| Type | Enumeration |
| Default Value | 1 |
| Valid Values | 0|1|2 |
innodb_flush_log_at_trx_commit 参数在 MySQL 中控制的是 InnoDB 存储引擎如何处理重做日志（redo log）的刷新到磁盘的操作。重做日志是 InnoDB 用来保证事务数据在系统发生故障后能恢复的一种日志机制。
重做日志的作用
- 数据完整性：
重做日志记录了事务对数据库进行的修改操作。在事务提交时，相关的日志信息首先被写入到重做日志中。这确保了即使在数据尚未完全写入磁盘的情况下发生崩溃或断电，事务的修改也不会丢失，因为这些修改可以通过重做日志恢复。
- 恢复机制：
在数据库启动过程中的恢复阶段，InnoDB 通过应用存储在重做日志中的记录来恢复未完成的事务操作，确保数据的一致性和完整性。
这个参数有三个设置值，每个值对数据完整性和性能有不同的影响：
- 设置为 1：
- 每次事务提交时，重做日志都会被立即写入并刷新到磁盘。这确保了即使数据库发生崩溃，所有已提交的事务数据都已经安全存储在磁盘上，不会丢失。
- 设置为 0：
- 重做日志只在每秒钟刷新到磁盘一次。这意味着，如果数据库在这一秒内崩溃（在日志被刷新到磁盘之前），那么自上次刷新以来已提交的所有事务的日志记录将不会存在于磁盘上，因此这些事务的数据会丢失。尽管日志记录已经写入到日志缓冲区中，但由于没有及时刷新到磁盘，所以这部分数据在崩溃时无法被恢复。
- 设置为 2：
- 类似于设置为 0，重做日志在每次事务提交时写入磁盘，但只在每秒刷新一次。如果在刷新之前系统崩溃，那么最近一秒内提交的事务可能会丢失，尽管它们已经写入到磁盘（但未刷新），因为磁盘上的数据还未完全稳定。
实际应用
选择适当的 innodb_flush_log_at_trx_commit 设置取决于业务对数据安全与性能的需求平衡。对于绝大多数需要保证数据不丢失的应用，推荐使用设置 1。如果性能是主要考虑因素，并且可以容忍极端情况下短暂的数据丢失，可以考虑使用设置 0 或 2。不过，始终需要根据具体的业务和运营需求来做出合适的配置选择。
