MySQL

MySQL体系结构

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中删除。

1

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中的透明网关。
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

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能够查看每个实例的情况

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

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列表的使用情况和运行状态

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

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

1
2
3
4
5
6
7

---
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

1

$ 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的情况

1
2
3
4
5
6
7
8

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。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

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的使用情况

1
2
3
4
5
6
7
8
9

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操作

Concept
Licensed under CC BY-NC-SA 4.0
comments powered by Disqus