20190728-再读MySQL技术内幕 InnoDB存储引擎 第2版

• Mysql是基于配置的，例如filesort，在内存中做额外排序，有相应的字段（max_length_for_sort_data，默认1024字节）设置排序内存大小。

• InnoDB引擎支持事务，支持非锁定读，即默认读取操作不会产生锁。

• 多版本并发控制MVCC

• 使用next-key locking避免幻读

• 插入缓冲 insert buffer

• • 物理页的组成部分，同数据页
  • 目标是非聚集且不唯一的索引的插入操作，在缓冲池中直接插入，不在则放入Insert Buffer，然后以一定的频率进行Insert Buffer和辅助索引叶子节点的Merge操作（将多个插入操作，合并到一个操作中（因为在一个索引页中）），提高了非聚集索引插入的性能
  • 辅助索引不能唯一，因为需要查找索引来判断插入记录的唯一性，这样Insert Buffer就失去意义了
  • change buffer:insert buffer的升级，对inser、delete、update、都进行缓冲；
• • • inser buffer
    • update buffer：分两步：1.标记删除；2.真正将记录删除；
    • delete buffer：对应update buffer的第一步
    • purge buffer：对应update buffer的第二步
• • Insert Buffer的数据结构是一颗全局的B+树（Mysql 4.1后），负责对所有表的辅助索引进行Insert Buffer。
• • • 非叶子节点：search key9字节（space4字节表空间id，marker1字节，兼容老版本insert buffer，offset4字节，页所在的偏移量）；
    • 叶子节点：space4字节，marker1字节，offset4字节，metadata4字节，secondary index record（第五列开始是插入记录的各个字段）
    • insert buffer bitmap标记每个辅助索引页的可用空间
    • merge insert buffer时机：
• • • • 辅助索引页被读取到缓冲池时
      • insert buffer bitmap页追踪到该辅助索引页已无可用空间时
      • master thread
• • 存放在共享表空间中，默认ibdata1

• 二次写 double write：实现数据页的可靠性

• • 16kb的数据页，可能发生页的写断裂，部分写失效（partial page write）。因为linux文件管理系统读取的大小事4kb。磁盘数据的最小单位是扇区512字节。
  • 发生写失效，重做日志无法恢复，因为页本身发生了损坏，需要页的一个副本进行还原，再应用重做日志
  • 组成：1. 内存的doublewrite buffer 2mb；2.物理磁盘共享表空间连续的128个页，2个区，2mb
  • 实现：1. 脏页复制到doublewrite buffer；2.将doublewrite buffer分两次每次1mb写入共享表空间的物理磁盘；（doublewrite页连续，此过程是顺序的，开销不大）；3.将doublewrite buffer中页写入各个表空间文件中，此过程是离散的。

• 自适应hash索引 adaptive hash index：对于数据量的应用来说，没啥大的用处

• 预读 read ahead

• 异步IO（AIO）:异步、合并IO请求（连续的页访问）

• 聚集索引：未定义主键时，生成6byte的ROWID为主键

• InnoDB体系结构

• • Master线程：保证数据一致性，合并插入缓冲等
  • IO线程：write，read，insert buffer，log io thread
  • Purge线程：回收undo页
  • Page Cleaner线程：刷新脏页

• 内存

• • innodb_buffer_pool：数据页、索引页、插入缓冲、锁信息、数据字典信息、自适应hash索引；可以多个实例
• • • innodb_buffer_pool可以多个实例
    • 缓存页默认大小是16kb
    • LRU List、Free List、Flush List：新读取的页，插入到LRU List的midpoint(5/8，尾端的3/8)位置；避免全表扫描对链表的影响
    • 在LRU List中被修改的页，称为脏页（dirty page，缓冲池和磁盘上的页数据不一致），通过check point机制进行刷新到磁盘
    • 脏页即存在与LRU列表，也存在于Flush列表: lru列表用来管理缓冲池中页的可用性，Flush列表用来将页刷新回磁盘，二者互不影响
• • redo log buffer：对页的物理操作
  • innodb_additional_mem_pool_size
  • 重做日志缓冲（redo log buffer）innodb_log_buffer_size控制大小，默认8MB（一般情况下足够）。
• • • master thread每秒刷新重做日志
    • 每个事物提交重做日志缓冲刷新到日志文件
    • 重做日志缓冲剩余空间小于1/2时，刷新
• • 额外的内存池

• check point技术：每秒页发生变化进行刷新到磁盘，性能消耗巨大。在特定的时间、条件、选择脏页刷新到磁盘

• write ahead log日志先行

• binlog

• • binlog_format:
• • • statement:逻辑日志
    • row：记录表的行更改情况；（一般设置项）
    • mixed：混合，默认采用statement，在一些情况下，使用row

• 表空间文件：tablespace，

• • 共享表空间文件：ibdata1文件。
  • 独立表空间文件：数据库名/表名.ibdata
  • 独立表空间开启：innodb_file_per_table=on

• 重做日志文件：

• • 至少一个日志组goup，一个组内至少2个文件（ib_logfile0,ib_logfile1）
  • 写入单位是512字节，linux文件管理系统的最小单位是4kb，磁盘存储的最小单位是扇区-512字节，所以保证了写入是原子性，要么成功，要么失败的。
  • binlog是mysql server层面的二进制日志，记录的是一个事务的具体操作内容，在事务提交前进行提交一次；而重做日志，是innodb引擎的本身的事务日志，在事务进行的过程中，不断的有重做日志被写入到重做日志文件中。

• InnoDB逻辑存储结构

• • 表空间:存放数据页，索引页，和插入缓冲bitmap页；共享表空间存放：回滚信息，插入缓冲索引页，系统事务信息，二次写缓冲
  • 段：数据段，索引段，回滚段
  • 区/簇：64个连续的页组成，所以大小为1MB。64*16
  • 块/页：InnoDB引擎磁盘管理的最小单位；常见的类型包括：数据页，索引页，回滚页，事务页
  • 行：最多允许存放16kb/2-200行数据，也就是7992行
  • 行数据格式：
  • 数据页结构：
• • • File Header
    • Page Header
    • 最大记录，最小记录
    • User record
    • Free Space
    • Page Directory
    • File Trailer
• • 索引与算法
• • • 平衡二叉树，红黑树
    • B+树：一个节点，就是一个页，页内的记录行以双向链表的形式维护，行记录又被slot进行管理分段
    • Multi-Range Read（MRR）优化：将随机访问变为较为顺序的数据访问，适用于range，ref，eq-ref。在回表之前，对主键排序；
    • Index Condition Pushdown（ICP）优化：在取出索引的同时，判断是否可以进行where条件过滤，也就是将where条件的部分过滤操作放在了存储引擎层。
• • 锁
• • • latch：保证并发线程操作临界资源的正确性，没有死锁检测机制
    • lock的对象是事务，锁定数据库中的对象，如表、页、行，有死锁检测机制
    • 锁的类型
• • • • 共享锁 s lock
      • 排他锁 x lock
      • 意向锁（共享is、排他ix）对于ix，对is是兼容的，x，s才不兼容
• • • 一致性非锁定读：MVCC（multi verison concurrent control）
• • • • 对于rc隔离级别，读取undo log最新的数据行版本
      • 对于rr隔离级别，读取事务开始前undo log中记录的最新版本
• • • 一致性锁定读
• • • • select ... for update
      • select ... in share mode
• • • 行锁算法
• • • • record lock
      • gap lock
      • next-key lock 为rr级别下使用的算法，避免幻读
• • • 锁问题：
• • • • 脏读
      • 不可重复读：update，delete
      • 幻读：insert
      • 丢失更新
• • • 锁超时（阻塞）
• • • • innodb_lock_wait_timeout
• • • 锁升级：
• • • • 行级锁--》页级锁--》表级锁。
      • innodb不存在锁升级的问题。不是根据每一行记录产生行锁，而是根据每个事务访问的每个页对锁进行管理，采用位图的方式，因此锁住一个页内的一行记录、或者多行记录，开销是一样的。
• • 事务控制
• • • 事务特性
• • • • 原子性
      • 一致性
      • 隔离性
      • 持久性
• • • 事务的隔离级别
• • • • 读未提交
      • 读提交
      • 读重复
      • 串行化
• • • 事务的实现
• • • • redo log保证事务的原子性和持久性,force log at commit
      • undo log保证事务的一致性。在共享表空间中采用段（undo segment）的形式进行管理。每个undo segment记录了1024个undo log segment，1.1版本后支持128个undo segment。
• • • • • 事务开始时，事务在undo log segment中申请，并分配页。
        • 在写入undo log时，同样需要写入重做日志
        • 事务提交时，将undo log放入“列表”，后面进行purge操作；判断undo log所在的页是否可以重用，若可以分配给下个事务使用。
• • • • innodb_flush_log_at_trx_commit用来控制重做日志刷新到磁盘的策略。
• • • • • 1：事务提交必须调用一次fsync操作
        • 0：事务提交不进行写入重做日志操作，在master thread中完成，master thread 1秒钟提交一次
        • 2：表示事务提交时将重做日志写入重做日志文件，但仅写入文件操作系统的缓存中，不进行fsync操作。
• • • • purge过程：
      • group commit

mysql各版本特性

转载于:https://my.oschina.net/xjt2014/blog/3080829