Kierke's Blog
MySQL 实战 45 讲 Study Notes
Kierke
  2018-12-06   2024-07-06    5.3k Words  18 Mins  

极客时间专栏《MySQL 实战 45 讲》（林晓斌 网名丁奇）学习笔记

开篇词 (1讲)

开篇词 | 这一次，让我们一起来搞懂MySQL

即使我只是一个开发工程师，只是 MySQL 的用户，在了解了一个个系统模块的原理后，再来使用它，感觉是完全不一样的。当在代码里写下一行数据库命令的时候，我就能想到它在数据库端将怎么执行，它的性能是怎么样的，怎样写能让我的应用程序访问数据库的性能最高。进一步，哪些数据处理让数据库系统来做性能会更好，哪些数据处理在缓存里做性能会更好，我心里也会更清楚。在建表和建索引的时候，我也会更有意识地为将来的查询优化做综合考虑，比如确定是否使用递增主键、主键的列怎样选择，等等。

我希望这个专栏能够帮助这样的一些开发者：他们正在使用 MySQL，知道如何写出逻辑正确的 SQL 语句来实现业务目标，却不确定这个语句是不是最优的；他们听说了一些使用数据库的最佳实践，但是更想了解为什么这么做；他们使用的数据库偶尔会出问题，亟需了解如何更快速、更准确地定位问题，甚至自己解决问题……




基础篇 (8讲)

01 | 基础架构：一条SQL查询语句是如何执行的？

  • 看一个事儿千万不要直接陷入细节里，你应该先鸟瞰其全貌，这样能够帮助你从高维度理解问题。

MySQL 的基本架构

image

概述

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

连接器

  • 连接器负责跟客户端建立连接，获取权限，维持和管理连接
1
2
mysql -h$ip -P$port -u$user -p
# 虽然密码也可以直接跟在 -p 后面写在命令行中，但这样可能会导致你的密码泄露
  • 连接命令中的 mysql 是客户端工具，用来跟服务端建立连接。
  • （1）完成经典的 TCP 握手
  • （2）连接器就要开始认证身份，这个时候用的就是输入的用户名和密码。
    • 如果用户名或密码不对，你就会收到一个”Access denied for user”的错误，然后客户端程序结束执行
    • 如果用户名密码认证通过，连接器会到权限表里面查出用户拥有的权限。之后，这个连接里面的权限判断逻辑，都将依赖于此时读到的权限。

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

  • 连接完成后，如果你没有后续的动作，这个连接就处于空闲状态。可以在 show processlist 命令中看到它。

  • 客户端如果太长时间没动静，连接器就会自动将它断开。这个时间是由参数 wait_timeout 控制的，默认值是 8 小时。

  • 如果在连接被断开之后，客户端再次发送请求的话，就会收到一个错误提醒： Lost connection to MySQL server during query。

  • 数据库的连接：

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

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

  • 解决方案：

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

查询缓存

  • 连接建立完后，就可以执行select语句了
  • MySQL 拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句。之前执行过的语句及其结果可能会以 key-value 对的形式，被直接缓存在内存中。key 是查询的语句，value 是查询的结果。如果你的查询能够直接在这个缓存中找到 key，那么这个 value 就会被直接返回给客户端。
  • 如果语句不在查询缓存中，就会继续后面的执行阶段。执行完成后，执行结果会被存入查询缓存中。（查询命中缓存，MySQL 不需要执行后面的复杂操作，就可以直接返回结果，这个效率会很高。）
  • 但是大多数情况下我会建议你不要使用查询缓存，为什么呢？因为查询缓存往往弊大于利。
  • 查询缓存的失效非常频繁：只要有对一个表的更新，这个表上所有的查询缓存都会被清空。
  • 对于更新压力大的数据库来说，查询缓存的命中率会非常低。
  • 除非你的业务就是有一张静态表，很长时间才会更新一次。比如，一个系统配置表，那这张表上的查询才适合使用查询缓存。
  • 可以将参数 query_cache_type 设置成 DEMAND，这样对于默认的 SQL 语句都不使用查询缓存。
  • 对于你确定要使用查询缓存的语句，可以用 SQL_CACHE 显式指定
1
select SQL_CACHE * from T where ID=10
  • MySQL 8.0 版本直接将查询缓存的整块功能删掉了

分析器

  • MySQL 需要知道你要做什么，因此需要对 SQL 语句做解析
  • 分析器先会做“词法分析”
    • MySQL 从你输入的”select”这个关键字识别出来，这是一个查询语句。它也要把字符串“T”识别成“表名 T”，把字符串“ID”识别成“列 ID”。
  • 做完了这些识别以后，就要做“语法分析”
    • 根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个 SQL 语句是否满足 MySQL 语法。
    • 如果你的语句不对，就会收到“You have an error in your SQL syntax”的错误提醒

优化器

  • 优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。
  • 优化器阶段完成后，这个语句的执行方案就确定下来了

执行器

  • 开始执行的时候，要先判断一下你对这个表 T 有没有执行查询的权限，如果没有，就会返回没有权限的错误
  • 在工程实现上，如果命中查询缓存，会在查询缓存返回结果的时候，做权限验证。查询也会在优化器之前调用 precheck 验证权限
  • 如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口。
  • 例如我们这个例子中的表 T 中，ID 字段没有索引，那么执行器的执行流程是这样的：
    • （1）调用 InnoDB 引擎接口取这个表的第一行，判断 ID 值是不是 10，如果不是则跳过，如果是则将这行存在结果集中；
    • （2）调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行。
    • （3）执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。
    • 至此，这个语句就执行完成了。
  • 对于有索引的表，执行的逻辑也差不多。第一次调用的是“取满足条件的第一行”这个接口，之后循环取“满足条件的下一行”这个接口，这些接口都是引擎中已经定义好的。

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

在有些场景下，执行器调用一次，在引擎内部则扫描了多行，因此引擎扫描行数跟 rows_examined 并不是完全相同的



02 | 日志系统：一条SQL更新语句是如何执行的？

image
  • 查询语句的那一套流程，更新语句也是同样会走一遍。
    • 执行语句前要先连接数据库
    • 分析器会通过词法和语法解析知道这是一条更新语句。优化器决定要使用 ID 这个索引。然后，执行器负责具体执行，找到这一行，然后更新。
  • 与查询流程不一样的是，更新流程还涉及两个重要的日志模块
    • redo log（重做日志）
    • binlog（归档日志）

重要的日志模块：redo log

  • 当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log里面，并更新内存，这个时候更新就算完成了。
  • 同时，InnoDB 引擎会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做
  • InnoDB 的 redo log 是固定大小的，比如可以配置为一组 4 个文件，每个文件的大小是 1GB。总共就可以记录 4GB 的操作。从头开始写，写到末尾就又回到开头循环写
image
  • write pos 是当前记录的位置，一边写一边后移，写到第 3 号文件末尾后就回到 0 号文件开头。checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件。
  • write pos 和 checkpoint 之间的是“粉板”上还空着的部分，可以用来记录新的操作。如果 write pos 追上 checkpoint，表示“粉板”满了，这时候不能再执行新的更新，得停下来先擦掉一些记录，把 checkpoint 推进一下。
  • 有了 redo log，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为crash-safe。

重要的日志模块：binlog

  • MySQL 整体来看，其实就有两块：
    • Server 层：它主要做的是 MySQL 功能层面的事情。Server 层也有自己的日志，称为 binlog（归档日志）
    • 引擎层：负责存储相关的具体事宜。redo log 是 InnoDB 引擎特有的日志

因为最开始 MySQL 里并没有 InnoDB 引擎。MySQL 自带的引擎是 MyISAM，但是 MyISAM 没有 crash-safe 的能力，binlog 日志只能用于归档。而 InnoDB 是另一个公司以插件形式引入 MySQL 的，既然只依靠 binlog 是没有 crash-safe 能力的，所以 InnoDB 使用另外一套日志系统——也就是 redo log 来实现 crash-safe 能力。

redo log VS binglog

  • 两种日志有以下三点不同：
    • （1）redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。
    • （2）redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。
    • （3）redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

执行器和 InnoDB 引擎在执行这个简单的 update 语句时的内部流程

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

两阶段提交

怎样让数据库恢复到半个月内任意一秒的状态？

  • 当需要恢复到指定的某一秒时，比如某天下午两点发现中午十二点有一次误删表，需要找回数据，那你可以这么做：
    • （1）首先，找到最近的一次全量备份，如果你运气好，可能就是昨天晚上的一个备份，从这个备份恢复到临时库；
    • （2）然后，从备份的时间点开始，将备份的 binlog 依次取出来，重放到中午误删表之前的那个时刻。
    • （3）这样你的临时库就跟误删之前的线上库一样了，然后你可以把表数据从临时库取出来，按需要恢复到线上库去。

为什么必须有“两阶段提交”?

  • 这是为了让两份日志之间的逻辑一致
  • 用反证法来进行解释：
    • 由于 redo log 和 binlog 是两个独立的逻辑，如果不用两阶段提交，要么就是先写完 redo log 再写 binlog，或者采用反过来的顺序。
    • 仍然用前面的 update 语句来做例子。假设当前 ID=2 的行，字段 c 的值是 0，再假设执行 update 语句过程中在写完第一个日志后，第二个日志还没有写完期间发生了 crash，会出现什么情况呢？
      • （1）先写 redo log 后写 binlog。假设在 redo log 写完，binlog 还没有写完的时候，MySQL 进程异常重启。由于我们前面说过的，redo log 写完之后，系统即使崩溃，仍然能够把数据恢复回来，所以恢复后这一行 c 的值是 1。
        但是由于 binlog 没写完就 crash 了，这时候 binlog 里面就没有记录这个语句。因此，之后备份日志的时候，存起来的 binlog 里面就没有这条语句。
        然后你会发现，如果需要用这个 binlog 来恢复临时库的话，由于这个语句的 binlog 丢失，这个临时库就会少了这一次更新，恢复出来的这一行 c 的值就是 0，与原库的值不同。
      • （2）先写 binlog 后写 redo log。如果在 binlog 写完之后 crash，由于 redo log 还没写，崩溃恢复以后这个事务无效，所以这一行 c 的值是 0。但是 binlog 里面已经记录了“把 c 从 0 改成 1”这个日志。所以，在之后用 binlog 来恢复的时候就多了一个事务出来，恢复出来的这一行 c 的值就是 1，与原库的值不同。
    • 可以看到，如果不使用“两阶段提交”，那么数据库的状态就有可能和用它的日志恢复出来的库的状态不一致。

当你需要扩容的时候，也就是需要再多搭建一些备库来增加系统的读能力的时候，现在常见的做法也是用全量备份加上应用 binlog 来实现的，这个“不一致”就会导致你的线上出现主从数据库不一致的情况。

  • redo log 和 binlog 都可以用于表示事务的提交状态，而两阶段提交就是让这两个状态保持逻辑上的一致。

小结

  • 物理日志 redo log
  • 逻辑日志 binlog
  • redo log 用于保证 crash-safe 能力。innodb_flush_log_at_trx_commit 这个参数设置成 1 的时候，表示每次事务的 redo log 都直接持久化到磁盘。这个参数我建议你设置成 1，这样可以保证 MySQL 异常重启之后数据不丢失。
  • sync_binlog 这个参数设置成 1 的时候，表示每次事务的 binlog 都持久化到磁盘。这个参数我也建议你设置成 1，这样可以保证 MySQL 异常重启之后 binlog 不丢失。
  • 两阶段提交是跨系统维持数据逻辑一致性时常用的一个方案


03 | 事务隔离：为什么你改了我还看不见？

概述

  • 事务就是要保证一组数据库操作，要么全部成功，要么全部失败。
  • 在 MySQL 中，事务支持是在引擎层实现的
  • MySQL 是一个支持多引擎的系统，但并不是所有的引擎都支持事务。
  • MySQL 原生的 MyISAM 引擎就不支持事务，这也是 MyISAM 被 InnoDB 取代的重要原因之一。

隔离性与隔离级别

  • ACID（Atomicity、Consistency、Isolation、Durability，即原子性、一致性、隔离性、持久性）
  • 当数据库上有多个事务同时执行的时候，就可能出现脏读（dirty read）、不可重复读（non-repeatable read）、幻读（phantom read）的问题，为了解决这些问题，就有了“隔离级别”的概念。
  • 隔离得越严实，效率就会越低。因此很多时候，我们都要在二者之间寻找一个平衡点。
  • SQL 标准的事务隔离级别包括：读未提交（read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（serializable ）
    • 读未提交是指，一个事务还没提交时，它做的变更就能被别的事务看到。
    • 读提交是指，一个事务提交之后，它做的变更才会被其他事务看到。
    • 可重复读是指，一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。
    • 串行化，顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。
  • 在实现上，数据库里面会创建一个视图，访问的时候以视图的逻辑结果为准。
    • 在“可重复读”隔离级别下，这个视图是在事务启动时创建的，整个事务存在期间都用这个视图
    • 在“读提交”隔离级别下，这个视图是在每个 SQL 语句开始执行的时候创建的。



Reference




Remark

  1. 两阶段提交
1
2
3
4
5
<font color=red></font>
![]()
<img src="" title="图片名称" alt="图片无法正常加载展示！" width="100%" height="100%" >
<img src="" width="70%">
****


MySQL 实战 45 讲 Study Notes
2018/MySQL-实战-45-讲-Study-Notes/
Author
Kierke
Published
2018-12-06 21:33
License
  1. 开篇词 (1讲)
    1. 开篇词 | 这一次，让我们一起来搞懂MySQL
  2. 基础篇 (8讲)
    1. 01 | 基础架构：一条SQL查询语句是如何执行的？
      1. MySQL 的基本架构
        1. 概述
        2. 连接器
        3. 查询缓存
        4. 分析器
        5. 优化器
        6. 执行器
    2. 02 | 日志系统：一条SQL更新语句是如何执行的？
      1. 重要的日志模块：redo log
      2. 重要的日志模块：binlog
        1. redo log VS binglog
        2. 执行器和 InnoDB 引擎在执行这个简单的 update 语句时的内部流程
      3. 两阶段提交
        1. 怎样让数据库恢复到半个月内任意一秒的状态？
        2. 为什么必须有“两阶段提交”?
      4. 小结
    3. 03 | 事务隔离：为什么你改了我还看不见？
      1. 概述
      2. 隔离性与隔离级别
  3. Reference
  4. Remark
  1. 开篇词 (1讲)
    1. 开篇词 | 这一次，让我们一起来搞懂MySQL
  2. 基础篇 (8讲)
    1. 01 | 基础架构：一条SQL查询语句是如何执行的？
      1. MySQL 的基本架构
        1. 概述
        2. 连接器
        3. 查询缓存
        4. 分析器
        5. 优化器
        6. 执行器
    2. 02 | 日志系统：一条SQL更新语句是如何执行的？
      1. 重要的日志模块：redo log
      2. 重要的日志模块：binlog
        1. redo log VS binglog
        2. 执行器和 InnoDB 引擎在执行这个简单的 update 语句时的内部流程
      3. 两阶段提交
        1. 怎样让数据库恢复到半个月内任意一秒的状态？
        2. 为什么必须有“两阶段提交”?
      4. 小结
    3. 03 | 事务隔离：为什么你改了我还看不见？
      1. 概述
      2. 隔离性与隔离级别
  3. Reference
  4. Remark