MySQL原理

1、JDBC Connection实例是线程安全的吗？

Connection实例是线程安全的吗？

能不能只创建一次，共享Connection对象？

答案是不能的， Connection不是线程安全的，他会在多线程环境下，导致数据库操作的混乱，特别是在事务存在的情况下：可能一个线程刚开启事务con.setAutoCommit(true)，而另一个线程直接提交事务con.commit();
对于单独查询的情况，似乎不会出现数据错乱的情况。是因为在JDBC中，使用了锁进行同步

源码：com.mysql.cj.jdbc.CallableStatement#executeQuery

connection本身是线程不安全的，并且connection创建开销比较大，所以一般使用数据库连接池来统一的管理connection对象，例如druid连接池，c3p0连接池等等

数据库连接池

在使用数据库连接池时，一个线程中所有DB操作都是使用同一个Connection实例吗？
在Spring环境中，获取connection源码如下所示：
源码：org.springframework.jdbc.datasource.DataSourceUtils#doGetConnection

非事务场景：在非事务创建中(同时没有使用Spring事务管理器)，每一次访问数据库，都是在DataSource中取出一个Connection实例，调用完毕之后归还资源，因此多次调用，应该是不同的Connection实例

事务场景：在使用事务的情况下，实际上是在ConnectionHolder中获取的Connection。而ConnectionHolder是在TransactionSynchronizationManager中获取的resources属性的值，即connection对象信息

源码：
org.springframework.transaction.support.TransactionSynchronizationManager#doGetResource

而ThreadLocal<Map<Object,Object>>线程上下文共享。即Connection对于与Thread绑定。因此在事务中无论操作多少次DB，事实上都是操作的同一个Connection对象

2、MySQL架构设计

SQL接口：SQL的标准来接受
SQL解析器：理解这个SQL语句要干什么事情
查询优化器：选择一个最优的查询路径
执行器：执行器就会去根据我们的优化器生成一套执行计划，然后不停的调用存储引擎的各种接口去完成SQL语句的执行计划
存储引擎：存储引擎其实就是执行SQL语句的，他会按照一定的步骤去查询内存数据，更新磁盘数据，查询磁盘数据，等等，执行诸如此类的一系列的操作，MySQL的架构设计中，SQL接口，SQL解析器，查询优化器其实都是通用的，他就是一套组件而已，但是存储引擎的话，他是支持各种各样的存储引擎的，比如我们常见的InnoDB、MyISAM、Memory等等

3、InnoDB内存结构

4、Buffer Pool

Buffer Pool: 缓冲池，简称BP。其作用是用来缓存表数据与索引数据，减少磁盘IO操作，提供效率
Buffer Pool由缓存数据页和对缓存数据页进行描述的控制块组成，控制块中存储着对应缓存页的所属的表空间、数据页的编号、以及对应缓存页在Buffer Pool中的地址等信息
Buffer Pool默认大小是128M，以Page页为单位，Page页默认大小16kb，而控制块的大小为数据页的5%，大概是800字节

注意：Buffer Pool大小为128M指的就是缓存页的大小，控制块则一般占5%，所以每次会多申请6M的内存空间用于存放控制块

如何判断一个页是否在Buffer Pool中缓存？
MySQL中有一个哈希表数据结构，它使用表空间号+数据页号，作为一个key，然后缓存页对应的控制块作为value

• 当需要访问某个页的数据时，先从哈希表中根据表空间号+页号看看是否存在对应的缓存页
• 如果有，则直接使用；如果没有，就从free链表中选出一个空闲的缓存页，然后把磁盘中对应的页加载到缓存页的位置

Page页

Buffer Pool的底层采用链表数据结构管理Page。在InnoDB访问表记录和索引时会在Page页中缓存，以后使用可以减少磁盘IO操作，提升效率

Page分类

Page页根据状态分为三种类型 :

free page : 空闲page，未被使用
clean page : 被使用page，数据没有被修改过
dirty page : 脏页，被使用page，数据被修改过，页中数据和磁盘数据产生了不一致

Page如何管理

针对上面所说的三种page类型，InnoDB通过三种链表结构来维护和管理

• free list : 表示空闲缓冲区，管理free page
  Buffer Pool的初始化过程中，是先向操作系统申请连续的内存空间，然后把它划分成若干个控制块&缓存页的键值对
  free链表是把所有空闲的缓存页的控制块作为一个个的节点放到一个链表中，这个链表便称之为free链表
  基节点：free链表中只有一个基节点是不记录缓存页信息(单独申请空间)，它里面就存放了free链表头节点地址，尾节点地址，还有free链表当前有多少个节点
  
  磁盘加载过程 :

  • 从free链表中取出一个空闲的控制块(对应缓存页)
  • 把该缓存页对应的控制块的信息填上(例如：页所在的表空间、页号之类的信息)
  • 把该缓存页对应的free链表节点(即：控制块)从连接中移除。表示该缓存页已经被使用
• flush list : 表示需要刷新到磁盘的缓冲区，管理dirty page，内部page按修改时间排序
  InnoDB引擎为了提高处理效率，在每次修改缓存页后，并不是立刻把修改刷新到磁盘上，而是在未来的某个时间点进行刷新操作，所以需要使用到flush链表存储脏页，凡是被修改过的缓存页对应的控制块都会作为节点加入到flush链表
  
  脏页即存在于flush链表，也在LRU链表中，但是两种互不影响，LRU链表负责管理page的可用性和释放，而flush链表负责管理脏页的刷盘操作

• LRU list : 表示正在使用的缓冲区，管理clean page和dirty page
  普通LRU算法
  LRU = Least Recently Used(最近最少使用) : 就是末尾淘汰法，新数据从链表头部增加，释放空间的从末尾淘汰
  

  • 当要访问某个页时，如果不在Buffer Pool，需要把该页加载到缓冲池，并且把缓存页对应的控制块作为节点添加到LRU链表的头部
  • 当要访问某个页时，如果在Buffer Pool中，则直接把该页对应的控制块移动到LRU链表的头部
  • 当需要释放空间时，从末尾淘汰

  普通LRU链表的优缺点：
  优点：所有最近使用的数据都在链表表头，最近未使用的数据都在链表表尾，保证数据能最快获取到
  缺点：
  1、 如果发生全表扫描(比如：没有建立合适的索引or查询时使用select * 等)，则有很大可能将真正热数据淘汰掉
  2、 由于MySQL中存在预读机制，很多预读的页都会被放到LRU链表的表头。如果这些预读的页都没有用到的话，这样，会导致很多尾部的缓存页很快就会被淘汰
  
  改进型LRU算法
  缓冲区以midpoint为基点，前面链表为new列表区，存放经常访问的数据，占63%；后面的链表称为old列表区，存放使用较少数据，占37%。加入元素时并不是从表头插入，而是从中间midpoint位置插入(就是说从磁盘中心读出的数据会放在冷数据区的头部)，如果数据很快被访问，那么page久会向new列表头部移动，如果数据没有被访问，会逐步向old尾部移动，等待淘汰
  
  冷数据区的数据页什么时候会被转到热数据区呢？

• 就是说，必须是一个数据页被加载到缓存页之后，在1s之后，你访问了这个缓存页，它才会被挪到热数据区域的链表头部去
• 1s这个时间是由参数 innodb_old_blocks_time 控制的

5、Change Buffer

Change Buffer : 写缓冲区，是针对二级索引(辅助索引)页的更新优化措施
作用：在进行DML操作时，如果请求的是辅助索引(非唯一键索引)没有在缓冲池中，并不立刻将磁盘页加载到缓冲池，而是在Change Buffer记录缓冲变更，等未来数据被读取时，再将数据合并恢复到Change Buffer中，从而减少磁盘IO的写
Change Buffer占用BufferPool空间，默认占25%，最大允许50%。可根据读写业务量来进行调整。参数 innodb_change_buffer_max_size

更新流程

写缓冲区，仅适用于非唯一普通索引页，为什么？

如果在索引设置唯一性，在进行修改时，InnoDB必须要做唯一性校验，因此必须查询磁盘，做一次IO操作。会直将记录插入到Buffer Pool中，然后再缓冲池修改，不会在ChangeBuffer操作

什么情况下进行merge?

将change buffer中的操作应用到原数据页，得到最新结果的过程称为merge
change buffer，实际上它是可以持久化的数据。也就是说，change buffer 在内存中有拷贝，也会被写入到磁盘上，以下情况会进行持久化：

• 访问这个数据页会触发merge
• 系统有后台线程会定期merge

• 在数据库正常关闭(shutdown)的过程中，也会执行merge操作

  Change Buffer的使用场景

  change buffer的主要目的就是将记录的变更动作缓存下来，所以在merge发生之前应当尽可能多的缓存变更信息，这样change buffer的优势发挥的就越明显
  应用场景：对于写多读少的业务来说，页面在写完以后马上被访问的概率比较小，此时change buffer的使用效果最好。这种业务模型常见的就是账单类、日志类的系统
  Buffer Pool内存结构

6、InnoDB 磁盘结构

InnoDB磁盘主要包含Tablespaces、InnoDB Data Dictionary、DoubleWrite Buffer、redo log和undo logs

表空间(Tablespaces)

表空间(Tablespaces) : 用于存储表结构(t_user.frm)和数据(t_user.ibd)，InnoDB表空间类型包括系统表空间、File-Per-Table表空间、常规表空间，Undo表空间、临时表空间等

• 系统表空间(The System Tablespace)

  • 包括InnoDB数据字典，DoubleWrite Buffer，Change Buffer，Undo Logs的存储区域。系统表空间也默认包含任何用户在系统表空间创建的表数据和索引数据
  • 系统表空间是一个共享的表空间因为它是被多个表共享的。该表空间的数据文件通过参数innodb_data_file_path控制，默认值是 ibdata1:12M:autoextend(文件名为ibdata1、12M、自动扩展)
    

• 独立表空间(File-Per-Table Tablespaces)

  • 默认开启，独立表空间是一个单表表空间，该表创建于自己的数据文件中，而非创建系统表空间中。当innodb_file_per_table选项开启时，表将被创建于独立表空间中。否则，innodb将被创建于系统表空间中
    
  • 每个表文件表空间由一个.ibd数据文件代表，该文件默认被创建于数据库目录中
    
  • 独立表空间的好处
    在独立表空间下，删除或者清理表后，存储空间会立刻返回给操作系统。而在共享表空间下，表空间数据文件的大小不会缩小
    可以通过复制File-per-table表空间的对应表的数据文件到其他mysql数据库实例的表空间下，实现表的导入迁移

• 通用表空间(General Tablespaces)

  • 通用表空间为通过create tablespace语法创建的共享表空间。通用表空间可以创建于mysql数据目录外的其他表空间，其可以容纳多张表，且支持所有的行格式
  • 通用表空间和系统表空间类似，也是共享的表空间，一个文件能够存储多个表数据。相比File-per-table表空间，通用表空间由于多表共享表空间，消耗的内存会更少一点，具有潜在的内存优势(占用的磁盘空间会更少)。
    首先ts1.ibd必须是chown -R mysql:mysql ts1.ibd，有mysql权限
    

• 撤销表空间(Undo Tablespaces)

  • 撤销表空间，用来保存回滚日志，即undo logs。撤销表空间由一个或多个包含Undo日志文件组成，会在undo tablespaces下生成undo_001和undo_002供2个文件
  • 回滚表空间的文件必须以”.ibu”作为扩展后缀名
  • 可以通过innodb_undo_directory属性查看回滚表空间的位置。默认路径是mysql的数据存储路径
  • InnoDB使用的undo表空间由 innodb_undo_tablespaces 配置选项控制，默认为0。参数值为0表示使用系统表空间ibdata1；大于0表示使用undo_001、undo_002等
    

• 临时表空间(Temporary Tablespaces)

  • 分为会话临时表空间和全局临时表空间两种。会话临时表空间存储的是用户创建的临时表和磁盘内部的临时表。全局临时表空间存储用户临时表的回滚段(rollback segments)
  • Session临时表空间在session请求时创建的，最大分配2个，一个是用户创建的临时表空间，一个是优化器创建的临时表空间。全局临时表空间默认是数据目录的ibtmp1文件，所有临时表共享，可以通过innodb_temp_data_file_path属性指定临时表空间的位置
    

  数据字典

  InnoDB数据字典由内部系统表组成，这些表包含用于查找表、索引、和表字段等对应的元数据。元数据物理上位于InnoDB系统表空间中。在MySQL8.0之前，由于历史原因，数据字典元数据在一定程度上与InnoDB表元数据文件(.frm文件)中存储的信息重叠
  
  注意：MySQL8.0之后，将所有原先存放于数据字典文件中的信息，全部存放到数据库系统表中，即将之前版本的.frm，.opt等文件都移除了，不再通过文件的方式存储数据字典信息

双写缓冲区

• 什么是写失效(部分页失效)
  InnoDB的页和操作系统的页大小不一致，InnoDB页大小一般为16kb，操作系统页大小为4k，InnoDB的页写入到磁盘时，一个页需要分4次写
  如果存储引擎正在写入页的数据到磁盘时发生了宕机，可能出现页只写了一部分的情况，比如只写了4k就宕机了，这种情况叫做部分写失效(partial page write)，可能会导致数据丢失
  有经验的DBA可能会想到，如果发生写失效，MySQL可以根据redo log进行恢复。这是一个办法，但是必须清楚认识到，redo log中记录的是对页的物理修改，如偏移量800，写’aaa’记录。如果这个页本身已经发生了损坏，再对其进行重做是没有意义的
  
• 双写缓冲区 DoubleWrite Buffer
  为了解决写失效问题，InnoDB实现了double write buffer，它位于系统表空间，是一个存储区域。在Buffer Pool的page页刷新到磁盘整整的位置前，会先将数据存在DoubleWrite缓冲区。这样在宕机重启时，如果出现数据页损坏，那么在应用redo log之前，需要通过该页的副本还原该页，然后再进行redo log重做，double write实现了InnoDB引擎数据页的可靠性
  

• 数据双写流程
  

  • step1 : 当进行缓冲池的脏页刷到磁盘的操作时，并不会直接写磁盘，每次脏页刷新必须要先写double write
  • step2 : 通过memcpy函数将脏页复制到内存中的double write buffer
  • step3 : double write buffer再分两次，每次1M，顺序写入共享表空间的物理磁盘上，第一次写
  • step4 : 在完成double write页的写入后，再将double write buffer中的页写入各个表的独立表空也文件中(数据文件.ibd)，第二次写
• 为什么要写两次
  可能有的同学有疑问，为啥写两次，刷一次数据文件保存数据不就可以了，为什么还要写共享表空间？其实是因为共享表空间是在ibdata文件中划出2MB连续的空间，专门给double write脏页用的，由于在这个过程中，double write也的存储是连续的，因此写入磁盘为顺序写，性能很高，完成double write后，再将脏页写入实际的各个表空间文件，这时写入就是离散的了

7、重做日志

WAL(Write-Ahead Logging)机制

WAL(Write-Ahead Logging)，预写日志(日志先行)，是一种数据安全写入机制。就是写先日志，然后再写磁盘。MySQL中的redo log就是采用WAL机制

为什么使用WAL？

磁盘的写是随机的，比较耗性能，所以如果把每一次的更新操作都写入log中，那么就成了顺序写操作，实际更新操作由后台线程在根据log异步写入。这样对client端，延迟就降低了。并且，由于顺序写入大概率是在一个磁盘块内，这样产生的io次数也大大降低。所以WAL的核心在于将随机写转换为了顺序写，降低了客户端的延迟，提升了吞吐量

redo log基本概念

redo log : 包含两部分，一个是内存中的日志缓冲 : redo log buffer，另一个是磁盘上的日志文件 ，redo log file
MySQL每执行一条DML语句，先将记录写入redo log buffer，后续某个时间点再一次性将多个操作记录写到redo log file。当故障发生致使内存数据丢失后，InnoDB会在重启时，经过重放redo log，将page恢复到崩溃之前的状态(实现事务性的持久性)

redo log持久化策略

缓冲区数据一般情况下是无法直接写入磁盘的，中间必须经过操作系统(OS Buffer)。因此，redo log buffer写入redo log file实际上是先写入OS Cache，然后再通过系统调用fsync()将其刷到redo log file.
redo buffer持久化到redo log的策略，可通过 innodb_flush_log_at_trx_commit设置 :
一般建议选择取值2，因为MySQL挂了没有损失，整个服务挂了才会损失1秒的事务提交数据，默认是1

redo log日志格式

物理日志vs 逻辑日志

• 物理日志 : 记录的是每一个Page页中具体存储的值是多少，在这个数据页上做了什么修改，比如：某个事务将系统表空间中的第100个数据页中偏移量为1000处的那个字节的值改为2

• 逻辑日志：记录的是每一个page页中具体数据是怎么变动的，他会记录一个变动的过程或SQL语句逻辑，比如：把一个page页中的一个数据从1改为2，再从2改为3，逻辑日志会记录1->2，2->3这个数据变化的过程

  redo日志属于物理日志，只是记录一下事务对数据库做了哪些修改
  
  type : 该条日志的类型
  space Id : 表空间ID
  page number : 页号
  data : 该条redo日志的具体内容

  redo log日志类型

  redo log根据在页面中写入数据的多少，将redo log日志划分为几种不同的类型 :

• MLOG_1BYTE(type=1) : 表示在页面的某个偏移量处写入1字节的redo日志类型
• MLOG_2BYTE(type=2) : 表示在页面的某个偏移量处写入2字节的redo日志类型
• MLOG_4BYTE(type=4) : 表示在页面的某个偏移量处写入4字节的redo日志类型
• MLOG_8BYTE(type=8) : 表示在页面的某个偏移量处写入8字节的redo日志类型
  

• MLOG_WRITE_STRING(type=30) : 表示在页面的某个偏移量处写入一串数据，但是因为不能确定写入的具体数据占用多少字节，所以需要在日志结构中添加一个Len字段
  

  redo log写入机制

  每个InnoDB存储引擎至少有1个重做日志文件组(group)，每个文件组下至少有两个重做日志文件，默认的为ib_logfile0、ib_logfile1
  日志组中每个重做日志的大小一致，并循环使用
  InnoDB以环形方式写入数据到重做日志文件，当文件1满了的时候，会自动切换到日志文件2，当重做日志文件2也满时，再切换到重做日志1
  
  write pos : 表示日志当前记录的位置，当ib_logfile_4写满后，会从ib_logfile_1开始记录
  check point : 表示将日志记录的修改写进磁盘，完成数据落盘，数据落盘后checkpoint会将日志上的相关记录擦出掉，即write pos > checkpoint 之间的部分是redo log空着的部分(顺指针)，用于记录新的记录，checkpoint -> write pos之间是redo log待落盘的数据修改记录

  如果write pos追上checkpoint，表示写满，这时候不能再执行新的更新，得停下来先擦掉一些记录，把checkpoint推进以下

  redo log相关参数

  

• innodb_log_buffer_size : log buffer的大小，通常设置为8M ~ 16M(因为MySQL每秒都会将日志缓冲区的内容刷到日志文件，因此无需设置超过1秒所需的内存空间)
• innodb_log_file_size : 事务日志的大小，默认48M
• innodb_log_files_group = 2 : 事务日志组中的事务日志个数，默认2个
• innodb_log_group_home_dir = ./ : 事务日志组路径，当前目录表示数据目录

8、撤销日志(undo log)

undo log基本概念

undo log是一种用于撤销回退的日志，在事务没提交之前，MySQL会先记录更新前的数据到undo log日志文件里，当事务回滚或者数据崩溃时，可以利用undo log来进行回滚，回到事务开始之前的状态

undo log的作用

• 提供回滚操作(undo log实现事务的原子性)
  在数据修改的时候，不仅记录了redo log，还记录了想对应的undo log，如果因为某些原因导致事务失败或回滚了，可以借助undo log进行回滚
  undo log和redo log记录物理日志不一样，它是逻辑日志。可以认为当delete一条记录时，undo log中会记录一条对应的insert记录
• 提供多版本控制(MVCC)(undo log实现多版本并发控制MVCC)
  MVCC，即多版本控制。在MySQL数据库InnoDB存储引擎中，用undo log来实现多版本并发控制(MVCC)，当读取的某一行被其他事务锁定，它可以从undo log中分析出来该行记录以前的数据版本是怎样的，从而让用户能投读取到当前事务之气的数据(快照读)

undo log的工作原理

在更新数据之前，MySQL会提前生成undo log日志，当事务提交的时候，并不会立即删除undo log，因为后面可能需要进行回滚操作，要执行回滚(rollback)操作时，从缓存中读取数据。undo log日志的删除是通过后台purge线程进行处理的

undo log的存储机制

为了保证事务并发操作时，在写各自的undo log时不产生冲突，InnoDB采用回滚的方式来维护undo log的并发写入和持久化
rollback segment称为回滚段 共有128个，每个回滚段中有1024个undo log segment，即支持128 * 1024个undo操作

undo log日志里面不仅存放着数据更新前的记录，还记录着RowID、事务ID、回滚指针。
其中事务ID每次递增，回滚指针第一次如果是insert语句的话，回滚指针为NULL，第二次update之后的undo log的回滚指针就会指向刚刚那一条undo log日志，以此类推，就会形成一条undo log的回滚链，方便找到该记录的历史版本

undo log相关参数

MySQL与undo相关的参数设置：

• innodb_max_undo_log_size
  表示每一个undo log对应的日志文件的最大值，默认最大值为1GB大小，默认初始化大小为10MB
  日志文件达到该阈值之后，且参数 innodb_undo_log_truncate=ON，才会触发truncate回收(收缩)动作，被truncate后的表空间文件大小缩小到undo log表空间数据文件默认的10MB大小。否则即便是到达最大之后，也不会自动回收undo log的表空间
• innodb_undo_directory
  指定undo log日志的存储目录，默认值为./
• innodb_undo_logs
  在MySQL5.6版本之后，可以通过此参数自定义多个rollback segment，默认值为128
• innodb_undo_tablespaces
  设置undo独立表空间个数，范围为0-128，默认为0，0表示不开启独立undo表空间且undo日志存储在ibdata文件中
  当DB写压力较大时，可以设置独立undo表空间，把undo从ibdata文件中分离开来，指定innodb_undo_directory目录存放，可以指定到SSD，加快undo log的读写性能
• innodb_undo_log_truncate
  表示是否开启自动收缩undo log的表空间操作。如果配置为ON，并且配置了2个或2个以上的undo log表空间数据文件，当某一个日志文件大小超过设置的最大值之后，就会自动的收缩数据文件，配置为2的原因是，在表空间收缩的时候，至少有一个undo log是在线的
• innodb_purge_rseg_truncate_frequency
  控制回收(收缩)undo log的频率。undo log空间在它的回滚段没有得到释放之前不会收缩，想要增加释放回滚区间的频率，就得降低innodb_purge_rseg_truncate_frequency设定值，该值越小，undo表空间被尝试truncate的频率越高。默认值为128次

9、二进制日志(Binlog)

Binlog基本概念

binlog是一个二进制格式的文件，用于记录用户对数据库更新的SQL语句信息，例如更改数据库表和更改内容的SQL语句都会记录在binlog里，但是不会记录SELECT和SHOW这类操作
默认情况下，binlog日志是二进制格式的，不能使用查询文本工具的命令(比如，cat,vi等)查看，可以使用mysqlbinlog解析查看

开启Binlog日志有以下两个重要的使用场景：

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

Binlog日志三种模式

• ROW(row-based replication，RBR) : 日志中会记录每一行数据被修改的情况，然后再slave端对相同的数据进行修改
  优点：能情况记录每一行数据的修改细节，能完全实现主从数据同步和数据的恢复
  缺点：批量操作，会产生大量的日志，尤其是alter table会让日志暴涨
• STATEMENT(statement-based replication，SBR) : 记录每一条修改数据的SQL语句(批量修改时，记录的不是单SQL语句，而是批量修改的SQL语句事件)，slave在复制的时候SQL进程会解析成和原来master端执行过的相同SQL再次执行。简称SQL语句复制
  优点：日志量小，减少磁盘IO，提升存储和恢复速率
  缺点：在某些情况下会导致数据不一致，比如last_insert_id()、now()等函数
  
• MIXED(mixed-based replication，MBR) : 以上两种模式的混合使用，一般会使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择写模式

企业场景如何选择Binlog的模式

• 如果生产中使用MySQL的特殊功能相对少(存储过程、触发器、函数)。选择默认的语句模式，Statement Level
• 如果生成中使用MySQL的特殊功能较多的，可以选择Mixed模式
• 如果生产中使用MySQL的特殊功能较多，又希望数据最大化一致，此时最好Row Level模式，但是要注意，该模式的binlog非常”沉重”

Binlog文件结构

MySQL的binlog文件中记录的是对数据库的各种修改操作，用来标识修改操作的数据结构是Log Event。不同的修改操作对应不同的log event。比如常用的log event有 : Query Event、Row Event、Xid Event等。binlog文件的内容就是各种Log Event的集合

Binlog写入机制

binlog什么时候刷新到磁盘？binlog刷数据到磁盘跟参数sync_binlog相关

• 如果设置为0，则表示MySQl不控制binlog的刷新，由文件系统去控制日志的刷新

• 如果设置为不为0的值，则标识每sync_binlog次事务，MySQL调用文件系统刷新binlog到磁盘中
  

  写入 :
  

Redo log和Binlog的区别

• redo log是InnoDB存储引擎特有的；binlog是MySQL的Server层实现的，所有引擎都可以使用
• redo log是物理日志，记录的是在”XXX数据页上做了XXX的修改”；binlog是逻辑日志，记录的是原始逻辑，其记录的是对应的SQL语句
• redo log 是循环写的，空间一定会用完，需要write pos和check point搭配；binlog是追加写，写到一定大小会切换到下一个，并不会覆盖以前的日志
• redo log作为服务器异常宕机后数据自动恢复使用，binlog可以作为主从复制和数据恢复使用。binlog没有自动crash-safe能力

Binlog命令操作

• 使用sql命令查看binlog文件
  启动binlog
  
• 启动成功之后，我们可以登录查看我们的配置是否起作用
  
• 查看binlog文件列表
  
• 查看正在写入的binlog文件
  
• 查看binlog文件信息
  
• mysql提供了一个用于查看binlog日志的工具，叫做mysqlbinlog
  

10、新版本结构演进

MySQL 5.7

• 将undo日志从共享表空间ibdata文件中分离出来，可以在安装MySQL时由用户自定指定文件大小和数量
• 增加了temporary临时空间，里面存储着临时表和临时查询结构数据集的数据
• Buffer Pool大小可以动态修改，无需重启数据库实例

MySQL 8.0

• 将InnoDB表的数据字典和undo都从共享表空间ibdata中彻底分离出来了，以前需要ibdata中数据字典与独立表空间ibd文件中数据字典一直才行，8.0版本就不需要了
• temporary临时表空间也可以配置多个物理文件，而且均为InnoDB存储引擎并能创建索引，这样加快了处理的速度
• 用户可以像Oracle数据库那这样设置一些表空间，每个表空间对应多个物理文件，每个表空间可以给多个表使用，但一个表只能存储在一个表空间中
• 将DoubleWrite Buffer从共享表空间ibdata中也分离出来了

11、InnoDB线程模型

IO Thread

在InnoDB中使用大量的AIO(Asyn IO)来做读写处理，这样可以极大提高数据库的性能。在InnoDB1.0版本之前共有4个IO Thread，分别是write、read、insert buffer和log thread，后来版本将read thread和write thread分别增加到4个，一共10个了

• read thread : 负责读取操作，将数据从磁盘加载到缓存page页。4个
• write thred : 负责写操作，将缓冲增液刷新到磁盘。4个
• log thread : 负责将日志缓冲区内容刷新到磁盘。1个
• insert buffer thread : 负责将写缓冲刷新到磁盘。1个

Purge Thread

事务提交之后，其使用undo日志将不再需要，因此需要Purge Thread回收已经分配的undo页

Page Clenaer Thread

作用是将脏数据刷新到磁盘，脏数据刷盘后相应的redo log也就可以覆盖，即可以同步数据，又能达到redo log循环使用的目的。会调用write thread线程处理

Master Thread

Master Thread是InnoDB的主线程，负责调度其他线程，优先级最高。作用是将缓冲池中的数据异步刷新到磁盘，保证数据的一致性。包括：脏页的刷新(page cleaner thread)、undo页回收(purge thread)、redo日志刷新(log thread)、合并写缓冲等

12、InnoDB数据文件

InnoDB表空间文件结构分为：Tablespace(表空间) -> Segment(段) -> Extent(区) -> Page(页) -> Row(行)

• Tablespace
  表空间能够看做是InnoDB存储引擎逻辑结构的最高层，用于存储多个ibd数据文件，用于存储表的记录和索引。一个文件包含多个段
  表空间分为：系统表空间、独立表空间、通用表空间、临时表空间、undo表空间
• 段(Segment)
  段是磁盘上空间分配和回收的申请者，是一个逻辑概念，用来管理物理文件，常见的段有数据段、索引段、回滚段等，其中索引段是非叶子节点部分，而数据段就是叶子节点部分，回滚段用于数据的回滚和版本控制
• 区(Extend)
  区是由连续页组成的空间，每个区的默认大小都是1MB，一个区中有64个连续的页。为了保证区中页的连续性，扩展的时候InnoDB存储引擎一次从磁盘申请4~5个区
• 页(Page)
  区是由连续的页(Page)组成的空间，默认每一个页的存储大小16kb，页，用于存储多个Row行记录。包含很多页类型，比如数据页，undo页，系统页，事务数据页，大的BLOB对象页
• 行(Row)
  InnoDB存储引擎是面向行的(row-oriented)，也就是说数据的存放按行进行存放
  行，包含了记录的字段值，DB_ROW_ID、事务ID(TRX ID)、回滚指针(Roll pointer)、字段指针(Field pointers)等信息

Page结构

Page是整个InnoDB存储的基本构件，也是InnoDB磁盘管理的最小单位，与数据库相关的所有内容都存储在这种Page结构里
Page分为几种类型，常见的页类型有数据页(B-tree Node)、Undo页(Undo Log Page)系统页(System Page)、事务数据页(Transaction System Page)等

File Header : 文件头，描述页信息
Page Header : 页头，页的状态
Infimum + Supremum : 最大和最小记录，这是两个虚拟的行记录
User Records : 用户记录，存储数据记录
Free Space : 空间空间，页中还没有别使用的空间
Page Directory : 页目录，存储用户记录的先对位置
File Trailer : 文件尾，校验页是否完整

页结构整体上可以分为三大部分，分别为通用部分(文件头、文件尾)、存储记录空间、索引部分

• 通用部分(File Header & File Trailer)
  主要指文件头和文件尾，将页的内容进行封装，通过文件头和文件尾校验的CheckSum方式来确保页的传输是完整的
  其中比较重要的是在文件头中的FIL_PAGE_PREV和FIL_PAGE_NEXT字段，通过这两个字段，我们可以找到该页的上一页和下一页，实际上所有页通过两个字段可以形成一条双向链表
  
• 记录部分(User Records & Free Space)
  页的主要作用是存储记录，所以”最小和最大记录”和”用户记录”部分占了页结构的主要空间。另外空闲空间也是灵活的部分，当有新的记录插入时，会从空闲空间中进行分配用于存储新记录
  
• 索引部分(Page Directory)
  数据页中行记录按照主键由小到大顺序串联成一个单链表(页中记录是以单向链表形式进行存储的)，单链表的链表头尾最小记录，链表尾为最大记录。并且为了更快速地定位到指定的行记录，通过 Page Directory 实现目录的功能，借助 Page Directory使用二分法快速找到需要查找的行记录
  

InnoDB文件存储格式

show table status;
一般情况下，如果row_format为REDUNDANT、COMPACT，文件格式为Antelope；如果row_format为DYNAMIC和COMPRESSED，文件格式为Barracuda

• File文件格式(File-Format)
  在早期的InnoDB版本中，文件格式只有一种，随着InnoDB引擎的发展，出现了新文件格式，用于支持新的功能。目前InnoDB只支持两种文件格式：Antelope和Barracuda

  • Antelope : 先前未命名，最原始的InnoDB文件格式，它支持两种行格式：COMPACT和REDUNDANT，MySQL 5.6及以前版本默认格式为Antelope
  • Barracuda : 新的文件格式。它支持InnoDB的所有行格式，包括新的行格式：COMPRESSED和DYNAMIC

  通过innodb_file_format配置参数可以设置InnoDB文件格式，之前默认值为Antelope，5.7开始之后改为Barracuda

• Row行格式(Row_format)
  表的行格式决定了它的行是如何物理存储的，这反过来又影响查询和DML操作的性能。如果单个page页中容纳更多行，查询和检索查询可以更加快地工作，缓冲池中所需要的内存更少，写入更新时需要I/O更少
  InnoDB存储引擎支持四种行格式：Redundant、Compact、Dynamic和Compressed
  查询MySQL使用的行格式，默认为Dynamic
  
  指定语法格式 :
  

• COMPACT行记录格式
  compact设计目标是高效地存储数据，一个页中存放的行数据越多，其性能就越高
  compact行记录由两部分组成：记录放入额外信息和记录真实数据
  
  记录额外信息部分
  服务器为了描述一条记录而添加了一些额外信息(元数据信息)，这些额外信息分为3类，分别是：变量字段长度列表、NULL值列表和记录头信息

  • 变长字段长度列表：变长字段的长度是不固定的，所以存储数据的时候需要把这些数据占用的字节数也存起来，读取数据的时候才能根据这个长度列表读取对应长度的数据。在compact行格式中，把所有变长类型的列的长度都存放在记录的开头部分形成一个列表，按照列的顺序逆序存放，这个列表就是变长字段长度列表
  • NULL值列表：表中某些列可能会存储NULL 值，如果把这些NULL值都放到记录的真实数据中会比较浪费空间，所以Compact行格式把这些值为NULL的列存储到NULL列表中。(如果表中所有列都不允许为NULL，就不存储NULL值列表)
  • 记录头信息: 记录头信息是由固定的5个字节组成的，5个字节也就是40个二进制位，不同的位代表不同的意思
    

  记录真实数据部分
  记录的真是数据除了插入的那些列的数据，MySQL会为每个记录默认添加一些列(也称为隐藏列)，具体如下：
  

  Compact中的行溢出机制
  什么是溢出行?
  MySQL中是以页为基本单位，进行磁盘与内存之间的数据交互的，我们知道一个页的大小是16KB，16KB = 16384字节，而一个varchar(m)类型最多可以存储65532个字节，一些的数据类型，比如TEXT可以存储更多，如果一个表中存在这样的大字段，那么一个页就无法存储一条完整的记录，这时就会发生溢出，多出的数据就会存储在外部的溢出页中
  总结：如果某些字段信息过长，无法存储在B数节点中，这时候会被单独分配空间，此时被称为溢出页，该字段称为页外列

  Compact中的行溢出机制
  InnoDB规定一页至少存储两条记录(B+树特点)，如果页中只能存放一条记录，InnoDB存储引擎会自动将行数据存储到溢出页中
  当发生行溢出时，数据页只保存了前768字节的前缀数据，接着是20个字节的偏移量，指向行溢出页
  
  其他行记录格式
  DYNAMIC和COMPRESSED行记录格式新格式引入的功能有：数据压缩、增强型长列数据的页外存储和大索引前缀
  Compressed和Dynamic行记录与Compact行记录格式是类似的，区别是在行溢出时，数据页不会存储真是数据的前768字节(完全溢出)，只存储20个字节的指针来指向溢出页
  Compressed与Dynamic相比，Compressed存储的行数据会以zlib的算法进行压缩以节省空间，因此对于BLOB、TEXT、VARCHAR这类大长度类型的数据能够进行非常有效的存储
  MySQL 5.7默认的行存储格式是 Dynamic

• Redundant
  Redundant是MySQl 5.0版本之前InnoDB的行存储方式
  
  Redundant行记录格式的首部是一个字段长度偏移列表，同样是按照列顺序逆序放置的。该条记录中所有列(包含隐藏列、NULL值列表)的长度信息都按照逆序存储在字段长度偏移列表

13、InnoDB参数优化

Buffer Pool参数优化

缓冲池内存相关

一个大的日志缓冲区允许大量的事务在提交之前不写日志到磁盘。因此，如果你有很多事务的更新，插入或删除操作，通过设置设个参数会大量的减少磁盘IO的次数
建议：在专用的数据库服务器上，可以将缓冲池大小设置为服务器物理内存60%~80%

• 查看缓冲大小
  
• 在线调整InnoDB缓冲大小
  
• 监控现在调整缓冲池的进度
  

配置多个 buffer pool 实例

当buffer pool的大小是GB级别的时，将一个buffer pool分割成几个独立的实例能降低多个线程同时读写缓存页的竞争性而提高并发性
通过innodb_buffer_pool_instances 参数可以调整实例个数。如果有多个实例，则缓存的数据页会随机放置到任意的实例中，且每个实例都有独立的buffer pool所有的特性

buffer pool可以存放多个instance，每个instance由多个chunk组成。instance的数量范围和chunk的总数量范围分别为1-64，1-1000

• innodb_buffer_pool_instances 的默认值是1，最大可以调整为64

待更新。。。，写文章不易，如感兴趣点赞关注，谢谢！