MySQL neighbor flush_InnoDB存储引擎

第2章InnoDB存储引擎

-一.InnoDB存储引擎的版本MySQL版本版本功能

MySQL5.1老版本的InnoDB支持ACID(原子性、一致性、隔离性、持久性)、行锁设计、MVCC(多版本并发控制技术)

InnoDB 1.0.x继承上述版本所有功能，增加了compress(压缩)和dynamic(动态)页格式

MySQL5.5InnoDB 1.1.x继承上述版本所有功能，增加了Linux AIO、多回滚段

MySQL5.6InnoDB 1.2.x继承上述版本所有功能，增加了全文索引支持、在线索引添加

mysql在创建表的时候定义表的性质(也叫表的类型)，共有三种：静态表，动态表，压缩表。默认是静态表，如果存在varchar、blob、text字段，表类型就是动态了。1.静态表： 字段有固定长度，例如：char(20)。如果使用gbk字符集存储中文username，将占用40byte，如果username的实际内容没有达到40byte，将会填充空格，以达到40byte。速度很快，因为mysql知道username总是从第41个字节开始，容易缓存，出现问题后也容易恢 复(mysql知道记录的确切位置)，需要更多的硬盘空间(如果有三个上面的字段，一条记录就会占120字节，即使实际只用了其中一部分)2.动态表： 字段不定长(变长)，这种表格式比较节省空间，但是复杂度更高，每条记录都有一个header，作用就是表明该记录有多长，所有的字符串列都是动态的(除非 小于4个字节，这种情况下，节省的空间可以忽略不计，增加的复杂度会反而会导致性能丢失)，通常占用比静态表少的多地空间，但是必须经常维护(避免碎 片)，例如：更新了用户名somebody为somebodyt，t不能立刻就出现在y的后面，因为空间被其他记录占用，对于出现碎片的列，每个新连接会 损失6个字节。而且出现问题后不容易重建(前面我说的静态表正好相反)，如果碎片严重，有可能出现库爆炸(^_^). 不包括连接的动态记录的空间消耗可以用下面的公式计算：3+(列数+7)/8+(字符列数)+数字列的打包尺寸+字符串长度+(空列的数量+7)/8 每条记录的header可以表明那个字符串列是空的，那个数字列包含0(非空)，在那种情况下不向磁盘存储，非空字符串包含一个长度字节加上字符串内容。压缩表： 只读，使用很少的空间，用myisampack工具创建，表要少得多，每条记录分开压缩，所以不能同时访问，可以压缩静态表和动态表。创建方法：myisampack [options] filename

-二.InnoDB存储引擎的架构

2.1后台线程

1.Master Thread

Master Thread是一个非常核心的后台线程，主要负责将缓冲池中的数据异步刷新到磁盘，保证数据的一致性，包括脏页的刷新、合并插入缓冲(INSERT BUFFER)、UNDO页的回收等。

2.IO Thread

在InnoDB存储引擎中大量使用了AIO(Async IO)来处理写请求，这样可以极大提高数据库的性能。而IO Thread的工作主要是负责这些IO请求的回调处理。

可以通过命令show engine innodb status来观察InnoDB中的IO Thread。

3.Purge Thread

事务提交后，其所使用的undolog可能不在需要，因此需要Purge Thread来回收已经使用并分配的undo页。在InnoDB1.1版本之前，purge操作仅在Master Thread中完成。而从InnoDB1.1开始，purge操作可以独立到单独的线程中完成，以此来减少Master Thread的工作，从而提高CPU的使用率以及提升存储引擎的性能。

从InnoDB1.2开始，InnoDB支持多个Purge Thread，这样做的目的是为了进一步加快undo页的回收。

Undolog:在操作任何数据之前，首先将数据备份到一个地方(undolog)，然后再修改；这样就算更改到一半的时候，出现意外更改没有完成，则还可以恢复原来数据。

Redolog:每当操作数据前，将数据真正更改的时候，先将相关操作写入重做日志。这样当断电或者发生意外，导致后续操作未能完成，系统恢复后，还可以继续操作。

4.Page Cleaner Thread

Page Cleaner Thread是在InnoDB1.2.x版本中引入的。其作用是将之前的版本中脏页的刷新操作都放入单独的线程中完成。其目的是减轻原Master Thread的工作及对于用户查询线程的阻塞，进一步提高InnoDB存储引擎的性能。

2.2内存

1.缓冲池

InnoDB存储引擎是基于磁盘存储的，并将其中的记录按照页的方式的方式进行管理。

缓冲池简单来说就是一块内存区域，通过内存的速度来弥补磁盘速度较慢对数据库性能的影响。常见的在数据库中操作页有两种情况：首先是读取页操作，会先将从磁盘读到的页放在缓冲池中，下一次再读相同的页时，会首先判断该页是否再缓冲池中；然后还有修改操作，当修改了缓冲池中的页，会以一定频率刷新到磁盘上。注：刷新回磁盘不是再每次页发生更改的时候触发，而是通过一种称为Checkpoint的机制刷新回磁盘。

缓冲池的大小直接影响着数据库的整体性能。

下图缓冲池中还有undo页。

从InnoDB 1.0.x版本开始，允许有多个缓冲池实例。每个页根据哈希值平均分配到不同缓冲池实例中。可以减少数据库内部资源的竞争，增加数据库的并发处理能力。

2.管理缓冲池内存区域

LRU List

数据库中的缓冲池是通过LRU(最近最少使用)算法来进行管理的。即最频繁使用的页在LRU列表的前端，而最少使用的页在lRU列表的尾端。当缓冲池不能存放新读取到的页时，将首先释放LRU列表中尾端的页。

InnoDB存储引擎对传统的LRU算法做了一些优化，LRU列表中还加入了midpoint位置。即新读入的页不是先放在LRU列表的首部，而是放在midpoint位置。原因是，直接放入列表首端的话会，那么某些sql操作可能会使缓冲池中的页被刷新出，从而影响缓冲池的效率。

Free List

LRU列表用来管理已经读取的页，但当数据库刚启动时，LRU列表时空的，即没有任何的页。这时页都存放在free列表中，当需要从缓冲池中分页时，首先从free列表中查找是否有可用的空闲页，如有则将该页从free列表中删除，放入到LRU列表中。否则，根据LRU算法，淘汰LRU列表末尾的页，将该内存空间分配给新的页。

Innodb存储引擎从1.0.x版本开始只是压缩页的功能。原来LRU列表中的页 默认为16k，而对于非16k的页，是通过unzip_LRU列表进行管理的。

Flush List

在LRU列表中的页被修改后，称该页为脏页，即缓冲池中的页和磁盘上的页的数据产生了不一致。这时数据库会通过checkpoint机制将脏页刷新回磁盘，而flush列表中的页即为脏页列表，需要注意的是，脏页既存在与LRU列表中，也存在与flush列表中。LRU列表用来管理缓冲池中的页的可用性，flush列表用来管理将页刷新回磁盘，二者互不影响。

3.重做日志缓冲

InnoDB存储引擎会首先将重做日志信息先放入到这个缓冲区，然后按一定频率将其刷新到重做日志文件。重做日志缓冲一般不需要设置很大。

通常情况下，8MB的重做日志缓冲池足以满足绝大部分的应用，因为重做日志在以下三种情况会将重做日志缓冲中的内容刷新到外部磁盘的重做日志文件中。

1.master Thread每一秒将重做日志缓冲刷新到重做日志文件；

2.每个事物提交时会将重做日志缓冲刷新到重做日志文件；

3.当重做日志缓冲池剩余空间小于1/2时，重做日志缓冲刷新到重做日志文件。

4.额外的内存池

在innodb存储引擎中，对内存的管理是通过一种称为内存堆的方式进行的。在对一些数据结构本身的内存进行分配时，需要从额外的内存池中进行申请，当该区域的内存不够时，会从缓冲池中进行申请。

-三.Checkpoint技术

为了协调CPU和磁盘速度的鸿沟，因此页的操作首先都是在缓冲池中完成的。当一个页在缓冲池中被DML语句修改，就会成为脏页，这时就需要将其刷新回磁盘。但是如果每次修改都将其刷新会严重影响性能。

同时，如果在从缓冲池将页的数据刷新到磁盘时发生宕机，那么数据就不能恢复了。为了避免发生数据丢失的问题，当事务提交的时候，先写重做日志，再修改页。当由于发生宕机导致数据丢失的时候，通过重做日志来完成数据的恢复。

但是重做日志不能无限增大

因此checkpoint技术的目的是解决以下几个问题：

01.缩短数据库恢复的时间

当数据库发生宕机 不需要重做所有日志，只需要重做checkpoint之后的操作。

02.当缓冲池不可用时，将数据刷新回磁盘

当缓冲池溢出页，那么也会强制执行checkpoint操作。

03.重做日志不可用时，刷新脏页

重做日志可以被重用的部分是指这些重做日志已经不再需要，当数据库发生宕机时，数据库恢复操作不需要这部分的重做日志，因此这部分可以被覆盖重用。若此时重做日志还需要使用，那么必须强制产生checkpoint，将缓冲池中的页至少刷新到当前重做日志的位置。

1.Sharp Checkpoint

发生在数据库关闭时将所有的脏页都刷新回磁盘。

2.Fuzzy Checkpoint

若在数据库运行时也使用sharp checkpoint，那么数据库的可用性就会受到很大的影响。所以用fuzzy checkpoint只刷新部分脏页。

1.Master Thread Checkpoint

Master  Thread中会发生Checkpoint，差不多以每秒或者每十秒的速度从缓冲池的脏页列表中刷新一定比例的页回磁盘。这个过程是异步的，不影响MySQL的其他操作，用户查询线程不会影响。

2.Flush_lru_list Checkpoint

Flush_lru_list Checkpoint是因为innodb存储引擎需要保证lru列表中需要有差不多100个空闲页可供使用。

3.Async/Sync Flush Checkpoint

这个指重做日志文件不可用的情况，这时强制将一些页刷新回磁盘，而此时是从脏页列表中选取的。

4.Dirty Page too much Checkpoint

当脏页数量太多，导致innodb存储引擎强制进行checkpoint。其目的总的来说还是为了保证缓冲池中有足够可用的页。

-四、Master Thread工作方式

1.innodb1.0.x版本之前的master thread

Master thread具有最高的线程优先级别。其内部由多个循环组成：主循环(loop)、后台循环(backgroud loop)、刷新循环(flush loop)、暂停循环(suspend loop)。

主循环：

其中有两大部分操作——每秒的操作和每10秒操作。

每秒的操作：

l日志缓冲刷新到磁盘，即使这个事务还没有提交(总是)；

即使某个事务还没有提交，InnoDB存储引擎仍然每秒会将重做日志缓冲中的内容刷新到重做日志文件。这一点是必须要知道的，因为这可以很好地解释为什么再大的事务提交(commit)的时间也是很短的。

l合并插入缓冲(可能)；

合并插入缓冲(Insert Buffer)并不是每秒都会发生的。InnoDB存储引擎会判断当前一秒内发生的IO次数是否小于5次，如果小于5次，InnoDB认为当前的IO压力很小，可以执行合并插入缓冲的操作。

l至少刷新100个innodb的缓冲池中的脏页到磁盘(可能)；

同样，刷新100个脏页也不是每秒都会发生的。InnoDB存储引擎通过判断当前缓冲池中脏页的比例(buf_get_modified_ratio_pct)是否超过了配置文件中innodb_max_dirty_pages_pct这个参数(默认为90，代表90%)，如果超过了这个阈值，InnoDB存储引擎认为需要做磁盘同步的操作，将100个脏页写入磁盘中。

l如果当前没有用户活动，则切换到background loop(可能)。

每10秒的操作：

l刷新100个脏页到磁盘(可能的情况下)；

l合并至多5个插入缓冲(总是)；

l将日志缓冲刷新到磁盘(总是)；

l删除无用的undo页(总是)；

l刷新100个或者10个脏页到磁盘(总是)。

在以上的过程中，InnoDB存储引擎会先判断过去10秒之内磁盘的IO操作是否小于200次，如果是，InnoDB存储引擎认为当前有足够的磁盘IO操作能力，因此将100个脏页刷新到磁盘。接着，InnoDB存储引擎会合并插入缓冲。不同于每秒一次操作时可能发生的合并插入缓冲操作，这次的合并插入缓冲操作总会在这个阶段进行。之后，InnoDB存储引擎会再进行一次将日志缓冲刷新到磁盘的操作。这和每秒一次时发生的操作是一样的。

接着InnoDB存储引擎会进行一步执行full purge操作，即删除无用的Undo页。对表进行update、delete这类操作时，原先的行被标记为删除，但是因为一致性读(consistent read)的关系，需要保留这些行版本的信息。但是在full purge过程中，InnoDB存储引擎会判断当前事务系统中已被删除的行是否可以删除，比如有时候可能还有查询操作需要读取之前版本的undo信息，如果可以删除，InnoDB会立即将其删除。

从源代码中可以发现，InnoDB存储引擎在执行full purge操作时，每次最多尝试回收20个undo页。

然后，InnoDB存储引擎会判断缓冲池中脏页的比例(buf_get_modified_ratio_pct)，如果有超过70%的脏页，则刷新100个脏页到磁盘，如果脏页的比例小于70%，则只需刷新10%的脏页到磁盘。

后台循环：

若当前没有用户活动(数据库空闲时)或者数据库关闭(shutdown)，就会切换到这个循环。

background loop会执行以下操作：

u删除无用的Undo页(总是)；

u合并20个插入缓冲(总是)；

u跳回到主循环(总是)；

u不断刷新100个页直到符合条件(可能，跳转到flush loop中完成)。

若flush loop中也没有什么事情可以做了，InnoDB存储引擎会切换到suspend__loop，将Master Thread挂起，等待事件的发生。若用户启用(enable)了InnoDB存储引擎，却没有使用任何InnoDB存储引擎的表，那么Master Thread总是处于挂起的状态。

2.innodb1.2.x版本之前的master thread

问题总结：由于innodb硬编码(hard coding)，会限制其刷新脏页和合并插入缓冲的数量，即使可以操作更多。

l从innodb1.0.x引入了innodb_io_capacity，用来表示磁盘IO的吞吐量，默认值为200。

l调整参数innodb_max_dirty_pages_pct(脏页占缓冲池的比例)为75

l从innodb1.0.x开始引入另外一个参数innodb_adaptive_flushing(自适应地刷新)，影响每秒刷新脏页的数量。之前是根据一个参数的值来进行，现在根据函数来执行。

l将purge thread放在单独的线程中。

3.innodb1.2.x版本的master thread

对于刷新脏页的操作，从master thread线程分离到一个单独的page cleaner thread中。

-五、InnoDB关键特性

插入缓冲(insert buffer)

1.Insert Buffer

Insert Buffer和数据页一样，也是物理页的一个组成部分。

在InnoDB存储引擎中，主键是行唯一的标识符。通常应用程序中行记录的插入顺序是按照主键递增的顺序进行插入的。因此，插入聚集索引(Primary Key)一般是顺序的，不需要磁盘的随机读取。比如按下列SQL定义表：

CREATE TABLE t (

a INT AUTO_INCREMENT,

b VARCHAR(30),

PRIMARY KEY(a)

);

注意并不是所有的主键插入都是顺序的。若主键类是UUID这样的类，那么插入和辅助索引一样，同样是随机的。即使主键是自增类型，但是插入的是指定的值，而不是NULL值，那么同样可能导致插入并非连续的情况。

但是不可能每张表上只有一个聚集索引，更多情况下，一张表上有多个非聚集的辅助索引(secondary index)。比如，用户需要按照b这个字段进行查找，并且b这个字段不是唯一的，即表是按如下的SQL语句定义的：

CREATE TABLE t (

a INT AUTO_INCREMENT,

b VARCHAR(30),

PRIMARY KEY(a),

key(b)

);

在这样的情况下产生了一个非聚集的且不是唯一的索引。在进行插入操作时，数据页的存放还是按主键a进行顺序存放的，但是对于非聚集索引叶子节点的插入不再是顺序的了，这时就需要离散地访问非聚集索引页，由于随机读取的存在而导致了插入操作性能下降。当然这并不是这个b字段上索引的错误，而是因为B+树的特性决定了非聚集索引插入的离散性。

InnoDB存储引擎开创性地设计了Insert Buffer，对于非聚集索引的插入或更新操作，不是每一次直接插入到索引页中，而是先判断插入的非聚集索引页是否在缓冲池中，若在，则直接插入；若不在，则先放入到一个Insert Buffer对象中，好似欺骗数据库这个非聚集的索引已经插到叶子节点，而实际并没有，只是存放在另一个位置。然后再以一定的频率和情况进行Insert Buffer和辅助索引页子节点的merge(合并)操作，这时通常能将多个插入合并到一个操作中(因为在一个索引页中)，这就大大提高了对于非聚集索引插入的性能。

(如果每次直接插入，需要离散的查找要插入的索引页，性能低下)

然而Insert Buffer的使用需要同时满足以下两个条件：

l索引是辅助索引(secondary index)；

l索引不是唯一(unique)的。

2.Change Buffer

InnoDB从1.0.x版本开始引入了Change Buffer，可将其视为Insert Buffer的升级。

从这个版本开始，InnoDB存储引擎可以对DML操作——INSERT、DELETE、UPDATE都进行缓冲，他们分别是：Insert Buffer、Delete Buffer、Purge buffer。

当然和之前Insert Buffer一样，Change Buffer适用的对象依然是非唯一的辅助索引。

对一条记录进行UPDATE操作可能分为两个过程：

l将记录标记为已删除；

l真正将记录删除。

3.Merge Insert Buffer

Insert Buffer中的记录何时合并(merge)到真正的辅助索引中呢？

概括地说，Merge Insert Buffer的操作可能发生在以下几种情况下：

l辅助索引页被读取到缓冲池时；

lInsert Buffer Bitmap页追踪到该辅助索引页已无可用空间时；

lMaster Thread。

第一种情况为当辅助索引页被读取到缓冲池中时，例如这在执行正常的SELECT查询操作，这时需要检查Insert Buffer Bitmap页，然后确认该辅助索引页是否有记录存放于Insert Buffer B+树中。若有，则将Insert Buffer B+树中该页的记录插入到该辅助索引页中。可以看到对该页多次的记录操作通过一次操作合并到了原有的辅助索引页中，因此性能会有大幅提高。

Insert Buffer Bitmap页用来追踪每个辅助索引页的可用空间，并至少有1/32页的空间。若插入辅助索引记录时检测到插入记录后可用空间会小于1/32页，则会强制进行一个合并操作，即强制读取辅助索引页，将Insert Buffer B+树中该页的记录及待插入的记录插入到辅助索引页中。这就是上述所说的第二种情况。

还有一种情况，之前在分析Master Thread时曾讲到，在Master Thread线程中每秒或每10秒会进行一次Merge Insert Buffer的操作，不同之处在于每次进行merge操作的页的数量不同。

两次写(double write)

如果说Insert Buffer带给InnoDB存储引擎的是性能上的提升，那么doublewrite(两次写)带给InnoDB存储引擎的是数据页的可靠性。

当发生数据库宕机时，可能InnoDB存储引擎正在写入某个页到表中，而这个页只写了一部分，比如16KB的页，只写了前4KB，之后就发生了宕机，这种情况被称为部分写失效(partial page write)。

有经验的DBA也许会想，如果发生写失效，可以通过重做日志进行恢复。这是一个办法。但是必须清楚地认识到，重做日志中记录的是对页的物理操作，如偏移量800，写'aaaa'记录。如果这个页本身已经发生了损坏，再对其进行重做是没有意义的。

Doublewrite过程：

doublewrite由两部分组成，一部分是内存中的doublewrite buffer，大小为2MB，另一部分是物理磁盘上共享表空间中连续的128个页，即2个区(extent)，大小同样为2MB。在对缓冲池的脏页进行刷新时，并不直接写磁盘，而是会通过memcpy函数将脏页先复制到内存中的doublewrite buffer，之后通doublewrite buffer再分两次，每次1MB顺序地写入共享表空间的物理磁盘上，然后马上调用fsync函数，同步磁盘，避免缓冲写带来的问题。在这个过程中，因为doublewrite页是连续的，因此这个过程是顺序写的，开销并不是很大。在完成doublewrite页的写入后，再将doublewrite buffer中的页写入各个表空间文件中，此时的写入则是离散的。

如果操作系统在将页写入磁盘的过程中发生了崩溃，在恢复过程中，InnoDB存储引擎可以从共享表空间中的doublewrite中找到该页的一个副本，将其复制到表空间文件，再应用重做日志。

自适应哈希索引(adaptive hash index)

InnoDB存储引擎会监控对表上各索引页的查询。如果观察到建立哈希索引可以带来速度提升，则建立哈希索引，称之为自适应哈希索引(Adaptive Hash Index，AHI)

异步IO(async IO)

为了提高磁盘操作性能，当前的数据库系统都采用异步IO(Asynchronous IO，AIO)的方式来处理磁盘操作。InnoDB存储引擎亦是如此。

与AIO对应的是Sync IO，即每进行一次IO操作，需要等待此次操作结束才能继续接下来的操作。但是如果用户发出的是一条索引扫描的查询，那么这条SQL查询语句可能需要扫描多个索引页，也就是需要进行多次的IO操作。在每扫描一个页并等待其完成后再进行下一次的扫描，这是没有必要的。用户可以在发出一个IO请求后立即再发出另一个IO请求，当全部IO请求发送完毕后，等待所有IO操作的完成，这就是AIO。

在InnoDB1.1.x之前，AIO的实现通过InnoDB存储引擎中的代码来模拟实现。而从InnoDB 1.1.x开始(InnoDB Plugin不支持)，提供了内核级别AIO的支持，称为NativeAIO。因此在编译或者运行该版本MySQL时，需要libaio库的支持。

刷新临界页(flush neighbor page)

InnoDB存储引擎还提供了Flush Neighbor Page(刷新邻接页)的特性。其工作原理为：当刷新一个脏页时，InnoDB存储引擎会检测该页所在区(extent)的所有页，如果是脏页，那么一起进行刷新。这样做的好处显而易见，通过AIO可以将多个IO写入操作合并为一个IO操作，故该工作机制在传统机械磁盘下有着显著的优势。但是需要考虑到下面两个问题：

l是不是可能将不怎么脏的页进行了写入，而该页之后又会很快变成脏页？

l固态硬盘有着较高的IOPS，是否还需要这个特性？

为此，InnoDB存储引擎从1.2.x版本开始提供了参数innodb_flush_neighbors，用来控制是否启用该特性。对于传统机械硬盘建议启用该特性，而对于固态硬盘有着超高IOPS性能的磁盘，则建议将该参数设置为0，即关闭此特性。

第四章表

查看页的情况#py_innodb_page_info.py -v t.ibd

4.1索引组织表

定义：

表都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表。在innodb存储引擎中，每张表都有个主键，如果在创建表时没有显示的定义主键，则innodb存储引擎会按如下方式选择或创建主键：

u首先判断表中是否有非空的唯一索引，如果有，则该列即为主键。

u如果不符合上述条件，innodb存储引擎会自动创建一个6字节大小的指针。

注：当表中有多个非空唯一索引时，innodb存储引擎将选择建表时第一个定义的非空唯一索引为主键。主键的选择根据的是定义索引的顺序，而不是建表时列的选择。

操作：

#select a,b,c,d,_rowid from z;

_rowid可以显示表的主键(只能显示单个列的主键的情况，对于多列组成的主键就不行了)

4.2innodb逻辑存储结构

从innodb存储引擎的逻辑存储结构来看，所有的数据都被逻辑地存放在一个空间中，成为表空间。表空间又由段，区，页组成。

4.2.1表空间

在默认情况下innodb存储引擎有一个共享表空间ibdata1，即所有的数据都存放在这个表空间中。如果用户启用了参数innodb_file_per_table,则每张表内的数据可以单独放到一个表空间中。

需要注意的是每张表的表空间内存放的只是数据，索引和插入缓冲birnap页，其他类的数据，如回滚信息，插入缓冲索引页、系统事务信息，二次写缓冲等还是存放在原来的共享表空间内。即使启用了参数innodb_file_per_table之后，共享表空间还是会不断增加其大小。

4.2.2段

表空间由各个段组成，常见的段有数据段、索引段、回滚段等。

对段的管理一般有引擎自身来完成，dba不能也没有必要对其进行控制。

4.2.3区

区由连续页组成，在任何情况下每个区的大小为1MB。

默认情况下，innodb存储引擎页的大小为16KB，即一个区中一共有64个连续的页。Innodb1.0.x开始引入压缩页；innodb1.2.x增加了参数innodb_page_size,可以设置默认页大小设置为4K,8K。

这里有个问题：在用户启用了参数innodb_file_per_table后，创建表默认为96KB。区中是64个连续的页，创建的表的大小至少是1MB才对啊？其实这是因为在每个段开始时，先用32个页大小的碎片页(frament page)来存放数据，在使用完这些页之后才是64个连续页的申请。

4.2.4页

Innodb有页的概念，页是innodb磁盘管理的最小单位。

默认页大小为16K，从innodb1.2.x开始，可以通过参数innodb_page_size将页的大小设置为4K,8K,16K.

常见的页类型有：

数据页、(B-tree Node)

undo页、(undo Log Page)

系统页、(System Page)

事务数据页、(transaction system Page)

插入缓冲位图页、(Insert buffer bitmap)

插入缓冲空闲列表页、(Insert buffer free list)

未压缩的二进制大对象页、(uncompressed blob page)

压缩的二进制打对象页。(compressed blob page)

4.2.5行

Innodb存储引擎是面向列的(row-oriented)，也就是说数据是按行进行存放的。每个页存放的行记录也是有硬性定义的，最多允许存放16K/2-200行的记录，即7992行记录。

4.3innodb行记录格式

如果用户自己知道页中行记录的组织规则，也可以自行通过编写工具的方式来读取其中的记录，比如py_innodb_page_info。Innodb1.0.x之前CompactAntelope文件格式

Redundant

Innodb1.0.x开始引入CompressedBarracuda文件格式

Dynamic

01.compact行记录格式变长字段长度列表

(列长度小于255字节，用1字节表示；大于255字节，用2字节表示)Null标志位

(占1字节)记录头信息

(占5字节)列1数据列2数据......

特别注意：null不占用该部分任何空间，即null除了占有null标志位，实际存储不占有任何空间；另外每行数据除了用户定义的列外，还有两个隐藏列：事务ID列(6字节)和回滚指针列(7字节)；若innodb没有定义主键，每行还会增加一个6字节大小的rowid列。

02.行溢出数据

Varchar类型

65535——>65535-2(2为当列长度小大于255字节，用2字节表示)=65533——>65535-2-1(1为空标志位)=65532

注1：如果允许建立varchar(65535)，会抛出一个warning，即会将其变成text类型。

注2：varchar(N)，N指的是字符长度，而文档说明varchar类型最大支持65535，单位是字节。

注3：MySQL官方手册中定义的65535长度是指所有varchar类型的长度总和，如果列的长度总和超出这个长度，依然无法创建。

大类型溢出

Innodb存储引擎的页为16K，即16384字节，存放不下65535.

在一般情况下，innodb存储引擎的数据都是存放在页类型为B-tree node中。但当发生行溢出时，数据存放在页类型为Uncompress Blob页中。

对于varchar保存数据到数据页时，从8098字节开始往blob页中存放。

注：

对于text和blob的数据类型，用户总是以为它们是存放在uncompressed blob page中的，其实这也不准确。但同时，既然用户使用了blob列类型，一般不可能存放长度这么小的数据。因此在大多数的情况下blob的行数据还是会发生行溢出，实际数据保存在blob页中，数据页只保存数据的前768字节。

03.compressed和dynamic行记录格式

这两种格式对于存放在blob中的数据采用了完全的行溢出的方式，即在数据页中只存放20个字节，实际的数据都存放在off page中。

Compressed行记录格式的另一个功能就是，存储在其中的行数据会以zlib的算法进行压缩，因此对于blob、text、varchar这类大长度类型的数据能够进行非常有效的存储。

04.char的行结构存储

Char类型被明确视为变长字符类型，对于未能占满长度的字符还是填充0x20。因此可以认为在多字节字符集的情况下，char和varchar的实际存储基本是没有区别的。

4.4innodb数据页结构

01.file header

用来记录页的一些头信息。

02.page header

用来记录数据页的状态信息。

03.infimum和supremum record

这是数据页中的两个虚拟行记录，用来限定记录的边界。

04.user record和free space

User record:实际存储行记录的内容。再次强调，innodb存储引擎表总是b+树索引组织的。

Free space：就是指空闲空间，同样也是个链表数据结构。在一条记录被删除后，该空间会被加入到空闲链表中。

05.page directory

存放了记录的相对位置(注意：这里存放的是页相对位置，而不是偏移量)

06.file trailer

为了检测页是否已经完整写入磁盘(如可能发生的写入过程中磁盘损坏、机器关机等)，innodb存储引擎的页中设置了file trailer。

操作：

当建立表并将其中插入数据，可以使用工具来分析。(例如分析t.ibd) #py_innodb_page_info.py -v t.ibd

... page offset 00000003,page type ,page level <0000>

...

(页类型为B-tree node的页存放的即是表中行的实际数据了)

第五章索引与算法

5.1 Innodb存储引擎索引概述

Innodb存储引擎支持一下几种常见的索引：

lB+树索引

l全文索引

l哈希索引

前面提过，innodb存储引擎支持的哈希索引是自适应的，innodb存储引擎会根据表的使用情况自动为表生成哈希索引，不能人为干预是否在一张表中生成哈希索引。

另一个容易被忽略的问题是：B+树索引并不能找到一个给定键值的具体行。B+树索引能找到的只是被查找数据行所在的页。然后数据库通过把页读入到内存，再在内存中进行查找，最好得到要查找的数据。

5.2 B+树索引

https://blog.51cto.com/qinbin/1877928

B+树索引的本质就是B+树在数据库中的实现。但是B+索引在数据库中有一个特点是高扇出性，因此在数据库中，B+树的高度一般在2~4层，这也就是说查找某一键值的行记录时最多只需要2到4次IO。

扇入：指直接调用该模块的上级模块的个数。

扇出：是指该模块直接调用的下级模块的个数。

数据库中的B+树索引可以分为聚集索引(clustered index)和辅助索引(secondary index)，但是不管是聚集还是辅助索引，其内部都是B+树的，即高度平衡的，叶子节点存放着所有的数据。聚集索引和辅助索引不同的是，叶子节点存放的是否是一整行的信息。

聚集索引

聚集索引就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的是整张表的行记录数据，叶子节点也成称为数据页。聚集索引的这个特性决定了索引组织表中数据也是索引的一部分。同B+树数据结构一样，每个数据页通过一个双向链表来进行链接。

由于实际的数据页只能按照一颗B+树进行排序，因此每张表只能拥有一个聚集索引。在多数情况下，查询优化器倾向于采用聚集索引。因为聚集索引能够在B+树索引的叶子节点上直接找到数据。此外，由于定义了数据的逻辑顺序，聚集索引能够特别快的访问针对范围值的查询。查询优化器能够快速发现某一段范围的数据页需要扫描。

注意：聚集索引的存储并不是物理上连续的，而是逻辑上连续的。这其中的两点：一是前面说过的页通过双向链表链接，页是按照主键的顺序排序；另一点是每个页中的记录也是通过双向链表进行维护的，物理存储上可以同样不按照主键存储。

聚集索引的另外一个好处是，它对于主键的排序查找和范围查找速度非常快。

使用order by对记录进行排序，在实际过程中并没有进行所谓的filesort操作，而这就是因为聚集索引的特点。

辅助索引

对于辅助索引(非聚集索引)，叶子节点并不包含行记录的全部数据。叶子节点除了包含键值外，每个叶子节点中的索引行中还包含了一个书签(bookmark)。该书签用来告诉innodb存储引擎哪里可以找到与索引相对于的行数据。由于innodb存储引擎表示索引组织表，因此innodb存储引擎的辅助索引的书签就是相应行数据的聚集索引键。

辅助索引的存在并不影响数据在聚集索引中的组织，因此每张表上可以有多个辅助索引。当通过辅助索引来寻找数据时，innodb存储引擎会遍历辅助索引并通过页基本的指针获得指向主键索引的主键，然后再通过主键索引来找到一个完整的行记录。举例来说，如果在一颗高度为3的辅助索引树中查找数据，那需要对这颗辅助索引树遍历3次找到指定主键，如果聚集索引树的高度同样为3，那么还需要对聚集索引树进行3次查找，最终找到一个完整的行数据所在的页，因此一个需要6次逻辑io访问以得到最终的一个数据页。

5.3B+树索引的使用

联合索引

索引覆盖

Innodb存储引擎支持索引覆盖，即从辅助索引中就可以得到查询的记录，而不需要查询聚集索引中的记录。使用索引覆盖的一个好处是辅助索引不包含整行记录的所有信息，故其大小要远小于聚集索引，因此可以减少大量的IO操作。

覆盖索引的另一个好处是对某些统计问题而言。例如进行查询：select count(*) from buy_log;innodb储存引擎并不会选择查询聚集索引来进行统计。由于buy_log表上还有辅助索引，而辅助索引远小于聚集索引，选择辅助索引可以减少io操作。