mysql45讲笔记

Maysql

设计原则: 如果内存够，就要多利用内存，尽量减少磁盘访问。

为什么一般推荐MySQL记录条数不超过5000万?

• 表结构更改, 索引更改耗时, STW
• 主从同步耗时
• 索引性能下降

架构

• mysql本质上也是如同sqlRouter一般, 将功能解耦并分层, 由客户端DataSourcePool -> 北向接入层(连接器) -> 缓存 ----(不命中)-> 词法语法分析(AST) -> 优化器(索引选择) -> 执行器(调南向OLAP接口) -> OLAP(innodb, myISAM...)组成

• 其中包含一些服务治理功能, 比如连接器负责长短连接管理, 鉴权等, 日志系统redo/bin log, 集群管理等

• 而具体引擎定义了查询方法和查询行为, 如对于select, 在innodb里如无索引字段, 则遍历list, 如有则查B树

  • OLAP会将扫描的行数返回为rows_examined存入日志

log

• MySQL 整体来看，其实就是两块：一块是 Server 层，它主要做的是 MySQL 功能层面的事情；还有一块是引擎层，负责存储相关的具体事宜。
• redo log 是 InnoDB 引擎特有的日志插件(myISAM没有)
• Server 层也有自己的日志，称为 binlog（归档日志）

redo log

MySQL官方文档

• 需求: 如果每一次的更新操作都需要写进磁盘，然后磁盘也要找到对应的那条记录，然后再更新，整个过程 IO 成本、查找成本都很高

• 解决思路: Write-Ahead Log(WAL, 先写日志，再写磁盘)

  • 当有一条记录需要更新的时候，InnoDB 引擎就会采用2PC协议，先prepare事务，然后在提交事务时（步骤5）写入环形队列redo log buffer里面，并更新内存，这个时候更新就算完成了（Lazy化）

  • 

  • 在系统比较空闲/ringbuffer空间不足的时候，将这个操作记录刷到磁盘里面（类似池化思想, 因为IO建立/销毁到磁盘的连接很耗时, 最好能一次性用少数连接更新完成）

• redo log大小固定, 也会有写满的时候, 需要适时清理, 清理前先将准备清理的data写到磁盘

• 采用环形队列(ringbuffer)实现, 当写指针write_pos追上清理指针checkpoint时, 就认为写满了. 此时需要暂停写指针(即所有写操作阻塞), 将清理指针后移一段绿色范围内的脏页刷回磁盘

• 由于redo log大小有限, 因此它只会记录增量物理磁盘的diff内容, 如在磁盘某地址页上增加了xxx

bin log

• binlog, 记录的是SQL语句的全量逻辑, 如某表某行某字段更改
• binlog是追加写cache, 没有清理需求, 写到事务提交就把cache写入binlog文件， 然后清空cache， 最后将binlog文件fsync刷盘
  • 可以认为是cache->binlog文件->磁盘三级结构， 类似CPU三级缓存， cache是每个事务独立的， binlog文件及磁盘是共享的
  • 
  • 由于fsync，才是将数据持久化到磁盘的操作，因此IO瓶颈一般出在fsync的刷盘时机
    • sync_binlog=0 的时候，表示每次提交事务都只 write，不 fsync；
    • sync_binlog=1 的时候，表示每次提交事务都会执行 fsync；
    • sync_binlog=N 的时候，表示每次提交事务都 write，但累积 N 个事务后才 fsync。对应的风险是：如果主机发生异常重启，会丢失最近 N 个事务的 binlog 日志。

update执行流程

1. 执行器先找引擎取 ID=xxx 这一行。ID 是主键，OLAP直接用树搜索找到这一行。如果 ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。

2. 执行器拿到引擎查到的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的一行数据，再调用引擎接口写入这行新数据。

3. 引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。

4. 执行器生成这个操作的 binlog，并把 binlog 写入磁盘。即cache -> binlog文件 -> fsync磁盘

5. 执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成。

log提交流程

• 使用2PC协议保证两种log的一致性, 将redo log提交动作拆分为事务prepare->commit, 并将刷binlog作为redolog事务的一部分
• 为什么用2PC? 反证法
  • 假如将redolog和binlog的刷盘分离, 则
    • 要么先redo后bin, 假设redo写完, bin还未写完, mysql挂了. 则事务能恢复, 但通过备份的binlog还原数据库就与原库不一致
    • 要么先bin后redo, 假设bin写完, redo还未写完, mysql挂了, 则事务回滚失败, 但同通过备份的binlog能还原出原库, 不一致
  • 两者结合, 能确保事务redo与备份bin一荣俱荣, 一损俱损

事务

• myISAM不支持事务
• 原子性 -> undolog回滚机制
• 一致性 -> 原子性 + 隔离性 + 持久性
• 隔离性 -> undolog回滚链条 + read-view + 行间锁
• 持久性 -> redolog

隔离级别

• 若隔离级别是“读未提交”， 则 V1 的值就是 2。这时候事务 B 虽然还没有提交，但是结果已经被 A 看到了。因此，V2、V3 也都是 2。
• 若隔离级别是“读已提交”，则 V1 是 1，V2 的值是 2。事务 B 的更新在提交后才能被 A 看到。所以， V3 的值也是 2。
• 若隔离级别是“可重复读”，则 V1、V2 是 1，V3 是 2。之所以 V2 还是 1，遵循的就是这个要求：事务A在执行期间看到的数据前后必须是一致的, 静态的, 不受其他事务影响的。
• 若隔离级别是“串行化”，则在事务 B 执行“将 1 改成 2”的时候，会被锁住。直到事务 A 提交后，事务 B 才可以继续执行。所以从 A 的角度看， V1、V2 值是 1，V3 的值是 2。

MVCC版本视图

• 隔离级别是通过在undo log回滚链条上创建视图(快照)实现的, 区别在于视图的创建时机和生命周期和创建规则

• 假设一个记录的某字段从 1 被按顺序改成了 2、3、4，当前最新值是4. 在回滚日志里面就会有类似下面的记录。
  

• 那么当其他事务需要读取该字段时, 就需要按隔离级别创建不同的read-view, 以确定读取的版本:

  • "读未提交”隔离级别下永远返回链条上的最新值，无read-view创建

  • “串行化”隔离级别下直接加读写锁, 无视图创建

  • 在“读已提交”隔离级别下，这个视图是在每条 SQL 语句(无论哪个事务)开始执行的时候都创建一遍, 创建规则同"可重复读"

    • 

  • 在“可重复读”隔离级别下，这个视图是在事务启动时创建的, 一直持续到自身事务A提交. 创建规则如下:

    • 按照可重复读的定义，一个事务启动的时候，能够看到所有已经提交的事务结果。但是之后，这个事务执行期间，其他事务的更新对它的无锁读请求不可见。

    • 因此，一个事务只需要在启动的时候声明:“以我启动的时刻为准，如果链条上的数据版本是在我启动之前生成的，就ok；如果是我启动以后才生成的，就表明这个记录的事务版本提交于我创建之后, 按定义就跳过，要找到满足条件的它的上一个提交版本”。这就需要以当前事务创建时间为基准的标尺read-view.

    • InnoDB实现: 为当前事务构造了一个有序数组，用来保存这个事务启动瞬间，当前正在“活跃”的所有事务 ID。“活跃”指的就是，启动了但还没提交。
      

    • 数组里面, 活跃事务 ID 的最小值记为低水位, 代表历史事务，当前系统里面已经创建过的事务 ID 的最大值加 1 (即下一个待分配的事务ID)记为高水位, 代表最新事务。

    • 那么, 对于当前事务启动瞬间生成的read-view来说，回滚链条上任一数据版本的 row trx_id，有以下几种可能：

      1. 如果落在绿色部分(<低水位)，表示这个版本是已提交的事务或者是当前事务自己生成的，这个数据是可见的；
      2. 如果落在红色部分(>=高水位)，表示这个版本是由将来启动的事务生成的，是肯定不可见的；
      3. 如果落在黄色部分，表明在当前事务开启的瞬间, 链条上的事务版本开启而未提交. 那就包括两种情况
         a. 若 row trx_id 在数组中，表示这个版本是由正在执行且还没提交的事务中途生成的，不可见；
         b. 若 row trx_id 不在数组中，表示这个版本从开启当前事务到创建read-view的间隙里已经完成提交, 属于不活跃快照，可见。

    • 可见, 对于读请求, 当前事务读到的总是链条上的历史快照, 因此也称为"快照读"或"一致性读"

    • 而对于写请求和有锁select, 其他事务的更新对它是可见的, 当前事务读到的是最新值, 因此也称为"当前读". 在下图中, 事务B提交前, 事务C已经提交了最新值(1,2), 那么事务B的set k=k+1是基于最新值(1,2)的, 而get k是基于历史快照(1,1)的

      • 假如事务B提交前, 事务C还未提交最新值, 由于更新操作要求"当前读"必须读到最新值, 那么根据2PC协议, 事务B会阻塞作为事务C的一部分, 直到C提交后才去读最新值

    • 当事务B提交后, 链条上新建快照版本为101, 此时若再get k, 得到(1,3)

    • 

索引

索引	引擎	特点
哈希	memcache, nosql	等值查询快, 范围查询慢(因为key无序)
有序数组	-	query快, update慢
二叉树	innodb, ES, myISAM	快

---

mysql> create table T(
id int primary key, --聚簇索引, 附带信息为row, 无需回表
k int not null, 
name varchar(16),
index (k) --二级索引, 附带信息为主键, 可能回表
)engine=InnoDB;

• 因为所有二级索引都会存储主键, 所以主键选取会影响索引占据的空间大小(能载入内存的大小), 进而影响查询性能, 同时, update导致的索引树修改也受影响
  • 自增主键: 占用空间小, 只会追加, 不触发页分裂
    • 但不适用于分布式主键, 应使用雪花算法
  • 业务字段: 占空间大, 可能触发页分裂
    • 如果只有一个唯一主键索引, 无二级索引, 可以使用业务字段做主键

覆盖索引 & 最左前缀

• 使用二级索引, 但需求结果集已经被二级索引的附加信息覆盖, 就不用再回表
• 使用场景: 有一个高频请求，要根据市民的身份证号查询他的姓名
  1. 只建立身份证号索引, 需要回表查姓名
  2. 建立(身份证号, 姓名)联合索引, 在用到最左前缀(身份证号)的时候就能用到索引覆盖, 无需回表
• 建立联合索引, 顺序至关重要, 应让最左前缀 + 覆盖索引覆盖尽可能多的业务场景

索引下推

存在联合索引（name, age）, 可以在索引遍历过程中，对索引中包含的字段(age)先做判断，直接过滤掉不满足条件的记录，减少回表次数

select * from tuser where name like '张 %' and age=10 and ismale=1;

前缀索引

• 由于所有二级索引叶节点都包含主键索引字段, 因此主键索引不能太长
• 而当主键索引是身份证号这种长字符串, 就需要截取前缀作为主键索引
• 截取太短, 无区分度; 截取太长, 占空间
• 通过以下命令查看前缀为4567时的区分度

select 
  count(distinct left(email,4)）as L4,
  count(distinct left(email,5)）as L5,
  count(distinct left(email,6)）as L6,
  count(distinct left(email,7)）as L7,
from SUser;

• 前缀索引由于人为截取, 可能导致索引覆盖不可用

锁

全局锁

• 应用场景: 全库备份, 通过 FTWRL 确保不会有其他线程对数据库做更新，然后对整个库做备份。注意，在备份过程中整个库完全处于只读状态, 这意味着
  • 主库写功能丧失
  • 从库接收主库binlog功能丧失
• innodb也支持在可重复读隔离级别下开启事务, 从而以一致性视图做库备份, 同时由于MVCC的支持, 写功能不受损.

表级锁

• 分为记录锁和结构锁
• 记录锁: 读读不互斥, 读写, 写写互斥
• 结构锁: CRUD之间不互斥, CRUD-alter, alter-alter互斥

结构锁是CRUD和alter时自动添加的, 如果不注意, 可能导致死锁:

• 在session C由于session A事务未提交而阻塞后, 此表会排斥一切CRUD和alter, 如果有重试机制, 则发生故障
• MariaDB/AliDB: 给alter添加过期时间, 不会一直阻塞

行级锁

• myISAM不支持
• 两阶段锁协议: 在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。
  • 因此, 如果事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放
  • 比如实现一个电影票在线交易业务，顾客 A 要在影院 B 购买电影票, 事务包含
    1. 从顾客 A 账户余额中扣除电影票价；
    2. 给影院 B 的账户余额增加这张电影票价；
    3. 记录一条交易日志。
  • 其中123都有可能存在并发访问, 如顾客A同时还订阅了自动缴费服务, 恰好在购买电影票时也进行扣款; 影院B同时有其他顾客买票进账; 日志增加
  • 按照并发程度和影响, 事务应按照3->1->2的顺序重排, 尽量减少关键操作的行级锁持有时间

死锁

• 事务 A 在等待事务 B 释放 id=2 的行锁，而事务 B 在等待事务 A 释放 id=1 的行锁
• 死锁处理

方法	特点
innodb_lock_wait_timeout	默认50s太长, 太小如1s又会与本身逻辑执行时间歧义, 造成误伤
innodb_deadlock_detect检测死锁, 回滚事务	扫描所有并发线程, O(并发度^2)
中间件做db北向流量并发控制, 或直接改mysql源码, 比如流量排队	-
逻辑分流, 比如将单行记录拆为N行之和, 修改时只取其中一行, 那么死锁概率降为1/N	其他逻辑也要修改

---

唯一索引 vs 普通索引

• 唯一索引的优势在于, OLAP已经认为逻辑上index无重复, 因此面对读操作, 只要找到一个就ok, 无需沿链表继续找
• 而面对写操作:
  • 当目标数据页已经在内存里, 两者都直接修改即可
  • 当目标数据页在磁盘中, 由于唯一索引要保证唯一性, 需先将磁盘数据依次读到内存做重复判断, 用不到change buffer
  • 普通索引没有判断步骤, 直接将写操作放入change buffer, 待下次载入数据页时(如一次读操作)再merge
    • 这决定了chagebuffer适用于写多读少的场景, 如果读写交替, 则每次写完change buffer后都需载入页, 退化为唯一索引

优化器逻辑

优化器负责按照一定规则选取索引, 但有时按照规则不一定得到最优选择

• 一个索引上不同的值的个数，称之为“基数”（cardinality）。这个基数越大，索引的区分度越好。
  • InnoDB 默认会选择 N 个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数。
  • 当变更的数据行数超过 1/M 的时候，会自动触发重新做一次索引统计。
  • analyze table手动触发
• 是否回表? 主键索引 vs 其他
• force index()强制使用索引, 不做选择

内存管理

• innodb有两种情况性能会产生刷盘抖动

  • redolog环形队列满, 写指针停下来等清理指针刷脏页
  • 内存空间不足, 需要按LRU淘汰页, 但发现都是脏页, 需要先刷盘后淘汰. 因此需要控制脏页率.
    • innodb_io_capacity 设置成磁盘的 IOPS
  • 其中redolog是动态的, LRU是静态的, 用一个eventloop根据二者算出最值作为刷盘速率
    

• 由于刷盘需要开启IO, 因此mysql8以前有连坐机制, 如果脏页的邻居也是脏页, 则在一次IO刷回. 这可能产生多米诺效应, 因此mysql8将innodb_flush_neighbors 设为0

收缩表

• 为了提高删除效率, delete一条记录并不会真的删除并重建b树, 而是会将对应记录标记为"可复用"的空洞
• insert记录如果导致页分裂, 也可能产生空洞
• update = delete + insert, 也会产生空洞

由此可见, 修改操作会造成大量无法回收的空洞, 因此需要重建表以压缩空间

离线重建

• 建一个新表, 然后把旧表按id增加顺序插入.
• 整个流程需要加元数据写锁, 旧表业务暂停, 因此是离线的

在线重建(类似写时复制, GC重标记, 流量切换, 优雅启停)

• 建一个新表, 然后把旧表按id增加顺序插入.
• 插入过程中加元数据读锁, 允许对旧表修改, 但同时将操作记录存在row log里
• 新表建好后, 将row log的操作应用到新表

• gh-ost中间件

count(*)

• myISAM不支持事务, 没有MVCC, 任意时刻记录条数确定, 因此总数记在元数据里

• innodb支持事务, 使用MVCC, 每一行记录都要判断自己是否对这个会话的事务可见，因此对于 count(*) 请求来说，InnoDB 只好把数据一行一行地读出依次判断，可见的行才能够用于计算“基于这个查询”的表的总行数。

  • 优化器会选择最小键索引树, 而非主键索引树来遍历, 减小空间交换

• 面对经常显示总记录的需求, 可以新建一张表, 专门用来存count, 并利用可重复读保证一致性
  

• 如果使用Redis记录总数, 需要处理定时刷盘和分布式事务的问题, 不推荐

order by原理

filesort

• 假如orderby字段没有索引, 那么会按照主键查找所有orderby字段, 并放入sort_buffer
• 对sort_buffer快排, 返回前limit个结果

rowidsort

• 假如orderby字段很多, filesort方法的sort_buffer放不下多少记录, 就需要外部排序
• 为了避免外排序, rowid会在sort_buffer里依次使用单个orderby字段排序, 排完序后回表取主键对应的需求字段

联合索引 & 索引覆盖

• 假如orderby字段本身就有序, 那么无需sort_buffer做排序
• 因此可以创建联合索引, 包含orderby字段
• 进一步, 主键索引 + orderby字段的索引能覆盖所有需求索引, 无需回表

幻读 & 间隙锁

幻读指的是一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行, 这破坏了事务的加锁语义和数据与日志的一致性

• 比如以下过程中, 事务A明明开启了事务, 却(当前)读到了事务C插入的(1,1,5), 这违反了加锁语义
• 而事务A在T6时刻才晚于BC提交, 在binlog里会把所有d=5的记录改成d=100, 违反了数据与日志的一致性

• 在可重复读隔离级别下，普通的查询是"快照读"，是不会看到别的事务插入的数据的。因此，幻读只有在“当前读”下才会出现。也即修改操作和加锁读操作

• 幻读仅专指“其他事务新插入的行”, 新修改的行不算幻读

• 这表明锁粒度过小, 没有将事务BC阻塞住，因此innodb引入间隙锁，将所有数据列 + 列间间隙（防止其他事务insert当前加锁时不存在的列）加锁，这样当BC试图修改时，就会被阻塞住直到A提交事务。

• innodb在可重复读隔离下， 引入行间锁解决了当前读的幻读问题， 引入MVCC解决了快照读的幻读问题和不可重复读问题

• 但是行间锁粒度很大，有可能导致

  • 卡表，比如在一个没有索引的字段上select for update或update，如果该表数据多，立马导致锁全表，表不可用

  • 死锁，比如
    

    • session A 执行 select … for update 语句，由于 id=9 这一行并不存在，因此会加上间隙锁 (5,10);
    • session B 执行 select … for update 语句，同样会加上间隙锁 (5,10)，间隙锁之间不会冲突，因此这个语句可以执行成功；
    • session B 试图插入一行 (9,9,9)，被 session A 的间隙锁挡住了，只好进入等待；
    • session A 试图插入一行 (9,9,9)，被 session B 的间隙锁挡住了，形成死锁。

• 因此，在保证业务不需要满足可重复读的前提下，可以将隔离级别设为读已提交，使间隙锁失效。同时将binlog格式改为row，满足日志一致性。

高可用

一主一备

• 备库向主库发起3次握手，建立长连接，并发送鉴权和需求binlog信息
• 然后备库启动两个线程io_thread 和 sql_thread。其中 io_thread 负责与主库建立连接。
• 同时，主库先完成内部所有事务的刷盘，即上图的redolog prep->binlog->redolog commit
• 主库 A 校验完用户名、密码后，开始按照备库 B 传过来的位置，从本地读取 binlog，发给 B。
• 备库 B 拿到 binlog 后，写到本地文件，称为中转日志（relay log）。
• sql_thread 读取中转日志，解析出日志里的命令，并执行。

binlog格式

• statement，记录主库执行的sql语句，但可能产生二义性导致主备不一致
  • 比如优化器基于采样判断，在备库还原时选择与主库不同的索引，而SQL语句中恰好有limit限制
• row，记录主库操作的行以及修改值，不会产生不一致
  • 但很占空间，比如删掉10行，就需要记载10条记录
• mixed，从语法角度和是否有额外注释判断是否会产生二义性，选择statement或row

互为主备

• 与一主一备不同，一主一备是备库向主库发起同步请求，主库被动
• 互为主备则只有在建立主备关系开始是备库发请求，之后由于二者关系平级，实际上是主库主动向备库同步

主备延迟

直接来源有两点：1.主库发送binlog遇到网络波动 2.备库消费relaylog速度慢。网络问题无法避免。因此relaylog慢消费可能是：

• 备库所在机器的性能要比主库所在的机器性能差。
• 备库同时担任从库角色，承担读请求。应该将从库和备库角色分离，形成一主一备多从
• 大客户/大事务，主库上事务执行多久，在备库上重放就需要多久，尽量均摊并在非业务高峰期执行
• 大表DDL
• 备库开多线程读relaylog，但并发度与主库接受的并发度不一致，这会造成积压。如图
  

备库多线程消费模型

分发原则：

• 不能两个 worker 更新同一行，造成更新覆盖。这就要求更新同一行的两个事务，必须被分发到同一个 worker 中排队。
• 同一个事务不能被拆开，必须放到同一个 worker 中。
• 分发单位的粒度要合适，注意热点表和热点行，防止某worker过热退化成单线程
• 为了负载均衡，可以使用哈希表 + 多个worker队列的形式，key值按分发单位的特征选取，比如
  • 按库分发，key = (库名)
  • 按表分发，key = (表名，库名)
  • 按行分发，key = (表名，库名，行字段，行值)

一主一备多从直连

• 主备切换的痛点是所有从库需要找新主库的位点，使用GTID
• 矫枉过正思想：考虑到切换过程中不能丢数据，所以我们找位点的时候，总是要找一个“稍微往前”的，然后再通过判断跳过那些在从库 B 上已经执行过的事务。

一主一备多从sidecar

无论哪种架构，由于主从可能存在延迟，客户端执行完一个更新事务后马上发起查询，如果查询选择的是从库的话，就有可能读到刚刚的事务更新之前的状态。

解决方法：

• 强制走主库：将读请求分类，如果要求强一致性，则分发到主库；如果要求最终一致性，则分发到从库
• Sleep：假设大多数情况下主备延迟在 1 秒之内，那么读请求之前先sleep（1）
  • 以卖家发布商品为例，商品发布后，用 Ajax 直接把客户端输入的内容作为“新的商品”显示在页面上，而不是真正地去数据库做查询。
  • 这样，卖家就可以通过这个显示，来确认产品已经发布成功了。等到卖家再刷新页面，去查看商品的时候，其实已经过了一段时间，也就达到了 sleep 的目的，进而也就解决了过期读的问题。
• 等待延迟归零：show slave status#seconds_behind_master表示从库落后时间，每次从库读请求先判断该值是否为0
• 等待binlog位点同步