首页 > 代码库 > Mysql 笔记(一)
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来控制
Mysql 笔记(一)