MySQL面试题，面不上算我输！

点击上方"数据与人", 右上角选择“设为星标”分享干货，共同成长！ 
 话不多说，直接上干货。

SQL 语句执行流程

MySQL 大体上可分为 Server 层和存储引擎层两部分。整体架构图

MySQL的架构不同于其他数据库，它的插件式的存储引擎架构可以在多种不同场景中应用并发挥良好作用。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。各层介绍：连接层最上层是客户端，包含本地sock通信和大多数基于客户端/服务端工具实现的类似于tcp/ip的通信。服务层
引擎层存储引擎负责MySQL中数据的存储和提取，服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取。show engines:查看所有的数据库引擎

show variables like '%engine%' 查看默认的数据库引擎
MyISAM和InnoDB对比
存储层数据存储层，主要是将数据存储在运行于裸设备的文件系统之上，并完成与存储引擎的交互。流程图：

SQL执行顺序

BinLog、RedoLog、UndoLog

binlog 用于记录数据库执行的写入性操作(不包括查询)信息，以二进制的形式保存在磁盘中。binlog 是 mysql的逻辑日志，并且由 Server 层进行记录，使用任何存储引擎的 mysql 数据库都会记录 binlog 日志。

• 逻辑日志：可以理解为记录的就是sql语句.

• 物理日志：mysql 数据最终是保存在数据页中的，物理日志记录的就是数据页变更 。

binlog 是通过追加的方式进行写入的，可以通过max_binlog_size 参数设置每个 binlog文件的大小，当文件大小达到给定值之后，会生成新的文件来保存日志。

binlog使用场景

在实际应用中， binlog 的主要使用场景有两个，分别是 主从复制 和 数据恢复 。

1. 主从复制 ：在 Master 端开启 binlog ，然后将 binlog发送到各个 Slave 端， Slave 端重放 binlog 从而达到主从数据一致。

2. 数据恢复 ：通过使用 mysqlbinlog 工具来恢复数据。

binlog刷盘时机

对于 InnoDB 存储引擎而言，只有在事务提交时才会记录biglog ，此时记录还在内存中，那么 biglog是什么时候刷到磁盘中的呢？mysql 通过 sync_binlog 参数控制 biglog 的刷盘时机，取值范围是 0-N：

• 0：不去强制要求，由系统自行判断何时写入磁盘；

• 1：每次 commit 的时候都要将 binlog 写入磁盘；

• N：每N个事务，才会将 binlog 写入磁盘。

从上面可以看出， sync_binlog 最安全的是设置是 1 ，这也是MySQL 5.7.7之后版本的默认值。但是设置一个大一些的值可以提升数据库性能，因此实际情况下也可以将值适当调大，牺牲一定的一致性来获取更好的性能。

binlog日志格式

binlog 日志有三种格式，分别为 STATMENT 、 ROW 和 MIXED。

在 MySQL 5.7.7 之前，默认的格式是 STATEMENT ， MySQL 5.7.7 之后，默认值是 ROW。日志格式通过 binlog-format 指定。

• STATMENT：基于SQL 语句的复制( statement-based replication, SBR )，每一条会修改数据的sql语句会记录到binlog 中  。

优点：不需要记录每一行的变化，减少了 binlog 日志量，节约了  IO  , 从而提高了性能；

缺点：在某些情况下会导致主从数据不一致，比如执行sysdate() 、  slepp()  等 。

• ROW：基于行的复制(row-based replication, RBR )，不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了 。

优点：不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题 ；

缺点：会产生大量的日志，尤其是` alter table ` 的时候会让日志暴涨

• MIXED：基于STATMENT 和 ROW 两种模式的混合复制(mixed-based replication, MBR )，一般的复制使用STATEMENT 模式保存 binlog ，对于 STATEMENT 模式无法复制的操作使用 ROW 模式保存 binlog

主从同步流程：

1. 主节点必须启用二进制日志，记录任何修改了数据库数据的事件。

2. 从节点开启一个线程（I/O Thread)把自己扮演成 mysql 的客户端，通过 mysql 协议，请求主节点的二进制日志文件中的事件 。

3. 主节点启动一个线程（dump Thread），检查自己二进制日志中的事件，跟对方请求的位置对比，如果不带请求位置参数，则主节点就会从第一个日志文件中的第一个事件一个一个发送给从节点。

4. 从节点接收到主节点发送过来的数据把它放置到中继日志（Relay log）文件中。并记录该次请求到主节点的具体哪一个二进制日志文件内部的哪一个位置（主节点中的二进制文件会有多个）。

5. 从节点启动另外一个线程（sql Thread ），把 Relay log 中的事件读取出来，并在本地再执行一次。

mysql 默认的复制方式是异步的，并且复制的时候是有并行复制能力的。主库把日志发送给从库后不管了，这样会产生一个问题就是：假设主库挂了，从库处理失败了，这时候从库升为主库后，日志就丢失了。由此产生两个概念：全同步复制和半同步复制。全同步复制主库写入binlog后强制同步日志到从库，所有的从库都执行完成后才返回给客户端，但是很显然这个方式的话性能会受到严重影响。半同步复制半同步复制的逻辑是这样，从库写入日志成功后返回ACK确认给主库，主库收到至少一个从库的确认就认为写操作完成。还可以延伸到由于主从配置不一样、主库大事务、从库压力过大、网络震荡等造成主备延迟，如何避免这个问题？主备切换的时候用可靠性优先原则还是可用性优先原则？如何判断主库Crash了？互为主备情况下如何避免主备循环复制？被删库跑路了如何正确恢复？

2.2 RedoLog

为什么需要redo log
我们都知道，事务的四大特性里面有一个是 持久性 ，具体来说就是只要事务提交成功，那么对数据库做的修改就被永久保存下来了，不可能因为任何原因再回到原来的状态 。
那么 mysql是如何保证一致性的呢？最简单的做法是在每次事务提交的时候，将该事务涉及修改的数据页全部刷新到磁盘中。但是这么做会有严重的性能问题，主要体现在两个方面：

1. 因为 Innodb 是以 页 为单位进行磁盘交互的，而一个事务很可能只修改一个数据页里面的几个字节，这个时候将完整的数据页刷到磁盘的话，太浪费资源了！

2. 一个事务可能涉及修改多个数据页，并且这些数据页在物理上并不连续，使用随机IO写入性能太差！

因此 mysql 设计了 redo log ， 具体来说就是只记录事务对数据页做了哪些修改，这样就能完美地解决性能问题了(相对而言文件更小并且是顺序IO)。

redo log基本概念

redo log 包括两部分：一个是内存中的日志缓冲( redo log buffer )，另一个是磁盘上的日志文件( redo logfile)。mysql 每执行一条 DML 语句，先将记录写入 redo log buffer，后续某个时间点再一次性将多个操作记录写到 redo log file。这种 先写日志，再写磁盘 的技术就是 MySQL
里经常说到的 WAL(Write-Ahead Logging) 技术。在计算机操作系统中，用户空间( user space )下的缓冲区数据一般情况下是无法直接写入磁盘的，中间必须经过操作系统内核空间( kernel space )缓冲区( OS Buffer )。因此， redo log buffer 写入 redo logfile 实际上是先写入 OS Buffer ，然后再通过系统调用 fsync() 将其刷到 redo log file
中，过程如下：mysql 支持三种将 redo log buffer 写入 redo log file 的时机，可以通过 innodb_flush_log_at_trx_commit 参数配置，各参数值含义如下：

redo log记录形式

前面说过， redo log 实际上记录数据页的变更，而这种变更记录是没必要全部保存，因此 redo log实现上采用了大小固定，循环写入的方式，当写到结尾时，会回到开头循环写日志。如下图：同时我们很容易得知， 在innodb中，既有redo log 需要刷盘，还有 数据页 也需要刷盘， redo log存在的意义主要就是降低对 数据页 刷盘的要求 ** 。在上图中， write pos 表示 redo log 当前记录的 LSN (逻辑序列号)位置， check point 表示 数据页更改记录 刷盘后对应 redo log 所处的 LSN(逻辑序列号)位置。write pos 到 check point 之间的部分是 redo log 空着的部分，用于记录新的记录；check point 到 write pos 之间是 redo log 待落盘的数据页更改记录。当 write pos追上check point 时，会先推动 check point 向前移动，空出位置再记录新的日志。启动 innodb 的时候，不管上次是正常关闭还是异常关闭，总是会进行恢复操作。因为 redo log记录的是数据页的物理变化，因此恢复的时候速度比逻辑日志(如 binlog )要快很多。重启innodb 时，首先会检查磁盘中数据页的 LSN ，如果数据页的LSN 小于日志中的 LSN ，则会从 checkpoint 开始恢复。还有一种情况，在宕机前正处于checkpoint 的刷盘过程，且数据页的刷盘进度超过了日志页的刷盘进度，此时会出现数据页中记录的 LSN 大于日志中的 LSN，这时超出日志进度的部分将不会重做，因为这本身就表示已经做过的事情，无需再重做。

redo log与binlog区别

由 binlog 和 redo log 的区别可知：binlog 日志只用于归档，只依靠 binlog 是没有 crash-safe 能力的。但只有 redo log 也不行，因为 redo log 是 InnoDB特有的，且日志上的记录落盘后会被覆盖掉。因此需要 binlog和 redo log二者同时记录，才能保证当数据库发生宕机重启时，数据不会丢失。2.3 UndoLog
数据库事务四大特性中有一个是 原子性 ，具体来说就是 原子性是指对数据库的一系列操作，要么全部成功，要么全部失败，不可能出现部分成功的情况。实际上， 原子性 底层就是通过 undo log 实现的。undo log主要记录了数据的逻辑变化，比如一条 INSERT 语句，对应一条DELETE 的 undo log ，对于每个 UPDATE 语句，对应一条相反的 UPDATE 的 undo log ，这样在发生错误时，就能回滚到事务之前的数据状态。同时， undo log 也是 MVCC(多版本并发控制)实现的关键。

MySQL 中的索引

索引的常见模型有哈希表、有序数组和搜索树。哈希表：一种以 KV 存储数据的结构，只适合等值查询，不适合范围查询。有序数组：只适用于静态存储引擎，涉及到插入的时候比较麻烦。可以参考 Java 中的 ArrayList。搜索树：按照数据结构中的二叉树来存储数据，不过此时是 N 叉树（B+树）。广泛应用在存储引擎层中。

B+树比 B 树优势在于：

1. B+ 树非叶子节点存储的只是索引，可以存储的更多。B+树比 B 树更加矮胖，IO 次数更少。

2. B+ 树叶子节点前后管理，更加方便范围查询。同时结果都在叶子节点，查询效率稳定。

3. B+树中更有利于对数据扫描，可以避免 B 树的回溯扫描。

索引的优点：

1. 唯一索引可以保证每一行数据的唯一性 ；

2. 提高查询速度 ；

3. 加速表与表的连接 ；

4. 显著的减少查询中分组和排序的时间；

5. 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

索引的缺点：

1. 创建跟维护都需要耗时 ；

2. 创建索引时，需要对表加锁，在锁表的同时，可能会影响到其他的数据操作 ；

3. 索引需要磁盘的空间进行存储，磁盘占用也很快；

4. 当对表中的数据进行 CRUD 的时，也会触发索引的维护，而维护索引需要时间，可能会降低数据操作性能。

索引设计的原则不应该：

1. 索引不是越多越好。索引太多，维护索引需要时间跟空间；

2. 频繁更新的数据，不宜建索引；

3. 数据量小的表没必要建立索引。

应该：

1. 重复率小的列建议生成索引。因为重复数据少，索引树查询更有效率，等价基数越大越好；

2. 数据具有唯一性，建议生成唯一性索引。在数据库的层面，保证数据正确性 ；

3. 频繁 group by、order by 的列建议生成索引。可以大幅提高分组和排序效率 ；

4. 经常用于查询条件的字段建议生成索引。通过索引查询，速度更快。

索引失效的场景：

1. 模糊搜索：左模糊或全模糊都会导致索引失效，比如'%a'和'%a%'。但是右模糊是可以利用索引的，比如'a%' 。

2. 隐式类型转换：比如 select * from t where name = xxx , name 是字符串类型，但是没有加引号，所以是由 MySQL 隐式转换的，所以会让索引失效 3、当语句中带有 or的时候：比如 select * from t where name=‘sw’ or age=14

3. 不符合联合索引的最左前缀匹配：(A,B,C)的联合索引，你只 where 了 C 或 B 或只有 B,C

关于索引的知识点：主键索引：主键索引的叶子节点存的是整行数据信息。在 InnoDB 里，主键索引也被称为聚簇索引（clustered index）。主键自增是无法保证完全自增的哦，遇到唯一键冲突、事务回滚等都可能导致不连续。唯一索引：以唯一列生成的索引，该列不允许有重复值，但允许有空值(NULL)普通索引跟唯一索引查询性能：InnoDB 的数据是按数据页为单位来读写的，默认每页 16KB，因此这两种索引查询数据性能差别微乎其微。change buffer：普通索引用在更新过程的加速，更新的字段如果在缓存中，如果是普通索引则直接更新即可。如果是唯一索引需要将所有数据读入内存来确保不违背唯一性，所以尽量用普通索引。非主键索引：非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引（secondary index）回表：先通过数据库索引扫描出数据所在的行，再通过行主键id取出索引中未提供的数据，即基于非主键索引的查询需要多扫描一棵索引树。覆盖索引：如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为覆盖索引。联合索引：相对单列索引，组合索引是用多个列组合构建的索引，一次性最多联合 16 个。最左前缀原则：对多个字段同时建立的组合索引(有顺序，ABC，ACB 是完全不同的两种联合索引) 以联合索引(a,b,c)为例，建立这样的索引相当于建立了索引a、ab、abc 三个索引。另外组合索引实际还是一个索引，并非真的创建了多个索引，只是产生的效果等价于产生多个索引。索引下推：MySQL 5.6 引入了索引下推优化，可以在索引遍历过程中，对索引中包含的字段先做判断，过滤掉不符合条件的记录，减少回表字数。索引维护：B+树为了维护索引有序性涉及到页分裂跟页合并。增删数据时需考虑页空间利用率。自增主键：一般会建立与业务无关的自增主键，不会触发叶子节点分裂。延迟关联：通过使用覆盖索引查询返回需要的主键，再根据主键关联原表获得需要的数据。InnoDB 存储：* .frm 文件是一份定义文件，也就是定义数据库表是一张怎么样的表。*.ibd 文件则是该表的索引，数据存储文件，既该表的所有索引树，所有行记录数据都存储在该文件中。MyISAM 存储：* .frm 文件是一份定义文件，也就是定义数据库表是一张怎么样的表。* .MYD 文件是 MyISAM 存储引擎表的所有行数据的文件。* .MYI 文件存放的是 MyISAM 存储引擎表的索引相关数据的文件。MyISAM 引擎下，表数据和表索引数据是分开存储的。MyISAM 查询：在 MyISAM 下，主键索引和辅助键索引都属于非聚簇索引。查询不管是走主键索引，还是非主键索引，在叶子结点得到的都是目的数据的地址，还需要通过该地址，才能在数据文件中找到目的数据。PS：InnoDB 支持聚簇索引，MyISAM 不支持聚簇索引。

SQL 事务隔离级别

4.1 ACID 的四个特性

1. 原子性（Atomicity）：把多个操作放到一个事务中，保证这些操作要么都成功，要么都不成功；

2. 一致性（Consistency）：理解成一串对数据进行操作的程序执行下来，不会对数据产生不好的影响，比如凭空产生，或消失；

3. 隔离性（Isolation，又称独立性）：隔离性的意思就是多个事务之间互相不干扰，即使是并发事务的情况下，他们只是两个并发执行没有交集，互不影响的东西；当然实现中，也不一定需要这么完整隔离性，即不一定需要这么的互不干扰，有时候还是允许有部分干扰的。所以 MySQL 可以支持 4 种事务隔离性；

4. 持久性（Durability）：当某个操作操作完毕了，那么结果就是这样了，并且这个操作会持久化到日志记录中。

PS：ACID 中 C 与 CAP 定理中 C 的区别

ACID 的 C 着重强调单数据库事务操作时，要保证数据的完整和正确性，数据不会凭空消失跟增加。CAP 理论中的 C 指的是对一个数据多个备份的读写一致性

4.2 事务操作可能会出现的数据问题

1. 脏读(dirty read)：B 事务更改数据还未提交，A 事务已经看到并且用了。B 事务如果回滚，则 A 事务做错了；

2. 不可重复读(non-repeatable read)：不可重复读的重点是修改: 同样的条件, 你读取过的数据, 再次读取出来发现值不一样了，只需要锁住满足条件的记录 ；

3. 幻读(phantom read)：事务 A 先修改了某个表的所有纪录的状态字段为已处理，未提交；事务 B 也在此时新增了一条未处理的记录，并提交了；事务 A 随后查询记录，却发现有一条记录是未处理的造成幻读现象，幻读仅专指新插入的行。幻读会造成语义上的问题跟数据一致性问题；

4. 在可重复读 RR 隔离级别下，普通查询是快照读，是不会看到别的事务插入的数据的。因此，幻读在当前读下才会出现。要用间隙锁解决此问题。

在说隔离级别之前，你首先要知道，你隔离得越严实，效率就会越低。因此很多时候，我们都要在二者之间寻找一个平衡点。SQL 标准的事务隔离级别由低到高如下：

上图从上到下的模式会导致系统的并行性能依次降低，安全性依次提高。读未提交：别人改数据的事务尚未提交，我在我的事务中也能读到。读已提交(Oracle 默认)：别人改数据的事务已经提交，我在我的事务中才能读到。可重复读(MySQL 默认)：别人改数据的事务已经提交，我在我的事务中也不去读，以此保证重复读一致性。串行：我的事务尚未提交，别人就别想改数据。标准跟实现：上面都是关于事务的标准，但是每一种数据库都有不同的实现，比如 MySQL InnDB 默认为 RR 级别，但是不会出现幻读。因为当事务 A 更新了所有记录的某个字段，此时事务 A 会获得对这个表的表锁，因为事务 A 还没有提交，所以事务 A 获得的锁没有释放，此时事务 B 在该表插入新记录，会因为无法获得该表的锁，则导致插入操作被阻塞。只有事务 A 提交了事务后，释放了锁，事务 B 才能进行接下去的操作。所以可以说，MySQL 的 RR 级别的隔离是已经实现解决了脏读，不可重复读和幻读的。

MySQL 中的锁

无论是 Java 的并发编程还是数据库的并发操作都会涉及到锁，研发人员引入了悲观锁跟乐观锁这样一种锁的设计思想。悲观锁：优点：适合在写多读少的并发环境中使用，虽然无法维持非常高的性能，但是在乐观锁无法提更好的性能前提下，可以做到数据的安全性缺点：加锁会增加系统开销，虽然能保证数据的安全，但数据处理吞吐量低，不适合在读书写少的场合下使用乐观锁：优点：在读多写少的并发场景下，可以避免数据库加锁的开销，提高 DAO 层的响应性能，很多情况下 ORM 工具都有带有乐观锁的实现，所以这些方法不一定需要我们人为的去实现。缺点：在写多读少的并发场景下，即在写操作竞争激烈的情况下，会导致 CAS 多次重试，冲突频率过高，导致开销比悲观锁更高。实现：数据库层面的乐观锁其实跟 CAS 思想类似， 通数据版本号或者时间戳也可以实现。数据库并发场景主要有三种：读-读：不存在任何问题，也不需要并发控制；读-写：有隔离性问题，可能遇到脏读，幻读，不可重复读；写-写：可能存更新丢失问题，比如第一类更新丢失，第二类更新丢失。两类更新丢失问题：第一类更新丢失：事务 A 的事务回滚覆盖了事务 B 已提交的结果第二类更新丢失：事务 A 的提交覆盖了事务 B 已提交的结果为了合理贯彻落实锁的思想，MySQL 中引入了杂七杂八的各种锁：

锁分类

MySQL 支持三种层级的锁定，分别为：表级锁定：MySQL 中锁定粒度最大的一种锁，最常使用的 MYISAM 与INNODB 都支持表级锁定。页级锁定：是 MySQL 中锁定粒度介于行级锁和表级锁中间的一种锁，表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。行级锁定：Mysql 中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，但加锁的开销也最大行级锁不一定比表级锁要好：锁的粒度越细，代价越高，相比表级锁在表的头部直接加锁，行级锁还要扫描找到对应的行对其上锁，这样的代价其实是比较高的，所以表锁和行锁各有所长。

MyISAM 中的锁

1. 虽然 MySQL 支持表、页、行三级锁定，但 MyISAM 存储引擎只支持表锁。所以 MyISAM 的加锁相对比较开销低，但数据操作的并发性能相对就不高。但如果写操作都是尾插入，那还是可以支持一定程度的读写并发
2. 从 MyISAM 所支持的锁中也可以看出，MyISAM 是一个支持读读并发，但不支持通用读写并发，写写并发的数据库引擎，所以它更适合用于读多写少的应用场合，一般工程中也用的较少。

InnoDB 中的锁

该模式下支持的锁实在是太多了，具体如下：共享锁和排他锁(Shared and Exclusive Locks)、意向锁(Intention Locks)、记录锁(Record Locks)、间隙锁(Gap Locks)、临键锁(Next-Key Locks)、插入意向锁(Insert Intention Locks)、主键自增锁(AUTO-INC Locks)、空间索引断言锁(Predicate Locks for Spatial Indexes)。举个栗子，比如行锁里的共享锁跟排它锁：lock in share modle 共享读锁：为了确保自己查到的数据没有被其他的事务正在修改，也就是说，确保查到的数据是最新的数据，并且不允许其他人来修改数据。但是自己不一定能够修改数据，因为有可能其他的事务也对这些数据使用了 in share mode 的方式上了 S 锁。如果不及时的 commit 或者 rollback 也可能会造成大量的事务等待。for update 排它写锁:为了让自己查到的数据确保是最新数据，并且查到后的数据只允许自己来修改的时候，需要用到 for update。相当于一个 update 语句。在业务繁忙的情况下，如果事务没有及时的 commit 或者 rollback 可能会造成其他事务长时间的等待，从而影响数据库的并发使用效率。Gap Lock 间隙锁：

1. 行锁只能锁住行，如果在记录之间的间隙插入数据就无法解决了，因此MySQL 引入了间隙锁(Gap Lock)。间隙锁是左右开区间。间隙锁之间不会冲突。

2. 间隙锁和行锁合称 NextKeyLock，每个 NextKeyLock 是前开后闭区间。

间隙锁加锁原则：

1. 加锁的基本单位是 NextKeyLock，是前开后闭区间。

2. 查找过程中访问到的对象才会加锁。

3. 索引上的等值查询，给唯一索引加锁的时候，NextKeyLock 退化为行锁。

4. 索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，NextKeyLock 退化为间隙锁。

5. 唯一索引上的范围查询会访问到不满足条件的第一个值为止。

MVCC

什么是MVCC：MVCC(Multi Version Concurrency Control的简称)，代表多版本并发控制。与MVCC相对的，是基于锁的并发控制，Lock-Based Concurrency Control)。
MVCC最大的优势：读不加锁，读写不冲突。在读多写少的OLTP应用中，读写不冲突是非常重要的，极大的增加了系统的并发性能

MVCC实现：

MVCC是通过在每行记录后面保存两个隐藏的列来实现的。这两个列，一个保存了行的创建时间，一个保存行的过期时间（或删除时间）。当然存储的并不是实际的时间值，而是系统版本号（system version number)。每开始一个新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比较。下面看一下在REPEATABLE READ隔离级别下，MVCC具体是如何操作的。

• SELECT

  InnoDB会根据以下两个条件检查每行记录：

1. InnoDB只查找版本早于当前事务版本的数据行（也就是，行的系统版本号小于或等于事务的系统版本号），这样可以确保事务读取的行，要么是在事务开始前已经存在的，要么是事务自身插入或者修改过的。

2. 行的删除版本要么未定义，要么大于当前事务版本号。这可以确保事务读取到的行，在事务开始之前未被删除。

只有符合上述两个条件的记录，才能返回作为查询结果

举例说明：

create table mvcctest( id int primary key auto_increment, name varchar(20));

transaction 1:

start transaction;
insert into mvcctest values(NULL,'mi');
insert into mvcctest values(NULL,'kong');
commit;

假设系统初始事务ID为1；transaction 2:

start transaction;
select * from mvcctest; (1)
select * from mvcctest; (2)
commit

SELECT：

假设当执行事务2的过程中，准备执行语句(2)时，开始执行事务3：transaction 3:

start transaction;
insert into mvcctest values(NULL,'qu');
commit;

事务3执行完毕，开始执行事务2 语句2，由于事务2只能查询创建时间小于等于2的，所以事务3新增的记录在事务2中是查不出来的，这就通过乐观锁的方式避免了幻读的产生。

UPDATE：

假设当执行事务2的过程中，准备执行语句(2)时，开始执行事务4：transaction session 4:

start transaction;
update mvcctest set name = 'fan' where id = 2;commit;

InnoDB执行UPDATE，实际上是新插入了一行记录，并保存其创建时间为当前事务的ID，同时保存当前事务ID到要UPDATE的行的删除时间。事务4执行完毕，开始执行事务2 语句2，由于事务2只能查询创建时间小于等于2的，所以事务修改的记录在事务2中是查不出来的，这样就保证了事务在两次读取时读取到的数据的状态是一致的。

DELETE：

假设当执行事务2的过程中，准备执行语句(2)时，开始执行事务5：transaction session 5:

start transaction;
delete from mvcctest where id = 2;commit;

事务5执行完毕，开始执行事务2 语句2，由于事务2只能查询创建时间小于等于2、并且过期时间大于等于2，所以id=2的记录在事务2 语句2中，也是可以查出来的,这样就保证了事务在两次读取时读取到的数据的状态是一致的。

缓冲池（buffer pool）

应用系统分层架构，为了加速数据访问，会把最常访问的数据，放在缓存(cache)里，避免每次都去访问数据库。操作系统，会有缓冲池(buffer pool)机制，避免每次访问磁盘，以加速数据的访问。MySQL 作为一个存储系统，同样具有缓冲池(buffer pool)机制，以避免每次查询数据都进行磁盘 IO。7.1 主要作用：

1. 存在的意义是加速查询 

2. 缓冲池(buffer pool) 是一种常见的降低磁盘访问 的机制；

3. 缓冲池通常以页(page 16K)为单位缓存数据；

4. 缓冲池的常见管理算法是 LRU，memcache，OS，InnoDB 都使用了这种算法；

5. InnoDB 对普通 LRU 进行了优化：将缓冲池分为老生代和新生代，入缓冲池的页，优先进入老生代，该页被访问，才进入新生代，以解决预读失效的问题页被访问。且在老生代停留时间超过配置阈值的，才进入新生代，以解决批量数据访问，大量热数据淘汰的问题。

7.2 预读失效：由于预读(Read-Ahead)，提前把页放入了缓冲池，但最终 MySQL 并没有从页中读取数据，称为预读失效

7.3 缓冲池污染：当某一个 SQL 语句，要批量扫描大量数据时，可能导致把缓冲池的所有页都替换出去，导致大量热数据被换出，MySQL 性能急剧下降，这种情况叫缓冲池污染。解决办法：加入老生代停留时间窗口策略后，短时间内被大量加载的页，并不会立刻插入新生代头部，而是优先淘汰那些，短期内仅仅访问了一次的页。

table 瘦身

空洞：MySQL 执行 delete 命令其实只是把记录的位置，或者数据页标记为了可复用，但磁盘文件的大小是不会变的。通过 delete 命令是不能回收表空间的。这些可以复用，而没有被使用的空间，看起来就像是空洞。插入时候引发分裂同样会产生空洞。重建表思路：

1. 新建一个跟 A 表结构相同的表 B；

2. 按照主键 ID 将 A 数据一行行读取同步到表 B；

3. 用表 B 替换表 A 实现效果上的瘦身。

重建表指令：alter table A engine=InnoDB，慎重用。

SQL Joins、统计、 随机查询

7种 join 具体如下：

统计：

1. MyISAM 模式下把一个表的总行数存在了磁盘上，直接拿来用即可 

2. InnoDB 引擎由于 MVCC 的原因，需要把数据读出来然后累计求和 

3. 性能来说，由好到坏：count(字段) < count(主键id) < count(1) ≈ count(*)，尽量用 count(*)。

随机查询：

mysql> select word from words order by rand() limit 3;

直接使用 order by rand()，explain 这个语句发现需要 Using temporary 和 Using filesort，查询的执行代价往往是比较大的。所以在设计的时要避开这种写法。

mysql> select count(*) into @C from t;
set @Y1 = floor(@C * rand());
set @Y2 = floor(@C * rand());
set @Y3 = floor(@C * rand());
select * from t limit @Y1,1;
select * from t limit @Y2,1;
select * from t limit @Y3,1;

这样可以避免临时表跟排序的产生，最终查询行数 = C + (Y1+1) + (Y2+1) + (Y3+1)exist 和 in 对比：

1. in 查询时首先查询子查询的表，然后将内表和外表做一个笛卡尔积，然后按照条件进行筛选。

2. 子查询使用 exists，会先进行主查询，将查询到的每行数据循环带入子查询校验是否存在，过滤出整体的返回数据。

3. 两表大小相当，in 和 exists 差别不大。内表大，用 exists 效率较高；内表小，用 in 效率较高。

4. 查询用 not in 那么内外表都进行全表扫描，没有用到索引；而 not extsts 的子查询依然能用到表上的索引。not exists 都比 not in 要快。

MySQL 优化

SQL 优化主要分 4 个方向：SQL 语句跟索引、表结构、系统配置、硬件。总优化思路就是最大化利用索引、尽可能避免全表扫描、减少无效数据的查询：减少数据访问：设置合理的字段类型，启用压缩，通过索引访问等减少磁盘 IO。返回更少的数据：只返回需要的字段和数据分页处理，减少磁盘 IO 及网络 IO。减少交互次数：批量 DML 操作，函数存储等减少数据连接次数。减少服务器 CPU 开销：尽量减少数据库排序操作以及全表查询，减少 CPU 内存占用 。分表分区：使用表分区，可以增加并行操作，更大限度利用 CPU 资源。SQL 语句优化大致举例：

1、合理建立覆盖索引：可以有效减少回表。2、union，or，in都能命中索引，建议使用 in 3、负向条件(!=、、not in、not exists、not like 等) 索引不会使用索引，建议用in。4、在列上进行运算或使用函数会使索引失效，从而进行全表扫描 5、小心隐式类型转换，原字符串用整型会触发 CAST 函数导致索引失效。原 int 用字符串则会走索引。6、不建议使用%前缀模糊查询。7、多表关联查询时，小表在前，大表在后。在 MySQL 中，执行 from 后的表关联查询是从左往右执行的(Oracle 相反)，第一张表会涉及到全表扫描。8、调整 Where 字句中的连接顺序，MySQL 采用从左往右，自上而下的顺序解析 where 子句。根据这个原理，应将过滤数据多的条件往前放，最快速度缩小结果集。

SQL调优大致思路：

1. 先用慢查询日志定位具体需要优化的 sql；

2. 使用 explain 执行计划查看索引使用情况 ；

3. 重点关注(一般情况下根据这 4 列就能找到索引问题)：key(查看有没有使用索引)、key_len(查看索引使用是否充分)、type(查看索引类型)、Extra(查看附加信息：排序、临时表、where 条件为 false 等)；

4. 根据上 1 步找出的索引问题优化 sql 5、再回到第 2 步。

表结构优化：

1. 尽量使用 TINYINT、SMALLINT、MEDIUM_INT 作为整数类型而非 INT，如果非负则加上 UNSIGNED 。

2. VARCHAR 的长度只分配真正需要的空间 。

3. 尽量使用 TIMESTAMP 而非 DATETIME 。

4. 单表不要有太多字段，建议在 20 以内。

5. 避免使用 NULL 字段，很难查询优化且占用额外索引空间。字符串默认为''。

读写分离：只在主服务器上写，只在从服务器上读。对应到数据库集群一般都是一主一从、一主多从。业务服务器把需要写的操作都写到主数据库中，读的操作都去从库查询。主库会同步数据到从库保证数据的一致性。一般 读写分离 的实现方式有两种：代码封装跟数据库中间件。分库分表：分库分表分为垂直和水平两个方式，一般是先垂直后水平。

1. 垂直分库：将应用分为若干模块，比如订单模块、用户模块、商品模块、支付模块等等。其实就是微服务的理念。

2. 垂直分表：一般将不常用字段跟数据较大的字段做拆分。

3. 水平分表：根据场景选择什么字段作分表字段，比如淘宝日订单 1000 万，用 userId 作分表字段，数据查询支持到最近 6 个月的订单，超过 6 个月的做归档处理，那么 6 个月的数据量就是 18 亿，分 1024 张表，每个表存 200W 数据，hash(userId)%100 找到对应表格。

4. ID生成器：分布式ID 需要跨库全局唯一方便查询存储-检索数据，确保唯一性跟数字递增性。

更多精彩内容，关注我们▼▼