cbirdcn.github.io

http://www.zhzq.site

View the Project on GitHub

深入理解MySQL之原理与实现

MySQL 体系架构

MySQL体系结构

连接器

负责与客户端建立连接, 验证身份，获取权限，维持和管理连接。

通过 TCP 连接, 验证用户身份, 当连接到达时获取用户当前所有权限, 而权限的获取是一次性的, 也就是说即使登录后对该用户的权限做了修改, 也无法立即生效, 需要等到用户下一次登录 MySQL 才能体现.

常用的登录命令 mysql -h$IP -P$PORT -u$USER -p

登录后，show processlist命令查询当前所有生效的连接。登录成功后, 如果没有后续的操作, 连接会处于 Sleep 状态。MySQL 连接默认的超时时间为 8 小时, 意味着该连接如果 8 小时内没有进行任何的操作, 就会被系统逐出。超时失效的连接将会被提示Lost connection to MySQL server during query

验证用户身份过程的实现

MySQL身份验证——Pluggable Authentication

访问控制通过两个步骤来实现

当客户端连接到MySQL服务器时，服务器会使用客户端提供的用户名和主机名从mysql.user系统表里面查询匹配的记录，然后使用记录里面提供的验证插件对客户端进行验证

MySQL使用插件方式进行验证可以带来如下好处

MySQL8.0目前提供如下验证插件

通常，使用插件验证方式需要在服务器端和客户端安装相对应的插件。服务器端的插件会验证客户端的连接，客户端的插件通常会内嵌到客户端程序里。当客户端连接到服务器时，服务器会通知客户端应该使用哪个插件。用户创建账户时，需要指定服务器端适当的插件，如果使用默认的插件，则不需要指定。服务器可以通过–default-authentication-plugin=plugin_name来设置默认的插件。当服务器和客户端同时使用默认插件时，服务器不需要和客户端进行往返的通信。mysql和mysqladmin客户端可以使用–default-auth=plugin_name选项来指定使用的插件。

查询缓存

MySQL 所有的查询请求都会先从查询缓存中查找

缓存内容可以看做一个一个典型的映射关系

Map<SQL 语句, 结果集>

如果查询语句命中缓存就不会执行后面的操作。

但 MySQL 为此做了相对复杂的缓存一致性的维护, 对表的任何写操作都会导致使用该表所对应的缓存全部失效，因为很难处理范围操作的缓存，所以就直接放弃该表的所有缓存。

MySQL8.0 之后, 官方已经彻底将缓存模块删除。

缓存规则

缓存完全占用内存空间，这是由配置影响的。

mysql> show variables like '%query_cache%';
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| have_query_cache             | YES     |
| query_cache_limit            | 1048576 |
| query_cache_min_res_unit     | 4096    |
| query_cache_size             | 599040  |
| query_cache_type             | ON      |
| query_cache_wlock_invalidate | OFF     |
+------------------------------+---------+
6 rows in set (0.02 sec)

缓存机制中的内存管理

查询缓存是完全存储在内存中的，所以在配置和使用它之前，我们需要先了解它是如何使用内存的。

MySQL 查询缓存使用内存池技术，自己管理内存释放和分配，而不是通过操作系统。内存池使用的基本单位是变长的 block, 用来存储类型、大小、数据等信息。一个结果集的缓存通过链表把这些 block 串起来。block 最短长度为 query_cache_min_res_unit。

当服务器启动的时候，会初始化缓存需要的内存，是一个完整的空闲块。当查询结果需要缓存的时候，先从空闲块中申请一个数据块为参数 query_cache_min_res_unit 配置的空间，即使缓存数据很小，申请数据块也是这个，因为查询开始返回结果的时候就分配空间，此时无法预知结果多大。

分配内存块需要先锁住空间块，所以操作很慢，MySQL 会尽量避免这个操作，选择尽可能小的内存块，如果不够，继续申请，如果存储完时有空余则释放多余的。

但是如果并发的操作，余下的需要回收的空间很小，小于 query_cache_min_res_unit，不能再次被使用，就会产生碎片。

缺点

虽然缓存避免了I/O和CPU计算，但是仍有缺点。

查询缓存对数据库的读和写都会带来额外的消耗

所以，查询缓存的适用场景

在工程中，使用本地缓存（比如Java的Caffeine）或者分布式缓存（比如 Redis）更合适。

分析器

词法分析

解析字符串中每个单词的含义, 建立连接后, 客户端都是以一条字符串格式的 SQL 语句与 MySQL 进行交互。

SELECT id, name, gender, score FROM student WHERE grade = 4 ORDER BY score LIMIT 0, 100;

在进行词法分析的时候, 会进行如下操作

将SQL解析成一棵语法树的Token, 语法树的节点主要分为以下两种类型:

语法/语义分析

分析SQL是否符合语法规则，生成语法树。MySQL使用了Bison来实现。

让MySQL知道做什么。

优化器

选择适合的索引，决定联表顺序等，最终确定执行方案。

执行器

先判断用户对表有没有相应的执行权限, 如果有权限, 根据表所属的引擎调用不同接口. 至于为什么在此处才查询是否有权限, 是因为有时候 SQL 语句需要操作的表不只是 SQL 语句中使用的, 例如当有触发器需要执行时, 涉及的表就没有 体现在 SQL 语句中. 查询语句会优先执行 获取满足条件的第一行 接口, 然后再循环调用 查询满足条件的下一行 接口

explain获取执行计划

mysql> explain SELECT * FROM dept_emp WHERE emp_no IN (SELECT emp_no FROM dept_emp GROUP BY emp_no HAVING COUNT(emp_no)>1);
+----+-------------+----------+------------+-------+-----------------+---------+---------+------+--------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys   | key     | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+----------+------------+-------+-----------------+---------+---------+------+--------+----------+-------------+
|  1 | PRIMARY     | dept_emp | NULL       | ALL   | NULL            | NULL    | NULL    | NULL | 331143 |   100.00 | Using where |
|  2 | SUBQUERY    | dept_emp | NULL       | index | PRIMARY,dept_no | PRIMARY | 16      | NULL | 331143 |   100.00 | Using index |
+----+-------------+----------+------------+-------+-----------------+---------+---------+------+--------+----------+-------------+

EXPLAIN 语句并不会真的去执行相关的语句，而是通过查询优化器对语句进行分析，找出最优的查询方案，并显示对应的信息。

EXPLAIN 执行计划支持 SELECT、DELETE、INSERT、REPLACE 以及 UPDATE 语句。

各列代表的含义总结如下

仔细介绍这些列。

select_type查询的类型，主要用于区分普通查询、联合查询、子查询等复杂的查询，常见的值有：

type（重要）查询执行的类型，描述了查询是如何执行的。所有值的顺序从最优到最差排序为： system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

常见的几种类型具体含义如下：

possible_keys这一列为 NULL ，则表示没有可能用到的索引；这种情况下，需要检查 WHERE 语句中所使用的的列，看是否可以通过给这些列中某个或多个添加索引的方法来提高查询性能。

key（重要） 列表示 MySQL 实际使用到的索引。如果为 NULL，则表示未用到索引。

rows 列表示根据表统计信息及选用情况，大致估算出找到所需的记录或所需读取的行数，数值越小越好。

Extra（重要） 列包含了 MySQL 解析查询的额外信息，通过这些信息，可以更准确的理解 MySQL 到底是如何执行查询的。常见的值如下：

日志系统

慢查询日志(slow log)和二进制日志(BinLog)，以及RedoLog(重做日志) 和 UndoLog(回滚日志)

慢查询日志

默认慢查询日志是禁用的，可以通过设置slow_query_log的值来开启，slow_query_log_file是存储日志的文件路径。

mysql> show variables  like '%slow_query_log%';
+---------------------+-----------------------------------------------+
| Variable_name       | Value                                         |
+---------------------+-----------------------------------------------+
| slow_query_log      | OFF                                           |
| slow_query_log_file | /var/lib/mysql/6e8605d66d55-slow.log          |
+---------------------+-----------------------------------------------+
2 rows in set (0.00 sec)
 
mysql> set global slow_query_log=1;

使用set global slow_query_log=1开启了慢查询日志只对当前数据库生效，如果MySQL重启后则会失效。如果要永久生效，就必须修改配置文件my.cnf的参数slow_query_log 和slow_query_log_file（其它系统变量也是如此）。

开启了慢查询日志后，什么样的SQL才会记录到慢查询日志里面呢？ 这个是由参数long_query_time控制，默认情况下long_query_time的值为10秒，可以使用命令修改，也可以在my.cnf参数里面修改。

mysql> show variables like 'long_query_time%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

mysql> set global long_query_time=4;
Query OK, 0 rows affected (0.00 sec)

mysql> show global variables like 'long_query_time';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 4.000000 |
+-----------------+----------+
1 row in set (0.00 sec)

注意：使用命令 set global long_query_time=4修改后，需要重新连接或新开一个会话才能看到修改值。你用show variables like ‘long_query_time’查看是当前会话的变量值.

新开一个session，触发慢查询：

mysql> select sleep(5);
+----------+
| sleep(5) |
+----------+
|        0 |
+----------+
1 row in set (5.00 sec)

查看日志

# tail -f  /var/lib/mysql/6e8605d66d55-slow.log
mysqld, Version: 5.7.34 (MySQL Community Server (GPL)). started with:
Tcp port: 3306  Unix socket: /var/run/mysqld/mysqld.sock
Time                 Id Command    Argument
# Time: 2023-09-26T06:53:21.460019Z
# User@Host: root[root] @ localhost []  Id:     5
# Query_time: 5.000218  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1695711201;
select sleep(5);

支持修改慢查询日志到文件或表。log_output 参数是指定日志的存储方式。log_output=’FILE’表示将日志存入文件，默认值是’FILE’。log_output=’TABLE’表示将日志存入数据库，这样日志信息就会被写入到mysql.slow_log表中。MySQL数据库支持同时两种日志存储方式，配置的时候以逗号隔开即可，如：log_output=’FILE,TABLE’。日志记录到系统的专用日志表中，要比记录到文件耗费更多的系统资源，因此对于需要启用慢查询日志，又需要能够获得更高的系统性能，那么建议优先记录到文件。

mysql> show variables like '%log_output%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |
+---------------+-------+
1 row in set (0.00 sec)
 
mysql> set global log_output='TABLE';
Query OK, 0 rows affected (0.00 sec)

慢查询表举例：

mysql> select * from mysql.slow_log;
+---------------------+---------------------------+------------+-----------+-----------+---------------+----+----------------+-----------+-----------+-----------------+-----------+
| start_time          | user_host                 | query_time | lock_time | rows_sent | rows_examined | db | last_insert_id | insert_id | server_id | sql_text        | thread_id |
+---------------------+---------------------------+------------+-----------+-----------+---------------+----+----------------+-----------+-----------+-----------------+-----------+
| 2016-06-16 17:37:53 | root[root] @ localhost [] | 00:00:03   | 00:00:00  |         1 |             0 |    |              0 |         0 |         1 | select sleep(3) |         5 |
| 2016-06-16 21:45:23 | root[root] @ localhost [] | 00:00:05   | 00:00:00  |         1 |             0 |    |              0 |         0 |         1 | select sleep(5) |         2 |
+---------------------+---------------------------+------------+-----------+-----------+---------------+----+----------------+-----------+-----------+-----------------+-----------+
2 rows in set (0.00 sec)

系统变量log-queries-not-using-indexes：未使用索引的查询也被记录到慢查询日志中（可选项）。如果调优的话，建议开启这个选项。另外，开启了这个参数，其实使用full index scan的sql也会被记录到慢查询日志。

默认情况下，复制从服务器不会将复制的查询写入慢速查询日志。要改变这一点，可以使用log_slow_slave_statements系统变量。当启用慢速查询日志时，该变量启用记录在从属服务器上执行时间超过long_query_time秒的查询。

系统变量Slow_queries用于查询慢查询记录条数。

在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具mysqldumpslow。建议在使用这些命令时结合 和more 使用 ，否则有可能出现刷屏的情况。

binlog（binary log）

BinLog 记录了对 MySQL 数据库执行更改的所有操作, BinLog 功能会将所有事务的操作通过日志的形式追加到磁盘中持久化, 不存在被自动覆盖的情况。

InnoDB 中 BinLog 的持久化是事务中的一个步骤, InnoDB 会等待 MySQL 返回 BinLog 持久化的结果, 再决定自身是提交还是回滚, 因此对 InnoDB 来说, 任何提交的事务必然存在 BinLog.

不同形式的日志：

二进制的binlog要通过mysqlbinlog查看。

binlog功能：

binlog的写入机制

定时增量备份binlog

mysql通过binlog定时增量备份脚本实现

检查是否开启binlog

mysql> show variables like '%log_bin%';
+---------------------------------+----------------------------------------+
| Variable_name                   | Value                                  |
+---------------------------------+----------------------------------------+
| log_bin                         | ON                                     |
| log_bin_basename                | /home/mysql/mysql/data/mysql-bin       |
| log_bin_index                   | /home/mysql/mysql/data/mysql-bin.index |
| log_bin_trust_function_creators | OFF                                    |
| log_bin_use_v1_row_events       | OFF                                    |
| sql_log_bin                     | ON                                     |
+---------------------------------+----------------------------------------+

如果log_bin的value值为ON则表示开启了，如果是OFF，则需要到my.cnf配置文件中的mysqld节点下新增log-bin=mysql-bin的配置，当然这里mysql-bin是自己定义的值，表示logbin的文件名，你可以根据自己需求指定到某个目录下。然后再重启mysql即可。

定时脚本的基本逻辑：

binlog恢复数据

binlog用于误删表或库的情况。

MySQL 5.7 - 通过 BINLOG 恢复数据 MySQL基于Binlog的数据恢复实战

逻辑：

如果不想让binlog生成sql再执行，可以 mysqlbinlog --no-defaults --start-datetime='18-07-03 21:56:11' --stop-datetime='18-07-03 21:56:04' mysql-bin.000003 | mysql -u root -p 前提是能保证中间没有其他不相关的事件，比如中间有其他表的创建过程，如果再次建表就会报错，所以更推荐生成sql检查后再执行。

索引

哈希表

适合存储kv，但是无法支持区间查询

搜索树索引

为支持区间查询，选择了搜索树。最典型的搜索树结构就是二叉查找树, 二叉树的特点是左子树的值小于等于双亲结点, 右子树的值大于双亲结点, 在查询的时候应用二分查找的原理能够做到理想情况下 O(log(N)) 级别的插入和查询, 并且由于其本身就是有序的, 因此天然支持区间查询.

因为磁盘随机读很耗时，为了降低磁盘随机读的频率, 首先就需要尽可能降低查询次数, 也就是降低树的深度. 在数据总量保持不变的前提下, 如果想降低树的深度, 最可行的办法就是将二叉树变为多叉树. 每个节点变成多叉树之后, 其双亲结点内部相应的需要维护一个小索引(假设是 10 叉树, 则双亲节点内部需要维护其负责的 10 个区间对应的指针), 但其实这个成本是可以忽略不计的, 因为我们一次将其从磁盘中取出, 每个节点内部的索引操作都是在内存中完成. 这样就能避免在一次查询中过多操作磁盘.

因此引出了 InnoDB 索引的实现: B+树, B+树就是为了充分利用磁盘预读功能而设计的一种数据结构。

磁盘预读与局部性原理：减少磁盘 IO, 因此磁盘往往不是严格按需读取, 而是每次会预读一块数据, 即使只读一个字节, 磁盘也会从这个位置开始, 顺序向后读取一定长度(默认 4k)的数据放入内存, 这样做的理论依据是注明的局部性原理:当一个数据被用到时, 其附近的数据也通常马上会被使用.

B+ 树每个节点可以存储多个关键字, 它将节点大小设置为磁盘页的大小, 充分利用了磁盘预读的功能, 每次读取磁盘页的时候就会读取整个节点, 也正因为每个节点存储着非常多的关键字(InnoDB 每个双亲结点大概可以存储 1200 个子节点), 会使得树深度很小, 进而要执行的磁盘读取操作次数就会非常少, 更多的是在内存中对读取的数据进行查询操作, 而这部分操作的消耗往往可以忽略不计.

B+ 树索引的本质就是 B+ 树在数据库中的实现，索引结构类似于一棵多叉树, 根据键快速找到数据。 在 InnoDB 中, 每个 B+ 数的双亲节点大致可以保存 1200 个子节点, 可以近似理解为 1200 叉树, 那么即使在面对亿级数据量时, 也能够做到不超过 4 层（1200 ^ 3 > 17亿；1200 ^ 4 > 2万亿）, 并且 B+ 数的第二层基本会常驻内存（1200 ^ 2 = 144万）

在 InnoDB 中, 表都是根据主键顺序以索引的形式存放的, 这种存储方式称之为索引组织表. 所有的数据都存储在主键的 B+ 树中. 除了主键以外的其他索引被称为辅助索引, 叶子节点存储着索引字段和主键的映射, 在通过辅助索引查询时, 需要先从辅助索引中找到记录的主键, 再回到主索引查询对应记录。这个过程叫做回表。

InnoDB 的 B+Tree 索引分为主索引和辅助索引。主索引的叶子节点 data 域记录着完整的数据记录，这种索引方式被称为聚簇索引。因为无法把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。

辅助索引的叶子节点的 data 域记录着主键的值，因此在使用辅助索引进行查找时，需要先查找到主键值，然后再到主索引中进行查找。

索引的维护

B+ 树在插入和删除元素的时候, 都需要维护其有序性。一般情况下，如果叶子节点不满，就找到插入位置插入即可。极端情况下，如果叶子节点已满, 就需要申请一个新的数据页, 然后挪动一部分数据过去, 这个过程称为页的分裂, 频繁的分裂会对性能造成影响

为什么 InnoDB 的表推荐使用自增主键？

对 B+ 树来说, 效率最高的插入方式就是插入 id 最大的元素(未必需要递增, 只需要保证每次插入最大即可), 这样的插入永远是在最后一个叶子节点中向后追加元素. 而其他情况下的插入则需考虑节点分裂. 对数据库来说, 实现 永远插入最大值 最简单的方式就是自增

联合索引

联合索引指的是对表上的多个列进行索引, 联合索引的创建方法也和单个索引相同, 唯一的不同之处在于有多个索引列. 底层结构也与普通索引基本相同, 不同之处在于联合索引的叶子节点中, key 是由多个值组成的, 并且 key 之间按照多个列从左到右的顺序排序。所以必须遵循最左匹配原则。

对于一本书，我们说”找到第几章第几节的XXX”，从没有听说过”找到第几节的XXX”！这是复合索引的重要特性，即最左匹配特性。

假设现在有一条 a, b, c, d 列组成的联合索引, 那么能匹配该索引的查询语句为:

a -> b -> c -> d
a -> b -> c
a -> b
a

覆盖索引

如果一条查询语句能够从辅助索引中获得全部需要的信息, 那么就不再需要回表, 我们就将这样的索引成为覆盖索引

索引的选择

SQL 语句只会选择一条索引去执行。当 SQL 语句中没有明确规定走哪一条索引时, 就会由查询优化器来选择一条.

选择标准:

所以，当可能出现排序的情况下，正确选择索引的方式：

索引失效

比如字段用函数表示时，因为函数不一定满足同层级跨节点有序，优化器放弃使用索引。

假设表 t 有字段 uid, 类型为 varchar(64)，当对字段用where uid = 1234查询时会触发全表扫描。原因是查询语句调用了隐式的类型转换函数对数据转型, MySQL 的类型转换规则是如果字符型和数字做比较的话, 会将字符型转换成数字。隐式类型转换的问题本质上还是由于 对索引字段做函数操作, 优化器会放弃走索引树的搜索功能, 触发主索引或辅助索引的全表扫描

优化SQL

锁

锁是一个用于管理对共享资源并发访问的数据结构.

InnoDB行锁

对于写操作, InnoDB 会在行记录上加锁。使用 lock 功能的对象是事务, 锁定的对象是数据库存储中的对象, 包括表, 页, 行. 并且一般锁会在事务 commit 或 rollback 后释放.

InnoDB 实现了 3 种行锁的算法, 分别是:

在 InnoDB 事务中, 行锁是在需要的时候才加上的, 但并不是不需要就立即释放, 需要等事务结束后再统一释放.

行锁在 InnoDB 中是基于索引实现的, 因此一旦某个加锁操作没有使用索引, 那么该锁就会退化为表锁.

记录锁举例：

-- id 列必须为主键或唯一索引
SELECT * FROM t WHERE id = 1 FOR UPDATE;
UPDATE t SET grade = 100 WHERE id = 1;

在使用 SELECT FOR UPDATE 和 UPDATE 时, id 为 1 的记录行会被锁住, 但锁住的索引必须是主索引或者唯一索引, 否则加的锁就是临键锁, 同时, 查询语句必须为精确匹配, 不能为 <, > 或 LIKE, BETWEEN 等, 否则也只会加临键锁

间隙锁区间举例：

UPDATE t SET grade = grade + 10 WHERE id BETWEEN 10 AND 15;

锁住id 为 11, 12, 13, 14 的记录. 但 10 和 15 两条记录不会被锁住.

临键锁（特殊间隙锁）：

每个数据行上的普通索引列都会存在一把临键锁.临键锁只与普通索引有关, 在主索引和唯一键索引上不存在临键锁.

事务

事务是数据库系统区别于文件系统的重要特性之一, 在文件系统中, 如果在写文件的时候进程退出, 这个文件就很有可能被损坏. 还有在顺序写入多个文件的场景, 如果执行到中间某个状态时进程退出, 就会产生复杂的中间状态.

数据库引入了事务, 就是希望能够安全的将数据库从一种一致状态转换到另一种一致的状态上来, 当数据库提交工作时, 可以确保要么所有的修改都已经成功保存, 要么所有的修改都被废弃。保证这些功能的关键就在于满足 ACID 特性.

当数据库上有多个事务同时执行的时候, 可能出现脏读, 不可重复读, 幻读等问题, 为了解决这些问题, 就有了隔离级别的概念.

要解释幻读，需要涉及快照读和当前读。

MySQL到底有没有修复幻读

快照读是指，事务启动时，就创建一个一致性的快照视图。后续数据库中的数据再怎么变化，这个快照的数据内容都不受它们的影响。而这个快照一直会伴随着整个事务的生命周期。在这个事务运行过程中的所有的普通查询都会从这个快照中去获取数据，事务中的这些普通的查询就属于快照读。

当前读是指，在一个事务执行的过程中，如果我们这个时候使用了DML语句，也就是我们平时所说的insert、update、delete语句，此时DML会执行当前读，它们会在操作数据库内容之前，去读取数据库中当前时间点以及提交的最新的数据，基于最新的数据的基础上，再去做这个DML语句自己的SQL逻辑。此时的这个读取数据库中最新已提交的数据的这个动作，就是当前读。我们拿一个事务当中的update语句来说，在修改数据的时候，需要先读到数据，才能基于读到的数据上再去做修改。而这个读取数据的时候，需要基于数据库中最新的已经提交的数据来做，如果此时仍然按照一致性快照读，那么会读取到当前事务开启的时候所能读取到的数据版本，而这个数据版本有可能已经不是最新的了，其他事务可能已经在这个数据版本的基础上进行的修改，如果不去数据库中读取其他事务更改后的数据，那么此时就会覆盖掉其他事务的修改操作。而这是数据库中锁不允许的。所以，在修改的时候，要执行当前读，然后再修改。

对于当前读并发的事务A和B。假设事务A先查询数据，再对这些目标数据执行update操作，最后查询这些数据是否符合想法。事务B在事务A第一次查询后insert了一条数据并提交了。这样在事务A中update时就不再是快照读而是当前读，包含了事务B插入的新数据，update时就会把事务B新增的数据也update掉，产生幻读（可能影响其他事务提交的数据，但是当前事务却不知情）。解决的办法是，在事务A第一次查询数据后就对这些数据加上锁，可以是行锁、表锁等看具体业务，比如select * from t lock in share mode;，这样事务B的insert就会被阻塞，当事务A执行完update和第二次查询时只会影响想要的数据。事务B等到事务A提交或回滚操作解锁后才能继续执行insert，如果等待了太久可能还会因事务超时而退出。

而对于快照读并发的事务A和B，也就是都只存在select操作，事务之间不存在幻读。

多版本并发控制(Multi-Version-Concurrency-Control, MVCC)

同一条记录可以存在多个版本.

每条记录在更新的时候都会同时记录一条回滚操作. 记录上的最新值通过回滚操作可以得到前一个状态的值.

查询时从最新值开始, 依次向前比较直到找到提交时间早于该事务启动时间的第一条记录, 然后返回.

实现事务

记录的多个版本只是逻辑上的概念, InnoDB 并不是真的存储数据, 存储的是能够将数据恢复到上一个版本的 undo log.

事务的隔离性由锁来实现, 原子性和持久性由 redo log 实现, 一致性由 undo log 实现. redo log 用来恢复提交事务修改的页操作, undo log 用来将行记录回滚到某个特定版本.

读已提交和可重复读的实现都利用了快照, 不同之处在于:

一个最直接的 UPDATE 语句执行方式:

UPDATE 操作是一个典型的随机写, 对于机械硬盘来说, 一次随机写平均花费 10ms, 并且一个事务中可能存在多条写操作, 在保证其能执行成功的同时还要保证原子性, 由此可见这并不是一个理想的方案.

InnoDB 引入了 WAL 思想, 其关键在于先写日志, 再写磁盘, 当有一条记录需要更新的时候, InnoDB 就会先把记录写到 RedoLog 中, 并更新内存, 此时更新操作就完成了. InnoDB 会在 适当 的时候, 将这个操作记录更新到磁盘中, 这样的更新都是在系统相对比较空闲的时候.

RedoLog 的实现方式：磁盘中的 RedoLog 是固定大小的(并不像 BinLog 可以在磁盘空间未满的情况下无限追加), 写入的方式类似环形队列, write pos 是当前记录的位置, 一边写一边后移, checkpoint 是当前需要擦除的位置, 也是往后推移并且循环, 擦除记录前要把记录更新到数据文件.事务在执行的时候, 生成的 RedoLog 会先写入 RedoLog Buffer, 当事务提交时再统一持久化到磁盘.

注意区分binlog和redolog：

RedoLog 的两阶段提交一共分为三步:

如果出现意外，如何保证 RedoLog 与 BinLog 一致？

对于 MySQL 来说, 每个事务的 BinLog 都有完整的格式, 通过识别该格式就可以判断事务额 BinLog 是否完整

RedoLog 记录了事务的行为, 可以通过其对数据页进行重做. 但事务如果需要进行回滚, 就需要 UndoLog. 当事务执行失败或者显式执行 ROLLBACK 的时候, 就可以利用 UndoLog 将数据回滚到某个特定的版本.

UndoLog 存放在数据库内部的回滚段中, UndoLog 本身不是快照, 只是逻辑地将数据库恢复到原来的样子, 比如某个字段自增, UndoLog 中就会记录将该字段 -1 可以得到上一个版本。

由于fsync将Redo log落盘太耗时，所以 MySQL 提供了 group commit 的功能, 即一次 fsync 刷新可以确保多个事务日志被写入文件.

因此 WAL 机制主要能带来两方面提升:

集群与高可用

在 MySQL 的高可用场景中, 最简单和常用的就是主备复制, 客户端的读写都直接访问主库, 而备库只负责将主库的更新同步到本地执行, 当主库出现问题的时候, 可以将主库下线, 并将备库立即提升为主库.

主从同步

MySQL 是通过 BinLog 的同步完成主备的数据同步功能的.

在主备同步时, 备库与主库维持了一个长连接, 主库有一个单独的线程用于处理备库的长连接, 日志的同步过程如下:

主从延迟

在备库上可以执行 show salve status 命令, 返回结果中有 seconds_behind_master, 用于表示当前备库延迟多少秒, MySQL 会统计BinLog 中主库记录的时间域当前系统时间的差值.

在正常情况下, BinLog 传给备库的延迟很低, 主备延迟的主要来源是备库接收完 BinLog 和提交事务的时间. 本质上说, 是从库消费中转日志(RelayLog) 的速度比主库生产 BinLog 的速度要慢.

主备切换的时候, 正常情况下应该采用可靠性优先策略:

这种方案下, 在步骤 3~5 期间整个数据库系统对外不可写.

在从库恢复数据时，多线程消费 RelayLog 的时候, 需要保证如下行为:

业务场景

秒杀

私信/站内信消息推送

听歌次数统计

锁

悲观锁

BEGIN;
SELECT count FROM tb WHERE id = ? FOR UPDATE;
-- do sth
UPDATE tb SET count = count - ? WHERE id = ?;
COMMIT;

乐观锁

BEGIN;
SELECT count FROM tb WHERE id = ?;
UPDATE tb SET count = count - ? WHERE id = ? AND count = :count;
COMMIT;
-- do sth

悲观锁预期目标数据总是被改变，所以将数据锁死等待修改。

乐观锁预期目标不会被改变，所以按照compare and set的方式修改

读写分离

读写分离的原理就是将数据库读写操作分散到不同的节点上

使用读写分离之后, 可能会引入两个问题:主从复制延迟、分配机制

主从复制延迟

如果业务服务器将数据写入到主库后进行读取, 此时读操作访问从库, 而主库的数据没有完全复制过来, 从库是无法读取到最新数据的.

解决方案:

分配机制

将读写操作区分, 一般有两种方式: 程序代码封装和中间件封装

在代码中抽象一个数据访问层, 实现读写操作分离和数据库服务器连接的管理.

封装一套中间件系统也可以实现读写分离和数据库服务器连接的管理。中间件对业务服务器提供 SQL 兼容的协议, 业务服务器无需自己进行读写分离, 对于业务服务器来说, 访问中间件和访问数据库没有区别

分布式

读写分离分散了读写操作的压力, 但没有分散存储的压力, 当数据量达到千万级以上的时候, 单台数据库服务器的存储能力就会成为瓶颈:

垂直分表: 适合将某些表中不常用且占用大量空间的列拆分出去. 代价是操作表的数量增加.

水平拆分（Sharding分片）: 适合行数较大的表, 会引入更多的复杂度: 路由, join 操作, count 操作 等

Sharding 策略

Sharding问题及解决：

连接池

主要参考

深入理解 MySQL 原理

从程序员的角度深入理解MySQL