MySQL基本上可以分为Server层和引擎层。Server层包括连接器、查询缓存、分析器、优化器、执行器等，涵盖了MySQL的大部分核心服务以及所有的内置函数；存储引擎层负责数据的存储和提取，其架构为插件形式，支持Innodb、MyISAM、Memory等多种存储引擎，目前OLTP系统大多数采用InnoDB，从MySQL5.5开始便作为默认存储引擎存在。

MySQL Server

连接器

连接器主要负责与客户端建立通信连接，获取权限，维持和管理连接。建立连接在完成TCP握手后会首先验证账户名密码，如果错误则会抛出Access denind for user的错误。如果用户名密码正确会验证连接用户的权限，一个用户成功建立连接后，即使更改了权限也不会影响当前会话，新建立的连接才会获取新的权限。

连接会话在没有操作的情况下会处于sleep空闲状态，当长时间处于空闲状态(默认8小时)，连接器会关闭该会话，时间由参数wait_timeout控制

Q：为什么修改了wait_timeout超时时间没有发生变化？
A：修改wait_timeout的同时需要修改interactive_timeout参数，这一点是根据客户端类型来决定调用哪个参数。在通过set global的方式在线修改参数后需要同步写入my.cnf，否则重启将失效。

Q：使用长连接后，MySQL内存增长太快？
A：MySQL在执行过程中临时使用的内存是管理在连接对象中的，在会话断开后才会释放，大量长连接累积容易导致内存增长太快，甚至发生OOM。因此可以考虑如下两种方案：定时断开长连接；如果MySQL版本为5.7或更高，可在大事务操作后执行mysql_reset_connection来重新初始化连接资源，相当于恢复到刚连接成功时的状态。

查询缓存

通过连接器连接上数据库之后，执行一条select查询，执行逻辑会先判断查询缓存中是否存在，如果之前执行过相同SQL，会以key-value的形式放在缓存中，后续查询就能直接从查询缓存中通过key查询对应的value直接返回客户端；如果不存在则会往后执行，在执行完成后再存到缓存中。

但查询缓存失效非常频繁，只要对表进行更新，这个表上所有的查询缓存都会被清空，查询缓存命中率太低。因此，建议在生产环境关闭查询缓存或者将参数query_cache_type设置为DEMAND让SQL需要显示指定使用查询缓存，如下面这个SQL。这一功能已在8.0中删除。

mysql> select SQL_CACHE * from T where ID=10；

分析器

在绕过查询缓存后，MySQL需要对SQL语句进行解析。首先会进行词法分析，对SQL语句各个字符串进行分析，操作类型，表，字段等等；然后再进行语法分析，判断SQL是否满足MySQL的语法要求

优化器

在解析完SQL之后，在正式执行之前还需要经过优化器的处理；比如索引的选择，表连接的顺序，生成相应的执行计划。优化器的逻辑就是寻找一个最优的执行计划，以最小的代价去执行语句。

执行器

在优化器生成执行计划之后，就正式开始执行SQL了，执行之前会先判断用户的权限，紧接着打开表，根据表的定义调用引擎提供的接口去操作数据。

INNODB存储引擎

MySQL存储引擎以插件形式提供，存储引擎是基于表的，针对不同的表可以使用不同的存储引擎。INNODB目前是MySQL默认的存储引擎，使用最为广泛。

  • INNODB存储引擎支持事务，主要面向OLTP的应用，支持行锁，外键等特性。另外还有一些其它的存储引擎：
  • MyISAM：MyISAM存储引擎不支持事务，采用表锁设计，并发性较差，主要面向OLAP的应用。
  • NDB：NDB存储引擎是一个集群存储引擎，NDB将内存全部放在内存中，并且支持横向扩展存储节点来提供数据库性能以及高可用性。
  • Federated：Federated存储引擎表并不存放数据，仅通过连接远程MySQL获取表数据，类似Oracle中的透明网关。
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

后台线程

  • Master Thread：主要负责将缓冲池中的数据异步刷新到磁盘，保证数据的一致性。其任务包含脏页刷新，合并插入缓冲，undo页回收等
  • IO Thread：INNODB中大量使用Async IO来处理写IO请求，提高数据库性能，IO Thread主要负责IO请求的回调处理，其中包含read和write，由参数innodb_read_io_threads和innodb_write_io_threads控制数量
  • Purge Thread：事务提交之后undo log就没用了，因此需要Purge Thread来回收undo页。可以通过参数innodb_purge_threads增加purge线程数，加快回收速度
  • Page Cleaner Thread：为了减轻Master Thread压力，利用Page Cleaner Thread来完成脏页刷新的功能

内存

Buffer Pool

INNODB通过划分一块共享内存区域作为数据缓存池，其中包含changer buffer,lock info、数据字典信息等，其大小由参数innodb_buffer_pool_size控制。缓冲池能够有效减少随机IO请求，提升数据库性能，数据库读取页时会将数据存放到缓冲池，如果下次读取相同的页则直接从内存返回；对数据页进行修改时，也是先修改缓冲池中的页，这就已经完成了修改，数据等后续脏页刷新到磁盘中。

INNODB支持多个缓冲池实例，每个页根据哈希值平均分配到不同缓冲池实例中，减少内部资源争用，增加并发处理能力，可以通过参数innodb_buffer_pool_instances来进行设置，默认为1，我们可以设置为8。通过show engine innodb status能够查看每个实例的情况

INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size   122865
Free buffers       37607
Database pages     85001
Old database pages 31394
Modified db pages  8423
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1118, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 68171, created 16830, written 2435299
0.29 reads/s, 0.31 creates/s, 17.72 writes/s
Buffer pool hit rate 1000 / 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: 85001, unzip_LRU len: 0
I/O sum[0]:cur[49], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size   122865
Free buffers       42950
Database pages     79656
Old database pages 29424
Modified db pages  136
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1221, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 62802, created 16854, written 1598926
0.19 reads/s, 0.02 creates/s, 17.72 writes/s
Buffer pool hit rate 1000 / 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: 79656, unzip_LRU len: 0
I/O sum[0]:cur[49], unzip sum[0]:cur[0]

LRU

数据库缓冲池通过LRU(Lastest Recent Used)算法来进行页管理的，也就是最频繁使用的页在列表的前端，最少使用的页在尾端。当缓冲池没有空闲空间时，将释放最尾端的页来保存新的数据。其中新读取的页并不是直接放在列表前端，而是放在LRU中的midpoint位置，midpoint的位置由参数innodb_old_blocks_pct控制，默认37，也就是LRU列表尾端37%的位置，在midpoint之后的为old列表，之前的为new列表。为了进一步加强LRU的管理，引入参数innodb_old_blocks_time参数控制页读取到midpoint后需要多久才能加载到LRU列表前端，避免热点数据页被移除。

数据库刚启动时，LRU列表为空，页都存放在Free列表中，当需要从缓冲池分配页时，先从Free列表查找是否有空闲页，有就将该页从Free列表中删除放入LRU中，否则根据算法淘汰LRU尾端的页，将内存页分配给新的页。当页从LRU列表的old部分加入new部分时的操作为page made young，因为innodb_old_blocks_time的设置没有从old加入new的操作为page not made young。通过show engine innodb status可以观察LRU列表即FREE列表的使用情况和运行状态

Total large memory allocated 16491479040
Dictionary memory allocated 82406262
Buffer pool size   982920
Free buffers       338741
Database pages     641998
Old database pages 237104
Modified db pages  8849
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 9553, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 509587, created 132411, written 13966604
1.98 reads/s, 1.62 creates/s, 134.07 writes/s
Buffer pool hit rate 1000 / 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: 641998, unzip_LRU len: 0
I/O sum[0]:cur[392], unzip sum[0]:cur[0]

从上述信息可以看出Buffer pool中有982920个页，即9829208*16K；free buffers表示当前Free列表中页的数量；Database pages表示LRU列表中页的数量；Buffer pool hit rate表示缓冲池命中率，这个值应该无限接近100%。

Innodb支持压缩页功能，可以将原本16K的页压缩为4K、8K，而由于页的大小发生了改变，LRU列表也有些改变，对于非16K的页是通过unzip_LRU进行管理的，对于不同的压缩页大小，分别管理并通过伙伴算法进行内存分配，例如要申请4K的页，其过程如下：

  • 检查4K的unzip_LRU列表，检查是否有可用页
  • 若有，则直接使用
  • 没有，检查8K的unzip_LRU列表
  • 若能得到空闲页，将页分裂成2个4K页，存放到4K的unzip_LRU列表
  • 若不能得到空闲页则向上申请一个16K的页，将页分成一个8K页，2个4K页，分别存放到对应的unzip_LRU列表
    我们可以通过information_schema.INNODB_BUFFER_PAGE_LRU来查看unzip_LRU中的页

在LRU中的页被修改后，就变成了脏页。这时内存中的数据和磁盘上的数据发生了不一致的情况，数据库会通过checkpoint机制将脏页刷新回磁盘，而Flush列表的页则为脏页列表。Modified db pages表示脏页数量，我们也可以通过查询information_schema.INNODB_BUFFER_PAGE_LRU中OLDEST_MODIFICATION大于0的页

重做日志缓冲

在一个高并发的OLTP系统中，如果每次DML操作都要从数据文件拉取对应记录修改再回写到数据文件中，这样产生的IO成本和时间成本都太高。因此MySQL采用了WAL(Write-Ahead Logging)机制，即先写日志，再写磁盘。InnoDB将更新记录写到redo log buffer中，然后同步刷新到重做日志文件，这个时候就已经完成了。redo log buffer的大小由参数innodb_log_buffer_size控制，默认为8MB。在下列三种情况下，会将redo log buffer中的内容刷新到redo log磁盘中

  • Master Thread每一秒将重做日志缓冲刷新到重做日志文件
  • 每个事务提交时会将重做日志缓冲刷新到重做日志文件
  • 当重做日志缓冲池空间小于1/2时，重做日志缓冲会刷新到重做日志文件

Redo log能够在实例崩溃或异常重启时，保证已提交的记录不会丢失，这种能力称之为crash-safe。因此建议将参数innodb_flush_log_at_trx_commit设置为1，确保每个事务的redo log持久化到磁盘。

Redo log刷盘的触发条件有四种：

  • redo log写满了，这时数据库无法写入，需要等待推进check point
  • 内存不足，把数据加载到内存中时内存不足了会把脏页写入磁盘
  • 系统空闲时间刷盘，当系统空闲时，数据库会自动进行刷盘
  • 数据库正常关闭，会把脏页写入数据文件，启动时能更快

Checkpoint

Checkpoint用于解决下列几个问题：

  1. 缩短数据库的恢复时间。
  2. 缓冲池不够用时，将内存中的脏页刷新到磁盘中
  3. 重做日志不可用时，刷新脏页

缓冲池不够用时，根据LRU算法会溢出最近最少使用的页，若此页为脏页，那么就需要通过checkpoint先将脏页回刷到磁盘中，此时内存页就变成了干净页，可以复写了。

重做日志文件大小是固定的，由innodb_log_file_size控制大小，innodb_log_file_in_group控制数量。日志从头开始写，写满之后又从头开始循环写入。

redo log

Write pos是当前记录位置，一边写一边向后移动，写道logfile_3的末尾后就要回到文件0的开头位置；Check point是当前要擦除的位置，也是向后推动的，擦除记录前要把记录写入到数据文件中。Write pos和check point之间表示干净页，可直接写入。如果Write pos追上check point，则表示日志满了，需要先将redo写入数据文件，推进check point。

对于InnoDB而言，其是通过LSN(Log Sequence Number)来标记版本的，LSN由8个字节的数字组成，每个页有LSN，重做日志也有LSN，Checkpoint也有LSN

---
LOG
---
Log sequence number 12233084271098
Log flushed up to   12233084270970
Pages flushed up to 12232939243084
Last checkpoint at  12232939162280

在Innodb引擎内部，有两种checkpoint，分别为sharp Checkpoint和Fuzzy Checkpoint。Sharp发生在数据库关闭时将所有脏页都刷新回磁盘，而Fuzzy则为每次都只刷新部分脏页，触发Fuzzy checkpoint有下面几种情况：

  • Master Thread以每秒或每10秒的速度从缓冲池的脏页列表中刷新一定比例的页到磁盘中，这个过程是异步进行的。
  • Innodb需要保证LRU列表中有一定的空闲页可供使用，如果不满足就需要从LRU尾端释放页，如果是脏页就需要进行checkpoint。检查由Page Cleaner线程进行，用户可以通过参数innodb_lru_scan_depth控制LRU可用页的数量，默认为1024
  • 重做日志文件不可用的情况下，需要强制将一些页刷回磁盘，页是从脏页列表中选取。若将已经写入到重做日志的LSN记为redo_lsn，将已刷新回磁盘最新页的LSN记为checkpoint_lsn，则定义
    checkpoint_age = redo_lsn - checkpoint_lsn
    再定义以下变量：
    async_water_mark = 75% * total_redo_log_file_size
    sync_water_mark = 90% * total_redo_log_file_size
    若每个重做日志文件大小为1G，有2个重做日志文件，则重做日志大小为2G，那么async_water_mark=1.5G，sync_water_mark=1.8G，则当checkpoint_age<async_water_mark时，不需要刷新任何脏页到磁盘；当async_water_mark<checkpoint_age<sync_water_mark时触发Async Flush，从脏页列表刷新足够的脏页回磁盘，使得满足checkpoint_age<async_water_mark
  • 当脏页比例超过参数innodb_max_dirty_pages_pct时，强制推进checkpoint，默认为75%。当前脏页比例通过算法得到一个0-100的值，当前日志序号与checkpoint序号的差值通过算法得到一个0-100的值记，取二者其中的最大值记为R，刷脏页的速度就等于innodb_io_capacity乘以R%。当前脏页比例可通过innodb_buffer_pool_pages_dirty/innodb_buffer_pool_pages_total计算

控制脏页刷盘策略，首先要告诉Innodb磁盘IO的能力，刷脏页的时候能有多快，通过参数innodb_io_capacity来设置，建议设置为磁盘的IOPS。可以通过fio工具来测试磁盘IOPS

$ fio -filename=/service/iotest -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=16k -size=500M -numjobs=10 -runtime=10 -group_reporting -name=mytest

在准备刷一个脏页时，如果数据页的邻居也是脏页，则一同写入磁盘，这个过程会一直延续下去，类似于连坐机制。这种行为是由参数innodb_flush_neighbors控制，为1则开启，为0则禁止，为2则只刷新连续的脏页。在使用SSD或者IOPS比较高的情况下，IOPS已经不存在性能瓶颈情况下，可以设置为0，并且8.0中也将这一参数默认设置为0

double write

doublewrite确保了Innodb存储引擎的数据页的可靠性。当数据库宕机时，可能存在部分写失效问题，也就是一个数据页只写了一部分就发生了宕机，这种情况页已经损坏，是无法利用redo日志进行恢复的。doublewrite的解决这种问题的方法就是在应用重做日志前，对页复制出一个副本，当写入失效发生时，先通过页的副本来还原页，再进行重做。

doublewrite

doublewrite由两部分组成，一部分是在内存中分配的doublewrite buffer，大小为2MB，另一部分就是共享表空间中连续的128个页，即2个区，大小也为2M。在对缓冲池的脏页进行刷新时，并不直接写磁盘，而是通过memcpy函数将脏页先复制到doublewrite buffer，之后通过doublewrite buffer分两次，每次1MB顺序写入共享表空间，然后调用fsync函数同步磁盘。在完成double write页的写入后，再将doublewrite buffer中的页写入各个表空间，此时是随机的。通过下列命令观察doublewrite的情况

mysql> show global status like 'innodb_dblwr%'\G
*************************** 1. row ***************************
Variable_name: Innodb_dblwr_pages_written
        Value: 15467
*************************** 2. row ***************************
Variable_name: Innodb_dblwr_writes
        Value: 4504
2 rows in set (0.09 sec)

Innodb_dblwr_pages_written:Innodb_dblwr_writes远小于64:1，说明系统写入压力并不是很高

changer buffer

在进行插入操作时，数据页的存放是按照主键进行顺序存放的，对于非主键二级索引叶子节点的插入不再是顺序的，这时就会造成随机IO，导致插入性能降低。为此，InnoDB引入了Insert buffer，对于非聚集索引的插入，不是直接插入索引页中，而是先判断插入的非聚集所以是否在缓冲池，若在的话直接插入；若不在则先放入到Insert buffer中，后续再以一定的频率和情况进行Insert buffer和二级索引叶子节点的merge合并操作，提升了非聚集索引的插入性能。

changer buffer是对insert buffer的升级，在insert的基础上增加了delete、update的支持。在更新数据页时，如果数据页在内存中则直接更新，如果不在内存则在不影响一致性的前提下将操作记录在change buffer中，待下次访问数据页再从磁盘读取到内存中并执行change buffer，这个过程称之为merge，后台线程会定期进行merge，正常关闭数据库也会执行merge。

change buffer是可持久化的，既写入内存，也会写入磁盘。change buffer使用的是buffer pool的内存，由参数innodb_change_buffer_max_size控制，默认为25，最大可以设置为50则表示能占用50%的buffer pool。

mysql> show engine innodb status\G
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 276671, node heap has 1 buffer(s)
Hash table size 276671, node heap has 0 buffer(s)
Hash table size 276671, node heap has 0 buffer(s)
Hash table size 276671, node heap has 0 buffer(s)
Hash table size 276671, node heap has 0 buffer(s)
Hash table size 276671, node heap has 1 buffer(s)
Hash table size 276671, node heap has 1 buffer(s)
Hash table size 276671, node heap has 1 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s

seg size显示了当前Insert buffer的大小为2*16KB，free list len表示空闲列表的长度，size表示已经合并记录页的数量，merges表示合并的次数。insert表示insert buffer，delete mark表示delete buffer，delete表示purge buffer，discarded operations表示merge时表已删除，无需再进行merge了。

唯一索引必须将数据页读到内存中才能做判断，因此唯一索引无法使用change buffer或insert buffer，只能针对普通二级索引。在插入完后立马进行读的场景下，会频繁进行merge，增加change buffer的维护工作，建议通过参数innodb_change_buffering关闭change buffer，其它场景都建议通过普通索引配合change buffer使用，减少磁盘IO。

自适应哈希

hash是一种非常快的查询方法，一般情况下时间复杂度未O(1)，而B+树的查找次数取决于B+树的高度。InnoDB会监控表上各索引页的查询，如果观察到建立哈希索引可以带来速度提升，则建立哈希索引，这种机制称之为自适应哈希索引(AHI)，AHI是通过缓冲池的B+树页构造而来，因此建立的速度很快，而不需要对整张表结构建立哈希索引。InnoDB会根据访问的频率和模式来自动为某些热点页建立哈希索引，哈希索引仅适用于等值查询。

AHI要求页的连续访问模式是一样的，例如对于复合索引(a，b)，其访问模式可能是a单独作为条件或者ab同时作为条件，如果两种模式交叉访问就不会对该页构造AHI。此外AHI还要求以该模式访问100次，页通过该模式访问N次，其中N等于页中记录*1/16

启用AHI后，读取和写入速度都得到了提升，其是数据库自动优化的，无需人工干预。我们可以通过show engine来查看AHI的使用情况

Hash table size 276671, node heap has 1 buffer(s)
Hash table size 276671, node heap has 0 buffer(s)
Hash table size 276671, node heap has 0 buffer(s)
Hash table size 276671, node heap has 0 buffer(s)
Hash table size 276671, node heap has 1 buffer(s)
Hash table size 276671, node heap has 1 buffer(s)
Hash table size 276671, node heap has 1 buffer(s)
Hash table size 276671, node heap has 1 buffer(s)
12.00 hash searches/s, 43.00 non-hash searches/s

InnoDB关闭行为

在实例关闭时，参数innodb_fast_shutdown影响着表的存储引擎为InnoDB的行为，参数取值范围为0、1、2，默认值为1。

  • 0表示MySQL关闭时，InnoDB需要完成所有的full purge和merge changer buffer，并将所有的脏页刷新回磁盘。在升级时，必须将这个参数调为0，然后再关闭数据库
  • 1表示不需要完成上述full purge和merge changer buffer，但还是会刷新部分脏页
  • 2表示不完成full purge和merge changer buffer操作，也不将缓冲池脏页刷新，而是将日志全部写入日志文件，在下次启动时会进行recovery操作

参数innodb_force_recovery影响了整个InnoDB恢复的情况，某些情况下用户自己知道如何进行恢复，例如在ALTER TABLE时出现异常，InnoDB会进行回滚操作，对于大表非常耗时，用户可以通过自己操作更快的恢复。参数可选值范围为0-6

  • 0为默认值，当需要恢复时，进行所有的恢复操作，如果恢复失败则关闭实例并将错误写入错误日志
  • 1表示忽略检查到的corrupt页
  • 2表示阻止MT线程的运行，如果MT线程需要执行full purge，则会发生crash
  • 3表示不进行事务的回滚操作
  • 4表示不进行插入缓冲的合并操作
  • 5表示不查看undo log，所有未提交的事务都会视为已提交
  • 6表示不进行前滚操作

当innodb_force_recovery大于0时，用户可以对表执行select、create和drop，但不能执行DML操作