MySQL Study之--MySQL体系结构深入解析

素颜马尾好姑娘i 2022-08-02 04:55 158阅读 0赞

**MySQL Study之--MySQL体系结构深入解析**

**MySQL体系架构**

![Center][]

由连接池组件、管理服务和⼯工具组件、sql接口组件、查询分析器组件、优化器组件、缓冲组件、插件式存储引擎、物理⽂文件组成。mysql是独有的插件式体系结构,各个存储引擎有自己的特点。

**MySQL内存结构：**

**![Center 1][]**

**Mysql 进程结构**

Mysql不像oracle那样是通过多进程来完成其功能的。默认情况下，InnoDB存储引擎的后台线程有7个：

**4个IO thread，**

**1个master thread，**

**1个锁（lock）监控线程，**

**1个错误监控线程；**

在InnoDB Plugin版本开始增加了默认IO thread的数量，默认的read thread和write thread分别增大到了4个，并且不再使用innodb\_file\_ io\_threads参数，而是分别使用innodb\_read\_io\_threads和innodb\_write\_io\_threads参数。

**查看mysql thread：**

mysql> show engine innodb status;
 =====================================
 150709 15:26:40 INNODB MONITOR OUTPUT
 =====================================
 Per second averages calculated from thelast 12 seconds
 -----------------
 BACKGROUND THREAD
 -----------------
 srv_master_thread loops: 0 srv_active, 0srv_shutdown, 804 srv_idle
 srv_master_thread log flush and writes: 804
 ----------
 SEMAPHORES
 ----------
 OS WAIT ARRAY INFO: reservation count 2
 OS WAIT ARRAY INFO: signal count 2
 Mutex spin waits 0, rounds 0, OS waits 0
 RW-shared spins 2, rounds 60, OS waits 2
 RW-excl spins 0, rounds 0, OS waits 0
 Spin rounds per wait: 0.00 mutex, 30.00RW-shared, 0.00 RW-excl
 ------------
 TRANSACTIONS
 ------------
 Trx id counter 5377
 Purge done for trx's n:o < 4872 undo n:o< 0
 History list length 1
 LIST OF TRANSACTIONS FOR EACH SESSION:
 ---TRANSACTION 0, not started
 MySQL thread id 3, OS thread handle0x7f1656c75700, query id 5 localhost root init
 show engine innodb status
 --------
 FILE I/O
 --------
 I/O thread 0 state: waiting for completedaio requests (insert buffer thread)
 I/O thread 1 state: waiting for completedaio requests (log thread)
 I/O thread 2 state: waiting for completedaio requests (read thread)
 I/O thread 3 state: waiting for completedaio requests (read thread)
 I/O thread 4 state: waiting for completedaio requests (read thread)
 I/O thread 5 state: waiting for completedaio requests (read thread)
 I/O thread 6 state: waiting for completedaio requests (write thread)
 I/O thread 7 state: waiting for completedaio requests (write thread)
 I/O thread 8 state: waiting for completedaio requests (write thread)
 I/O thread 9 state: waiting for completedaio requests (write thread)
 Pending normal aio reads: 0 [0, 0, 0, 0] ,aio writes: 0 [0, 0, 0, 0] ,
 ibufaio reads: 0, log i/o's: 0, sync i/o's: 0
 Pending flushes (fsync) log: 0; bufferpool: 0
 283 OS file reads, 5 OS file writes, 5 OSfsyncs
 0.00 reads/s, 0 avg bytes/read, 0.00writes/s, 0.00 fsyncs/s
 -------------------------------------
 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 276707, node heap has 0buffer(s)
 0.00 hash searches/s, 0.00 non-hashsearches/s
 ---
 LOG
 ---
 Log sequence number 1611537
 Log flushed up to 1611537
 Pages flushed up to 1611537
 Last checkpoint at 1611537
 0 pending log writes, 0 pending chkp writes
 8 log i/o's done, 0.00 log i/o's/second
 ----------------------
 BUFFER POOL AND MEMORY
 ----------------------
 Total memory allocated 137363456; inadditional pool allocated 0
 Dictionary memory allocated 39010
 Buffer pool size 8192
 Free buffers 8040
 Database pages 152
 Old database pages 0
 Modified db pages 0
 Pending reads 0
 Pending writes: LRU 0, flush list 0 singlepage 0
 Pages made young 0, not young 0
 0.00 youngs/s, 0.00 non-youngs/s
 Pages read 152, created 0, written 1
 0.00 reads/s, 0.00 creates/s, 0.00 writes/s
 No buffer pool page gets since the lastprintout
 Pages read ahead 0.00/s, evicted withoutaccess 0.00/s, Random read ahead 0.00/s
 LRU len: 152, unzip_LRU len: 0
 I/O sum[0]:cur[0], unzip sum[0]:cur[0]
 --------------
 ROW OPERATIONS
 --------------
 0 queries inside InnoDB, 0 queries in queue
 0 read views open inside InnoDB
 Main thread process no. 2461, id139733873489664, state: sleeping
 Number of rows inserted 0, updated 0,deleted 0, read 0
 0.00 inserts/s, 0.00 updates/s, 0.00deletes/s, 0.00 reads/s
 ----------------------------
 END OF INNODB MONITOR OUTPUT

[root@mysrv ~]# mysql -u root -p
 Enter password:
 Welcome to the MySQL monitor. Commands end with ; or \g.
 Your MySQL connection id is 3
 Server version: 5.6.4-m7-log Sourcedistribution
 
 Copyright (c) 2000, 2011, Oracle and/or itsaffiliates. All rights reserved.
 
 Oracle is a registered trademark of OracleCorporation and/or its
 affiliates. Other names may be trademarksof their respective
 owners.
 
 Type 'help;' or '\h' for help. Type '\c' toclear the current input statement.
 
 mysql> show variables like'innodb_%io%';
 +---------------------------------+---------+
 | Variable_name | Value |
 +---------------------------------+---------+
 | innodb_additional_mem_pool_size | 8388608|
 | innodb_io_capacity | 200 |
 | innodb_read_io_threads | 4 |
 | innodb_replication_delay | 0 |
 | innodb_use_native_aio | ON |
 | innodb_version | 1.2.4 |
 | innodb_write_io_threads | 4 |
 +---------------------------------+---------+
 7 rows in set (0.01 sec)

**后台线程主要作用有三个：**

1. 刷新内存池中的数据，保证缓冲池中的内存缓存的是最近的数据

2. 将修改的数据文件刷新到磁盘文件

3. 在数据库发生异常情况下，保证InnoDB能恢复到正常运行状态

master thread的线程优先级别最高。其内部由几个循环（loop）组成：主循环（loop）、后台循环（backgroundloop）、刷新循环（flush loop）、暂停循环（suspend loop）。master thread会根据数据库运行的状态在loop、background loop、 flush loop和suspend loop中进行切换。loop称为主循环，因为大多数的操作都在这个循环中，其中有两大部分操作：每秒钟的操作和每10秒的操作.

Loop为主循环，如果没有用户活动，就切换到backgroundloop中，然后跳转会loop，如果还需要flush刷新，那就转到flush loop，如果在flush loop中也空闲，那就切换到suspend loop中，将master thread挂起等待事件的发生。

**Mysql 进程结构**

**![Center 2][]**

**![Center 3][]**

**Loop：**

每秒一次的操作包括：

™   日志缓冲刷新到磁盘，即使这个事务还没有提交（总是）。

™   合并插入缓冲（可能）。

™   至多刷新100个InnoDB的缓冲池中的脏页到磁盘（可能）。

™   如果当前没有用户活动，切换到background loop（可能）。

**接着来看每10秒的操作，包括如下内容**：

™   刷新100个脏页到磁盘（可能）。

™   合并至多5个插入缓冲（总是）。

™   将日志缓冲刷新到磁盘（总是）。

™   删除无用的Undo页（总是）。

™   刷新100个或者10%脏页到磁盘（总是）。

™   产生一个检查点（总是）。

**Background loop会执行以下操作**

™   删除无用的Undo页（总是）。

™   合并20个插入缓冲（总是）。

™   跳回到主循环（总是）。

™   不断刷新100个页，直到符合条件（可能，跳转到flush loop中完成）。

**Mysql进程结构\--控制磁盘io的参数**

**™  innodb\_io\_capacity**

™  用来表示磁盘IO的吞吐量，默认值为200。对于刷新到磁盘的数量（脏缓冲），会按照innodb\_io\_capacity的百分比来刷新相对数量的页

™  在合并插入缓冲时，合并插入缓冲的数量为innodb\_io\_capacity数值的5%。

™  在从缓冲区刷新脏页时，刷新脏页的数量为innodb\_io\_capacity。

™  **innodb\_max\_dirty\_pages\_pct**

™    默认值为75%。加快脏页刷新频率，减少恢复时间，也可保证磁盘IO负载。

**™  innodb\_adaptive\_flushing：**

™  在innodbplugin中。该值影响每1秒刷新脏页的数量。原来的刷新规则是：如果脏页在缓冲池所占的比例小于innodb\_max\_dirty\_pages\_pct时，不刷新脏页。大于innodb\_max\_dirty\_pages\_pct时，刷新100个脏页，而innodb\_adaptive\_flushing参数的引入，InnoDB存储引擎会通过一个名为buf\_flush\_get\_desired\_flush\_rate的函数判断产生重做日志的速度来判断最合适的刷新脏页的数量。因此，当脏页的比例小于innodb\_max\_dirty\_pages\_pct时，也会刷新一定量的脏页。

™  还有两个和mysql启动和关闭有关的参数

™**  innodb\_fast\_shutdown**

™  这个参数影响着innodb表的行为，该参数可设置为0，1，2

™  0: 表示当MySQL关闭时，InnoDB需要完成所有的full purge和merge insert buffer操作。耗时

™     比较长。

™  1: 是默认值。表示不需要完成full purge和merge insert buffer操作，但是在缓冲池中的一

™     些数据脏页会刷新到磁盘。

™  2: 表示不完成full purge和merge insert buffer操作，也不将缓冲池中的数据脏页写回到磁

™     盘，而是将日志写入到日志文件。MySQL下次启动时，会执行恢复操作。

™**  innodb\_force\_recovery**

™      影响InnoDB的恢复状况。默认为0，表示需恢复时执行所有的恢复操作。若不能有效恢复，则MySQL有可能宕机，错误信息会被写入错误日志文件。还有 1~6等不同值，根据

™  需要可以设置。

™  0 :正常的关闭和启动，不会做任何强迫恢复操作；

™  1 :跳过错误页，让mysqld服务继续运行。跳过错误索引记录和存储页，可以执行备份操作

™  2 :阻止InnoDB的主线程运行。清理操作时出现mysqld服务崩溃，则会阻止数据恢复操作；

™  3 :恢复的时候，不进行事务回滚；

™  4 :阻止INSERT缓冲区的合并操作。不做合并操作，为防止出现mysqld服务崩溃。不计算表的统计信息

™  5 :mysqld服务启动的时候不检查回滚日志：InnoDB引擎对待每个不确定的事务就像提交的事务一样；

™  6 :不做事务日志前滚恢复操作；

**Mysql内存结构—buffer pool**

InnoDB存储引擎内存主要由几个部分组成：缓冲池（bufferpool）、重做日志缓冲池（redo log buffer）以及额外的内存池（additional memory pool）

缓冲池是占内存最大的部分，用来存放各种数据的缓存。因为InnoDB的存储引擎的工作方式总是将数据库文件按页（每页16K）读取到缓冲池，然后按最近最少使用（LRU）的算法来保留在缓冲池中的缓存数据。如果数据库文件需要修改，总是首先修改在缓存池中的页（发生修改后，该页即为脏页），然后再按照一定的频率将缓冲池的脏页刷新（flush）到文件。

**buffer pool是通过三种list来管理的：**

1) free list

2) lru list

3) flush list

buffer pool中的最小单位是page，在innodb中定义三种page

1) free page :此page未被使用，此种类型page位于free链表中

2) clean page:此page被使用，对应数据文件中的一个页面，但是页面没有被修改，此种类型

page位于lru链表中

3) dirty page:此page被使用，对应数据文件中的一个页面，但是页面被修改过，此种类型page

位于lru链表和flush链表中

**插入缓冲：**

不是缓冲池的一部分，InsertBuffer是物理页的一个组成部分,它带来InnoDB性能的提高。根据B+算法(下文会提到)的特点，插入数据的时候会主键索引是顺序的，不会造成数据库的随机读取，而对于非聚集索引(即辅助索引)，叶子节点的插入不再是顺序的了，这时需要离散地访问非聚集索引，插入性能在这里变低了。InnoDB引入插入缓冲，判断非聚集索引页是否在缓冲池中，如果在则直接插入;不在，则先放在插入缓冲区中。然后根据上述master thread中介绍的，会有一定的频率将插入缓冲合并。此外，辅助索引不能是唯一的，因为插入到插入缓冲时，并不去查找索引页的情况，否则仍然会造成随机读，失去插入缓冲的意义了。插入缓冲可能会占缓冲池中内存，默认也能会占到1/2，所以可以将这个值调小点，到1/3。通过IBUF\_POOL\_SIZE\_PER\_MAX\_SIZE来设置，2表示1/2,3表示1/3。  
  
**两次写：**

它带来InnoDB数据的可靠性。如果写失效，可以通过重做日志进行恢复，但是重做日志中记录的是对页的物理操作，如果页本身损坏，再对其进行重做是没有意义的。所以，在应用重做日志前，需要一个页的副本，当写入失效发生时，先通过页的副本来还原该页，再进行重做，这就是doublewire。  
恢复数据=页副本+重做日志

![Center 4][]

**Mysql内存结构—log buffer与管理池**

**日志缓冲：**

日志缓冲将重做日志信息先放入这个缓冲区，然后按一定频率将其刷新到重做日志文件。该值一般不需要设置为很大，因为一般情况下每一秒钟就会将重做日志缓冲刷新到日志文件，因此我们只需要保证每秒产生的事务量在这个缓冲大小之内即可。

**额外的内存池：**

额外的内存池同样十分重要。在InnoDB存储引擎中，对内存的管理是通过一种称为内存堆（heap）的方式进行的，他是用于缓存InnoDB引擎的数据字典信息和内部数据结构，在对一些数据结构本身分配内存时，需要从额外的内存池中申请，当该区域的内存不够时，会从缓冲池中申请，若是mysqld服务上的表对象数量较多，InnoDB引擎数据量很大，且innodb\_buffer\_pool\_size的值设置较大，则应该适当地调整innodb\_additional\_mem\_pool\_size的值。若是出现缓存区的内存不足，则会直接向操作系统申请内存分配，并且会向MySQL的error log文件写入警告信息。

**binlog和redolog区别：**

**1) binary log,会记录所有与MySQL有关的日志记录,包括 Innodb、MyISAM、Heap等其他存储引**

擎的日志而 innodb存储引擎的重做日志(redolog),只记录有关其本身的事务日志

**2) 记录内容不同：**  
      binary log:不管你将二进制日志文件的记录格式设置为STATEMENT，还是ROW,又或是

MIXED, 其记录的都是关于一个事务的具体操作内容.  
      redo log:  记录innodb 每个页的更改的物理情况

**3）写入时间不同：**  
      binary log：事务提交前进行记录  
      redo log ：事物进行过程中，不断写入。

[Center]: /images/20220731/fd44a24a7c7a4b3b9ea203165bea5984.png
[Center 1]: /images/20220731/7ab1ba504451488d94e605a5c3f99110.png
[Center 2]: /images/20220731/3b4add1a444f455b9d035e67bfc373dc.png
[Center 3]: /images/20220731/10bfebb3522e42cda65f0e2101c7b764.png
[Center 4]: /images/20220731/dbe35319cbe440bab84ab358cb02fa1c.png