Mysql

本文最后更新于：星期一, 九月 12日 2022, 1:25 凌晨

基础

存储引擎

mysql支持的存储引擎有很多种，主要是innodb、myisam两种

  • myisam
    不支持事务，不支持外键约束，不支持行锁，支持表锁。索引文件和数据文件分开，能在内存里缓存更多的索引，对查询的性能会更好，适用于少量插入，大量查询的场景。执行select count（* ） from table操作时，由于保存了表的行数，因此可以直接读取已经保存的值，不需要进行全表扫描。
  • innodb
    mysql 5.5之后的默认存储引擎。主要特点是支持事务，支持行锁和表锁（默认为行锁），走聚簇索引，强制要求有主键，支持外键约束，可用来做分库分表、读写分离、主备切换。执行select count（* ） from table操作时，需要进行全表扫描

这里能问一道面试题
一张表共有10条数据，删除了最后2条，重启数据库后，再插入一条数据，此时自增id是多少？
如果表类型是myisam,id是11
如果表类型是innodb，id是9
原因
innodb会把自增id最大值记录在内存中，重启后，内存数据丢失，所以插入时从id=8开始自增

索引

数据结构

mysql索引是用一个数据结构组织某一列的数据，如果根据那一列的数据进行查询，可以不用全表扫描，根据那个特定的数据结构去找到那一列的值，再找到对应的行物理地址即可

mysql索引是通过b+树实现，b+树是b树的一个变种，因此先讲一下b树概念

b树

  • 叶子节点具有相同深度，指针为空

  • 所有索引元素不重复

  • 节点中的数据索引从左到右递增排列

    b树

b+树

  • 非叶子节点不存储data，只存储索引（冗余），可以放更多索引

  • 叶子节点包含所有索引字段

  • 叶子节点用指针链接，提高区间访问性能

    b+树

数据查询都是从树的根节点开始二分查找

myism索引实现

myism索引最大的特点是数据文件（后缀名MYD）和索引文件（后缀名MYI）是分开的，也就是非聚簇索引。

数据文件里每行对应一个物理地址，而myism索引里，每个叶子节点的data存放的就是数据行的物理地址。数据查找时，先在索引文件里找到对应的数据行物理地址，然后就可以在数据文件里找到对应的行。

也就是说数据查找流程是先在索引文件里搜索到行物理地址，然后到数据文件里定位到一条具体的数据行。

myism

如图根据左下角示例，依次根据箭头显示了整个myism索引的数据查找流程

innodb索引实现

和myisam最大的区别在于innodb的数据文件本身就是个B+树结构的索引文件，就是主键key，叶子节点的data就是待查找的那条数据所在行。总结下来两点

  • 表数据文件本身就是B+树结构的索引文件（后缀名ibd）
  • 聚簇索引，叶子节点包含了完整的数据记录

如下图

innodb

这里有几个可以作为面试题的问题

  1. innodb引擎的数据库表为啥要求必须有主键？
    答案:
    因为聚簇索引是根据主键来聚合数据，如上图最底部叶子节点绿色的都是主键，下面两行是数据。如果不定义主键，innodb会选择一个唯一的非空索引来替代主键。如果连这样的索引都没有，它会隐式定义一个主键来作为聚簇索引。所以，聚簇索引也被称为主键索引。
    另外说一句，如上图，聚簇索引的索引号和对应的数据记录是在一起的，这样索引顺序和数据存储的顺序也是一致的。

  2. 所谓的”回表“是什么？为啥非主键索引结构的叶子节点存储的是主键值？
    答案:
    非主键的字段创建索引，叶子节点存储的data值是主键值，因为可以用主键的值到聚簇索引里，根据主键值再次查找到数据，即所谓的回表，例如:

    select * from table where name = ‘张三’

    先到name的索引里去找，找到张三对应的叶子节点，叶子节点的data就是那一行的主键，假设是id=15，然后再根据id=15，到数据文件里的聚簇索引（主键索引）里定位到id=15这一行的完整数据

  3. 为啥主键推荐整型自增字段？用uuid不香么？
    答案:
    因为这样可以保持聚簇索引直接加记录就行了，如果用那种不是单调递增的主键值，可能会导致b+树分裂后重新组织，会浪费时间，影响性能（这也是不用uuid做主键的原因之一）。原因之二: 根据前面问题2，可知如果主键都是uuid字符串，那么所有非主键索引叶子节点存储的data都是uuid字符串，会导致索引变得很大，浪费磁盘空间

  4. 为啥Innodb引擎索引使用B+树而不是B树？
    答案

    • B+树内部非叶子节点只存放键，不存放值，因此，一次读取，可以在内存页中获取更多的键，有利于更快地缩小查找范围。 B+树叶子节点由一条指针链连接。当需要进行一次全数据遍历时，B+树只需使用O(logN)时间找到最小的一个节点，然后通过指针链进行O(N)的顺序遍历即可。而B树则需要对树的每一层进行遍历，这会需要更多的内存置换次数，因此也就需要花费更多的时间
    • B树适合随机检索，而B+树同时支持随机检索和顺序检索
    • B+树空间利用率更高，可减少I/O次数，磁盘读写代价更低。一般索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。这样，索引查找过程中就要产生磁盘I/O消耗。B+树的内部结点并没有指向关键字具体信息的指针，只是作为索引使用，其内部结点比B树小，能容纳的结点中关键字数量更多，一次性读入内存中可以查找的关键字也就越多，相对的，IO读写次数也就降低了。而IO读写次数是影响索引查询效率的最大因素
    • B+树的查询效率更加稳定。B树搜索有可能会在非叶子结点结束，越靠近根节点的记录查找时间越短，只要找到关键字即可确定记录的存在，其性能等价于在关键字全集内做一次二分查找。而B+树中，顺序检索比较明显，随机检索时，任何关键字的查找都必须走一条从根节点到叶节点的路径，所有关键字的查找路径长度相同，导致每一个关键字的查询效率相当
    • B树在提高了磁盘IO性能的同时并没有解决元素遍历效率低下的问题。B+树的叶子节点使用指针链顺序连接在一起，只要遍历叶子节点就可以实现整棵树的遍历。而在数据库中基于范围的查询是非常频繁的，B树不支持这样的操作
    • 增删文件（节点）时，效率更高。因为B+树的叶子节点包含所有关键字，并有序的链表结构存储，这样可很好提高增删效率

使用规则

select * from table where a=1 and b=2 and c=3

针对上述sql怎么建立索引，才可以确保这个sql会使用索引来查询？

一般我们针对平时要查询的几个字段，会建立一个联合索引，后面写sql都要符合一个最左前缀匹配原则。确保所有sql都可以使用这个联合索引，通过索引来查询

create index (a,b,c)

  1. 全列匹配
    这个表示如果where条件里就用了这3个字段，那么一定可以用到这个联合索引

    select * from table where a=1 and b=2 and c=3

  2. 最左前缀匹配
    这个表示如果用到了联合索引最左边的一个或者几个字段，那么也可用上这个索引，在索引里查找时，就用最左边的几个字段就ok了

    select * from table where a=1 and b=2

  3. 最左前缀匹配了，但是中间某个值没匹配
    这个表示如果就用了联合索引的第一个字段和第三个字段,那么会按照第一个字段值在索引里找，找完以后对结果集扫描一遍,根据第三个字段来过滤，第三个字段是不走索引搜索的，就会有一个额外的过滤的工作，但是还能用到索引

    select * from table where a=1 and c=3

  4. 没有最左前缀匹配
    这个肯定不会用到索引，千万别犯这个错误

    select * from table where b=2

  5. 前缀匹配
    这个表示如果不是等值的，比如=，>=，<=的操作，而是like操作，那么必须要是like ‘XX%’这种才能用上索引

    select * from table where a=1 and b=2 and c like ‘2018%’

  6. 范围列匹配
    这个表示如果是范围查询，比如>=，<=，between操作，只有在符合最左前缀的规则下才可以进行范围查询，范围查询后的字段就不用索引了

    select * from table where a>=1 and b=1

    这个sql就只能根据联合索引中的字段a来查询

  7. 包含函数
    这个表示如果对某个字段用了函数，比如substring之类的东西，那么那一字段不会用上索引

    select * from table where a=1 and 函数(b) = 2

    这个sql也只能根据联合索引中的字段a来查询

缺点

  • 增加磁盘消耗
  • 占用磁盘文件
  • 高并发频繁插入和修改索引，会导致性能损耗

使用时注意事项

  1. 高并发场景建议尽量创建少的索引

  2. 创建索引时，执行下列sql

    select count(discount(col)) / count(*)

    看看选择性，就是这个字段的唯一值在总行数的占比，如果过低，就代表这个字段的值其实都差不多或者类似，那创建索引几乎没什么意义，搜一个值定位到一大坨行，还得重新扫描
    如果一个字段的值几乎都不太一样，此时用索引的效果才最好

  3. 某字段是个很长的字符串，如果要建立索引，最好针对这个字符串的前缀来创建前缀索引，比如前10个字符。
    要用字符串的前多少位来创建前缀索引，取决于不同长度的前缀的选择性，一般前缀长度越长选择性值越高

事务（transaction）

ACID

  • Atomicity（原子性）
    事务中所有操作要么全部完成，要么全部不完成，不会在中间某个环节结束。如果事务在执行中发生错误，会回滚（rollback）恢复到事务开始前状态，好像这个事务从来没执行过一样。也就是说，事务不可分隔，简化
  • Consistency（一致性）
    事务开始前，结束后，数据库完整性没有被破坏
  • Isolation（隔离性）
    数据库允许多个并发事务对数据进行crud操作的能力。它可以防止多个事务并发操作时，不会因为参差进行导致数据不一致。隔离级别有下面几个:读未提交（read uncommitted）、读已提交（read committed）、可重复读（repeatable read）以及串行化（serializable）
  • Durability(持久性)
    事务结束后，对数据修改是永久的，即使系统故障，数据也不会丢失，状态也是事务结束后最新的状态

隔离级别不同引发的脏读、不可重复读、幻读

首先说明，在mysql里隔离级别是可以设置的，在MYSQL.ini文件里添加下列配置

transaction-isolation=REPEATABLE-READ

可用配置值有4个，每种级别引发的问题见如下列表

隔离级别配置值 引发问题
READ-UNCOMMITTED 脏读、不可重复读、幻读
READ-COMMITTED 不可重复读、幻读
REPEATABLE-READ 幻读（Mysql默认事务隔离级别）
SERIALIZABLE 无
  • 脏读
    一个事务能读取另外一个事务中还未提交的数据记录，如果这个未提交的事务回滚了，那么能读取未提交数据记录的事务就读到了脏数据
  • 不可重复读
    一个事务内，多次读取同一条数据记录，每次读取结果都不同
  • 幻读
    同一个事务内多次查询返回的数据记录结果集不同。原因是有另外一个事务新增、修改、删除了这个事务结果集里的数据记录，导致发生同一条数据记录内容被修改了或结果集的数据记录条数变多变少了

不可重复读针对的是一条数据记录的被修改，幻读针对的是多条数据记录的新增、修改、删除

注: MySQL事务隔离级别是通过锁和MVCC机制实现的。后续章节将对这两个依次说明

SQL调优

最简单的就是说一下怎么看sql的执行计划

explain select * from table
表字段 解释
table 表名
type 类型包括 all（全表扫描）、const（读常量，最多一条记录匹配）、eq_ref（走主键，一般就最多一条记录匹配）、index（扫描全部索引）、range（扫描部分索引）
possible_keys 显示可能使用的索引
key 实际使用的索引
key_len 使用的索引长度
ref 联合索引哪一字段
rows 一共扫描和返回了多少行
extra using filesort（需要额外进行排序）、using temporary（mysql构建了临时表，比如排序的时候）、using where（就是对索引扫出来的数据再次根据where来过滤出了结果）

稍微进阶就是使用show processlist查看当前库的所有连接信息，还有开启慢查询日志，查看慢查询sql

锁

锁类型

  • 表锁
  • 行锁
  • 页锁

两种引擎锁机制

  1. myisam会加表锁，读数据时候默认加个表共享锁，写数据时候加个表排他锁
  2. innodb行锁分为共享锁（S）、排它锁（X）
    • 共享锁:多个事务加共享锁读同一行数据，别的事务不能写这行数据
    • 排它锁:一个事务可以写这行数据，别的事务只能读，不能写
      innodb行锁通过给索引上的索引加锁来实现，因此只有通过索引条件检索数据时候，innodb才用行级锁，否则用表锁
  3. innodb表锁分为意向共享锁（IS）、意向排它锁（IX）
    • 意向共享锁，加共享行锁前，必须先加这个共享表锁
    • 意向排它锁，给行加排它锁前，必须先给表加排它锁，这是innodb自动加的，不需要手工操作
      insert、update、delete，会自动给行加行排它锁,select什么锁都不加，因为默认隔离级别是可重复读（见前述），多个事务对某行数据随便读，不会冲突

总结

面试时候重点说innodb的锁机制

针对某行数据，如果有一个事务在修改，它会加排它锁（X），同时获取到了表级的排它锁。
另外的事务不能修改，只能等待。但是可以随便读。
不同事务如果修改不同的行数据，会拿不同行的行级排它锁，但是大家都拿到了表级排它锁。表级排它锁可以随便拿，不冲突。
同一行数据同一时刻只能一个事务写，但可以同时多个事务读

悲观锁和乐观锁

  • 悲观锁: 事务拿数据时，认为会被别的事务修改，所以每次拿数据时候上锁，不让别的事务拿这条数据，直到悲观锁被释放
  • 乐观锁: 事务拿数据时，认为别的事务不会修改，所以不上锁，但是在提交更新时判断在它拿数据进行crud操作时，是否会有别的事务去操作这条数据

乐观锁可以手工实现，表里增加一个version字段，每次修改成功值+1，这样每次修改好提交时，看自己这个事务之前查询出来，准备修改的这条数据version字段值和数据库表里这条数据当前version字段值是否一致？不一致就不修改这条数据。

不推荐使用悲观锁，select ... for update语法会产生死锁问题

死锁

两个事务互相持有了一个悲观锁，然后还去请求对方手里持有的悲观锁，这样就死循环了，这就是死锁。

innodb的事务管理和锁定机制里，有专门检测死锁的机制，在系统产生死锁之后的很短时间内就能检测到死锁的存在。

注意: 当产生死锁的场景中涉及到不止innodb引擎时，innodb是没办法检测到死锁的，这时就只能通过锁定超时限制参数InnoDB_lock_wait_timeout来解决

死锁解决方法

  • 回滚较小的那个事务
  • 在REPEATABLE-READ隔离级别下，如果两个线程同时对相同条件过滤的数据用select … for update加悲观锁，在没有符合该条件的数据情况下，两个线程都会加锁成功。系统发现数据还不存在，会试图插入一条新数据，如果两个线程都这么做，就会出现死锁。这种情况下，将隔离级别改成READ COMMITTED，就可避免问题。

判断事务大小: 事务各自插入、更新或者删除的数据量

MVCC机制

概念

英语全称为Multi Version Concurrency Control，简称MVCC。一般我们称之为“数据多版本并发控制”

它是为了解决mysql事务隔离级别中出现的读一致性问题，用来保证同一个事务中前后两次读取数据结果一致(幻读问题)

实现原理

在每行数据记录后保存两个隐藏的列，这两个列一个保存了行创建时间(DATATRXID,大小为6个字节,插入为自增id)，一个保存了行过期时间（DATAROLLPTR，回滚指针，大小为7个字节）。但是存储的不是真实时间而是系统版本号（system version number）。每开始一个新事务，系统版本号都会自动新增，事务开始时刻的系统版本号会作为事务版本号，用来和查询到每行记录的版本号进行比较

看一下crud四种操作时，做了什么

  • SELECT
    InnoDB会根据以下条件检查每行记录
    1. 只查找版本小于(早于)当前事务版本的数据行，这样可确保事务读取的行要么在开始事务之前就已存在，要么是事务自身插入或者修改过的
    2. 行的删除版本号要么未定义，要么大于当前事务版本号，这样可确保事务读取到的行在事务开始之前未被删除
  • INSERT
    为新插入的每一行保存当前系统版本号作为行版本号
  • DELETE
    为删除的每一行保存当前系统版本号作为行删除标识
  • UPDATE
    为插入的一行新纪录保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为删除标识。保存这两版本号，使大多数操作都不用加锁。不足之处在于每行记录都需要额外的存储空间，需要做更多的行检查工作和一些额外的维护工作

示例说明

第1个事务

begin; 
insert into person values(1,'A'); 
insert into person values(2,'B'); 
commit;

假设事务ID从1开始自增，那么此时为

id name DATATRXID DATAROLLPTR
1 A 1 null
2 B 1 null

创建版本是当前事务ID，删除版本为空

第2个事务

执行第1次查询

begin; 
select * from person;

读取到两条原始数据，是“A”和“B”两个数据，这个时候当前事务ID是2,根据前述select操作只查找版本小于当前事务ID为2的数据行，即创建版本号为1的“A”和“B”

第3个事务

插入数据

begin; 
insert into person values(3,'C'); 
commit;

多了一条“C”数据，它的创建版本号是当前事务ID为3

id name DATATRXID DATAROLLPTR
1 A 1 null
2 B 1 null
3 C 3 null

第2个事务，执行第2次查询

begin; 
select * from person;

和第1次查询一样。不能查到第3个事务插入的数据“C”，“C”的创建版本号为3大于第2个事务ID即2，所以还是只能查到创建版本号为1的“A”和“B”两条数据

第4个事务

删除数据，删除id=2的“B”这条记录，

begin;
delete from person where id = 2;
commit;

此时变成如下表格

id name DATATRXID DATAROLLPTR
1 A 1 null
2 B 1 4
3 C 3 null

根据前述delete操作实现原理，“B”的回滚版本号被记录为当前事务ID即4，其他不变

第2个事务中，执行第3次查询

begin; 
select * from person;

前述可知，第2个事务ID为2，那么能查询到创建版本号小于2的数据“A”和删除版本号大于2的数据“B”，也就是说还是“A”和“B”两条数据

第5个事务，执行更新操作，这个事务ID为5

begin; 
update person set name='D' where id = 1;
commit;

第5个事务更新“A”数据，旧数据的回滚版本号被记录为当前事务ID即5(同时写入Undo Log)，产生一条新数据，创建版本号为当前事务ID为5

id name DATATRXID DATAROLLPTR
1 A 1 5
2 B 1 4
3 C 3 null
1 D 5 null

第2个事务，执行第4次查询

begin; 
select * from person;

还是根据前述select操作实现原理

首先查不到第3个事务插入的数据“C”，这是因为“C”创建版本号为3大于第2个事务ID即2，所以还是只能查到创建版本号为1的两条数据，即“A”和“B”

其次，第5个事务更新后，数据”D”的创建版本号为5大于2，代表是在第2个事务之后增加的，查不出来，而旧数据“A”的回滚版本号大于2，代表是在第2个事务之后删除的，可查。所以也还是只能查到创建版本号为1的两条数据，即“A”和“B”

通过以上示例，可知通过创建版本号与回滚版本号的查找规则，无论其他事务是插入、修改还是删除，第2个事务查询到的数据都没有变化，从而说明MVCC解决了幻读问题

但是如果查询操作在插入、修改、或者删除之后，同时这些操作的事务并没有提交时，按照MVCC机制查询的事务还是不能保证前后两次数据读取的一致性

Undo Log

在InnoDB引擎里，MVCC通过前述实现原理和Undo Log来一起实现读的一致性

它是为了实现事务的原子性而产生的
事务开始前，在对数据进行操作之前，先把操作的数据备份到一个日志文件里，以便在事务处理出现异常或者回滚时，MySQL可利用Undo Log中的备份数据恢复到事务执行前的状态，主要是用于回滚操作

MVCC通过和Undo Log结合一起实现读的一致性的根本原因是Undo Log保存了未提交之前的老版本数据，因此可以将其作为老版本快照便于其它事务来进行并发的读操作

快照读
普通的select查询就是快照读，它由缓存区(Undo buffer)和undo区(Undo Log)两部分组成

当前读
sql读取的是最新版本数据，加锁的数据读取都是当前读

Redo Log

它是为了实现恢复操作而产生的，是为了实现事务的持久性
Redo Log对事务中操作的任何数据，都把最新的数据备份到一个日志文件里，它不是事务提交后才写入日志文件，而是在事务执行过程中就开始写入Redo Log

Redo Log实现事务的持久性是指发生故障时，如果有脏页没有写入磁盘，在重启MySQL时，根据Redo Log进行重做，从而把没有写入磁盘的数据进行持久化

如下图，最新数据备份在redo log，如果发生故障，磁盘中数据不是最新数据，则进行重做把最新数据从redo log持久化到ibd磁盘文件中。undo log存放的是在进行操作之前的老数据，通过快照读方便其它事务并发进行读操作
redo log

架构

读写分离

高并发场景下，基本发生读操作的情况远远大于写操作，如果数据库读和写压力都同在一台服务器上，这显然不太合理。于是，把一台数据库主机分为单独的一台写主库（主要负责写操作），而把读的数据库压力分配给多台读的从库，这就是读写分离的典型场景，也就是一写多读

一写多读

如图，多个从库采用异步方式更新主库变化，写操作是在主库上进行，读操作则是在各从库上进行。

为了进一步降低数据库压力(高并发的瓶颈)，也会在业务层部署分布式缓存集群(redis)，把读压力转移给应用服务器端，其实与数据库主从设计是遵循同一个原则，就是为了降低后端数据库的压力

问题
由于延时（网络传输，操作）而引起的数据库主从数据不一致的问题

主从复制

MySQL之间数据复制的基础是二进制日志文件（binary log）

binary log

如图所示3个步骤

  1. 一台MySQL数据库一旦启用二进制日志后，作为主库，它的数据库中所有操作都会以“事件”方式记录在二进制日志中
  2. 其他数据库作为从库通过一个I/O线程与主库保持通信，并监控主库的二进制日志文件的变化，如果发现主库二进制日志文件发生变化，则会把变化复制到自己的中继日志（relay log）中
  3. 之后从库的一个sql线程会把相关“事件”执行到自己的数据库中，以此实现从库和主库的一致性，也就实现了主从复制

实现主从复制需要做的配置

  • 主库
    开启二进制日志
    配置唯一的server-id
    获得master二进制日志文件名及位置
    创建一个用于slave和master通信的用户账号
  • 从库
    配置唯一的server-id
    使用master分配的用户账号读取master二进制日志
    启用slave服务

上述这个复制过程在mysql里默认是异步的。主库在执行完客户端提交的事务后会立即将结果返给给客户端，并不关心从库是否已经接收并处理。所以如果主库挂掉了，此时主库上已提交的事务可能并没有传到从库上。这就产生了主从数据不一致的问题

主从数据不一致解决方案

半同步复制

介于异步和全同步复制之间，主库在执行完客户端提交的事务后不是立刻返回给客户端，而是等待至少一个从库接收到并写到relay log中才返回给客户端。相对于异步复制，半同步复制提高了数据的安全性，同时也造成了一定程度的延迟，这个延迟最少是一个TCP/IP往返的时间。所以，半同步复制最好在低延时的网络中使用

如图5.6和5.7版本半同步复制流程有略微差异

5.6和5.7版本半同步复制流程差异

原理

  • 事务在主库写完binlog后需要从库返回一个已接受，才返回给客户端
  • mysql5.5版本以后，以插件的形式存在，需要单独安装
  • 确保事务提交后binlog至少传输到一个从库
  • 不保证从库完成这个事务的binlog
  • 性能有一定的降低
  • 网络异常或从库挂机，卡主库，会一直到超时或从库恢复

优点

利用数据库原生功能，比较简单

缺点

主库的写请求时延会增长，吞吐量会降低

双主模式

改用双主多从架构，保证高可用

1.两台mysql主库都可读写，互为主备，默认只用一台（masterA）负责数据写操作，另一台（masterB）备用
2.masterA是masterB的主库，masterB又是masterA的主库，它们互为主从
3.两台主库之间做高可用,可采用keepalived等方案（使用VIP对外提供服务）
4.所有从库与masterB进行主从同步（双主多从）
5.建议采用高可用策略时，masterA或masterB均不因挂机恢复后，抢占VIP（非抢占模式）

双主模式

优点

一定程度上保证了主库的高可用,在一台主库挂掉后,可在极短的时间内切换到另一台主库上（尽可能减少主库挂机对业务造成的影响），减少了主从同步给主库带来的压力

缺点

  • masterB可能会一直处于空闲状态（可用它当从库，负责部分读操作）
  • 从库要等masterB先同步完了数据，才能去masterB上去同步数据，可能会造成一定程度的同步延时

分库分表

概念

  • 分库
    提升并发量。一个库一般最多支撑到并发 2000，就要扩容了，单库并发量最好保持在每秒1000左右。所以把数据分库到多个库中，访问时，访问一个库就好了

  • 分表
    单表单表数据量太大，会极大影响 sql 执行的性能。所以要把一个表的数据放到多个表中，然后查询时，就查一个表。比如按照用户 id 来分表，将一个用户的数据放在一个表中。操作时，对一个用户就操作那个表就好了。这样可以让每个表数据量在可控的范围内，比如每个表就固定在 200 万以内（mysql单表数据量我觉得最多也就200万左右，超过执行性能就很差）。

为啥要分库分表？

见下列表格

分库分表前 分库分表后
并发支撑情况 单机部署，扛不住高并发 从单机到多机，能承受的并发增加了多倍
磁盘使用情况 单机磁盘容量几乎撑满 拆分为多个库，数据库服务器磁盘使用率大大降低
sql执行性能 单表数据量太大，sql越跑越慢 单表数据量减少，sql执行效率明显提升

数据库拆分

目的

通过某种特定的条件，按照某个维度，将存放在同一个数据库中的数据分散存放到多个数据库上，达到分散单库负载的效果。

基本原则

  1. 优先考虑缓存，降低对数据库的读操作
  2. 再考虑读写分离，降低数据库写操作
  3. 最后开始数据拆分: 首先垂直拆分、再水平拆分
  4. 先考虑按照业务垂直拆分
  5. 再考虑水平拆分：先分库(设置数据路由规则，把数据分配到不同的库中)
  6. 最后再考虑分表，单表拆分到数据200万以内（个人经验单表200万足够了）

垂直拆分

把一个有很多字段的表给拆分成多个表，或者是多个库里。每个库表的结构都不一样，每个库表都包含部分字段。一般会将少量访问频率高的字段放到一个表里，然后将大量访问频率低的字段放到另外一个表里。因为数据库是有缓存的，访问频率高的行字段越少，可以在缓存里缓存更多的行，性能就越好。这个一般在表层面做的较多。

水平拆分

把一个表的数据给弄到多个库的多个表里去，但每个库的表结构都一样，只不过每个库表放的数据是不同的，所有库表数据合起来就是全部数据。水平分表意义，是将数据均匀放在更多的库里，然后用多个库来扛更高的并发，还有就是用多个库的存储容量来进行扩容。总结就是扛高并发，扩容

两种常用数据路由规则

  • 按照 range 来分
    就是每个库一段连续的数据放一张表，一般按时间范围来分，但是现在很少用，因为容易产生热点问题，大量的流量都打在最新的数据上，好处是扩容简单

  • 按照某个字段 hash 均匀分散
    这个较常用。可以平均分配每个库的数据量和请求压力;但扩容比较麻烦，要有一个数据迁移的过程，之前的数据要重新计算 hash 值重新分配到不同的库或表

分布式事务

2PC（Two-Phase Commit）

也就是所谓的XA方案

2PC

有一个事务管理器的概念，负责协调多个数据库（资源管理器）的事务，事务管理器先问各个数据库准备好了吗？如果每个数据库都回复 ok，那么就正式提交事务，在各个数据库上执行操作；如果任何其中一个数据库回答不ok，那就回滚事务。

事务管理器又被称之为协调者，数据库即资源管理器又被称之为参与者

这种分布式事务方案，比较适合单体应用，跨多个库的分布式事务，而且因为严重依赖于数据库层面来操作事务，效率很低，绝对不合适高并发场景。

某个系统内部如果出现跨多库的操作，其实是不合规的。现在微服务系统都要求每个服务只能操作自己对应的一个数据库。如果要操作其他服务对应的库，是不允许直连其他服务的库，这违反微服务架构的规范。随便胡乱交叉访问，可能会出现数据被别人改错，自己的库被别人写挂等情况。

如果给要操作其他服务的库，必须通过调用其他服务的接口来实现，绝不允许交叉访问其他服务的数据库。

具体流程

阶段1: 准备阶段

  1. 事务管理器向所有数据库发送事务内容，询问是否可以提交事务，并等待所有数据库答复
  2. 各数据库执行事务操作，将Undo和Redo信息记入事务日志中（但不提交事务）
  3. 如数据库执行成功，给事务管理器反馈YES，即可以提交；如执行失败，给事务管理器反馈NO，即不可提交

阶段2: 提交阶段

此阶段分两种情况: 所有数据库均反馈YES、或任何一个数据库反馈NO。
所有数据库均反馈YES时，即提交事务。
任何一个数据库反馈NO时，即中断事务。

提交事务: （所有数据库均反馈YES）

  1. 事务管理器向所有数据库发出正式提交事务的请求（即Commit请求）
  2. 数据库执行Commit请求，并释放整个事务期间占用的资源
  3. 各数据库向事务管理器反馈Ack完成的消息
  4. 事务管理器收到所有数据库反馈的Ack消息后，即完成事务提交

中断事务: （任何一个数据库反馈NO）

  1. 事务管理器向所有数据库发出回滚请求（即Rollback请求）
  2. 数据库使用阶段1中的Undo信息执行回滚操作，并释放整个事务期间占用的资源
  3. 各数据库向事务管理器反馈Ack完成的消息
  4. 事务管理器收到所有数据库反馈的Ack消息后，即完成事务中断

提交事务和中断事务

图上方是提交事务，下方是中断事务流程图

缺点

  • 同步阻塞: 所有参与事务的逻辑均处于阻塞状态
  • 单点: 事务管理器存在单点问题，如果事务管理器出现故障，数据库将一直处于锁定状态
  • 脑裂: 在阶段2中，如果只有部分数据库接收并执行了Commit请求，会导致节点数据不一致

3PC（Three-Phase Commit）

3PC是基于2PC思想演化而来的
事务提交过程分为CanCommit、PreCommit、DoCommit三个阶段来进行

3PC

具体流程

阶段1: CanCommit

  1. 事务管理器向所有数据库发出包含事务内容的CanCommit请求，询问是否可以提交事务，并等待所有参与者答复
  2. 数据库收到CanCommit请求后，如果认为可以执行事务操作，则反馈YES并进入预备状态，否则反馈NO

阶段2: PreCommit
此阶段分两种情况:

  1. 所有数据库均反馈YES，即执行事务预提交
  2. 任何一个数据库反馈NO，或者等待超时后事务管理器还无法收到所有数据库反馈，即中断事务

事务预提交:（所有数据库均反馈YES时）

  1. 事务管理器向所有数据库发出PreCommit请求，进入准备阶段
  2. 数据库收到PreCommit请求后，执行事务操作，将Undo和Redo信息记入事务日志中（但不提交事务）
  3. 各数据库向事务管理器反馈Ack响应或No响应，并等待最终指令

中断事务:（任何一个数据库反馈NO，或者等待超时后事务管理器还无法收到所有数据库反馈时，即中断事务）

  1. 事务管理器向所有数据库发出abort请求
  2. 无论是已收到事务管理器发出的abort请求，或者在等待事务管理器请求过程中出现超时，数据库均会中断事务

阶段3: do Commit
此阶段也存在两种情况:

  1. 所有数据库均反馈Ack响应，即执行真正的事务提交
  2. 任何一个数据库反馈NO，或者等待超时后事务管理器还无法收到所有数据库反馈，即中断事务

提交事务: （所有数据库均反馈Ack响应时）

  1. 如果事务管理器处于工作状态，则向所有数据库发出do Commit请求
  2. 数据库收到do Commit请求后，会正式执行事务提交，并释放整个事务期间占用的资源
  3. 各数据库向事务管理器反馈Ack完成的消息
  4. 事务管理器收到所有数据库反馈的Ack消息后，即完成事务提交

中断事务: （任何一个数据库反馈NO，或者等待超时后事务管理器还无法收到所有数据库反馈，即中断事务）

  1. 如果事务管理器处于工作状态，向所有数据库发出abort请求
  2. 数据库使用阶段1中的Undo信息执行回滚操作，并释放整个事务期间占用的资源
  3. 各数据库向事务管理器反馈Ack完成的消息
  4. 事务管理器收到所有数据库反馈的Ack消息后，即完成事务中断

注意:进入阶段3后，无论事务管理器自己出现问题还是事务管理器与数据库网络出现问题，都会导致数据库无法接收到事务管理器发出的do Commit请求或abort请求。此时，数据库都会在等待超时后，继续执行事务提交

优缺点

  • 优点: 降低了阻塞范围，在等待超时后事务管理器或数据库会中断事务。避免了事务管理器单点问题，阶段3中事务管理器出现问题时，数据库会继续提交事务
  • 缺点: 脑裂问题依然存在，即在数据库收到PreCommit请求后等待最终指令，如果此时事务管理器无法与数据库正常通信，会导致数据库继续提交事务，造成数据不一致（前述注意事项）

TCC

TCC 全称: Try、Confirm、Cancel。

  • Try 阶段
    这个阶段说的是对各个服务的资源做检测以及对资源进行锁定或者预留
  • Confirm 阶段
    这个阶段说的是在各个服务中执行实际的操作
  • Cancel 阶段
    如果任何一个服务的业务方法执行出错，那么这里就需要进行补偿，就是执行已经执行成功的业务逻辑的回滚操作。（把那些执行成功的回滚）

涉及支付、交易相关场景，会用 TCC，严格保证分布式事务要么全部成功，要么全部自动回滚，保证资金的正确性，确保资金不会出现问题。但最好各个业务执行的时间都比较短。如图

TCC

但是需要程序员自己手写回滚逻辑，或是补偿逻辑，业务代码很难维护

本地消息表

ebay出品

  • A 系统在自己本地一个事务里操作同时，插入一条数据到消息表
  • 接着 A 系统将这个消息发送到 MQ 中去；
  • B 系统收到消息之后，在一个事务里，往自己本地消息表里插入一条数据，同时执行其他的业务操作
    如果收到消息之后。发现这个消息已经被处理过了，那么此时这个事务会回滚，这样保证不会重复处理消息
  • B 系统执行成功之后，就会更新自己本地消息表的状态以及 A 系统消息表的状态
  • 如果 B 系统处理失败，那就不会更新消息表状态，那么此时 A 系统会定时扫描自己的消息表，如果有未处理的消息，会再次发送到 MQ 中去，让 B 再次处理
  • 保证了最终一致性，哪怕 B 事务失败了，但是 A 会不断重发消息，直到 B 那边成功为止。

本地消息表

严重依赖于数据库的消息表来管理事务，不适合高并发场景

可靠消息最终一致性方案

直接基于 MQ 来实现事务。比如阿里的 RocketMQ 就支持消息事务

  • A 系统先发送一个 prepared 消息到 mq，如果这个 prepared 消息发送失败那么就直接取消操作。
  • 如果 prepared 消息发送成功，那么接着执行本地事务，如果成功就告诉 mq 发送确认消息，如果失败就告诉 mq 回滚消息
  • 如果发送了确认消息，那么此时 B 系统会接收到确认消息，然后执行本地的事务
  • mq 会自动定时轮询所有 prepared 消息回调你的接口，询问 prepared 消息是不是本地事务处理失败了，所有没发送确认的消息，是继续重试还是回滚？这里可以查下数据库看之前本地事务是否执行了，如果回滚了，那么这里也回滚。这就避免了可能本地事务执行成功，而确认消息却发送失败的情况。
  • 如果系统 B 的事务失败了，就重试，直到成功。如果实在不行，要么就是针对重要的资金类业务进行回滚，比如 B 系统本地回滚后，想办法通知系统 A 也回滚；或是发送报警手工进行回滚和补偿。
  • 举用 RocketMQ 例子，或者自己封装一套类似的逻辑出来

可靠消息最终一致性方案

最大努力通知方案

  • 系统 A 本地事务执行完之后，发送个消息到 MQ
  • 这里会有个专门消费 MQ 的最大努力通知服务，这个服务会消费 MQ 然后写入数据库中记录下来，或是放入内存队列，接着调用系统 B 的接口
  • 要是系统 B 执行成功就 ok 了；要是系统 B 执行失败了，那么最大努力通知服务就定时尝试重新调用系统 B，反复 N 次，最后还是不行就放弃

阿里SEATA Saga模式

阿里开源的分布式事务解决方案seata中支持3种模式，其中TA和TCC模式可自行参考“参考资料”中提供的链接

这里重点说一下长事务解决方案Saga模式

SEATA

上图是阿里蚂蚁金服提供的Sage模式基本理论，它用一种非常纯朴的方式来处理一致性: 补偿。
见图右边部分流程图: 左侧是正常的事务流程，当执行到 T3 时发生错误，则开始执行右边的事务补偿流程，返向执行T3、T2、T1 的补偿服务，其中 C3 是 T3 的补偿服务、C2 是 T2 的补偿服务、C1 是 T1 的补偿服务，将T3、T2、T1 已经修改的数据补偿掉

适用场景

  • 适用于业务流程长且需要保证事务最终一致性的业务系统，它一阶段就会提交本地事务，无锁、长流程情况下可以保证性能

  • 事务参与者（资源管理器）可能是其它公司的服务或者是遗留系统的服务，无法进行改造和提供 TCC 要求的接口，可以使用 Saga 模式

优点

  • 一阶段提交本地数据库事务，无锁，高性能
  • 参与者可采用事务驱动异步执行，高吞吐
  • 补偿服务即正向服务的“反向”，易于理解，易于实现

缺点

  • Saga 模式由于一阶段已经提交本地数据库事务，且没有进行“预留”动作，所以不能保证隔离性

具体实现原理和使用见“参考资料”中提供的链接

参考资料

  1. 中华石杉–互联网Java进阶面试训练营
  2. 2小时学会MySQL索引优化
  3. 关于如何实现一个TCC分布式事务框架的一点思考
  4. SEATA Saga 模式
  5. SEATA AT模式
  6. SEATA TCC模式

推荐书单

中间件
中间件 数据库 Mysql

本博客所有文章除特别声明外，均采用 CC BY-SA 4.0 协议 ，转载请注明出处！