漫谈mysql体系结构 mysql的体系结构

mysql 体系结构 Innodb体系结构

• 一、Mysql 体系结构

• SQL 层包含权限判断，查询缓存，解析器，预处理，查询优化器，缓存和执行计划

• 权限判断:可以审核用户没有访问某个库，某个表，或者表里某行的权限
• 查询缓存:通过Query Cache 进行操作，如果数据在Query Cache中，则直接返回结果给客户端
• 查询解析器:针对sql语句进行解析，判断语法是否正确
• 预处理器:对解析器无法解析的语义进行处理，如数据表和数据列是否存在，解析列名和别名，是否有歧义，接下来预处理器会验证用户权限
• 优化器:对sql进行改写和相应的优化，索引选择，并生成最优的执行计划，就可以调用API接口，通过存储引擎访问数据
• 优化器功能：

• 二、query_cache详解
• 三、InnoDB体系结构

• **1、Master Thread**
• **2、IO Thread**
• **3、Purage Thread**
• **4、Page Cleaner Thread**
• undo页
• redo是啥
• 内存

• 1、缓存池

• 那么什么是查询缓存呢？

• 四、InnoDB存储结构

• 表空间
• 段
• 区
• 页
• 行

• 五、内存结构

• 先来介绍系统全局区(SGA)由哪些主要内存区域组成
• 再来介绍程序缓存区(PGA)中包含的内存区域
• Buffer状态及其链表结构
• 之前介绍过InnoDB是双向链表结构，由三种不同的buffer状态生出了三条链表

• 六、线程及其作用
• 七、InnoDB的三大特性

一、Mysql 体系结构

Mysql的体系结构可以分为两层，Mysql Server 层和存储引擎层。在Mysql server层中又包括连接层和sql层

最先连接处理的是连接层，连接层包括通信协议、线程处理、用户密码认证三个部分

通信协议:负责检测客户端版本是否兼容mysql服务器端

线程:处理是指每一个连接请求都会分配应一个对应的线程，相当于一套sql对应一个线程，一个线程对应一个逻辑CPU，并会在多个逻辑CPU之间进行切换

用户密码认证:验证创建的账号和密码，以及host主机授权是否可以连接到msyql服务器

连接层、sql层、存储引擎层

SQL 层包含权限判断，查询缓存，解析器，预处理，查询优化器，缓存和执行计划

权限判断:可以审核用户没有访问某个库，某个表，或者表里某行的权限

查询缓存:通过Query Cache 进行操作，如果数据在Query Cache中，则直接返回结果给客户端

查询解析器:针对sql语句进行解析，判断语法是否正确

预处理器:对解析器无法解析的语义进行处理，如数据表和数据列是否存在，解析列名和别名，是否有歧义，接下来预处理器会验证用户权限

优化器:对sql进行改写和相应的优化，索引选择，并生成最优的执行计划，就可以调用API接口，通过存储引擎访问数据

优化器功能：

1. 确认表查询表（多对多的查询会选择合适的表做为查询依赖）
2. 对于数据表扫表查询字段(确定好查询的顺序) join
3. 重写where从句，去掉无意义的查询操作，尽可能去更改限制条件，减少查询范围
4. 判断索引的使用
   a.是否覆盖了所有的字段
   b.是否在where上使用，是否group order 上使用
5. (join) 连接方式是否内还是外
6. (join) 尝试简化子查询 => 合并视图

二、query_cache详解

query_cache_type =1
query_cache_size=0

压力测试软件 sysbench 可以进行 cpu、内存、磁盘I/O、线程、数据库的性能测试

三、InnoDB体系结构

先弄清楚两个概念，一个是数据库，一个是数据库实例，mysql数据库是一个单进程多线程模型的数据库。数据库的实例就是进程加内存的组合，水就是内存，杯子就是数据库,而在内存中的数据，早晚有一天也会根据刷新机制刷到磁盘上，谁来帮助数据库刷新呢？那就是线程，这就是一条数据从内存到磁盘的过程

InnoDB体系结构实际由，内存结构、线程、磁盘文件这三层组成

InnoDB存储引擎有多个内存块，可以认为这些内存块组成了一个大的内存池

后台线程(是下面说的4个线程)的主要作用是负责刷新内存池中的数据，保证缓冲池中的内存缓存的是最近的数量。此外将已经修改的数据文件刷新到磁盘文件，同时保证在数据库上发生异常的情况下Innodb能恢复到正常运行状态

1、Master Thread

Master Thread 是一个非常核心的后台线程，主要负责将缓冲池中的数据异步刷新到磁盘，保证数据的一致性，包括脏页的刷新、合并插入缓冲

2、IO Thread

InnoDB存储引擎中大量使用了AIO(Async IO) 来处理写IO请求，这样可以极大提高数据库的性能。而IO Thread的工作主要是负责这些IO请求的回调(call back处理)

3、Purage Thread

事务被提交后，InnoDB支持多个Purage Thread，这样做的目的是为了进一步加快undo页的回收。同时由于Purage Thread需要离散地读取undo页，这样也能更进一步利用磁盘的随机读取性能 (负责删除无用的undo页)

4、Page Cleaner Thread

其作用将脏页的刷新操作都放入到单独的线程中来完成。而其目的是为了减轻原Master THread的工作及对于用户查询线程的阻塞，进一步提高Innodb存储引擎的性能 (负责脏页刷新的线程)

undo页

undo日志用于存放数据修改被修改前的值，假设修改 tba 表中 id=2的行数据，把Name=’B’ 修改为Name = ‘B2’ ，那么undo日志就会用来存放Name=’B’的记录，如果这个修改出现异常，可以使用undo日志来实现回滚操作，保证事务的一致性。

对数据的变更操作，主要来自 INSERT UPDATE DELETE，而UNDO LOG中分为两种类型，

1. 一种是 INSERT_UNDO（INSERT操作），记录插入的唯一键值；
2. 一种是UPDATE_UNDO（包含UPDATE及DELETE操作），记录修改的唯一键值以及old column记录。

Id	Name
1	A
2	B
3	C
4	D

mysql> show global variables like '%undo%';
mysql> show global variables like '%truncate%';

redo是啥

当数据库对数据做修改的时候，需要把数据页从磁盘读到buffer pool中，然后在buffer pool中进行修改，那么这个时候buffer pool中的数据页就与磁盘上的数据页内容不一致，称buffer pool的数据页为dirty page 脏数据，如果这个时候发生非正常的DB服务重启，那么这些数据还没在内存，并没有同步到磁盘文件中（注意，同步到磁盘文件是个随机IO），也就是会发生数据丢失，如果这个时候，能够在有一个文件，当buffer pool 中的data page变更结束后，把相应修改记录记录到这个文件（注意，记录日志是顺序IO），那么当DB服务发生crash的情况，恢复DB的时候，也可以根据这个文件的记录内容，重新应用到磁盘文件，数据保持一致。

这个文件就是redo log ，用于记录 数据修改后的记录，顺序记录。它可以带来这些好处：

当buffer pool中的dirty page 还没有刷新到磁盘的时候，发生crash，启动服务后，可通过redo log 找到需要重新刷新到磁盘文件的记录；
buffer pool中的数据直接flush到disk file，是一个随机IO，效率较差，而把buffer pool中的数据记录到redo log，是一个顺序IO，可以提高事务提交的速度；
假设修改 tba 表中 id=2的行数据，把Name=’B’ 修改为Name = ‘B2’ ，那么redo日志就会用来存放Name=’B2’的记录，如果这个修改在flush 到磁盘文件时出现异常，可以使用redo log实现重做操作，保证事务的持久性。

Id	Name
1	A
2	B
3	C
4	D

这里注意下redo log 跟binary log 的区别，redo log 是存储引擎层产生的，而binary log是数据库层产生的。假设一个大事务，对它做10万行的记录插入，在这个过程中，一直不断的往redo log顺序记录，而binary log不会记录，知道这个事务提交，才会一次写入到binary log文件中。binary log的记录格式有3种：row，statement跟mixed，不同格式记录形式不一样。

内存

1、缓存池

InnoDB存储引擎是基于磁盘存储的,并将其中的记录按照页的方式进行管理

缓冲池简单来说就是一块内存区域，通过内存的速度来弥补磁盘速度较慢对数据库性能的影响。在数据库中进行读取页的操作，首先将从磁盘读到的页存放在缓冲池中，这个过程为将页"FIX"在缓冲池中。下一次再读相同的页时，首先判断该页是否在缓冲池中。若在缓冲池中，称该页在缓冲池中被命中，直接读取该页。否则读取磁盘上的页

对于数据库中页的修改操作，则首先修改在缓冲池中的页，然后再已一定的频率刷新到磁盘上。这里需要注意的是，页从缓冲池刷新回磁盘的操作并不是在每次页发生更新时触发，而是通过一种称为Checkpoint的机制刷新回磁盘

允许有多个缓冲池实例，每个页根据哈希值平均分配到不同缓冲池实例中。这样做的好处是减少数据库内部的资源竞争增加数据库的并发处能力。可以通过Innodb_buffer_pool_instances 来进行配置，默认是1

SHOW engine innodb status

你能看到 InnoDB 缓冲池包括了数据页、索引页、插入缓冲、锁信息、自适应 Hash 和数据字典信息等。

InnoDB 存储引擎基于磁盘文件存储，访问物理硬盘和在内存中进行访问，速度相差很大，为了尽可能弥补这两者之间 I/O 效率的差值，我们就需要把经常使用的数据加载到缓冲池中，避免每次访问都进行磁盘 I/O。

频次 * 位置”这个原则，可以帮我们对 I/O 访问效率进行优化

首先，位置决定效率，提供缓冲池就是为了在内存中可以直接访问数据。

其次，频次决定优先级顺序。因为缓冲池的大小是有限的，比如磁盘有 200G，但是内存只有 16G，缓冲池大小只有 1G，就无法将所有数据都加载到缓冲池里，这时就涉及到优先级顺序，会优先对使用频次高的热数据进行加载。

了解了缓冲池的作用之后，我们还需要了解缓冲池的另一个特性：预读

缓冲池的作用就是提升 I/O 效率，而我们进行读取数据的时候存在一个“局部性原理”，也就是说我们使用了一些数据，大概率还会使用它周围的一些数据，因此采用“预读”的机制提前加载，可以减少未来可能的磁盘 I/O 操作

那么什么是查询缓存呢？

查询缓存是提前把查询结果缓存起来，这样下次不需要执行就可以直接拿到结果。需要说明的是，在 MySQL 中的查询缓存，不是缓存查询计划，而是查询对应的结果。这就意味着查询匹配的鲁棒性大大降低，只有相同的查询操作才会命中查询缓存。因此 MySQL 的查询缓存命中率不高，在 MySQL8.0 版本中已经弃用了查询缓存功能

show variables like '%query_cache%';

缓冲池并不等于查询缓存，它们的共同点都是通过缓存的机制来提升效率。但缓冲池服务于数据库整体的 I/O 操作，而查询缓存服务于 SQL 查询和查询结果集的，因为命中条件苛刻，而且只要数据表发生变化，查询缓存就会失效，因此命中率低

四、InnoDB存储结构

MyISAM：每个MyISAM在磁盘上存储成三个文件。第一个文件的名字以表的名字开始，扩展名指出文件类型。
.frm文件存储表定义；
数据文件的扩展名为.MYD (MYData)；
索引文件的扩展名是.MYI (MYIndex)。

InnoDB逻辑存储单元主要分为表空间、段、区、和页

表空间

Innodb存储引擎表中所有数据都是存储在表空间中的，表空间又分为系统表空间，它以ibdata1来命名，它会存储所有数据的信息以及回滚段(undo)的信息。

.idb: 记录数据+索引 innodb的独享空间 针对于当前这个表使用 --这是默认的

undo表空间可以通过参数单独设置存储位置了，可从ibdata1中独立出来

MariaDB [(none)]> show variables like ‘%auto%’;

MariaDB [(none)]> show variables like ‘%Innodb_data%’;

在遇到高并发事物时，会受到不小的影响，建议把ibdata1初始值大小调整为1024M

独立表空间，设置参数innodb_file_per_table=1 或者 innodb_file_per_table=ON即可，默认使用独立表空间，就是每个表就有自己的表空间文件，而不用存储在ibdata1中，独立表空间文件存储对应表的B+tree数据、索引、和插入缓冲等消息，其余信息还是存储在默认表空间中

独立表空间的每个表有自己的表空间，并且可以实现表空间的转移，回收表空间也很方便，使用alter table_name engine=innodb 或者pt-online_schema_change命令即可。但有一个不好的地方在于每个表文件都有.frm 和 .ibd 文件两个描述符，如果单表增长过快就容易出性能问题。

段

表空间是由段组成的，也可以把一个表理解为一个段，通常有数据段、回滚段、索引段等。每个段由N个区间和32个零散的页组成，段空间扩展是以区为单位进行扩展的。通常情况下，创建一个索引的同时就会创建两个段，分别为非叶子节点和叶子节点段。那一个表有几个段呢?

答案是4个，是索引个数的2倍

区

区是由连续的页组成的，是物理上连续分配的一段空间，每个区的大小固定是1MB

页

InnoDB的最小物理存储分配单位是page，有数据页回滚页等。一般情况下，一个区由64个连接的页组成，页默认大小是16KB

区就等于64*16KB=1MB。但Mysql数据库从5.6开始可以自定义调低page的大小，可以默认的16KB调整为8KB或4KB。而mysql5.7开始可以调高page的大小，可以从默认的16KB调整为32KB或者64KB。一般情况下，一个page页会默认预留1/16的空间用于更新数据。真正使用的是15/16的空间。一个页最少可以存两行数据，虚拟最小行(infimum records)和虚拟最大行(infimum records).用来限定行记录的范围，以此来保证B+tree节点是双向链表结构

行

页里面又记录着行记录的信息，InnoDB存储引擎是面向行的，也就是数据是按照行存储的

Innodb存储引擎有两种文件格式
Antelope文件格式

• compact 和 redundant 两种行记录格式

Brracuda文件格式

• compressed 和 dynamic 两种行记录格式

tb_emp1是表名字

#windows环境下的

linux mysql5.7默认是 dynamic 行记录格式

实际采用的数据都存在溢出的页中(off-page)，而数据页只存前20个字节的数据，针对溢出列所在新页利用率会更高

五、内存结构

先来介绍系统全局区(SGA)由哪些主要内存区域组成

1、innodb_buffer_pool
用途：用来缓存InnoDB表的数据、索引、插入缓存、数据字典等信息
2、innodb_log_buffer
用途：事物在内存中的缓存，即redo log buffer的大小

3、Query Cache
用途： 高速查询缓存，前面已经介绍过，再生产环境中建议关闭

4、key_buffer_size
用途： 只用于MyISAM存储引擎表，缓存MyISAM存储
引擎表的索引文件(区别于innodb_buffer_pool数据和索引都缓存)

5、innodb_additional_mem_pool_size
用途：用来保存数据字典信息和其他内部数据结构的内存池的大小。Mysql5.7.4中该参数被移除了

再来介绍程序缓存区(PGA)中包含的内存区域

1、sort_buffer_size
用途：主要用于SQL语句在内存中的临时排序

2、join_buffer_size
用途:表连接使用，用于BKA。MySQL5.6之后开始支持。

3、read_buffer_size
用途：表顺序扫描的缓存，只能应用于MyISAM表存储引擎

4、read_rud_buffer_size
用途：Mysql随机读缓冲区大小，用于做mrr,mrr是Mysql5.6之后才有的特性

在介绍两个特性的

1、tmp_table_size
用途：sql语句在排序或者分组时没有用到索引，就会使用临时表空间

2、max_heap_table_size
用途：管理 heap、memory存储引擎表

建议生产环境中把tmp_buffer_size和max_heap_table_size设置成一样的值，如果两者不一样，则会按照两者中小的值来起限制作用

而且相应的值不要太小，值太小容易出现"converted heap to myisam"的报错

还有两个重要的参数

1. default_tmp_storage_engine 是指临时表默认的存储引擎
2. internal_tmp_disk_storage_engine 是指在磁盘上临时表的管理，有该参数决定(CREATE TEMPORARY TABLE)

Buffer状态及其链表结构

page是innodb磁盘I/O的最小单位，数据是存放在page中的，那么对应到内存中就是一个个的buffer。每个buffer又分为三种状态

1. free buffer: 此状态下的buffer从未被使用,像一张白纸，但在实际生产中，数据库很繁忙的情况下，free buffer的状态基本是不存在的
2. clean buffer:内存中buffer里面的数据和磁盘page的数据一致
3. dirty buffer: 内存中新写入的数据还没有刷新到磁盘，跟磁盘中数据不一致

buffer在内存中是需要被组织起来的，由chain来管理，也就是链。

之前介绍过InnoDB是双向链表结构，由三种不同的buffer状态生出了三条链表

1. free list: 把那些free状态的buffer都串联起来。在数据库真正跑起来的时候，每次把page调到内存中，都先会判断free buffer的使用情况。如果不够用了，就会从lru list和flush list链表中释放free buffer，以获得新的空闲buffer
2. lru list: 会把那些与磁盘数据一致，并且最近最少被使用的buffer串联起来，释放出free buffer，page调到内存中便于使用新的可用buffer
3. flush list: 把那些dirty buffer 串联起来，为了方便刷新线程把脏数据刷到磁盘。推进checkpoint Lsn，使实例崩溃之后，可以快速恢复。其实flush list 中也隐藏这一个lru的规则。举个例子，假如目前有条数据是19，下一秒变成了29，再下一秒又变成了39，这样的数据情况就是经常被访问、被使用到，暂时不能把它串起来。我们要把那些最近少被"弄脏"的数据串起来，刷新到磁盘之后，释放出更多free buffer供我们使用

六、线程及其作用

mysql> show variables like '%innodb%';

什么是脏页:
当内存数据页和磁盘数据页上的内容不一致时，我们称这个内存页为脏页；

什么是干净页：
内存数据写入磁盘后，内存页上的数据和磁盘页上的数据就一致了，我们称这个内存页为干净页。

刷脏页的时机:

1. redo log写满时，没有看见了，此时需要将checkpoint向前推进，推进的这部分日志对应的脏页刷入到磁盘，此时所有的更新全部阻塞，此时写的性能变为0，必须待刷一部分脏页后才能更新。
2. 系统内存不足时，需要将一部分数据页淘汰掉，如果淘汰的是脏页，需要先将脏页同步到磁盘。
3. MySQL认为空闲的时间，这种没有性能问题。
4. mysql正常关闭之前，会把所有脏页刷入磁盘，不存在性能问题。

master thread

先来介绍master thread线程，它是后台线程中的主线程.优先级最高，其内部有4个循环

1. 主循环 loop
2. 后台循环background loop
3. 刷新循环flush loop
4. 暂停循环suspend loop

根据数据的运行状态会在这四个循环之间进行切换、在loop主循环中又包含两种操作，为每1秒、每10秒的操作

每1秒操作 (内存)

1. 日志缓冲刷新到磁盘，即使这个事务还没有提交
2. 刷新脏页到磁盘
3. 执行合并插入缓冲的操作
4. 产生checkpoint(检查点)
5. 清楚无用的table cache
6. 如果当前没有用户活动，就可能切换到background loop

每10秒操作

1. 日志缓冲刷新到磁盘，即使这个事务还没有提交
2. 执行合并插入缓冲的操作
3. 刷新脏页到磁盘
4. 删除无用undo页 (undo的介绍为了满足事务的原子性,首先将数据备份到一个地方（这个存储数据备份的地方称为Undo Log)
5. 产生checkpoint

undo用来回滚行记录到某个版本。undo log一般是逻辑日志，根据每行记录进行记录

接下来四大I/O 线程

• read thread
• write thread
• redo log thread
• change buffer thread

2. redo log thread 负责把日志缓冲中的内容刷新到redo log 文件中。
3. change buffer thread 负责把插入缓冲(change buffer)中的内容刷新到磁盘
4. read/write thread 是数据库的读写请求线程

默认值都是4个。如果使用高转速磁盘，可以适当调大该值

5. page cleaner thread 是负责脏页刷新的线程

8. purge thread：负责删除无用undo页，由于进行DML语句的操作都会生成undo、系统需要定期对undo页进行清理，这时就需要purge操作，从mysql5.6开始把purge thread单独从master thread中分离出来，通过innodb_purge_thread参数来控制purge的线程个数。默认是1个



9. checkpoint：线程的作用是redo log 发生切换时，执行checkpoint。 redo log 发生切换或者文件快写满，会触发把脏页刷新到磁盘。还有就是确保redo log 刷新到磁盘

实现真正持久化 避免数据丢失

• error monitor thread 是负责数据库报错的监控线程
• lock monitor thread 是负责所锁的监控线程

七、InnoDB的三大特性

插入缓存

影响数据库最主要的性能问题就是I/O,而插入缓冲的作用就是把普通索引上的DML操作从随机I/O变成顺序I/O,提高I/O效率。它的工作原理也很简单，就是先判断插入的普通索引页是否在缓冲池中，如果在就可以直接插入，如果不在就要先放到change buffer中 ，然后进行change buffer和普通索引的合并操作，可以将多个插入合并到一个操作中，一下子就提高了普通索引插入性能，

innodb_change_buffer_max 的含义: 占innodb_buffer_pool的最大比例，默认是25%，最大占比buffer pool 1/4的大小，建议调整为50

innodb_change_buffering: change buffer 的类型

有如下几种类型:

• all:buffer inserts,delete-marking operations,and purges

缓冲全部inserts、delete标记操作和purges操作

• none:Do not buffer any operations

关闭 insert buffer

• inserts:Buffer insert operations

insert 标记操作

• deletes:Buffer delete-marking operations

delete 标记操作

• changes: Buffer both inserts and delete-marking

未进行实际insert 和 delete,只是标记，等待后续purge

• purges:Buffer the physical deletion operations that happen in the
  background

缓冲后台进程purges(物理删除)操作。建议选择默认的all就可以了

两次写

插入缓冲带来的是针对普通索引插入性能上的提升，而double write就是保证写入的安全性防止在Mysql实例发生宕机时，InnoDB发生数据页部分页写(partial page write) 的问题。数据库实例崩溃，我们可以通过redo log 进行恢复，不会有任何问题，但redo log文件记录的是页的物理操作，如果页都损坏了，是无法进行任何恢复操作的巧妇难为无米之炊就是这个道理。所以我们需要页的一个副本，如果实例宕机了，可以先通过副本把原来的页还原出来，再通过redo log 进行恢复，重做。这就是double write的作用

双写缓冲是一个位于系统空间中的存储区域，InnoDB缓冲池中刷出的脏页在被写入数据文件之前，都先会写入double write buffer，然后从两次写缓冲区分两次，每次将1MB大小的数据写入磁盘共享表空间(double write)，最后再从double write buffer写入数据文件

自适应哈希索引

详细分析MySQL事务日志(redo log和undo log)redo和undo日志 参考
高性能Mysql