合 MySQL体系结构
MySQL Server体系结构
MySQL由数据库和数据库实例组成，是单进程多线程架构（Oracle和PG都是多进程架构）。
数据库：物理操作系统文件或者其它文件的集合，在mysql中，数据库文件可以是frm、myd、myi、ibd等结尾的文件，当使用ndb存储引擎时候，不是os文件，是存放于内存中的文件。
数据库实例：由数据库后台进程/线程以及一个共享内存区组成，共享内存可以被运行的后台进程/线程所共享。
1 2 3 4 5 | top -Hp pid 可以查看某个进程的线程信息 ps -Lf pid 也可以查看某个进程的线程信息 # 线程和连接id之间的关系 select a.THREAD_OS_ID,PROCESSLIST from performance_schema.threads a; |
https://www.mysql.com/common/images/PSEA_diagram.jpg
•Connectors（连接者）：指的是不同语言中与SQL的交互，从图3-1中可以看到目前流行的语言都支持MySQL客户端连接。
•Connection Pool（连接池）：管理缓冲用户连接、线程处理等需要缓存的需求。在这里也会进行用户账号、密码和库表权限验证。
•SQL Interface（SQL接口）：接收用户执行的SQL语句，并返回查询的结果。
•Parser（查询解析器）：SQL语句被传递到解析器时会进行验证和解析（解析成MySQL认识的语法，查询什么表、什么字段）。解析器是由Lex和YACC实现的，是一个很长的脚本。其主要功能是将SQL语句分解成数据结构，并将这个结构传递到后续步骤中，后续SQL语句的传递和处理就是基于这个结构的。如果在分解构成中遇到错误，则说明该SQL语句可能有语法错误或者不合理。
•Optimizer（查询优化器）：在查询之前，SQL语句会使用查询优化器对查询进行优化（生成查询路径树，并选举一条最优的查询路径）。它使用“选取—投影—连接”策略进行查询。
•Caches&Buffers（缓存&缓冲）：主要包含QC以及表缓存、权限缓存等。对于QC，以往主要用于MyISAM存储引擎，目前在MySQL 8.0中已放弃，对于现在非常流行的InnoDB存储引擎来讲，QC已无任何意义，因为InnoDB存储引擎有自己的且非常完善的缓存功能。除QC之外（记录缓存、key缓存，可使用参数单独关闭），该缓存机制还包括表缓存和权限缓存等，这些是属于Server层的功能，其他存储引擎仍需要使用。
•Pluggable Storage Engines（插件式存储引擎）：存储引擎是MySQL中具体的与文件打交道的子系统，也是MySQL最具特色的一个地方。MySQL的存储引擎是插件式的，它根据MySQL AB公司提供的文件访问层的一个抽象接口来定制一种文件访问机制（这种访问机制就叫存储引擎）。目前存储引擎众多，且它们的优势各不相同，现在最常用于OLTP场景的是InnoDB（当然也支持OLAP存储引擎，但MySQL自身的机制并不擅长OLAP场景）。
•Files&Logs（磁盘物理文件）：包含MySQL的各个引擎的数据、索引的文件，以及redo log、undo log、binary log、error log、query log、slow log等各种日志文件。
•File System（文件系统）：对存储设备的空间进行组织和分配，负责文件存储并对存入的文件进行保护和检索的系统。它负责为用户建立文件，存入、读出、修改、转储文件，控制文件的存取。常见的文件系统包括XFS、NTFS、EXT4、EXT3、NFS等，通常数据库服务器使用的磁盘建议用XFS。
那么，上述各个组件之间是如何协同工作的？下面我们举一个查询例子进行说明。
假如在MySQL中有一个查询会话请求，那么大概流程如下：
（1）MySQL客户端对MySQL Server的监听端口发起请求。
（2）在连接者组件层创建连接、分配线程，并验证用户名、密码和库表权限。
（3）如果打开了query_cache，则检查之，有数据直接返回，没有继续往下执行。
（4）SQL接口组件接收SQL语句，将SQL语句分解成数据结构，并将这个结构传递到后续步骤中（将SQL语句解析成MySQL认识的语法）。
（5）查询优化器组件生成查询路径树，并选举一条最优的查询路径。
（6）调用存储引擎接口，打开表，执行查询，检查存储引擎缓存中是否有对应的缓存记录，如果没有就继续往下执行。
（7）到磁盘物理文件中寻找数据。
（8）当查询到所需要的数据之后，先写入存储引擎缓存中，如果打开了query_cache，也会同时写进去。
（9）返回数据给客户端。
（10）关闭表。
（11）关闭线程。
（12）关闭连接。
MySQL 使用典型的客户端/服务器（Client/Server）结构，结构图如下所示：
查询缓存，用于将执行过的SELECT语句和结果缓存在内存中。每次执行查询之前判断是否命中缓存，如果命中直接返回缓存的结果。缓存命中需要满足许多条件，SQL语句完全相同，上下文环境相同等。实际上除非是只读应用，查询缓存的失效频率非常高，任何对表的修改都会导致缓存失效；因此，查询缓存在MySQL 8.0中已经被删除。
SQL接口，接收客户端发送的各种DML和DDL命令，并且返回用户查询的结果。另外还包括所有的内置函数（日期、时间、数学以及加密函数）和跨存储引擎的功能，例如存储过程、触发器、视图等。
解析器，对SQL语句进行解析，例如语义和语法的分析和检查，以及对象访问权限检查等。
优化器，利用数据库的统计信息决定SQL语句的最佳执行方式。使用索引还是全表扫描的方式访问单个表，多表连接的实现方式等。优化器是决定查询性能的关键组件，而数据库的统计信息是优化器判断的基础。
缓存与缓冲，由一系列缓存组成的，例如数据缓存、索引缓存以及对象权限缓存等。对于已经访问过的磁盘数据，在缓冲区中进行缓存；下次访问时可以直接读取内存中的数据，从而减少磁盘IO。
存储引擎，存储引擎是对底层物理数据执行实际操作的组件，为服务器层提供各种操作数据的API。MySQL支持插件式的存储引擎，包括InnoDB、MyISAM、Memory等。
InnoDB存储引擎体系结构
InnoDB存储引擎体系结构如图所示（该图来自Percona Database Performance Blog，参考链接：https://www.percona.com/blog/2010/04/26/xtradb-innodb-internals-in-drawing/）。
https://myslide.cn/slides/21572#
从MySQL 5.5版本开始默认使用InnoDB作为引擎，它擅长处理事务，具有自动崩溃恢复的特性，在日常开发中使用非常广泛。下面是官方的InnoDB引擎架构图，主要分为内存结构和磁盘结构两大部分。
内存结构
InnoDB存储引擎是基于磁盘存储的，并将其中的记录按照页的方式进行管理。
缓冲池简单来说就是一块内存区域，通过内存的速度来弥补磁盘速度较慢对数据库性能的影响。在数据库中进行读取页的操作，首先将从磁盘读到的页存放在缓冲池中，这个过程为将页"FIX"在缓冲池中。下一次再读相同的页时，首先判断该页是否在缓冲池中。若在缓冲池中，称该页在缓冲池中被命中，直接读取该页。否则读取磁盘上的页。
对于数据库中页的修改操作，则首先修改在缓冲池中的页，然后再以一定的频率刷新到磁盘上。这里需要注意的是，页从缓冲池刷新回磁盘的操作并不是在每次页发生更新时触发，而是通过一种称为Checkpoint的机制刷新回磁盘
允许有多个缓冲池实例，每个页根据哈希值平均分配到不同缓冲池实例中。这样做的好处是减少数据库内部的资源竞争增加数据库的并发处能力。可以通过Innodb_buffer_pool_instances来进行配置，默认是1
InnoDB缓冲池包括了数据页、索引页、插入缓冲、锁信息、自适应Hash和数据字典信息等。InnoDB存储引擎基于磁盘文件存储，访问物理硬盘和在内存中进行访问，速度相差很大，为了尽可能弥补这两者之间I/O效率的差值，我们就需要把经常使用的数据加载到缓冲池中，避免每次访问都进行磁盘I/O。
内存结构主要包括Buffer Pool、Change Buffer、Adaptive Hash Index和Log Buffer四大组件。
Buffer Pool：缓冲池是InnoDB在启动时分配的一块内存区域，用于InnoDB在访问数据时缓存表和索引数据。利用缓冲池，可以合并一些对经常访问的数据的操作，直接从内存中处理，加快了处理速度。通常，在专用数据库服务器上，可以将80％的物理内存分配给InnoDB缓冲池。为了提高缓存管理的效率，使用页面链表的方式+LRU（最近最少使用）算法进行管理。该区域大小由参数innodb_buffer_pool_size来定义，默认128MB。
1 2 3 4 5 6 | MySQL [(none)]> show variables like 'innodb_buffer_pool_size%'; +-------------------------+-----------+ | Variable_name | Value | +-------------------------+-----------+ | innodb_buffer_pool_size | 134217728 | +-------------------------+-----------+ |
Buffer Pool以Page页为单位，默认大小16K，BP的底层采用链表数据结构管理Page。在InnoDB访问表记录和索引时会在Page页中缓存，以后使用可以减少磁 盘IO操作，提升效率。
Change Buffer（Insert buffer part of buffer pool）：这是一种特殊的数据结构（早期只支持INSERT操作的缓冲，所以也叫作Insert Buffer），当受影响的页面不在缓冲池中时，将会缓存对辅助索引页的更改。这些更改可能是由INSERT、UPDATE、DELETE（DML）语句执行时所导致的，当其他读取操作从磁盘中加载数据页时，如果这些数据页包含Change Buffer中缓存的更改操作页，那么将进行合并操作。
Adaptive Hash Index：自适应哈希索引（AHI），用于管理缓冲池中的内部数据结构，并对缓冲池中的相关工作负载和内存操作组合进行自动调节，且不会牺牲任何事务功能、性能和可靠性。
Log Buffer（Redo Log Buffer）：重做日志缓冲区是用于保存将要写入重做日志磁盘文件中的数据的内存缓冲区域。重做日志缓冲区的大小由innodb_log_buffer_size配置参数定义。重做日志缓冲区中的内容会定期刷新到磁盘上的日志文件中。更大的重做日志缓冲区允许运行更大的事务，这在一定程度上避免提交大事务之前需要将重做日志写入磁盘中。因此，如果在应用场景中经常有大事务，则可以考虑增大重做日志缓冲区以减少磁盘I/O操作。innodb_flush_log_at_trx_commit参数控制如何将重做日志缓冲区的内容写入日志文件中（例如，设置为1时，每个事务提交时都需要执行一次将重做日志缓冲区的内容写入日志文件中）。innodb_flush_log_at_timeout参数控制重做日志的刷新频率。
innodb-buffer-pool-list:
innodb-change-buffer:
Insert Buffer（插入缓存）？
影响数据库最主要的性能问题就是I/O，而插入缓冲的作用就是把普通索引上的DML操作从随机I/O变成顺序I/O、提高I/O效率。它的工作原理也很简单，就是先判断插入的普通索引页是否在缓冲池中，如果在就可以直接插入，如果不在就要先放到change buffer中 ，然后进行change buffer和普通索引的合并操作，可以将多个插入合并到一个操作中，一下子就提高了普通索引插入性能，
1 2 3 4 5 6 7 8 | MySQL [(none)]> show variables like '%change%'; +-------------------------------+-------+ | Variable_name | Value | +-------------------------------+-------+ | innodb_change_buffer_max_size | 25 | | innodb_change_buffering | all | | session_track_state_change | OFF | +-------------------------------+-------+ |
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就可以了
刷脏页的时机:
l redo log写满时，没有看见了，此时需要将checkpoint向前推进，推进的这部分日志对应的脏页刷入到磁盘，此时所有的更新全部阻塞，此时写的性能变为0，必须待刷一部分脏页后才能更新。
l 系统内存不足时，需要将一部分数据页淘汰掉，如果淘汰的是脏页，需要先将脏页同步到磁盘。
l MySQL认为空闲的时间，这种没有性能问题。
l mysql正常关闭之前，会把所有脏页刷入磁盘，不存在性能问题。
Buffer状态及其链表结构
page是innodb磁盘I/O的最小单位，数据是存放在page中的，那么对应到内存中就是一个个的buffer。每个buffer又分为三种状态
