1. 架构概括

MySQL - 图1

2. 运行机制

MySQL.jpg

  1. 建立连接：通过客户端/服务器通信协议与MySQL建立连接。MySQL 客户端与服务端的通信方式是 “ 半双工 ”。对于每一个 MySQL 的连接，时刻都有一个线程状态来标识这个连接正在做什么。
    1. 通讯机制
      1. 全双工：能同时发送和接收数据。
      2. 半双工：指的某一时刻，要么发送数据，要么接收数据，不能同时。
      3. 单工： 只能发送数据或只能接收数据。
    2. 线程状态：show processlist查看用户正在运行的线程信息，root用户能查看所有线程，其他用户只能看自己的

image.png

  1.   1. `Id`：线程ID，可以使用kill xx
  2.   1. `User`：启动这个线程的用户
  3.   1. `Host`：发送请求的客户端的IP和端口号
  4.   1. `db`：当前命令在哪个库执行
  5.   1. `Command`：该线程正在执行的操作命令
  6.      - Create DB：正在创建库操作
  7.      - Drop DB：正在删除库操作
  8.      - Execute：正在执行一个PreparedStatement
  9.      - Close Stmt：正在关闭一个PreparedStatement
  10.      - Query：正在执行一个语句
  11.      - Sleep：正在等待客户端发送语句
  12.      - Quit：正在退出
  13.      - Shutdown：正在关闭服务器
  14.   6. `Time`：表示该线程处于当前状态的时间，单位是秒
  15.   6. `State`：线程状态
  16.      - Updating：正在搜索匹配记录，进行修改
  17.      - Sleeping：正在等待客户端发送新请求
  18.      - Starting：正在执行请求处理
  19.      - Checking table：正在检查数据表
  20.      - Closing table : 正在将表中数据刷新到磁盘中
  21.      - Locked：被其他查询锁住了记录
  22.      - Sending Data：正在处理Select查询，同时将结果发送给客户端
  23.   8. `Info`：一般记录线程执行的语句，默认显示前100个字符。查看完整的使用`show full processlist`
  1. 查询缓存：这是MySQL的一个可优化查询的地方，如果开启了查询缓存且在查询缓存过程中查询到完全相同的SQL语句，则将查询结果直接返回给客户端；如果没有开启查询缓存或者没有查询到完全相同的 SQL 语句则会由解析器进行语法语义解析，并生成“解析树”。
    1. 缓存Select查询的结果和SQL语句
    2. 执行Select查询时，先查询缓存，判断是否存在可用的记录集，要求是否完全相同（包括参数值），这样才会匹配缓存数据命中。
    3. 即使开启查询缓存，以下SQL也不能缓存
      • 查询语句使用SQL_NO_CACHE
      • 查询的结果大于query_cache_limit设置
      • 查询中有一些不确定的参数，比如now()
    4. show variables like '%query_cache%'; 查看查询缓存是否启用，空间大小，限制等

image.png

  1. show status like 'Qcache%'; 查看更详细的缓存参数，可用缓存空间，缓存块，缓存多少等

image.png

  1. 解析器：将客户端发送的SQL进行语法解析，生成”解析树”。预处理器根据一些MySQL规则进一步检查“解析树”是否合法，例如这里将检查数据表和数据列是否存在，还会解析名字和别名，看看它们是否有歧义，最后生成新的“解析树”。
  2. 查询优化器：根据“解析树”生成最优的执行计划。MySQL使用很多优化策略生成最优的执行计划，可以分为两类：静态优化（编译时优化）、动态优化（运行时优化）。
    1. 等价变换策略
      • 5=5 and a>5 ==>a>5
      • a<b and a=5 ==>b>5 and a=5
      • 基于联合索引，调整条件位置等
    2. 优化count、min、max等函数
      • InnoDB引擎min函数只需要找索引最左边
      • InnoDB引擎max函数只需要找索引最右边
      • MyISAM引擎count(*)，不需要计算，直接返回
    3. 提前终止查询
      • 使用了limit查询，获取limit所需的数据，就不在继续遍历后面数据
    4. in 的优化
      • MySQL对in查询，会先进行排序，再采用二分法查找数据。比如where id in (2,1,3)，变成 in (1,2,3)
  3. 查询引擎执行SQL：此时查询执行引擎会根据 SQL 语句中表的存储引擎类型，以及对应的API接口与底层存储引擎缓存或者物理文件的交互，得到查询结果并返回给客户端。若开启用查询缓存，这时会将SQL 语句和结果完整地保存到查询缓存（Cache&Buffer）中，以后若有相同的 SQL 语句执行则直接返回结果。
    1. 如果开启了查询缓存，先将查询结果做缓存操作
    2. 返回结果过多，采用增量模式返回

      3. MySQL存储引擎

      存储引擎在MySQL的体系架构中位于第三层，负责MySQL中的数据的存储和提取，是与文件打交道的子系统，它是根据MySQL提供的文件访问层抽象接口定制的一种文件访问机制，这种机制就叫作存储引擎。
      使用show engines，查看当前数据库支持的存储引擎。
      image.png
      在5.5之前默认采用MyISAM引擎；5.5之后默认采用InnoDB引擎。（引擎和表绑定）

      3.1 InnoDB和MyISAM对比

      | | InnoDB | MyISAM | | —- | —- | —- | | 事务和外键 | 支持：有安全性和完整性，适合大量Insert和Update | 不支持 | | 锁机制 | 行级锁（基于索引加锁） | 表级锁 | | 索引结构 | 聚集索引，索引和记录在一起存储 | 非聚集索引，索引和记录分开存储 | | 并发能力 | 通过多版本并发控制（MVCC）来支持高并发 | 表锁，写操作按表排他，并发力低 | | 存储文件 | .frm表结构、.ibd数据 | .frm表结构、.MYD表数据、.MYI索引 |

3.2 InnoDB

image.png

3.2.1 内存结构

内存结构主要包括Buffer Pool、Change Buffer、Adaptive Hash Index和Log Buffer四大组件。

  • Buffer Pool： 缓冲池，简称BP。BP以Page页为单位，默认大小16K，BP的底层采用链表数据结构管理Page。在InnoDB访问表记录和索引时会在Page页中缓存，以后使用可以减少磁 盘IO操作，提升效率。
    • Page的三种类型
      • free page ： 空闲page，未被使用
      • clean page：被使用page，数据没有被修改过
      • dirty page：脏页，被使用page，数据被修改过，页中数据和磁盘的数据产生了不一致
    • Page的管理机制：针对上述三种page类型，InnoDB通过三种链表结构来维护和管理
      • free list ：表示空闲缓冲区，管理free page
      • flush list：表示需要刷新到磁盘的缓冲区，管理dirty page，内部page按修改时间 排序。脏页即存在于flush链表，也在LRU链表中，但是两种互不影响，LRU链表负责管理page的可用性和释放，而flush链表负责管理脏页的刷盘操作。
      • lru list：表示正在使用的缓冲区，管理clean page和dirty page，缓冲区以 midpoint为基点，前面链表称为new列表区，存放经常访问的数据，占63%；后 面的链表称为old列表区，存放使用较少数据，占37%。
    • 改进型LRU算法维护

每当有新的page数据读取到buffer pool时，InnoDb引擎会判断是否有空闲页，是否足够，如果有就将free page从free list列表删除，放入到LRU列表中。没有空闲页，就会根据LRU算法淘汰LRU链表默认的页，将内存空间释放分配给新的页。 

  1.   - 普通LRU：末尾淘汰法，新数据从链表头部加入，释放空间时从末尾淘汰  
  2.   - 改进LRU：链表分为new和old两个部分，加入元素时并不是从表头插入，而是从中间 midpoint位置插入，如果数据很快被访问，那么page就会向new列表头部移动，如果数据没有被访问，会逐步向old尾部移动，等待淘汰。
  • Buffer Pool配置参数
    • **show variables like '%innodb_page_size%'; **//查看page页大小
    • **show variables like '%innodb_old%'; **//查看lru list中old列表参数
    • **show variables like '%innodb_buffer%';** //查看buffer pool参数
    • 建议：将innodb_buffer_pool_size设置为总内存大小的60%-80%，nnodb_buffer_pool_instances可以设置为多个，这样可以避免缓存争夺。
    • Change Buffer：写缓冲区，简称CB。在进行DML操作时，如果BP没有其相应的Page数据，并不会立刻将磁盘页加载到缓冲池，而是在CB记录缓冲变更，等未来数据被读取时，再将数据合并恢复到BP中。

ChangeBuffer占用BufferPool空间，默认占25%，最大允许占50%，可以根据读写业务量来 进行调整。参数innodb_change_buffer_max_size;
image.png
当更新一条记录时，该记录在BufferPool存在，直接在BufferPool修改，一次内存操作。如果该记录在BufferPool不存在（没有命中），会直接在ChangeBuffer进行一次内存操作，不用再去磁盘查询数据，避免一次磁盘IO。当下次查询记录时，会先进性磁盘读取，然后再从 ChangeBuffer中读取信息合并，最终载入BufferPool中。
写缓冲区，仅适用于非唯一普通索引页，为什么？
如果在索引设置唯一性，在进行修改时，InnoDB必须要做唯一性校验，因此必须查询磁盘， 做一次IO操作。会直接将记录查询到BufferPool中，然后在缓冲池修改，不会在 ChangeBuffer操作。

  • Adaptive Hash Index：自适应哈希索引，用于优化对BP数据的查询。InnoDB存储引擎会监 控对表索引的查找，如果观察到建立哈希索引可以带来速度的提升，则建立哈希索引，所以称之为自适应。InnoDB存储引擎会自动根据访问的频率和模式来为某些页建立哈希索引。
  • Log Buffer：日志缓冲区，用来保存要写入磁盘上log文件（Redo/Undo）的数据，日志缓冲区的内容定期刷新到磁盘log文件中。日志缓冲区满时会自动将其刷新到磁盘，当遇到BLOB 或多行更新的大事务操作时，增加日志缓冲区可以节省磁盘I/O。

LogBuffer主要是用于记录InnoDB引擎日志，在DML操作时会产生Redo和Undo日志。LogBuffer空间满了，会自动写入磁盘。可以通过将innodb_log_buffer_size参数调大，减少磁盘IO频率 。
innodb_flush_log_at_trx_commit参数控制日志刷新行为，默认为1

  • 0 ：每隔1秒写日志文件和刷盘操作（写日志文件LogBuffer—>OS cache，刷盘OS cache—>磁盘文件），最多丢失1秒数据
  • 1：事务提交，立刻写日志文件和刷盘，数据不丢失，但是会频繁IO操作
  • 2：事务提交，立刻写日志文件，每隔1秒钟进行刷盘操作

    3.2.2 磁盘结构

    InnoDB磁盘主要包含Tablespaces，InnoDB Data Dictionary，Doublewrite Buffer、Redo Log和Undo Logs。

    表空间（Tablespaces）

    用于存储表结构和数据。表空间又分为系统表空间、独立表空间、 通用表空间、临时表空间、Undo表空间等多种类型；
  • 系统表空间（The System Tablespace）

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

  • 独立表空间（File-Per-Table Tablespaces）

默认开启（innodb_file_per_table=on），独立表空间是一个单表表空间，该表创建于自己的数据文件中，而非创建于系统表空间中。当innodb_file_per_table选项开启时，表将被创建于表空间中。否则，innodb将被创建于系统表空间中。每个表文件表空间由一个.ibd数据文件代表，该文件 默认被创建于数据库目录中。表空间的表文件支持动态（dynamic）和压缩 （commpressed）行格式。

  • 通用表空间（General Tablespaces）

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

  • 撤销表空间（Undo Tablespaces）

撤销表空间由一个或多个包含Undo日志文件组成。在MySQL 5.7版本之前Undo占用的 是System Tablespace共享区，从5.7开始将Undo从System Tablespace分离了出来。 InnoDB使用的undo表空间由innodb_undo_tablespaces配置选项控制，默认为0。参 数值为0表示使用系统表空间ibdata1;大于0表示使用undo表空间undo_001、 undo_002等。
image.png

  • 临时表空间（Temporary Tablespaces）

分为session temporary tablespaces 和global temporary tablespace两种。session temporary tablespaces 存储的是用户创建的临时表和磁盘内部的临时表。global temporary tablespace储存用户临时表的回滚段（rollback segments ）。mysql服务 器正常关闭或异常终止时，临时表空间将被移除，每次启动时会被重新创建。

数据字典（InnoDB Data Dictionary）

InnoDB数据字典由内部系统表组成，位于系统表空间，这些表包含用于查找表、索引和表字段等对象的元数 据。元数据物理上位于InnoDB系统表空间中。由于历史原因，数据字典元数据在一定程度上 与InnoDB表元数据文件（.frm文件）中存储的信息重叠。

双写缓冲区（Doublewrite Buffer）

位于系统表空间，是一个存储区域。在BufferPage的page页刷新到磁盘真正的位置前，会先 将数据存在Doublewrite 缓冲区。如果在page页写入过程中出现操作系统、存储子系统或 mysqld进程崩溃，InnoDB可以在崩溃恢复期间从Doublewrite 缓冲区中找到页面的一个好 备份。在大多数情况下，默认情况下启用双写缓冲区，要禁用Doublewrite 缓冲区，可以将 innodb_doublewrite设置为0。使用Doublewrite 缓冲区时建议将innodb_flush_method设 置为O_DIRECT。
image.png

MySQL的innodb_flush_method这个参数控制着innodb数据文件及redo log的打开、 刷写模式。有三个值：fdatasync(默认)，O_DSYNC，O_DIRECT。设置O_DIRECT表示 数据文件写入操作会通知操作系统不要缓存数据，也不要用预读，直接从Innodb Buffer写到磁盘文件。 默认的fdatasync意思是先写入操作系统缓存，然后再调用fsync()函数去异步刷数据文 件与redo log的缓存信息。

撤销日志（Undo Logs）

撤消日志是在事务开始之前保存的被修改数据的备份，用于例外情况时回滚事务。撤消日志属于逻辑日志，根据每行记录进行记录。撤消日志存在于系统表空间、撤消表空间和临时表空间中。

重做日志（Redo Log）

重做日志是一种基于磁盘的数据结构，用于在崩溃恢复期间更正不完整事务写入的数据。 MySQL以循环方式写入重做日志文件，记录InnoDB中所有对Buffer Pool修改的日志。当出现实例故障（像断电），导致数据未能更新到数据文件，则数据库重启时须redo，重新把数 据更新到数据文件。读写事务在执行的过程中，都会不断的产生redo log。默认情况下，重做日志在磁盘上由两个名为ib_logfile0和ib_logfile1的文件物理表示。

3.2.3 线程模型

InnoDB共有4种线程，分别是：MasterThread、IOThread、PurgeThread、PageCleanerThread。

IOThread

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

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

  • insert buffer thread ：负责将写缓冲内容刷新到磁盘。1个

    PurgeThread

    事务提交之后，其使用的undo日志将不再需要，因此需要Purge Thread回收已经分配的undo页。
    大于0，启用（默认）。
    image.png

    PageCleanerThread

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

    MasterThread

    Master thread是InnoDB的主线程，负责调度其他各线程，优先级最高。作用是将缓冲池中的数据异步刷新到磁盘 ，保证数据的一致性。包含：脏页的刷新（page cleaner thread）、undo页回收（purge thread）、redo日志刷新（log thread）、合并写缓冲等。内部有两个主处理，分别是每隔1秒和10秒处理。

  • 每1秒的操作：

    • 刷新日志缓冲区，刷到磁盘
    • 合并写缓冲区数据，根据IO读写压力来决定是否操作
    • 刷新脏页数据到磁盘，根据脏页比例达到75%才操作（innodb_max_dirty_pages_pct， innodb_io_capacity）
    • image.png
  • 每10秒的操作：
    • 刷新脏页数据到磁盘
    • 合并写缓冲区数据
    • 刷新日志缓冲区
    • 删除无用的undo页

image.png

3.2.4 数据文件

数据文件的存储结构：

分为一个ibd数据文件—>Segment（段）—>Extent（区）—>Page（页）—>Row（行）
image.png

  • Tablesapce：

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

  • Segment：

段，用于管理多个Extent，分为数据段（Leaf node segment）、索引段（Non-leaf node segment）、回滚段（Rollback segment）。一个表至少会有两个segment，一个管理数据，一个管理索引。每多创建一个索引，会多两个segment。

  • Extent：

区，一个区固定包含64个连续的页，大小为1M。当表空间不足，需要分配新的页资源，不会 一页一页分，直接分配一个区。

  • Page：

页，用于存储多个Row行记录，大小为16K。包含很多种页类型，比如数据页，undo页，系 统页，事务数据页，大的BLOB对象页。
Page是文件最基本的单位，无论何种类型的page，都是由page header，page trailer和page body组成。
image.png

  • Row：

行，包含了记录的字段值，事务ID（Trx id）、滚动指针（Roll pointer）、字段指针（Field pointers）等信息。

文件格式（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。
image.png

行格式（Row_format）

表的行格式决定了它的行是如何物理存储的，这反过来又会影响查询和DML操作的性能。如果在单个page页中容纳更多行，查询和索引查找可以更快地工作，缓冲池中所需的内存更少，写入更新时所需的I/O更少。
InnoDB存储引擎支持四种行格式：REDUNDANT、COMPACT、DYNAMIC和COMPRESSED。

Row Format Compact Storeage Characteristics Enhanced Variable-Length Column Storage Large Index Key Prefix Support Compression Support Supported Teblespace Types Required File Format
REDUNDANT NO NO NO NO system,
file-per-table		 Antelope or Barracude
COMPACT YES NO NO NO system,
file-per-table		 Antelope or Barracude
DYNAMIC YES YES YES NO file-per-table Barracude
COMPRESSED YES YES YES YES file-per-table Barracude

DYNAMIC和COMPRESSED新格式引入的功能有：数据压缩、增强型长列数据的页外存储和大索引前缀。
每个表的数据分成若干页来存储，每个页中采用B树结构存储；
如果某些字段信息过长，无法存储在B树节点中，这时候会被单独分配空间，此时被称为溢出页， 该字段被称为页外列。

  • REDUNDANT 行格式

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

  • COMPACT 行格式

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

  • DYNAMIC 行格式

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

  • COMPRESSED 行格式

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

3.2.5 Undo Log

介绍

  • Undo Log：数据库事务开始之前，会将要修改的记录存放到 Undo 日志里，当事务回滚时或者数 据库崩溃时，可以利用 Undo 日志，撤销未提交事务对数据库产生的影响。
  • Undo Log产生和销毁：Undo Log在事务开始前产生；事务在提交时，并不会立刻删除undo log，innodb会将该事务对应的undo log放入到删除列表中，后面会通过后台线程purge thread进行回收处理。Undo Log属于逻辑日志，记录一个变化过程。例如执行一个delete，undolog会记 录一个insert；执行一个update，undolog会记录一个相反的update。
  • Undo Log存储：undo log采用段的方式管理和记录。在innodb数据文件中包含一种rollback segment回滚段，内部包含1024个undo log segment。可以通过下面一组参数来控制Undo log存储。

image.png

作用

  • 实现事务的原子性

Undo Log 是为了实现事务的原子性而出现的产物。事务处理过程中，如果出现了错误或者用户执行了 ROLLBACK 语句，MySQL 可以利用 Undo Log 中的备份将数据恢复到事务开始之前的状态。

  • 实现多版本并发控制（MVCC）

Undo Log 在 MySQL InnoDB 存储引擎中用来实现多版本并发控制。事务未提交之前，Undo Log 保存了未提交之前的版本数据，Undo Log 中的数据可作为数据旧版本快照供其他并发事务进行快照读。
image.png
事务A手动开启事务，执行更新操作，首先会把更新命中的数据备份到 Undo Buffer 中。
事务B手动开启事务，执行查询操作，会读取 Undo 日志数据返回，进行快照读 。

3.2.6 Redo Log 和 Bin Log

RedoLog

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

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

    原理

    Redo Log 是为了实现事务的持久性而出现的产物。防止在发生故障的时间点，尚有脏页未写入表的 IBD 文件中，在重启 MySQL 服务的时候，根据 Redo Log 进行重做，从而达到事务的未入磁盘数据进行持久化这一特性。
    image.png

    写入机制

    Redo Log 文件内容是以顺序循环的方式写入文件，写满时则回溯到第一个文件，进行覆盖写。

  • write pos 是当前记录的位置，一边写一边后移，写到最后一个文件末尾后就回到 0 号文件开头；

  • checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件；

write pos 和 checkpoint 之间还空着的部分，可以用来记录新的操作。如果 write pos 追上 checkpoint，表示写满，这时候不能再执行新的更新，得停下来先擦掉一些记录，把 checkpoint 推进一下。

相关配置参数

每个InnoDB存储引擎至少有1个重做日志文件组（group），每个文件组至少有2个重做日志文件，默认为ib_logfile0和ib_logfile1。
可以通过下面一组参数控制Redo Log存储：
image.png
Redo Buffer 持久化到 Redo Log 的策略，可通过 Innodb_flush_log_at_trx_commit 设置：

  • 0：每秒提交 Redo buffer ->OS cache -> flush cache to disk，可能丢失一秒内的事务数 据。由后台Master线程每隔 1秒执行一次操作。
  • 1 (默认) ：每次事务提交执行 Redo Buffer -> OS cache -> flush cache to disk，最安全，性能最差的方式。

  • 2：每次事务提交执行 Redo Buffer -> OS cache，然后由后台Master线程再每隔1秒执行OS cache -> flush cache to disk 的操作。

    一般建议选择取值2，因为 MySQL 挂了数据没有损失，整个服务器挂了才会损失1秒的事务提交数据。
    image.png

    BinLog

    Binlog是引擎插件上层的功能，事务提交后，第一个就会调用Binlog功能接口，然后再调用其他存储引擎的功能接口。因此先写Binlog，然后才执行Innodb的Redolog和Undolog和脏页刷新操作。

    记录模式

    Redo Log 是属于InnoDB引擎所特有的日志，而MySQL Server也有自己的日志，即 Binary log（二进制日志），简称Binlog。Binlog是记录所有数据库表结构变更以及表数据修改的二进制日志，不会记录SELECT和SHOW这类操作。Binlog日志是以事件形式记录，还包含语句所执行的消耗时间。Binlog日志默认是关闭的，开启Binlog日志有以下两个最重要的使用场景。

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

  • 数据恢复：通过mysqlbinlog工具来恢复数据。

Binlog文件名默认为“主机名_binlog-序列号”格式，例如oak_binlog-000001，也可以在配置文件 中指定名称。文件记录模式有STATEMENT、ROW和MIXED三种，具体含义如下。

  • ROW（row-based replication, RBR）：日志中会记录每一行数据被修改的情况，然后在 slave端对相同的数据进行修改。
    • 优点：能清楚记录每一个行数据的修改细节，能完全实现主从数据同步和数据的恢复。
    • 缺点：批量操作，会产生大量的日志，尤其是alter table会让日志暴涨。
  • STATMENT（statement-based replication, SBR）：每一条被修改数据的SQL都会记录到 master的Binlog中，slave在复制的时候SQL进程会解析成和原来master端执行过的相同的 SQL再次执行。简称SQL语句复制。
    • 优点：日志量小，减少磁盘IO，提升存储和恢复速度
    • 缺点：在某些情况下会导致主从数据不一致，比如last_insert_id()、now()等函数。

  • MIXED（mixed-based replication, MBR）：以上两种模式的混合使用，一般会使用 STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存 binlog，MySQL会根据执行的SQL语句选择写入模式。

    文件结构

    MySQL的binlog文件中记录的是对数据库的各种修改操作，用来表示修改操作的数据结构是Log event。不同的修改操作对应的不同的log event。比较常用的log event有：Query event、Row event、Xid event等。binlog文件的内容就是各种Log event的集合。
    Binlog文件中Log event结构如下图所示：
    image.png

    写入机制

  • 根据记录模式和操作触发event事件生成log event（事件触发执行机制）

  • 将事务执行过程中产生log event写入缓冲区，每个事务线程都有一个缓冲区 Log Event保存在一个binlog_cache_mngr数据结构中，在该结构中有两个缓冲区，一个是 stmt_cache，用于存放不支持事务的信息；另一个是trx_cache，用于存放支持事务的信息。

  • 事务在提交阶段会将产生的log event写入到外部binlog文件中。不同事务以串行方式将log event写入binlog文件中，所以一个事务包含的log event信息在 binlog文件中是连续的，中间不会插入其他事务的log event。

    文件操作（恢复）

  • Binlog状态查看

image.png

  • 开启Binlog功能 （docker）
    1. 在挂载目录中设置参数

image.png

  1. 给日志路径/var/lib/mysql/mysql-bin.log设置权限：

chown -R mysql:mysql /var/lib/mysql 这里的路径是docker内部的权限。

  1. 重启容器
  2. 再次查看Binlog状态

image.png

  • 使用show binlog events命令

    1. show binary logs; //等价于show master logs;
    2. show master status;
    3. show binlog events;
    4. show binlog events in 'mysqlbinlog.000001';

    image.png

  • 使用mysqlbinlog 命令

image.png

  • 使用 binlog 恢复数据
    1. show binlog events in 'mysql-bin.000001';查看记录

image.png

  1. 从创建连接开始，到删库之前的pos结束。
    1. mysqlbinlog --no-defaults --start-position=1488 --stop-position=2222 /var/lib/mysql/mysql-bin.000001 | mysql -u root -p
  • 按时间恢复的命令
    1. mysqlbinlog --no-defaults --start-datetime="2020-04-25 18:00:00" --stop-datetime="2020-04-26 00:00:00" mysqlbinlog.000002 | mysql -u root -p

  • 删除Binlog文件

    1. purge binary logs to 'mysqlbinlog.000001'; //删除指定文件
    2. purge binary logs before '2020-04-28 00:00:00'; //删除指定时间之前的文件
    3. reset master; //清除所有文件

  • 设置Binlog的有效期

image.png

Redo Log和Binlog区别

  • Redo Log是属于InnoDB引擎功能，Binlog是属于MySQL Server自带功能，并且是以二进制文件记录。
  • Redo Log属于物理日志，记录该数据页更新状态内容，Binlog是逻辑日志，记录更新过程。
  • Redo Log日志是循环写，日志空间大小是固定，Binlog是追加写入，写完一个写下一个，不会覆盖使用。

  • Redo Log作为服务器异常宕机后事务数据自动恢复使用，Binlog可以作为主从复制和数据恢 复使用。Binlog没有自动crash-safe能力。

    4. 索引

    4.1 索引类型

    索引可以提升查询速度，会影响where查询，以及order by排序。MySQL索引类型如下：

  • 从索引存储结构划分：B Tree索引、Hash索引、FULLTEXT全文索引、R Tree索引

  • 从应用层次划分：普通索引、唯一索引、主键索引、复合索引
  • 从索引键值类型划分：主键索引、辅助索引（二级索引）

  • 从数据存储和索引键值逻辑关系划分：聚集索引（聚簇索引）、非聚集索引（非聚簇索引）

    4.1.1 普通索引

    这是最基本的索引类型，基于普通字段建立的索引，没有任何限制。
    创建普通索引的方法如下：

  • CREATE INDEX index_name ON tablename(字段名);

  • ALTER TABLE tablename ADD INDEX index_name (字段名);

  • CREATE TABLE tablename ( [...], INDEX [索引的名字] (字段名) );

    4.1.2 唯一索引

    与”普通索引”类似，不同的就是：索引字段的值必须唯一，但允许有空值 。在创建或修改表时追加唯一约束，就会自动创建对应的唯一索引。
    创建唯一索引的方法如下：

  • CREATE UNIQUE INDEX <索引的名字> ON tablename (字段名);

  • ALTER TABLE tablename ADD UNIQUE INDEX [索引的名字] (字段名);

  • CREATE TABLE tablename ( [...], UNIQUE [索引的名字] (字段名);

    4.1.3 主键索引

    它是一种特殊的唯一索引，不允许有空值。在创建或修改表时追加主键约束即可，每个表只能有一个主键。
    创建主键索引的方法如下：

  • CREATE TABLE tablename ( [...], PRIMARY KEY (字段名) );

  • ALTER TABLE tablename ADD PRIMARY KEY (字段名);

    4.1.4 复合索引

    单一索引是指索引列为一列的情况，即新建索引的语句只实施在一列上；用户可以在多个列上建立索 引，这种索引叫做组复合索引（组合索引）。复合索引可以代替多个单一索引，相比多个单一索引复合索引所需的开销更小。
    索引同时有两个概念叫做窄索引和宽索引，窄索引是指索引列为1-2列的索引，宽索引也就是索引列超 过2列的索引，设计索引的一个重要原则就是能用窄索引不用宽索引，因为窄索引往往比组合索引更有效。
    创建组合索引的方法如下：

  • CREATE INDEX <索引的名字> ON tablename (字段名1，字段名2...);

  • ALTER TABLE tablename ADD INDEX [索引的名字] (字段名1，字段名2...);
  • CREATE TABLE tablename ( [...], INDEX [索引的名字] (字段名1，字段名2...) );

复合索引使用注意事项：

  • 何时使用复合索引，要根据where条件建索引，注意不要过多使用索引，过多使用会对更新操作效率有很大影响。

  • 如果表已经建立了(col1，col2)，就没有必要再单独建立（col1）；如果现在有(col1)索引，如果查询需要col1和col2条件，可以建立(col1,col2)复合索引，对于查询有一定提高。

    4.1.5 全文索引

    查询操作在数据量比较少时，可以使用like模糊查询，但是对于大量的文本数据检索，效率很低。如果使用全文索引，查询速度会比like快很多倍。在MySQL 5.6 以前的版本，只有MyISAM存储引擎支持全 文索引，从MySQL 5.6开始MyISAM和InnoDB存储引擎均支持。
    创建全文索引的方法如下：

  • CREATE FULLTEXT INDEX <索引的名字> ON tablename (字段名);

  • ALTER TABLE tablename ADD FULLTEXT [索引的名字] (字段名);
  • CREATE TABLE tablename ( [...], FULLTEXT KEY [索引的名字] (字段名);

和常用的like模糊查询不同，全文索引有自己的语法格式，使用 match 和 against 关键字，比如：
select * from user where match(name) against('aaa');
全文索引使用注意事项：

  • 全文索引必须在字符串、文本字段上建立。
  • 全文索引字段值必须在最小字符和最大字符之间的才会有效。（innodb：3-84；myisam：4- 84）
    • 查看全文索引参数

image.png
只有字符串在3~84的时候，才会创建全文索引。

  • 全文索引字段值要进行切词处理，按syntax字符进行切割，例如b+aaa，切分成b和aaa。

  • 全文索引匹配查询，默认使用的是等值匹配，例如a匹配a，不会匹配ab,ac。如果想匹配可以在布尔模式下搜索a 。`select from user where match(name) against(‘a*’ in boolean mode);`

    4.2 索引原理

    4.3.1 聚簇索引和辅助索引

    聚簇索引和非聚簇索引：B+Tree的叶子节点存放主键索引值和行记录就属于聚簇索引；如果索引值和行记录分开存放就属于非聚簇索引。
    主键索引和辅助索引：B+Tree的叶子节点存放的是主键字段值就属于主键索引；如果存放的是非主键值就属于辅助索引（二级索引）。 在InnoDB引擎中，主键索引采用的就是聚簇索引结构存储。

    聚簇索引

    聚簇索引是一种数据存储方式，InnoDB的聚簇索引就是按照主键顺序构建 B+Tree结构。B+Tree 的叶子节点就是行记录，行记录和主键值紧凑地存储在一起。 这也意味着 InnoDB 的主键索引就 是数据表本身，它按主键顺序存放了整张表的数据，占用的空间就是整个表数据量的大小。通常说 的主键索引就是聚集索引。
    InnoDB的表要求必须要有聚簇索引：

  • 如果表定义了主键，则主键索引就是聚簇索引

  • 如果表没有定义主键，则第一个非空unique列作为聚簇索引

  • 否则InnoDB会从建一个隐藏的row-id作为聚簇索引

    辅助索引

    InnoDB辅助索引，也叫作二级索引，是根据索引列构建 B+Tree结构。但在 B+Tree 的叶子节点中 只存了索引列和主键的信息。二级索引占用的空间会比聚簇索引小很多， 通常创建辅助索引就是 为了提升查询效率。一个表InnoDB只能创建一个聚簇索引，但可以创建多个辅助索引。

    非聚簇索引

    与InnoDB表存储不同，MyISAM数据表的索引文件和数据文件是分开的，被称为非聚簇索引结构 。

    4.3 索引分析与优化

    4.3.1 EXPLAIN

    MySQL 提供了一个 EXPLAIN 命令，它可以对 SELECT 语句进行分析，并输出 SELECT 执行的详细信 息，供开发人员有针对性的优化。例如：
    EXPLAIN SELECT * from user WHERE id < 3;
    image.png

  • select_type ： 表示查询的类型

    • SIMPLE ： 表示查询语句不包含子查询或union
    • PRIMARY：表示此查询是最外层的查询
    • UNION：表示此查询是UNION的第二个或后续的查询
    • DEPENDENT UNION：UNION中的第二个或后续的查询语句，使用了外面查询结果
    • UNION RESULT：UNION的结果
    • SUBQUERY：SELECT子查询语句
    • DEPENDENT SUBQUERY：SELECT子查询语句依赖外层查询的结果。

    最常见的查询类型是SIMPLE，表示我们的查询没有子查询也没用到UNION查询。

  • type：表示存储引擎查询数据时采用的方式。比较重要的一个属性，通过它可以判断出查询是全表扫描还是基于索引的部分扫描。常用属性值如下，从上至下效率依次增强。

    • ALL：表示全表扫描，性能最差。
    • index：表示基于索引的全表扫描，先扫描索引再扫描全表数据。
    • range：表示使用索引范围查询。使用>、>=、<、<=、in等等。
    • ref：表示使用非唯一索引进行单值查询。
    • eq_ref：一般情况下出现在多表join查询，表示前面表的每一个记录，都只能匹配后面表的一 行结果。
    • const：表示使用主键或唯一索引做等值查询，常量查询。
    • NULL：表示不用访问表，速度最快。
  • possible_keys ： 表示查询时能够使用到的索引。注意并不一定会真正使用，显示的是索引名称。
  • key ： 表示查询时真正使用到的索引，显示的是索引名称。
  • rows： MySQL查询优化器会根据统计信息，估算SQL要查询到结果需要扫描多少行记录。原则上rows是 越少效率越高，可以直观的了解到SQL效率高低。
  • key_len：表示查询使用了索引的字节数量。可以判断是否全部使用了组合索引。

key_len的计算规则如下：

  • 字符串类型

字符串长度跟字符集有关：latin1=1、gbk=2、utf8=3、utf8mb4=4
char(n)：n字符集长度
varchar(n)：n 字符集长度 + 2字节

  • 数值类型

TINYINT：1个字节
SMALLINT：2个字节
MEDIUMINT：3个字节
INT、FLOAT：4个字节
BIGINT、DOUBLE：8个字节

  • 时间类型

DATE：3个字节
TIMESTAMP：4个字节
DATETIME：8个字节

  • 字段属性

NULL属性占用1个字节，如果一个字段设置了NOT NULL，则没有此项

  • Extra： Extra表示很多额外的信息，各种操作会在Extra提示相关信息，常见几种如下：

    • Using where ：表示查询需要通过索引回表查询数据。
    • Using index ： 表示查询需要通过索引，索引就可以满足所需数据。
    • Using filesort ： 表示查询出来的结果需要额外排序，数据量小在内存，大的话在磁盘，因此有Using filesort 建议优化。
    • Using temprorary ： 查询使用到了临时表，一般出现于去重、分组等操作。

      4.3.2 回表查询

      在之前介绍过，InnoDB索引有聚簇索引和辅助索引。聚簇索引的叶子节点存储行记录，InnoDB必须要 有，且只有一个。辅助索引的叶子节点存储的是主键值和索引字段值，通过辅助索引无法直接定位行记 录，通常情况下，需要扫描两遍索引树。先通过辅助索引定位主键值，然后再通过聚簇索引定位行记 录，这就叫做回表查询，它的性能比扫一遍索引树低。
      总结：通过索引查询主键值，然后再去聚簇索引查询记录信息

      4.3.3 覆盖索引

      explain的输出结果Extra字段为Using index时，能够触发索引覆盖。
      只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快，这就叫做索引覆盖。
      实现索引覆盖最常见的方法就是：将被查询的字段，建立到组合索引。

      4.3.4 最左前缀原则

      复合索引使用时遵循最左前缀原则，最左前缀顾名思义，就是最左优先，即查询中使用到最左边的列， 那么查询就会使用到索引，如果从索引的第二列开始查找，索引将失效 。
      image.png

      4.3.5 Like查询

      面试题：MySQL在使用like模糊查询时，索引能不能起作用？
      回答：MySQL在使用Like模糊查询时，索引是可以被使用的，只有把%字符写在后面才会使用到索引。
      select from user where name like ‘%o%’; //不起作用
      select       from user where name like ‘o%’; //起作用
      select * from user where name like ‘%o’; //不起作用

      4.3.6 NULL查询

      面试题：如果MySQL表的某一列含有NULL值，那么包含该列的索引是否有效？
      对MySQL来说，NULL是一个特殊的值，从概念上讲，NULL意味着“一个未知值”，它的处理方式与其他 值有些不同。比如：不能使用=，<，>这样的运算符，对NULL做算术运算的结果都是NULL，count时 不会包括NULL行等，NULL比空字符串需要更多的存储空间等。

      “NULL columns require additional space in the row to record whether their values are NULL. For MyISAM tables, each NULL column takes one bit extra, rounded up to the nearest byte.”

      虽然MySQL可以在含有NULL的列上使用索引，但NULL和其他数据还是有区别的，不建议列上允许为 NULL。最好设置NOT NULL，并给一个默认值，比如0和 ‘’ 空字符串等，如果是datetime类型，也可以 设置系统当前时间或某个固定的特殊值，例如’1970-01-01 00:00:00’。

      4.3.7 索引与排序

      MySQL查询支持filesort和index两种方式的排序，filesort是先把结果查出，然后在缓存或磁盘进行排序 操作，效率较低。使用index是指利用索引自动实现排序，不需另做排序操作，效率会比较高。
      filesort有两种排序算法：双路排序和单路排序。

  • 双路排序：需要两次磁盘扫描读取，最终得到用户数据。第一次将排序字段读取出来，然后排序；第二次去读取其他字段数据。

  • 单路排序：从磁盘查询所需的所有列数据，然后在内存排序将结果返回。如果查询数据超出缓存 sort_buffer，会导致多次磁盘读取操作，并创建临时表，最后产生了多次IO，反而会增加负担。
    • 解决方案：少使用select *；增加sort_buffer_size容量和max_length_for_sort_data容量。

如果我们Explain分析SQL，结果中Extra属性显示Using filesort，表示使用了filesort排序方式，需要优化。如果Extra属性显示Using index时，表示覆盖索引，也表示所有操作在索引上完成，也可以使用 index排序方式，建议大家尽可能采用覆盖索引。

  • 以下几种情况，会使用index方式的排序。
    • ORDER BY 子句索引列组合满足索引最左前列

explain select id from user order by id; //对应(id)、(id,name)索引有效

  • WHERE子句+ORDER BY子句索引列组合满足索引最左前列

explain select id from user where age=18 order by name; //对应 (age,name)索引

  • 以下几种情况，会使用filesort方式的排序。
    • 对索引列同时使用了ASC和DESC

explain select id from user order by age asc,name desc; //对应 (age,name)索引

  • WHERE子句和ORDER BY子句满足最左前缀，但where子句使用了范围查询（例如>、<、in 等）

explain select id from user where age>10 order by name; //对应 (age,name)索引

  • ORDER BY或者WHERE+ORDER BY索引列没有满足索引最左前列

explain select id from user order by name; //对应(age,name)索引

  • 使用了不同的索引，MySQL每次只采用一个索引，ORDER BY涉及了两个索引

explain select id from user order by name,age; //对应(name)、(age)两个索引

  • WHERE子句与ORDER BY子句，使用了不同的索引

explain select id from user where name='tom' order by age; //对应(name)、(age)索引

  • WHERE子句或者ORDER BY子句中索引列使用了表达式，包括函数表达式

explain select id from user order by abs(age); //对应(age)索引

4.4 查询优化

4.4.1 慢查询定位

开启慢查询日志

查看 MySQL 数据库是否开启了慢查询日志和慢查询日志文件的存储位置的命令如下：
image.png
通过如下命令开启慢查询日志： 

  1. SET global slow_query_log = ON;
  2. SET global slow_query_log_file = 'OAK-slow.log';
  3. SET global log_queries_not_using_indexes = ON;
  4. SET long_query_time = 10;
  • long_query_time：指定慢查询的阀值，单位秒。如果SQL执行时间超过阀值，就属于慢查询记录到日志文件中。

  • log_queries_not_using_indexes：表示会记录没有使用索引的查询SQL。前提是slow_query_log 的值为ON，否则不会奏效。

    查看慢查询日志

    文本方式查看

  • 直接使用文本编辑器打开slow.log日志即可。

image.png

  • time：日志记录的时间
  • User@Host：执行的用户及主机
  • Query_time：执行的时间
  • Lock_time：锁表时间
  • Rows_sent：发送给请求方的记录数，结果数量
  • Rows_examined：语句扫描的记录条数
  • SET timestamp：语句执行的时间点
  • select….：执行的具体的SQL语句
    • 使用mysqldumpslow查看

MySQL 提供了一个慢查询日志分析工具mysqldumpslow，可以通过该工具分析慢查询日志 内容。
运行如下命令查看慢查询日志信息：
perl mysqldumpslow.pl -t 5 -s at C:\ProgramData\MySQL\Data\OAK-slow.log
除了使用mysqldumpslow工具，也可以使用第三方分析工具，比如pt-query-digest、 mysqlsla等。

4.4.2 慢查询优化

索引和慢查询

  • 如何判断是否为慢查询？

MySQL判断一条语句是否为慢查询语句，主要依据SQL语句的执行时间，它把当前语句的执 行时间跟 long_query_time 参数做比较，如果语句的执行时间 > long_query_time，就会把 这条执行语句记录到慢查询日志里面。long_query_time 参数的默认值是 10s，该参数值可 以根据自己的业务需要进行调整。

  • 如何判断是否应用了索引？

SQL语句是否使用了索引，可根据SQL语句执行过程中有没有用到表的索引，可通过 explain 命令分析查看，检查结果中的 key 值，是否为NULL。

  • 应用了索引是否一定快？

下面我们来看看下面语句的 explain 的结果，你觉得这条语句有用上索引吗？比如
select * from user where id>0;
虽然使用了索引，但是还是从主键索引的最左边的叶节点开始向右扫描整个索引树，进行了 全表扫描，此时索引就失去了意义。
而像 select * from user where id = 2;这样的语句，才是我们平时说的使用了索引。它表示的意思是，我们使用了索引的快速搜索功能，并且有效地减少了扫描行数。
查询是否使用索引，只是表示一个SQL语句的执行过程；而是否为慢查询，是由它执行的时间决定 的，也就是说是否使用了索引和是否是慢查询两者之间没有必然的联系。
我们在使用索引时，不要只关注是否起作用，应该关心索引是否减少了查询扫描的数据行数，如果扫描行数减少了，效率才会得到提升。对于一个大表，不止要创建索引，还要考虑索引过滤性，过 滤性好，执行速度才会快。

提高索引过滤性

假如有一个5000万记录的用户表，通过sex=’男’索引过滤后，还需要定位3000万，SQL执行速度也 不会很快。其实这个问题涉及到索引的过滤性，比如1万条记录利用索引过滤后定位10条、100 条、1000条，那他们过滤性是不同的。索引过滤性与索引字段、表的数据量、表设计结构都有关系。
下面我们看一个案例： 

  1. 表：student
  2. 字段：id,name,sex,age
  3. 造数据：insert into student (name,sex,age) select name,sex,age from student;
  4. SQL案例：select * from student where age=18 and name like '张%';（全表扫描）
  • 优化1

alter table student add index(name); //追加name索引

  • 优化2

alter table student add index(age,name); //追加age,name索引

  • 优化3

可以看到，index condition pushdown 优化的效果还是很不错的。再进一步优化，我们可以把名 字的第一个字和年龄做一个联合索引，这里可以使用 MySQL 5.7 引入的虚拟列来实现。 

  1. # 为user表添加first_name虚拟列，以及联合索引(first_name,age)
  2. alter table student add first_name varchar(2) generated always as (left(name, 1)), add index(first_name, age);
  4. explain select * from student where first_name='张' and age=18;

慢查询原因总结

  • 全表扫描：explain分析type属性all
  • 全索引扫描：explain分析type属性index
  • 索引过滤性不好：靠索引字段选型、数据量和状态、表设计

  • 频繁的回表查询开销：尽量少用select *，使用覆盖索引

    4.4.3 分页查询优化

    一般性分页

    般的分页查询使用简单的 limit 子句就可以实现。limit格式如下：
    SELECT * FROM 表名 LIMIT [offset,] rows

  • 第一个参数指定第一个返回记录行的偏移量，注意从0开始；

  • 第二个参数指定返回记录行的最大数目；
  • 如果只给定一个参数，它表示返回最大的记录行数目；

思考1：如果偏移量固定，返回记录量对执行时间有什么影响？

  1. select * from user limit 10000,1;
  2. select * from user limit 10000,10;
  3. select * from user limit 10000,100;
  4. select * from user limit 10000,1000;
  5. select * from user limit 10000,10000;

结果：在查询记录时，返回记录量低于100条，查询时间基本没有变化，差距不大。随着查询记录 量越大，所花费的时间也会越来越多。
思考2：如果查询偏移量变化，返回记录数固定对执行时间有什么影响？

  1. select * from user limit 1,100;
  2. select * from user limit 10,100;
  3. select * from user limit 100,100;
  4. select * from user limit 1000,100;
  5. select * from user limit 10000,100;

结果：在查询记录时，如果查询记录量相同，偏移量超过100后就开始随着偏移量增大，查询时间 急剧的增加。（这种分页查询机制，每次都会从数据库第一条记录开始扫描，越往后查询越慢，而 且查询的数据越多，也会拖慢总查询速度。）

分页优化方案

  • 利用覆盖索引优化

    1. select * from user limit 10000,100;
    2. select id from user limit 10000,100;

  • 利用子查询优化 

    1. select * from user limit 10000,100;
    2. select * from user where id>= (select id from user limit 10000,1) limit 100;

    原因：使用了id做主键比较(id>=)，并且子查询使用了覆盖索引进行优化。

    4.5 事务和锁

    4.5.1 ACID

    4.5.2 事务的控制

    MVCC的实现机制：

  • MVCC只在 Read Commited 和 Repeatable Read 两种隔离级别下工作；

  • 读操作可以分为两类: 快照读（Snapshot Read）与当前读 （Current Read）；
    • 快照读：读取的是记录的快照版本（有可能是历史版本），不用加锁。（select）
    • 当前读：读取的是记录的最新版本，并且当前读返回的记录，都会加锁，保证其他事务不会再并发 修改这条记录。（select… for update 或lock in share mode，insert/delete/update）

MVCC的流程：

  1. 会给每条记录加上三个字段：
    1. DB_ROW_ID：用于回滚指针指向的id；
    2. DB_TRX_ID：事务号
    3. DB_ROLL_PT：回滚指针，指向别的DB_ROW_ID
  2. 事务a来修改该行记录
  3. 排他锁，锁定该行
  4. 记录RedoLog日志
  5. 把该行复制到UndoLog
  6. 修改该行的值，并记录DB_TRX_ID事务号，并且使DB_ROLL_PT指向UndoLog中的该条记录

MVCC经实现了读读、读写、写读并发处理。

4.5.3 事务的隔离级别

并发的问题和隔离级别的关系

回滚覆盖 脏读 不可重复读 幻读
读未提交 x 可能发生 可能发生 可能发生
读已提交 x x 可能发生 可能发生
可重复读 x x x 可能发生
串行化 x x x x

脏读：读到未提交的数据
不可重复读：前后读的数据不一致
幻读：按相同条件查询，结果不一致

MySQL默认隔离级别：可重复读
Oracle、SQLServer默认隔离级别：读已提交
查询事务的隔离级别： show variables like 'tx_isolation';
设置事务的隔离级别：

  1. set tx_isolation='READ-UNCOMMITTED';
  2. set tx_isolation='READ-COMMITTED';
  3. set tx_isolation='REPEATABLE-READ';
  4. set tx_isolation='SERIALIZABLE';

4.5.4 锁的机制

4.5.4.1 InnoDB的七种锁

共享锁/排他锁

InnoDB实现了两种标准的行级锁：共享锁（简称S锁）、排他锁（简称X锁）。

  • 共享锁：简称为S锁，在事务要读取一条记录时，需要先获取该记录的S锁。
  • 排他锁：简称X锁，在事务需要改动一条记录时，需要先获取该记录的X锁。

如果事务T1持有行R的S锁，那么另一个事务T2请求访问这条记录时，会做如下处理：

  • T2 请求S锁立即被允许，结果 T1和T2都持有R行的S锁
  • T2 请求X锁不能被立即允许,此操作会阻塞

如果T1持有行R的X锁，那么T2请求R的X、S锁都不能被立即允许，T2 必须等待T1释放X锁才可以，因为X锁与任何的锁都不兼容。

意向锁

为什么会有意向锁？
首先意向锁是表级锁。
因为InnoDB是支持表锁和行锁共存的，如果一个事务A获取到某一行的排他锁，并未提交，这时候事务B请求获取同一个表的表共享锁。因为共享锁和排他锁是互斥的，因此事务B想对这个表加共享锁时，需要保证没有其他事务持有这个表的表排他锁，同时还要保证没有其他事务持有表中任意一行的排他锁。
然后问题来了，你要保证没有其他事务持有表中任意一行的排他锁的话，去遍历每一行？这样显然是一个效率很差的做法。
意向锁分为两类：

  • 意向共享锁：简称IS锁，当事务准备在某些记录上加S锁时，需要现在表级别加一个IS锁。
  • 意向排他锁：简称IX锁，当事务准备在某条记录上加上X锁时，需要现在表级别加一个IX锁。

比如：

  • select ... lock in share mode，要给表设置IS锁;
  • select ... for update，要给表设置IX锁;

意向锁又是如何解决这个效率低的问题呢：
如果一个事务A获取到某一行的排他锁，并未提交,这时候表上就有意向排他锁和这一行的排他锁。这时候事务B想要获取这个表的共享锁，此时因为检测到事务A持有了表的意向排他锁，因此事务A必然持有某些行的排他锁，也就是说事务B对表的加锁请求需要阻塞等待，不再需要去检测表的每一行数据是否存在排他锁。
意向锁仅仅表明意向的锁，意向锁之间并不会互斥，是可以并行的。

记录锁（Record Lock）

记录锁是最简单的行锁，仅仅锁住一行。如：SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE，如果c1字段是主键或者是唯一索引的话，这个SQL会加一个记录锁（Record Lock）
记录锁永远都是加在索引上的，即使一个表没有索引，InnoDB也会隐式的创建一个索引，并使用这个索引实施记录锁。它会阻塞其他事务对这行记录的插入、更新、删除。
一般我们看死锁日志时，都是找关键词，比如lock_mode X locks rec but not gap，就表示一个X型的记录锁。记录锁的关键词就是rec but not gap。

间隙锁（Gap Lock）

为了解决幻读问题，InnoDB引入了间隙锁(Gap Lock)。间隙锁是一种加在两个索引之间的锁，或者加在第一个索引之前，或最后一个索引之后的间隙。它锁住的是一个区间，而不仅仅是这个区间中的每一条数据。
比如lock_mode X locks gap before rec表示X型gap锁。

临键锁（Next-Key Lock）

Next-key锁是记录锁和间隙锁的组合，它指的是加在某条记录以及这条记录前面间隙上的锁。说得更具体一点就是:临键锁会封锁索引记录本身，以及索引记录之前的区间，即它的锁区间是前开后闭，比如(5,10]。
如果一个会话占有了索引记录R的共享/排他锁，其他会话不能立刻在R之前的区间插入新的索引记录。

插入意向锁

插入意向锁,是插入一行记录操作之前设置的一种间隙锁。这个锁释放了一种插入方式的信号。它解决的问题是：多个事务，在同一个索引，同一个范围区间插入记录时，如果插入的位置不冲突，就不会阻塞彼此。
假设有索引值4、7，几个不同的事务准备插入5、6，每个锁都在获得插入行的独占锁之前用插入意向锁各自锁住了4、7之间的间隙，但是不阻塞对方因为插入行不冲突。

自增锁

自增锁是一种特殊的表级别锁。它是专门针对AUTO_INCREMENT类型的列，对于这种列，如果表中新增数据时就会去持有自增锁。简言之，如果一个事务正在往表中插入记录，所有其他事务的插入必须等待，以便第一个事务插入的行，是连续的主键值。
通过show variables like '%innodb_autoinc_lock_mode%';显示自增模式，有三种。

  • 0：表示传统锁模式，使用表级AUTO_INC锁。一个事务的INSERT-LIKE语句在语句执行结束后释放AUTO_INC表级锁，而不是在事务结束后释放。
  • 1: 连续锁模式,连续锁模式对于Simple inserts不会使用表级锁，而是使用一个轻量级锁来生成自增值，因为InnoDB可以提前知道插入多少行数据。自增值生成阶段使用轻量级互斥锁来生成所有的值，而不是一直加锁直到插入完成。对于bulk inserts类语句使用AUTO_INC表级锁直到语句完成。
  • 2:交错锁模式,所有的INSERT-LIKE语句都不使用表级锁，而是使用轻量级互斥锁。
    • INSERT-LIKE:指所有的插入语句，包括：INSERT、REPLACE、INSERT…SELECT、REPLACE…SELECT,LOAD DATA等。
    • Simple inserts:指在插入前就能确定插入行数的语句，包括：INSERT、REPLACE，不包含INSERT…ON DUPLICATE KEY UPDATE这类语句。
    • Bulk inserts: 指在插入钱不能确定行数的语句，包括：INSERT … SELECT/REPLACE … SELECT/LOAD DATA。

4.5.4.2 锁

RC隔离级别

查询条件是主键
对查询条件是主键id的场景，会加一个排他锁（X锁），或者说加一个X型的记录锁。
查询条件是唯一索引
id是唯一索引，name是主键的场景下，我们给定SQL：delete from t2 where id = 6;。
在RC隔离级别下，该SQL需要加两个X锁，一个对应于id 唯一索引上的id = 6的记录，另一把锁对应于聚簇索引上的[name=’b’,id=6]的记录。

为什么主键索引上的记录也要加锁呢？ 如果并发的一个SQL，是通过主键索引来更新：update t2 set id = 666 where name = ‘b’;此时，如果delete语句没有将主键索引上的记录加锁，那么并发的update就会感知不到delete语句的存在，违背了同一记录上的更新/删除需要串行执行的约束。

查询条件是普通索引
若id列是普通索引，那么对应的所有满足SQL查询条件的记录，都会加上锁。同时，这些记录对应主键索引，也会上锁。
查询条件上没有索引
若id列上没有索引，MySQL会走聚簇索引进行全表扫描过滤。每条记录都会加上X锁。但是，为了效率考虑，MySQL在这方面进行了改进，在扫描过程中，若记录不满足过滤条件，会进行解锁操作。

RR隔离级别

查询条件是主键
给定SQL：delete from t1 where id = 6;，如果id是主键的话，在RR隔离级别下，跟RC隔离级别，加锁是一样的，也都是在id = 6这条记录上加上X锁。
查询条件是唯一索引
给定SQL：delete from t1 where id = 6;，如果id是唯一索引的话，在RR隔离级别下，跟RC隔离级别，加锁也是一样的哈，加了两个X锁，id唯一索引满足条件的记录上一个，对应的主键索引上的记录一个。
查询条件是普通索引
如果查询条件是普通的二级索引，在RR（可重复读的隔离级别下），除了会加X锁，还会加间隙Gap锁。Gap锁的提出，是为了解决幻读问题引入的，它是一种加在两个索引之间的锁。
查询条件上没有索引
如果查询条件列没有索引，主键索引的所有记录，都将加上X锁，每条记录间也都加上间隙Gap锁。大家可以想象一下，任何加锁并发的SQL，都是不能执行的，全表都是锁死的状态。如果表的数据量大，那效率就更低。

在这种情况下，MySQL做了一些优化，即semi-consistent read，对于不满足条件的记录，MySQL提前释放锁，同时Gap锁也会释放。而semi-consistent read是如何触发的呢：要么在Read Committed隔离级别下；要么在Repeatable Read隔离级别下，设置了innodb_locks_unsafe_for_binlog参数。但是semi-consistent read本身也会带来其他的问题，不建议使用。

RR级别下的加锁规则

对于RC隔离级别，加的排他锁（X锁），是比较好理解的，哪里更新就锁哪里嘛。但是RR隔离级别，间隙锁是怎么加的呢？我们一起来学习一下。
对InnoDb的锁来说，面试的时候问的比较多，就是Record lock、Gap lock、Next-key lock。接下来我们来学习，RR隔离级别，到底一个锁是怎么加上去的。
首先MySQL的版本，是5.x 系列 <=5.7.24，8.0 系列 <=8.0.13。加锁规则一共包括：两个原则、两个优化和一个bug。

  • 原则1：加锁的基本单位都是next-key lock。next-key lock（临键锁）是前开后闭区间。
  • 原则2：查找过程中访问到的对象才会加锁。
  • 优化1：索引上的等值查询，给唯一索引加锁的时候，next-key lock退化为行锁（Record lock）。
  • 优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock退化为间隙锁（Gap lock）。
  • 一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

案例分析
有如下表：

id（唯一索引） name（普通索引）
0 0
5 5
10 10
15 15
20 20
  1. 等值查询间隙锁

update table set XXX where id = 6;

  1. 首先，加临建锁(5,10]
  2. 然后，因为id=6等值查询，根据优化2，所以临建锁退化为间隙锁(5,10)
    1. 非唯一索引等值锁
    2. 主键索引范围锁

select * from table where id>=10 and id<11 for update;

  1. 首先，id=10，主键等值查询，根据优化1， 会由(5,10]的临建锁转化为10的行锁
  2. 其次，id>10,id<11 加临建锁(10,15]
    1. 非唯一索引范围锁

select * from table where name>=10 and name<11 for update;

  1. 这里最终就是加了(5,15]的临建锁
    1. 唯一索引范围锁bug

select * from table where id>10 and id<=15 for update;

  1. 首先，id>=10 and id<=15会加临建锁(10,15]
  2. 但是，由一个bug可知（唯一索引上的范围查询会访问到不满足条件的第一个值为止），最终会加临建锁(10,20]。
    1. 普通索引上存在“等值”的例子
  3. 普通索引的“等值”也会向后查询到值不相等为止
    1. limit语句减少加锁范围

      4.5.5 查看事务锁的情况

      我们怎么查看执行中的SQL加了什么锁呢？或者换个说法，如何查看事务的加锁情况呢？有这两种方法：
  • 使用infomation_schema数据库中的表获取锁信息

  • 使用show engine innodb status 命令

    使用infomation_schema数据库中的表获取锁信息

    infomation_schema数据库中，有几个表跟锁紧密关联的。

  • INNODB_TRX：该表存储了InnoDB当前正在执行的事务信息，包括事务id、事务状态（比如事务是在运行还是在等待获取某个所）等。

  • INNODB_LOCKS：该表记录了一些锁信息，包括两个方面：1.如果一个事务想要获取某个锁，但未获取到，则记录该锁信息。2. 如果一个事务获取到了某个锁，但是这个锁阻塞了别的事务，则记录该锁信息。

  • INNODB_LOCK_WAITS:表明每个阻塞的事务是因为获取不到哪个事务持有的锁而阻塞。

    INNODB_TRX

  • trx_tables_locked：该事务当前加了多少个表级锁。

  • trx_rows_locked：表示当前加了多少个行级锁。

  • trx_lock_structs：表示该事务生成了多少个内存中的锁结构。

    INNODB_LOCKS

    89b26de83d2e6a68c2d7da52a470b41d.png
    可以看到两个事务Id 1644842和1644843都持有什么锁，就是看那个lock_mode和lock_type。但是并看不出是哪个锁在等待那个锁导致的阻塞，这时候就可以看INNODB_LOCK_WAITS表啦。

    INNODB_LOCK_WAITS

    INNODB_LOCK_WAITS 表明每个事务是因为获取不到哪个事务持有的锁而阻塞。
    image.png

  • requesting_trx_id：表示因为获取不到锁而被阻塞的事务的事务id

  • blocking_trx_id：表示因为获取到别的事务需要的锁而导致其被阻塞的事务的事务Id。

    SHOW ENGINE INNODB STATUS

    INNODB_LOCKS 和 INNODB_LOCK_WAITS 在MySQL 8.0已被移除，其实就是不鼓励我们用这两个表来获取表信息。而我们还可以用show engine innodb status获取当前系统各个事务的加锁信息。
    在看死锁日志的时候，我们一般先把这个变量innodb_status_output_locks打开，它是MySQL 5.6.16 引入的:
    set global innodb_status_output_locks =on;
    在RR隔离级别下，我们交替执行事务A和B：
    image.png
    show engine innodb status查看日志，如下： ```sql TRANSACTIONS

Trx id counter 1644854 Purge done for trx’s n:o < 1644847 undo n:o < 0 state: running but idle History list length 32 LIST OF TRANSACTIONS FOR EACH SESSION: —-TRANSACTION 283263895935640, not started 0 lock struct(s), heap size 1136, 0 row lock(s) —-TRANSACTION 1644853, ACTIVE 7 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1 MySQL thread id 7, OS thread handle 11956, query id 563 localhost ::1 root update insert into t5 values(6,6,6) ———- TRX HAS BEEN WAITING 7 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 267 page no 4 n bits 80 index c of table test2.t5 trx id 1644853 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 8000000a; asc ;; 1: len 4; hex 8000000a; asc ;;

TABLE LOCK table test2.t5 trx id 1644853 lock mode IX RECORD LOCKS space id 267 page no 4 n bits 80 index c of table test2.t5 trx id 1644853 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 8000000a; asc ;; 1: len 4; hex 8000000a; asc ;;

  1. 这结构锁的关键词需要记住一下哈：
  3. - `lock_mode X locks gap before rec`表示X型的gap锁
  4. - `lock_mode X locks rec but not gap`表示 X型的记录锁（Record Lock）
  5. - `lock mode X` 一般表示 X型临键锁（next-key 锁）
  7. 以上的锁日志，我们一般关注点，是一下这几个地方：
  9. - `TRX HAS BEEN WAITING 7 SEC FOR THIS LOCK TO BE GRANTED`表示它在等这个锁
  10. - `RECORD LOCKS space id 267 page no 4 n bits 80 index c of table `test2`.`t5` trx id 1644853 lock_mode X locks gap before rec insert intention waiting`表示一个锁结构，这个锁结构的Space ID是267，page number是4，n_bits属性为80，对应的索引是c，这个锁结构中存放的锁类型是X型的插入意向Gap锁。
  11. - `0: len 4; hex 8000000a; asc ;;`对应加锁记录的详细信息，8000000a代表的值就是10，a的16进制是10。
  12. - `TABLE LOCK table `test2`.`t5` trx id 1644853 lock mode IX `表示一个插入意向表锁
  14. 这个日志例子，其实理解起来，就是事务A持有了索引c的间隙锁（~，10），而事务B想获得这个gap锁，而获取不到，就一直在等待这个插入意向锁。
  15. <a name="lFpIK"></a>
  16. ### 4.5.6 死锁案例分析
  17. <a name="hCCPT"></a>
  18. # 5. 集群
  19. <a name="Ti6Pf"></a>
  20. ## 5.1 主从
  21. <a name="g50Rq"></a>
  22. ### 5.1.1 实现原理
  23. ![image.png](https://cdn.nlark.com/yuque/0/2022/png/22245218/1654071205929-2c71b5e8-89a0-4e58-89d9-b1b4cbf89fde.png#clientId=u452e57d4-297d-4&crop=0&crop=0&crop=1&crop=1&from=paste&height=317&id=u26833054&margin=%5Bobject%20Object%5D&name=image.png&originHeight=396&originWidth=745&originalType=binary&ratio=1&rotation=0&showTitle=false&size=101837&status=done&style=shadow&taskId=u5304f27b-2965-45e3-8f47-a725cd8de30&title=&width=596)<br />主从复制整体分为以下**三个步骤**： 
  25. 1. 主库将数据库的变更操作记录到Binlog日志文件中
  26. 1. 从库读取主库中的Binlog日志文件信息写入到从库的Relay Log中继日志中
  27. 1. 从库读取中继日志信息在从库中进行Replay,更新从库数据信息
  29. 上述过程中，涉及了Master的**BinlogDump Thread**和**Slave的I/O Thread**、**SQL Thread**，它们的作用如下：
  31. 1. Master服务器对数据库更改操作记录在Binlog中，BinlogDump Thread接到写入请求后，读取 Binlog信息推送给Slave的I/O Thread。 
  32. 1. Slave的I/O Thread将读取到的Binlog信息写入到本地Relay Log中。
  33. 1. Slave的SQL Thread检测到Relay Log的变更请求，解析relay log中内容在从库上执行。 
  35. 上述过程都是异步操作，**俗称异步复制**，存在数据延迟现象。<br />**异步复制的时序图：**<br />![image.png](https://cdn.nlark.com/yuque/0/2022/png/22245218/1654071936126-0d954725-e44f-4f01-bb1e-9334ed8a5af2.png#clientId=u452e57d4-297d-4&crop=0&crop=0&crop=1&crop=1&from=paste&height=234&id=u4fb062ad&margin=%5Bobject%20Object%5D&name=image.png&originHeight=293&originWidth=933&originalType=binary&ratio=1&rotation=0&showTitle=false&size=59552&status=done&style=shadow&taskId=u2d9a6e50-82c3-42d6-9fe2-94b1f6c9d1c&title=&width=746.4)<br />**mysql主从复制存在的问题：** 
  37. - 主库宕机后，数据可能丢失
  38. - 从库只有一个SQL Thread，主库写压力大，复制很可能延时
  40. **解决方法：**
  42. - 半同步复制---解决数据丢失的问题
  43. - 并行复制----解决从库复制延迟的问题  
  44. <a name="uJTU2"></a>
  45. ### 5.1.2 半同步复制
  46. 在从节点收到binlog日志写入relaylog后，进行ACK通知Master。然后主才提交。在一定程度上解决了数据一致性问题。<br />![image.png](https://cdn.nlark.com/yuque/0/2022/png/22245218/1654072234296-5f2ed0cc-0087-4499-b585-2fc694544d9a.png#clientId=u452e57d4-297d-4&crop=0&crop=0&crop=1&crop=1&from=paste&height=262&id=u4248d1a8&margin=%5Bobject%20Object%5D&name=image.png&originHeight=327&originWidth=934&originalType=binary&ratio=1&rotation=0&showTitle=false&size=69739&status=done&style=shadow&taskId=u0cca787a-9d17-42e7-8893-2c77dcf585f&title=&width=747.2)
  47. <a name="V7R1j"></a>
  48. ### 5.1.3 并行复制
  49. MySQL从5.6版本开始追加了并行复制功能，目的就是为了改善复制延迟问题，并行复制称为enhanced multi-threaded slave（简称MTS）。  <br />MySQL**5.6之前**的版本中，当主库上有多个线程[并发](https://so.csdn.net/so/search?q=%E5%B9%B6%E5%8F%91&spm=1001.2101.3001.7020)执行SQL时，**sql_thread只有一个**，在某些TPS比较高的场景下，会出现主库严重延迟的问题。MySQL为了解决这个问题，将sql_thread演化了多个worker的形式，在slave端并行应用relay log中的事务，从而提高relay log的应用速度，减少复制延迟。这就是并行复制的由来。<br />![image.png](https://cdn.nlark.com/yuque/0/2022/png/22245218/1654074124838-454ce7ee-bda5-4031-989f-7781702aeb42.png#clientId=u452e57d4-297d-4&crop=0&crop=0&crop=1&crop=1&from=paste&height=205&id=u3057c601&margin=%5Bobject%20Object%5D&name=image.png&originHeight=256&originWidth=603&originalType=binary&ratio=1&rotation=0&showTitle=false&size=21054&status=done&style=shadow&taskId=u8e0c7a32-b256-439e-a861-42af9867da9&title=&width=482.4)<br />在MySQL中，复制线程是由参数slave_parallel_workers来控制的，通常情况下，在8G内存、8核CPU的机器上，将该值设置为8比较合适，如果你的CPU核数比较高，那么可以适当调整为8~16之间的数字。<br />![image.png](https://cdn.nlark.com/yuque/0/2022/png/22245218/1654498830860-73d7c602-9484-45f6-a727-153130d6133c.png#clientId=ub6f20be1-c2e8-4&crop=0&crop=0&crop=1&crop=1&from=paste&height=100&id=u9da6c95f&margin=%5Bobject%20Object%5D&name=image.png&originHeight=125&originWidth=519&originalType=binary&ratio=1&rotation=0&showTitle=false&size=6891&status=done&style=shadow&taskId=u6e4f602d-e707-4d74-a885-371037dfa27&title=&width=415.2)
  50. <a name="nDBrg"></a>
  51. #### MySQL5.6版本
  52. MySQL支持的粒度是**按照数据库**进行并行执行relay log，这种方式能够解决一部分问题，因为不同数据库上的SQL，肯定不会修改表中的同一行内容。这样也就不会产生锁争用。在一些数据库均匀分布，每个数据库使用频率都差不多的场景下，这种并行复制的方法比较好。如果你的业务的数据都集中在一个热点表，这种情况下，并行复制会退化为单线程复制。
  54. <a name="L51Ov"></a>
  55. #### MySQL 5.7
  56. 通过**对事务进行分组**，当事务提交时，它们将在单个操作中写入到二进制日志中。如果多个事务能同时提交成功，那么它们意味着没有冲突，因此可以在Slave上并行执行，所以通过在主库上的二进制日志中添加组提交信息。<br />**MySQL 5.7的并行复制基于一个前提，即所有已经处于prepare阶段的事务，都是可以并行提交的。**这些当然也可以在从库中并行提交，因为处理这个阶段的事务都是没有冲突的。在一个组里提交的事务， 一定不会修改同一行。这是一种新的并行复制思路，完全摆脱了原来一直致力于为了防止冲突而做的分发算法，等待策略等复杂的而又效率底下的工作。 <br />InnoDB事务提交采用的是两阶段提交模式。一个阶段是prepare，另一个是commit。 <br />为了兼容MySQL 5.6基于库的并行复制，5.7引入了新的变量slave-parallel-type，其可以配置的值有： DATABASE（默认值，基于库的并行复制方式）、LOGICAL_CLOCK（基于组提交的并行复制方式）。  
  58. 在MySQL 5.7版本中，其设计方式是将组提交的信息存放在**GTID**中。为了避免用户没有开启GTID功能 （gtid_mode=OFF），**MySQL 5.7又引入了称之为Anonymous_Gtid的二进制日志event类型 ANONYMOUS_GTID_LOG_EVENT。 **通过mysqlbinlog工具分析binlog日志，就可以发现组提交的内部信息。 <br />![image.png](https://cdn.nlark.com/yuque/0/2022/png/22245218/1654509485307-dd17e22b-df9d-4e23-8346-31f61f24009b.png#clientId=ub6f20be1-c2e8-4&crop=0&crop=0&crop=1&crop=1&from=paste&height=284&id=ue87ab541&margin=%5Bobject%20Object%5D&name=image.png&originHeight=355&originWidth=934&originalType=binary&ratio=1&rotation=0&showTitle=false&size=297036&status=done&style=shadow&taskId=u0080c751-c3a2-4442-9923-e95ca08f9e1&title=&width=747.2)<br />可以发现MySQL 5.7二进制日志较之原来的二进制日志内容多了last_committed和 sequence_number，last_committed表示事务提交的时候，上次事务提交的编号，如果事务具有相同 的last_committed，表示这些事务都在一组内，可以进行并行的回放。  
  59. <a name="q4lX3"></a>
  60. #### MySQL 5.8
  61. MySQL8.0 是基于write-set的并行复制。MySQL会有一个集合变量来存储事务修改的记录信息（主键哈 希值），所有已经提交的事务所修改的主键值经过hash后都会与那个变量的集合进行对比，来判断改行是否与其冲突，并以此来确定依赖关系，没有冲突即可并行。这样的粒度，就到了 row级别了，此时并行的粒度更加精细，并行的速度会更快。  
  62. <a name="n1wAE"></a>
  63. #### 并行复制的配置与调优
  65. - binlog_transaction_dependency_history_size 用于控制集合变量的大小。 
  66. - binlog_transaction_depandency_tracking 
  68. 用于控制binlog文件中事务之间的依赖关系，即last_committed值。 
  70.    - COMMIT_ORDERE: 基于组提交机制 
  71.    - WRITESET: 基于写集合机制 
  72.    - WRITESET_SESSION: 基于写集合，比writeset多了一个约束，同一个session中的事务 last_committed按先后顺序递增 
  73. - transaction_write_set_extraction 
  75. 用于控制事务的检测算法，参数值为：OFF、 XXHASH64、MURMUR32
  77. - master_info_repository 
  79. 开启MTS功能后，务必将参数master_info_repostitory设置为TABLE，这样性能可以有50%~80% 的提升。这是因为并行复制开启后对于元master.info这个文件的更新将会大幅提升，资源的竞争 也会变大。
  81. -  slave_parallel_workers 
  83. 若将slave_parallel_workers设置为0，则MySQL 5.7退化为原单线程复制，但将 slave_parallel_workers设置为1，则SQL线程功能转化为coordinator线程，但是只有1个worker 线程进行回放，也是单线程复制。然而，这两种性能却又有一些的区别，因为多了一次 coordinator线程的转发，因此slave_parallel_workers=1的性能反而比0还要差。 
  85. - slave_preserve_commit_order
  87. MySQL 5.7后的MTS可以实现更小粒度的并行复制，但需要将slave_parallel_type设置为 LOGICAL_CLOCK，但仅仅设置为LOGICAL_CLOCK也会存在问题，因为此时在slave上应用事务的 顺序是无序的，和relay log中记录的事务顺序不一样，这样数据一致性是无法保证的，为了保证事 务是按照relay log中记录的顺序来回放，就需要开启参数slave_preserve_commit_order。  <br /> 要开启enhanced multi-threaded slave其实很简单，只需根据如下设置：
  88. ```sql
  89. slave-parallel-type=LOGICAL_CLOCK
  90. slave-parallel-workers=16
  91. slave_pending_jobs_size_max = 2147483648
  92. slave_preserve_commit_order=1
  93. master_info_repository=TABLE
  94. relay_log_info_repository=TABLE
  95. relay_log_recovery=ON

并行复制监控

在使用了MTS后，复制的监控依旧可以通过SHOW SLAVE STATUS\G，但是MySQL 5.7在 performance_schema库中提供了很多元数据表，可以更详细的监控并行复制过程。
image.png
通过replication_applier_status_by_worker可以看到worker进程的工作情况 。

5.2 读写分离

MySQL - 图41

MHA

MHA故障处理机制：

  • 把宕机master的binlog保存下来
  • 根据binlog位置点找到最新的slave
  • 用最新slave的relay log修复其它slave 将保存下来的binlog在最新的slave上恢复
  • 将最新的slave提升为master
  • 将其它slave重新指向新提升的master，并开启主从复制

MHA优点：

  • 自动故障转移快
  • 主库崩溃不存在数据一致性问题
  • 性能优秀，支持半同步复制和异步复制

  • 一个Manager监控节点可以监控多个集群

    主备切换

    主备延迟问题
    主备延迟是由主从数据同步延迟导致的，与数据同步有关的时间点主要包括以下三个：

  • 主库 A 执行完成一个事务，写入 binlog，我们把这个时刻记为 T1;

  • 之后将binlog传给备库 B，我们把备库 B 接收完 binlog 的时刻记为 T2;
  • 备库 B 执行完成这个binlog复制，我们把这个时刻记为 T3。

所谓主备延迟，就是同一个事务，在备库执行完成的时间和主库执行完成的时间之间的差值，也就 是 T3-T1。
在备库上执行show slave status命令，它可以返回结果信息，seconds_behind_master表示当前 备库延迟了多少秒。
同步延迟主要原因如下：

  • 备库机器性能问题

机器性能差，甚至一台机器充当多个主库的备库。

  • 分工问题

备库提供了读操作，或者执行一些后台分析处理的操作，消耗大量的CPU资源。

  • 大事务操作

大事务耗费的时间比较长，导致主备复制时间长。比如一些大量数据的delete或大表DDL操 作都可能会引发大事务。
可靠性优先
主备切换过程一般由专门的HA高可用组件完成，但是切换过程中会存在短时间不可用，因为在切 换过程中某一时刻主库A和从库B都处于只读状态。如下图所示：
image.png
主库由A切换到B，切换的具体流程如下：

  • 判断从库B的Seconds_Behind_Master值，当小于某个值才继续下一步
  • 把主库A改为只读状态（readonly=true）
  • 等待从库B的Seconds_Behind_Master值降为 0
  • 把从库B改为可读写状态（readonly=false）
  • 把业务请求切换至从库B

可用性优先
不等主从同步完成， 直接把业务请求切换至从库B ，并且让 从库B可读写 ，这样几乎不存在不可 用时间，但可能会数据不一致

5.3 分库分表

MySQL - 图43