InnoDB

一. 基础：体系结构

1.1 运行模式

mysql支持的模式：单进程多线程，集群模式是多进程实例，单个数据库文件

oracle是多进程，单个数据库文件。

1.2 mysql结构

连接池
管理服务和工具组件
sql接口组件
查询分析器组件
优化器组件
缓存组件
插件式存储引擎
物理文件

存储引擎是基于表的而不是基于数据库。存储引擎是底层物理结构的实现。

1.3 存储引擎

1.3.1 InnoDB

设计目标：面向在线事务处理(OLTP)的应用。

特点：行锁设计，支持外键。支持类似oracle的非锁定读，即读操作不产生锁。

实现：

将数据放在一个逻辑的表空间中。
通过多版本并发控制(MVCC)来获得高并发性。
实现了SQL标准的4种隔离级别，默认为REPEATABLE级别。通过next-key-locking的策略避免幻读。
插入缓存，二次写，自适应哈希索引，预读等提高性能和高可用。
使用聚集式存储，每张表中的数据的存储都是按照主键的顺序进行存放的。没有显式指定主键的就为每一行生成一个6字节的ROWID，作为主键。

1.3.2 MyISAM

设计目标：面向OLAP数据库应用

特点：表锁设计，不支持事务，支持全文索引。缓冲池只缓存索引文件。

实现：

MYD文件存储数据，MYI文件存放索引。

NDB

1.4 连接mysql

1.4.1 TCP/IP

权限校验：在连接mysql的时候，mysql会先查一张权限表，里面记录了使用某个用户登录的时候，这个用户可以从哪些IP连接这个mysql实例。

改视图在mysql架构下，表名为use

  
use mysql
select * from user

1.4.2 命名管道/共享内存

1.4.3 UNIX域套接字

二. INNODB存储引擎

存储引擎里面有几大块内存，组成内存池。这些数据包括，缓存磁盘数据（预读），重做日志缓存。

2.1 线程模型

master thread ：主要负责将缓冲池数据异步刷到磁盘。
io thread：IO操作。SHOW ENGINE INNODB STATUS，可以查看io线程的详情。
purge thread：redo页的释放线程。
clean page thread：后台清理页的线程

2.2 内存模型

2.2.1 缓冲池

InnoDB存储引擎是基于磁盘存储的，记录以页的方式进行设计。同时为了完美契合利用操作系统的页概念，设计的页大小和操作系统的页成倍数关系。大多数操作系统默认为4k, Innodb的页大小为16k。InnoDB读数据和刷数据也是以页为单位的。

读取页采用缓存的操作，刷缓存中的页采用的是定时刷的方式。

  
#显示缓冲池大小
mysql>SHOW VARIABLES LIKE 'innodb_buffer_pool_size'

缓冲池中缓存的数据页类型有：索引页，数据页，undo页，插入缓存，自适应哈希索引

多缓冲池实例

从InnoDB 1.0.x开始支持多个缓冲池实例，每个页根据哈希值平均分配到不同的池中，减少内部竞争，提高性能。原理类似于分段锁，减小锁粒度达到提高并发的效果。

mysql>SHOW VARIABLES LIKE 'innodb_buffer_pool_instance';

2.2.2 缓冲池管理

2.2.2.1 LRU list

mysql中的LRU是一个传统LRU的一个变种，mysql中的LRU数据分为两个部分：热点数据和传统LRU数据。

热点数据

进入了LRU数据队列中，并且成功在LRU队列中存活了一定时间的数据，就会进入热点数据区域。

传统LRU数据

当一个页的数据在缓存池里面发现没有的时候，会先释放队列末尾的数据页，同时读取页数据进入这个传统的LRU数据区域。

热点数据和传统LRU数据通过midpoint进行设置。

  
# 表示传统LRU数据所占的百分比。
mysql> SHOW VARIABLES LIKE 'innodb_old_blocks_pct'
# 表示新的页数据进来以后，在传统LRU数据中存活多久就能进入热点数据区域
mysql> SET GLOBAL innodb_old_blocks_time=1000

当页数据从传统LRU数据进入到热点数据的这个动作称为：page make young

因为innodb_old_blocks_time设置导致没有进入热点数据区域的动作称为page not make young

上面的这两个动作的频率和次数可以通过 SHOW ENGINE INNODB STATUS;进行查看。

当LRU中的页数据被修改以后，这个页数据就会被标记为脏页，脏页任然会存在LRU列表中，但同时也会存在与FLUSH列表中，数据库通过checkpoint机制将这个页数据刷回磁盘中。(类似于CPU高速缓存和主存之间的关系，当高速缓存的行数据和主存中的数据不一致的时候就会被标记为脏行，在高速缓存的LRU过期失效以后就被刷回主存中)。SHOW ENGINE INNODB STATUS;中的modified db page代表的就是缓冲池中的脏页数据。

2.3 重做日志缓冲

上面可以看到Innodb存储引擎除了缓冲区以外，还有缓冲池外和重做日志缓冲。Innodb会将需要重做的日志放到这个内存区域，然后按照一定频率刷到重做日志文件中。一般情况下会每秒钟刷一次，因此这个区域一般不用配置很大，默认通过innodb_log_buffer_size进行控制，默认为8M。

在数据还在缓冲区没刷盘前，为了防止刷盘动作失败后数据丢失，一般都会是先执行是write ahead log的这么一个动作(应该是在进入内存前就需要先写重做日志了把，不然意外断点这种情况的时候怎么办)，先写重做日志。zookeeper，prometheus都会有这么一个动作，在保证重做日志成功后才会进行刷盘动作。

刷日志时机：

Master thread 每秒钟刷一次日志文件
每个事务提交的时候会将重做日志的缓冲区刷到日志文件
重做日志缓冲池剩余空间小于1/2，会刷到日志文件(那个线程？)

2.4 额外缓冲区

2.5 checkpoint

checkpoint就是类似于jvm垃圾回收中的safepoint一样，在某一个节点上面就会进行刷盘的操作，减少内存中的脏页数据。

checkpoint实现的考虑指标：

数据恢复的时候需要的时间一定不能太长 - 要求重做日志的大小要维持在一个合理值
要能保护缓冲池，缓冲池不够用的时候，也就是数据被挤出LRU的时候需要进行刷盘操作（同CPU高速缓存）
能保护重做日志缓冲区，重做日志的缓冲区不够用的时候也需要进行刷盘的动作。

Innodb checkpoint机制具体实现

checkpoint实现无外乎就是，什么时候刷，刷多少，从哪里获取脏页是哪个问题。

2.5.1 LSN (Log Sequence Number)

这是一个8字节的数字，单位是字节。每个页数据都会记录有自己的数据LSN，Checkpoint_LSN，checkpoint机制的实现是依赖lsn实现的。

mysql中主要有两种checkpoint，sharp checkpoint和fuzzy checkpoint。sharp checkpoint用于数据库关闭的时候会把所有的脏页刷盘。性能较低。运行过程主要是fuzzy checkpoint。fuzzy checkpoint主要有以下集中情况。

master thread checkpoint
异步刷，秒级别刷一定比例会磁盘，异步操作
FLUSH_LRU_LIST checkpoint
InnoDb1.1 前是由用户查询线程完成，在mysql 5.6后，InnoDB 1.2.x版本开始，这个操作被单独放在了一个page cleaner线程中进行。用户可以通过innodb_lru_scan_depth这个参数中进行设置。
Async/sync checkpoint
重做日志不可用的时候会执行这个刷checkpoint的动作。有分两种模式，同步和异步。首先根据checkpoint_lsn(最后checkpoint位置的LSN)和最新的数据LSR计算出待刷盘的数据大小，然后根据这个大小跟同步水位和异步水位做对比，看需要采取什么样的刷盘策略。其中异步刷盘的策略不会中断用户操作线程，同步刷盘策略就会中断所有的线程，有点类似jvm中的垃圾回收。
Dirty Page too much checkpoint。
参数由innodb_max_dirty_page_pct进行控制，默认值为75，当缓冲区的脏页数量达到75%的时候就会进行强制的checkpoint。

2.6 master thread

最优最高优先级的线程，内部由多个循环组成，主循环main loop，后台循环background loop，刷新循环flush loop，暂停循环suspend loop。master thread会根据数据库的不同的状态在不同的循环中运行。

2.6.1 主循环

主要分为每秒钟的操作和没十秒钟的操作。

2.6.1.1 每秒钟的操作

事务的重做日志缓冲刷盘到重做日志文件中（一定发生）
合并插入缓冲
缓冲池中的脏页数据进行刷盘(buf_get_modified_ratio_pct脏页比例，是否超过了innodb_max_dirty_page_pct这个设置的参数, 默认为90%)
用户没活动，切换到后台循环

2.6.1.2 每10秒钟的操作

100个脏页刷盘（可能）
触发条件：过去10秒io操作小于200次。
10个脏页刷盘（总是）
合并至多5个插入缓冲（总是）
将事务的重做日志缓冲刷新到磁盘（总是）
删除无用的Undo页。
对表进行了update，delete等操作的时候一些数据页被标记为删除。但因为一致性读的原因，有可能用户的查询还需要读到这些被删除的数据，所以有一些undo也是还有用的，但是只要是没用的undo页，innodb就会马上将它删除掉。

2.6.2 后台循环

当前用户没有活动，或者数据库关闭的时候就会切换到这个循环上来。

删除无用的undo页
合并20个插入缓冲
调回主循环

2.7 性能优化

由于InnoDB最开始是设计为磁盘式数据库的，在代码中的设置的刷页的数量都是固定的，最大100页，但是一些IO密集型的应用，可能会产生每秒钟比100页更多的脏页数据，在今天固态硬盘越来越普及的今天，每秒100页*16k, 1m/s远没达固态硬盘瓶颈。因此从 1.0.x开始，就开始提供innodb_io_capacity的参数进行控制磁盘IO的写入速率。

innodb_io_capacity

合并插入缓冲的时候合并的数量为innodb_io_capacity值的5%
从缓冲区刷新脏页的时候，最多的一次性刷新的脏页数量为innodb_io_capacity页

innodb_io_capacity * 16k = 写入速度，可以根据这个公式，同时根据自己的磁盘的性能设置一个合适的值。

Innodb_max_dirty_pages_pct

当该值大于设定的比例的时候，才会刷100个脏页进磁盘。如果内存很大或者产生脏页的速度很快，那么脏页刷盘的动作就会显得很慢，同时数据库恢复阶段也会需要很久。1.0.x前为90%，后面默认为75%。这个跟jvm的垃圾回收的历程也是类似的。

innodb_purge_batch_size

控制没此full purge的时候回收的undo页数量。

性能判断

略

2.8 关键特性

innodb的关键特性包括如下：

插入缓冲
两次写
自适应哈希缩影
异步IO
刷新邻接页，类似预读，预写

2.8.1 插入缓冲

2.8.1.1 简介

对于非聚集索引的插入或更新，就是除了主键外的二级索引(辅助索引)的插入或者更新操作，插入不是一次性插入到索引页中的，而是先判断插入的页是否在缓冲池中，在的话就直接插入，否则的话就插入到插入缓冲中。然后根据一定的频率和具体的情况，将插入缓冲中的多个插入修改和辅助索引页节点合并，提高非聚集索引插入的性能。

2.8.1.2 产生的原因

对于聚集索引来说，表中的数据页和主键的索引页在逻辑上和物理上都是顺序存放的。这个时候的插入动作在数据页和索引页上都是顺序append的，不需要再查询别的数据页去确认数据的顺序，所以很快。

但是对于非聚集且不是唯一的索引来说，在进行插入的时候，数据页的存放还是按照主键的聚集索引进行存放的，但是对于非聚集索引的插入就不是顺序的了，可能一会儿插入的是索引页的头，一会儿是插入索引页子节点的尾部，所以对非聚集且不是唯一的索引来说插入是离散的，由于不是唯一索引，因此很可能需要离散地插入同一个位置多次。而Insert Buffer就是为了合并这些由于不是唯一索引，当多条插入数据对应的非聚集索引页插入点是同一个的时候只需要插入一次IO就可以了，减少IO，进而提高性能。

2.8.1.3 插入缓冲的缺点

当数据库的IO压力大部分都是写IO的时候，当数据库发生宕机的时候，插入缓冲中将会存在大量的没有合并都非聚集索引中的数据，这个时候恢复的时候就需要很长的时间。（合并插入后依然会？）

2.8.1.4 如何查看插入缓冲的性能提升

可以通过SHOW ENGINE INNODB STATUS;中的INSERT BUFFER AND ADAPTIVE HASH INDEX项中进行查看。

size：已经合并记录页数量。free list len: 空闲列表的长度 seg size : 当前Insert buffer的大小11336 * 64k = 177M

inserts : 插入的记录数 merged recs : 合并的插入记录数量 merges : 合并的次数

可以理解为 merges 为合并后实际发生的插入操作次数， merged recs为合并前一共需要插入的数量。

merges ：merged recs 约等于 1:3，也就是降低了2/3的随机IO。

2.8.1.5 change buffer

Insert buffer的升级版，从Innodb 1.0.x开始引入channge buffer。包含了 Insert Buffer, Delete Buffer, Purge Buffer。同样的它的适用对象也是非聚集非唯一性的索引。

当一个update动作发生的时候，分为两步：

标记数据删除，这里是直接标记在Delete Buffer，稍等合并后再真正操作索引
真正的数据删除，这个才是在Purge Buffer里面发生的

2.8.1.6 合并时机

由上面可以知道当插入或者修改一条数据记录的时候，这个非聚集索引页不在缓冲池的时候，会进入change buffer中，change buffer是什么时候真正地合并到非聚集的索引页中的呢？

目标辅助索引页被读到缓冲池的时候(哪一条线程触发这个动作，哪一条线程就负责合并)
追踪到该辅助索引页没有空间可以用的时候（insert buffer bitmap检测到合并插入后索引页剩余空间会小于1/32就会强制执行合并）
主线程定时任务

2.8.2 两次写

上面的插入缓冲给InnoDB带来的是性能的提升，双写给Innodb带来的就是数据页的可靠性。

2.8.2.1 解决的问题

当存储引擎写刷页数据进磁盘时候，发生了宕机。这个时候16k的页数据，只写了4k。而这个时候如果通过重做日志进行修复的话，重做日志的例如是命令级别的，就是一个数据页中，可以想象由有多个执行命令insert，update啊组成，重做日志无法感知页级别的重做或者损坏。就是无法保证上一个命令在刷盘时候的原子性。这个时候如果直接进行重做日志redo的话，可能那个页的数据本身就有问题了。因此需要一个更高的粒度去保证redo语句执行的时候，这个数据页是正常的。为了保证数据页是正常的，InnoDB便添加了double write机制，保证写过程的数据完整性。

2.8.2.2 具体实现

double wirte由两部分组成，一个是内存的double wirte buffer，大小为2MB，另一部分是物理磁盘中的共享表空间的128个连续页。128*16=2042k=2mb。在刷脏页数据的时候，并不是直接写磁盘，而是先写进这个double wirte buffer中，然后在特定的触发条件下，再将double write buffer以1M顺序写入共享表空间的磁盘文件中，最后再将double wirte buffer中的数据离散地写入各个表空间中。

在恢复的时候，就是先从double wirte buffer中恢复页的副本数据，然后再进行重做日志的恢复。

2.8.3 自适应哈希

原理就是查找B+树的时候，需要根据B+树的高度进行n次查询，生产千万级的数据，一般B+树的高度为3-4，也就是需要3-4次查询。而自适应哈希就是通过对某些数据的访问频率，为热点页建立哈希索引，就是直接hash.get获取热点页数据

2.8.4 异步IO

2.8.5 刷新邻接页

类似于操作系统的预读预写功能，根据最近相关性，当刷一个脏页的时候回去检查同一个区的其他页时候也是脏页，是的话就一起刷了，也是减少IO 的一个优化策略。

MVCC

数据库隔离级别：

读未提交

读提交

可重复读

串行

数据库知识的三大块：索引，锁和事务

mysql提供一致性非锁定读和行级锁支持。

锁类型

共享锁，允许事务读操作
排它锁，允许事务删除或者更新一行数据

意向锁

意向锁即为表级锁

意向共享锁

事务的四个隔离级别：

READ UNCOMMITED

READ COMMITED

REPEATABLE READ

SERIALIZABLE

专业名词

聚集索引和非聚集索引

聚集索引：是以主键创建的索引，此索引的逻辑顺序与数据的实际物理存放顺序一致

优点：范围查询快，因为是物理连续的

缺点：插入慢，因为插入的时候要重新做一次排序

非聚集索引：是以除了主键以外的索引建的索引，也叫二级索引。非聚集索引在创建的时候可以是单列，也可以是多列组成的。此类索引的逻辑顺序与数据的时机物理存放顺序不一致

优点：

本质区别：表记录的排列速顺序和索引的排列顺序是否一致。

回表

非聚集索引查询数据的时候需要先查到聚集索引的key，然后根据这个key如查询真正的数据，这个过程就叫做回表。也就是非聚集索引的查询是要经过两次查询的。

DML DDL DCL TCL

DML (Data Manipulation Lanuage) 数据操作语言

SELECT INSERT UPDATE DELETE MERGE CALL EXPLAIN PLAN LOCK TABLE

DDL (Data Defintion Language) 数据库定义语言

CREATE ALTER DROP TRUNCATE COMMENT RENAME

DCL (Data Control Language) 数据库控制语言

GRANT 授权 REVOKE 取消授权

TCL (Transaction Control Language) 事务控制语言

SAVEPOINT 设置保存点 ROLLBACK 回滚 SET TRANSACTION

唯一索引和普通索引

唯一索引指的是和主键一样

mysql分区

参考

《InnoDB技术内幕》
https://www.cnblogs.com/geaozhang/p/7241744.html
https://cloud.tencent.com/developer/article/1424670
MySQL中MVCC的正确打开方式（源码佐证）