MySQL架构设计浅析

1. MySQL相关文件(目录)路径

1.1 配置文件

/etc/my.cnf

/etc/mysql/my.cnf

/usr/local/etc/my.cnf

~/.my.cnf

1.2 数据目录

/var/lib/mysql

1.3 日志文件(建议关闭)

• 通用日志：记录数据库中执行的每一个动作，记录所有的sql语句

  默认路径/var/lib/mysql/${hostname}.log

  是否开启show global variables like 'general_log'

• 错误日志：记录数据库中执行的错误信息

  默认路径/var/log/mysqld.log

  是否开启show variables like 'log_error'

• 二进制日志：主从复制时使用

  默认路径/var/lib/mysql/${hostname}-bin.index、/var/lib/mysql/${hostname}-relay-bin.index

• 慢查询日志：记录执行时间比较长的sql语句，用于数据库调优

  默认路径/var/lib/mysql/${hostname}-slow.sql

  是否开启show variables like 'slow_query_log'

1.4 数据文件

• 系统数据文件

  • ibdata1：系统表空间，记录系统的相关数据
  • ib_logfile0、ib_logfile1：redo log文件(重做日志文件)，mysql保证数据不丢失的一个重要环节文件，与log buffer缓存对应
  • ibtmp1：临时表空间数据文件

• 数据库数据文件

  • 每个数据库都对应一个目录，其中包含所有的表相关文件

  • 每个表都对应磁盘上的一个或者多个文件

  • 不同的存储引擎生成的文件是不一样的，同一个数据库中可以使用多种引擎创建表的

    frm文件是表结构定义文件

    ibd文件是InnoDB引擎的数据和索引文件

    MYD文件是MyISAM引擎表的数据文件

    MYI文件是MyISAM引擎表的索引文件

2. MySQL的逻辑架构

2.1 server层

• 连接池(服务端)

• 分析器

• 查询缓存

  MySQL8版本之后就已经移除了，原因：1是占内存，2是缓存维护需要资源，3是缓存的命中率不高

  查询缓存设置情况语句：show variables like 'query_cache_type'

  ​ 0或者off表示禁用

  ​ 1或者on表示除SELECT SQL_NO_CACHE开头之外的语句都进行缓存

  ​ 2或者DEMAND表示只缓存SELECT SQL_CACHE开头的语句

  查询缓存命中情况语句：show status like 'Qcache_hits'

  缓存清理语句

  ​ FLUSH QUERY CACHE表示清理缓存碎片

  ​ RESET QUERY CACHE表示清空缓存

  ​ FLUSH TABLES表示重新加载所有打开的表，同时也会清空缓存

• 优化器

• 执行器

• 数据库引擎

2.2 引擎层

• InnoDB

  • 特性：支持表锁、行锁；支持事务；count结果获取为扫表

  • 磁盘结构：

    系统表空间(ibdata1)：数据字典、双写缓冲区、修改缓冲区、回滚段

    用户表空间：保存用户数据的磁盘文件，即ibd文件，默认每张表一个文件，由 innodb_file_per_table参数控制，若该参数为off那么用户表空间的数据将存放在系统表空间中

    通用表空间：默认没有磁盘文件，使用create tablespace命令创建的表空间就是通用表空间，类似于Oracle

    undo表空间：默认没有磁盘文件，默认回滚日志保存在系统表空间中

    临时表空间：默认没有，mysql数据库使用到临时表时会自动生成

    redo log(重做日志文件)：保证数据不丢失的一组重要文件（默认2个，每个50MB大小），提交事务之前先把内存缓冲区的redo log写入文件

  • 内存结构：

    buffer pool：数据页、索引页、自适应hash索引(完全由InnDB控制)、修改缓冲区，由innodb_buffer_pool_size参数控制

    redo log buffer：redo log缓冲区，分为主线程定时每秒写入到磁盘中的redo log文件，和事务提交时主动写入到redo log文件两种情况，其中事务提交可以通过innodb_flush_log_at_trx_commit参数控制，该参数有三个值：0表示提交事务时不进行写入磁盘，1表示提交事务时直接写入磁盘，2表示提交事务时写入操作系统缓存，具体什么时候写入磁盘由操作系统决定

  • 逻辑存储结构：

    表空间：所有数据存储的空间称之为表空间

    段：表空间是由各个段组成的，常见的段有数据段、索引段、回滚段，一个段会包含多个区，但至少一个

    区：由64个连续的页组成，所以一个区有1mb

    页：固定16kb，由innodb_page_size参数控制

    行：InnoDB数据是以行为单位存储的，1页中包含多个行数据，若一行数据超过16kb，则是在数据页之间通过指针的方式进行跨数据页进行存储

• MyISAM

  特性：支持表锁，不支持行锁；不支持事务；count结果获取有专门的存储地方

• Memory

  内存引擎，将所有数据都保存在内存中，操作速度快，但是重启数据库后数据将丢失

3. InnoDB引擎数据更新流程

1. 执行update语句时，首先把对应数据页查询出来，放到buffer pool中，若数据页本身就在buffer pool中则直接进行下一步
2. 按照update语句修改buffer pool中的对应的数据
3. 这时buffer pool中的数据页和磁盘的数据页开始不一致，就产生了脏页
4. 在第2步修改buffer pool数据页之前，先把修改动作记录到redo log buffer中
5. commit时，就是把redo log buffer中的内容刷新到redo log文件中
6. 如果写入成功就表示commit成功，写入失败就需要rollback

4. InnoDB引擎脏页落盘流程

提示：脏页是指buffer pool中的数据页和磁盘中的数据页不一致，redo log buffer和redo log文件只是保证事务提交后数据不会丢失，一个必须的辅助流程，与buffer pool脏页落盘是两个不同的事情，但又息息相关

4.1 为什么需要脏页落盘

1. buffer pool中的脏页需要移除
2. redo log文件需要覆盖，但被覆盖的数据中存在脏页还未处理

4.2 落盘的时机

• sharp checkpoint：当关闭数据库之前执行强制落盘，把buffer pool中的脏页全都落盘

• fuzzy checkpoint：模糊落盘

  • Master Thread Checkpoint：主线程定时执行落盘操作，每1秒和每10秒执行
  • FLUSH_LRU_LIST Checkpoint：若buffer pool中需要移除的数据是脏页时需要落盘操作
  • Async/Sync Flush Checkpoint：以redo log文件使用情况为条件，当该文件使用量小于75%时，不执行落盘，当该文件使用量大于75%小于90%时，以异步方式执行落盘，当该文件使用量大于90%时，以同步方式执行落盘
  • Dirty Page too much：当buffer pool脏页太多时执行落盘

4.3 落盘的流程

1. 先把脏页写入内存中的双写缓冲区(2MB)

2. 然后分两次每次1MB将内存双写缓冲区的数据写入系统表空间的双写缓冲区

3. 最后再分两次每次1MB把双写缓冲区的数据写入用户空间数据文件