mysql相关

最新推荐文章于 2025-08-08 14:10:14 发布

原创最新推荐文章于 2025-08-08 14:10:14 发布 · 868 阅读

25 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库

文章目录

1、sql
2、执行一条语句，期间会发生什么
- 2.1 执行一条select语句的过程
- 2.2 执行一条update语句的过程
3、索引
4、事务
5、锁
6、日志
7、buffer pool
8、mysql主从复制
9、性能调优
10、分库分表

1、sql

1.0 mysql架构分层

mysql的架构分两层

server层：负责建立连接、语法分析和执行sql
存储引擎层：负责数据的存储和提取，支持InnoDB（默认）、MyISAM、Memory等存储引擎

1.1 InnoDB是如何存储数据的

记录是按照行来存储的，但数据库的读取并不以行为单位，否则每次读一行，效率很低

InnoDB的数据是按【数据页，默认16KB】为单位来读写的，读一条记录时，以页为单位整体读入内存

每个数据页有两个指针，指向上一个和下一个数据页，连起来的页相当于一个双向链表，逻辑上连续

在数据页中对记录进行分组，然后将组号记录到页目录，使其起到索引作用，通过二分快速定位到记录在哪个分组，再到分组里遍历找到记录

但是，存储大量数据的时候就需要多个数据页，就需要使用B+树作为索引，每个节点就是一个数据页

1.2 mysql单表不要超过2000w行？

单表数量限制：bigint上限，一般没到这个限制之前，mysql可能已经爆满了
表空间限制：每个数据页是16KB
IO次数、查询性能限制：超过2000w推荐值时，B+树层级可能会更高，影响查询性能

1.3 count(*)和count(1)有什么区别，哪个性能好

性能：count(*) = count(1) > count(主键字段) > count(字段)

select count(*) from t_order;     # 等价于count(0)

select count(1) from t_order;     # 统计表中有多少个记录，遍历主键索引，不会读取记录中任何字段的值，效率会高一些

select count(id) from t_order;    # 表中只有主键索引时，InnoDB遍历聚簇索引。表中如果有二级索引时，优化器会去优化，遍历二级索引（占用更少的存储空间，成本比遍历主键索引小）

select count(name) from t_order;  # 统计表中name字段不为NULL的记录，进行全表扫描来计数，效率最差；如果非要统计这个表中该字段不为NULL的记录个数，可以给这个字段建立一个二级索引，避免全表扫描

如果加了二级索引，count(*)还是很慢，要怎么优化？

使用近似值：通过show table status 或者 explain 命令
额外的表保存计数值

1.4 buffer pool有什么用

mysql数据存在磁盘中，当更新一条记录时，要先从磁盘读取该记录，然后在内存中修改这条记录，那么改完之后是直接写回磁盘还是缓存起来？

选择通过buffer pool缓存起来：避免下次查询命中时再去读磁盘
如果下次读取数据，这个数据在buffer pool，那么就会直接读取，否则就去读磁盘
修改数据时，如果数据在buffer pool，会直接修改数据所在的页，然后设置其为脏页，将本次对这个页的修改以redo log的形式记录下来（避免断电），在合适的时机将脏页写入磁盘（减少IO，不会马上写，WAL技术：先写日志，然后合适时间写到磁盘）

1.5 NOSQL和SQL的区别

SQL数据库：（Oracle、MySQL等关系型数据库）

存储结构化数据，逻辑上以行列二维表形式存在，每一列代表数据的一种属性，每一行代表一个数据实体
支持ACID（原子性、一致性、隔离性、持续性），数据间存在关联，水平拓展较难，要解决跨服务器join、分布式事务等问题

NoSQL数据库：（MongoDB、redis等非关系型数据库）

提供了不同于二维表的存储方式，可以时json文档、哈希表或其他方式
支持BASE（基本可用、软状态和最终一致性）、架构层面易拓展

1.6 数据库三大范式

第一范式（1NF）：要求数据库表的每一列都是不可分割的原子数据项

第二范式（2NF）：在1NF基础上，要确保表中每一列都是和主键相关的，而不能只与主键的某一部分相关（主要针对联合主键）

第三范式（3NF）：在2NF基础上，要确保表中每一列数据都和主键直接相关，而不能间接相关

1.7 mysql存储引擎有哪些、特性是什么

InnoDB：是mysql的默认存储引擎，有ACID事务支持、行级锁、外键约束等特性，适用于读写操作，支持较好的数据完整性和并发控制

由于它在事务支持、并发性能、崩溃恢复方面有优势，被选为默认的存储引擎
事务支持：ACID（原子性、一致性、隔离性、持久性）
并发性能：采用行级锁定机制，可以提供更好的并发性能
崩溃恢复：通过redolog日志实现崩溃恢复，保持数据的持久性和一致性

MyISAM：是mysql的另一种存储引擎，具有较低的存储空间和内存消耗，适用于大量读操作场景，但是不支持事务、行级锁和外键约束，只支持表锁（粒度较大）

和InnoDB的区别：
- 事务：InnoDB支持事务，MyISAM不支持
- 索引结构：InnoDB是聚簇索引，必须要有主键；MyISAM是非聚簇索引，数据和文件是分离的
- 锁的粒度：InnoDB最小锁粒度是行锁，MyISAM最小锁粒度是表锁（一个更新语句会锁住整张表）
- count的效率：InnoDB不保存表的行数，select count(*) from table需要全表扫描；MyISAM用一个变量保存了表的行数

Memory：把数据存储在内存中，适用于对性能较高的读操作，但服务重启或崩溃时数据回丢失，也不支持事务、行级锁和外键约束

2、执行一条语句，期间会发生什么

2.1 执行一条select语句的过程

a. 经过连接器

连接Mysql服务（TCP），校验用户名、密码、权限

# 查看mysql服务被多少个客户端连接
$ show processlist

# 空闲连接不会一直占用，定义了空闲连接的最大空闲时长，默认8小时
$ show variables like 'wait_timeout';

# mysql连接器有限制max_connections
$ show variables like 'max_connections';

# 如何解决长连接占用的问题
1、定期断开长连接
2、客户端主动重置连接

b. 查询缓存

c. 经过解析器解析sql

词法分析：select username from userinfo，获取到4个token，2个keyword（select、from）
语法分析：检查语法是否正确

d. 执行sql

prepare预处理阶段：预处理器做字段检查、代码展开
optimize优化阶段：优化器做sql执行计划
execute执行阶段：执行器和存储引擎交互
- 主键索引查询
- 全表扫描：查询条件没有用到索引，优化器决定用访问类型为ALL进行查询
- 索引下推：减少二级索引在查询时的回表操作，提高查询效率

2.2 执行一条update语句的过程

执行一条 update t_user set name='xiaolin' where id = 1; 的流程如下：

1、执行器负责执行，调用存储引擎接口，通过主键索引获取 id=1 的记录

如果 id=1 这一行所在的数据页在buffer中，返回给执行器更新
如果不在，就把数据页从磁盘读入buffer，返回记录给执行器

2、执行器得到记录，会看更新前后的记录是否一样

不一样才把更新前后的记录都当作参数传给innodb层，让innodb执行更新记录操作；否则什么也不做

3、innodb开启事务：更新记录前，记录响应的undolog（记录旧值）

4、innodb更新记录：先更新内存（同时标记为脏页），把记录写到redolog，由后台线程的WAL技术找时机把脏页写到磁盘（减少IO）

5、数据更新完成：开始记录该语句的binlog，暂存到binlog cache，在事务提交时统一把该事务运行过程中的所有binlog刷到硬盘

6、两阶段事务提交：prepare阶段、commit阶段

7、更新语句执行完成

3、索引

索引是数据的目录，索引和数据位于存储引擎中，通过索引查找某行数据时，先从磁盘读取索引到内存，再通过索引从磁盘中找到数据，涉及多次磁盘IO

mysql的索引数据结构，需要满足：

尽可能少的磁盘IO中完成查询
能高效查询某个记录，高效进行范围查找

索引数据最好能按序排列，这样可以使用二分查找（查询时间复杂度O(logn), 但是插入时间复杂度很低，所有元素要后移，在磁盘中不能接受）

引入二叉查找树（左子树所有节点小于根节点，右子树所有节点大于根节点）

极端情况下，每次插入的都是二叉查找树中最大的元素，这样这棵树退化为链表（查询时间复杂度为O(n)，数据存储在每个节点，这样磁盘交互越多）

引入平衡二叉树AVL（左右子树高度差不能超过1），但是插入元素越多，树高越高，也会增加IO磁盘操作

于是需要把二叉树变为多叉树，来减少树高，引入B树，一个M阶B树，每个节点最多有M-1个数据和最多M个子节点，超过就会分裂节点

但是B树的每个节点都包含（索引+记录），用户数据大小可能远远超过索引数据，非目标数据也会加载到内存，引起浪费；而且不利于进行范围查询

最终引入B+树：
- 叶子节点才存放实际数据（索引+记录）；数据量相同情况下，相比既存索引又存记录的B树，非叶子节点只存放索引，会更矮胖，磁盘IO更少
- 所有索引都会在叶子节点出现，叶子节点之间是双向链表，利于范围查询
- 插入和删除效率高，甚至可以不用动非叶子节点，甚至不用变形

二分查找 -> (插入问题) -> 二分查找树 -> (退化问题) -> AVL树 -> (树高问题) -> B树 -> (范围查询，树形调整问题) -> B+树

索引类似书的目录，可以

减少扫描的数据量，提高查询效率（数据量较大时，没用索引就要全表扫描，时间复杂度是O(n)；建索引后只需要扫描索引表即可）
优化数据结构（将数据按一定规则排序和组织，提高效率）
约束和限制数据，避免产生数据错误

索引的实现：

B+树，叶子节点才存放数据，非叶子节点只存放索引，且每个节点里的数据都是按主键顺序存放（页目录中通过槽查找记录可以用二分找到记录在哪个槽然后遍历找到记录），读一个节点就是一次磁盘IO（索引和数据都存在硬盘）

叶子节点包括了所有索引值信息，每个叶子节点都有前后两个指针，形成双向链表（插入、删除方便，但检索效率不高，最差需要遍历）

3.0 B+树和B树的特点

B+树：

所有叶子节点都在同一层，存储实际的数据记录或指向数据记录的指针，每次搜索都会叨叨叶子节点，确保所有数据项的检索都有相同的IO延迟
叶子节点连成双向链表，适合范围查询和排序扫描，可以沿着叶子节点的链表顺序访问数据，无需多次随机访问
非叶子节点仅存储键值和指向子节点的指针（用来指导搜索路径，快速定位到正确的叶子节点），不包含数据记录
B+树有大量冗余节点（非叶子节点都是），让数据在插入、删除和更新操作效率很高并且会自动重新平衡，确保树高保持相对稳定，不会像B树那样发生复杂变化

B树：

非叶子节点既存储索引信息也存储部分数据（B+树可以更矮胖，查询底层节点的磁盘IO次数更少），在非叶子节点可能就能找到数据，性能相对不稳定
叶子节点没有链表连接，不利于范围查询，只能通过树的遍历完成，涉及多个节点的磁盘IO

3.1 索引分类

a. 按数据结构分

B+Tree索引：B+树是多叉树，叶子节点才存放数据，非叶子节点只存放索引的值。每个节点中的数据都是按主键顺序存放的。每个叶子节点都有两个指针，分别指向前后两个叶子节点，形成一个双向链表。索引和数据都存在硬盘，每读取一个节点就是一次磁盘IO，千万级数据在B+树中查询效率很高，只需要3-4次磁盘IO
Hash索引
Full-Text索引

b. 按物理存储分

聚簇索引（主键索引）：B+树的叶子节点存放完整的用户记录实际数据

如果有主键，默认使用主键作为聚簇索引的索引键
如果没有主键，就选第一个不包含NULL的唯一列作为聚簇索引的索引键
上面两个都没有，就自动生成一个隐式自增ID列作为聚簇索引的索引键

id为主键索引
select * from product where id = 5;

叶子节点
5 -> 0001 | apple | 6.0

非聚簇索引（二级索引）：B+树的叶子节点存放的是主键值，而不是实际数据。过程如下
- 首先检索二级索引中的B+树索引值（pro_no），找到对应的叶子节点获取主键值
- 然后通过主键索引中的B+树找到对应的叶子节点获取整行数据——回表，查两个B+树才能找到数据

pro_no为二级索引，使用该二级索引查询商品
select * from product where pro_no = '0002';

叶子节点
0002 -> 2

如果找的数据能在二级索引的B+树叶子节点中找到，就不需要回表（覆盖索引），例如
select id from product where pro_no = '0002';

c. 按字段特效分

主键索引：建立在主键上的索引（PRIMARY KEY），一张表最多一个主键索引，不允许有空值
唯一索引：建立在UNIQUE字段上的索引（UNIQUE KEY），一张表可以有多个唯一索引，索引列的值必须唯一，允许有空值
普通索引：建立在普通字段上的索引（INDEX），不要求字段为主键/UNIQUE
前缀索引：对字符类型字段的前几个字建立的索引，而不是在整个字段上建立的索引。目的是减少索引占用的存储空间

d. 按字段个数分

单列索引：建立在单列上的索引
联合索引：建立在多列上的索引，使用联合索引查询时，按联合索引的顺序来进行数据排序查找

在使用联合索引时，存在最左匹配原则，如果不遵循最左匹配原则，联合索引就会失效

例如，创建了一个(a, b, c)联合索引，下面的查询条件可以匹配上联合索引
where a = 1;
where a = 1 and b = 2 and c = 3;
where b = 2 and a = 1;(查询优化， a的顺序不重要)
而下面的查询条件匹配不上
where b = 2;
where b = 2 and c = 3;

利用索引的前提是key是有序的，并不是查询过程中使用了联合索引查询，就代表联合索引中所有字段都用到了联合索引进行查询
也可能存在着联合索引中不分字段用到了联合索引，部分字段没有用到的情况

3.2 联合索引和最左前缀原则

建立联合索引时，应该把区分度大的字段排在前面，这样区分度大的字段越有可能被更多的SQL用到

优化器如果发现区分度不超过30%时，一般会忽略索引，进行全表扫描

最左前缀原则：联合索引在使用时，必须从最左边的列开始匹配，且匹配顺序不能跳过（要满足连续匹配）

索引下推（ICP）：

将非索引字段的过滤条件下推到存储引擎索引扫描阶段执行

优化器的一种技术，将部分过滤条件下推到存储引擎层，在索引扫描阶段就进行过滤，减少回表的次数和数据量，进而提升查询效率
存储引擎在扫描索引叶子节点时，先根据索引字段条件过滤，再用下推条件过滤索引叶子节点对应的行数据（不回表，直接判断索引叶子节点中的列值）
如果索引条件不能完全覆盖查询条件（例如联合索引中间字段缺失，或者条件涉及非索引字段），就会扫描出大量索引行指针，导致大量回表
只有当查询的过滤条件中包含索引字段以外的字段时，ICP才有意义

WHERE A = 10 AND C < 20
A=10 可以用索引范围定位。
C<20 不能连续匹配（中间缺 B），不能直接用索引范围扫描。
传统执行会先扫描所有 A=10 的索引行指针，然后回表判断 C<20。
使用ICP后，存储引擎在扫描索引叶子节点时，直接判断 C<20，不满足的索引行指针不返回，减少回表

联合索引(a,b,c) ，查询条件 where b > xxx and a = x ，写法中 b 条件在前，但MySQL优化器会自动重排序条件，先用 a = x 定位范围，再用 b > xxx 过滤
联合索引 (a, b, c) 的最左前缀是：
第一列 a
第一列和第二列 (a, b)
第一列、第二列和第三列 (a, b, c)
a = x 是最左列的等值条件，定位索引范围起点。
b > xxx 是第二列的范围条件，限制索引扫描范围。
这符合联合索引的最左前缀原则，索引扫描可以利用 a 和 b 两列。

3.3 什么情况会发生索引失效

1、对索引使用左（like %xx）或者左右模糊（like %xx%）匹配时

例如name like %林查询的结果可能是【陈林、周林】，不知道从那个索引值开始比较，只能全表扫描

2、在查询条件中对索引列使用函数或进行表达式计算

索引保存的是索引字段的原始值，而不是经过函数计算后的值
但是mysql 8.0以后支持了函数索引，可以对函数计算后的值建立一个索引

3、对索引隐式类型转换，字符串和数字进行比较时，会自动把字符串转数字再进行比较——隐式类型转换CAST函数作用于字符串索引列时

索引字段是字符串类型，但是查询条件中输入的参数是整形的话，就会走全表扫描
但是如果索引字段是整型，查询条件中输入的参数是字符串的话，还是可以走索引
问题就是：mysql的数据类型转换规则是自动会把数字转换成字符串

4、联合索引使用破坏了最左匹配原则时

查询条件中的各个列必须是联合索引中从最左边开始连续的列

5、where子句中，or前是索引列，or后不是索引列

3.4 索引优化方法

1、前缀索引优化：前缀索引是为了减小大字符串字段中索引字段大小，增加一个索引页中存储的索引值，提高索引的查询速度

2、覆盖索引优化：在二级索引中就能查到记录，不需要通过聚簇索引查询获得，避免回表。比如建立【商品ID、名称、价格】的联合索引，而我们要查的就是这三个

3、主键索引最好是自增的、长度也不要太大：插入新纪录时都是追加，不需要移动数据。相较于随机的非自增主键，避免了数据移动页分裂，索引结构也不紧凑，影响查询效率；另外主键长度越小，二级索引的叶子节点越小，占用的空间也越小

4、索引最好设置为NOT NULL：优化器难以优化NULL；NULL没意义但占用物理空间（至少1字节）

5、防止索引失效：

3.5 什么时候需要索引，什么时候不需要

索引能够提高查询速度，但是也有缺点：

占用物理空间
创建索引和维护索引需要耗费一定资源
降低表的增删改效率，因为B+树为了维护索引有序需要动态维护

什么时候用：

1、主键：具有唯一性，唯一标识一条记录，通常是表中的一个自增长字段或者是一个业务相关的字段

字段最好是递增的有序的，避免随机无序的字段可能导致发生页分裂

2、外键：是关联两个表的字段，通常是一个表中的主键在另一个表中的引用，常用于关连查询

外键约束维护的是表和表之间的关系，确保数据的完整性和一致性；例如学生表中的学生只可以选在课程表中的课程

3、常用于查询、排序、分组的字段

如果查询条件不是一个字段，可以建联合索引
查询时不需再做一次排序，建立索引之后在B+树中的记录都是排序好的，查询时就不需要再去做一次排序了

什么时候不用：

1、业务数据最好不要用来作为主键，因为未来可能因为业务需要出现业务字段重复/重用等情况

2、整个表区分度太低的字段不适合当主键，这样建索引得进行回表，开销变大，而且优化器会选成本较小的全表查询，这样索引没起到加快查询作用反而还占用了空间

3、通常自增字段做主键对单机系统没问题，如果多台服务器各自都可以录入数据且要合并，可能会主键重复，要考虑分布式ID方案

但是使用uuid时，innodb无法总是把新行插入到索引最后，而是需要为新行寻找新的合适位置来分配新空间，需要频繁做页分裂操作，导致移动大量的数据，影响性能
频繁的页分裂，页会变得稀疏并被不规则填充，最终导致数据有碎片
uuid由36个字符组成，占用页内存（页大小是固定的），一个页上能放的关键字数量就比较少，导致索引树变高，发生磁盘IO次数越多

4、表数据太少、经常更新的字段，维护索引有序会影响性能

4、事务

事务是保证数据一致性和完整性的重要机制，MyISAM引擎不支持事务，所以Mysql的默认引擎是InnoDB

4.1 事务的特性

1、原子性：一个事务中的所有操作，要么全部执行成功，要么全部失败。异常发生会回滚到事务开始前的状态

通过undo log（回滚日志）来保证

2、一致性：事务执行前后，数据库的状态须保持一致，变化后的状态必须符合数据库的约束和完整性

通过持久性+原子性+隔离性来保证

3、隔离性：多个事务使用相同数据时，对其他并发的事务是隔离的

通过MVCC（多版本并发控制）或者锁机制保证

4、持久性：事务执行成功后，对数据的修改就是永久的，不会丢失

通过redo log（重做日志）来保证

使用事务时，要注意：

1、事务的边界：事务应尽量缩小到只包含必要的操作

2、锁定资源：事务执行时要锁定相关的资源，且锁定的资源范围不宜过大

3、回滚和提交：事务执行失败要立即回滚；事务执行成功要立即提交

4、并发控制：事务的隔离性要通过并发控制（锁定机制和隔离级别）来实现

4.2 并发事务会导致什么问题，如何解决

严重性：脏读 > 不可重复读 > 幻读

脏读：一个事务读到了另一个未提交事务修改过的数据
不可重复读：一个事务内多次读同一个数据的结果不一样
幻读：一个事务内前后两次查到的记录数量不一样

解决方式：

锁机制：mysql提供了行级锁、表级锁、页级锁等，确保只有一个操作能够访问/修改数据
事务隔离级别：mysql提供了读未提交、读已提交、可重复读、串行化的事务隔离级别，在多个事务并发执行时控制事务间的隔离程度，避免数据不一致
- 读未提交：保证一个事务还没提交时，它做的变更就能被其他事务看到——无法处理并发问题
- 读提交：保证一个事务提交后，它做的变更才能被其他事务看到——处理脏读。通过【每个select语句执行前】生成的readview实现
- 可重复读：保证一个事务执行过程中看到的数据和启动时是一样的——处理脏读、不可重复读。通过【启动事务时，第一个select前】生成的readview实现
  - 这是默认隔离级别，避免不了幻读，所以尽量在开启事务之后马上执行select … for update这类锁定读的语句（会对记录加next-key lock），从而避免其他事务插入一条新纪录，避免幻读
- 串行化：保证多个事务发生读写冲突时，后访问的事务要等前一个事务执行完成——处理脏读、不可重复读、幻读。
  - 通过【加读写锁】实现，普通的select查询会加S型next-key lock，其他事务就没法对这些加锁的记录进行增删改了
MVCC：mysql用MVCC管理并发访问，在数据库中保存不同版本的数据来实现不同事务间的隔离
- MVCC通过版本链来控制并发事务访问同一个记录时的行为，允许多个事务同时读取同一行数据而不会彼此阻塞，每个事务看到的数据版本都是该事务开始时的数据版本（即使其他事务在此期间修改了数据，实现了非阻塞读）

4.3 事务的隔离级别以及如何实现

SQL标准提出了4种隔离级别来规避脏读、不可重复读、幻读

1、读未提交：一个事务没提交时，它做的变更就能被其他事务看到

可能会出现脏读、不可重复读、幻读
实现：可以读到未提交事务修改的数据，所以直接读取最新数据即可

2、读提交：一个事务提交后，它做的变更才能被其他事务看到

可能会出现不可重复读、幻读
实现：通过read view实现，可以理解为数据快照，【在每个语句执行前】会生成一个read view

3、可重复读（mysql innoDB默认隔离级别）：一个事务执行过程中看到的数据跟事务启动时看到的是一致的

可能会出现幻读，但是可以很大程度的避免幻读，但不能避免，解决方案为：
a。针对快照读（select）：通过MVCC方式（事务的read view里的字段和记录中的两个隐藏列的比对，控制并发事务访问同一个记录时的行为）解决，版本链控制，不会加锁
b。针对当前读（select…for update，每次都是读最新的数据）：通过记录锁+间隙锁方式解决幻读，加锁后，其他事务在锁范围内插入记录会被阻塞
但是串行化又比较消耗性能，所以，尽量在开始事务以后，马上执行select… for update这类当前读语句，因为会加锁，从而避免其他事务插入一条新纪录
实现：通过read view实现，可以理解为数据快照，【在启动事务时】生成一个read view，然后整个事务期间都适用这个read view

4、串行化：对记录加读写锁，多个事务同时操作这条记录时，后访问的事务必须等前一个事务执行完才能执行

都不会发生

有一张账户余额表，里面有一条账户余额为 100 万的记录。然后有两个并发的事务，事务 A 只负责查询余额，事务 B 则会将我的余额改成 200 万，下面是按照时间顺序执行两个事务的行为
｜	启动事务A	｜	启动事务B	｜
｜查得余额100w	｜				｜
｜				｜	查得余额100w	｜
｜				｜	修改为200w	｜
｜查得余额v1		｜				｜
｜				｜	提交事务B	｜
｜查得余额v2		｜				｜
｜提交事务A		｜				｜
｜查得余额v3		｜				｜
	   

- 在「读未提交」隔离级别下，事务 B 修改余额后，虽然没有提交事务，但是此时的余额已经可以被事务 A 看见了，于是事务 A 中余额 V1 查询的值是 200 万，余额 V2、V3 自然也是 200 万了；
- 在「读提交」隔离级别下，事务 B 修改余额后，因为没有提交事务，所以事务 A 中余额 V1 的值还是 100 万，等事务 B 提交完后，最新的余额数据才能被事务 A 看见，因此额 V2、V3 都是 200 万； 
- 在「可重复读」隔离级别下，事务 A 只能看见启动事务时的数据，所以余额 V1、余额 V2 的值都是 100 万，当事务 A 提交事务后，就能看见最新的余额数据了，所以余额 V3 的值是 200 万； 
- 在「串行化」隔离级别下，事务 B 在执行将余额 100 万修改为 200 万时，由于此前事务 A 执行了读操作，这样就发生了读写冲突，于是就会被锁住，直到事务 A 提交后，事务 B 才可以继续执行，所以从 A 的角度看，余额 V1、V2 的值是 100 万，余额 V3 的值是 200万

5、锁

根据加锁的范围，包括全局锁、表级锁和行锁三类

一个定时任务，批量处理订单，先锁订单，再返回库存。支付回调正好相反，先锁库存再更新订单状态。两边互相等待，出现死锁

抓死锁日志：show engine innodb status，看是哪两个线程再互相等待

如何解决上面的问题？

把大批量的操作拆成小批次
统一加锁的顺序，先锁库存，再锁订单

行级锁：InnoDB的特产，支持高并发，分为

共享锁 for share：适合读操作时用来防止数据被修改，多个事务可以同时加共享锁，但会阻止排他锁的写入
排他锁 for update：适合精确锁住一行，比如处理未支付的订单，别人改不了
间隙锁：存在于可重复读隔离级别防幻读使用，锁住两个索引之间的缝，防止偷偷插入数据
适用场景：适合频繁的单行操作如订单系统中的订单修改删除等，锁粒度较小，能精确控制对表中某行数据的访问，减少了锁竞争的可能，提高并发性能

5.1 全局锁

全局锁：锁住整个数据库，常用于备份、全库只读，谁都别写，动一下直接报错（不会因为数据/表结构更新，出现备份文件的数据和预期不一致）

使用全局锁和释放全局锁，执行

flush tables with read lock

unlock tables

执行后，整个数据库就处于只读状态，此时其他线程的以下操作都会阻塞，这样不会影响数据库备份：

对数据的增删改操作
对标结构的更改操作

备份期间，业务只能读数据而不能更新数据，有什么方式可以避免？

如果数据库引擎支持【可重复读】的隔离级别，那么在备份数据库之前开启事务，创建read view，这样整个事务执行期间都使用这个快照，而且由于MVCC的支持，备份期间业务仍然可以对数据进行更新操作

mysqldump就是这个原理，加上-single-transaction时，就会在备份之前开启事务

5.2 表级锁

表级锁：MyISAM的特产，适合读多写少；

适用场景：适合大批量操作如表的重建、大量数据加载等，保证数据完整和一致性，锁粒度较大，可能会引起锁竞争和性能问题

分为

5.2.1 表锁

表锁的颗粒度较大，除了限制别的线程的读写外，也会限制本线程接下来的读写，会影响并发性能

// 读锁：允许当前会话读，阻塞其他会话对表的写
lock tables t_student read;

// 写锁：允许当前会话读，阻止其他会话对表的读写
lock tables t_student write;

// 释放当前会话的所有表锁
unlock tables;

5.2.2 元数据锁（MDL）

（MDL写锁）改表结构时别人查都查不了；（MDL读锁）对一张表做CRUD时，避免其他线程对表结构做变更

不需要显式使用MDL，当我们对数据库表操作时，会自动给这个表添加MDL，保证在操作表CRUD时，其他线程不能修改表结构：

对一张表进行CRUD时，加的是MDL读锁
对一张表进行表结构修改时，加的是MDL写锁

MDL是在事务提交后才会释放，事务执行期间，MDL是一直持有的

如果有一个长事务（事务开启后一直没提交），这时如果其他线程因为加写锁阻塞了，此时申请MDL锁的会进行排队，队列中写锁优先级高于读锁，那么其他线程的后续语句都会被阻塞
所以在对表进行结构变更前，要先确认是否有长事务已经对表加上了MDL锁，可以考虑kill掉这个长事务再做表结构变更

5.2.3 意向锁

意向锁是指：执行插入/更新/删除操作时，先对表加上【意向独占锁】，然后对该记录加独占锁。相当于提前打声招呼，加速判断是否会出现加锁冲突

在使用InnoDB引擎的表里，对某些记录加上【共享锁/独占锁】之前，要先再表级别加上【意向共享锁/意向独占锁】，意向锁是表级别的锁，不会和行级的共享锁/独占锁发生冲突，只会和共享表锁/独占表锁发生冲突

即当执行插入、更新、删除操作，需要先对表加上【意向独占锁】，然后对该记录加独占锁
普通的select语句是不会加行级锁的，利用MVCC实现的一致性读，是无锁的
意向锁的目的是为了快速判断表里面是否有记录被加锁，有了意向锁，在对记录加独占锁之前，会先加上表级别的意向独占锁，避免在加独占表锁的时候需要去遍历表里面的记录

5.2.4 ANTO-INC锁

在插入数据时，会加一个表级别的AUTO-INC锁，赋予被AUTO_INCREMENT修饰的字段递增值，在执行完插入语句后立刻释放，而不是事务提交后才释放

5.3 行级锁

InnoDB支持行级锁，MyISAM不支持行级锁，主要有三类：

记录锁：仅对一条记录加锁，分为S锁和X锁
- S锁和S锁兼容，S锁和X锁不兼容，X锁和X锁不兼容

mysql > begin;
mysql > select * from t where id = 1 for update;  // 为t表中主键id为1的记录加上X锁，这样其他事务则无法修改这条记录

// 事务commit后，事务过程中生成的锁都会被释放

间隙锁：锁定一个范围，但不包括记录本身
- 只存在于可重复读隔离级别，目的是为了解决这个隔离级别下的幻读
- 假设表中有范围id为（3，5）的间隙锁，那么其他事务就无法插入id=4的记录，防止幻读发生
Next-Key临键锁：上面的组合，锁定一个范围，并且锁定记录本身，既能保护该记录，又能阻止其他事务将新的记录插入到被保护记录前面的间隙中
- 假设表中有范围id为（3，5] 的临键锁，那么其他事务无法插入id=4的记录，也不能修改id=5的这条记录
- 如果一个事务获取了X锁，那么另一个事务在获取相同范围的X锁时，是会被阻塞的

5.4 mysql死锁了怎么办

订单不能重复，在新增订单时会做幂等性校验，做法是在新增订单记录前，通过select ... for update来查询订单是否存在，不存在才插入。这种方式在业务量较大时，容易出现死锁

死锁的4个必要条件：互斥、占有且等待、不可强占、循环等待，发生死锁时，这些条件必然成立，破坏任意一个就能打破死锁
数据库层面有两种策略来解除死锁：
- 设置事务等待锁的超时时间：当一个事务等待时间超过该值，就对这个事务进行回滚来释放锁。进而另一个事务得以执行，默认是50s
- 开启主动死锁检测：主动死锁检测发生在死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行

5.5 一条update是不是原子性的，为什么

是原子性的，主要通过锁+undolog日志保持原子性

执行update时，会加行级锁，保证一个事务更新一条记录时不会被其他事务干扰
事务执行过程中会生成undolog，如果事务失败了则用undolog回滚

5.6 两个线程update语句同时处理一条数据是否会阻塞

会阻塞，如update ... where id = 1，因为innodb实现了行级锁。当事务A对id=1这行更新时，会对主键id为1的记录加X类型的记录锁，这样事务B对id=1的记录进行更新时，发现已经有记录锁了，就会陷入阻塞状态

5.7 如果2个范围不是主键或索引，是否还会阻塞

如果2个范围查询的字段不是索引，代表update没有用到索引，此时触发全表扫描，全部索引都会加行级别锁，此时第二条update执行时就会阻塞

6、日志

6.0 mysql日志分类

重做日志redolog：是innodb存储引擎层生成的日志，实现了事务中的持久性，用于掉电等故障恢复（buffer pool是基于内存来提高读写效率的，不可靠，掉电后没落盘的脏页就会丢失）

有记录要更新时，innodb会先更新内存（标记为脏页），然后会将本次对这个页的修改以redolog形式记录下来。后续在适当的时候由后台线程将缓存在buffer pool的脏页刷到磁盘里（WAL）
事务提交时，只要先将redolog持久化到磁盘即可，不用等buffer pool里的脏页数据持久化到磁盘。这样系统崩溃时就可以用redo log恢复数据
WAL：mysql的写操作并不是马上写到磁盘，而是先写日志，然后在合适的时间写到磁盘上

回滚日志undolog：是innodb存储引擎层生成的日志，事务没提交前会记录更新前的数据到undolog，可以实现事务中的原子性，用于事务回滚和MVCC

redolog记录的是事务【完成后】的数据状态，记录的是更新之后的值，提交前存在redolog buffer pool；undolog记录的是事务【开始前】的数据状态，记录的是更新之前的值

二进制日志binlog：是server层生成的日志，是追加写，写满一个文件就创建一个新的文件继续写，保存的是全量非查询日志，用于数据备份和主从复制

使用两阶段提交来保证和redolog的一致性（redolog和binlog都要持久化到磁盘，但各自是独立的，可能出现半成功状态），以binlog写成功为事务提交成功的标识
将redolog的写入拆成prepare和commit，中间穿插写入binlog。
prepare：事务XID写入redolog，把redolog对应的事务状态设置为prepare，然后redolog持久化到磁盘
commit：事务XID写入binlog，把binlog持久化到磁盘，调用事务提交，把redolog状态设置为commit
mysql重启后会按顺序扫描redolog，处于prepare阶段的redolog，即可以回滚事务（在binlog中找不到XID，binlog还没刷盘），也可以提交事务（在binlog中找到了XID，都已刷盘）

中继日志relaylog：用于在主从复制场景下，slave通过io线程拷贝master的binlog后本地生成的日志

慢查询日志：用于记录执行时间过长的sql，需要设置阈值后手动开启

6.1 undo log

undo log是一种用于撤销回退的日志，事务没提交之前，mysql会记录更新前的数据到undo log，事务回滚时利用它来回滚，作用主要是：

实现事务回滚，保障事务的原子性
实现MVCC（MVCC是通过readview+redo log实现的，读提交隔离级别是在select时生成一个readview，可重复读隔离级别是在启动事务时生成一个readview）

一条记录的每一次更新操作产生的undo log格式都有一个roll_pointer指针和一个trx_id事务id：

通过trx_id 可以知道该记录是被哪个事务修改的
通过roll_pointer指针可以将undo log串成一个版本链（链表）

6.2 redo log

redo log是物理日志，记录了某个数据页做什么修改，每执行一个事务就会产生这样的日志，事务提交时，redo log会持久化到磁盘（有缓存、redo log有两个避免写满问题）

mysql重启后，根据redo log可以把所有数据恢复到最新状态
和undo log的区别：
- redo log记录的是此次事务修改后的数据状态，记录的是更新之后的值，保证事务的持久性
- undo log记录的是此次事务修改前的数据状态，记录的是更新之前的值，保证事务的原子性

6.3 binlog

binlog文件是记录了数据库表结构变更和表数据修改的日志，不会记录查询类的操作

和redo log的区别：
- binlog是server层实现的，所有存储引擎都可以用；redo log是innodb存储引擎实现的
- 文件格式不同：
- 写入方式不同：binlog是追加写，写满就创建新的写，保存的是全量日志；redo log是循环写，写满就从头开始；所以删库后只能用binlog恢复数据
- 用途不同：binlog用来做备份恢复、主从复制；redo log用来做掉电故障恢复；

6.3.1 binlog是如何实现主从复制的

mysql上的所有变化以二进制形式保存在磁盘，主从复制的过程就是将binlog中的数据从主库传到从库，这个过程是异步的，分为3个阶段：

写入binlog：主库写binlog，提交事务，并更新本地存储数据
同步binlog：把binlog复制到所有从库上，每个从库把binlog写到暂存日志
回放binlog：回放binlog，并更新存储引擎中的数据

所以在集群模式下，写数据时可以只写主库，读数据时只读从库，这样即使写请求会锁表或者锁记录，也不会影响读请求的执行

一个主库一般跟2-3个从库，从库并不是越多越好，多了连接上来的IO线程也多，主库也要创建同样多的log dump线程来处理复制数据
主从复制主要要3种模型：
- 同步复制：mysql主库提交事务的线程要等所有从库复制成功响应，才返回结果。性能差、可用性也差
- 异步复制：mysql主库提交事务的线程并不会等待binlog同步到各从库，就返回结果。默认模式
- 半同步复制：只要数据成功复制到任意一个从库，主库的事务线程就可以返回

6.4 两阶段提交

事务提交后，redolog和undolog都要持久化到磁盘，两个逻辑是独立的，可能出现半成功状态，这样两份日志可能就会出现不一致

update t_user set name='xiaolin' where id = 1;
如果上述语句持久化过程中出现半成功状态：
1、如果将redolog刷到磁盘后，mysql宕机，binlog还没来得及写入：重启后，可以通过redo log恢复name的状态，但是binlog没有记录这条。主从模式下，出现数据不一致
2、如果将binlog刷到磁盘后，mysql宕机，redolog还没来得及写入：重启后，name还是旧值，但binlog记录了这条更新语句，主从架构中，binlog会被复制到从库，从库进行更新，出现不一致

为了避免两份日志之间逻辑不一致问题，引入了【两阶段提交】来解决

两阶段提交是分布式事务一致性协议，可以保证多个逻辑操作要么全成功，要么全失败，不会出现半成功状态

两阶段提交把单个事务的提交拆分成两个阶段，且都由协调者和参与者共同完成：

准备阶段：裁判（协调者）依次问拳击手（参与者）是否准备好
提交阶段：拳击手（参与者）都准备好了就开打，任何一位没准备好都会对事务进行回滚

事务请求             mysql server               binlog                   innodb存储引擎
   ｜-------事务提交----->|
              prepare阶段
                        ｜----事务id写入redolog，将redolog的事务状态设为prepare---->|
                        ｜<---------------ok-----------------------------------｜       （时刻A）
              commit阶段
                        ｜---事务id写入binlog----->|
                        |<------ok---------------|                                      （时刻B）
                        |----调引擎提交事务接口，将redolog状态设为commit------>|
                        |<-------------------ok---------------------------|
   |<--------ok---------|

时刻A：redolog已经写入磁盘，binlog还没写入
时刻B：redolog和binlog都已写入，但redolog还没写入commit标识
mysql重启后会按顺序扫描redolog，碰到处于prepare状态的redolog，就可以拿里面的事务ID去binlog查看是否存在此ID
- 如果没有，说明redolog完成了刷盘而binlog没有，于是回滚事务，对应时刻A崩溃恢复
- 如果有，说明redolog和binlog都完成了刷盘，于是提交事务，对应时刻B崩溃恢复

综上，对处于prepare阶段的redolog，既可以提交事务，也可以回滚事务，取决于是否可以在binlog中找到和redolog相同的XID，有就提交事务，没有就回滚事务，来保证两个日志的一致性。

两阶段提交是以binlog写成功为事务提交成功的标志
binlog写入后，就会被同步到从库，所以在主库要去提交这个事务，来保证主从数据一致

6.4.1 两阶段提交有什么问题

两阶段提交虽然保证了两个日志文件的一致性，但是性能较差，主要因为：

磁盘IO次数较高：对双1配置，每个事务提交都会进行两次刷盘（一次redolog、一次binlog）
- 引入binlog组提交：当有多个事务提交时，会将多个binlog刷盘操作合成一个。引入后，prepare阶段不变，commit阶段拆分，每个阶段都有一个队列和锁来保护，第一个进队列的事务会是leader。这样锁的粒度就减小了，多个阶段可以并发执行，提升效率
- flush阶段：多个事务按进入顺序将binlog从cache写入文件（不刷盘）
- sync阶段：对多个事务的binlog合并一次刷盘
- commit阶段：各个事务按顺序做innodb commit操作
锁竞争激烈：两阶段提交可以保证【单事务】两个日志内容一致；但【多事务】情况下不能保证两者提交顺序一致，所以要加锁来保证提交的原子性，事务获取到锁了才能进入prepare阶段，一直到commit阶段结束才能释放锁