一、基础架构

MySQL基本架构

下边是MySQL的基本架构示意图

MySQL可以分为Server层和存储引擎层以下两部分。

• Server层：连接器、查询缓存、分析器、优化器、执行器等。涵盖MySQL大多数核心服务功能，以及所有内置函数。
  • 注意：所有跨存储引擎的功能都在这一层实现。比如：存储过程、触发器、视图等
• 存储引擎层：负责数据的存储和提取。其架构模式是插件式的，支持InnoDB、MyISAM、Memory等多个存储引擎。
  • 注：从MySQL 5.5.5版本开始InnoDB成为了默认存储引擎
  • 不同存储引擎的表数据存取方式不同，支持的功能也不同

从上面架构图可以看出：不同的存储引擎共用一个Server层，也就是从连接器到执行器的部分。

连接器

连接命令：

1

mysql -h$ip -P$port -u$user -p

注意：在交互对话里面输入密码。虽然密码也可以直接跟在-p后面写在命令行中，但这样可能会导致你的密码泄露。如果你连的是生产服务器，强烈建议你不要这么做。

如果用户名密码认证通过，连接器会到权限表里面查出你拥有的权限。之后，这个连接里面的权限判断逻辑，都将依赖于此时读到的权限。

要点1：权限表信息被用户加载到内存中

• 一个用户成功建立连接后，即使你用管理员账号对这个用户的权限做了修改，也不会影响已经存在连接的权限。修改完成后，只有再新建的连接才会使用新的权限设置。

使用show processlist查看连接信息：

要点2：数据库中的长短连接

• 数据库里面，长连接是指连接成功后，如果客户端持续有请求，则一直使用同一个连接。
• 短连接则是指每次执行完很少的几次查询就断开连接，下次查询再重新建立一个。
• 建议：建立连接的过程通常是比较复杂的，所以推荐使用中要尽量减少建立连接的动作，也就是尽量使用长连接。

全部使用长连接后，可能会发现，有些时候MySQL占用内存涨得特别快，这是因为MySQL在执行过程中临时使用的内存是管理在连接对象里面的。这些资源会在连接断开的时候才释放。所以如果长连接累积下来，可能导致内存占用太大，被系统强行杀掉（OOM），从现象看就是MySQL异常重启了。

要点3：如何解决MySQL占用内存太大问题？

• 定期断开长连接。使用一段时间，或者程序里面判断执行过一个占用内存的大查询后，断开连接，之后要查询再重连
• MySQL 5.7或更新版本，可以在每次执行一个比较大的操作后，通过执行 mysql_reset_connection来重新初始化连接资源。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态

查询缓存

要点4

• 执行过的语句及其结果可能会以key-value对的形式，被直接缓存在内存中。key是查询的语句，value是查询的结果

要点5：大多数情况下我会建议你不要使用查询缓存，为什么呢？因为查询缓存往往弊大于利。

• 更新压力大的数据库来说，查询缓存的命中率会非常低。除非你的业务就是有一张静态表，很长时间才会更新一次。比如，一个系统配置表，那这张表上的查询才适合使用查询缓存。
• 将参数query_cache_type设置成DEMAND，这样对于默认的SQL语句都不使用查询缓存。而对于你确定要使用查询缓存的语句，可以用SQL_CACHE显式指定

1

mysql> select SQL_CACHE * from T where ID=10；

要点6：MySQL 8.0版本直接将查询缓存的整块功能删掉了，也就是说8.0开始彻底没有这个功能了。

分析器

词法分析：输入的是由多个字符串和空格组成的一条SQL语句，MySQL需要识别出里面的字符串(一个个单词，称为一个token) 分别是什么，代表什么。

• 比如：从输入的"select"这个关键字识别出来，这是一个查询语句。它也要把字符串“T”识别成“表名T”，把字符串“ID”识别成“列ID”

语法分析：根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个SQL语句是否满足MySQL语法。

下边是一条语法错误的语句：

1
2
3

mysql> elect * from t where ID=1;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'elect * from t where ID=1' at line 1

要点7：一般语法错误会提示第一个出现错误的位置，所以你要关注的是紧接“use near”的内容。

课后问题：如果表T中没有字段k，执行了这个语句 select * from T where k=1, 那肯定是会报“不存在这个列”的错误： “Unknown column ‘k’ in ‘where clause’”。这个错误是在我们上面提到的哪个阶段报出来的呢？

要点8：解析器和预处理器

• 解析器：处理语法和解析查询, 生成一课对应的解析树。
• 预处理器：进一步检查解析树的合法。比如: 数据表和数据列是否存在, 别名是否有歧义等。如果通过则生成新的解析树，再提交给优化器。
• 摘自《高性能MySQL》

从上边 预处理器的功能，可以看出，这个字段错误的判断 发生在 分析器阶段。

优化器

经过了分析器，MySQL就知道你要做什么了。在开始执行之前，还要先经过优化器的处理。

表中如果存在多个索引，优化器会分析 各个索引执行SQL语句的成本，并选中成本最低的方案进行 SQL语句执行。

优化器阶段完成后，这个语句的执行方案就确定下来了，然后进入执行器阶段。

执行器

MySQL通过分析器知道了你要做什么，通过优化器知道了该怎么做，于是就进入了执行器阶段，开始执行语句。

要点9：开始执行的时候，要 先判断一下你对这个表T有没有执行查询的权限 ，如果没有，就会返回没有权限的错误

两次验证：

• 工程实现上，如果命中查询缓存，会在查询缓存放回结果的时候，做权限验证。
• 查询也会在优化器之前调用precheck验证权限

要点10：如果有对应操作表的权限，打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口。

要点11：在数据库的慢查询日志中有一个rows_examined的字段，表示这个 语句执行过程中扫描了多少行。这个值就是在执行器每次调用引擎获取数据行的时候累加的。

注意区分：引擎扫描行数 和 rows_examined的值 (这个指的是，调用执行器次数)

要点12：执行器调用一次，在引擎内部则扫描了多行，因此引擎扫描行数跟rows_examined并不是完全相同的。

二、日志系统

三、事务隔离

事务的概念以及特性

要点1：并不是所有引擎都支持事务，MySQL原生的MyISAM引擎就不支持事务，而InnoDB存储引擎支持事务，这正是MySQL存储引擎改为InnoDB的原因之一。

事务的本质就是一系列数据库操作的集合。 现实世界状态的转换过程要映射到数据库，这需要数据库操作遵守四个特性ACID，具体如下：

• 原子性(Atomicity): 一个事务中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。若执行事务过程中发生了错误，就会把已经执行的操作恢复成没执行前的样子。 现实生活中，银行转账是一个不可分割的操作，体现了事务的原子性。

• 一致性(Consistency): 指事务操作前和操作后，数据满足完整性约束，数据库保持一致性状态。对于转账的操作来说，完整性约束之一是转账之后转账银行卡总余额不变。数据库世界只是现实世界的一个映射，现实世界中存在的约束在数据库中都有体现，那么就称这些数据是一致的。 数据库中数据的一致性，可以靠两方面来保证：

  • 数据库本身可以解决一部分一致性需求。例如：数据库中，可以为表建立主键、唯一索引、外键等，还可以声明某个列为NOT NULL以此来拒绝NULL值插入
  • 更多的一致性需求 需要靠写业务代码的程序员来保证，这样可以提高数据库的性能。

• 隔离性(Isolation): 数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。每个事务都有一个完整的数据空间，对其他并发事务是隔离的。

  • 现实世界中，状态转换对应的数据库操作，要保证其他状态转换不会影响本次状态转换
  • 数据库中采取一些措施：让访问相同数据的不同状态转换对应的数据库操作的执行顺序是有一定规律的， 这就是后边要讲的事务的隔离级别。

• 持久性(Durability): 事务处理结束后，数据将被永久保留。

InnoDB引擎采用什么技术 来保证事务的四个特性？

• 持久性：通过 redo log(重做日志) 保证
• 原子性：通过 undo log(回滚日志) 保证
• 隔离性：通过 MVCC(多版本并发控制) 或 锁机制 来保证。这要看是哪种隔离级别，不同隔离级别保证隔离性的方式不一样
• 一致性：一致性 通过 持久性+原子性+隔离性 一起保证

随着事务对应的数据库操作执行到不同的阶段，事务的状态也会不断变化，如下图：

要点2：只有当事务处于 提交的 或者 中止的 状态时，一个事务的生命周期才算是结束。对于提交的事务，事务对数据库所作的修改将永久生效；对于中止的事务，事务对数据库的所有修改都会被 回滚到没执行该事务之前的状态。

并发事务引发的问题？

脏读：一个事务 读到 另一个事务 未提交的数据。这是由于另一个事务更新数据后，还未提交事务，就由于某些原因触发回滚操作导致的。

不可重复读：一个事务在 事务执行期间，两次读取数据 出现不一样的情况。这主要是由于 当前事务执行过程中，恰好另一个事务对其读取数据做了更改并且提交了事务。

幻读：一个事务执行期间，多次查询某个符合条件记录的数量，出现前后读取 查询记录数量不一致的情况。这主要是 由于当前事务执行过程中，另一个事务插入 或 删除了符合其读取条件的一条数据库记录。

事务隔离的实现

SQL 标准提出了四种隔离级别来规避这些现象，隔离级别越高，性能效率就越低，这四个隔离级别如下：

• 读未提交（read uncommitted），指一个事务还没提交时，它做的变更就能被其他事务看到；
• 读提交（read committed），指一个事务提交之后，它做的变更才能被其他事务看到；
• 可重复读（repeatable read），指一个事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，MySQL InnoDB 引擎的默认隔离级别；
• 串行化（serializable ） ，对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。在多个事务对这条记录进行读写操作时，如果发生了读写冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行；

要点3：MySQL中，虽然支持4种隔离级别，但与SQL标准中规定的各级隔离级别允许发生的现象有出入。具体体现在：ＭySQL中 可重复读 隔离级别下，可以很大程序避免幻读现象发生。

针对上述不同隔离级别，并发事务可能出现的问题如下：

四种隔离级别具体实现：

• 「读未提交」隔离级别：可以读到未提交事务修改的数据，所以直接读取最新的数据就好了，不用采取什么隔离措施；
• 「读提交」和「可重复读」隔离级别：它们是通过 Read View 来实现的。区别在于创建 Read View时机不同(Read View 可读视图/数据快照，可以认为保存某个时间段数据库可以查看数据状态，只有当前事务能看到)
  • 「读提交」：每个语句执行前，会重新生成一个 Read View
  • 「可重复读」：启动事务时， 生成一个Read View，整个事务期间都用这个Read View
• 「串行化」隔离级别 ：通过加读写锁的方式来避免并行访问；

要点4：执行「开始事务」命令，并不意味着启动了事务。而在「可重复读」隔离级别下，开始事务的时间 会影响到Read View创建，会影响到数据库中数据观测情况

在 MySQL 有两种开启事务的命令，分别是：

• 第一种：begin/start transaction
• 第二种：start transaction with consistent snapshot

这两种开启事务的命令，事务的启动时机是不同的：

• 执行了 begin/start transaction 命令后，并不代表事务启动了。只有在执行这个命令后，执行了第一条 select 语句，才是事务真正启动的时机；
• 执行了 start transaction with consistent snapshot 命令，就会马上启动事务。

要点5：在不同的隔离级别下，数据库行为是有所不同的。Oracle数据库的默认隔离级别其实就是“读提交”，MySQL数据库隔离级别是“可重复读”。对于一些从Oracle迁移到MySQL的应用，为保证数据库隔离级别的一致，你一定要记得将MySQL的隔离级别设置为“读提交”。

以下命令可以用于查看数据库隔离级别：

1

show variables like 'transaction_isolation';

MVCC

ReadView中的字段 和 记录中两个隐藏列

记录中 隐藏列 包含一个 roll_pointer指针(指向undo日志) 还有 trx_id 字段用于记录某个事务对该聚簇索引记录进行改动

记录通过roll_pointer 指针，将所有事务操作过的记录版本 链成一条链，这就构成了 多版本记录。 之后，创建 一个事务的Read View后，其中包括了四个比较重要的字段：

根据创建Read View时机不同，上边字段中的值也会有所变动。比较有参考价值的字段就是：

• m_ids ：指的是在创建 Read View 时，当前数据库中「活跃事务」的事务 id 列表，注意是一个列表，“活跃事务”指的就是，启动了但还没提交的事务.

当一个 事务去访问记录的时候，要读取记录版本链上哪个版本的信息，依据如下：

• 创建Read View之后，所有记录根据 trx_id 可以划分成下列三种情况：

• 事务在去访问记录的时候，除了自己更新的记录总是可见的，还可以分为下列几种情况：
  • trx_id < min_trx_id：这个版本记录 在创建Read View前，已经提交事务了，该版本的记录 对当前事务是可见的。
  • trx_id > max_trx_id：说明当前这个版本 是在创建Read View后 才启动事务操作生成的， 该版本记录对当前事务 不可见
  • min_trx_id < trx_id < max_trx_id，需要判断trx_id是否在活跃的列表中：
    • trx_id在m_ids列表中：表示生成该版本记录的活跃事务 依然活跃着(还没提交事务)，该版本的记录对对当前事务不可见
    • trx_id不在m_ids列表中：表示生成 该版本记录的活跃事件已经被提交了，该记录对当前事务 可见

通过上边，版本链(记录 roll_pointer指针链接多个版本记录) 来控制 并发事务访问同一个记录时的行为(通过 记录trx_id隐藏列 和 Read View中字段对比) 称为MVCC(多版本并发控制)。

要点6：回滚日志(undo log)中的记录不能一直保留，什么时候删除？

A：在不需要的时候才删除。系统会判断，当没有事务再需要用到这些回滚日志时，回滚日志会被删除。当系统里没有比这个回滚日志更早的read-view的时候(可以理解为 回滚日志指向的记录的trx_id 在所有 read_view中，都处于已提交事务范畴，也就是对所有当前事务都可见)，此时可以以此删除undo log。

要点7：为什么建议你尽量不要使用长事务?

A：长事务意味着系统里面会存在很老的事务视图。由于这些事务随时可能访问数据库里面的任何数据，所以这个事务提交之前，数据库里面它可能用到的回滚记录都必须保留，这就会导致大量占用存储空间。除了对回滚段的影响，长事务还占用锁资源，也可能拖垮整个库

读提交 和 可重复读 隔离级别实现

• 读提交：每个 select 都会生成一个新的 Read View，这意味着 每次生成的Read View其 m_ids(活跃且未提交的事务id列表)可能不同。通过上边的 记录trx_id隐藏列和Read View字段对比规则来看，可以得出，事务期间的多次读取同一条数据，前后两次读的数据可能会不一样。

• 可重复读：启动事务时生成一个 Read View，然后整个事务期间都在用这个 Read View，这意味着 Read View其 m_ids(活跃且未提交的事务id列表)保持一致。通过上述规则来看，这种设计可以保证 在事务期间读到的数据都是事务启动前的记录。

事务的启动方式

MySQL中，默认执行一条SQL语句，都会 自动开启 和 自动提交 事务。

事务启动的其他方式有下边几种：

1. 显式启动事务语句， begin 或 start transaction；配套的提交语句是commit；回滚语句是rollback
2. set autocommit=0 命令：将当前这个线程的自动提交关掉。 意味着只执行一个select语句，这个事务就自动启动了，但是并不会自动提交。

要点8：有些客户端连接框架会默认连接成功后先执行一个set autocommit=0的命令。这就导致接下来的查询都在事务中，如果是长连接，就导致了意外的长事务。

建议：使用set autocommit=1， 并且总是通过显式语句的方式来启动事务。要不就是使用MySQL中默认的，执行一条SQL语句自动开启和自动提交事务。

可以在information_schema库的innodb_trx这个表中查询长事务，下边命令用于 ：查找持续时间超过60s的事务：

1

select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60

要点9：上述介绍了 MySQL事务隔离级别的现象和实现原理。通过实现原理分析了 长事务存在的风险，可以通过正确设置事务启动方式 来避免长事务。

四、深入浅出索引

五、全局锁、表锁 and 行锁

给表加个字段 怎么有那么多阻碍？

六、事务到底是隔离还是不隔离？

附录

参考文献

• MySQL 实战45讲 — 极客时间
• 小林Coding事务隔离级别怎么实现的？
• 《MySQL是怎样运行的》

版权信息

本文原载于kitebin.top，遵循CC BY-NC-SA 4.0协议，复制请保留原文出处。