0%

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