MYSQL INNODB 相关参数

1.innodb_buffer_pool_size (物理内存80%)

innodb_buffer_pool_size
├── 数据页缓存 (约 70-80%)
│   ├── 表数据页
│   ├── 主键索引页
│   └── 二级索引页
├── 更改缓冲区 (最多25%)
├── 自适应哈希索引 (自动管理)
├── 锁信息
└── 内部数据结构

# 核心参数
innodb_buffer_pool_size = 16G
innodb_buffer_pool_instances = 8
# LRU 管理
innodb_old_blocks_pct = 37  # Old Sublist 占比
innodb_old_blocks_time = 1000  # 页在Old列表停留时间(ms)
# 刷新策略
innodb_max_dirty_pages_pct = 90  # 最大脏页百分比
innodb_max_dirty_pages_pct_lwm = 10  # 低水位线
# 预读
innodb_read_ahead_threshold = 56  # 线性预读阈值
innodb_random_read_ahead = OFF  # 随机预读
#预热
启用 innodb_buffer_pool_dump_at_shutdown 和 innodb_buffer_pool_load_at_startup innodb_buffer_pool_dump_pct

2.innodb_log_buffer_size 日志缓存innodb_flush_log_at_trx_commit 日志缓存写到文件的方式0每秒刷盘。1提交刷盘。2折中方案 MySQL 崩溃：无丢失 OS 崩溃：最多丢失 1 秒数据3. innodb_redo_log_capacity 日志文件动态调整innodb_log_file_size 固定日志文件大小 4.innodb_flush_method 控制 InnoDB 如何刷新数据到磁盘

5.show engine innodb status\G

----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 0   #总分配内存
Dictionary memory allocated 702565  #数据字典内存
Buffer pool size   33044  #有多少个页
Free buffers       21942  #有多少空白页
Database pages     11102  #LRUlist有多少页在使用
Old database pages 4078  #old页
Modified db pages  0  #脏页
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 709, not young 820
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1580, created 9524, written 11088
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 11102, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]

6.change buffer

innodb_change_buffering
Change Buffer 最大大小 = 
    innodb_buffer_pool_size × innodb_change_buffer_max_size / 100
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 138401, node heap has 0 buffer(s)
Hash table size 138401, node heap has 0 buffer(s)
Hash table size 138401, node heap has 0 buffer(s)
Hash table size 138401, node heap has 0 buffer(s)
Hash table size 138401, node heap has 0 buffer(s)
Hash table size 138401, node heap has 0 buffer(s)
Hash table size 138401, node heap has 0 buffer(s)
Hash table size 138401, node heap has 0 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s

7.临时表空间

tmpdir 存储：
├── 临时表文件（MyISAM/MEMORY引擎）
│   ├── #sql_xxx.MYD
│   ├── #sql_xxx.MYI
│   └── #sql_xxx.frm（5.7及之前）
├── 排序文件（sort buffer 溢出）
│   ├── MYxxxxxxxx
├── 分组临时文件
├── UNION 临时文件
├── 某些 JOIN 的临时文件
└── 其他引擎的临时文件
innodb_tmpdir 存储：
├── 在线 DDL 临时文件
│   ├── 重建表的中间文件
│   ├── 索引创建临时文件
│   └── 列修改临时文件
├── 临时表空间文件（如果使用 TempTable）
│   ├##ibtmp1（如果指定位置）
└── 仅限 InnoDB 的内部临时文件
# innodb_temp_tablespaces_dir 目录
/var/lib/mysql-temp/innodb_temp/
├── ibtmp1          # 固定文件名，存储临时表数据
└── temp_*.ibt      # 临时表空间文件
# innodb_tmpdir 目录
/fast-ssd/innodb_tmp/
├── #sql_xxx_0.ibd  # 临时创建的文件
├── #sql_xxx_1.ibd
├── tmp_sort_xxx    # 排序操作临时文件
└── tmp_ddl_xxx     # DDL操作临时文件
1. 查询开始执行
   ↓
2. 创建临时表（内存中）
   ├── 如果使用 TempTable 引擎 → temptable 内存池
   ├── 如果使用 MEMORY 引擎 → MEMORY 引擎内存池
   └── 如果使用 InnoDB 引擎 → InnoDB 缓冲池
   ↓
3. 数据填充
   ↓
4. 达到内存限制
   ↓
5. 溢出到磁盘
   ├── TempTable + mmap=ON → tmpdir 的 mmap 文件
   ├── TempTable + mmap=OFF → #innodb_temp 表空间
   └── MEMORY 引擎 → tmpdir 的 MyISAM 文件
备注:避免使用 InnoDB 临时表防止临时数据占用过多的innodb buffe

溢出到磁盘的条件总结

条件：大小超过内存限制

创建表空间语法：
create tablespace tablespace_name
add datafile 'file_name'
[FILE_BLOCK_SIZE = value] 只能在压缩表的时候才需要
[engine [=] engine_name]

[mysqld]
# Undo Log 优化配置
innodb_undo_tablespaces = 8
innodb_max_undo_log_size = 1073741824  # 1GB
innodb_undo_log_truncate = ON
innodb_undo_directory = /mnt/ssd/undo_logs  # 如果有多块盘
innodb_purge_threads = 4
innodb_rollback_segments = 128
# 监控长事务
long_query_time = 2