Mysql45讲

March 26, 2022 15695

关于MySQL的发音：

The official way to pronounce “MySQL” is “My Ess Que Ell” (not “my sequel”), but we do not mind if you pronounce it as “my sequel” or in some other localized way.

一条SQL查询语句是如何执行的？

img

MySQL 可以分为 Server 层和存储引擎层两部分。不同的存储引擎共用一个 Server 层。

Server 层包括连接器、查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。

现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始成为了默认存储引擎。

连接器

1
mysql -h
ip -P


port -u$user -p

连接命令中的 mysql 是客户端工具，用来跟服务端建立连接。在完成经典的 TCP 握手后，连接器就要开始认证你的身份。

  1. 如果用户名或密码不对，你就会收到一个”Access denied for user”的错误，然后客户端程序结束执行。

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

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

显示用户正在运行的线程

1
show processlist

Command 列显示为“Sleep”的这一行，就表示现在系统里面有一个空闲连接。

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

建议尽量使用长连接

数据库里面，长连接是指连接成功后，如果客户端持续有请求，则一直使用同一个连接。短连接则是指每次执行完很少的几次查询就断开连接，下次查询再重新建立一个。

全部使用长连接后，有些时候 MySQL 占用内存涨得特别快

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

解决方法

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

查询缓存

MySQL 拿到一个查询请求后:

  1. 先到查询缓存：之前是否执行过这条语句，之前执行过的语句及其结果可能会以 key(查询语句)-value（查询结果） 对的形式，被直接缓存在内存中。(MySQL 8.0版本直接将查询缓存的整块功能删掉了 （弊大于利）查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空)。
  2. 没有找缓存结果：开始真正执行语句

分析器

  1. 词法分析：MySQL 需要识别出输入的的字符串（SQL语句）分别是什么，代表什么；
  2. 语法分析：根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个 SQL 语句是否满足 MySQL 语法。（语法错误提示：“You have an error in your SQL syntax”）。

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

优化器

在开始执行之前，还要先经过优化器的处理。

优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。

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

执行器

  1. 开始执行的时候，要先判断一下你对这个表 T 有没有执行查询的权限(在工程实现上，如果命中查询缓存，会在查询缓存返回结果的时候，做权限验证。查询也会在优化器之前调用 precheck 验证权限)；
  2. 如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口。

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

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

一条SQL更新语句是如何执行的？

1
update T set c=c+1 where ID=2;

img

执行流程

执行语句前要先连接数据库，这是连接器的工作

在一个表上有更新的时候，跟这个表有关的查询缓存会失效，所以这条语句就会把表 T 上所有缓存结果都清空。

分析器会通过词法和语法解析知道这是一条更新语句。优化器决定要使用 ID 这个索引。然后，执行器负责具体执行，找到这一行，然后更新。

两个重要的日志模块：redo log（重做日志）和 binlog（归档日志）（只要我们写的是DML语句（insert,update,delete,create）等等，那么我们在数据库服务端执行的时候就会涉及到 redo log(重做日志) 和 binlog(归档日志) 两个日志文件的变动）

redo log（重做日志）–InnoDB特有的日志

问题：如果每一次的更新操作都需要写进磁盘，然后磁盘也要找到对应的那条记录，然后再更新，整个过程 IO 成本、查找成本都很高。

解决：WAL 技术，WAL 的全称是 Write-Ahead Logging，它的关键点就是先写日志，再写磁盘。

当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log里面，并更新内存，这个时候更新就算完成了。同时，InnoDB 引擎会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做。

问题：InnoDB 的 redo log 是固定大小的，redo log 写满之后，先将日志中的部分记录写到磁盘，腾出redo log的空间继续写入。

img

write pos 是当前记录的位置，一边写一边后移，写到第 3 号文件末尾后就回到 0 号文件开头。check point 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件。

write pos 和 checkpoint 之间的是“redo log”上还空着的部分，可以用来记录新的操作。

如果 write pos 追上 checkpoint，表示“redo log”满了，这时候不能再执行新的更新，得停下来先擦掉一些记录，把 checkpoint 推进一下。

有了 redo log，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为 crash-safe。

redo log 用于保证 crash-safe 能力。innodb_flush_log_at_trx_commit 这个参数设置成 1 的时候，表示每次事务的 redo log 都直接持久化到磁盘。这个参数建议设置成 1，这样可以保证 MySQL 异常重启之后数据不丢失。

binlog（归档日志）–Server层 日志

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

sync_binlog 这个参数设置成 1 的时候，表示每次事务的 binlog 都持久化到磁盘。这个参数建议设置成 1，这样可以保证 MySQL 异常重启之后 binlog 不丢失。

redo log binlog
InnoDB 引擎特有的 MySQL 的 Server 层实现的，所有引擎都可以使用
物理日志：记录的是“在某个数据页上做了什么修改” 逻辑日志：记录的是这个语句的原始逻辑
循环写入，空间固定会用完 追加写入，binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志

浅色框表示是在 InnoDB 内部执行的，深色框表示是在执行器中执行的。

img

两阶段提交:为了让两份日志之间的逻辑一致

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

事务就是要保证一组数据库操作，要么全部成功，要么全部失败。在 MySQL 中，事务支持是在引擎层实现的（MySQL 原生的 MyISAM 引擎就不支持事务）。

ACID（Atomicity、Consistency、Isolation、Durability，即原子性、一致性、隔离性、持久性）

隔离性与隔离级

隔离得越严实，效率就会越低

当数据库上有多个事务同时执行的时候，就可能出现脏读（dirty read）、不可重复读（non-repeatable read）、幻读（phantom read）的问题，为了解决这些问题，就有了“隔离级别”的概念。

  1. 脏读：读到其他事务未提交的数据；
  2. 不可重复读：前后读取的记录内容不一致；
  3. 幻读：前后读取的记录数量不一致。

脏读：事务A查询数据后进行了一次修改且未提交，而事务B这个时候去查询，然后使用了这个数据，因为这个数据还没有被事务A 提交到数据库中，所以事务B的得到数据就是脏数据，对脏数据进行操作可能是不正确的。 不可重复读: 事务A访问了两次数据，但是这访问第二次之间事务B进行一次并进行了修改，导致事务A访问第二次的时候得到的数据与第一次不同，导致一个事务访问两次数据得到的数据不相同。因此叫做不可重复读。 幻读： 与不可重复读都点相似，只是这次是事务B在事务A访问第二次的之前做了一个新增，导致事务A第二次读取的时候发现了多的记录，这就是幻读。 丢失修改：事务A访问该数据，事务B也访问该数据，事务A修改了该数据，事务B也修改了该数据，这样导致事务A的修改被丢失，因此称为丢失修改； 不可重复度和幻读区别： 不可重复读主要是修改操作，幻读的主要在于新增或者删除。 幻读主要在于数据的条数变了，而不可重复读主要在于数据内容变了。

SQL 标准的事务隔离级别包括：读未提交（read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（serializable ）。

  1. 读未提交，一个事务还没提交时，它做的变更就能被别的事务看到；
  2. 读提交，一个事务提交之后，它做的变更才会被其他事务看到；
  3. 可重复读，一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的；(别人改数据的事务已经提交，我在我的事务中也不去读。)
  4. 串行化，顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。

这4种隔离级别，并行性能依次降低，安全性依次提高

在实现上，数据库里面会创建一个视图，访问的时候以视图的逻辑结果为准。

在“可重复读”隔离级别下，这个视图是在事务启动时创建的，整个事务存在期间都用这个视图。（事务启动时的视图可以认为是静态的，不受其他事务更新的影响。）

事务启动方式：

  1. 一致性视图是在执行第一个快照读语句时创建的；
  2. 一致性视图是在执行 start transaction with consistent snapshot 时创建的。

在“读提交”隔离级别下，这个视图是在每个 SQL 语句开始执行的时候创建的。

这里需要注意的是，“读未提交”隔离级别下直接返回记录上的最新值，没有视图概念；

而“串行化”隔离级别下直接用加锁的方式来避免并行访问。

Oracle 数据库的默认隔离级别其实就是“读提交”；

MySQL默认的隔离级别是”可重复读”。

事务隔离的实现

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

同一条记录在系统中可以存在多个版本，就是数据库的多版本并发控制（MVCC）

系统会判断，当没有事务再需要用到这些回滚日志时，回滚日志会被删除。（就是当系统里没有比这个回滚日志更早的视图的时候）

问题：为什么建议你尽量不要使用长事务

长事务意味着系统里面会存在很老的事务视图。由于这些事务随时可能访问数据库里面的任何数据，所以这个事务提交之前，数据库里面它可能用到的回滚记录都必须保留，这就会导致大量占用存储空间。

在 MySQL 5.5 及以前的版本，回滚日志是跟数据字典一起放在 ibdata 文件里的，即使长事务最终提交，回滚段被清理，文件也不会变小。

长事务还占用锁资源，也可能拖垮整个库，这个我们会在后面讲锁的时候展开。

事务的启动方式

  1. 显式启动事务语句， begin 或 start transaction。配套的提交语句是 commit，回滚语句是 rollback。

  2. set autocommit=0，这个命令会将这个线程的自动提交关掉。意味着如果你只执行一个 select 语句，这个事务就启动了，而且并不会自动提交。这个事务持续存在直到你主动执行 commit 或rollback语句，或者断开连接。

    建议总是使用 set autocommit=1, 通过显式语句的方式来启动事务。

  3. 用 commit work and chain 语法

    在 autocommit 为 1 的情况下，用 begin 显式启动的事务，如果执行 commit 则提交事务。如果执行 commit work and chain，则是提交事务并自动启动下一个事务，这样也省去了再次执行 begin 语句的开销。同时带来的好处是从程序开发的角度明确地知道每个语句是否处于事务中。

information_schema 库的 innodb_trx 这个表中查询长事务

问题：有什么方案来避免出现或者处理长事物？

首先，从应用开发端来看：

  1. 确认是否使用了 set autocommit=0。这个确认工作可以在测试环境中开展，把 MySQL 的 general_log 开起来，然后随便跑一个业务逻辑，通过 general_log 的日志来确认。一般框架如果会设置这个值，也就会提供参数来控制行为，你的目标就是把它改成 1。
  2. 确认是否有不必要的只读事务。有些框架会习惯不管什么语句先用 begin/commit 框起来。我见过有些是业务并没有这个需要，但是也把好几个 select 语句放到了事务中。这种只读事务可以去掉。
  3. 业务连接数据库的时候，根据业务本身的预估，通过 SET MAX_EXECUTION_TIME 命令，来控制每个语句执行的最长时间，避免单个语句意外执行太长时间。

其次，从数据库端来看：

  1. 监控 information_schema.Innodb_trx 表，设置长事务阈值，超过就报警 / 或者 kill；

  2. Percona 的 pt-kill 这个工具不错，推荐使用；

  3. 在业务功能测试阶段要求输出所有的 general_log，分析日志行为提前发现问题；

  4. 如果使用的是 MySQL 5.6 或者更新版本，把 innodb_undo_tablespaces 设置成 2（或更大的值）。如果真的出现大事务导致回滚段过大，这样设置后清理起来更方便。

    (innodb_undo_tablespaces是控制undo是否开启独立的表空间的参数

    1. 为0表示：undo使用系统表空间，即ibdata1
    2. 不为0表示：使用独立的表空间，一般名称为 undo001 undo002，存放地址的配置项为：innodb_undo_directory
    3. 一般innodb_undo_tablespaces 默认配置为0，innodb_undo_directory默认配置为当前数据目录)

深入浅出索引

索引的出现是为了提高查询效率

哈希表索引模型

哈希表是一种以 键 - 值（key-value）存储数据的结构，我们只要输入待查找的键即 key，就可以找到其对应的值即 Value。

思路：

用一个哈希函数把 key 换算成一个确定的位置，然后把 value 放在数组的这个位置。

问题：多个 key 值经过哈希函数的换算，会出现同一个值的情况

解决方法：拉出一个链表

缺点：因为不是有序的（比如四个 ID_card_n 的值并不是递增的，这样做的好处是增加新的 User 时速度会很快，只需要往后追加），所以哈希索引做区间查询的速度是很慢的。

哈希表这种结构适用于只有等值查询的场景，比如 Memcached 及其他一些 NoSQL 引擎。

有序数组索引模型

适用于等值查询和范围查询场景

根据身份证号查找对应的名字有序数组示意图

用二分法就可以快速得到，这个时间复杂度是 O(log(N))

优点：仅仅看查询效率，有序数组就是最好的数据结构

缺点：中间插入一个记录就必须得挪动后面所有的记录，成本太高

有序数组索引只适用于静态存储引擎

二叉搜索树索引模型

根据身份证号查找对应的名字二叉搜索树示意图

二叉搜索树的特点是：父节点左子树所有结点的值小于父节点的值，右子树所有结点的值大于父节点的值。

时间复杂度是 O(log(N))

优点：二叉树是搜索效率最高的

缺点：

  1. 为了维持 O(log(N)) 的查询复杂度，你就需要保持这棵树是平衡二叉树。为了做这个保证，更新的时间复杂度也是 O(log(N))。
  2. 索引不止存在内存中，还要写到磁盘上（所以大多数的数据库存储并不使用二叉树）

解决方法：为了让一个查询尽量少地读磁盘，就必须让查询过程访问尽量少的数据块。那么，我们就不应该使用二叉树，而是要使用“N 叉”树。这里，“N 叉”树中的“N”取决于数据块的大小。

N叉树优点：读写上的性能优点，以及适配磁盘的访问模式

在 MySQL 中，索引是在存储引擎层实现的，所以并没有统一的索引标准，即不同存储引擎的索引的工作方式并不一样。而即使多个存储引擎支持同一种类型的索引，其底层的实现也可能不同。

InnoDB 的索引模型

在 InnoDB 中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。

InnoDB 使用了 B+ 树索引模型，所以数据都是存储在 B+ 树中的。

每一个索引在 InnoDB 里面对应一棵 B+ 树

B+ 树能够很好地配合磁盘的读写特性，减少单次查询的磁盘访问次数。

假设有一个主键列为 ID 的表，表中有字段 k，并且在 k 上有索引

1
2
3
4
5
mysql> create table T(
id int primary key, 
k int not null, 
name varchar(16),
index (k))engine=InnoDB;

表中 R1~R5 的 (ID,k) 值分别为 (100,1)、(200,2)、(300,3)、(500,5) 和 (600,6)，两棵树的示例示意图如下:

InnoDB 的索引组织结构

图中不难看出，根据叶子节点的内容，索引类型分为主键索引和非主键索引。

查询时：

  1. 主键查询方式

    1
    select * from T where ID=500

    只需要搜索 ID 这棵 B+ 树

  2. 普通索引查询方式

    1
    select * from T where k=5

    需要先搜索 k 索引树，得到 ID 的值为 500，再到 ID 索引树搜索一次。这个过程称为回表。

基于非主键索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询。

主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引（clustered index）。

非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引（secondary index）。

索引维护

B+ 树为了维护索引有序性，在插入新值的时候需要做必要的维护。

页分裂:新插入的数据符合条件的数据页已经满了，根据 B+ 树的算法，这时候需要申请一个新的数据页，然后挪动部分数据过去。这个过程称为页分裂。（除了性能外，页分裂操作还影响数据页的利用率。原本放在一个页的数据，现在分到两个页中，整体空间利用率降低大约 50%。）

页合并：当相邻两个页由于删除了数据，利用率很低之后，会将数据页做合并。合并的过程，可以认为是分裂过程的逆过程。

性能和存储空间方面考量，自增主键往往是更合理的选择

自增主键的插入数据模式，正符合了递增插入的场景。每次插入一条新记录，都是追加操作，都不涉及到挪动其他记录，也不会触发叶子节点的分裂。

适合用业务字段直接做主键的场景:

  1. 只有一个索引；
  2. 该索引必须是唯一索引。

直接将这个索引设置为主键，可以避免每次查询需要搜索两棵树

覆盖索引

经过索引优化，避免回表过程

搜索由

1
select * from T where k between 3 and 5

变为

1
select ID from T where k between 3 and 5

只需要查 ID 的值，而 ID 的值已经在 k 索引树上了，因此可以直接提供查询结果，不需要回表。也就是说，在这个查询里面，索引 k 已经“覆盖了”我们的查询需求，我们称为覆盖索引。

由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

最左前缀原则

为一个不频繁的请求创建一个索引感觉很浪费

最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符。

问题：在建立联合索引的时候，如何安排索引内的字段顺序

评估标准:索引的复用能力

  1. 第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。
  2. (如果既有联合查询，又有基于 a、b 各自的查询)第二原则是,空间

索引下推

MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

1
mysql> select * from tuser where name like '张%' and age=10 and ismale=1;

无索引下推执行流程

索引下推执行流程

问题：重建索引时，是重建非主键索引还是主键索引？

重建非主键索引：

1
2
alter table T drop index k;
alter table T add index(k);

重建主键索引：

1
2
alter table T drop primary key;
alter table T add primary key(id);

重建索引 k 的做法是合理的，可以达到省空间的目的。

但是，重建主键的过程不合理。不论是删除主键还是创建主键，都会将整个表重建。所以连着执行这两个语句的话，第一个语句就白做了。这两个语句，你可以用这个语句代替 ： alter table T engine=InnoDB。

全局锁和表锁 ：给表加个字段怎么有这么多阻碍？

数据库锁设计的初衷是处理并发问题。作为多用户共享的资源，当出现并发访问的时候，数据库需要合理地控制资源的访问规则。而锁就是用来实现这些访问规则的重要数据结构。

MySQL 里面的锁大致可以分成全局锁、表级锁和行锁三类。

全局锁

全局锁就是对整个数据库实例加锁。MySQL 提供了一个加全局读锁的方法，命令是 Flush tables with read lock (FTWRL)。

当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。

全局锁的典型使用场景是，做全库逻辑备份。

全局锁的问题:

  1. 如果你在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆；
  2. 如果你在从库上备份，那么备份期间从库不能执行主库同步过来的 binlog，会导致主从延迟。

官方自带的逻辑备份工具是 mysqldump。当 mysqldump 使用参数–single-transaction 的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。而由于 MVCC 的支持，这个过程中数据是可以正常更新的。

问题：有了mysqldump功能，为什么还需要 FTWRL 呢？

一致性读是好，但前提是引擎要支持可重复读隔离级别。比如，对于 MyISAM 这种不支持事务的引擎，如果备份过程中有更新，总是只能取到最新的数据，那么就破坏了备份的一致性。这时，我们就需要使用 FTWRL 命令了。

-single-transaction 方法只适用于所有的表使用事务引擎的库。

问题：既然要全库只读，为什么不使用 set global readonly=true 的方式呢？

  1. 一在有些系统中，readonly 的值会被用来做其他逻辑，比如用来判断一个库是主库还是备库。因此，修改 global 变量的方式影响面更大，不建议你使用。
  2. 在异常处理机制上有差异。如果执行 FTWRL 命令之后由于客户端发生异常断开，那么 MySQL 会自动释放这个全局锁，整个库回到可以正常更新的状态。而将整个库设置为 readonly 之后，如果客户端发生异常，则数据库就会一直保持 readonly 状态，这样会导致整个库长时间处于不可写状态，风险较高。

表级锁

MySQL 里面表级别的锁有两种：一种是表锁，一种是元数据锁（meta data lock，MDL)。

表锁

表锁的语法是 lock tables … read/write。与 FTWRL 类似，可以用 unlock tables 主动释放锁，也可以在客户端断开的时候自动释放。需要注意，lock tables 语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。

元数据锁(MySQL 5.5 版本中引入)

MDL 不需要显式使用，在访问一个表的时候会被自动加上。

MDL 的作用是，保证读写的正确性。

  1. 当对一个表做增删改查操作的时候，加 MDL 读锁；
  2. 当要对表做结构变更操作的时候，加 MDL 写锁。

每执行一条DML、DDL语句时都会申请MDL锁，DML操作需要MDL读锁，DDL操作需要MDL写锁（MDL加锁过程是系统自动控制，无法直接干预，读读共享，读写互斥，写写互斥）

  1. 读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。
  2. 读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。

MDL 会直到事务提交才释放，在做表结构变更的时候，你一定要小心不要导致锁住线上查询和更新。

表锁一般是在数据库引擎 不支持行锁 的时候才会被用到的。

问题：如何安全地给小表加字段？

首先我们要解决长事务，事务不提交，就会一直占着 MDL 锁。在 MySQL 的 information_schema 库的 innodb_trx 表中，你可以查到当前执行中的事务。如果你要做 DDL 变更的表刚好有长事务在执行，要考虑先暂停 DDL，或者 kill 掉这个长事务。

问题：如果你要变更的表是一个热点表，虽然数据量不大，但是上面的请求很频繁，而你不得不加个字段，你该怎么做呢？

在 alter table 语句里面设定等待时间，如果在这个指定的等待时间里面能够拿到 MDL 写锁最好，拿不到也不要阻塞后面的业务语句，先放弃。之后开发人员或者 DBA 再通过重试命令重复这个过程。

DDL NOWAIT/WAIT n

1
2
ALTER TABLE tbl_name NOWAIT add column ...
ALTER TABLE tbl_name WAIT N add column ...

问题：如果你发现你的应用程序里有 lock tables 这样的语句，需要怎么做？

  1. 要么是你的系统现在还在用 MyISAM 这类不支持事务的引擎，那要安排升级换引擎；
  2. 要么是你的引擎升级了，但是代码还没升级。最后业务开发就是把 lock tables 和 unlock tables 改成 begin 和 commit，问题就解决了。

问题：备份一般都会在备库上执行，你在用–single-transaction 方法做逻辑备份的过程中，如果主库上的一个小表做了一个 DDL，比如给一个表上加了一列。这时候，从备库上会看到什么现象呢？

假设这个 DDL 是针对表 t1 的， 这里我把备份过程中几个关键的语句列出来：

1
2
3
4
5
6
7
8
9
10
11
12
Q1:SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Q2:START TRANSACTION  WITH CONSISTENT SNAPSHOT；
/* other tables */
Q3:SAVEPOINT sp;
/* 时刻 1 */
Q4:show create table `t1`;
/* 时刻 2 */
Q5:SELECT * FROM `t1`;
/* 时刻 3 */
Q6:ROLLBACK TO SAVEPOINT sp;
/* 时刻 4 */
/* other tables */
  1. 如果在 Q4 语句执行之前到达，现象：没有影响，备份拿到的是 DDL 后的表结构。
  2. 如果在“时刻 2”到达，则表结构被改过，Q5 执行的时候，报 Table definition has changed, please retry transaction，现象：mysqldump 终止；
  3. 如果在“时刻 2”和“时刻 3”之间到达，mysqldump 占着 t1 的 MDL 读锁，binlog 被阻塞，现象：主从延迟，直到 Q6 执行完成。
  4. 从“时刻 4”开始，mysqldump 释放了 MDL 读锁，现象：没有影响，备份拿到的是 DDL 前的表结构。

行锁功过：怎么减少行锁对性能的影响？

MySQL 的行锁是在引擎层由各个引擎自己实现的。

(MyISAM 引擎就不支持行锁)

两阶段锁

在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。

如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。

死锁和死锁检测

当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态，称为死锁。

当出现死锁以后，有两种策略：

  1. 一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置。

    问题：在 InnoDB 中，innodb_lock_wait_timeout 的默认值是 50s，意味着当出现死锁以后，第一个被锁住的线程要过 50s 才会超时退出，然后其他线程才有可能继续执行。对于在线服务来说，这个等待时间往往是无法接受的。

    我们又不可能直接把这个时间设置成一个很小的值，比如 1s。这样当出现死锁的时候，确实很快就可以解开，但如果不是死锁，而是简单的锁等待呢？所以，超时时间设置太短的话，会出现很多误伤。

  2. 另一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑。

    innodb_deadlock_detect 的默认值本身就是 on。

    问题：每当一个事务被锁的时候，就要看看它所依赖的线程有没有被别人锁住，如此循环，最后判断是否出现了循环等待，也就是死锁。

    每个新来的被堵住的线程，都要判断会不会由于自己的加入导致了死锁，这是一个时间复杂度是 O(n) 的操作。假设有 1000 个并发线程要同时更新同一行，那么死锁检测操作就是 100 万这个量级的。虽然最终检测的结果是没有死锁，但是这期间要消耗大量的 CPU 资源。因此，你就会看到 CPU 利用率很高，但是每秒却执行不了几个事务。

问题：怎么解决由热点行更新导致的性能问题呢？

问题的症结在于，死锁检测要耗费大量的 CPU 资源。

  1. 如果能确保这个业务一定不会出现死锁，可以临时把死锁检测关掉。

  2. 控制并发度

    并发控制要做在数据库服务端。如果你有中间件，可以考虑在中间件实现；如果你的团队有能修改 MySQL 源码的人，也可以做在 MySQL 里面。

    基本思路就是，对于相同行的更新，在进入引擎之前排队。这样在 InnoDB 内部就不会有大量的死锁检测工作了。

  3. 设计上优化

    将一行改成逻辑上的多行来减少锁冲突

事务到底是隔离的还是不隔离的？

在 MySQL 里，有两个“视图”的概念：

一个是 view。它是一个用查询语句定义的虚拟表，在调用的时候执行查询语句并生成结果。创建视图的语法是 create view … ，而它的查询方法与表一样。

另一个是 InnoDB 在实现 MVCC 时用到的一致性读视图，即 consistent read view，用于支持 RC（Read Committed，读提交）和 RR（Repeatable Read，可重复读）隔离级别的实现。它没有物理结构，作用是事务执行期间用来定义“我能看到什么数据”。

参考章节

“快照”在 MVCC 里是怎么工作的？

在可重复读隔离级别下，事务在启动的时候就“拍了个快照”。注意，这个快照是基于整库的。

InnoDB 里面每个事务有一个唯一的事务 ID，叫作 transaction id。它是在事务开始的时候向 InnoDB 的事务系统申请的，是按申请顺序严格递增的。

而每行数据也都是有多个版本的。每次事务更新数据的时候，都会生成一个新的数据版本，并且把 transaction id 赋值给这个数据版本的事务 ID，记为 row trx_id。同时，旧的数据版本要保留，并且在新的数据版本中，能够有信息可以直接拿到它。

也就是说，数据表中的一行记录，其实可能有多个版本 (row)，每个版本有自己的 row trx_id。

在实现上， InnoDB 为每个事务构造了一个数组，用来保存这个事务启动瞬间，当前正在“活跃”的所有事务 ID。“活跃”指的就是，启动了但还没提交。

数组里面事务 ID 的最小值记为低水位，当前系统里面已经创建过的事务 ID 的最大值加 1 记为高水位。这个视图数组和高水位，就组成了当前事务的一致性视图（read-view）。

数据版本的可见性规则，就是基于数据的 row trx_id 和这个一致性视图的对比结果得到的。

对于当前事务的启动瞬间来说，一个数据版本的 row trx_id，有以下几种可能：

  1. 如果落在绿色部分，表示这个版本是已提交的事务或者是当前事务自己生成的，这个数据是可见的；

  2. 如果落在红色部分，表示这个版本是由将来启动的事务生成的，是肯定不可见的；

  3. 如果落在黄色部分，那就包括两种情况

    1. 若 row trx_id 在数组中，表示这个版本是由还没提交的事务生成的，不可见；
    2. 若 row trx_id 不在数组中，表示这个版本是已经提交了的事务生成的，可见。

    (高水位的定义是事务创建时所有未提交的事务ID的最大值+1是高水位，但并不是小于高水位大于低水位的事务就都没有提交。所以row trx_id 在这个范围内却不在数组中就是已经提交了的可见)

一个数据版本，对于一个事务视图来说，除了自己的更新总是可见以外，有三种情况：

  1. 版本未提交，不可见；
  2. 版本已提交，但是是在视图创建后提交的，不可见；
  3. 版本已提交，而且是在视图创建前提交的，可见。

更新逻辑

更新数据都是先读后写的，而这个读，只能读当前的值，称为“当前读”（current read）。

除了 update 语句外，select 语句如果加锁，也是当前读。（lock in share mode 或 for update）

问题：事务的可重复读的能力是怎么实现的？

可重复读的核心就是一致性读（consistent read）；

而事务更新数据的时候，只能用当前读。如果当前的记录的行锁被其他事务占用的话，就需要进入锁等待。

而读提交的逻辑和可重复读的逻辑类似，它们最主要的区别是：

  1. 在可重复读隔离级别下，只需要在事务开始的时候创建一致性视图，之后事务里的其他查询都共用这个一致性视图；
  2. 在读提交隔离级别下，每一个语句执行前都会重新算出一个新的视图。

这里需要说明一下，“start transaction with consistent snapshot; ”的意思是从这个语句开始，创建一个持续整个事务的一致性快照。所以，在读提交隔离级别下，这个用法就没意义了，等效于普通的 start transaction。

查看评论