mysql - innodb 知识点整理

整理自《MySQL技术内幕 InnoDB存储引擎 第2版》

线程及作用

• Master Thread : 脏页回写，合并insert buffer, undo 页回收。
• IO Thread: innodb 使用了很多的AIO, 要有线程来接收结果。
  分类有 read, write, insert buffer, log Thread
  innodb_read_io_thead, innodb_write_io_thead 增大到4个
• purge Thread : 回收不再使用的undo页。
• page Cleaner Thread : 刷新脏页

内存

• 内存分部
  
  主要是数据页，索引页，insert buffer, 自适应哈希，锁信息，还有重做日志。

• 页面转换算法采用LRU算法，但是为了防止全表扫描导致热点数据被换出, 每次新来数据都是插入在中间。使用了LRU参数 innodb_old_block_pct，innodb_old_block_time是控制过多久换到热点数据。

• 脏页刷新机制
  sharp flush 将所有脏页刷新, fuzzy flush 将部分脏页刷新（checkpoint）
  checkpoint 机制
  作用：加快数据恢复，当缓存不够时可以刷新脏页，redo日志不够时帮忙刷新脏页。
  刷新时机：

1. master 线程每1秒或10秒刷新,
2. LRU 要保持100个空闲页，当少于100时会进行刷新
3. Async sync 刷新, 当redo log达到 75%,90% redo_log_size 时，分别会进行 Async sync
4. 脏页多，可以使用innodb_max_dirty_pages_pct控制脏页过多。

特性

插入缓冲 ： 解决随机写问题，转化成顺序写。只能针对非聚集且非唯一索引，为什么（唯一索引需要查询，又变成了随机查询，无意义）。
二次写：解决异常导致的页损坏问题。
自适应hash：加快等值查询，条件查询达到100次 ，访问了N=页记录*1/16
异步IO:随机读转化成顺序读
刷新邻接页：随机写优化成顺序写

底层数据结构

compact : 可变长度列表，null值列表，5字节头部，6字节事务ID, 7字节回滚指针，6字节唯一主键（如果有），数据
redundant: 长度累加列表，6字节头部（有一个n_fields是10bit 表示字段个数，可以看出最多是1023个），6字节事务ID, 7字节回滚指针，6字节唯一主键（如果有），数据

日志

慢查日志，参数 long_query_time 可以设置慢查的最短时间
binlog 作用：恢复，同步（主从同步），审计
格式
row：每条数据具体变更
statement：sql语句
mix：默认还是statement, 特殊条件会变成row (ndb, 使用不确定方法，使用insert delay, 使用udf函数，使用临时表)

数据库优化

索引：B+树介绍，聚集，非聚集区别，左前缀原则，选择性，主键要小
索引无法命中情况：
联合索引失效情况

其他失效情况：

• 在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描
• mysql在使用不等于（！=或者<>）的时候无法使用索引会导致全表扫描
• is null 也无法使用索引
• 字符串不加单引号索引失效

冷热库分离，为热库减肥，提升查询与存储效率。

联合索引设计原则：业务使用到的，选择性低的不要，在业务使用的情况下尽量把选择性高的排在前面。
分表：水平分表，纵向分表，如何使用redis, es 优化查询效率和非主键查询模式。
小key：主键要小，要自增
查询优化：批量分表查询怎么做（先将所有key hash, 同一张表数据只要查一次），遍历数据库时使用 id + limit 方式加快遍历。
explain：分析sql过程，优化执行。https://blog.csdn.net/weixin_36586564/article/details/104015533

事务及隔离级别

ACID, 原子性，一致性，隔离性，持久性
隔离级别
读未提交，读已提交，可重复读，串行化。
脏读，不可重复读，幻读
重复读使用快照读，MVCC，新增事务ID,删除事务ID.
幻读，引入间隙锁。