Mysql参数调优解析

1.脏页

定义：
在MySQL中，“脏页”是指已经被修改但尚未写回磁盘的数据页。MySQL使用了一种称为“脏页管理”的机制来跟踪这些被修改但尚未持久化到磁盘上的数据页。这种机制可以帮助MySQL提高性能，因为将数据写入磁盘是一个相对缓慢的操作，而延迟这个操作可以减少磁盘IO的负担。当对数据库进行更新操作（比如插入、更新或删除记录）时，MySQL会将这些修改写入到内存中的数据页中，而不是立即将其写入到磁盘上。这些被修改的数据页就成为了“脏页”。MySQL会定期地将脏页写回磁盘，这个过程称为“脏页刷新”。
风险：脏页的存在意味着如果数据库系统崩溃或意外关闭，一些已经被修改但尚未写入磁盘的数据可能会丢失，导致数据不一致性。因此，MySQL会在适当的时候执行检查点操作，将内存中的脏页写入磁盘，以确保数据的持久性和一致性。

相关参数：
innodb_page_cleaners：脏页清理线程数量
innodb_max_dirty_pages_pct：缓存池中脏页占比的上限
innodb_max_dirty_pages_pct_lwm：缓存池中脏页占比的下限
innodb_flush_method：脏页刷新到磁盘的方式
innodb_flush_sync：脏页刷新到磁盘，是否使用同步的方式
innodb_sync_array_size：异步刷新时缓冲区的大小

2.自适应刷新

定义：
MySQL中的自适应刷新（Adaptive Flushing）是MySQL 8.0引入的一个特性，旨在优化InnoDB存储引擎的内存管理和磁盘刷新行为。具体来说，自适应刷新通过监视系统的负载、查询模式和其他因素，动态地调整内存缓冲池（Buffer Pool）的使用方式，以提高系统的性能和效率。这项功能的核心在于它可以自动地根据当前的运行环境和工作负载调整内存管理策略，而不需要手动干预。

相关参数：
innodb_adaptive_flushing：是否开启自适应刷新
innodb_adaptive_flushing_lwm：缓存池中脏页数量的下限
innodb_adaptive_hash_index：是否启用自适应哈希索引
innodb_adaptive_max_sleep_delay：线程睡眠最大时间
innodb_fast_ahi_cleanup_for_drop_table：是否开启优化自适应哈希索引清理

3.查询优化器

定义：
查询优化器（Query Optimizer）是数据库管理系统（DBMS）中的一个核心组件，它的主要职责是生成和选择最佳的执行计划，以最有效的方式处理给定的SQL查询。优化器在查询执行之前工作，其目的是通过分析查询并找到最有效的执行路径，来最小化查询的执行时间和资源消耗。优化器可以显著提升SQL查询的执行速度，减少查询的响应时间。通过选择最优的执行计划，优化器可以降低CPU、内存和I/O等资源的使用量。优化器会根据查询条件自动选择最合适的索引，从而避免全表扫描，提高查询效率。在某些情况下，优化器会对查询进行重写，以便更高效地执行。例如，将子查询转换为连接。优化器会对不同的执行计划进行成本估算，并选择成本最低的执行路径。

相关参数：
innodb_stats_auto_recalc：是否自动重新统计信息
innodb_stats_method：统计信息的计算方法
innodb_stats_on_metadata：是否在访问元数据时更新统计信息
innodb_stats_persistent：是否持久化统计信息
innodb_stats_persistent_sample_pages：持久化统计信息采集页数
innodb_stats_transient_sample_pages：临时统计信息采集页数
optimizer_prune_level：是否舍弃一些不好的查询计划
optimizer_search_depth：查询优化器查询的深度
optimizer_switch：查询优化器规则开关
optimizer_trace_limit：查询优化器每次查询的跟踪日志最大行数
optimizer_trace_max_mem_size：：查询优化器每次查询的跟踪日志最大内存
optimizer_trace_offset：查询优化器跟踪日志的偏移量
range_optimizer_max_mem_size：查询优化器使用范围查找时最大内存

4.反查

定义：InnoDB 存储引擎的反查功能是一种数据库性能优化技术，它允许数据库引擎在执行查询时跟踪查询的执行路径，并从中提取有用的信息以优化性能。具体来说，反查功能会记录查询语句在执行过程中访问的表、索引以及访问方式等信息。通过分析这些信息，数据库引擎可以更好地理解查询的执行方式，并做出优化决策，例如选择更合适的索引、调整查询执行计划等，以提高查询性能。反查功能通常用于识别潜在的性能瓶颈和优化机会，尤其是在复杂查询或大型数据库中。通过启用反查功能并分析查询执行路径，数据库管理员可以更好地了解数据库的行为，并针对性地进行性能优化。

相关参数：
innodb_backquery_enable：是否开启反查功能
innodb_backquery_history_limit：反查历史记录的大小
innodb_backquery_window：反查窗口大小

4.预读

定义：
MySQL的预读功能是指MySQL数据库在执行查询时，会根据特定的算法和策略提前读取可能会被查询到的数据，以提高查询性能。这种预读取通常是通过读取磁盘上的数据页并将其缓存在内存中来实现的。预读取功能可以显著提高查询性能，因为它可以减少磁盘I/O操作的次数，从而降低了查询的响应时间。然而，预读取也可能会导致资源浪费，因为预读取的数据可能最终并没有被查询到，或者因为预读取导致的内存占用过高而影响了其他查询的性能。因此，在配置MySQL时需要根据具体的应用场景和硬件资源来调整预读取功能的参数。

相关参数：
innodb_random_read_ahead：是否开启随机预读
innodb_read_ahead_threshold：进行顺序读取时预读开启的阈值

5.压缩页

定义：
在数据库管理系统（DBMS）中，压缩是一种技术，用于减少数据存储的占用空间。具体到 MySQL 的 InnoDB 存储引擎中，数据压缩指的是通过特定的算法对数据页进行压缩，以减少它们在磁盘上的占用空间。这有助于提高存储效率和 I/O 性能，因为较小的数据可以更快地读取和写入。

相关参数：
innodb_compression_failure_threshold_pct：压缩失败比例
innodb_compression_level：压缩等级
innodb_compression_pad_pct_max：为每个压缩页额外分配的空间比例

6.InnoDB 缓冲池

定义：
InnoDB缓冲池是一个重要的内存区域，用于存储数据页的副本。数据页是从磁盘上的表空间中读取的数据块，包括表数据和索引数据。InnoDB缓冲池的主要作用是减少磁盘IO操作，提高查询性能。当需要访问某些数据时，首先会在InnoDB缓冲池中查找对应的数据页，如果找到了就可以直接从内存中获取数据，而不需要进行磁盘IO操作。InnoDB缓冲池主要用于存储数据页的副本，以减少磁盘IO操作，提高查询性能；

相关参数：
innodb_buffer_pool_dump_at_shutdown：停机时是否保存缓冲池到硬盘
innodb_buffer_pool_dump_pct：保存到硬盘时缓冲池的比例
innodb_buffer_pool_size：缓冲池大小
innodb_buffer_pool_load_at_startup：开机时是否加载硬盘保存的缓冲池
innodb_old_blocks_pct：旧块在缓冲池中的比例
innodb_old_blocks_time：新块被认定为旧块的时间

7.LRU算法

定义：
LRU算法的核心思想是根据页面的访问历史来进行淘汰决策。当需要淘汰一个页面时，LRU算法会选择最久未被访问的页面进行淘汰，以最大程度地保留最近被访问过的页面，从而提高命中率。在数据库系统中，LRU算法常被用于管理缓冲池中的数据页或索引页。通过优先淘汰最久未被访问的页面，LRU算法能够有效地管理缓冲池中的空间，保留最近被访问过的热点数据，从而提高命中率，减少磁盘I/O操作，提升系统性能。

相关参数：
innodb_lru_scan_depth：LUR扫描深度

8.InnoDB 变更缓冲池

定义：
InnoDB存储引擎使用变更缓冲池来暂时存储对索引的更新操作（如插入、更新、删除）。当执行对索引的更新操作时，InnoDB不会立即将这些更新操作直接写入到磁盘上的索引页中，而是将这些更新操作暂时存储在变更缓冲池中。这样做的目的是为了减少频繁的磁盘写入操作，提高性能。

相关参数：
innodb_change_buffer_max_size：变更缓冲池大小
nnodb_change_buffering：变更缓冲池类型

9.连接线程池

定义：
连接线程池可以帮助MySQL服务器更好地管理连接资源。它可以通过复用已有的线程来处理新的连接请求，从而减少了连接的创建和销毁次数，降低了系统的开销。同时，连接线程池还可以根据系统负载动态调整线程池的大小，以适应不同的工作负载，进一步优化了连接管理的效率。

相关参数：
thread_cache_size：线程池中线程个数
thread_handling：处理客户端连接线程的模式
thread_pool_oversubscribe：允许线程池中线程数超过配置的线程组数的倍数
thread_pool_size：线程池中线程组个数
thread_stack：线程池中线程的栈大小

10.自旋锁

定义：
MySQL中的自旋锁（spin lock）是一种用于同步多线程访问共享资源的机制。它的主要作用是防止多个线程同时访问同一个临界区，从而避免数据的不一致性和竞争条件。自旋锁是一种忙等待锁，即当一个线程尝试获取锁时，如果锁已经被其他线程持有，该线程不会立即进入睡眠状态，而是会在一个循环中不断检查锁的状态，直到成功获取锁或达到某种条件（如最大自旋次数）。

相关参数：
innodb_spin_wait_delay：每次等待时间
innodb_sync_spin_loops：循环次数

11.清理线程

定义：
清理（Purge）线程是 InnoDB 存储引擎的一个后台线程之一，负责清理和回收不再需要的事务日志。事务日志是用来记录数据库中所有的变更操作，确保数据库的原子性和持久性。当一个事务完成时，相关的日志信息会被保留在事务日志中，直到不再需要为止。InnoDB 存储引擎使用 MVCC 来提供事务的隔离级别。Purge 线程负责清理已经不再需要的旧版本数据，确保数据库中只保留当前活跃的数据版本，以减少存储空间的占用。当一个事务提交后，相应的事务日志就不再需要了。Purge 线程会定期扫描事务日志，回收已经提交的事务日志，释放相关的存储空间，以避免日志文件无限增长而导致磁盘空间耗尽。在一些情况下，例如长时间运行的事务或者事务日志被占用完，可能会导致历史数据无法被有效地清理。Purge 线程会处理这些过期的历史数据，以确保数据库的正常运行。

相关参数：
innodb_purge_batch_size：每批清理的事务日志数量
innodb_purge_rseg_truncate_frequency：清理回滚日志的频率
innodb_purge_threads：清理线程数量
innodb_max_purge_lag：事务存活触发清理的最大阈值
innodb_max_purge_lag_delay：对其他事务延迟的最大阈值

12.事务

相关参数：
innodb_commit_concurrency：同时提交的事务线程数
innodb_lock_wait_timeout：事务等待锁的超时时间（行锁）
innodb_rollback_on_timeout：事务超时后是否进行回滚操作
lock_wait_timeout：事务等待锁的超时时间（表锁）
transaction_alloc_block_size：每次为事务分配内存的大小
transaction_isolation：事务的隔离级别
transaction_prealloc_size：事务预分配内存大小
cdb_kill_idle_trans_timeout：事务的最大存活时间

13.回滚日志

定义：
在MySQL中，Undo Log（回滚日志）是用于实现事务的 ACID 特性之一——"事务的一致性"的重要机制。Undo Log 记录了事务执行过程中对数据的修改操作，在事务提交或回滚时，可以利用 Undo Log 将数据恢复到之前的状态。Undo Log 是 MySQL 中实现事务和 MVCC 的重要组件之一。它记录了事务执行过程中对数据的修改操作，保证了事务的原子性和隔离性，同时也支持了数据库的并发控制。

相关参数：
innodb_rollback_segments：回滚段数量
innodb_max_undo_log_size：回滚日志的大小
innodb_undo_log_truncate：是否开启定期清理回滚日志

14.连接

相关参数：
init_connect：客户端连接上服务端需要执行的QL
max_connections：最大连接数
back_log：最大等待队列
max_user_connections：每个用户的最大连接数
wait_timeout：连接空闲时间
interactive_timeout：交互式连接空闲时间
connect_timeout：连接超时时间
max_connect_errors：连接错误时最大重试次数
max_allowed_packet：客户端服务端通讯时传输的数据包上限
net_buffer_length：客户端服务端通讯时缓存区的初始大小
net_read_timeout：服务器读取客户端数据超时时间
net_retry_count：客户端中断后，服务端的重连次数
net_write_timeout：服务器写入客户端数据超时时间
host_cache_size：缓存客户端的信息
disconnect_on_expired_password：密码过期是否立即断开连接

15.I/O线程数

相关参数：
innodb_write_io_threads：写入操作的I/O线程数
innodb_read_io_threads：读取操作的I/O线程数
innodb_io_capacity_max：最大 I/O 操作的上限
innodb_io_capacity： I/O 操作的最大数量