最近看了《MySQL技术内幕InnoDB存储引擎》一书,受益良多,对Mysql InnoDB有了进一步的了解。于是根据自己理解和整理的资料,写了一系列深入InnoDB的文章,其中不少知识来着《MySQL技术内幕InnoDB存储引擎》以及《MYSQL内核:INNODB存储引擎》,感谢这两本书的作者,也向各位推荐这两本书。
这一系列文章都是从MySql的使用和设计的出发,不会涉及源码,希望可以帮助大家更深入理解InnoDB的设计和实现。
概念区分
先明确两个概念,虽然平常我们并不需要严格区分他们
数据库:存储数据的物理操作系统文件或其他形式文件的集合
数据库实例:MySql数据库实例由后台线程以及一个共享内存区组成,负责操作数据库文件。
MySql是一个单进程多线程架构的数据库,MySql数据库实例在系统上表现就是一个进程。
MySql架构
MySql架构图如下
MySql中有以下组件连接池组件 -- Connection Pool
管理服务和工具组件 -- Management Services & utilities
SQL接口组件 -- SQL Interface
查询分析器组件 -- Praser
优化器组件 -- Optimizer
缓冲组件 -- Caches & Buffers
插件式表存储引擎 -- Pluggable Storage Enginess
物理文件 -- Files & Logs
MySQL区别于其他数据库的一个最重要的特点是插件式存储引擎。它是基于表的,而不是数据库。MySql常用存储引擎如下:
MyISAM存储引擎不支持事务
缓冲池只缓存索引文件,不缓冲数据文件
由MYD和MYI文件组成,MYD用来存放数据文件,MYI用来存放索引文件,
InnoDB存储引擎
独立表空间,支持MVCC,行锁设计,提供一致性非锁定读
支持外键,插入缓冲,二次写,自适应哈希索引,预读
使用聚集的方式存储数据,每张表的存储都是按主键顺序存放。
此外还有NDB,Memory,Archive,Federated,Marai等存储引擎。
InnoDB架构
InnoDB架构图如下
以下主要从内存和线程的角度分析InnoDB的架构。
内存池
主要工作:维护所有进程/线程需要使用的多个内部数据结构
缓存磁盘上的数据,方便快速地读取,同时对磁盘文件数据修改之前在这里缓存
重做日志缓存
InnoDB内存池主要有以下部分
缓冲池
InnoDB是基于磁盘存储的,并将其中的记录按照页的方式进行管理。
而缓冲池就是一块内存区域,主要缓冲数据页和索引页。
InnoDB中对页的读取操作,首先判断该页是否在缓冲池中,若在,直接读取该页,若不在则从磁盘读取页数据,并存放在缓冲池中。
对页的修改操作,首先修改在缓冲池中的页,再以一定的频率(Checkpoint机制)刷新到磁盘。
参数:innodb_buffer_pool_size设置缓冲池大小
缓冲池通过LRU(Latest Recent Used,最近最少使用)算法进行管理。最频繁使用的页在LRU列表前端,最少使用的页在尾端,当缓冲池不能存放新读取的页时,首先释放LRU列表尾端的页(页数据刷新到磁盘,并从缓冲次中删除)。
InnoDB对于新读取的页,不是放到LRU列表最前端,而是放到midpoint位置(默认为5/8处)。
这是因为一些SQL操作会访问大量的页(如全表扫描),读取大量非热点数据,如果直接放到首部,可能导致真正的热点数据被移除。
关于页的概念会在存储篇解释,这里就理解为InnoDB将表数据拆分为若干固定大小的页,每页保存若干表记录。
重做日志缓存
重做日志先放到这个缓冲区,然后按一定频率刷新到重做日志文件。
参数:innodb_log_buffer_size
刷新规则:Master Thread每秒将一部分重做日志缓冲刷新到重做日志文件
每一事务提交时会将重做日志刷新到重做日志文件(如果配置了)
重做日志缓冲区使用空间大于1/2
额外的内存池
内存堆,对InnoDB内部使用的数据结构对象进行管理
Checkpoint机制
InnoDB对于对于DML语句操作(如Update或Delete),事务提交时只需在缓冲池中中完成操作,然后再通过Checkpoint将修改后的脏页数据刷新到磁盘。
InnoDB有两种Checkpoint
Sharp Checkpoint:数据库关闭是将所有脏页刷新会磁盘
Fuzzy Checkpoint:Master Thread Checkpoint
Master Thread每个1秒或10秒按一定比例将缓存池的脏页列表刷新会磁盘
FLUSH LRU LIST Checkpoint
Page Cleaner线程发现LRU列表中可用页数量少于innodb_lru_scan_depth(1024),就将LRU列表尾端移除,如果这些页中有脏页,就需要Checkpoint
Async/Sync Flush Checkpoint
重做日志文件空间不可以用时,将一部分脏页刷新到磁盘。
Dirty Page too much Checkpoint:
脏页数量太多(超过比例innodb_max_dirty_pages_pct,默认75),执行Checkpoint。
重做日志
重做日志是为了保证事务的原子性,持久性。InnoDB采用Write Ahread Log策略,事务提交时,先写重做日志,再修改页。
数据库宕机重启时通过执行重做日志恢复数据。
但由于Checkpoint机制,数据库宕机重启并不需要重做所有的日志,因为Checkpoint之前的页都刷新到磁盘了,只需执行最新一次Checkpoint后的重做日志进行恢复,这样可以缩短数据库的恢复时间。
InnoDB中重做日志文件是循环使用的。当页被Checkpoint刷新到磁盘后,对应的重做日志就不需要使用 ,其空间可以被覆盖重用。
如果待写入的重做日志文件空间不可用(脏页还没有刷新到磁盘),就需要强制产生Checkpoint,将缓冲池中的页至少刷新到当前重做日志的位置。
InnoDB 1.2.x(MySql 5.6)后,FLUSH LRU LIST Checkpoint以及Async/Sync Flush Checkpoint操作放到Page Cleaner线程,以免阻塞用户线程。
线程
主要作用:负责刷新内存池中的数据,保证缓冲池的内存缓冲的是最近的数据
已修改的数据文件刷新到磁盘文件
保证数据库发生异常的情况下InnoDB能恢复到正常状态。
InnoDB运行时主要有以下线程
Master Thread
负责将缓冲池中的数据异步刷新到磁盘,保证数据的一致性,包括脏页的刷新,合并插入缓冲(INSERT BUFFER),UNDO页的回收等。
IO Thread
负责AIO请求的回调处理。
参数:innodb_read_io_threads,innodb_write_io_threads
Purge Thread
事务提交后,undo log可能不再需要,由Purge Thread负责回收并重新分配的这些已经使用的undo页。
注意:Purge Thread需要离散地读取undo页。
Page Cleaner Thread
InnoDB 1.2.x引入,将Master Threader中刷新脏页的工作移至该线程,如上面说的FLUSH LRU LIST Checkpoint以及Async/Sync Flush Checkpoint。
Master Thread
Master Thread具有最高的线程优先级别,内部由多个循环组成:主循环(loop),后台循环(backgroup loop),刷新循环(flush loop),暂停循环(suspend loop),Master Thread根据数据库运行状态在以上循环切换。
Master Thread主要流程伪代码如下
void master_thread() {
goto loop;
// 主循环
loop:
for(int i = 0; i < 10; i++) {
// 每秒一次操作
thread_sleep(1)
// 日志缓冲刷新到磁盘,即使这个事务没提交
do log buffer flush to disk
// 合并插入缓冲(如果前一秒IO次数少于5次,InnoDB认为IO压力很小,执行该操作)
if(last_one_second_ios < 5)
do merge at most 5 insert buffer
// 至多刷新100个InnoDB的脏页到磁盘(脏页比例超过innodb_max_dirty_pages_pct)
if(buf_get_modified_ratio_pct > innodb_max_dirty_pages_pct)
do buffer poll flush 100 dirty page
// 没有用户活动,跳转到 backgroupo loop
if(no user activity)
goto backgroupo loop
}
// 每10秒操作
// 刷新100个脏页到磁盘(过去10秒内IO操作小于200次)
if(last_ten_second_ios < 200)
do buffer pool flush 100 dirty page
// 合并最多5个插入缓冲
do merge at most 5 insert buffer
// 合并最多5个插入缓冲
do log buffer flush to disk
// 删除无用的Undo页(最多20个undo页)
do full purge
//脏页比例超过innodb_max_dirty_pages_pct,刷新100个脏页到磁盘,否则刷新10个脏页
if(buf_get_modified_ratio_pct > 70%)
do buffer pool flush 100 dirty page
else
do buffer pool flush 10 dirty page
goto loop
// 后台循环
backgroup loop:
// 删除无用的Undo
do full purge
// 合并20个插入缓冲
do merge 20 insert buffer
if not idel:
goto loop
else
goto flush loop
// 刷新循环
flush loop:
// 刷新100个脏页到磁盘,直到脏页比例小于innodb_max_dirty_pages_pct
do buffer pool flush 100 dirty page
if(buf_get_modified_ratio_pct>innodb_max_dirty_pages_pct)
goto flush loop
goto suspend loop
// 暂停循环
suspend loop:
// 暂停线程
suspend_thread()
// 等待事件
waiting event;
goto loop;
}
如上所示,主循环有两大操作,每秒操作和十秒操作。
InnoDB1.0.x优化:
在每秒操作中,Master Thread每次最多刷新100个脏页(脏页比例超过innodb_max_dirty_pages_pct),合并20个插入缓冲,如果在写入密集的应用,处理速度可能太慢了。
从InnoDB 1.0.x开始,提供了通过innodb_io_capacity参数
每秒操作中合并插入缓冲数量为innodb_io_capacity * 5%
刷新脏页数量为innodb_io_capacity
而默认innodb_max_dirty_pages_pct参数值从90调整为75
引入以下参数
innodb_adaptive_flushing:自适应刷新
脏页比例小于innodb_max_dirty_pages_pct,也会刷新一定量的脏页(由InnoDB控制刷新策略和数量)
innodb_purge_batch_size:控制每次full purge回收Undo页,默认还是20
InnoDB1.2.x优化:InnoDB空闲时,执行原来的10秒一次操作,繁忙时,执行原来的每秒一次操作
刷新脏页操作,分离到单独Page Cleaner Thread
InnoDB关键特性
插入缓冲
插入聚集索引一般是顺序的,不需要磁盘的随机读取
但插入非聚集索引叶子节点不是顺序的,需要离散访问非聚集索引页,速度较慢。
对于非聚集索引的插入或更新,先判断插入的非聚集索引页是否在缓存池中,若在,直接插入,或不在,先放到一个Inser Buffer对象中,
然后根据一些算法将Insert Buffer缓存的记录通过后台线程慢慢合并刷新回辅助索引。
插入缓冲将多次插入合并为一次操作,减少磁盘的离散操作。
使用Insert Buffer需满足两个条件:
索引是辅助索引
索引不是唯一的(不需要查找索引页判断唯一性)
InnoDB从1.0.x引入Change Buffer,对INSERT,DELETE,UPDATE都进行缓冲。
参数:innodb_change_buffer_max_size,Change Buffer最多使用缓冲池内存空间。
两次写 doublewrite
部分写失效:页数据写入到磁盘时只写了一部分(如16K数据只写了2K),数据库就宕机了,导致页数据损坏,这时无法使用重做日志恢复。(执行重做日志时需要利用页的一些变量,如checksum)
因此在使用重做日志恢复数据库,需要有一个页的副本,当发生写失效时,先通过页的副本还原该页,再进行重做。于是InnoDB实现了doublewrite技术。
doublewrite有两部分,一部分是内存中的doublewrite buffer,大小为2MB,另一部分是磁盘共享表空间连续的128个页,也是2MB。
doublewrite要求刷新缓冲池的脏页时执行以下步骤通过memcpy函数将脏页复制到内存的doublewrite buffer
doublewrite buffer分两次,每次1MB顺序写入共享表空间
调用fsync函数同步磁盘,避免缓冲写带来问题,确保数据刷新到共享表空间(顺序写,开销小)
将上述的脏页数据写入各个表空间文件(离散写)
自适应哈希索引
InnoDB会监控对表上各索引页的查询执行情况,如发现建立哈希索引可以提升速度,则建立哈希索引,这是过程不需要用户干预。
参数:innodb_adaptive_hash_index,默认AHI为开启状态
异步IO
InnoDB使用异步IO操作磁盘,避免同步IO导致阻塞,也可以进行IO Merge操作,将多个IO操作合并为一个IO操作。
刷新邻接页
当刷新一个脏页时,InnoDB会检测该页所在区的所有页,如果是脏页,一起刷新,这是可以通过AIO将多个IO写入操作合并为一个IO操作。
参数:innodb_flush_neighbors,控制开关
文件
参数文件
在MySql实例启动时指定某些初始化参数,如数据库文件目录,内存池大小等。
参看参数文件所在目录:mysql --help|grep my.cnf
linux下默认目录为/etc/mysql/my.cnf
/etc/mysql/conf.d/mysql.cnf为客户端参数文件
/etc/mysql/mysql.conf.d/mysql.cnf为服务端参数文件
参数类型:动态参数,可以在MySql实例运行中进行更改
SET [global | session] system_var_name = expr
SET [@@golbal. | @@session. | @@]system_var_name = expr
静态参数:只能在实例停止时修改
注意:datadir参数指定MySql数据目录,它是数据文件,日志文件默认存放目录。
日志文件
错误日志
遇到问题应该查看该文件以便定位问题。
参数:log-error
慢查询日志
记录执行时间超过某一阀值的所有SQL
参数:
log_slow_queries:记录慢SQL的开关
long_query_time:慢SQl的阀值,默认为10,单位秒
log_queries_not_using_indexes:是否记录没有使用索引的查询
log_throttle_queries_not_using_indexs:每分钟最多记录没使用索引的SQL的数量
slow-query-log-file:指定目录,默认在data目录
log_output:输出格式,默认为FILE,可以配置为TABLE(记录到slow_log表)
可以使用mysqldumpslow分析慢日志
查询日志
记录所有对MySql数据库的请求信息。
默认文件名为主机名.log,也可以输出到mysql架构的general_log表中。
二进制日志
记录对MySql数据库执行更改的所有操作,不包括select,show这类操作。
可用于恢复,复制,审计(判断是否有对数据库进行注入攻击)
MySql官方手册测试表明,开启二进制日志会使性能下降1%,但考虑到复制,point-in-time的恢复等功能,完成可以接受,建议开启。
参数:
log_bin:是否开启二进制日志,默认No
log-bin:指定日志名称,默认为主机名,后缀为二进制日志序列号,如bin_log.000001
max_binlog_size:单个二进制日志文件最大值
binlog_cache_size:二进制日志缓冲区大小,基于会话
sync_binlog:1 表示使用同步写磁盘方法写入二进制日志,默认为0
binlog_format:二进制日志格式,可以为STATEMENT,ROW,MIXED
-- STATEMENT:记录SQL语句
-- ROW:记录表的行更改情况
-- MIXED:默认使用STATEMENT,一些特定情况使用ROW
使用ROW可以为数据库的恢复和复制带来更好的可靠性,但会导致二进制文件大小增加。复制的网络开销也有所增加。
可以使用mysqlbinlog可以分析二进制日志
套接字文件
当使用UNIX域套接字方式进行连接时需要的文件。
参数:socket
pid文件
MySql实例的进程ID文件。
参数:pid_file
表结构定义文件
存放MySql表结构定义。
在数据目录下,每一个表都有一个子目录,存放对应的表结构定义文件,后缀为frm
MySql8.0后,移除了.frm文件,表结构定义存放到数据库系统表中。
每个存储引擎都可以自行定义的文件保存引擎所需的数据。InnoDB存储引擎定义了以下文件:
表空间文件
InnoDB将所有数据(表数据,索引,插入缓冲索引页,回滚信息,插入缓冲索引页,系统事务信息,二次写缓冲等等)逻辑地放在一个空间中,称为共享表空间。
表空间数据默认保证在数据目录下的ibdata1文件,该文件初始大小为10M。
一个表空间可以由多个文件组成。
参数:innodb_data_file_path,可以通过多个文件组成一个表空间,同时指定文件属性,如
/db/ibdata1:2000M;/db/ibdata2:2000M:autoextend,autoextend表示文件可以自动扩容,只有最后一个文件可以被指定为自动扩容。
开启参数innodb_file_per_table后,每个表都产生一个独立的表空间,文件命名为:表名.ibd,该表的数据,索引和插入缓冲BITMAP等信息到保存到独立表空间,但其它数据(如回滚信息,插入缓冲索引页,系统事务信息,二次写缓冲)还是存放在默认的表空间。
重做日志文件
每个InnoDB存储引擎至少有1个重做日志文件组,每个文件组下至少有2个重做日志文件,默认为ib_logfile0,ib_logfile1,一个文件写满后,再写另一个文件,循环复用。
innodb_log_file_size:每个重做日志文件大小
innodb_log_files_in_group:日志文件组中重做日志文件数量,默认为2
innodb_mirrored_log_groups:日志镜像文件组数量,默认为1,表示只有一个日志文件组,没有镜像
innodb_log_group_host_dir:日志文件所在路径,默认为./,表示MySql数据目录
Undo日志文件
undo log保证事务的原子性, 帮助事务回滚以及MVCC功能。
在InnoDB 1.2.x(MySQL 5.6)前,undo日志保存在共享表空间ibdata1文件中的,随着数据库的运行时间的不断增长,会导致共享表空间越来越大,
从InnoDB 1.2.x(MySQL 5.6)起,Undo日志被分离出来,由单独的Undo表空间管理,这样可以避免处理Undo日志的IO过于集中,有助于分散IO负载。
Undo日志默认保存数据目录下的undo_001,undo_002文件中。
MySQL 5.7,提供undo log在线回收功能
MySQL 8.0,可以通过SQL语句非常方便的管理 Undo 表空间
innodb_undo_directory:指定UNDO独立表空间位置
innodb_undo_logs:设置rollback segment个数,默认为128(一个rollback segment支持1024并发),在InnoDB 1.2,该参数替换之前版本的innodb_rollback_segments
innodb_undo_tablespaces:组成独立表空间文件个数
innodb_undo_log_truncate: MySQL 自动收缩 Undo 表空间,防止磁盘占用过大,默认开启(Mysql5.7.5之后提供)
innodb_max_undo_log_size:超过该阀值将被自动收缩
关于重做日志和undo日志会在事务篇详细解析。
如果您觉得本文不错,欢迎关注我的微信公众号,您的关注是我坚持的动力!