innodb核心配置总结---官方文档阅读笔记

-- 每个表单独文件和单独表空间，而不是放在系统表空间，每个表的文件表空间允许操作系统在表被截断或删除时回收磁盘空间。每表文件表空间还支持动态和压缩行格式以及相关功能

innodb_file_per_table=on

-- 设置的模式要包含NO_ENGINE_SUBSTITUTION，这样创建表的时候，有不支持的存储引擎直接报错，而不是替换成innodb

sql_mode=NO_ENGINE_SUBSTITUTION

-- [mysqld]设置默认存储引擎

default-storage-engine=innodb

--

sync_binlog

-- 加大更改缓冲区大小，避免辅助索引频繁随机io，但是会占用总buffer pool一部分大小，所以适用大量dml操作，大量静态读操作应该调小改值

innodb_change_buffering

-- 允许配置为总buffer pool的百分比25-50之间

innodb_change_buffer_max_size

-- 自适用哈希索引启用

innodb_adaptive_hash_index

-- 自适用哈希索引关闭

--skip-innodb-adaptive-hash-index

-- 自适用哈希索引分区数

innodb_adaptive_hash_index_parts=8-512之间

-- 日志缓冲区，默认16MB，大型事务可以考虑加大，节省io

innodb_log_buffer_size

-- 日志缓冲区刷新频率

innodb_flush_log_at_timeout

-- 日志缓冲区刷新方式

innodb_flush_log_at_trx_commit=0,1,2

-- 表存储数据行的格式，REDUNDANT, COMPACT, DYNAMIC,COMPRESSED

innodb_default_row_format

-- 创建表空间，库，表名统一用小写，(表空间文件迁移导入要求)

lower_case_table_names=1

-- 启用死锁检测，允许取消(回滚)较小的事务，允许另一个事务继续

innodb_deadlock_detect

-- 死锁超时设置

innodb_lock_wait_timeout

-- 将死锁信息打印到mysqld错误日志

innodb_print_all_deadlocks

-- 自动增量锁定模式(自动增长主键列)，mysql8.0默认为2，共有0,1,2

-- 0 传统模式：多个insert select 情况下并发，自动增长量交织，不能保证使用基于语句的复制可靠地传播到副本服务器。而1 连续模式可以保证

-- 1 连续锁模式：使用表级AUTO-INC锁，可以安全地用于基于语句的复制

-- 2 “交错”锁定模式:最快和最具可伸缩性的锁定模式，但在从二进制日志重放SQL语句时，使用基于语句的复制或恢复方案是不安全的。并且分配的增量值可能有间隙

innodb_uAutoinc_ULock_Umode=2

-- 定义B+树索引所有叶子节点的总大小，单个叶子页16K，页默认是16k是内存刷新到磁盘的最小单位

innodb_page_size

-- 定义单个B+树索引叶子节点填充的空间百分比，剩余空间保留用于将来的索引增长,设置为100时，聚集索引页中1/16的空间可供将来的索引增长使用

innodb_fill_facto

-- 定义单个B+树索引叶子节点填充空间百分比阀值，默认为50%，innodb_fill_factor比它低时，InnoDB将尝试收缩索引树以释放该页

MERGE_THRESHOLD

-- 全文索引并行创建的线程数，默认为2，在大型表上创建全文索引时，请考虑增加线程数。

innodb_ft_sort_pll_degree

-- 用于配置全文索引缓存大小（基于每个表），这会影响刷新全文索引缓存的频率

innodb_ft_cache_size variable

-- 给定实例中的所有表定义全局全文索引缓存大小限制

innodb_ft_total_cache_size

-- 删除全文索引不会立刻释放空间，所以需要在表上执行OPTIMIZE TABLE且带下面参数

innodb_optimize_fulltext_only=ON

-- 系统表空间设置数据文件大小,autoextend默认自动扩展8M，此参数只能为新创建的数据空间文件配置，已有的数据文件不能修改

innodb_data_file_path=/data/ibdata1:988M;/data/ibdata2:50M:autoextend

-- 用于在自动扩展共享表空间文件满时扩展该文件的增量大小，不适用于每表单独表空间的单个增长

innodb_autoextend_increment

-- 通用表空间目录，数据目录以外的目录，如果不指定该目录，主备环境两个表空间文件则不同

innodb_directories

-- 对于包含压缩表的常规表空间（ROW_FORMAT=compressed）,必须指定该参数，且在innodb_page_size范围内，如果不指定该值，那么默认等于innodb_page_size，数据行将不会压缩

FILE_BLOCK_SIZE

-- 压缩表物理页大小， 必须是KEY_BLOCK_SIZE=FILE_BLOCK_SIZE/1024

KEY_BLOCK_SIZE

-- 撤销表空间目录，如果未定义，则默认在数据目录下创建两个

innodb_undo_directory

-- MySQL8.0.23前undo表空间初始化大小

innodb_page_size

-- MySQL8.0.23后以下面参数的至少1/4扩展undo表空间，或者按照AUTOEXTEND_SIZE

innodb_max_undo_log_size

-- MySQL8.0.14前可以配置最多127个undo表空间，但是不能删除，之后的数据库版本支持删除undo表空间

innodb_undo_tablespaces

-- 给每个undo表空间和全局临时表空间的回滚段数,最大为128，为InnoDB Page Size / 16的数量

innodb_rollback_segments

-- 启用该参数，超过innodb_max_undo_log_size变量定义的大小限制的undo表空间将被截断，循环执行

innodb_undo_log_truncate

-- purge线程查找undo表空间，按以下参数设置的每N次调用，purge就截断一次，默认为128.要增加频率则下调

innodb_purge_rseg_truncate_frequency

-- MySQL8.0.16开始用户创建的临时表和优化程序在InnoDB配置为磁盘内部临时表的存储引擎，由下面参数决定

internal_tmp_disk_storage_engine

-- 会话临时表空间目录，不指定则默认为数据目录，文件后缀为.ibt,关闭服务器会删除，启动则重建(发生故障时可以手动删除重启)，全局临时表空间不能驻留在原始设备上

innodb_temp_tablespaces_di

-- 全局临时表空间（ibtmp1）存储对用户创建的临时表所做更改的回滚段，默认大于12MB，不设置则默认路径为数据目录,关闭服务器会删除，启动则重建(发生故障时可以手动删除重启)，全局临时-- 表空间不能驻留在原始设备上,指定值，并限制大小ibtmp1:12M:autoextend:max:500M

innodb_temp_data_file_path

-- 禁用表空间路径验证，即验证路径文件和数据字典是否匹配，一般不用，在移动表空间文件后，在禁用表空间路径验证的情况下启动服务器可能会导致未定义的行为

log_error_verbosity

-- 在linux和windows系统上禁用该参数，以避免在物理上向新分配的表空间页写入空值。禁用后使用posix_fallocate() 将空间分配给表空间文件，该调用将保留空间，而不会实际写入空值。

-- 禁用后为避免导致碎片化并增加随机I/O，使用AUTOEXTEND_SIZE选项，表空间扩展大小可以增加到4GB

innodb_extend_and_initialize

-- 变量控制是否启用doublwrite缓冲区，设置为0是禁用，在Fusion-io存储设备上会自动禁用，且建议在Fusion-io存储设备设置innodb_flush_method=O_DIRECT，充分利用该特性

innodb_doublewrite

-- doublwrite目录，应该放在可用的最快存储介质上

innodb_doublewrite_di

-- doublwrite文件数量，默认是两个：flush list doublewrite文件(默认大小是InnoDB page size*doublewrite page bytes)

-- 和LRU list doublewrite文件(默认大小是InnoDB page size*（doublewrite pages+（512/缓冲池实例数）），其中512是为单页刷新保留的插槽总数)

-- 最大数量是innodb_buffer_pool_instances的两倍

innodb_doublewrite_files

-- （在MySQL 8.0.20中引入）控制每个线程的最大doublewrite页数。如果未指定任何值，则将innodb_doublewrite_pages设置为innodb_write_io_threads值。

innodb_doublewrite_pages

-- （在MySQL 8.0.20中引入）控制要在批量写入的doublewrite页的数量

innodb_doublewrite_batch_size

-- redo日志文件大小，更改后重启，会导致删除旧的redo日志文件，重新创建

innodb_log_file_size

-- redo日志文件数量

innodb_log_files_in_group

-- redo日志归档，为空是关闭，格式：'label1:directory_path1[;label2:directory_path2;…]'，场景：备份速度跟不上redo写时候开启，MySQL Enterprise Backup备份时使用

-- 如果是不支持redo归档的备份程序，则配合会话执行DO innodb_redo_log_archive_start('label', 'subdir')使用，期间保持归档开启状态

-- 目录必须提前手工创建，且不是datadir, innodb_data_home_dir, innodb_directories, innodb_log_group_home_dir, innodb_temp_tablespaces_dir, innodb_tmpdir innodb_undo_directory, -- or secure_file_priv

innodb_redo_log_archive_dirs=

-- innobd初始化需要配置的重要信息有：（MySQL服务器第一次启动时初始化，确保启动加路径参数mysqld --defaults-file=path_to_option_file）

在[mysqld]里添加

系统表空间数据文件配置

InnoDB Doublewrite缓冲区文件配置

重做日志文件配置

撤消表空间配置

全局临时表空间配置

会话临时表空间配置

页面大小配置

内存配置

1，在linux上禁用回写缓存，hdparm -W0 /dev/hda，开启innodb_doublewrite=ON

2，配置系统表空间路径用以下组合参数，并提前创建目录，且设置mysql启动用户的访问权限

[mysqld]

innodb_data_home_dir = /myibdata/

innodb_data_file_path=ibdata1:50M:autoextend

或者使用绝对路径

innodb_data_file_path=/myibdata/ibdata1:50M:autoextend

3，在默认数据目录外配置doublewrite路径

innodb_doublewrite_dir=/path/to/doublewrite_directory

4，redo日志文件

[mysqld]

innodb_log_group_home_dir = /dr3/iblogs

innodb_log_files_in_group=2 -- 建议设置为2

innodb_log_file_size -- 字节为单位，innodb_log_file_size * innodb_log_files_in_group不能超过最大值512G，设置较大值，缓冲池中的检查点刷新活动较少，从而减少磁盘I/O

5，撤销表空间路径

innodb_undo_directory

6，全局临时表空间路径

innodb_temp_data_file_path

7，会话临时表空间路径

innodb_temp_tablespaces_di

8，页面大小配置，默认16k适用各种工作负载，较大容易引起磁盘争用，较小导致频繁io

innodb_page_size

9，内存配置

-- 设置为操作系统内存的50%-75%，该设置包含存储innodb表，索引和辅助缓冲区的内存区域大小，默认为128M

-- 恒等公式innodb_buffer_pool_size=倍数*innodb_buffer_pool_instances*innodb_buffer_pool_chunk_size，如果不匹配，系统自动调整innodb_buffer_pool_size大小，使其为块大小*缓冲区个数

innodb_buffer_pool_size

-- 内存较大的系统可以把innodb_buffer_pool_size划分为多个区域，最大不应该超过1000，只有当innodb_buffer_pool_size大于1G时才生效

innodb_buffer_pool_instances

-- 内存块大小，以MB为单位增加或者减少

innodb_buffer_pool_chunk_size

案例1：在innodb_buffer_pool_chunk_size不变的情况下调整innodb_buffer_pool_size和innodb_buffer_pool_instances，会自动调整innodb_buffer_pool_size大小，使其与恒等公式匹配

案例2：同时调整三者的情况下，会自动调整innodb_buffer_pool_chunk_size=innodb_buffer_pool_size/innodb_buffer_pool_instances

案例3：只调整innodb_buffer_pool_chunk_size情况下，自动调整innodb_buffer_pool_size或者innodb_buffer_pool_instances(根据情况两个都调整或者只调整其一，innodb_buffer_pool_size会比原来的大),使其与恒等公式匹配

-- 日志缓冲区大小，默认16M，大事务可加大该值，改善io

innodb_log_buffer_size

MySQL的全局和每线程内存分配计算公式：

nnodb_buffer_pool_size

+ key_buffer_size

+ max_connections*(sort_buffer_size+read_buffer_size+binlog_cache_size)

+ max_connections*2MB

10，禁止以语句或者改表形式修改系统表，适用场景：分发和部署。可以配合innodb_change_buffering=0，innodb_log_file_size设置最小值,

-- 且禁用innodb_undo_tablespaces and innodb_undo_directory等使用

--innodb-read-only=1

11，使缓冲区抗扫描

-- innodb_buffer里的旧块百分比(LRU中不被保留在缓冲区的页），取值范围是5-95，默认值是37

-- 读取较小的表时，调高参数，较大表时，调高参数，防止一次占用很大缓冲区内存

innodb_old_blocks_pct

-- 默认值是1000ms，调高只读取一次的数据，刷出LRU列表的频率，提高命中率，建议以基准测试为准

innodb_old_blocks_time

12，InnoDB缓冲池Innodb_buffer_pool预取

-- 顺序预读，控制innodb在检测顺序页面访问模式时的敏感度，默认值56，取值范围0-64，如：该值设置为48，则InnoDB仅在按顺序访问当前区段中的48页时触发线性预读请求

innodb_read_ahead_threshold

-- 随机预读，如：在缓冲池中发现来自同一扩展数据块的13个连续页面，InnoDB将异步发出请求，以预取扩展数据块的剩余页面

Innodb_buffer_pool_read_ahead -- 设置为on为开启

Innodb_buffer_pool_read_ahead_evicted

Innodb_buffer_pool_read_ahead_rnd

13,配置缓冲池Innodb_buffer_pool刷新

-- 刷新线程数量，小于等于innodb_buffer_pool_instances，默认值为4，如果配置大于innodb_buffer_pool_instances,则自动设置与其相等

innodb_page_cleaners

-- 脏页达到的低水位值，默认值为10，始终低于innodb_max_dirty_pages_pct，缓冲池脏页百分比达到这个值时启动刷新，用于防止脏页数量达到innodb_max_dirty_pages_pct定义的阀值

innodb_max_dirty_pages_pct_lwm

-- 脏页刷新阀值，Innodb_buffer_pool脏页达到这个数量时候，innodb主动刷新脏页

innodb_max_dirty_pages_pct

-- 刷新临近页，0-表示禁用，1,-表示刷新相同范围内的连续脏页，2-表示在相同范围内刷新脏页

-- 传统HDD存储设备上建议开启，减少IO开销，ssd上建议禁用

innodb_flush_neighbors

-- 定义每个缓冲池实例在lru列表查找脏页的量，线程每秒执行工作量为innodb_lru_scan_depth * innodb_buffer_pool_instances，由innodb_page_cleaners线程在后台每秒执行一次

-- 改参数调整与io负载有关，太大会导致io容量饱和，缓冲池大的情况下减少该值

innodb_lru_scan_depth

-- 自适应刷新算法，默认开启，通过跟踪缓冲池脏页数量和重做日志记录生成速率来实现

innodb_adaptive_flushing

-- 自适应刷新重做日志容量的低水位值，当超过该值时，即使禁用了innodb_adaptive_flushing，也会启用自适应刷新

innodb_adaptive_flushing_lwm

-- 定义innodb保留先前计算的刷新状态快照的迭代次数，控制自适应刷新响应前台工作负载变化的速度

-- 具有一致工作负载、较大日志文件大小（innodb_log_file_size）和未达到75%日志空间利用率的小峰值的系统应使用较高的innodb_flushing_avg_loops值

innodb_flushing_avg_loops

-- 定义InnoDB可用的I/O容量，保证I/O活动的峰值不会消耗服务器的整个I/O容量

innodb_io_capacity

-- 在空闲期间(不修改数据库页的时间段)限制缓冲区刷新，值是已设置 innodb_io_capacity的百分比，默认是100，须定义小于100的值

-- 该参数有助于延长固态存储设备的寿命，副作用是长时间空闲之后的较长关机时间，以及在服务器发生故障时的较长恢复时间。

innodb_idle_flush_pct

14,保存和恢复缓冲池状态

-- 默认为25，关机时存储的最近使用的页面的百分比,存储在文件ib_buffer_pool里，文件名和位置可以用innodb_buffer_pool_filename修改，存储的状态，在mysql服务器重启时加载

innodb_buffer_pool_dump_pct

-- 开启关机存储缓冲池状态，启动时候执行mysqld --innodb-buffer-pool-load-at-startup=ON;加载(默认开启)

innodb_buffer_pool_dump_at_shutdown=ON

-- 联机online保存和加载缓冲池状态

-- 保存

SET GLOBAL innodb_buffer_pool_dump_now=ON;

-- 查看保存进度

SHOW STATUS LIKE 'Innodb_buffer_pool_dump_status';

-- 加载

SET GLOBAL innodb_buffer_pool_load_now=ON;

-- 查看加载进度

SHOW STATUS LIKE 'Innodb_buffer_pool_load_status';

-- 终止加载操作

SET GLOBAL innodb_buffer_pool_load_abort=ON;

15，核心文件排除缓冲池页

-- 核心文件记录正在运行的进程的状态和内存映像，默认为on，配合操作系统的支持madvise() MADV_DONTDUMP，是否产生核心文件，以及核心文件是否包含缓冲池页，根据具体情况而定

innodb_buffer_pool_in_core_file -- 是否向核心文件写入缓冲池页

core_file

15，并发线程数

-- 限制客户端连接到innodb的并发线程的数量，开启后，执行线程数量达到这个限制，额外的线程会休眠，休眠时间由innodb_thread_sleep_delay决定

-- 当innodb_thread_concurrency>0时，允许执行单个SQL语句期间发出的多个请求进入innodb，而不用遵守innodb_thread_concurrency的设置，允许以最小的开销重复调度线程

innodb_thread_concurrency

-- 线程休眠时间:微妙，在innodb_thread_concurrency设置为0时，无效

innodb_thread_sleep_delay

--允许线程休眠的最大时间，设置后innodb动态向上和下调整休眠时间，在innodb_thread_concurrency设置为0时，无效

innodb_adaptive_max_sleep_delay

-- 全局总票证数量，默认5000，(线程有权进入innbodb后，会获得一个票证。）

innodb_concurrency_tickets

16，配置后台InnoDB I/O线程数

-- I/O后台请求线程数，默认是4，取值范围1-64，只能在my.cnf配置，不能动态更改。每个线程数最大可处理256个挂起的I/O请求。

-- 通过SHOW ENGINE INNODB STATUS查看数量，以调整提高性能，linux下默认使用异步I/O，改变了这些线程提高服务的方式

innodb_read_io_threads

innodb_write_io_threads

-- 异步I/O参数，默认开启状态，linux下使用需要libaio库，原理是查询线程直接向操作系统发起I/O请求，消除了后台线程数量的限制。

-- 缺点是在I/O密集的系统，无法控制一次发送到操作系统的I/O写请求的数量，导致并行请求过多

-- 如果操作系统中的异步I/O子系统出现问题，导致InnoDB无法启动，可以设置innodb_use_native_aio=0启动。

-- 在tmpdir位置、tmpfs文件系统、，以及不支持tmpfs上异步I/O的Linux内核等情况下，启动默认禁用异步I/O

innodb_use_native_aio

17，配置InnoDB I/O容量

-- 变量定义了innodb可用的总体I/O容量，用于所有活动的缓冲池实例之间平均分配,应将其设置为系统每秒可执行的I/O操作数（IOPS）左右

-- 默认值200，通常，100左右的值适用于用户级存储设备，例如高达7200 rpm的硬盘驱动器

-- 对于低端SSD，默认设置200通常就足够了。对于更高端的总线连接SSD，请考虑更高的设置，例如1000。不建议大于20000

innodb_io_capacity

-- 检查点刷新变量，默认开启，开启后在检查点发生I/O活动时忽略innodb_io_capacity设置，禁用可以使其遵循innodb_io_capacity设置

innodb_flush_sync

-- I/O最大容量变量，如果刷新活动落后，innodb会以高于innodb_io_capacity配置刷新，通常为innodb_io_capacity的两倍或者值2000，不能设置低于innodb_io_capacity的值

-- 单个常规磁盘驱动器，建议将其设置为200到400之间。对于高端的总线连接SSD，请考虑更高的设置，如2500

innodb_io_capacity_max

18，配置自旋(spin)锁轮询

-- MySQL 8.0.16后可配置。多个线程过于频繁地轮询共享对象（如互斥锁或rw锁）可能会导致“cache ping pong”.

-- 通过设置该值，强制轮询之间的随机延迟来取消轮询活动的同步化，从而最小化了这个问题。随机延迟被实现为自旋等待循环

-- 自旋等待循环的持续时间由循环中发生的暂停指令数决定。该数字是通过随机选择一个从0到但不包括innodb_spin_wait_delay值的整数，然后将该值乘以50而生成的

-- 如：设置为6，从以下范围随机选择一个整数：

{0,1,2,3,4,5}

所选整数乘以50（暂停指令值），得到六个可能的暂停指令值之一：

{0,50,100,150,200,250}，作为自旋等待循环的持续时间

innodb_spin_wait_delay

-- MySQL 8.0.16引入变量控制暂停指令值的大小，将innodb_spin_wait_delay*50减少到innodb_spin_wait_delay*5

-- 较小的暂停指令值适用于具有相对较长的暂停指令的处理器体系结构

innodb_spin_wait_pause_multiplie

19，配置清除线程

-- 默认为4，最大值为32.如果DML操作集中在少数几个表上，请将innodb_purge_threads设置为较低，这样线程就不会为了访问繁忙的表而相互竞争,涉及较大的对象值，可增加设置值

-- 清除线程还负责阶段撤销表空间，可通过innodb_purge_rseg_truncate_frequency配置清除截断频率

innodb_purge_threads

-- 从MySQL 8.0.26开始默认为0，没有最大清除滞后。否则，如果超过innodb_max_purge_lag，则清除工作将自动在可用的清除线程innodb_purge_threads之间重新分配

-- MySQL 8.0.14之前计算清除延迟公式(purge lag/innodb_max_purge_lag - 0.5) * 10000

-- MySQL 8.0.14后计算清除延迟公式(purge_lag/innodb_max_purge_lag - 0.9995) * 10000

-- 可通过SHOW ENGINE INNODB STATUS;中显示的History list length 20得到延迟数值

innodb_max_purge_lag

-- 变量指定当超过innodb_max_purge_lag阈值时施加的延迟的最大延迟（以微秒为单位）

innodb_max_purge_lag_delay

-- 清除批处理大小，定义从历史记录列表中清除一批解析和处理的撤消日志页数。默认值为300。按innodb_purge_batch_size/innodb_purge_threads得到的页数分配配每个清除线程

-- 该参数还定义了通过撤消日志每128次迭代清除释放的撤消日志页的数量

innodb_purge_batch_size

20，为InnoDB配置优化器统计信息

-- 默认开启持久优化器统计信息，在CREATE TABLE and ALTER TABLE中使用STATS_PERSISTENT, STATS_AUTO_RECALC, and STATS_SAMPLE_PAGES配置统计信息可覆盖此参数

innodb_stats_persistent=ON

-- 1是默认启用状态,0为禁用，用于控制当表的行数变化超过10%时，是否自动计算统计信息。即使启用该参数，后台异步统计也会有几秒延迟，可通过ANALYZE TABLE语句重新计算统计信息

-- 而添加或删除索引不受该参数影响，会立刻更新统计到mysql.innodb_index_stats表

innodb_stats_auto_recalc

-- 在CREATE TABLE and ALTER TABLE中使用STATS_PERSISTENT, STATS_AUTO_RECALC, and STATS_SAMPLE_PAGES配置统计信息，

-- 或者修改表mysql.innodb_table_stats，mysql.innodb_index_stats后执行flush table 表名；

-- 配置InnoDB Optimizer统计的采样页数(在表每个索引随即抽取)，默认值为20.ANALYZE TABLE时使用，如果统计不准考虑增加该值，统计过慢减少该值。如果无法权衡大小，可减少表索引列数量和-- 表分区数量

innodb_stats_persistent_sample_pages

-- 默认情况下，InnoDB在计算统计信息时读取未提交的数据,但不会包含对于从表中删除行的未提交事务，设置以下参数可将标记删除行的也参与统计。

-- 设置该参数后ANALYZE TABLE在重新计算统计信息时会考虑删除标记的记录。innodb_stats_include_delete_marked是影响innodb表的全局设置，只适用于持久化优化器统计。

innodb_stats_include_delete_marked

21，配置非持久性优化器统计参数

-- 在innodb_stats_persistent=off和使用stats_persistent=0创建或更改单个表时,优化器统计信息不会持久化到磁盘

-- 非持久性优化器统计信息在以下情况下更新：

SHOW TABLE STATUS, SHOW INDEX, 查询INFORMATION_SCHEMA.TABLES 或者INFORMATION_SCHEMA.STATISTICS

或者SET GLOBAL innodb_stats_on_metadata=ON

或者--auto rehash参数启动mysql

或者表是第一次打开

或者InnoDB检测到自上次更新统计数据以来，表的1/16已被修改

-- 配置采样页数(非持久性统计参数），默认值是8，全局变量。过小导致统计不准确，过大导致打开表或执行SHOW table STATUS所需的时间显著减慢。

innodb_stats_transient_sample_pages

22，估算InnoDB表的ANALYZE TABLE复杂度

-- ANALYZE TABLE复杂度公式，结果越大，ANALYZE TABLE执行时间越长，除此外还有深入的公司

innodb_stats_persistent_sample_pages * 表的列数 * 表的分区数量

23，为索引页配置合并阈值

-- 创建表，修改表，创建索引使用以下配置设置合并阀值。

-- 如果删除一行或更新操作缩短一行时，索引页的“页面已满”百分比低于MERGE_THRESHOLD，InnoDB会尝试将索引页与相邻的索引页合并

-- 评估设置的影响使用：SELECT NAME, COMMENT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME like '%index_page_merge%';

MERGE_THRESHOLD=N

24,启用专用MySQL服务器的自动配置

-- 在Docker容器或者专门运行mysql的服务器上可以启用该参数，启用后自动配置这些参数：(服务器硬件值与自动配置的值参考官方文档)

-- innodb_buffer_pool_size，innodb_log_file_size，innodb_log_files_in_group (as of MySQL 8.0.14)，innodb_flush_method

-- 自动配置的设置会在每次启动MySQL服务器时进行评估和重新配置

innodb_dedicated_serve

25，表压缩

-- 创建表使用表压缩，压缩表的页可能比innodb_page_size，压缩工作在innodb_buffer_pool中完成，所以需要占用缓冲池空间

-- 系统表空间不能保存压缩页，压缩只适用于每个表或常规表空间中存储在文件中的表（和索引），所以必须启用 innodb_file_per_table=1

-- 在特定表上测试压缩的另一种方法是，将未压缩表中的一些数据复制到每个表空间的一个文件中的一个类似的压缩表（具有所有相同的索引），并查看生成的.ibd文件的大小。

-- 查看压缩效果，INFORMATION_SCHEMA.INNODB_CMP，INFORMATION_SCHEMA.INNODB_CMP_PER_INDEX中字段COMPRESS_OPS to COMPRESS_OPS_OK.

ROW_FORMAT=COMPRESSED

-- 压缩表制定的压缩页大小，压缩后。必须满足KEY_BLOCK_SIZE=FILE_BLOCK_SIZE/1024，FILE_BLOCK_SIZE大小为32k，64k不支持压缩。而FILE_BLOCK_SIZE大小由innodb_page_size

-- 如果指定ROW_FORMAT=COMPRESSED，则系统会忽略KEY_BLOCK_SIZE设置，默认KEY_BLOCK_SIZE为innodb_page_size的一半，反之指定有效KEY_BLOCK_SIZE值会自动忽略ROW_FORMAT=COMPRESSED

-- 设置最佳KEY_BLOCK_SIZE值的方法，是创建表，并观察表空间文件.ibd大小，另删除表不会降低.ibd大小

-- 建议设置为8k，常规表空间不支持16K压缩页大小

ALTER TABLE KEY_BLOCK_SIZE

-- 索引压缩跟上面表压缩方法一致

-- 启用索引压缩

innodb_cmp_per_index_enabled

-- 压缩级别，调整压缩级别和CPU开销之间的平衡，更高的值允许您将更多数据放入存储设备，但在压缩过程中会增加CPU开销

innodb_compression_level

-- MySQL有时会在页面中保留一些空白空间（填充），以便修改日志很快填满，并且在仍有足够空间避免拆分页面时重新压缩页面。

-- 随着系统跟踪页面拆分的频率，每个页面中剩余的填充空间量会有所不同。在繁忙的服务器上频繁写入压缩表，您可以调整

-- 指定更新压缩表期间压缩失败的截止点

innodb_compression_failure_threshold_pct

-- 允许您调整每个页面内保留的最大空间量，以记录对压缩行的更改，而无需再次压缩整个页面

innodb_compression_pad_pct_max

-- 允许您禁用将重新压缩的页面的图像写入重做日志。当对压缩数据进行更改时，可能会发生重新压缩。

-- 默认情况下启用此选项可以防止在恢复期间使用不同版本的zlib压缩算法时可能发生的损坏。

innodb_log_compressed_pages

26，配置保留文件段页的百分比

-- 默认值是12.5%，变量是MySQL 8.0.26中引入的一个高级特性，它允许定义保留为空页的表空间文件段页的百分比。为将来的增长保留一定百分比的页面，以便可以连续地分配B树中的页面。

-- 修改保留页百分比的能力允许对InnoDB进行微调，以解决数据碎片或存储空间使用效率低下的问题。

innodb_segment_reserve_facto

27，整理表格碎片

-- (数据页)索引叶子节点出现碎片重新整理

ALTER TABLE tbl_name ENGINE=INNODB and ALTER TABLE tbl_name FORCE use online DDL.

或者mysqldump导出表数据，删除表，重建并导入数据

28，用截断表回收磁盘空间

-- 确保innodb_file_per_table是开启的，且该表与另外的表没有外键。当一个表被截断时，它将被删除并在一个新的.ibd文件中重新创建，释放的空间将返回给操作系统

truncate table ...

29，在线ddl配置

-- 在线ddl修改需要依赖的参数，临时日志文件大小

innodb_sort_buffer_size

-- 在线ddl临时日志文件允许的最大值，如果innodb_sort_buffer_size超过了innodb_online_alter_log_max_size，则修改失败：DB_ONLINE_LOG_TOO_BIG

innodb_online_alter_log_max_size

30，打印死锁信息到错误日志

-- 设置为on，SET GLOBAL innodb_print_all_deadlocks=ON;

innodb_print_all_deadlocks

31，故障排查

-- 在进行故障排除时，通常最好从命令提示符运行MySQL服务器，而不是通过mysqld炣safe或Windows服务运行MySQL服务器。

-- 然后您可以看到mysqld打印到控制台的内容，因此您可以更好地了解发生了什么。在Windows上，使用--console选项启动mysqld，将输出指向控制台窗口。

-- 场景数据页损坏，出现SELECT * FROM tbl_name statements 严重错误或者 InnoDB background后台进程意外退出

-- 设置为1后，强制恢复启动，1失败可以尝试从2...递增，为4或更高可能导致数据永久损坏，更高的值包含更低的值的功能。建议启动前所有数据备份

[mysqld]

innodb_force_recovery = 1

32，锁等待超时回滚

-- 锁定等待超时导致InnoDB回滚当前语句（等待锁定并遇到超时的语句）

innodb-rollback-on-timeout=on

33,复制配置

-- master保证持久性和一致性参数

innodb_flush_log_at_trx_commit=1

-- 0表示mysql不控制操作系统刷新binlog，1表示mysql调用文件系统的刷新立刻刷到磁盘

sync_binlog=1

-- slave配置

--skip-log-bin -- 设置为不记录主机过来的的binlog日志

--log-slave-updates=OFF -- 开启后记录主机过来的binlog日志，并让slave自己也可以成为其他备机的主机

-- 如果slave不用做切换或者作为其他slave的主机，只需要master创建复制账户即可

-- 开启复制前，确保在备机上禁用事件，避免事件在备机运行

event_scheduler=OFF or DISABLED

34，gtid复制配置

-- 二进制文件达到最大max_binlog_size，如果设置了IGNORE_ERROR，则会在服务器上记录错误并停止二进制记录；如果设置了ABORT_SERVER，则会关闭服务器。

binlog_error_action

-- 压缩线程名称，服务器禁用binlog时，执行了指定数量的事务后，周期性对mysql.gtid_executed压缩，多行gtid合并成一行

-- 开启binlog情况下，二进制日志循环时压缩，不使用线程thread/sql/compress_gtid_table

-- SELECT * FROM performance_schema.threads WHERE NAME LIKE '%gtid%'查看，而不是SHOW PROCESSLIST(不展示)

thread/sql/compress_gtid_table

-- 开始压缩的事务数量和压缩率控制参数,该值设置为0意味着线程永远不会唤醒，MySQL8.0.17后建议禁用,因为用innodb/clone_gtid_thread替代了它

gtid_executed_compression_period

35，将一个主机的不同库和表复制到不同备机配置

-- 在每个备机使用--replicate-wild-do-table而不是replicate-do-db，后者在statement-format和mixed-format格式的复制下是不安全的。除非是row-format格式

Replica 1 should use --replicate-wild-do-table=databaseA.%.

Replica 2 should use --replicate-wild-do-table=databaseB.%.

Replica 3 should use --replicate-wild-do-table=databaseC.%.

36，提高复制性能

-- 此系统变量控制从日志文件读取的最小数据量，并且增加它可能会减少文件读取和I/O在操作系统当前未缓存文件数据时停止。

-- 变量为从二进制日志和中继日志文件读取的每个线程分配了一个大小为该值的缓冲区，包括源上的转储线程和副本上的协调线程

rpl_read_size

-- 禁用不作为源主机的备机重放binlog产生日志

log_slave_updates

37,监控复制延迟

-- SHOW REPLICA|SLAVE STATUS输出的Seconds_Behind_Master字段不是正确的，所以要用性能架构下的实时表

performance_schema.replication_applier_status_by_coordinato

performance_schema.replication_applier_status_by_worker