Mysql 基础架构

Learn1993
225 阅读15分钟

这是我参与11月更文挑战的第4天，活动详情查看：2021最后一次更文挑战

Mysql 架构理论

Mysql 体系架构

网络连接层

  • 客户端连接器

服务层

  • 连接池: 负责存储和管理客户端与数据库的连接， 一个线程管理一个
  • 系统管理和控制工具: 备份恢复, 安全管理， 集群管理
  • SQL 接口: 用于接收SQL命令， 返回结果
  • 解析器: 将SQL解析生成一个“解析树”， 根据一些MySQL规则进一步解析是否合法
  • 查询优化器: 转换成执行计划，与搜索引擎交互
  • 缓存: 表缓存， 记录缓存， 权限缓存， 引擎缓存

存储引擎层

负责MySQL数据的存储与提取， 与底层系统文件进行交互。MySQL存储引擎是插件式的， 服务器中的查询自行引擎通过接口与存储引擎进行通信。

系统文件层

  • 日志文件

    • 错误日志(Error log)

      默认开启

       show  variables like '%log_error%'

    • 通用查询日志(General query log)

      记录一般查询日志, 默认false

      show variables like '%general%'
set global general_log = 'ON';

    • 二进制日志(binary log)

      记录了数据库执行的更改操作， 并记录了发生时间，执行时长, 不记录select, show的SQL，用于数据库恢复和主从复制

      show varibles like '%log_bin%' 
show varibles like '%binlog%'
show binary logs;

      log_bin 是个只读参数 必须去/etc/mysql/mysql.conf.d下修改mysql.cnf文件

      log_bin=rcj
server-id=1

      rcj是设定的binlog前缀名, server-id 是为了表示服务器ID

    • 慢查询日志

      记录所有执行时间超时的查询SQL， 默认关闭。 时长默认10秒

      show variables like '%slow_query%';
set global slow_query_log = 'ON';
show variables like '%long_query_time%';

  • 配置文件

    用于存放MySQL的配置文件，比如my.cnf, my.ini等

  • 数据文件

    • db.opt: 记录这个库默认使用的字符集合校验规则。
    • frm：存储与表有关的元数据(meta)信息, 包括表结构的定义信息， 每张表都有frm文件。
    • MYD: MyISAM存储引擎专用， 存放MyISAM表的数据(data), 每张表都有一个.MYD文件
    • MYI: MyISAM存储引擎专用， 存放MyISAM表的索引结构信息，每张表对应一个.MYI文件
    • ibd文件和IBDATA:存放InnoDB的数据文件(包含索引), innodb的两种表空间: 独享表空间和共享表空间。独享表空间使用.ibd存放数据， 每张表对应一个.ibd文件。共享表空间使用.ibdata文件，所有表共同使用一个(或多个).ibdata文件。
    • ibdata1: 系统表空间数据文件，存储表元数据，undo日志等
    • ib_logfile0, ib_logfile1: redo log日志文件

  • pid文件

    mysqld应用程序在unix/linux环境下的一个进程文件，存放进程ID

  • socket文件

    在unix/linux环境下客户端连接可以不通过TCP/IP网络直接使用Unix Socket来连接MySQL

运行流程

未命名文件.png

  • 建立连接

Mysql客户端与服务端的通信方式是“半双工”， 每个Mysql连接，时刻都有一个线程状态表示这个连接在做什么

show processlist

  • id: 线程ID， kill XX

  • user: 启动这个线程的用户

  • db: 命令在哪个库

  • Command: 操作命令

    • Create DB
    • Drop DB
    • Execute
    • Close Stmt
    • Query
    • Sleep
    • Quit
    • Shutdown

  • Time: 线程处于当前状态的时间， 单位是秒

  • State: 线程状态

    • Updating: 正在搜索匹配记录，进行修改
    • Sleeping: 正在等待客户端发送新的请求
    • Starting: 正在执行请求命令
    • Checking table: 检查数据表
    • Closing table: 将表中数据刷新到磁盘中
    • Locked: 被其他查询锁住记录
    • Sending Data: 正在处理select请求， 同时将结果发送给客户端

  • Info: 一般记录线程执行的语句， 默认显示前100个字符。完整可以使用show full processlist;

  • 查询缓存

    mysql8中已经去除了缓存，5.6默认关闭。如果开启缓存并且在缓存过程中查询完全相同的SQL语句，则将查询结果直接返回给客户端。即便使用查询缓存，下面SQL也不能缓存

    • 查询语句使用SQL_NO_CACHE
    • 查询结果大于query_cache_limit设置
    • 查询中有一些不确定参数，比如now
    show variables like '%query_cache%';

  • 解析器

    将SQL进行词法和语法解析， 生成解析树。预处理器根据规则进一步检查解析树是否合法，生成新的解析树。

  • 查询优化器

    分为两类: 静态优化，动态优化

    • 等价变换策略 5==5 and a> 5 改成a>5

    • 优化count, min, max函数

      innodb的min函数查找索引最左边， MyISAM的count不需要计算，直接返回

    • 提前终止查询

      • 使用limit查询，获取数据后，不再遍历

    • in的优化

      先进行排序，再用二分查找

  • 执行引擎执行SQL语句，如果开启缓存，将结果保存到缓存。返回结果过多，会采用增量模式返回。

存储引擎

innodb和MyISAM对比

  • 事务和外键: Innodb支持事务和外键， 具有安全性和完整性， 适合大量insert或update操作。MyISAM不支持，适合大量select查询。
  • 锁机制: Innodb行锁，基于索引。 MyISAM表锁
  • 索引结构: Innodb使用聚集索引。索引和记录一起存储。MyISAM使用非聚集索引， 索引和记录分开
  • 并发处理能力:MyISAM 读不阻塞，读写阻塞， 写操作并发低。Innodb读写阻塞与隔离级别有关， MVCC支持高并发。
  • 存储文件: Innodb有两个: .frm结构文件, .idb数据文件，表最大支持64TB。MyISAM三个文件: .frm结构文件， MYD表数据文件, .MYI索引文件。MySQL5.0后默认限制256TB。

Innodb存储结构

  • 内存结构

    包括Buffer Pool， Change Buffer, Adaptive Hash Index和LogBuffer四大组件。

    • Buffer Pool: 缓冲池， 以Page页为单位， 页默认大小16K， 底层使用链表管理Page。访问表记录和索引会在Page页缓存，减少磁盘IO，提升效率。

      • Page管理

        page 有三种类型: free, clean,dirty。InnoDB提供了三种链表结构来维护和管理。

        • free list: 表示空闲缓冲区， 管理free page。
        • flush list: 表示需要缓冲到磁盘的缓冲区， 管理dirty page。内部按修改时间排序。脏页既在flush链表，也在LRU链表，互不影响。LRU管理Page的可用和释放。flush链表管理脏页的刷盘。
        • LRU list: 管理clean和dirty page。缓冲区以midpoint为基点， 前面称为new列表区,存放经常访问的数据，占63%。后面链表称为old列表区， 存放使用较少的数据，占37%。
      show variables like '%innodb_page_size%';
show variables like '%innodb_old%';
show variables like '%innodb_buffer%';

      innodb_buffer_pool_size设置为总内存大小的60%-80%, innodb_buffer_pool_instances可以设置多个，可以避免缓存争夺。

    • Change Buffer: 写缓冲区，DML操作时，如果没有相应的Page数据，并不会立刻将磁盘页加载到缓冲池， 而是在Change Buffer记录缓存变更，等未来数据被读取时，再将数据合并到BP中。但是仅适用于非唯一普通索引列，如果有唯一性, 会将记录查询到BufferPool,然后在缓冲池修改。

      Change Buffer占用Buffer Pool, 默认占25%， 最大允许占50%, 参数是innodb_change_buffer_max_size。

    • Adaptive Hash Index: 优化对Buffer Pool数据的查询。

    • Log Buffer: 保存写入磁盘log文件(redo/undo)的数据， 定期刷新到磁盘log文件中。

      参数innodb_flush_log_at_trx_commit参数控制日志刷新行为，默认为1

      • 0： 每隔1秒写日志文件和刷盘操作，最多丢失1秒数据
      • 1: 事务提交，立刻写日志文件和刷盘， 数据不丢失。
      • 2: 事务提交，立刻写日志文件，每隔1秒进行刷盘。

  • 磁盘结构

    包含Tablespaces, InnoDB Data Dictionary, Doublewrite Buffer, Redo Log 和undo Logs

    • 表空间:

      • 系统表空间: 用于存储InnoDB数据字典， Change Buffer, Undo Logs的存储区域。默认包含任何用户在系统表空间创建的表数据和索引数据。系统表空间是共享表空间，通过参数innodb_data_file_path控制,默认ibdata1:12M:autoextend

      • 独立表空间: 默认开启, 独立表空间是一个单表表空间， 创建在自己的数据文件中。innodb_file_per_table选项控制。表文件是.ibd数据文件，默认被创建在数据库目录。支持动态和压缩行格式。

      • 通用表空间: 通过create tablespace语法创建的共享表空间。通用表空间可以创建在mysql数据目录外的其他表目录，可以容纳多张表， 支持所有行格式。

      CREATE TABLESPACE ts1 ADD DATAFILE ts1.ibd Engine=InnoDB;
      • 撤销表空间: 撤销表空间由一个或多个Undo日志文件组成，innodb_undo_tablespaces控制，默认0 表示使用系统表空间ibdata1，大于0表示使用undo表空间undo_001，undo_002
      • 临时表空间: 分为session temporary tablespaces和global temporary tablespace两种。 session temporary tablespace 存储的是用户创建的临时表和磁盘内部的临时表。 global temporary tablespace存储用户临时表的回滚段。mysql服务正常关闭或异常关闭时，临时表空间将被移除， 每次启动时会被创建。

    • 数据字典

      内部系统表组成, 用于查询表，索引和表字段等对象的元数据， 元数据物理上位于系统表空间中

    • 双写缓冲区

      位于系统表空间， 在Buffer Page刷新到磁盘真正位置之前，会先将数据存在Doublewrite缓冲区，如果写入过程中发生操作系统，存储子系统或mysqld崩溃， Innodb会在崩溃期间从Doublewrite缓冲区中找到页面的一个好备份。 大多数情况启用， 要禁用的话innodb_doublewrite设置为0。

      innodb_flush_method控制着innodb数据文件及redo log的打开， 刷写模式。 有三个值，默认值fdatasync， 先写操作系统缓存，再调用fsync异步刷新数据文件与redo log的缓存信息。O_DIRECT 数据文件写入操作会通知操作系统不要缓存，也不要预读，直接写入磁盘文件, 还有O_SYNC。 建议设置为O_DIRECT。

    • redo log

      基于磁盘的数据结构， 用于崩溃恢复期间更正不完整事务写入的数据。循环方式写入重做日志，记录对Buffer Pool修改的日志。默认情况下ib_logfile0和ib_logfile1表示。

    • undo log

      事务开始前保存的被修改数据的备份， 用于例外情况时回滚事务。逻辑日志， 存在系统表空间， 撤销表空间和临时表空间中。

线程模型

  • IO Thread

    4个write, 4个read, insert buffer 和log thread 共10个

    • read thread: 从磁盘加载到缓存页
    • write thread: 刷新脏页到磁盘
    • log thread: 将日志缓冲区刷新到磁盘
    • insert buffer: 将写缓冲内容刷新到磁盘

  • Purge Thread

    回收已经分配的undo页。 默认4个

    show variables like '%innodb_purge_threads%'

  • Page Clearner Thread

    将脏数据刷盘，会调用write thread处理。

    show variables like '%innodb_page_cleaners%'

  • Master Thread

    主线程， 负责调度各线程，优先级最高， 内部有两个主处理，分别是每隔1秒和10秒处理

    每隔1秒:

    • 刷新日志缓冲区到磁盘
    • 合并写缓冲区， 根据IO读写压力来决定是否操作
    • 刷新脏页数据到磁盘， 根据脏页比例达到75%才操作(innodb_max_dirty_pages_pct, innodb_io_capacity)

    每隔10秒

    • 刷新脏页数据到磁盘
    • 合并写缓冲区数据
    • 刷新日志缓冲区
    • 删除无用的undo页

Innodb数据文件

  • TableSpcae

    存储多个ibd数据文件， 用于存储表的记录和索引。一个文件包含多个段。

  • Segment

    管理多个Extent， 分为数据段，索引段， 回滚段。一个表至少有两个Segment, 一个管理数据，一个管理索引， 每多创建一个索引，会多两个segment

  • Extent

    一个区固定包含64个连续的页，大小为1M, 表空间不足的时候，回直接分配一个区

  • Page

    存储多个Row行记录，大小为16K。有很多页类型: 数据页，undo页, 系统页, 数据事务页，大的Blob对象页。 由Page header， page trailer和page body组成。

  • Row

    行， 包含记录字段， 事务ID，滚动指针， 字段指针

文件存储

select * from information_schema.innodb_sys_tables

如果row_format为REDUNDANT，COMPACT， 文件格式为Antelope;如果 row_format为DYNAMIC和COMPRESSED，格式为Barracuda。

  • REDUNDANT

    变长列值的前768字节存储在B树节点的索引记录中，其余的存储在溢出页上。对于大于等于786字节的固定长度字段InnoDB会转换为变长字段，以便能够在页外存储。

  • COMPACT

    与REDUNDANT行格式相比，COMPACT行格式减少了约20%的行存储空间，但代价是增加了某些操作的CPU使用量。如果系统负载是受缓存命中率和磁盘速度限制，那么COMPACT格式可能更快。如果系统负载受到CPU速度的限制，那么COMPACT格式可能会慢一些。

  • DYNAMIC

    使用DYNAMIC行格式，InnoDB会将表中长可变长度的列值完全存储在页外，而索引记录只包含指向溢出页的20字节指针。大于或等于768字节的固定长度字段编码为可变长度字DYNAMIC行格式支持大索引前缀，最多可以为3072字节，可通过innodb_large_prefifix参数控制

  • COMPRESSED

    COMPRESSED行格式提供与DYNAMIC行格式相同的存储特性和功能，但增加了对表和索引数据压缩的支持。

Undo Log

数据库事务开始之前，会将要修改的记录存放到 Undo 日志里，当事务回滚时或者数据库崩溃时，可以利用 Undo 日志，撤销未提交事务对数据库产生的影响。

Undo Log在事务开始前产生；事务在提交时，并不会立刻删除undo log，innodb会将该事务对应的undo log放入到删除列表中，后面会通过后台线程purge thread进行回收处理。Undo Log属于逻辑日志，记录一个变化过程。

undo log采用段的方式管理和记录。在innodb数据文件中包含一种rollbacksegment回滚段，内部包含1024个undo log segment。

show variables like '%innodb_undo%'

作用是实现事务原子性和MVCC

Redo Log

指事务中修改的任何数据，将最新的数据备份存储的位置（Redo Log），被称为重做日志。

随着事务操作的执行，就会生成Redo Log，在事务提交时会将产生Redo Log写入Log Buffffer，并不是随着事务的提交就立刻写入磁盘文件。等事务操作的脏页写入到磁盘之后，Redo Log 的使命也就完成了，Redo Log占用的空间就可以重用（被覆盖写入）。

show variables like '%innodb_log%';

Bin log

Binlog是记录所有数据库表结构变更以及表数据修改的二进制日志。Binlog日志是以事件形式记录，还包含语句所执行的消耗时间。

  • 主从复制：在主库中开启Binlog功能，这样主库就可以把Binlog传递给从库，从库拿到Binlog后实现数据恢复达到主从数据一致性。
  • 数据恢复：通过mysqlbinlog工具来恢复数据

文件记录模式有STATEMENT、ROW和MIXED三种。

  • ROW：日志中会记录每一行数据被修改的情况，然后在slave端对相同的数据进行修改。
  • STATMENT：每一条被修改数据的SQL都会记录到master的Binlog中，slave在复制的时候SQL进程会解析成和原来master端执行过的相同的SQL再次执行。简称SQL语句复制。
  • MIXED：以上两种模式的混合使用，一般会使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择写入模式。
show variables like 'log_bin'

binlog-format=ROW
log-bin=mysqlbinlog

show binary logs
show master status
show binlog events
show binlog events in 'mysqlbinlog.000001';

mysqlbinlog "mysqlbinlog.000001"
mysqlbinlog "mysqlbinlog.000001" > "test.sql"

//按指定时间恢复 
mysqlbinlog --start-datetime="2020-04-25 18:00:00" --stop- datetime="2020-04-26 00:00:00" mysqlbinlog.000002 | mysql -uroot -p1234 
//按事件位置号恢复 
mysqlbinlog --start-position=154 --stop-position=957 mysqlbinlog.000002 | mysql -uroot -p1234
//删除指定文件 
purge binary logs to 'mysqlbinlog.000001'; 
//删除指定时间之前的文件 
purge binary logs before '2020-04-28 00:00:00'; 
//清除所有文件
reset master;

expire_logs_days启动自动清理功能。默认值为0表示没启用。设置为1表示超出1天binlog文件会自动删除掉。

Redo log 和 Binlog区别

  • Redo Log是属于InnoDB引擎功能，Binlog是属于MySQL Server自带功能，并且是以二进制文件记录。
  • Redo Log属于物理日志，记录该数据页更新状态内容，Binlog是逻辑日志，记录更新过程。
  • Redo Log日志是循环写，日志空间大小是固定，Binlog是追加写入，写完一个写下一个，不会覆盖使用。
  • Redo Log作为服务器异常宕机后事务数据自动恢复使用，Binlog可以作为主从复制和数据恢复使用。Binlog没有自动crash-safe能力
avatar
文章
阅读
粉丝