丁奇出品：MySQL知识图谱.PNG

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

连接器 - 分析器 - 优化器 - 执行器

redo log（异常重启恢复）

• 使用WAL技术， write-adead logging ，先写日志，再写磁盘
• 大小固定，循环写
• 属于Innodb特有的日志，保证数据库发生重启，之前提交的记录都不会丢失，叫crash-safe

binlog（备份，防止删除）

• MYSQL实现的，所有的引擎都有
• 物理日志，追加写，达到大小就切换下一个
• redo log是循环写的，不持久保存，binlog的归档这个功能，是redo log 不具备的

image.png

redo log的写入分为两个步骤，prapare和commit，也就是“两阶段提交”

• 这是为了让两份日志之间的逻辑一致。

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

• 使用binlog

3、为什么你改了我还看不见

image.png

我们来看看在不同的隔离级别下，事务 A 会有哪些不同的返回结果，也就是图里面 V1、 V2、V3 的返回值分别是什么。

• 若隔离级别是“读未提交”， 则 V1 的值就是 2。这时候事务 B 虽然还没有提交，但是 结果已经被 A 看到了。因此，V2、V3 也都是 2。（v1= 2， v2= 2， v3=2 ）
• 若隔离级别是“读提交”，则 V1 是 1，V2 的值是 2。事务 B 的更新在提交后才能被 A 看到。所以， V3 的值也是 2。（v1= 1， v2= 2， v3=2）
• 若隔离级别是“可重复读”，则 V1、V2 是 1，V3 是 2。之所以 V2 还是 1，遵循的就 是这个要求:事务在执行期间看到的数据前后必须是一致的。（v1= 1， v2= 1， v3=2），可以理解为每个事务启动的时候打一个快照，别人改的“我不听我不管”
• 若隔离级别是“串行化”，则在事务 B 执行“将 1 改成 2”的时候，会被锁住。直到事 务 A 提交后，事务 B 才可以继续执行。所以从 A 的角度看， V1、V2 值是 1，V3 的值 是 2。（v1= 1， v2= 1， v3=2）

什么时候需要“可重复读”的场景

做数据校对，判断上个月的余额和当前余额的差额，是否与本月的账单明细一致，即使有用户发生一笔交易，也不会影响你的校对结果

4、深入浅出索引

索引的常见模型

• 哈希表这种结构适用于只有等值查询的场景
• 有序数组在等值查询和范围查询场景中的性能就都非常优秀
• 有序数组索引只适用于静态存储引擎
• 二叉搜索树的特点是:每个节点的左儿子小于父节点，父节点又小于右儿子。

根据叶子节点的内容，索引类型分为主键索引和非主键索引

• 主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引 (clustered index)。
• 非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引 (secondary index)。

基于主键索引和普通索引的查询有什么区别?

如果语句是 select * from T where ID=500，即主键查询方式，则只需要搜索 ID 这棵 B+ 树;

如果语句是 select * from T where k=5，即普通索引查询方式，则需要先搜索 k 索引 树，得到 ID 的值为 500，再到 ID 索引树搜索一次。这个过程称为回表。

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

由于每个非主键索引的叶子节点上都是主键的值。显然，主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。从性能和存储空间方面考量，自增主键往往是更合理的选择。

有没有什么场景适合用业务字段直接做主键的呢?还是有的。比如，有些业务的场景需求是这样的:

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

现在，我们一起来看看这条 SQL 查询语句的执行流程:

1. 在 k 索引树上找到 k=3 的记录，取得 ID = 300;
2. 再到 ID 索引树查到 ID=300 对应的 R3;（回表）
3. 在 k 索引树取下一个值 k=5，取得 ID=500;
4. 再回到 ID 索引树查到 ID=500 对应的 R4;（回表）
5. 在 k 索引树取下一个值 k=6，不满足条件，循环结束。

在这个过程中，回到主键索引树搜索的过程，我们称为回表。可以看到，这个查询过程读 了 k 索引树的 3 条记录(步骤 1、3 和 5)，回表了两次(步骤 2 和 4)。

在这个例子中，由于查询结果所需要的数据只在主键索引上有，所以不得不回表。那么，有没有可能经过索引优化，避免回表过程呢?

• 覆盖索引
• 最左前缀原则
• 索引下推

覆盖索引

如果执行的语句是 select ID from T where k between 3 and 5，这时只需要查 ID 的 值，而 ID 的值已经在 k 索引树上了，因此可以直接提供查询结果，不需要回表。也就是 说，在这个查询里面，索引 k 已经“覆盖了”我们的查询需求，我们称为覆盖索引。

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

单独为一个不频繁的请求创建一个(身份证号，地址)的索引又感觉有点浪费。应该怎么做呢

• 最左前缀原则

最左前缀原则

在建立联合索引的时候，如何安排索引内的字段排序？

• 通过调整顺序，可以少维护一个索引，那么这个顺序就可以优先考虑采用（比如已经有（a，b），就不需要单独在a上建立索引）
• 考虑空间，name字段是比age字段大，建议创建一个（name，age）的联合索引和一个（age）的单独字段

索引下推

mysql5.6 引入索引下推优化，可以在索引遍历过程中，对索引包含的字段先做判断，直接过滤不满足条件的记录，减少回表次数。

image.png

image.png

小结

索引优化：包含覆盖索引、前缀索引，索引下推

6、全局锁和表锁，给表加个字段怎么有这么多障碍

根据加锁的范围，MySQL 里面的锁大致可以分成全局锁、表级锁和行锁三类

全局表

全局锁就是对整个数据库实例加锁。使用场景是做全库逻辑备份

表级锁

表级锁分两种：1、表锁 2、元数据锁

表锁的语法是 lock tables ... read/write。

MDL(metadata lock) 不需要显式使用，在访问一个表的时 候会被自动加上。MDL 的作用是，保证读写的正确性。

如何安全地给小表加字段?

• 首先我们要解决长事务，事务不提交，就会一直占着 MDL 锁。在 MySQL 的 information_schema 库的 innodb_trx 表中，你可以查到当前执行中的事务。如果你要 做 DDL 变更的表刚好有长事务在执行，要考虑先暂停 DDL，或者 kill 掉这个长事务
• 在 alter table 语句里面设定等待时间，如果在这个指定的等待时间里面能够拿到 MDL 写锁最好，拿不 到也不要阻塞后面的业务语句，先放弃。之后开发人员或者 DBA 再通过重试命令重复这 个过程。

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

两阶段锁协议

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

死锁和死锁检测

当并发系统不同线程出现循环资源依赖，就会导致这几个线程进入无限等待的状态

image.png

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

1. 直接进入等待，直到超时，这个超时时间可以通过参数innodb_lock_wait_timeout 来设置。默认值是 50s
2. 发起死锁检测，发现死锁后，主动回滚死锁链条中的某个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻 辑。默认值本身就是 on

我给你留下一个问题吧。如果你要删除一个表里面的前 10000 行数据，有以下三种 方法可以做到:

• 第一种，直接执行 delete from T limit 10000;
• 第二种，在一个连接中循环执行 20 次 delete from T limit 500;
• 第三种，在 20 个连接中同时执行 delete from T limit 500。

答案（方案二）

• 方案一，事务相对较长，则占用锁的时间较长，会导致其他客户端等待资源时间较长。
• 方案二，串行化执行，将相对长的事务分成多次相对短的事务，则每次事务占用锁的时间 相对较短，其他客户端在等待相应资源的时间也较短。这样的操作，同时也意味着将资源 分片使用(每次执行使用不同片段的资源)，可以提高并发性。
• 方案三，人为自己制造锁竞争，加剧并发量。

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

MYSQL 的MVVC

是行级锁的变种，尽可能减少加锁操作，实现了非阻塞读以及写只锁定行的功能

通过保存对行记录在某个时间点的快照实现。

INNODB的MVVC

通过在行记录后添加两个隐藏列实现，分别是创建时间和删除时间，保存的并不是时间，而是相应的系统版本号，这个版本号是全局事务递增的，创建时间就事务开始时的版本号，删除时间保存该记录被删除时事务的版本号。

select

只查询创建时间小于当前事务版本号，确保事务读取的行要么事务开始前就已经存在，要么是自己插入和修改

insert

添加创建时间为当前事务版本号

delete

添加删除时间为当前事务版本号

update

实际是新增了一行记录，将当前事务版本号保存为该记录创建时间，同时修改要update行的删除时间为当前事务版本号

image.png

9、普通索引和唯一索引，应该如何选择？

查询过程

假设，执行查询的语句是 select id from T where k=5。这个查询语句在索引树上查找的过程，先是通过B+树从树根开始，按层搜索到叶子节点，也就是图中右下角的这个数据页，然后可以认为数据页内部通过二分法来定位记录。

• 对于普通索引来说，查找到满足条件的第一个记录(5,500)后，需要查找下一个记录，直到碰到第一个不满足k=5条件的记录。
• 对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索。

那么，这个不同带来的性能差距会有多少呢？答案是，微乎其微。

你知道的，InnoDB的数据是按数据页为单位来读写的。也就是说，当需要读一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存。在InnoDB中，每个数据页的大小默认是16KB。

因为引擎是按页读写的，所以说，当找到k=5的记录的时候，它所在的数据页就都在内存里了。那么，对于普通索引来说，要多做的那一次“查找和判断下一条记录”的操作，就只需要一次指针寻找和一次计算。

当然，如果k=5这个记录刚好是这个数据页的最后一个记录，那么要取下一个记录，必须读取下一个数据页，这个操作会稍微复杂一些。

更新过程

当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InooDB会将这些更新操作缓存在change buffer中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行change buffer中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。

change buffer

change buffer 只限于用在普通索引的场景下，而不适用于唯一索引。

change buffer 的主要目的就是将记 录的变更动作缓存下来，所以在一个数据页做 merge 之前，change buffer 记录的变更 越多(也就是这个页面上要更新的次数越多)，收益就越大

其实，这两类索引在查询能力上是没差别的，主要考虑的是对更新性能的影响。所以，我建议你尽量选择普通索引。

change buffer 和 redo log

redo log 主要节省的 是随机写磁盘的 IO 消耗(转成顺序写)，而 change buffer 主要节省的则是随机读磁盘 的 IO 消耗。

redo log（写）、change buffer（读）

10、MYSQL为什么有时候会选错索引？

索引统计

选择索引是优化器的工作。

优化器选择索引的目的，是找到一个最优的执行方案，并用最小的代价去执行语句。在数据库里面，扫描行数是影响执行代价的因素之一。扫描的行数越少，意味着访问磁盘数据的次数越少，消耗的CPU资源越少。

扫描行数并不是唯一的判断标准，优化器还会结合是否使用临时表、是否排序等因素进行综合判断。

MySQL在真正开始执行语句之前，并不能精确地知道满足这个条件的记录有多少条，而只能根据统计信息来估算记录数。

这个统计信息就是索引的“区分度”。显然，一个索引上不同的值越多，这个索引的区分度就越好。而一个索引上不同的值的个数，我们称之为“基数”（cardinality）。也就是说，这个基数越大，索引的区分度越好。

优化器会估算这两个选择的代价，从结果看来，优化器认为直接扫描主键索引更快。当然，从执行时间看来，这个选择并不是最优的。

索引选择异常和处理

1、采用force index强行选择一个索引
2、我们可以考虑修改语句，引导MySQL使用我们期望的索引。
3、在有些场景下，我们可以新建一个更合适的索引，来提供给优化器做选择，或删掉误用的索引。

11、怎么给字符串字段加索引？

前缀索引

使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。

既可以占用更小的空间，也能达到相同的查询效率的方法：

• 使用倒序存储。你可以在表上再创建一个整数字段，来保存身份证的校验码，同时在这个字段上创建索引。
• 使用 hash 字段。你可以在表上再创建一个整数字段，来保存身份证的校验码，同时在这个字段上创建索引。

它们的区别，主要体现在以下三个方面：

1. 从占用的额外空间来看，倒序存储方式在主键索引上，不会消耗额外的存储空间，而
   hash 字段方法需要增加一个字段。当然，倒序存储方式使用 4 个字节的前缀长度应该
   是不够的，如果再长一点，这个消耗跟额外这个 hash 字段也差不多抵消了。
2. 在 CPU 消耗方面，倒序方式每次写和读的时候，都需要额外调用一次 reverse 函数，
   而 hash 字段的方式需要额外调用一次 crc32() 函数。如果只从这两个函数的计算复杂
   度来看的话，reverse 函数额外消耗的 CPU 资源会更小些。
3. 从查询效率上看，使用 hash 字段方式的查询性能相对更稳定一些。因为 crc32 算出来
   的值虽然有冲突的概率，但是概率非常小，可以认为每次查询的平均扫描行数接近 1。
   而倒序存储方式毕竟还是用的前缀索引的方式，也就是说还是会增加扫描行数。

12、为什么我的MySQL会“抖”一下？

当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”。内存数据
写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页”。

什么情况会引发数据库的 flush （刷脏页）过程呢？

1. 第一种场景就是 InnoDB 的 redo log 写满了。这时候系统会停止所有更新操作，把 checkpoint 往前推进，redo log 留出空间可以继续写。我在第二讲画了一个redo log 的示意图，这里我改成环形，便于大家理解。

   
   
   image.png
2. 第二种场景是对应的就是系统内存不足。当需要新的内存页，而内存不够用的时候，就要淘汰一些数据页，空出内存给别的数据页使用。如果淘汰的是“脏页”，就要先将脏页写到磁盘。
3. 第三种场景是，对应的就是 MySQL 认为系统“空闲”的时候。只要有机会就刷一点“脏页”。
4. 第四种场景是，对应的就是 MySQL 正常关闭的情况。这时候，MySQL 会把内存的脏页都flush 到磁盘上，这样下次 MySQL 启动的时候，就可以直接从磁盘上读数据，启动速度会很快。

InnoDB 刷脏页的控制策略

InnoDB 的刷盘速度就是要参考这两个因素：一个是脏页比例，一个是 redo log 写
盘速度。
InnoDB 会根据这两个因素先单独算出两个数字

13、为什么表数据删除一半，表文件大小不变？

表数据既可以存在共享表空间里，也可以是单独的文件。这个行为是由参数 innodb_file_per_table 控制的:

1. 这个参数设置为 OFF 表示的是，表的数据放在系统共享表空间，也就是跟数据字典放 在一起;
2. 这个参数设置为 ON 表示的是，每个 InnoDB 表数据存储在一个以 .ibd 为后缀的文件 中。

我建议你不论使用 MySQL 的哪个版本，都将这个值设置为 ON。因为，一个表单独存储 为一个文件更容易管理，而且在你不需要这个表的时候，通过 drop table 命令，系统就会 直接删除这个文件。而如果是放在共享表空间中，即使表删掉了，空间也是不会回收的。

14、count(*)这么慢，我该怎么办?

为什么 InnoDB 不跟 MyISAM 一样，也把数字存起来呢?

这是因为即使是在同一个时刻的多个查询，由于多版本并发控制(MVCC)的原因， InnoDB 表“应该返回多少行”也是不确定的。这里，我用一个算 count(*) 的例子来为你 解释一下。

这和 InnoDB 的事务设计有关系，可重复读是它默认的隔离级别，在代码上就是通过多版 本并发控制，也就是 MVCC 来实现的。每一行记录都要判断自己是否对这个会话可见，因 此对于 count(*) 请求来说，InnoDB 只好把数据一行一行地读出依次判断，可见的行才能 够用于计算“基于这个查询”的表的总行数。

1、对于 count(主键 id) 来说，InnoDB 引擎会遍历整张表，把每一行的 id 值都取出来，返 回给 server 层。server 层拿到 id 后，判断是不可能为空的，就按行累加。

2、对于 count(1) 来说，InnoDB 引擎遍历整张表，但不取值。server 层对于返回的每一 行，放一个数字“1”进去，判断是不可能为空的，按行累加。
单看这两个用法的差别的话，你能对比出来，count(1) 执行得要比 count(主键 id) 快。因 为从引擎返回 id 会涉及到解析数据行，以及拷贝字段值的操作。

3、对于 count(字段) 来说:

1. 如果这个“字段”是定义为 not null 的话，一行行地从记录里面读出这个字段，判断不 能为 null，按行累加;
2. 如果这个“字段”定义允许为 null，那么执行的时候，判断到有可能是 null，还要把值 取出来再判断一下，不是 null 才累加。

15、日志和索引相关问题

追问 1:MySQL 怎么知道 binlog 是完整的?

回答:一个事务的 binlog 是有完整格式的:

• statement 格式的 binlog，最后会有 COMMIT;
• row 格式的 binlog，最后会有一个 XID event。

另外，在 MySQL 5.6.2 版本以后，还引入了 binlog-checksum 参数，用来验证 binlog 内容的正确性。对于 binlog 日志由于磁盘原因，可能会在日志中间出错的情况，MySQL 可以通过校验 checksum 的结果来发现。所以，MySQL 还是有办法验证事务 binlog 的 完整性的。

追问 2:redo log 和 binlog 是怎么关联起来的?

回答:它们有一个共同的数据字段，叫 XID。崩溃恢复的时候，会按顺序扫描 redo log:

• 如果碰到既有 prepare、又有 commit 的 redo log，就直接提交;
• 如果碰到只有 parepare、而没有 commit 的 redo log，就拿着 XID 去 binlog 找对应 的事务。