文章目录
减少IO次数
为了防止数据丢失：sync_binlog=1，innodb_flush_log_at_trx_commit=1 事务提交立即写盘（redo log与binlog 配置 “双1”）。对于写盘，mysql提供一些优化：
redo log buffer 和 binlog buffer都是先从内存中存到os page cache上，再将page cache持久化。添加缓冲，减少IO压力
binlog_group_sync_commit_delay 延迟多久写盘，多等几个一起写；binlog_group_sync_commit_no_delay_count 等多少个一起写
sync_binlog的优先级高于上面这两个配置
等几个事务一起写redo log，就是组提交 group commit
组提交 group commit
一组事务里，选个leader事务，等leader要写盘的时候会拿着当前的刻度LSN（日志逻辑序列号）去写，把redo log里面小于LSN的日志都持久化。那其他事务就直接执行完成，不需要再走一趟写盘了，就减少了写盘的次数。
LSN：是单调递增的，用来对应redo log的一个个写入点。每次写入长度为length的redo log，LSN的值就会加length。LSN也会在数据更新时写入数据页中，避免redo log重复更新数据。比如：将change buffer中的变更应用到数据页时，innodb会比较LSN看是不是已经应用过了。
WAL机制
WAL机制主要得益于：
- redo log、binlog顺序写，顺序写比随机写速度快
- 组提交机制，降低了磁盘的IOPS（每秒输入输出次数）消耗
redo log与binlog 配置双非1场景
目标：减小IO压力
- 业务高峰期
- 备库延迟，为了让备库尽快赶上主库
- 批量导入数据
表数据删一半，表大小不变？
innodb_filer_per_table
off : 表数据放在系统共享空间下，和数据字典放在一起；共享空间中，即使删表，空间也不会回收
on: 单独存在.idb文件中，更好管理。比如drop table 直接删除整个文件就可以了
删除数据时，表空间没有变小：复用
- 删除一行数据时，数据会标记为删除；如果下次在这个位置插入新数据，就直接复用 // 空洞
- 更新数据相当于在旧位置删除数据，在新位置插入数据；删除旧数据同上 // 空洞
- 页分裂时也会产生空洞
- 删除数据页时，这个数据页可以被拿到任何位置复用 // 空洞
- 删除整个表时，所有数据页都会被标记为可复用 // 空洞
如何移除空洞，减小表空间
重建表 alter table t或者用gh-ost工具
- optimize table t = alter table t + analyze table t
alter table t这个DDL在以前不是online的（意味着重建表过程中不能读写表），因为创建过程中临时表存在server层；后来引入online DDL：插入临时表时，记录操作日志到row log中，重建表完成后重放，此时重建表是在引擎层（inplace）完成的 // 不需要重建整个表，二十在原表数据文件上直接修改
- 全文检索、空间检索（地理空间）是inplace但不是online的
刷脏页机制
select * from t where id=1;只查一行数据也执行的很慢或者抖了一下，可能原因：
一：等MDL锁
二：等行锁
三：在刷脏
- 查询语句需要内存时，需要淘汰的数据是脏页，就需要刷盘 // buffer pool剩余不足50%
- 刷脏会占用磁盘IO，影响更新语句，然后就感觉卡顿了一下
落盘的时机
- 事务commit
- sql正常关闭
- buffer pool剩余不足50%
- redo log的check point更新
- 后台定时落盘
刷盘速度
innodb_io_capacity 配置权力刷脏时可以刷多快，这个值最好是磁盘的IOPS；
刷脏太慢的原因
- 内存脏页太多
- redo log写满
所以脏页的比例和redo log写满的速度会影响刷盘的速度
优化
准备刷一个藏页时，如果邻居也是脏页（蔓延）就大家一起刷。
innodb_flush_neighbors=1开启连坐；0关闭，SSD（固态硬盘）不用开，因为SSD刷的快，不开可以减少sql语句执行等待的时间。