MySQL 笔记

InnoDB存储引擎

mysql 存储引擎（好难用，看https://www.zybuluo.com/eqyun/note/27850）

简介

InnoDB是事务安全的MySQL存储引擎，从MySQL5.5版本开始是默认的表存储引擎，是第一个完整支持ACID事务的MySQL存储引擎，其特点是行锁设计、支持MVCC、支持外键、提供一致性锁定读，同时被设计用来最有效地利用以及使用内存和CPU

InnoDB存储引擎体系架构

后台线程(多个)->InnoDB存储引擎内存池->物理文件

后台线程

1. Master Thread

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

2. IO Thread

在InnoDB中大量使用了AIO（Async IO）来处理IO 请求，IO Thread主要是负责这些IO请求的回调处理。Io Thread共有4类：write、read、insert buffer和log IO thread。在InnoDB 1.0.x 版本开始，read thread和write thread分别增加大4个，用innodb_file_io_threads和innodb_write_io_threads参数来设置，如
可以看到thread 0为insert buffer thread,IO thread 1为log thread.

    mysql> show engine innodb status\G;
    *************************** 1. row ***************************
    Type: InnoDB
    Name:
    ....
    --------
    FILE I/O
    --------
    I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
    I/O thread 1 state: waiting for completed aio requests (log thread)
    I/O thread 2 state: waiting for completed aio requests (read thread)
    I/O thread 3 state: waiting for completed aio requests (read thread)
    I/O thread 4 state: waiting for completed aio requests (read thread)
    I/O thread 5 state: waiting for completed aio requests (read thread)
    I/O thread 6 state: waiting for completed aio requests (write thread)
    I/O thread 7 state: waiting for completed aio requests (write thread)
    I/O thread 8 state: waiting for completed aio requests (write thread)
    I/O thread 9 state: waiting for completed aio requests (write thread)

3. Purge Thread

事务提交后，使用的undolog可能不再需要，因此需要PurgeThread 来回收已经使用并分配的undo页面。在InnoDB1.1版本前，它是在Master线程中完成的，InnoDB1.1后，独立一条线程。在配置文件中 启用独立Purge Thread线程配置为：

    [mysqld]
    innnodb_purge_threads=1

在1.2版本后支持多个Purge Thread，加书undo页的回收，可用以后语句查询：

    mysql> show variables like ‘innodb_purge_threads‘\G;
    *************************** 1. row ***************************
    Variable_name: innodb_purge_threads
    Value: 1
    1 row in set (0.00 sec)
    ERROR:
    No query specified

4. Page Cleaner Thread

Page Cleaner Thread是在InnoDB 1.2x版本加入的，作用是将之前版本中脏页的刷新操作都加入到单独的线程中完成，目的是为了减轻原Master Thread的工作和用户查询线程的阻塞。

内存

1. 内存池

InnoDB是基于磁盘存储的，并将记录按照页的方式进行管理，内存池就是为了解决CUP速度与磁盘速度之间的鸿沟。

在数据库中进行记取页的操作，首先将磁盘的页放到缓冲池，这个过程称将页FIX在缓冲池。下一次再读相同的页时，首先读缓冲池，有直接读，没有再去读磁盘

对数据库中页的修改也首先修改缓冲池，再以一定的频率刷新到磁盘上。缓冲池的大小直接影响数据库的整体性能

在InnoDB下，其缓冲池配置可通过innodb_buffer_pool_size来设置，查询语句为：

    mysql> show variables like ‘innodb_buffer_pool_size‘\G;
    *************************** 1. row ***************************
    Variable_name: innodb_buffer_pool_size
    Value: 134217728
    1 row in set (0.01 sec)

缓冲池缓存的数据页类型有：索引页、数据据、undo页、插入缓冲(insert buffer)、自适应哈希索引(adaptive hash index)、InnoDB的锁信息(lock info)、数据字典信息(data dictionary)等

从InnoDB 1.0.x版本开始，允许多个缓冲池实例，这样做的好处是减少数据库内部的资源竞争，增加数据库的并发能力，可以通过innodb_buffer_pool_instances来配置，默认为1，查询语句为：

    mysql> show variables like ‘innodb_buffer_pool_instances‘\G;
    *************************** 1. row ***************************
    Variable_name: innodb_buffer_pool_instances
    Value: 1
    1 row in set (0.00 sec)

查看缓冲池的使用状态可以通过以下语法查询：

    mysql> use information_schema;
    Database changed
    mysql> select pool_id,pool_size,free_buffers,database_pages from INNODB_BUFFER_POOL_STATS\G;
    *************************** 1. row ***************************
    pool_id: 0
    pool_size: 8192
    free_buffers: 7606
    database_pages: 586
    1 row in set (0.00 sec)

2. LRU List、Free List和 Fush List

主要设计对内存池的管理，算法理论太强，看得头痛

通常来说，数据库中的缓冲池是通过LRU(Lastest Recent Used,最近最少使用)算法来管理的。

在InnoDB中，缓冲池页的大小默认为16k

LRU List:最频繁使用的页在LRU列表的首端，最少使用的在尾端，先释放尾端的页

InnoDB中，优化了LRU算法，在列表中加入了midpoint位置，就是说，新加入的页不是直接放在首部，而是放在LRU列表中的 midpoint位置，这个算法称为midpoint insertion strategy，默认下该位置 是在LRU列表长度的5/8，可通过innodb_old_blocks_pct控制(谁他妈的鸟你)

    mysql> show variables like ‘innodb_old_blocks_pct‘\G;
    *************************** 1. row ***************************
    Variable_name: innodb_old_blocks_pct
    Value: 37
    1 row in set (0.00 sec)

在innoDB中，midpoint之后的列表称为old列表，之前的为new列表

优化还加入了另一个参数innodb_old_blocks_time，用来表示页读取到mid位置后需要等待多久才会被加入到LRU列表的new列表，因此可以通过修改这个值来尽可以使LRU列表中的热点数不被刷出。

    mysql> show variables like ‘innodb_old_blocks_time‘\G;
    *************************** 1. row ***************************
    Variable_name: innodb_old_blocks_time
    Value: 0
    1 row in set (0.00 sec)

够狠，默认为0

当页从LRU列表的old部分加入new部分，这个操作称为page make young,而因innodb_old_blocks_time的设置而导致没有从old移动new的操作称为page not make young，可以通过以下方法查看：

    mysql> show engine innodb status\G;
    *************************** 1. row ***************************
    Type: InnoDB
    Name:
    Status:
    =====================================
    141024 7:50:59 INNODB MONITOR OUTPUT
    =====================================
    ....
    ----------------------
    BUFFER POOL AND MEMORY
    ----------------------
    Total memory allocated 137363456; in additional pool allocated 0
    Dictionary memory allocated 219529
    Buffer pool size 8192
    Free buffers 5528
    Database pages 2661
    Old database pages 1002
    Modified db pages 374
    Pending reads 0
    Pending writes: LRU 0, flush list 0, single page 0
    Pages made young 0, not young 0
    0.00 youngs/s, 0.00 non-youngs/s
    Pages read 2655, created 6, written 41
    40.57 reads/s, 0.12 creates/s, 0.80 writes/s
    Buffer pool hit rate 986 / 1000, young-making rate 0 / 1000 not 0 / 1000
    Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
    LRU len: 2661, unzip_LRU len: 0
    I/O sum[0]:cur[32], unzip sum[0]:cur[0]

可以看到buffer pool size有8192,即8198*16=127M左右，（真少）
Free buffers表示当前Free列表中页的数量，
Database pages表示LRU列表页的数量
youngs/s表示第秒page make young的次数
non_yongs/s:每秒page not make young的次数

重要：Buffer pool hit rage,表示缓冲池的命中率，这个通常不小于95%，如果小于95%，请观察是否由于全表扫描引起的LRU列表被污染

可以通过以下语法看缓冲池的使用情况：

    mysql> select pool_id,hit_rate,pages_made_young,pages_not_made_young from information_schema.innodb_buffer_pool_stats\G;
    *************************** 1. row ***************************
    pool_id: 0
    hit_rate: 998
    pages_made_young: 6580
    pages_not_made_young: 0
    1 row in set (0.00 sec)

可以通过以下语句看LRU表的暗体情况：

    mysql> select table_name,space,page_number,page_type from innodb_buffer_page_lru where space=1;
    Empty set (0.16 sec)

没东西....

InnoDB支持压缩功能，原来16K的页压缩为1K

LRU列表中的页被修改后，称为脏页(dirty page)，即缓冲页面上的数据产生了不一致，这时候数据库会通过CHECKPIOINT机制将脏页刷新回磁盘，即Flush列表中的页为脏页列表。

在show engine innodb status中
Modified db pages为是脏页数目

3. 重做日志缓冲

InnoDB内存区除了缓冲池外，还有重做日志缓冲(redolog buffer)。Innodb首先将重做日志放到这个缓冲，再一定频率刷新到重做日志文件。重做缓冲不用太多，因为每一秒就会刷新到日志文件，默认为8M，可通过innodb_log_buffer_size控制

把日志缓冲刷新到日志文件中的情况有以下三种 ：
a. Master Thread每一秒将重做日志缓冲刷新到重做日志文件中
b. 每个事务提交时
c.当重做日志缓冲池剩余空间小于1/2时

4. 额外的内存池

在InnoDB中，对内存的管理是通过一种 称为内存堆 （heap)的方式进行的，在对一些数据结构本身的内存进行分配 时，需要从额外内存池中申请，当该区不够时，会从缓冲池中申请。
例如：分配 了多个缓冲池，但是每个缓冲池的帧缓冲(frame buffer)还有对应的缓冲控制对象(buffer contrl block)，这些对象记录一些如LRU、锁、等待等信息

CheckPoint

上面提到需要把脏页刷新到磁盘，若每一次弄脏的页都刷新，那开销太大，但是如果脏了不刷新，突然停电死机了，那数据就不能恢复了。为了避免这种情况，事务数据库都采用了Write Ahead Log策略，就是当事务提交时，先做重做日志，再修改页。当死机时，可以通过重做日志来完成数据的恢复。

CheckPoint是为了解决以下问题：
a. 缩短数据库的恢复时间
b. 缓冲池不够用时，将脏页刷新回磁盘
c. 重做日志不可用时，刷新脏页

CheckPoint分两种 ：
a. Sharp Checkpoint
b. Fuzzy Checkpoint

Sharp Checkpoint 发生 在数据库关闭时将所有的脏页刷回磁盘
在数据库运行时运行的是Fuzzy Checkpoint,就是只刷新一部分脏页，而不是刷新所有的脏页

Fuzzy CheckPoint分以下几种 情况：
a. Master Thread Checkpoint
b. Flush_lru_list checkpoint
c. Async/Sync Flush Checkpoint
d. Dirty Page too much Checkpoint

Master Thread Checkpoint 差不多以每秒的速度从缓冲池刷新一定比例回磁盘

Flush_lru_list checkpoint是在移除LRU列表的尾端的页面时，刷新脏页的操作
Ayync/Sync Flush Checkpoiint是在重做日志不可用时，强制刷新

Dirty page too mush是在脏页太多的情况下，强制刷新。可用 innodb_max_dirty_pages_pct来控制