从这里开始，跟我一起搞懂 MySQL!

提前申明：《MySQL 基础实战》系列是学习极客时间林晓斌的《MySQL实战45讲》的整理和总结，希望大家仅做为学习使用！

ACID是数据库管理系统中用于确保事务处理可靠性的四个基本特性，具体如下：

1.原子性（Atomicity）：事务被视为一个不可分割的工作单元，事务中的所有操作要么全部执行成功，要么全部不执行，就像一个原子一样 indivisible。这意味着事务处理过程中，如果任何一部分操作失败，整个事务都会被回滚，以保持数据的一致性。
例如，在银行转账过程中，从账户A转出金额和向账户B存入金额必须同时成功或失败，不能只完成其中一项。

2.一致性（Consistency）：确保事务执行前后，数据库从一个有效状态转换到另一个有效状态，符合预定义的业务规则或约束条件。它关注的是数据的逻辑正确性和业务含义的完整性。

例子：假设有一个库存管理系统，业务规则要求商品的库存数量不能为负数。当一个订单事务发生时，系统需要减少相应商品的库存量。一致性确保在事务执行后，即使多个订单同时处理同一件商品，商品的库存数量也不会变成负数，而是准确反映了实际的剩余库存，保持库存数据的逻辑合理性。

3.隔离性（Isolation）：多个事务并发执行时，每个事务好像在单独、序列化执行一样，彼此之间互不影响。事务之间的隔离程度可以通过不同的隔离级别来控制，如读未提交（Read Uncommitted）、读已提交（Read Committed）、可重复读（Repeatable Read）和串行化（Serializable）。例如，用户A在查看账户余额时，用户B正好在向A转账，良好的隔离性保证A看到的余额不会是转账过程中的中间状态。

4.持久性（Durability）
持久性指的是一旦事务被提交，其结果就会被永久保存到数据库中，即使后续发生系统崩溃或电源故障等意外情况，数据的改变也不会丢失。

例子：在电商平台上，用户完成一笔购买交易后，系统会从用户的支付账户扣除相应的金额，并将订单状态标记为“已支付”。持久性保证，即使在这之后系统遭遇了硬件故障或重启，用户账户的扣款操作和订单状态的变化依然会被保留，用户下次登录时能够看到交易记录和正确的账户余额，确保了用户资金变动的不可逆性和可靠性。

MySQL中的事务两阶段提交（Two-Phase Commit, 2PC）机制主要是为了保障ACID特性中的原子性（Atomicity）和持久性（Durability），同时它也是实现事务隔离性（Isolation）的一个重要环节。

原子性（Atomicity）：两阶段提交确保了事务的所有操作要么全部完成，要么全部不完成。在第一阶段，所有参与事务的节点都会准备（Prepare）提交，这意味着它们承诺如果事务被决定提交，它们都将提交自己的那部分更改。如果在第一阶段有任何参与者无法准备提交，整个事务就会在第二阶段被中止，所有已准备的参与者都会被告知撤销（Rollback），从而保证了事务作为一个整体的不可分割性。
持久性（Durability）：在两阶段提交的第二阶段，如果所有参与者都已准备好提交，协调者会发出提交（Commit）指令。参与者在接收到提交指令后，会将之前准备的更改永久写入到数据库中，同时也会持久化事务日志（如Redo Log和Binlog），确保即使在提交后发生系统故障，也能从日志中恢复事务，保证事务的更改不会丢失。

至于隔离性（Isolation），两阶段提交本身并不直接提供事务隔离，它是通过在单个数据库节点上实现的事务隔离级别（如READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ、SERIALIZABLE）来保证的。不过，两阶段提交在分布式环境下确保了不同节点上的事务能够按照预期的隔离级别正确地交互和提交，从而间接支持了隔离性的实现。

综上，两阶段提交机制是MySQL确保分布式事务满足ACID属性，特别是在原子性和持久性方面，的关键技术之一。

事务隔离的实现是由多版本并发控制（MVCC），通过undo log版本链（回滚日志，记录redo log的反操作）和read-view实现事务隔离（MVCC）。

架构示意图
- Server 层：包括连接器、查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。
  - binlog（归档日志）
    - Server层日志；
    - 逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。
    - 追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。
- 存储引擎层：负责数据的存储和提取。其架构模式是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始成为了默认存储引擎。
  - redo log
    - 引擎层 InnoDB 特有的日志；
    - 物理日志，记录的是“在某个数据页上做了什么修改”
    - 循环写的，空间固定会用完（比喻擦除粉板记录到账本）

WAL（Write Ahead Log ，译为进行写操作前先写日志。）

逻辑：
- 它的关键点就是先写日志，再写磁盘，也就是先写粉板，等不忙的时候再写账本。
- 具体来说，当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log（粉板）里面，并更新内存，这个时候更新就算完成（返回给客户端成功）了。同时，InnoDB 引擎会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做，这就像打烊以后掌柜做的事。
redo log：
- 擦除粉板记录到账本
- crash-safe：即保证即使数据库发生异常重启，之前提交的记录都不会丢失
  - 只要赊账记录记在了粉板上或写在了账本上，之后即使掌柜忘记了，比如突然停业几天，恢复生意后依然可以通过账本和粉板上的数据明确赊账账目。

两阶段提交：执行器和 InnoDB 引擎在执行这个简单的 update 语句时的内部流程
mysql> create table T(ID int primary key, c int);

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

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

- 为什么一定要两阶段提交？
  - 　由于 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，与原库的值不同。
    可以看到，如果不使用“两阶段提交”，那么数据库的状态就有可能和用它的日志恢复出来的库的状态不一致。
  - 简单说，redo log 和 binlog 都可以用于表示事务的提交状态，而两阶段提交就是让这两个状态保持逻辑上的一致。
    - 恢复：redolog和binlog具有关联行，在恢复数据时，redolog用于恢复主机故障时的未更新的物理数据，binlog用于备份操作。每个阶段的log操作都是记录在磁盘的，在恢复数据时
      binlog事务是否成功（是/否），决定是回滚还是执行。
      - redolog 状态为commit则说明binlog也成功，直接恢复数据；
    - 扩容：数据库的扩容，即增加备份库来提高系统读数据库的能力的时候，常采取“全量备份 + binlog”实现。假如binlog和redo log记录的事务的逻辑状态不一致，则会导致严重的主从数据库数据不一致问题。

事务隔离

事务特性：提到事务，你肯定会想到 ACID（Atomicity、Consistency、Isolation、Durability，即原子性、一致性、隔离性、持久性），今天我们就来说说其中 I，也就是“隔离性”。
隔离性与隔离级别：当数据库上有多个事务同时执行的时候，就可能出现脏读（dirty read）、不可重复读（non-repeatable read）、幻读（phantom read）的问题，为了解决这些问题，就有了“隔离级别”的概念。隔离得越严实，效率就会越低。因此很多时候，我们都要在二者之间寻找一个平衡点。
- 问题（好例子）
  - 脏读：脏读指的是读到了其他事务未提交的数据，未提交意味着这些数据可能会回滚，也就是可能最终不会存到数据库中，也就是不存在的数据。读到了并一定最终存在的数据，这就是脏读。
  - 不可重复读：通常针对数据更新（UPDATE）操作。
    - 可重复读：可重复读指的是在一个事务内，最开始读到的数据和事务结束前的任意时刻读到的同一批数据都是一致的。通常针对数据更新（UPDATE）操作。
    - 不可重复读：
      - 是指在事务1内，读取了一个数据，事务1还没有结束时
      - 事务2也访问了这个数据，修改了这个数据（UPDATE），并提交。
      - 紧接着，事务1又读这个数据。由于事务2的修改，那么事务1两次读到的的数据可能是不一样的，因此称为是不可重复读。
  - 幻读：幻读是针对数据插入（INSERT）操作来说的。
    - 事务A 在读取某个范围内的记录时（事务1查询id<10的记录时，返回了2条记录）
    - 此时事务B 又在该范围内插入（INSERT）了新的记录（事务2插入了一条id为3的记录，并提交）
    - 再次读取该范围的记录时，会产生幻行，这就叫幻读。（接着事务1查询id<10的记录时，返回了3条记录）
    - 什么是幻读解释？
    - 例子1：
    - --样例，先建表和插入语句，InnoDB 的默认事务隔离级别是可重复读（下面的解释也基于此） CREATE TABLE `t` ( `id` int(11) NOT NULL, `c` int(11) DEFAULT NULL, `d` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `c` (`c`) ) ENGINE=InnoDB; insert into t values(0,0,0),(5,5,5), (10,10,10),(15,15,15),(20,20,20),(25,25,25);
      下面先来看一下这个场景（注意：这是我假设的一个场景）：图 1 假设只在 id=5 这一行加行锁
    - 可以看到，session A 里执行了三次查询，分别是 Q1、Q2 和 Q3。它们的 SQL 语句相同，都是 select * from t where d=5 for update。这个语句的意思你应该很清楚了，查所有 d=5 的行，而且使用的是当前读，并且加上写锁。现在，我们来看一下这三条 SQL 语句，分别会返回什么结果。
      - Q1 只返回 id=5 这一行；
      - 在 T2 时刻，session B 把 id=0 这一行的 d 值改成了 5，因此 T3 时刻 Q2 查出来的是 id=0 和 id=5 这两行；
      - 在 T4 时刻，session C 又插入一行（1,1,5），因此 T5 时刻 Q3 查出来的是 id=0、id=1 和 id=5 的这三行。
      - 其它注意
        
        其中，Q3 读到 id=1 这一行的现象，被称为“幻读”。也就是说，幻读指的是一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行。
        
        这里，我需要对“幻读”做一个说明：
        
        在可重复读隔离级别下，普通的查询是快照读，是不会看到别的事务插入的数据的。因此，幻读在“当前读”下才会出现。
        
        上面 session B 的修改结果，被 session A 之后的 select 语句用“当前读”看到，不能称为幻读。幻读仅专指“新插入的行”。
- 解决方案
  - 读未提交（read uncommitted）：一个事务还没提交时，它做的变更就能被别的事务看到。
  - 读提交（read committed）：一个事务提交之后，它做的变更才会被其他事务看到。
  - 可重复读（repeatable read）：一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。
  - 串行化（serializable ）：顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。

4 种隔离级别对这三个问题的解决程度。

	可能问题（多个事务执行时）
隔离级别（解决方案）	脏读	不可重复读	幻读
读未提交	可能	可能	可能
读提交	不可能	可能	可能
可重复读	不可能	不可能	可能
串行化	不可能	不可能	不可能

只有串行化的隔离级别解决了全部这 3 个问题，其他的 3 个隔离级别都有缺陷。

在实现上，数据库里面会创建一个视图，访问的时候以视图的逻辑结果为准。
- 在“可重复读”隔离级别下，这个视图是在事务启动时创建的，整个事务存在期间都用这个视图。
  - MVCC视图实在开始事务的时候就创建好了，这个视图会一直使用，直到该事务结束
- 在“读提交”隔离级别下，这个视图是在每个 SQL 语句开始执行的时候创建的。
  - MVCC视图会在每一个语句前创建一个，所以在RC级别下，一个事务是可以看到另外一个事务已经提交的内容，因为它在每一次查询之前都会重新给予最新的数据创建一个新的MVCC视图。
- 这里需要注意的是，“读未提交”隔离级别下直接返回记录上的最新值，没有视图概念；(直接返回最小行数据）
- 而“串行化”隔离级别下直接用加锁的方式来避免并行访问。
不同数据库的默认隔离级别，从不同数据库迁移过来的时候注意
- Oracle：读提交
- MySQL：可重复读（阿里：读提交，查看另一篇文章）show variables like 'transaction_isolation';
事务隔离的实现是由多版本并发控制（MVCC），通过undo log版本链和read-view实现事务隔离（MVCC）。
- 另外在 MySQL 中，实际上每条记录在更新的时候都会同时记录一条回滚操作。记录上的最新值，通过回滚操作，都可以得到前一个状态的值。即除了记录变更记录，还会记录一条变更相反的回滚操作记录，前者记录在redo log，后者记录在undo log。
- 假设一个值从 1 被按顺序改成了 2、3、4，在回滚日志里面就会有类似下面的记录。
  这一点其实就是一个版本迭代记录，类似功能的实现的还有GIT等等这种版本管理工具，只有将每次修改都记录下来，才能方便回滚到指定的每次提交。
  
  当前值是 4，但是在查询这条记录的时候，不同时刻启动的事务会有不同的 read-view。如图中看到的，在视图 A、B、C 里面，这一个记录的值分别是 1、2、4，同一条记录在系统中可以存在多个版本，就是数据库的多版本并发控制（MVCC）。对于 read-view A，要得到 1，就必须将当前值依次执行图中所有的回滚操作得到。
  - 【在可重复读隔离级别中，表中的数据其实已经改变，在前面的视图里，需要查找某条记录时，是通过取当前数据，再取视图对应的回滚段回滚到该视图的值。】
  - 同时你会发现，即使现在有另外一个事务正在将 4 改成 5，这个事务跟 read-view A、B、C 对应的事务是不会冲突的
    - 【因为有行锁保证不会出现一个事务把4改到5,然后另外一个事务回滚的情况】
你一定会问，回滚日志总不能一直保留吧，什么时候删除呢？答案是，在不需要的时候才删除。也就是说，系统会判断，当没有事务再需要用到这些回滚日志时，回滚日志会被删除。
- 什么时候才不需要了呢？就是当系统里没有比这个回滚日志更早的 read-view 的时候。当没有比回滚日志更早的读视图（可重复读隔离级别中，read-view读视图在事务开启时创建，事务提交的时候删除）的时候，这个数据不会再有谁驱使它回滚了，这个回滚日志也就失去了用武之地，可以删除了
- 基于上面的说明，我们来讨论一下为什么建议你尽量不要使用长事务。
  - 长事务意味着系统里面会存在很老的事务视图。由于这些事务随时可能访问数据库里面的任何数据，所以这个事务提交之前，数据库里面它可能用到的回滚记录都必须保留，这就会导致大量占用存储空间。在 MySQL 5.5 及以前的版本，回滚日志是跟数据字典一起放在 ibdata 文件里的，即使长事务最终提交，回滚段被清理，文件也不会变小。我见过数据只有 20GB，而回滚段有 200GB 的库。最终只好为了清理回滚段，重建整个库。
  - 除了对回滚段的影响，长事务还占用锁资源，也可能拖垮整个库，这个我们会在后面讲锁的时候展开。
- 如前面所述，长事务有这些潜在风险，我当然是建议你尽量避免。其实很多时候业务开发同学并不是有意使用长事务，通常是由于误用所致。MySQL 的事务启动方式有以下几种：
  - 1. 显式启动事务语句， begin 或 start transaction。配套的提交语句是 commit，回滚语句是 rollback。
  - 2. set autocommit=0，这个命令会将这个线程的自动提交关掉。意味着如果你只执行一个 select 语句，这个事务就启动了，而且并不会自动提交。这个事务持续存在直到你主动执行 commit 或 rollback 语句，或者断开连接。
    - 有些客户端连接框架会默认连接成功后先执行一个 set autocommit=0 的命令。这就导致接下来的查询都在事务中，如果是长连接，就导致了意外的长事务。因此，我会建议你总是使用 set autocommit=1, 通过显式语句的方式来启动事务。
      - autocommit=1;表示MySQL自动开启和提交事务。比如执行一个update语句，语句只完成后就事务自动提交了。不需要显示的使用begin、commit来开启和提交事务。所以，当我们需要对某些操作使用事务的时候，手动的用begin、commit来开启和提交事务。阿里的数据库 show variables like 'autocommit';
      - 有些客户端连接框架会默认连接成功后先执行一个 set autocommit=0 的命令。这就导致接下来的查询都在事务中，如果是长连接，就导致了意外的长事务。因此，我会建议你总是使用 set autocommit=1, 通过显式语句的方式来启动事务。
任何隔离级别，数据操作都有两种：快照读（普通select）和当前读（select for update, update 等）。
- 快照读采用MVCC
- 当前读采用锁。不同隔离级别下同个手段的实现也各有差异，比如同样当前读采用锁，读已提交隔离级别下，写加排它锁，事务结束释放，读加共享锁，读完立刻释放，这样解决了脏读问题，但存在不可重复读问题；而在可重复读隔离级别下，写加排它锁，事务结束释放，读加共享锁，事务结束释放，这样解决了不可重复读问题，但存在幻读问题。
  - for update, lock in share mode，update，delete，insert都是当前读的规则，就是读取最新的已经提交的数据。 update先查询再修改，这里的查询就是使用的当前读。 delete要先查询再删除，这里的查询也是要当前读。插入的时候，要判断主键是否已经存在、是否违反唯一约束，此时查看主键是否存在的查询也是当前读。

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

- - 事务ID：InnoDB 里面每个事务有一个唯一的事务 ID，叫作 transaction id。它是在事务开始的时候向 InnoDB 的事务系统申请的，是按申请顺序严格递增的。
  - 每行数据也都是有多个版本的，每次事务更新该行数据的时候，都会生成一个新的数据版本，
    - 新的数据版本包含（以如下V4为例）：图中虚线框里是同一行数据的 4 个版本，当前最新版本是 V4，k 的值是 22，它是被 transaction id 为 25 的事务更新的，因此它的 row trx_id 也是 25。
      - 本次数据的值
      - 事务id
      - 还有一个引用(指向上一个数据版本) -- redolog
    - 语句更新会生成 undo log（回滚日志）吗？那么，undo log 在哪呢？undo log可以看成是一个回滚日志, 记录redo log的反操作.
      - 实际上，图 2 中的三个虚线箭头，就是 undo log；
      - 而 V1、V2、V3 并不是物理上真实存在的，而是每次需要的时候根据当前版本和 undo log 计算出来的。比如，需要 V2 的时候，就是通过 V4 依次执行 U3、U2 算出来。
      - 如图：就是一个记录被多个事务连续更新后的状态）
  - 事务的启动方式：
  - 如前面所述，长事务有这些潜在风险，我当然是建议你尽量避免。其实很多时候业务开发同学并不是有意使用长事务，通常是由于误用所致。MySQL 的事务启动方式有以下几种：
    - 显式启动事务语句：
      - begin 或 start transaction。配套的提交语句是 commit，回滚语句是 rollback。（不管autocommit 是1还是0 。只有当commit数据才会生效，ROLLBACK后就会回滚）
        
        begin/start transaction 命令并不是一个事务的起点，在执行到它们之后的第一个操作 InnoDB 表的语句，事务才真正启动
        
        一致性视图：是在执行第一个快照读语句时创建的　　
      - start transaction with consistent snapshot; 配套的提交语句是 commit，回滚语句是 rollback。（不管autocommit 是1还是0 。只有当commit数据才会生效，ROLLBACK后就会回滚）
        
        马上启动事务
        
        一致性视图：是在执行 start transaction with consistent snapshot 时创建的
        
        这里需要说明一下，“start transaction with consistent snapshot; ”的意思是从这个语句开始，创建一个持续整个事务的一致性快照。所以，在读提交隔离级别下，这个用法就没意义了，等效于普通的 start transaction。
      - 自动启动事务：set autocommit=0，这个命令会将这个线程的自动提交关掉。
        
        意味着如果你只执行一个 select 语句，这个事务就启动了，而且并不会自动提交。这个事务持续存在直到你主动执行 commit 或 rollback 语句，或者断开连接。
        
        有些客户端连接框架会默认连接成功后先执行一个 set autocommit=0 的命令。这就导致接下来的查询都在事务中，如果是长连接，就导致了意外的长事务。
    - 隐式启动事务语句：set autocommit=1（mysql默认为1，可以设置会话级别（session级别）为0）通过显式语句的方式来启动事务。
      - autocommit=1;表示MySQL自动开启和提交事务。比如执行一个update语句，语句只完成后就自动提交了。不需要显示的使用begin、commit来开启和提交事务。
      - 所以，当我们需要对某些操作使用事务的时候，手动的用begin、commit来开启和提交事务。　　
  - “快照”在MVCC里是如何工作的
    - 在 MySQL 里，有两个“视图”的概念：
      - 一个是 view。它是一个用查询语句定义的虚拟表，在调用的时候执行查询语句并生成结果。创建视图的语法是 create view … ，而它的查询方法与表一样。
      - 另一个是 InnoDB 在实现 MVCC 时用到的一致性读视图，即 consistent read view，用于支持 RC（Read Committed，读提交）和 RR（Repeatable Read，可重复读）隔离级别的实现。它没有物理结构，作用是事务执行期间用来定义“我能看到什么数据”。（逻辑结构是：就是通过高低水位，数据版本号，undo日记来进行判断数据可见不可见，达到MVCC目的）
    - 一致性读视图：InnoDB 为每个事务构造了一个数组（一致性视图），用来保存这个事务启动瞬间，当前正在“活跃”的所有事务 ID。“活跃”指的就是，启动了但还没提交。
      - 高低水位：
        
        低水位：该数组里面事务ID的最小值（即该事务启动瞬间，当前活跃事务ID的最小值）
        
        高水位：即该事务启动瞬间，当前系统里面已经创建过的事务 ID 的最大值加 1 记为高水位。
  - 以“可重复读”隔离级别为例：
    - 通俗理解：
      - 一个事务只需要在启动的时候声明说，“以我启动的时刻为准，如果一个数据版本是在我启动之前生成的，就认；
      - 如果是我启动以后才生成的，我就不认，我必须要找到它的上一个版本”。当然，如果“上一个版本”也不可见，那就得继续往前找。
      - 还有，如果是这个事务自己更新的数据，它自己还是要认的。
    - 通俗理解2：一个数据版本，对于一个事务视图来说，除了自己的更新总是可见以外，有三种情况：
      - 版本未提交，不可见；
      - 版本已提交，但是是在视图创建后提交的，不可见；
      - 版本已提交，而且是在视图创建前提交的，可见。
    - 一致性读视图数组：[96, 98, 99, 101]；对于当前事务的启动瞬间来说，一个数据版本的 row trx_id，有以下几种可能
      - 如果落在绿色部分，表示这个版本是已提交的事务或者是当前事务自己生成的，这个数据是可见的；
      - 如果落在红色部分，表示这个版本是由将来启动的事务生成的，是肯定不可见的；
      - 如果落在黄色部分，那就包括两种情况
        
        a. 若 row trx_id 在数组中，表示这个版本是由还没提交的事务生成的，不可见；
        
        b. 若 row trx_id 不在数组中，表示这个版本是已经提交了的事务生成的，可见。
  - 一致性读视图（隔离级别涉及可重复读）、读提交，行锁 区别，如下例子解读：
索引的常见模型
- 常见模型：哈希表、有序数组、搜索树（二叉树、平衡二叉树、红黑树、B树、B+树），其它有跳表、LSM 树等
- 哈希表
  - 图中四个 ID_card_n 的值并不是递增的（非有序）
    - 这样做的好处是增加新的 User 时速度会很快，只需要往后追加
    - 但缺点是，因为不是有序的，所以哈希索引做区间查询的速度是很慢的。
      - 例子：如果你现在要找身份证号在[ID_card_X, ID_card_Y]这个区间的所有用户，就必须全部扫描一遍了。
      - 哈希表这种结构适用于只有等值查询的场景，比如 Memcached 及其他一些 NoSQL 引擎。
        
        等值查询就是用等号来匹配查询结果，分为单条件查询、多条件查询，
        
        与等值查询对应的是模糊查询、范围查询
  - 你要查 ID_card_n2 对应的名字是什么，处理步骤就是：
    - 首先，将 ID_card_n2 通过哈希函数算出 N；
    - 然后，按顺序遍历，找到 User2。
  - 　　
- 有序数组
  - 等值查询和范围查询：性能就都非常优秀。这里我们假设身份证号没有重复，这个数组就是按照身份证号递增的顺序保存的。
    - 这时候如果你要查 ID_card_n2 对应的名字，用二分法就可以快速得到，这个时间复杂度是 O(log(N))。
    - 同时很显然，这个索引结构支持范围查询。你要查身份证号在[ID_card_X, ID_card_Y]区间的 User，可以先用二分法找到 ID_card_X（如果不存在 ID_card_X，就找到大于 ID_card_X 的第一个 User），然后向右遍历，直到查到第一个大于 ID_card_Y 的身份证号，退出循环。
  - 更新：在需要更新数据的时候就麻烦了，你往中间插入一个记录就必须得挪动后面所有的记录，成本太高。
  - 所以，有序数组索引只适用于静态存储引擎，比如你要保存的是 2017 年某个城市的所有人口信息，这类不会再修改的数据。
- 搜索树
  - 二叉树、平衡二叉树、红黑树、B树、B+树不断演进和优化
  - 平衡二叉树：你可以想象一下一棵 100 万节点的平衡二叉树，树高 20。一次查询可能需要访问 20 个数据块。在机械硬盘时代，从磁盘随机读一个数据块需要 10 ms 左右的寻址时间。也就是说，对于一个 100 万行的表，如果使用二叉树来存储，单独访问一个行可能需要 20 个 10 ms 的时间，这个查询可真够慢的。
    - 定义：它是一棵空树或它的左右两个子树的高度差的绝对值不超过1，并且左右两个子树都是一棵平衡二叉树。
    - 问题：为什么树高20的话，就要遍历20个块？
    - 解答：因为是二叉树结构，每次指针查找很大概率是触发随机磁盘读（比如很难刚好碰上一个节点和他的左右儿子刚好相邻）；老师的话改成“一颗树高20的平衡二叉树遍历一次最多要访问20次数据块就好理解了”
    - 为了让一个查询尽量少地读磁盘，就必须让查询过程访问尽量少的数据块。那么，我们就不应该使用二叉树，而是要使用“N 叉”树。这里，“N 叉”树中的“N”取决于数据块的大小。
  - N叉树：（N是多少取决于数据块大小）
    - 以 InnoDB 的一个整数字段索引为例，这个 N 差不多是 1200（1200叉树）。这棵树高是 4 的时候，就可以存 1200 的 3 次方个值，这已经 17 亿了。
      - 考虑到树根的数据块总是在内存中的，一个 10 亿行的表上一个整数字段的索引，查找一个值最多只需要访问 3 次磁盘。
      - 其实，树的第二层也有很大概率在内存中，那么访问磁盘的平均次数就更少了。
      - N 叉树由于在读写上的性能优点，以及适配磁盘的访问模式，已经被广泛应用在数据库引擎中了。
    - 1200怎么来的？
      - MySql默认一个节点的长度为16K，一个整数（bigint）字段索引的长度为 8B,另外每个索引还跟着6B的指向其子树的指针；所以16K/14B ≈ 1170
        以每一层只进行一次磁盘IO，也就是在『一页』的大小16K内进行计算，一个节点14Byte，16KB * 1024 / 14Byte = 1170没毛病（约等于1200）
  - InnoDB 的索引模型：在 InnoDB 中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。又因为前面我们提到的，InnoDB 使用了 B+ 树索引模型，所以数据都是存储在 B+ 树中的。
    - 1.在 InnoDB 中，每一张表其实就是多个 B+ 树，即一个主键索引树和多个非主键索引树。
    - 例子：我们有一个主键列为 ID 的表，表中有字段 k，并且在 k 上有索引。
      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 里，主键索引也被称为聚簇索引（clustered index）。
        
        非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引（secondary index）。
      - 基于主键索引和普通索引的查询有什么区别？
        
        如果语句是 select * from T where ID=500，即主键查询方式，则只需要搜索 ID 这棵 B+ 树；
        
        如果语句是 select * from T where k=5，即普通索引查询方式，则需要先搜索 k 索引树，得到 ID 的值为 500，再到 ID 索引树搜索一次。这个过程称为回表。也就是说，基于非主键索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询。
        
        你知道的，InnoDB 的数据是按数据页为单位来读写的。也就是说，当需要读一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页（16KB）为单位，将其整体读入内存。在 InnoDB 中，每个数据页的大小默认是 16KB。
        
        因为引擎是按页读写的，所以说，当找到 k=5 的记录的时候，它所在的数据页就都在内存里了。那么，对于普通索引来说，要多做的那一次“查找和判断下一条记录”的操作，就只需要一次指针寻找和一次计算。
        
        当然，如果 k=5 这个记录刚好是这个数据页的最后一个记录，那么要取下一个记录，必须读取下一个数据页，这个操作会稍微复杂一些。
        
        但是，我们之前计算过，对于整型字段，一个数据页可以放近千个 key，因此出现这种情况的概率会很低。所以，我们计算平均性能差异时，仍可以认为这个操作成本对于现在的 CPU 来说可以忽略不计。
        
        物理读 - 当执行一个查询时，为了返回满足查询的结果集，系统必须访问表中的数据。这些数据以 16 KB 大小的数据页（Page，Oracle DB 中称之为 Block）形式存储在磁盘上。当查询需要访问该数据时，如果该数据不在 InnoDB Buffer Pool 中，则系统会将该页从磁盘上的数据文件中加载到 InnoDB Buffer Pool 中，每一个 16 KB 页的加载到内存动作被称之为一个物理读（物理 IO）。 https://developer.aliyun.com/article/165980
        
        逻辑读 - 档执行一个查询时，为了返回满足查询的结果集，系统必须访问表中的数据。这些数据以 16 KB 大小的数据页（Page，Oracle DB 中称之为 Block）形式存储在磁盘上。当查询需要访问该数据时，如果该数据在 InnoDB Buffer Pool 中，则对每一个 16 KB 页的内存访问称之为一个逻辑读（逻辑 IO）。
        
        例子：某SQL耗时很多，但是执行次数少，且扫描行数少、返回行数页少（即走了索引），但是该数据库实例CPU打满前都是这个SQL耗时变多
        
        分析发现：有个字段字典/特征是longtext类型的，有2M大小，而一页大小只有16K，所以每行读取时都有大量的逻辑读产生；
    - 索引维护
    - 经过大量增删改的表，都是可能是存在空洞的
      - 数据删除流程
        
        delete删掉某个记录：假设，我们要删掉 R4 这个记录，InnoDB 引擎只会把 R4 这个记录标记为删除（可以复用）。如果之后要再插入一个 ID 在 300 和 600 之间的记录时，可能会复用这个位置。但是，磁盘文件的大小并不会缩小。
        
        记录的复用，只限于符合范围条件的数据。比如上面的这个例子，R4 这条记录被删除后，如果插入一个 ID 是 400 的行，可以直接复用这个空间。但如果插入的是一个 ID 是 800 的行，就不能复用这个位置了。
        
        delete删掉整个page：你已经知道了 InnoDB 的数据是按页存储的，那么如果我们删掉了一个数据页上的所有记录，会怎么样？整个数据页就可以被复用了。
        
        而当整个页从 B+ 树里面摘掉以后，可以复用到任何位置。以图 1 为例，如果将数据页 page A 上的所有记录删除以后，page A 会被标记为可复用。这时候如果要插入一条 ID=50 的记录需要使用新页的时候，page A 是可以被复用的。
        
        如果相邻的两个数据页利用率都很小，系统就会把这两个页上的数据合到其中一个页上，另外一个数据页就被标记为可复用。
        
        delete整张表：进一步地，如果我们用 delete 命令把整个表的数据删除呢？结果就是，所有的数据页都会被标记为可复用。但是磁盘上，文件不会变小（delete不能回收表空间，可以复用，像是空洞）。
      - 如果数据是按照索引递增顺序插入的，那么索引是紧凑的。但如果数据是随机插入的，就可能造成索引的数据页分裂。f 假设图 1 中 page A 已经满了，这时我要再插入一行数据，会怎样呢？　
        
        可以看到，由于 page A 满了，再插入一个 ID 是 550 的数据时，就不得不再申请一个新的页面 page B 来保存数据了。页分裂完成后，page A 的末尾就留下了空洞（注意：实际上，可能不止 1 个记录的位置是空洞）。
      - 更新索引上的值：可以理解为删除一个旧的值，再插入一个新值。不难理解，这也是会造成空洞的。
      - 数据空洞的解决：重建表
        
        原理：你可以新建一个与表 A 结构相同的表 B，然后按照主键 ID 递增的顺序，把数据一行一行地从表 A 里读出来再插入到表 B 中。
        由于表 B 是新建的表，所以表 A 主键索引上的空洞，在表 B 中就都不存在了。显然地，表 B 的主键索引更紧凑，数据页的利用率也更高。如果我们把表 B 作为临时表，数据从表 A 导入表 B 的操作完成后，用表 B 替换 A，从效果上看，就起到了收缩表 A 空间的作用。
        
        alter table t engine = InnoDB（也就是 recreate）；optimize table t 等于 recreate+analyze。
    - 发
      - B+ 树为了维护索引有序性，在插入新值的时候需要做必要的维护
      - 以下面这个图为例，如果插入新的行 ID 值为 700，则只需要在 R5 的记录后面插入一个新记录。如果新插入的 ID 值为 400，就相对麻烦了，需要逻辑上挪动后面的数据，空出位置。而更糟的情况是，如果 R5 所在的数据页已经满了，根据 B+ 树的算法，这时候需要申请一个新的数据页，然后挪动部分数据过去。这个过程称为页分裂（逆向过程：合并）
    - 既然InnoDB对数据的存储必须依赖于主键，那么对于没有创建主键的表，InnoDB对聚簇索引如何处理？InnoDB对聚簇索引处理如下：
      - - 如果定义了主键，那么InnoDB会使用主键作为聚簇索引
      - - 如果没有定义主键，那么会使用第一非空的唯一索引（NOT NULL and UNIQUE INDEX）作为聚簇索引
      - - 如果既没有主键也找不到合适的非空索引，那么InnoDB会自动生成一个不可见的名为ROW_ID的列名为GEN_CLUST_INDEX的聚簇索引，该列是一个6字节的自增数值，随着插入而自增
    - 你可能在一些建表规范里面见到过类似的描述，要求建表语句里一定要有自增主键。当然事无绝对，我们来分析一下哪些场景下应该使用自增主键，而哪些场景下不应该。
      - 自增主键：
        
        确保有序：插入性能更佳
        
        自增主键是指自增列上定义的主键，在建表语句中一般是这么定义的： NOT NULL PRIMARY KEY AUTO_INCREMENT。插入新记录的时候可以不指定 ID 的值，系统会获取当前 ID 最大值加 1 作为下一条记录的 ID 值。也就是说，自增主键的插入数据模式，正符合了我们前面提到的递增插入的场景。每次插入一条新记录，都是追加操作，都不涉及到挪动其他记录，也不会触发叶子节点的分裂。
        
        有业务逻辑的字段做主键：则往往不容易保证有序插入，这样写数据成本相对较高。
        
        节约存储空间：主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小
        
        假设你的表中确实有一个唯一字段，比如字符串类型的身份证号，那应该用身份证号做主键，还是用自增字段做主键呢？
        
        由于每个非主键索引的叶子节点上都是主键的值。如果用身份证号做主键，那么每个二级索引的叶子节点占用约 20 个字节，而如果用整型做主键，则只要 4 个字节，如果是长整型（bigint）则是 8 个字节。
      - 有没有什么场景适合用业务字段直接做主键的呢？
        
        比如，有些业务的场景需求是这样的：只有一个索引；该索引必须是唯一索引。
        
        你一定看出来了，这就是典型的 KV 场景。由于没有其他索引，所以也就不用考虑其他索引的叶子节点大小的问题。
        
        这时候我们就要优先考虑上一段提到的“尽量使用主键查询”原则，直接将这个索引设置为主键，可以避免每次查询需要搜索两棵树。
    - 覆盖索引：在下面这个表 T 中，如果我执行 select * from T where k between 3 and 5，需要执行几次树的搜索操作，会扫描多少行？
      - 现在，我们一起来看看这条 SQL 查询语句的执行流程：
      - 在这个过程中，回到主键索引树搜索的过程，我们称为回表。可以看到，这个查询过程读了 k 索引树的 3 条记录（步骤 1、3 和 5），回表了两次（步骤 2 和 4）。
      - 在这个例子中，由于查询结果所需要的数据只在主键索引上有，所以不得不回表。那么，有没有可能经过索引优化，避免回表过程呢？
      - 覆盖索引：如果执行的语句是 select ID from T where k between 3 and 5，这时只需要查 ID 的值，而 ID 的值已经在 k 索引树上了，因此可以直接提供查询结果，不需要回表。也就是说，在这个查询里面，索引 k 已经“覆盖了”我们的查询需求，我们称为覆盖索引。
    - mysql> create table T ( ID int primary key, k int NOT NULL DEFAULT 0, s varchar(16) NOT NULL DEFAULT '', index k(k)) engine=InnoDB; insert into T values(100,1, 'aa'),(200,2,'bb'),(300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg');
    - 发
    - 最左前缀原则---联合索引：书上例子（对于数据结构较好理解），我们用（name，age）这个联合索引来分析；可以看到，索引项是按照索引定义里面出现的字段顺序排序的（先按照第一个字段name排序：李四、王五、张六、张三；第一个字段name相同的情况下，再按照第二个字段排序：张三的10、10、20）；若根据name查询age很频繁，这个索引就很好，可以用到覆盖索引，避免回表；
      - 当你的逻辑需求是查到所有名字是“张三”的人时，可以快速定位到 ID4，然后向后遍历得到所有需要的结果。
      - 如果你要查的是所有名字第一个字是“张”的人，你的 SQL 语句的条件是”where name like ‘张 %’”。这时，你也能够用上这个索引，查找到第一个符合条件的记录是 ID3，然后向后遍历，直到不满足条件为止。
        
        若联合索引是(name, dept, age)，也可以用 name like'张%' and dept like '计算机%'
        
        若是 name like '%张' -- 不满足最左前缀，不会走联合索引；
      - 在建立联合索引的时候，如何安排索引内的字段顺序？
        
        这里我们的评估标准是，索引的复用能力。因为可以支持最左前缀，所以当已经有了 (a,b) 这个联合索引后，一般就不需要单独在 a 上建立索引了。因此，第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。
        
        那么，如果既有联合查询，又有基于 a、b 各自的查询呢？查询条件里面只有 b 的语句，是无法使用 (a,b) 这个联合索引的，这时候你不得不维护另外一个索引，也就是说你需要同时维护 (a,b)、(b) 这两个索引。
        
        这时候，我们要考虑的原则就是空间了。比如上面这个市民表的情况，name 字段是比 age 字段大的，那我就建议你创建一个（name,age) 的联合索引和一个 (age) 的单字段索引。
    - 联合索引在B+树上的存储结构及数据查找方式（查看）
      - 构造例子
      - 1 CREATE TABLE `t1` ( 2 `a` int(11) NOT NULL AUTO_INCREMENT, 3 `b` int(11) NULL DEFAULT NULL, 4 `c` int(11) NULL DEFAULT NULL, 5 `d` int(11) NULL DEFAULT NULL, 6 `e` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, 7 PRIMARY KEY (`a`) USING BTREE, 8 INDEX `index_bcd`(`b`, `c`, `d`) USING BTREE 9 ) ENGINE = InnoDB AUTO_INCREMENT = 8 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; 10 11 -- ---------------------------- 12 -- Records of t1 13 -- ---------------------------- 14 INSERT INTO `t1` VALUES (1, 13, 12, 4, 'dll'); 15 INSERT INTO `t1` VALUES (2, 1, 5, 4, 'doc'); 16 INSERT INTO `t1` VALUES (3, 13, 16, 5, 'img'); 17 INSERT INTO `t1` VALUES (4, 12, 14, 3, 'xml'); 18 INSERT INTO `t1` VALUES (5, 1, 1, 4, 'txt'); 19 INSERT INTO `t1` VALUES (6, 13, 16, 1, 'exe'); 20 INSERT INTO `t1` VALUES (7, 5, 3, 6, 'pdf');
      - T1表的表数据如下：（a是自增列， (`b`, `c`, `d`) 是联合索引）
      - bcd联合索引在B+树上的结构图：联合索引（b，c，d）也会生成一个索引树，同样是B+树的结构，只不过它的data部分存储的是联合索引所在行的主键值（下图叶子节点紫色背景部分）；存储规则如下
        
        存储引擎会首先根据第一个索引列排序，如上图我们可以单看第一个索引列，横着看，如，1 1 5 12 13....他是单调递增的；
        
        如果第一列相等则再根据第二列排序
        
        依次类推就构成了上图的索引树，上图中的b列都等于1时，则根据c排序，此时c列也相等则按d列排序，如：1 1 4 ，1 1 5，c=4在c=5前面，以及13 12 4,13 16 1,13 16 5就可以说明这种情况。
        
        联合合索引的查找方式
        
        比如 select * from T1 where b = 12 and c = 14 and d = 3; 也就是T1表中a列为4的这条记录。
        
        存储引擎首先从根节点（一般常驻内存）开始查找，第一个索引的第一个索引列为1,12大于1，第二个索引的第一个索引列为56,12小于56，于是从这俩索引的中间读到下一个节点的磁盘文件地址，从磁盘上Load这个节点，通常伴随一次磁盘IO，然后在内存里去查找。当Load叶子节点的第二个节点时又是一次磁盘IO，比较第一个元素，b=12,c=14,d=3完全符合，于是找到该索引下的data元素即ID值，再从主键索引树上找到最终数据。
        
        最左前缀匹配原则
        
        我们创建的index_bcd(b,c,d)索引，相当于创建了(b)、（b、c）（b、c、d）三个索引（没有(b、d) 索引），看完下面你就知道为什么相当于创建了三个索引。
        
        我们看，联合索引是首先使用多列索引的第一列构建的索引树，用上面idx_t1_bcd(b,c,d)的例子就是优先使用b列构建，当b列值相等时再以c列排序，若c列的值也相等则以d列排序。我们可以取出索引树的叶子节点看一下
        
        索引的第一列也就是b列可以说是从左到右单调递增的
        但我们看c列和d列并没有这个特性，它们只能在b列值相等的情况下这个小范围内递增，
        如第一叶子节点的第1、2个元素和第二个叶子节点的后三个元素。
        由于联合索引是上述那样的索引构建方式及存储结构，所以联合索引只能从多列索引的第一列开始查找。所以如果你的查找条件不包含b列如（c,d）、(c）、(d)是无法应用缓存的，以及跨列也是无法完全用到索引如(b,d)，只会用到b列索引。
        
        例子：电话本一样，有姓和名以及电话，姓和名就是联合索引。在姓可以以姓的首字母排序，姓的首字母相同的情况下，再以名的首字母排序。如：
        
        我们知道姓和名是很快就能够
        
        从姓的首字母索引定位到姓，然后定位到名，进而找到电话号码，因为所有的姓从上到下按照既定的规则（首字母排序）是有序的，而名是在姓的首字母一定的条件下也是按照名的首字母排序的，
        
        但是整体来看，所有的名放在一起是无序的，所以如果只知道名查找起来就比较慢，因为无法用已排好的结构快速查找。到这里大家是否明白了为啥会有最左前缀匹配原则了吧。
        
        较快查找
        
        姓、名 ——> 电话
        
        姓——> 电话
        
        其它例子
        
        （select * from T1 where b = 12 and c = 14 and d = 3;-- 全值索引匹配三列都用到 select * from T1 where b = 12 and c = 14 and e = 'xml';-- 应用到两列索引 select * from T1 where b = 12 and e = 'xml';-- 应用到一列索引 select * from T1 where b = 12 and c >= 14 and e = 'xml';-- 应用到bc两列列索引及索引条件下推优化 select * from T1 where b = 12 and d = 3;-- 应用到一列索引因为不能跨列使用索引没有c列连不上 select * from T1 where c = 14 and d = 3;-- 无法应用索引，违背最左匹配原则 -- 市民表：建立了联合索引（name, age） select * from tuser where name like '张%' and age=10 and ismale=1; -- 只能用到name索引，用不了(name, age索引）
        
        联合索引，范围查询后，后面的字段不走索引 （查看）
        
        底层B+树叶子节点的键值是通过链表连接的，而组合索引是从左向右按照索引字段的顺序进行查找，只有当前一个索引列是确定值的时候，下一个索引列的值才是递增的状态，才能通过该组合索引的B+树遍历得到，而一旦前一个索引列是范围匹配，则之后的索引列的值不存在递增关系，那么就没办法通过B+树遍历得到。
        
        例如，以下联合索引。A>=2,B=4的查询过程。
        
        a=2时，B是有序的
        
        2<=a<=3 时，b是无序的，就不能继续向下查找了。必须在前一个是定值的情况下，才能继续向下查找。
        
        下推优化：mysql> select * from tuser where name like '张%' and age=10 and ismale=1;
        
        图 3 中，在 (name,age) 索引里面我特意去掉了 age 的值，这个过程 InnoDB 并不会去看 age 的值，只是按顺序把“name 第一个字是’张’”的记录一条条取出来回表。因此，需要回表 4 次。
        
        图 4 跟图 3 的区别是，InnoDB 在 (name,age) 索引内部就判断了 age 是否等于 10，对于不等于 10 的记录，直接判断并跳过。在我们的这个例子中，只需要对 ID4、ID5 这两条记录回表取数据判断，就只需要回表 2 次。　　　　
锁
- 锁的分类：
  - 设计初衷：数据库锁设计的初衷是处理并发问题。作为多用户共享的资源，当出现并发访问的时候，数据库需要合理地控制资源的访问规则。而锁就是用来实现这些访问规则的重要数据结构。
  - 根据加锁的范围，MySQL 里面的锁大致可以分成全局锁、表级锁和行锁三类
- 全局锁
  - 概念：全局锁就是对整个数据库实例加锁。
  - 使用场景：全局锁的典型使用场景是，做全库逻辑备份（在备份过程中整个库完全处于只读状态）。也就是把整库每个表都 select 出来存成文本。
  - 加锁方法：
    - 传统全局读锁方法：
      - 命令是 Flush tables with read lock (FTWRL)。当你需要让整个库处于只读状态的时候，可以使用这个命令
      - 之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。
    - 若引擎支持可重复读（底层是MVCC 即一致性读视图）—— 事务引擎InnoDB：
    - 既然要全库只读，为什么不使用 set global readonly=true 的方式呢？确实 readonly 方式也可以让全库进入只读状态，但我还是会建议你用 FTWRL 方式，主要有两个原因：
      - 一是，在有些系统中，readonly 的值会被用来做其他逻辑，比如用来判断一个库是主库还是备库。因此，修改 global 变量的方式影响面更大，我不建议你使用。
      - 二是，在异常处理机制上有差异。
        
        如果执行 FTWRL 命令之后由于客户端发生异常断开，那么 MySQL 会自动释放这个全局锁，整个库回到可以正常更新的状态。
        
        而将整个库设置为 readonly 之后，如果客户端发生异常，则数据库就会一直保持 readonly 状态，这样会导致整个库长时间处于不可写状态，风险较高。
- 表级锁
  - 类型：MySQL中的表级锁有两种：
    - 1.表锁： lock tables ... read/write
    - 2.元数据锁(MDL metadata lock)：不需要显式使用，在访问一个表的时候会被自动加上。MDL 的作用是，保证读写的正确性。
  - 1.表锁：lock tables ... read/write
    - 例子：如果在某个线程 A 中执行 lock tables t1 read, t2 write; 这个语句，
      - 本线程与其它线程
        
        则其他线程写 t1、读写 t2 的语句都会被阻塞。
        
        同时，线程 A 在执行 unlock tables 之前，也只能执行读 t1、读写 t2 的操作。连写 t1 都不允许，自然也不能访问其他表。
      - 读锁与写锁
        
        读锁（线程A lock tables t1 read）共享锁：本线程只能读t1、其它线程也可以读t1；都不能写t1
        
        写锁（线程A lock table t2 write）排他锁：本线程可以读写t2，其它线程不能读也不能写
    - 在还没有出现更细粒度的锁的时候，表锁是最常用的处理并发的方式。而对于 InnoDB 这种支持行锁的引擎，一般不使用 lock tables 命令来控制并发，毕竟锁住整个表的影响面还是太大。
  - - 给小表增加字段也会导致连接池（线程池）打爆；（也知道了，事务中的 MDL 锁，在语句执行开始时申请，但是语句结束后并不会马上释放，而会等到整个事务提交后再释放。）
    - 如何安全地给小表加字段？
      - 首先我们要解决长事务，事务不提交，就会一直占着 MDL 锁。在 MySQL 的 information_schema 库的 innodb_trx 表中，你可以查到当前执行中的事务。如果你要做 DDL 变更的表刚好有长事务在执行，要考虑先暂停 DDL，或者 kill 掉这个长事务。
        
        但考虑一下这个场景。如果你要变更的表是一个热点表，虽然数据量不大，但是上面的请求很频繁，而你不得不加个字段，你该怎么做呢？这时候 kill 可能未必管用，因为新的请求马上就来了
    - 比较理想的机制是，在 alter table 语句里面设定等待时间，如果在这个指定的等待时间里面能够拿到 MDL 写锁最好，拿不到也不要阻塞后面的业务语句，先放弃。之后开发人员或者 DBA 再通过重试命令重复这个过程。
    - ALTER TABLE tbl_name NOWAIT add column ... ALTER TABLE tbl_name WAIT N add column ...
- 行锁
  - 概念：行锁就是针对数据表中行记录的锁（是引擎层实现的，不是所有引擎都支持）。这很好理解，比如事务 A 更新了一行，而这时候事务 B 也要更新同一行，则必须等事务 A 的操作完成后才能进行更新
  - 实现原理：行锁的实现是通过给索引上的索引项添加锁实现的，故只有当执行的脚本走索引时，innodb才会使用行锁，否则innodb只能使用元数据锁MDL，两种锁均无需显示添加。故此时id必须是主键（聚集索引）或创建了辅助索引。
  - 如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放（减少一次操作中锁住共享数据的时间，进而提升效率）。
    - 反例：
    - 正例：
      - 假设你负责实现一个电影票在线交易业务，顾客 A 要在影院 B 购买电影票。我们简化一点，这个业务需要涉及到以下操作。也就是说，要完成这个交易，我们需要 update 两条记录，并 insert 一条记录。当然，为了保证交易的原子性，我们要把这三个操作放在一个事务中。那么，你会怎样安排这三个语句在事务中的顺序呢？（解决：如果你把语句 2 安排在最后，比如按照 3、1、2 这样的顺序，那么影院账户余额这一行的锁时）
        
        1.从顾客 A 账户余额中扣除电影票价；
        
        2.给影院 B 的账户余额增加这张电影票价；（容易锁冲突）
        
        3.记录一条交易日志。
      - 如果这个影院做活动，可以低价预售一年内所有的电影票，而且这个活动只做一天。于是在活动时间开始的时候，你的 MySQL 就挂了。你登上服务器一看，CPU 消耗接近 100%，但整个数据库每秒就执行不到 100 个事务。这是什么原因呢？这里，我就要说到死锁和死锁检测了。
  - 死锁和死锁检测
    - 概念：当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态，称为死锁。这里我用数据库中的行锁举个例子。
    - 例子：
    - 解决策略：当出现死锁以后，有两种策略：
      - 一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置。（默认值是 50s，对于在线服务来说，这个等待时间往往是无法接受的。但是，我们又不可能直接把这个时间设置成一个很小的值，比如 1s。这样当出现死锁的时候，确实很快就可以解开，但如果不是死锁，而是简单的锁等待呢？所以，超时时间设置太短的话，会出现很多误伤。）
      - 另一种策略是主动死锁检测，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on（默认值就是on），表示开启这个逻辑。
        
        主动死锁检测引发的CPU大量占用问题
        
        整体死锁检测的耗费代价是 O(n2)级别的。并发更新同一行的1000个线程，整体耗费的死锁检测操作为1000*1000=100万。但是这期间要消耗大量的 CPU 资源。因此，你就会看到 CPU 利用率很高，但是每秒却执行不了几个事务。
        
        怎么解决由这种热点行更新导致的性能问题呢？
        
        1，头痛医头：就是如果你能确保这个业务一定不会出现死锁，可以临时把死锁检测关掉。
        
        2，控制并发度
        
        根据上面的分析，你会发现如果并发能够控制住，比如同一行同时最多只有 10 个线程在更新，那么死锁检测的成本很低，就不会出现这个问题。
        
        一个直接的想法就是，在客户端做并发控制。但是，你会很快发现这个方法不太可行，因为客户端很多。我见过一个应用，有 600 个客户端，这样即使每个客户端控制到只有 5 个并发线程，汇总到数据库服务端以后，峰值并发数也可能要达到 3000。
        
        因此，这个并发控制要做在数据库服务端。如果你有中间件，可以考虑在中间件实现；
        
        你可以考虑通过将一行改成逻辑上的多行来减少锁冲突。还是以影院账户为例，可以考虑放在多条记录上，比如 10 个记录，影院的账户总额等于这 10 个记录的值的总和。
        
        这样每次要给影院账户加金额的时候，随机选其中一条记录来加。这样每次冲突概率变成原来的 1/10，可以减少锁等待个数，也就减少了死锁检测的 CPU 消耗。
        
        这个方案看上去是无损的，但其实这类方案需要根据业务逻辑做详细设计。如果账户余额可能会减少，比如退票逻辑，那么这时候就需要考虑当一部分行记录变成 0 的时候，代码要有特殊处理。
        
        发
  - 行锁：好文章 https://www.cnblogs.com/jian0110/p/12721924.html

posted on 2022-10-21 10:18 gogoy 阅读(75) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

gogoy

从这里开始，跟我一起搞懂 MySQL!

导航

公告