Innodb学习笔记

本文是《MySQL技术内幕(InnoDB存储引擎)第2版》这本书的读书笔记

配置

首先需要执行下面两行,以便创建服务

1
2
mysqld --initialize --user=mysql --console
mysqld -install

然后net start mysql,如果出现下面的错误,表示mysql没有初始化,例如data文件夹就没有被正式创建,要使用mysqld --initialize-insecure来初始化

1
2
3
4
MySQL 服务正在启动 .
MySQL 服务无法启动。

服务没有报告任何错误。

MySQL体系结构与存储引擎

MySQL的架构如下图所示

可以通过SHOW ENGINES命令来查看当前MySQL支持的存储引擎。也可以查找information_schema架构下的ENGINE表来实现。

InnoDB的表存储于ibd文件中,通过MVCC实现高并发性,并实现了SQL标准规定的四种隔离级别,默认级别是RR。

MyISAM不支持事务和表锁,但支持全文索引,主要面向OLAP。MyISAM在MYD中存储表数据,在MYI中存储表索引。

InnoDB存储引擎

前置知识

为了便于理解,先要介绍一些前置知识,这些知识在第3、4章等章节中。

Log

MySQL中有binlog、slowlog、error log、查询日志等日志形式。

通过show variables like 'log_error'\G可以获得error log的存储位置,error log可以定位一些严重的错误,例如MySQL无法启动等问题。slowlog可以定位下面的一些问题:

  1. 查询时间超过阈值

    1
    show variables like 'long_query_time'\G
  2. 没有通过索引的查询

    1
    show variables like 'log_queries_not_using_indexes'\G
  3. 用来限制每分钟最大的2的数量

    1
    show variables like 'log_throttle_queries_not_using_indexes'\G

slowlog的相关信息可以在mysql数据库的slow_log表下面看到,可以通过mysqldumpslow进行展示。
查询日志存储了所有对MySQL的请求信息。
binlog记录了MySQL对数据库进行更改的所有操作。可以通过show master status\G获得binlog的存储位置。binlog可以被用来恢复数据、主从复制以及审计(audit)(是否有对数据库的攻击)。当sync_binlog==0时,由底层文件系统控制对binlog缓存的刷新。对于InnoDB而言,它将binlog统一写到一块缓存中,等事务提交之后,直接将缓冲中的binlog写入binlog文件,该缓冲大小由binlog_cache_size决定,默认为32K,并且是基于会话的。可以通过mysqlbinlog查看当前的binlog。

InnoDB拥有redo log和undo log,redo log存储于形如ib_logfile*的文件里面。
LSN(log sequence number)是InnoDB中的一个长度为8字节的数,存在于redo log和数据页中。

1
2
3
4
5
6
7
8
9
> show engine innodb status\G
Log sequence number 19739600 表示当前最新的redo log(一般在缓冲区内)的LSN
Log buffer assigned up to 19739600
Log buffer completed up to 19739600
Log written up to 19739600
Log flushed up to 19739600 表示已经刷到磁盘上的redo log的LSN
Added dirty pages up to 19739600
Pages flushed up to 19739600 表示已经刷到磁盘上数据页的最高的LSN
Last checkpoint at 19739600 表示Checkpoint上的最高LSN

InnoDB将数据按层次划分为表空间、segment、extent、page。其中页作为磁盘和内存之间交互的基本单位,InnoDB中页的大小一般为16KB,但可以通过innodb_page_size进行设置。当16KB的页放不下时,就可能发生行溢出,此时数据放在未压缩BLOB页中。

在InnoDB中,页包括

  1. 数据页(B-tree Node)
  2. undo页
  3. 系统页
  4. 事务数据页
  5. 插入缓冲位图页
  6. 未压缩BLOB页
  7. 压缩BLOB页

数据页的构成如下图所示

insert buffer

聚集索引下,表中相邻行存储的物理顺序和逻辑顺序是一致的,因此一张表只能有一个聚集索引,InnoDB的聚集索引按照主键进行聚集。
辅助索引是非聚集索引,对于它们的插入或更新操作,不是每一次直接插入到索引页中,而是先判断插入的索引页是否在缓冲池中,若在,则直接插入;若不在,则先放入到一个insert buffer对象中。在后台线程中将insert buffer更新到辅助索引上。这样的好处是能够将同索引页多个插入合并到一个操作中完成。

正文

InnoDB的主要架构如下所示。

其中主线程中主要根据数据库状态运行主循环(loop)、后台循环(backgroup loop)、刷新循环(flush loop)和暂停循环(suspend loop)四个循环。

若干IO Thread主要分为四类:read、write、insert buffer和log IO,在当前版本下,可以通过innodb_read_io_threads等参数进行设置。
Purge Thread用来回收已经使用并分配的UNDO页,当设置innodb_purge_threads大于0时,该线程会启动,从而减轻主线程负担。
Page Cleaner Thread用来刷新脏页,它也是为了减轻主线程负担。

InnoDB设有缓冲池,用来存放从磁盘中读到的页。在读取页时,首先会尝试从缓冲池中查找,否则读磁盘上的页。如前面所讲,脏页会定时被刷回磁盘,这个是通过一个叫Checkpoint的机制实现的。
通常,缓冲池中的缓存页类型有索引页、数据页、undo页、insert buffer、adaptive hash index、锁信息lock info、数据字典信息。InnoDB将innodb_buffer_pool_instances设置为大于1,此时可以得到多个缓冲池实例。
缓冲池中的Database pages通过LRU算法进行管理(但adaptive hash index、lock信息、insert buffer等信息并没有放在缓冲池中),将最近使用的页放在最前面。InnoDB为LRU加入了midpoint位置,新读到的页会先放到midpoint,而不是LRU首部,在midpoint之前的称为new列表,之后的称为old列表。当数据库启动时,LRU是空的,这时候首先需要从Free列表中查找可用的空闲页,放到LRU中,否则使用LRU算法淘汰列表末尾的页。可以通过SHOW ENGINE INNODB STATUS观察LRU列表和Free列表的情况。例如,可以通过Buffer pool hit rate(实际没找到在哪里)缓冲池命中率,如果命中率较低的话,需要观察是不是因为全表扫描引起的LRU列表被污染的问题。还可以通过information_schema.INNODB_BUFFER_POOL_STATS观察缓冲池的运行状态。

InnoDB一般先会把redo log写到位于内存中的缓冲区内,然后在下面三种情况下进行刷新

  1. 每隔一秒
  2. 每个事务提交时
  3. 重做日志缓冲池剩余空间小于1/2

InnoDB使用Checkpoint的机制将脏页刷新回磁盘,这样可以抑制redo log和缓冲池的不断增长,同时避免在宕机后通过redo log重头开始恢复造成很大的恢复开销。Checkpoint分为Sharp和Fuzzy,前者在数据库关闭时刷回所有的脏页,如果在运行时采用,会严重降低可用性。而Fuzzy只会刷新一部分脏页,主要分为以下情况

  1. 主线程以一定时间间隔刷新脏页