MySQL

概述

结构图

server层

server层由连接器、分析器、查询缓存、优化器、执行器组成；
连接器：负责与客户端建立连接，获取权限，维持和管理连接；
分析器：进行词法分析和语法分析；词法分析（把输入的“select”关键词识别出来，这是一个查询语句。把表明 字符串T、列明ID 字符串等识别出来） 语法分析（根据语法规则，判断输入的sql是否满足MySQL语法）
查询缓存：如要要查询的数据在缓存在缓存中，则直接返回不走后面的逻辑；对一个表进行更新，则这个表上所有的查询缓存都会被情况，此对于更新压力大的数据库来说，查询缓存的命中率会非常低；MySQL8.0版本直接将查询缓存的整块功能删除了；
优化器：在表里有多个索引时，决定用哪个索引；在一个语句有多表关联（join）时，决定关联顺序；
执行器：判断对T表有没有执行查询的权限；使用某个储存引擎提供的接口来读写；

存储引擎

负责数据的存储和提取，支持InnoDB、MyISAM、MEMORY、MERGE等多个存储引擎；

InnoDB

特征：

1. 支持事务、外键，使得数据有更高的完整性和统一性；
2. 支持行锁和表锁
3. 读写并发，写不阻塞读，MVCC
4. 特殊的索引存放方式（B+树），可以减少IO，提升查询效率
5. 支持崩溃恢复，不丢失数据

使用场景：经常更新的表，存在并发读写或者有事务处理的业务系统

MyISAM

特征：

1. 不支持事务
2. 仅支持表锁
3. 数据和索引分开，内存可以加载更多的索引，查询速度更快
4. 储存了表的行数（count速度快）

使用场景：适合少量插入，大量查询。如数据分析，报表系统的项目

MERGE

1. 一组MyISAM表（表结构均完全相同）的组合，MERGE表本身无数据
2. 可以突破单个MyISAM表的限制，将其分布在不同的磁盘上，有效改善访问效率，用于数据仓储

MEMORY

1. 数据存在内存中，默认使用HASH索引，访问非常快
2. 用于内容不频繁改变的代码或者作为统计操作的中间操作结果表
3. 只有memory储存引擎支持HASH索引

binlog（归档日志）

server层特有的日志

概述

记录的是这个语句的原始逻辑，比如“给ID=2的这一行的c字段加1”，binlog是可以追加写入的，写到一定大小后会切换到下一个，并不会覆盖以前的日志。

作用

1. 把binlog导出成sql语句，把所有的操作重放一遍，来实现数据的恢复。
2. 实现主从复制，它的原理就是从服务器读取主服务器的binlog，然后执行一遍

日志生产流程

1. 执行器先找到引擎取ID=2这一行。ID是主键，引擎直接用树搜索找到这一行。如何ID=2这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回；
2. 执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的一行数据，再调用引擎接口写入这行新数据。
3. 引擎将这行新数据更新到内存中，同时将这个更新操作记录到redo log中，此时redo log处于prepare状态。然后告知执行器执行完了，随时可以提交事务。
4. 执行器生成这个操作的binlog，并把binlog写入磁盘。
5. 执行器调用引擎的提交事务接口，引擎把刚刚写入的redo log改成提交（commit）状态，更新完成。

PS:MySQL通过两阶段提交来保证redo log和binlog数据是一致的，如果写redo log失败了，就回滚不再写binlog；如果redo log成功了，写binlog失败了，这个事务也会回滚，将无效的binlog删除。

事务

基本特脏

原子性：事务是数据库的逻辑工作单位，事务中包含的各个操作要么都做，要么都不做
一致性：事务执行的结果必须是使数据库从一个一致性状态到另一个一致性状态。因此当数据库只包含成功事务提交的结果时，就说数据库处于一致性状态。如果数据库系统运行时发生故障，有些事务尚未完成就被迫中断，这些未完成事务对数据库所做的修改有一部分已写入物理数据库，这时数据库就处于一个不正确状态，或者说是不一致的状态。
隔离性：一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对其他并发事务是隔离的，并发执行的各个事务之间不能相互干扰。
持久性：一个事务一旦提交，它对数据库中的数据的改变应该就是永久性的。接下来的其他操作或故障不应该对其执行结果有任何影响。持久性就是靠redo log来实现的。

问题

脏读：一个事务中访问到了另外一个事务未提交的数据
不可重复读：一个事务读取同一条记录2次，得到的结果不一致
幻读：一个事务读取2次，得到的记录条数不一致。幻读仅专指“新插入的行”

锁机制

乐观锁

数据库表设计：id value verion
语句：update table set value = 2 ,version = version +1 where id = #{id} and version = #{version}

共享锁

1. 共享锁允许一个事务读数据，不允许修改数据，如果其他事务要再对改行加锁，只能加共享锁；
2. 可以用select … lock in share mode;的方式手工加上一把读锁；
3. innodb从来不会自己主动加个共享锁
4. 满足当前读

排它锁（悲观锁）

1. 排它锁是修改数据时加的锁，可以读取和修改数据，一旦一个事务对改行加锁，其他事务将不能再对改数据加其他锁。
2. 在操作数据的时候，包括增删改，都会默认加上一个排它锁。selcet 直接通过MVCC读快照就行了，不用加锁
3. 手工加锁，用一个for update 给一行数据加上一个排它锁
4. 满足当前读
5. lock in share mode只锁覆盖索引（锁普通索引但不用回表，如果要回表作用通for update），而使用for update时，系统会认为你接下来要做更新操作，因此会顺便给主键索引上满足条件的行加上行锁

意向锁

1. 当我们给一行数据加上共享锁/排它锁之前，数据库会自动在这张表上加一个意向锁共享锁/排它锁
2. 如果一张表上面至少有个意向共享/排它锁，说明有其他事务给其中的某些数据行加上了共享锁/排它锁
3. 加表锁不需要扫描每一行判断是否有加行锁，只需要判断是否存在意向锁即可，提高了加表锁效率

按照加锁的范围分

行锁

行锁是通过索引实现的，如果不通过索引条件检索数据，那么innodb将对表中所有记录进行加锁，再有MySQL Server层进行过滤的时候，如果发现不满足WHERE条件，会释放对应记录的锁。
通过二级索引给数据行加锁，回表后主键索引也会被锁住，也就是说通过普通索引的查询需要加两把锁。
在InnoDB事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这就是两阶段锁协议。

record lock 记录锁
当我们对于唯一性的索引（包括唯一索引和主键索引）使用等值查询，精准匹配到一条记录的时候，这个时候使用的就是记录锁。

假设是主键索引，select * from t where id = 7; – 会锁住id=7这一条记录

gap lock 间隙锁
对索引项之间的间隙加锁。当我们查询的记录不存在，没有命中任何一个record，无论是用等值查询还是范围查询的时候，它使用的都是间隙锁。间隙锁主要是阻塞插入insert。相同的间隙锁之间不冲突。Gap Lock 只在 RR 中存在。
假设是主键索引，select * from t where id = 6, 没有命中记录会锁住(4,7)，因此会阻塞这个范围内的insert事务

next-key lock 临键锁
当我们使用了范围查询，不仅仅命中了Record记录，还包含了 Gap间隙，在这种情况下我们使用的就是临键锁，它是MySQL里面默认的行锁算法，相当于记录锁加上间隙锁。可以解决幻读。Next-key Lock 只在 RR 中存在。

假设不是主键索引或唯一索引，而是普通二级索引，select * fromt t where id = 7; – 锁住(4,7]和(7,10] ，此时如果插入一条 id = 8 的记录将会阻塞住。之所以要把 id = 7 前后的间隙都锁住，仍然是为了解决幻读问题，因为 id 是非唯一索引，所以 id = 7 可能会有多条记录，为了防止再插入一条 id = 7 的记录。

表级锁

表锁

1. 索引失效从行锁升级为表锁
2. 一般在执行DDL语句时会对整个表进行加锁，比如说ALTER TABLE等操作
3. 手动加锁，共享读锁：lock table tablename read; 独占写锁：lock table tableName write；

MDL锁

1. 锁的是表结构，不需要显示使用，在访问一个表的时候会被自动加上
2. 当对一个表做增删改查操作的时候，加MDL读锁；当要对表结构变更操作的时候，加MDL写锁。

全局锁

对整个数据库实例加锁，库变为只读状态，用于做全库逻辑备份时。主要是MyISAM引擎表的备份，而InnoDB有一致性视图可以避免数据一致性问题，可以不用加全局锁。

加锁规则

1. 加锁的基本单位是next-key lock
2. 查找过程中访问到的对象才会加锁
3. 索引上的等只查询，给唯一索引加锁的时候，next-key lock退化为行锁（需要记录存在）
4. 索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock退化为间隙锁
5. 唯一索引上的范围查询会访问到不满足条件的第一个值为止

隔离级别

未提交读（Read Uncommitted）

1. 可能读取到其他会话中未提交事务修改的数据
2. 完全不加锁
3. 在事务A读取数据的时候，事务B读取和修改数据加了共享锁。这种隔离级别，会导致脏读、不可重复读以及幻读。

已提交读（Read Commited）

1. 只能读取到已提交的数据
2. 在事务A读取数据时增加了共享锁，一旦读取，立即释放锁，事务B读取修改数据是增加了行级排它锁，知道事务结束才释放锁。也就是说，事务A在读取数据时，事务B只能读取数据，不能修改。当事务A读取到数据后，事务B才能修改。这种隔离级别，可以避免脏读，但依然存在不可重复读以及幻读的问题。
3. 这里指的加锁不能修改是指不能修改最终存储的数据，但是它实际上修改已经先加入到了Undo log中，即MVCC的机制，等锁释放后再持久化到最终的数据中。
4. 每一个语句执行前都会重新算出一个新的视图
5. 每一个语句执行前都会重新算出一个新的视图
6. 除了普通的select外其他的操作（包括显式加锁的select）通过记录锁实现
7. 因为只用到行锁，对于更新和删除，InnoDb只锁住更新和删除的行，假设where没有命中也不会锁表。
8. 对于更新操作，如果某行已经被锁了，Innodb提供“半一致性读”，返回该行最新提交的版本，如果命中更新中where的条件，如果加不了锁那就等待锁的释放。提高了更新的并发
9. 语句执行过程中加上的行锁，在语句执行完成后，就要把“不满足条件的行”上的行锁直接释放了，不需要等到事务提交

可重复读（Repeatable Read） MySQL的默认事务隔离级别

1. 它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行
2. 在事务A读取数据时增加了共享锁，事务结束，才释放，事务B读取修改数据时增加了行级排它锁，知道事务结束才释放。也就是说，事务A在没有结束事务时，事务B只能读取数据，不能修改。当事务A结束事务，事务B才能修改。这种隔离级别，可以避免脏读、不可重复读，但依然存在幻读的问题。
3. 只需要在事务开始的时候创建一致性视图，之后事务里的其他查询都公用这个一致性视图
4. 普通的select（不显式加锁）通过MVCC实现
5. 除了普通的select外其他的操作（包括显式加锁的select）通过记录锁、间隙锁、临键锁实现
6. 为保证binlog记录顺序，非索引更新会锁住全表记录，且事务结束前不会对不符合条件记录有逐步释放的过程

可序列化（Serializable）

在事务A读取数据时增加了共享锁，事务结束，才释放锁，事务B读取修改数据时增加了表级排他锁，直到事务结束才释放锁。可序列化解决了脏读、不可重复读、幻读等问题，但隔离级别越来越高的同时，并发性会越来越低。

MVCC 多版本并发控制

为了支持高并发读写的情况下保持数据一致性，同一条记录在系统中可以存在多个版本（可以认为是mysql乐观锁的一种实现）

1. innodb存储引擎，会在每行数据的最后加两个隐藏行，一个保存创建时间，一个保存行的删除时间，但是这儿存放的不是时间，而是事务id
2. 只能查找创建时间小于等于当前事务ID的数据，和删除时间大于当前事
   务ID的行（或未删除）。我可以查到在我这个事务开始之前已经存在的数据，即使它在后面被修改或者删除了。在我这个事务之后新增的数据，我是查不到的。

undo log 回滚日志

1. ubdo log主要作用就是回滚和多版本控制(MVCC)
2. undo log主要存储的也是逻辑日志，比如我们要insert一条数据了，那undo log会记录的一条对应的delete日志。我们要update一条记录时，它会记录一条对应相反的update记录。
3. 每行记录都实现了两个隐藏字段DB_TRX_ID（插入或更新行的最后一个事务的事务ID，事务编号是自动递增的，我们把它理解为创建版本号，在数据新增或者修改为新数据的时候，记录当前事务ID）、DB_ROLL_PTR（回滚指针，我们把它理解为上一个版本）
4. 
   当前版本是V4，V1、V2、V3 并不是物理上真实存在的，而是每次需要的时候根据当前版本和 undo log 计算出来的，如果有一个事务，它的低水位是 18，那么当它访问这一行数据时，就会从 V4 通过 U3 计算出 V3，所以在它看来，这一行的值是 11。

一致性视图

1. InnoDB 为每个事务构造了一个数组，用来保存这个事务启动瞬间，当前正在“活跃”的所有事务 ID
2. 如果落在绿色部分，表示这个版本是已提交的事务或者是当前事务自己生成的，这个数据是可见的
3. 如果落在红色部分，表示这个版本是由将来启动的事务生成的，是肯定不可见的；
4. 如果落在黄色部分，那就包括两种情况：
   a. 若 row trx_id 在数组中，表示这个版本是由还没提交的事务生成的，不可见；
   b. 若 row trx_id 不在数组中，表示这个版本是已经提交了的事务生成的，可见。
5. 自己的更新总是可见，不管有没有提交

对于读提交，查询只承认在语句启动前就已经提交完成的数据；
对于可重复读，查询只承认在事务启动前就已经提交完成的数据；
对于更新，更新数据都是先读后写的，而这个读，只能读当前的值，称为“当前读”，即总是读取已经提交完成的最新版本，否则会出现txId小的事务更新覆盖txId更大的事务之前更新好的数据，造成数据不一致的情况

死锁

两个事务在相互等待对方的行锁释放

预防

1. 尽量使用索引访问数据，避免没有where条件的操作，避免锁表
2. 打开死锁主动监测，当发现死锁时会让一方主动让出锁
3. 大事务分解成小事务
4. 使用等值查询而不是范围查询

分布式锁

创建订单记录：如果该订单不存在才创建，select id from order where order_no= ‘xxxx’ for update（先创建一个锁表，然后通过for update创建表锁，防止幻读）

保证查询和插入是在同一个事务中

缺点

1. 当有大量的请求进来时，大部分的请求都会进行排队等待。为了保证数据库的稳定性，事务的超时时间往往又设置得很小，所以就会出现大量事务被中断的情况
2. 除了阻塞等待之外，因为订单没有删除操作，所以这张锁表的数据将会逐渐累积，我们需要设置另外一个线程，隔一段时间就去删除该锁表中的过期订单，这就增加了业务的复杂度

集群

主从复制

主库在收到客户端提交事务的请求之后，会先写入 Binlog，然后再提交事务，更新存储引擎中的数据，事务提交完成后，给客户端返回操作成功的响应。同时，从库会有一个专门的复制线程，从主库接收 Binlog，然后把 Binlog 写到一个中继日志里面，再给主库返回复制成功的响应，提交事务和复制这两个流程在不同的线程中执行，互相不会等待，这是异步复制

1. log dump thread：发送binlog给slave
2. IO Thread：连接到master获取binlog，并且解析binlog写入中继日志，原来是单线程后期发展成多线程
3. sql thread：从库的SQL线程，是用来读取relay log，把数据写入到数据库，单线程，是造成延迟主从延迟的主要瓶颈

主从延迟

默认情况下都是异步复制，肯定会产生延迟的问题。主库写，从库读，但是从库跟不上主库，有一定延迟，此时读从库会读到旧数据。如插入数据后马上读取。当主库并发严重时，从库延迟会更大

解决方案

1. 分库，降低库的读写并发，此时延迟可以忽略不计
2. 打开并行复制，减少延迟
3. 如果实在是要保证插入后马上读取这种操作的一致性，可以通过中间件直连主库。但是不推荐，读写分离就没有意义了
4. 直接规避这个问题。设计系统的业务流程时，尽量不要在更新数据之后立即去查询更新后的数据。如付款成功后会更新订单状态为已支付，但是付款后提示一个付款成功或者其他广告之类的，不立即跳到订单页。
5. 没有完全避免延迟的方法，但至少要做到能够监控主从延迟，当延迟太大的时候，采用一些降级方案。比如说，把重要业务的读请求切回主库，暂停一些不重要的业务，甚至限流等等。

方案

单线程复制

老版本采用，SQLThread是单线程，sql排队执行。不能并发执行是因为不知道这些sql之间的事务、顺序等关系

全同步复制

等待全部从库的事务执行完毕，才返回给客户端，同步复制这种方式在实际项目中，基本上没法用，原因有两个：一是性能很差，因为要复制到所有节点才返回响应；二是可用性也很差，主库和所有从库任何一个数据库出问题，都会影响业务

半同步复制

1. 主库在执行完客户端提交的事务后不是立刻返回给客户端，而是等待至少一个从库
   接收到binlog并写到relaylog中才返回给客户端
2. 兼顾了异步复制和同步复制的优点。如果主库宕机，至少还有一个从库有最新的数据，不存在丢数据的风险。并且，半同步复制的性能也还凑合，也能提供高可用保证，从库宕机也不会影响主库提供服务。所以，半同步复制这种折中的复制方式，也是一种不错的选择
3. rpl_semi_sync_master_wait_no_slave，至少等待数据复制到几个从节点再返回，这个数量配置的越大，丢数据的风险越小，但是集群的性能和可用性就越差。最大可以配置成和从节点的数量一样，这样就变成了同步复制，一般情况下，配成默认值 1 也就够了，这样性能损失最小，可用性也很高，只要还有一个从库活着，就不影响主库读写。丢数据的风险也不大，只有在恰好主库和那个有最新数据的从库一起坏掉的情况下，才有可能丢数据
4. rpl_semi_sync_master_wait_point，控制主库执行事务的线程，是在提交事务之前（AFTER_SYNC）等待复制，还是在提交事务之后（AFTER_COMMIT）等待复制。默认是 AFTER_SYNC，也就是先等待复制，再提交事务，这样完全不会丢数据。AFTER_COMMIT 具有更好的性能，不会长时间锁表，但还是存在宕机丢数据的风险如果说，主库提交事务的线程等待复制的时间超时了，这种情况下事务仍然会被正常提交。并且，MySQL 会自动降级为异步复制模式，直到有足够多（rpl_semi_sync_master_wait_no_slave）的从库追上主库，才能恢复成半同步复制。如果这个期间主库宕机，仍然存在丢数据的风险。

多库并行复制

将单主变为多主，需要分库分表，场景有限，因为大部分场景都是单库多表

异步复制之 GTID 复制

从库的SQL线程是多线程，把那些在主库上并行执行的事务，分为一个组，并且给他们编号，这一个组的事务在从库上面也可以并行执行。这个编号，我们把它叫做 GTID（Global Transaction Identifiers）

数据备份

全量备份

备份文件包含数据库中的所有数据，占用的磁盘空间非常大，每次备份操作都要拷贝大量数据，备份过程中会占用数据库服务器大量的 CPU、磁盘 IO 资源，并且为了保证数据一致性，还有可能会锁表，这些都会导致备份期间，数据库本身的性能严重下降，因为很耗性能只能定期的全量备份，如果来不及做下一次备份，那么周期内的数据全部丢失

增量备份

binlog

混合备份

通过定期的全量备份，配合 Binlog，我们就可以把数据恢复到任意一个时间点

恢复过程：先全量恢复，再用 Binlog 把数据恢复到删库跑路之前的那个时刻

在回放 Binlog 的时候，指定的起始时间可以比全量备份的时间稍微提前一点儿，确保全量备份之后的所有操作都在恢复的 Binlog 范围内，这样可以保证恢复的数据的完整性（为了确保回放幂等，需要设置 Binlog 的格式为 ROW 格式）