• 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各版本特性