Mysql体系架构剖析——岁月云实战笔记

1 体系架构

        理论内容阅读了mysql体系架构剖析，其他的根据岁月云的实战进行记录。

1.1 连接层

        mysql最上层为连接服务，引入线程池，允许多台客户端连接，主要工作：连接处理、授权认证、安全防护、管理连接等。

        连接处理：每个客户端连接都会分配一个线程，由连接池管理。

        授权认证：对客户端的身份验证，基于用户名、主机信息、密码。如指定用户，%表示主机都可以访问，也可以限定网段或具体ip

        安全防护：验证客户端查询权限，如下图对psi用户限制只能访问psi数据库。

1.1.1 show PROCESSLIST

        查看当前连接信息。下图中TIme表示客户端从建立连接到现在的时间，

 1.1.2 max_connections

        下图是我的生产环境配置，临时调整set global max_connections=5000,有效期命令执行开始，宕机关闭结束。

 1.1.3 timeout相关参数

        下面的单位为秒（s），wait_timeout默认为8小时，没有使用就释放。

1.2 服务层 

        服务层用于处理核心服务：如标准sql接口、查询解析、SQL优化和统计、全局和引擎依赖的缓存与缓冲器等。

1.2.1 查询缓存

        在应用中使用二级缓存，就没必要使用mysql的查询缓存，因为耗费的是mysql的内存，势必对业务产生较大影响。不想一级缓存caffeine可以通过应用分布式来缓解压力，二级缓存redis自身由独立的内存服务器或虚拟机，而mysql不一样的。是啥情况会反复执行相同的sql呢？如果出现这种情况，前端就需要作防抖处理了。故不推荐使用查询缓存。因此mysql8.0+把查询缓存去掉了。

1.2.2 SQL解析器

        通过词法分析、语法分析将sql翻译为mysql能识别的结构。这个好比产品经理要把用户需求翻译成为产品需求的逻辑。

1.2.3 查询优化器

• 等价变换策略：如：where 1=1 and as_id=1001等价于where as_id=1001，早在2012年很多系统都是这么干的where 1=1，为了方便添加条件，都这么写，引擎优化会作转换
• 优化count、min、max：因为mysql索引采用的B+树，树的左子节点比当前节点小，右子节点比当前节点大，根据这个原则，min只需要查找索有最左边即可，max只需要查询最右边，这样就很快了。对于count，InnoDB 必须实际扫描索引或表来计算 COUNT。
• 提前终止查询：limit来终止后面数据的查询
• in的优化：mysql对in查询会进行排序，然后采取二分查找数据，提升查找效率。
• 条件查询：先根据where条件选择，再根据字段进行属性投影，然后形成最终的查询结果。
• 连接查询：决定使用那个索引

1.3 存储引擎层

        存储引擎直接与数据交互。为上层提供接口，以屏蔽不同存储引擎的差异。

        下面是mariadb的，跟mysql还是有区别。InnoDb中Supports transactions, row-level locking, foreign keys and encryption for tables代表支持事务、行级锁、外键、表加密。

         mysql5.7如下，InnoDb中Supports transactions, row-level locking, and foreign keys代表支持事务、行级锁、外键。

        mysql中InnoDB存储引擎，所有数据逻辑存储空间叫做表空间，表空间对应物理结构是磁盘中的文档、日志、数据等。表空间的逻辑组织：段（segment）、区（extend）、页（page，也称为块）。

        表空间中的段组成：数据段、索引段、回滚段。

        区：由页组成，每个区大小为1M，innodb每次从磁盘一次申请4~5个区，页大小16K，一个区由64个页。

1.4 物理存储层

        文件的物理存储层（磁盘），binlog日志、数据文件、错误日志、慢查询日志、redo/undo日志（事务）等。

        查看数据目录

        mysql中sys数据库，可以通过视图的形式把information_schema和performance_schema两者结合起来。

2 SQL运行机制

        这个不是我的图，请看上面那位博主的解读，这里过多不解释。

2.1 半双工通讯        

mysql客户端/服务端通讯协议是半双工的，即要么客户端->服务端，要么服务端->客户端，这两个操作不能同时发生。一端发送消息，另一端要接收完整的消息才能响应，消息无法切割小块，也就不能进行流量管控。

        如果请求的sql语句或者响应结果特别大，服务器会拒绝并抛出异常。通过max_allowed_packet参数来调整，我的生产环境设置的128M，这种情况一般会发生在批量插入或者将文件内容写入到数据库的情况。

2.2 sql执行流程

       用户管理对用户进行授权， 访问控制模块用于表数据访问进行鉴权，表管理模块缩表管理，从meta信息判断表属于那种存储引擎。

3 InnoDB存储引擎

        InnoDB平衡了高可靠性和高性能，也就是说mysql满足的是CA（一致性+可用性）。

3.1 架构     

        InnoDB的结构如下：

3.2 内存结构

        设计保证高性能。

3.2.1 buffer pool

        缓存表和索引数据，以数据页为单位，每页16KB，官方建议专用mysql数据库服务器，80%的物理内存通常分配给缓冲池。缓冲池针对LRU算法进行淘汰优化。

        我的电脑设置了26G。

•   预读机制

        线性预读单位为extend，一个extend中有64个页，innodb_read_ahead_threshold=56，代表连续访问一个extend的56个页面之后把下一个extend读入buffer pool中，这个参数不能超过64，默认56.

        随机预读默认为off，因为mysql5.5已废弃，故不启用，因为影响性能。 

•    Page

         Free Page：未被使用的页，位于free链表，用于缓冲池初始化（mysql启动时）时申请占用连续的空间。

        clean Page：已被使用的页，但页面没发生修改，位于LRU链表

        dirty Page：因为写入内存中，使得内存数据与磁盘不一致数据，发生变更的row所在的位置就是dirty page，内存的数据同步到磁盘后，就恢复为clean page

•   自适应hash索引

        innodb中自适应hash索引使得mysql性能更接近于内存服务器。特点时降低二级索引树的频繁访问，自适应。缺点：占用innodb buffer pool，只适合是二级索引等值查询。

        因为二级索引由索引列和主键列两列组成，使得自适应hash索引成为可能。当二级索引被频繁访问（最近连续3次被访问），innodb将使用索引前缀建立自适应hash索引，将索引值转换为一种指针，可以直接访问提升效率。

 

3.2.2 Page Directory

        mysql的工程师经过大量测试，设置每隔6个数据为会有一个插槽，这个slot指定了一条记录，确实很像跳表，这样查询起来更高效。看来还是要了解一些优秀产品的设计理念，这样在做其他软件的时候也可以借鉴。这是个很好的设计。

3.2.3 LRU列表

        Mysql中buffer Pool对LRU算法进行了优化，LRU列表是一个双向链表，设计了老区和新区，乍一听跟jdk1.8的内存模型有点像。为什么要这么设计，因为Mysql有预读操作，提前把页写入缓存池，但最终可能没有读取到，这个就是预读失效的情况。另外当全面扫码大量数据时，buffer中热点数据将被全部替换出去，此时mysql性能必然急剧下降，这个是缓冲池污染的问题。

        New Sublist新生代、Old Sublist老生代，中间点是新生代与老生代的边界处，因为有预读，所以数据先写入中间点，真正读取的时候才会拿到LRU列表的头部。如果没有被访问过，则移动到Old sublist中。

        缓冲池污染问题处理：老生代停留时间窗口(T)，插入到old sublist即使立刻被访问，也不会立即放入new sublist头部，只有满足被访问并且在老生代停留时间>T,才会被放入新生代头部。

        

        新老占比参数，如下37表示老快占比37%。innodb_old_blocks_time代表老生代停留时间窗口，单位为毫秒（ms）

  3.2.4 change buffer       

        对于唯一索引，数据变更时在buffer pool中直接更改，针对普通索引，会将更新记录到change buffer，先判断是否在缓冲池，如果不在则将变更记录到change buffer，然后定期将数据页到内存中合并，再写入buffer pool中。也就是说要想数据写入到磁盘，必须将数据更新到buffer pool中。详细流程请看那位博主的视频，他讲的很详细。

        写buffer pool是为了减少io，而changer buffer是非唯一索引，数据是分散的，故需要统一写入buffer pool。

         all表示缓冲区插入、删除标记操作和清楚。innodb_change_buffer_max_size表示change buffer占buffer pool的百分比，一般最大设置为50%，读多写少应该减少。

3.2.5 log buffer 

        日志缓冲区，用于记录innodb引擎日志，在DML（增、删、改）操作中产生redo和undo日志。我的生产环境日志缓冲区大小为4M。并没有使用默认的16M。

        缓冲区调小，是针对资源有限的情况，我们生产环境参数调小是不对的。因为日志文件满了就会做刷盘操作，那么势必增加了磁盘IO，同时对于大事务处理性能也会下降。因此这个值最后我今天写笔记的时候发现并更正过来。innodb_log_buffer_size 是一个静态变量，意味着必须要重启mysql才可以。

        日志刷新频率，innodb_flush_log_at_trx_commit默认为1，但我的生产环境调整为2，只是为了在性能和安全方面找一个平衡点。

• 0：表示每隔1s写日志文件和刷盘操作，最多丢失1s数据；
• 1：事务提交，立刻写日志文件和磁盘，数据不丢失，但会频繁IO。默认为1.
• 2：事务提交，每秒会有一个后台线程将日志真正写入磁盘。

3.4 磁盘结构

        设计保证可靠性。

3.4.1 系统和独立表空间

•  系统表空间

        系统表空间是change buffer在磁盘中的存储区域，系统表空间默认12M，满了每次增加64M。

        可以统计一下生产环境ibdata1，这个文件已经到了140M。 

[root@bs6 mysqldata_3306]# stat ibdata1File: ‘ibdata1’Size: 146800640 	Blocks: 286720     IO Block: 4096   regular file
Device: 801h/2049d	Inode: 5905580180  Links: 1
Access: (0640/-rw-r-----)  Uid: (   27/   mysql)   Gid: (   27/   mysql)
Access: 2024-12-01 06:00:00.087284737 +0800
Modify: 2024-12-12 10:33:19.175178952 +0800
Change: 2024-12-12 10:33:19.175178952 +0800Birth: -

•  独立表空间

   又称之为每表表空间，包含单个innodb表的数据和索引。可以查看到每个一个ibd文件。

3.4.2 通用表空间

        共享表空间包括innodb系统表空间和通用表空间。这个使用要求技术就比较搞了，我的生产环境没有用到。

1. 数据分布优化
跨磁盘分布：通过将不同的表分配到位于不同物理磁盘上的通用表空间，可以分散 I/O 负载，从而提高整体性能。例如，将频繁访问的热表放置在一个快速 SSD 上，而将较少访问的冷表放置在较慢的传统硬盘上。
隔离关键表：对于某些对性能要求极高的关键业务表，可以将其单独放在一个专用的通用表空间中，以避免与其他表的竞争，并确保其获得最佳性能。
2. 简化备份和恢复
部分备份：如果某些表的数据非常重要或者变化频繁，可以通过将其分配到独立的通用表空间来进行更细粒度的备份和恢复操作，而不需要备份整个数据库实例。
快速恢复：当某个表出现问题时，可以从备份中仅恢复该表所属的通用表空间，而不影响其他表，加快了故障恢复的速度。
3. 资源管理和维护
在线重定义表：利用通用表空间可以更容易地进行在线重定义表（如 ALTER TABLE 操作），因为新旧表可以分别存放在不同的表空间中，减少了锁定时间。
表迁移：方便地将表从一个位置迁移到另一个位置，例如从一个服务器迁移到另一个服务器，只需移动相应的通用表空间文件即可。
清理过期数据：如果某些表包含大量历史数据，可以考虑将其归档到独立的通用表空间中，然后根据需要逐步删除或归档这些数据，而不影响当前活跃的数据。
4. 存储策略定制
压缩和加密：可以根据不同的安全性和性能需求，为各个通用表空间设置不同的压缩级别和加密选项。例如，敏感信息可以存储在加密的通用表空间中，而非敏感信息则可以存储在未加密但经过压缩优化的表空间中，以节省存储空间并提升读写速度。
文件系统特性利用：利用特定文件系统的特性，如快照、复制等，来增强数据保护和灾难恢复能力。
5. 多租户环境
租户隔离：在多租户环境中，每个租户的数据可以被分配到独立的通用表空间中，从而实现更好的逻辑隔离和资源管理。

3.4.3 undo表空间

        撤销表空间存储undo日志，用于事务回滚。mysql8.0.23之前，数据页为16K，undo表空间默认为10M，之后版本undo初始大小16M

        可以查看我的生产环境并没有undo表空间的问题，我的mysql版本是5.7，经调查发现mysql5.7.6之后undo表空间从系统表空间中分离，即之前版本占用的是系统表空间的共享区。

3.4.3 临时表空间

        内部临时表，在执行复杂查询，如group by、order by、distinct、union等引擎会自动根据条件触发创建临时表空间。

        外部临时表，会话结束自动清理。

 3.4.4 双写缓冲区

        mysql8.0.20之前，双写缓冲区位于innodb系统表空间，之后，位于双写文件中。Innodb在崩溃恢复期间从双写缓冲区占到数据页的副本。

        innodb的页16K，操作系统页大小是4K，因此innodb写入到磁盘需要分4次写入，如果存储引擎正在写入页到磁盘发生崩溃，就有可能发生只写一部分的情况，这种情况就是部分写失效。为解决这个问题，设计了双写缓冲区。在出现数据也损坏，在应用redo log之前，通过该页副本进行还原该页，再进行redo log重做，实现了innodb数据页写入的可靠性。

3.4.5 redo log file

        与内存中log buffer对应，

• WAL策略

        write-ahead logging，预写式日志（写前日志），先写日志，再写磁盘。性能和安全的平衡。

redo日志本身也是磁盘中的文件，为什么使用WAL策略就更高效呢？因为redo日志是顺序写，只需要把日志写入到文件的文本即可，因此效率非常搞。而最终写入数据表中是磁盘随机写。

        innodb_flush_log_at_trx_commit默认为1，我的生产环境被更改为2，重启数据库后会丢失未从文件系统缓存刷新到redo日志的那部分事务。看来这个要更正，因为财务saas这种对数据要求比较敏感，所以这位工程师不能因为之前的经验就次判定。innodb_flush_log_at_trx_commit是一个静态设置，因此也需要重启mysql才能生效。

3.4.6 undo log file

        undo log在事务开始前产生，提交后不会立刻删除undo log，innodb会将该事务对应的undolog写入删除列表，由后台线程异步删除。undo log也会产生redo log，因为undo log也需要持久化保护。

3.5 innodb线程模型

        这跟就跟微服务架构的思想一样，不同的线程负责不同的任务。

• AIO Thread

        执行show engine innodb status;，在status列可以查看到具体信息。

--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
333645 OS file reads, 997253 OS file writes, 439913 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s

        read thread：负责读取操作，将数据从磁盘加载到缓存Page页（buffer pool），4个

        write thread：负责写操作，将缓存脏页（Buffer Pool Dirty Pages）刷新到磁盘，4个

        log thread：负责将日志缓冲区内容刷新到磁盘

        insert buffer thread：负责将写缓冲区刷新到磁盘

• 清除线程（Purge Thread）

        提交事务之后，用来回收分配和使用undo页面，加快undo页面恢复速度。

   

• 页面清洁线程（Page Cleaner Thread）

        将脏页刷新到磁盘，是完成持久化的线程， MySQL 5.7默认为4，innodb_page_cleaners的最大值取决于innodb_buffer_pool_instances，必须要小于等于它。

• 主线程（Master Thread）

        负责调度其他线程，优先级最高。主要负责从缓冲池到磁盘数据的异步刷新，以确保一致性。innodb_max_dirty_pages_pct设置脏页比率，下面是达到75%就会触发刷新脏页到磁盘操作。mysql8.0之后这个默认值位90%。

3.6 一致性日志

        redo log属于物理日志。undo log和binlog属于逻辑日志。binlog工作在服务层，undo、redo日志工作在Innodb引擎层。物理日志面向数据页（描述具体某个Page的修改操作），逻辑日志记录存储事务中操作。

3.6.1 undo log

        记录修改前的数据，用于事务回撤，提供回滚操作（rollback），保证数据的原子性。undo log采取段的方式管理，每个undo log segment对应一个回滚日志，默认128个回滚段

3.6.2 redo log

        记录修改之后的数据，用于崩溃回复，提供前滚操作（重做）,保证数据的持久性。因为事务提交后并不会立刻提交磁盘，而是修改数据页（Dirty Page），数据页写入到磁盘时系统崩溃，可以通过redo log进行恢复。下面我们的生成环境nnodb_flush_log_at_trx_commit为2针对财务软件，该配置也不是正确的，应该调整为默认值1，因为2代表异步写，存在丢数据的风险。这个前面已经描述了。

3.6.3 binlog

        记录所有数据库表结构变更及表数据修改的二进制日志，不会记录show和select等操作。用于主从同步或者数据同步（如cannel中间件基于binlog做数据同步）

        下入逻辑：事务执行过程中，先把日志写入binlog cache（内存中），事务提交，binlog cache写入到binlog文件中（这步会调用fsync将数据持久化到磁盘），

        我的生产环境binlog数据格式采用ROW，mixed过于复杂，而statment则依赖上下文环境容易出错。

        日志配置如下： 

        在核心系统，这个sync_binlog应该设置为1.