学习MySQL原理，这张脑图就够了

想要原始脑图下载地址去github下载

脑图大纲

执行过程

客户端

• 连接器
  • 查询缓存
    • 分析器
      • 优化器
        • 执行器
          • 存储引擎

组成

Server层

• 连接器

  • 客户端连接
    • TCP握手
    • 认证通过后连接器会读取权限表，之后的连接中的权限依赖于此时读到的权限
    • 可能存在的问题
      • Mysql长连接时间过长，导致占用内存飞涨，由于执行过程中使用的临时内存在连接断开时才释放，所以长连接积累下可能导致内存占用太大，导致OOM
        • 解决方案
          • 定期断开长连接
          • 5.7以上的版本可以使用mysql_reset_connection来重新初始化连接资源
    • 连接超时时间参数
      • wait_timeout
        • 非交互式，如函数编程
      • interactive_timeout
        • 交互式，如mysql客户端
  • 缓存
    • 特定场景下开启
      • 表基本不变
    • 大多数情况下不要开启
      • 表更新后这个表上所有缓存被清空
      • 经常更新的表命中率会很低
    • 参数query_cache_type
      • 设置为DEMAND
        • 默认语句不实用
        • 可以显示用SQL_CACHE指定
          • select SQL_CACHE * from T where ID=10;
    • MySQL8版本已去除该功能

• 查询缓存

• 分析器

  • 语法分析
  • 词法分析

• 优化器

  • 多个索引时，决定使用哪个索引
  • 多表关联查询时，决定各个表的执行顺序

• 执行器

  • 权限验证

  • 打开表，调用引擎操作接口

  • 每次调用引擎获取数据，rows_examined累加

    • 有些场景下，执行器调用一次，引擎内部可能扫描多行，因此引擎扫描行数与rows_examined不完全相同

存储引擎

• InnoDB
• MyISAM
• Memory

日志

WAL技术

• Write-Ahead Logging

  • 先写日志再保存数据

redo log

• InnoDB引擎记录

• 固定大小

• 循环写

• 物理日志

• 参数设置

  • innodb_flush_log_at_trx_commit=1

    • 每次事务的redo log都直接持久化到硬盘
    • 保证MySQL异常重启后数据不丢失

binlog

• Server层日志

• 逻辑日志

• 追加写

• 参数设置

  • sync_binlog=1

    • 每次事务的binlog都持久化到磁盘
    • 保证MySQL异常重启之后数据不丢失

两阶段提交

• 引擎将数据更新到内存中，记录到redo log中，redo log处于prepare状态
• 执行器生成binlog，写入磁盘
• 执行器调用引擎提交事务，redo log改为commit状态

误删数据

delete 误删行数据

• 恢复

  • FlashBack工具

    • 通过binlog进行恢复

  • 不建议直接在主库上执行

• 预防

  • sql_safe_updates设置为on
  • 代码上线前SQL语句评审

drop table或truncate table误删表数据

• 恢复

  • 使用全量备份
  • 实时备份binlog
  • 使用mysqlbinlog命令进行恢复

drop database误删数据库

• 使用延迟复制备库

• 预防

  • 账号分离
  • 制定操作规范

rm命令误删MySQL实例

• MySQL集群会自动选举出新的主库

服务端数据查询流程

获取一行，写到net_buffer

重新获取一行，写入net_buffer，直到net_buffer写满

调用网络接口把数据发出去，清空net_buffer

直到所有符合条件的行查询完成

join查询

算法

• Index Nested-Loop Join算法

  • 建议使用

    • 相比单表查询，扫描总行数相同，可以减少交互次数，不需要自己拼接SQL语句

  • 选择小表做驱动表

    • 驱动表走全表扫描
    • 被驱动表走树搜索
    • 被驱动表有索引的情况下

• Block Nested-Loop Join算法

  • join_buffer
  • 选择小表做驱动表

建议

• 如果使用的是Index Nested-Loop Join算法

  • 建议使用

• 如果使用的是Block Nested-Loop Join算法

  • 尽量不实用join

    • 扫描行数多，占用大量系统资源

回表过程MRR优化

• 思路

  • 顺序读
  • 满足条件的记录id放入read_rnd_buffer
  • 在read_rnd_buffer中排序
  • 再回表查询

BKA算法

• 5.6版本引入，需要手动开启
• 基于被驱动表的MRR实现
• 可以提升BNL效率
• 5.7版本以后默认开启

临时表

创建临时表

• create temporary table temp_t like t1;

特点

• 只能被创建它的session访问，对其他session不可见

• 临时表可以与普通表同名，不同session之间可以同名

  • 临时表存储时会在表明前增加进程id和线程id前缀

• 同一个session内有同名的临时表和普通表时，show create语句以及增删改查语句操作的是临时表

• show tables不显示临时表

• 创建的session关闭，临时表删除

  • 使用连接池时需要手动删除

用处

• join查询

  • 多个session同时join不会冲突
  • 不用担心表删除问题

• 复杂查询的优化

  • 分库分表系统的跨库查询

主备复制的问题

• 只有在binlog_format=statment/mixed时，binlog中才会记录临时表操作
• 主库会把执行临时表操作的语句的线程id写到binlog中，防止不同主库session创建的临时表冲突

内部临时表

union联合查询

group by

分库分表

跨库查询

• 使用proxy层

  • 中间件开发工作量大
  • proxy端压力大，容易出现内存不够和CPU瓶颈

• 把各个分库拿到的数据汇总到一个MySQL实例的一个表中

  • 汇总库上创建一个临时表，包含查询和条件字段
  • 在各个分库上执行查询
  • 把分库执行结果插入到临时表中
  • 获取结果

优化

group by优化

• 使用generated column机制

  • 创建一列，在列上增加索引，可以避免使用排序

• 直接排序

  • 使用SQL_BIG_RESULT提示
  • 使用sort_buffer进行排序

• 建议

  • 如果group by结果没有排序要求，要在语句后加order by null
  • 尽量让group by过程用上索引，使用explain确认没有Using temporary和Using filesort
  • group by统计的数据量不大，尽量只使用内存临时表，可以调整tmp_table_size参数，避免使用磁盘临时表
  • 数据量大，使用SQL_BIG_RESULT提示，告诉优化器使用直接排序

buffer

sort_buffer

join_buffer

临时表

• 内存临时表
• 磁盘临时表

Memory引擎

堆组织表

• 数据和索引是分开的

与InnoDB的引擎的区别

• InnoDB是有序存放，内存表按照写入顺序存储
• InnoDB容易产生数据空洞，内存表对空洞利用率高
• 数据位置发送改变时，InnoDB只需要修改主键索引，内存表修改所有索引
• InnoDB使用普通索引查找会产生回表，内存表不会
• InnoDB表数据长度可能不同，内存表每行数据长度相同

索引

• 主键索引是hash索引

  • 范围查询不使用主键索引，走全表扫描

• 可以手动创建b-tree索引

  • 类似InnoDB

建议

• 不建议在生产上使用Memory引擎表

  • 锁颗粒度

    • 只支持表锁

  • 数据持久化

    • 重启丢失数据
    • 双M架构可能导致主库内存表数据被删

• 手工创建的临时表可以采用Memory引擎

  • 不需要写磁盘
  • hash索引比b-tree索引快

自增主键

只能保证自增不能保证连续

• 唯一键冲突
• 事务回滚
• 自增主键批量申请

自增锁

批量插入数据操作

• insert … select

  • RR级别下，会给select的表里扫描到的记录和间隙加读锁
  • 如果insert和select是同一张表，可能会造成循环写入，需要引入用户表和临时表来做优化

• replace … select

• load data

建议

• innodb_autoinc_lock_mode设置为2
• binlog_format=row

快速的复制一张表

使用mysqldump

导出CSV文件

• 文件在MySQL服务端

物理拷贝

• 不能直接拷贝.frm文件和.ibd文件
• MySQL5.6引入可传输表空间，导出+导入表空间的方式

分区表

特点

• 对server层来说是1个表

  • 所有分区公用一个MDL锁
  • 启动时访问所有分区

• 对引擎层来说是n个表

  • 按照分区加锁

分区方式

• 范围分区
• hash分区
• list分区

优化点

长事务

• 问题

  • 存在很多老的事务视图，回滚记录，占用存储空间
  • 占用锁资源
  • MySQL5.5版本以前，回滚日志和数据字典放在ibdata中，即使长事务提交回滚段被清理，文件也不会变小

• 原因

  • set autocommit=0

索引

• 联合索引
• 长字段索引

事务隔离

ACID特性

• Automicity
• Consistency
• Isolation
• Durablity

多事务执行的问题

• 脏读
• 不可重复读
• 幻读

事务隔离级别

• 读未提交

  • 事务没有提交前，所做的修改其他事务都可以看得到

• 读提交

  • 事务提交后，所做的修改其他事务才可以看得到

• 可重复读

  • 一个事务执行过程中看到的数据跟事务开始时始终是一致的

• 串行化

  • 对于同一行记录，操作是串行的，写会加写锁，读会加读锁

• select @@global.transaction_isolation;

MVCC（多版本并发控制）

• 不同时刻启动的事务会用不同的read-view

事务启动方式

• 显示启用

  • begin/start transaction， commit，rollback

• 手动提交事务

  • set autocommit=0，关闭自动提交
  • 事务持续到主动执行commit或rollback语句

• 建议

  • 在 autocommit 为 1 的情况下，用 begin 显式启动的事务，如果执行 commit 则提交事务。如果执行 commit work and chain，则是提交事务并自动启动下一个事务，这样也省去了再次执行 begin 语句的开销。同时带来的好处是从程序开发的角度明确地知道每个语句是否处于事务中。

• 长事务监控

  • information_schema.innodb_trx表

索引

目的

• 提升查询效率

类型

• hash表
• 有序数组
• 搜索数

InnoDB的索引模型

• B+树

• 主键索引（聚簇索引）

  • 叶子节点是整行数据

• 非主键索引

  • 叶子节点是主键的值

索引维护

• 页分裂

  • 原有数据页已满，需要申请一个新的数据页，然后挪动部分数据过去

• 页合并

• 自增主键

  • 插入记录都是追加操作，不涉及挪动其他记录，不会导致页分裂
  • 主键长度越小，普通索引的叶子节点就越小，节省空间

回表

• 回到主键索引数搜索的过程

• 覆盖索引

  • 查询非主键索引即可拿到数据，就不需要回表

  • 优势

    • 由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段

  • 弊端

    • 索引字段的维护总是有代价的。因此，在建立冗余索引来支持覆盖索引时就需要权衡考虑了

• 最左前缀原则

  • 第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的

    • 可以支持最左前缀，所以当已经有了 (a,b) 这个联合索引后，一般就不需要单独在 a 上建立索引了
    • 查询条件里面只有 b 的语句，是无法使用 (a,b) 这个联合索引的，这时候你不得不维护另外一个索引，也就是说你需要同时维护 (a,b)、(b) 这两个索引

  • 第二个原则是空间

    • name 字段是比 age 字段大的 ，那我就建议你创建一个（name,age) 的联合索引和一个 (age) 的单字段索引

• 索引下推

  • MySQL5.6以后引入
  • 在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数

索引选择

• change buffer

  • change buffer 用的是 buffer pool 里的内存，因此不能无限增大。change buffer 的大小，可以通过参数 innodb_change_buffer_max_size 来动态设置。这个参数设置为 50 的时候，表示 change buffer 的大小最多只能占用 buffer pool 的 50%

  • 读少写多的业务使用change buffer效果最好

  • 写完马上要读的业务使用change buffer可能会产生副作用

  • 普通索引，可以使用change buffer加快更新速度

  • 唯一索引，无法使用change buffer

    • 需要先读出ID判断是否重复，再进行更新，因此无法使用change buffer

  • redo log 主要节省的是随机写磁盘的 IO 消耗（转成顺序写），而 change buffer 主要节省的则是随机读磁盘的 IO 消耗

• 尽量使用普通索引

  • 因为普通索引的更新操作可以使用change buffer，减少了磁盘的随机读

索引执行选择

• 优化器选择索引影响因素

  • 扫描行数
  • 是否使用临时表
  • 是否排序

• 索引误判的解决方法

  • 应用端用force index强行指定
  • 修改sql语句引导优化器
  • 增加或删除索引

前缀索引

• 使用前缀索引定义好长度，可以做到即节省空间，又不用额外增加太多的查询成本

• 区分度越高越好

• 使用前缀索引就无法使用覆盖索引对查询性能的优化

• 区分度不好的自动选择前缀索引的方法

  • 方法

    • 倒序存储

      • 身份证

    • 字段进行hash，通过hash字段建立索引

  • 缺点

    • 不能做范围查询、排序
    • 只能做等值查询

影响索引选择的几个条件

• 条件字段有函数操作

  • 对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能
  • 如时间函数month等

• 隐式类型转换

  • 字符串与数字进行比较时，将字符串转换成数字

• 隐式字符编码转换

  • 两个表关联查询，使用的不是相同的字符集

锁

全局锁

• 全局读锁Flush tables with read lock

  • 数据更新、数据定义、更新类事务语句都会被阻塞

• 使用场景：全库逻辑备份

• 风险

  • 主库上只读会导致业务停摆
  • 从库上只读会导致主从延迟

• 不使用全局锁的数据备份方法

  • mysqldump使用参数-single-transaction

    • 可重复度隔离策略下，导数据之前会启动一个事务，拿到一致性视图

  • 缺点，引擎必须支持可重复读隔离级别

• 与set global readonly=true的区别

  • 有些系统中readonly的值会被用来做其他逻辑，修改global变量方式影响面比较大
  • FTWRL命令之后，如果客户端异常断开，mysql会自动释放全局锁，readonly设置后悔一直保持，风险较高

表级锁

• 表锁

  • lock tables … read/write
  • unlock tables

• 元数据锁MDL

  • 不需要显示使用，在访问一个表时会自动加上，事务结束后才释放

  • MySQL 5.5版本引入

  • 风险

    • 给一个小表加个字段，导致整个库都挂了

      • A启动事务对标加了MDL读锁
      • B对表进行加字段更新操作
      • C进行select查询，会被阻塞，后面所有的查询都会被阻塞

    • 解决办法

      • alter table 使用NOWAIT或WAIT n语法

行锁

• 引擎自己实现

  • MyISAM就不支持行锁，只能通过表锁实现

• 两阶段锁协议

  • 在InnoDB事务中，行锁是需要的时候才加上，等到事务结束后才释放
  • 如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放

• 死锁

  • 处理策略

    • 等待直到超时，可以通过参数innodb_lock_wait_timeout设置

    • 死锁检测，通过参数innodb_deadlock_detect打开

      • 耗费CPU

        • 解决办法

          • 一定不会出现死锁，关闭死锁检测
          • 控制并发度
          • 中间件或修改源代码

事务隔离

• begin/start transaction

  • 一致性视图在执行第一个快照读语句时创建

• begin/start transaction with consistent snapshot

  • 一致性视图在执行begin/start时创建

MVCC快照

• 可重复读隔离级别下，事务在启动时创建基于整库的快照

• 实现

  • 唯一事务ID，transaction id，严格递增

  • 数据表中的一行记录，可能有多个版本row，每个版本有自己的trx_id

    • 多个版本row不是物理存在的，二是根据binlog和row的trx_id计算出来的

  • 视图数组和高水位组成了当前事务的一致性视图

  • InnoDB利用所有数据都有多个版本的特性，实现了秒级创建快照的能力

更新逻辑

• 更新数据都是先读后写的，而这个读，只能读当前的值，称为“当前读”（current read）

  • update语句

  • 加锁的select语句

    • lock in share mode

非索引更新

• RR模式下，会锁住全表记录

间隙锁(解决RR幻读问题）

• 锁住两个值之间的间隙

• 间隙锁之间不互斥，两个间隙锁可以锁住同一个间隙

• 间隙锁锁住的是往这个间隙里插入数据的操作

• 间隙锁和行锁合称为next-key-lock

  • 前开后闭的区间

• 间隙锁可能产生死锁

• 执行delete 或update操作时，可能导致间隙锁合并

加锁规则

• 基本单位是next-key-lock
• 查找过程中访问到的对象才会加锁
• 索引上的等值查询，给唯一索引加锁的时候，next-key-lock会退化为行锁
• 索引上的等值查询，向右遍历时且最后一个值不满足等值条件是，next-key-lock会退化为间隙所
• next-key-lock先加间隙锁，再加行锁
• 锁是加在索引上的

两种锁方法

• lock in share lock

  • 覆盖索引满足查询条件时，只锁覆盖索引

• for update

  • 锁查询到的索引和主键索引

死锁

• show engine innodb status

  • LATESTDETECTED DEADLOCK

几个概念

read buffer

buffer pool

• 命中率

  • show engine innodb status
  • 建议达到99%以上

• innodb_buffer_pool_size

  • 设置为物理内存的60%-80%

page cache

数据页

• 页分裂
• 页空洞

Oneline DDL

• 建临时文件
• 原表中数据存储到临时文件
• 原表操作生产row log
• 根据row log同时修改临时文件
• 用临时文件替换原表

sort_buffer

• MySQL为每个线程分配一块内存用于排序

幻读

• 出现条件

  • 当前读才会出现
  • 仅只读到新插入的行，不包括更新的行

• 问题

  • 语义不一致
  • 数据不一致

• 原因分析

  • 只锁住一行
  • 其他行允许更新
  • 允许新插入行

• 解决办法

  • 间隙锁

Buffer Pool

表行数统计

MyISAM

• 把一个表的总行数存在磁盘上，执行count(*)时直接返回

InnoDB

• 方案

  • 一行一行的读出来，然后累计计数
  • 为什么不采用记录总行数的方式，因为MVCC的原因，总行数不准确

• 进行的优化

  • 选择最小的索引数来遍历

• 优化方案

  • 使用show table status命令返回的TABLE_ROWS

    • show table status命令返回的TABLE_ROWS是采样值，不准确

  • 使用Redis记录表行数

    • 逻辑上不准确

  • 使用数据表记录

    • 使用事务保证逻辑准确

count（）语句

• 含义

  • 对返回的结果集一行一行的进行判断，如果count函数的参数不是null，累计值加1，否则不加
  • count(*)、count(主键 id) 和 count(1) 都表示返回满足条件的结果集的总行数；而 count(字段），则表示返回满足条件的数据行里面，参数“字段”不为 NULL 的总个数。

• count（*）

  • 专门做了优化，不取值，而是只统计行数

• count（主键ID）

  • 遍历整张表，每行的ID取出来，给server层，server层判断是否为null，进行累加

• count（1）

  • 遍历整张表但不取值，server层累加

• count（字段）

  • 判断字段是否可以为null

建议

• count(字段)<count(主键 id)<count(1)≈count(*)

排序

为什么外部排序文件有多个

• 外部排序一般使用归并排序算法。MySQL 将需要排序的数据分成 n 份，每一份单独排序后存在这些临时文件中。然后把这 n个有序文件再合并成一个有序的大文件。

全字段排序

• 条件

  • sort_buffer能放的下查询的字段的查询过程

• 过程

  • 根据查询字段初始化sort_buffer
  • 根据索引找到第一个满足条件的主键id
  • 到主键id索引取出整行，取查询字段放入sort_buffer
  • 取下一行数据
  • 对sort_buffer中的数据按照排序字段进行排序
  • 按照排序结果取前limit行返回

rowid排序

• 条件

  • sort_buffer不能放的下查询的字段的查询过程

• 过程

  • 根据第一个查询字段和id初始化sort_buffer
  • 根据索引找到第一个满足条件的主键id
  • 到主键id索引取出整行，取第一个查询字段和id放入sort_buffer
  • 取下一行数据
  • 对sort_buffer中的数据按照排序字段进行排序
  • 遍历排序结果，取前limit行，按照id回到表中取出其他字段值

设计思想

• 如果内存够，就要多利用内存，尽量减少磁盘访问

优化

• 使用联合索引

  • 索引本身有序

• 使用覆盖索引

  • 不需要再进行回表

随机排序

• order by rand()

  • 归并排序
  • 优先队列排序

• 在最小主键和最大主键之间取随机数

  • select max(id),min(id) into @M,@N from t ;set @X= floor((@M-@N+1)*rand() + @N);select * from t where id >= @X limit 1;

• 解决主键不均匀的问题

  • select count(*) into @C from t;set @Y = floor(@C * rand());set @sql = concat("select * from t limit ", @Y, “,1”);prepare stmt from @sql;execute stmt;DEALLOCATE prepare stmt;

注意事项

删除数据的时候尽量加limit

• 控制删除数据的条数
• 减小加锁范围

binlog为statement格式时，delete 带 limit，很可能会出现主备数据不一致的情况

为什么kill不掉

• kill逻辑

  • 设置线程的kill状态

• kill不掉的原因

  • 线程没有执行到判断状态的逻辑

  • 终止逻辑耗时较长

    • 超大事务执行期间被kill
    • 大查询回滚
    • DDL命令执行到最后阶段

性能

短连接风暴

• 原因

  • 建立连接耗费资源较多

    • 三次握手
    • 认证鉴权
    • 读写权限

  • 连接建立后执行很少的SQL语句就断开

• 表现

  • 连接数超过max_connection的限制
  • 客户端获取不到新的连接

• 解决办法

  • 关闭占着连接不工作的线程

    • show processlist
    • information_schema.innodb_trx表
    • 先断开事务外空闲连接，再断开事务内空闲连接
    • 断开后客户端需要重新连接，不能用原有句柄重试查询

  • 减少连接消耗

    • 跳过权限验证阶段

      • 重启数据库增加-skip-grant-talbes参数启动

查询长时间不返回

• 表被锁住

  • lock table t1 write;

• 等flush

  • flush关闭打开的表

• 等行锁

慢查询

• 表现

  • 查询慢

    • 没有索引

• 原因

  • 索引没有设计好

    • 在线加索引

      • 应急加索引流程

        • 在备库上关闭binlog，set sql_log_bin=off
        • 备库上执行alter table添加索引
        • 执行主备切换
        • 主库上关闭binlog，加上索引

      • 建议使用gh-ost

  • SQL语句没写好

    • 改写SQL语句

      • query_rewrite功能

mysql> insert into query_rewrite.rewrite_rules(pattern, replacement, pattern_database) values (“select * from t1 where id + 1 = ?”, “select * from t1 where id = ? - 1”, “learnDB”);

call query_rewrite.flush_rewrite_rules();

- MySQL选错了索引

	- 语句加force index

• 优化

  • pt-query-digest工具

QPS突增

• 新业务上线bug导致

  • 单独服务

    • 数据库端之间把新业务白名单去掉

  • 单独用户

    • 管理员账号删除账号

  • 跟主体服务一体

    • 压力最大的sql语句重写

并发查询

• innodb_thread_concurrency

  • 默认0
  • 建议设置为64-128
  • 查询线程进入锁等待，并发线程计数减1

数据可靠性

WAL机制

• redo log

  • redo log buffer->page cache->磁盘

  • 事务执行过程中的redo log 也写到redo log buffer中

  • 写入磁盘时机

    • InnoDB有个后台线程，每1秒会把redo log buffer中的数据写到page cache
    • redo log buffer占用空间即将达到innodb_log_buffer_size一半时，将redo log buffer写入page cache
    • 关联并行事务提交时

• binlog

  • 每个线程独立拥有binlog cache

  • sync_binlog参数

    • 说明

      • 0：每次提交write，不进行fsync
      • 1：每次提交都fsync
      • N：每N次提交进行一次fsync

    • 建议

      • IO速度慢，出现瓶颈时，设置为N比较大的值
      • 100-1000

    • 设置为N的风险

      • 主机发生异常重启时，会丢失最近的N个事务的binlog日志

• 优势

  • redo log和binlog都是顺序写
  • 组提交机制

• 优化思路

  • 设置 binlog_group_commit_sync_delay 和 binlog_group_commit_sync_no_delay_count 参数
  • sync_binlog设置为大于1，常用（100-1000）
  • innodb_flush_log_at_trx_commit设置为2

双1配置

• sync_binlog和innodb_flush_log_at_trx_commit都设置为1

• 组提交

  • 日志逻辑序列号LSN

  • 一组里面提交的事务越多，越能节省IOPS

    • 单线程退化为一个组只有一个事务

  • 参数控制

    • binlog_group_commit_sync_delay

      • 延迟多少微秒再调用fsync

    • binlog_group_commit_sync_no_delay_count

      • 累计多少次才调用fsync

非双1配置

• 业务高峰期
• 备库延迟
• 用备份数据恢复主库的副本
• 批量导入数据

crash-safe可靠性保证

• 如果客户端收到事务成功的消息，事务就一定持久化了
• 如果客户端收到事务失败（比如主键冲突、回滚等）的消息，事务就一定失败了
• 如果客户端收到“执行异常”的消息，应用需要重连后通过查询当前状态来继续后续的逻辑。此时数据库只需要保证内部（数据和日志之间，主库和备库之间）一致就可以了

binlog

主备同步

• 备库设置为只读模式

  • 好处

    • 防止误操作
    • 防止切换过程中出现双写
    • 用readonly状态判断节点角色

  • 设置为readonly状态对超集super权限用户是无效的

• 备库

  • io_thread->relay log->sql_thread

三种格式

• statement

• row

• mixed

  • 可能会产生数据不一致的情况记录为row格式，否则记录为statement格式

使用binlog恢复数据

• 正确做法

  • 用mysqlbinlog工具解析出来，然后把解析的结果发给mysql执行

• 错误做法

  • 用mysqlbinlog工具解析出日志，复制里面的statement语句去执行

优化建议

• 建议设置为row格式

  • 便于恢复数据

• 备库log_slave_updates设置为on

双M结构循环复制问题

• 两个库的server id必须不同
• 备库连接到binlog进行重放过程中，生成与原binlog的server id相同的新的binlog
• 收到从库发过来的日志，先判断server id，如果跟自己相同，丢弃该日志

主备延迟

可能原因

• 备库配置差

• 备库压力大

  • 备库提供了过多的读能力

• 大事务

  • delete删除过多数据
  • 大表DDL

• 备库并行复制能力

主备切换

• 一主一备切换流程

  • 可靠性优先

    • 等seconds_behind_master为0再进行切换

  • 可用性优先

    • 直接切换

• 一主多备切换

  • 基于位点切换

    • 位点计算不准确

    • 可能产生主键冲突错误

      • sql_slave_skip_counter跳过事务
      • slave_skip_errors忽略错误

  • 基于GTID切换

    MySQL5.6引入

    • GTID=server_uuid:gno

      • server_uuid
      • gno每次提交事务加1

备库并行复制策略

• MySQL5.6以前

  • 手动并行

    • 按表并行
    • 按行并行

• MySQL5.6

  • 按库并行

• MariaDB

  • 按组提交并行

• MySQL5.7

  • slave-parallel-type

    • DATABASE
    • LOGICAL_CLOCK

• MySQL5.7.22

  • slave-parallel-type

    • DATABASE

    • LOGICAL_CLOCK

    • WRITE_SET

      • binlog-transaction-dependency-tracking

        • COMMIT_ORDER
        • WRITESET
        • WRITESET_SESSION

同步位点

读写分离

方案

• proxy方案
• 直连方案

过期读

• 原因

  • 主从延迟不同步

• 解决方案

  • 强制走主库

    • 必须要拿到最新结果的请求

  • sleep方案

    • 主库更新后，读从库前sleep一下

  • 判断主备无延迟方案

    • show slave status查看seconds_behind_master值
    • 对比GTID确保主备无延迟
    • 对比位点确保主备无延迟

  • 配合semi-sync方案

    • 当事务提交时，主库把binlog发给从库
    • 从库收到binlog后，回复ack
    • 主库收到ack，给客户端返回事务完成确认

  • 等主库位点方案

    • 使用
      select master_pos_wait(file, pos[, timeout]);命令

      • 从库上执行
      • file和pos指的是主库上的文件名和位置
      • timeout可选

  • 等GTID方案

    • 使用select wait_for_executed_gtid_set(gtid_set, 1);

判断数据库健康状态

• select 1

  • 执行select 1是否有返回
  • 不准确，如果查询线程数达到最大值，select 1仍可返回，实际上无法正常使用

• 查表判断

  • 创建健康表，根据查询该表结果判断
  • 判断不出无法插入、更新的情况

• 更新判断（推荐）

  • 更新健康表中的某个字段

  • 主备可能冲突

    • 在更新字段中增加server_id主键

  • 判定慢

• 内部统计（推荐）

  • performance_schema库

    • file_summary_by_event_name 表里统计了每次 IO 请求的时间
    • 打开所有的 performance_schema 项，性能大概会下降 10% 左右

XMind: ZEN - Trial Version