数据库

常见面试题

用于自测，点击 G 即可跳转到相应答案~~

MySQL 索引(⭐⭐必问)

• 索引的类型有哪些？G
• 索引的数据结构有哪些？G
• B+树和B-树的区别？G
• B+树和Hash的区别？G
• 索引创建原则？G
• 什么情况下索引失效？G
• 聚集索引是什么？选取规则是什么？G
• 前缀索引？G
• uuid 和 自增id，做索引有啥区别？G
• 什么是回表查询？什么时候会触发回表查询？G⭐🚩
• 联合索引应用场景？为什么要建联合索引？G⭐🚩

MySQL 事务(⭐⭐必问)

• 事务隔离级别？G
• 可重复读下，如何避免幻读？G
• MVCC 的实现原理？G
• 各事务隔离级别是如何实现的？可重复读：G；读已提交：G
• 事务实现原理？ACID 是怎么保证的？未

MySQL 锁(⭐⭐必问)

• 行级锁，表级锁；排它锁，共享锁；记录锁、间隙锁，临键锁；两阶段锁；
• 死锁的原因？G
• 如何避免死锁？G

MySQL 日志(⭐⭐常问)

• undo log 是啥？有啥用？G
• redo log 是啥？有啥用？G
• undo log 和 redo log 有啥区别？G
• 为什么有 bin log 了还要 redo log，两者有什么区别以及两者生成的时机？G
• 执行 update 的过程？⭐G
• 事务提交过程：什么是两阶段提交？G 为啥要两阶段提交？G

MySQL 架构(⭐常问)

• 主从复制是咋实现的？G
• 从库是越多越好吗？G
• 主从复制有哪些模型？/如何保持主从间的数据一致性？G
• 分库、分表的方式有哪些？G
• 水平分片有哪些规则？G

MySQL 数据类型

• MySQL有哪些数据类型？
• varchar 与 char 的区别？int 和 int (11) 区别？tinyint 与 int 区别？
• MySQL 的 NULL 值是怎么存放的？会占用空间吗？
• MySQL 怎么知道 varchar(n) 实际占用数据的大小？
• varchar(n) 中 n 最大取值为多少？
• 行溢出后，MySQL 是怎么处理的？

面试问的

• 执行MySQL语句特别慢，可能的原因？G
• drop 和 delete 有啥区别？G
• count(1)、count(*)、count(字段)，哪个效率最高？G⭐🚩

一、事务

1.1 概念及原理

问：什么是事务？

事务是一组操作的集合，是一个不可分割的单位，事务中包含若干操作，这些操作要么都成功，要么都失败。

1
2
3
4
5
6

# 开启一个事务
START TRANSACTION;
# 多条 SQL 语句
SQL1,SQL2...
# 提交事务
COMMIT;

问：事务的四大特性？ACID 是什么？

原子性（Atomicity）：事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；

一致性⭐（Consistency）：执行事务前后，数据保持一致。例如转账业务中，无论事务是否成功，转账者和收款人的总额应该是不变的；

隔离性（Isolation）：并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；

持久性（Durability）：一个事务被提交之后，它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

注：只有保证了事务的持久性、原子性、隔离性之后，一致性才能得到保障。也就是说 A、I、D 是手段，C 是目的！

问：MySQL中，各有什么机制保证以上四个属性？

• 原子性、一致性、持久性：redo log、undo log；
• 隔离性：锁、MVCC。

问：持久性是怎么保证的？

redo log。

1.2 并发事务

A、B同时对数据库进行事务操作时，会发生的问题。

问：并发事务会带来哪些问题？

• 脏读
• 不可重复读
• 幻读
• 丢失修改
• …

脏读：一个事务读到了另一个事务还未commit的数据；

不可重复读：一个事务中先后读取同一条数据，期间另一个事务修改了该数据，造成第一个事务先后读取到的数据不一致。即，一个事务中前后两次读到的数据不一致；

幻读：一个事务查询某条数据时，发现没有对应行，但之后另一个事务插入了该条数据，当第一个事务准备插入该条数据时，又发现这行数据已存在。即，前后读取的记录数量不一致，就像出现了幻觉一样；

丢失修改：第一个事务修改了某条数据，第二个事务随后又修改了某条数据，造成第一个事务的修改结果丢失。

问：不可重复读和幻读的区别？

• 不可重复读的重点是内容修改或者记录减少比如多次读取一条记录发现其中某些记录的值被修改；
• 幻读的重点在于记录新增比如多次执行同一条查询语句（DQL）时，发现查到的记录增加了。

幻读其实可以看作是不可重复读的一种特殊情况，单独把区分幻读的原因主要是解决幻读和不可重复读的方案不一样。

**举个例子：**执行 delete 和 update 操作的时候，可以直接对记录加锁，保证事务安全。而执行 insert 操作的时候，由于记录锁（Record Lock）只能锁住已经存在的记录，为了避免插入新记录，需要依赖间隙锁（Gap Lock）。也就是说执行 insert 操作的时候需要依赖 Next-Key Lock（Record Lock+Gap Lock） 进行加锁来保证不出现幻读。

问：并发事务的控制方式有哪些？

MySQL 中并发事务的控制方式无非就两种：锁 和 MVCC。锁可以看作是悲观控制的模式，多版本并发控制（MVCC，Multiversion concurrency control）可以看作是乐观控制的模式。

锁 控制方式下会通过锁来显示控制共享资源而不是通过调度手段，MySQL 中主要是通过 读写锁 来实现并发控制。

• 共享锁（S 锁）：又称读锁，事务在读取记录的时候获取共享锁，允许多个事务同时获取（锁兼容）。
• 排他锁（X 锁）：又称写锁/独占锁，事务在修改记录的时候获取排他锁，不允许多个事务同时获取。如果一个记录已经被加了排他锁，那其他事务不能再对这条事务加任何类型的锁（锁不兼容）。

读写锁可以做到读读并行，但是无法做到写读、写写并行。另外，根据根据锁粒度的不同，又被分为 表级锁（table-level locking） 和 行级锁（row-level locking） 。InnoDB 不光支持表级锁，还支持行级锁，默认为行级锁。行级锁的粒度更小，仅对相关的记录上锁即可（对一行或者多行记录加锁），所以对于并发写入操作来说，InnoDB 的性能更高。不论是表级锁还是行级锁，都存在共享锁（Share Lock，S 锁）和排他锁（Exclusive Lock，X 锁）这两类。

MVCC 是多版本并发控制方法，即对一份数据会存储多个版本，通过事务的可见性来保证事务能看到自己应该看到的版本。通常会有一个全局的版本分配器来为每一行数据设置版本号，版本号是唯一的。

MVCC 在 MySQL 中实现所依赖的手段主要是：隐藏字段、read view、undo log。

• undo log：undo log 用于记录某行数据的多个版本的数据。
• read view 和 隐藏字段：用来判断当前版本数据的可见性。

1.3 事务隔离级别

问：都有哪些事务隔离级别？

SQL 标准定义了四个隔离级别：

• READ-UNCOMMITTED(读取未提交)：最低的隔离级别，允许读取尚未提交的数据变更。可能会导致脏读、幻读或不可重复读。
• READ-COMMITTED(读取已提交)：允许读取并发事务已经提交的数据。可以阻止脏读，但是幻读或不可重复读仍有可能发生。
• REPEATABLE-READ(可重复读)：对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改。可以阻止脏读和不可重复读，但幻读仍有可能发生。MySQL InnoDB 引擎的==默认隔离级别==；
• SERIALIZABLE(可串行化)：最高的隔离级别，完全服从 ACID 的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰。也就是说，该级别可以防止脏读、不可重复读以及幻读。

问：可重复读下，如何避免幻读？

首先要明确，使用「串行化」隔离级别才能彻底解决幻读现象，**==可重复读==只能在==很大程度上==**避免幻读(并不是完全解决了，详见这篇文章)。

可重复读的解决方案分为两方面：

• 针对快照读（普通 select 语句），是通过 MVCC 方式解决了幻读。因为可重复读隔离级别下，事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，即使中途有其他事务插入了一条数据，是查询不出来这条数据的，所以就很好了避免幻读问题。
• 针对当前读（select … for update 等语句），是通过 next-key lock（记录锁+间隙锁）方式解决了幻读。因为当执行 select … for update 语句的时候，会加上 next-key lock，如果有其他事务在 next-key lock 锁范围内插入了一条记录，那么这个插入语句就会被阻塞，无法成功插入，所以就很好了避免幻读问题。

以下说明：为啥可重复读没完全解决幻读？

示例场景1：

• 事务 A 执行查询 id = 5 的记录，此时表中是没有该记录的，所以查询不出来。注意，此时并未提交事务！

1
2
3
4
5
6

# 事务 A
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t_stu where id = 5;
Empty set (0.01 sec)

• 然后事务 B 插入一条 id = 5 的记录，并且提交了事务。

1
2
3
4
5
6
7
8
9

# 事务 B
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t_stu values(5, '小美', 18);
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

• 此时，事务 A 更新 id = 5 这条记录；

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

# 事务 A
mysql> update t_stu set name = '小林coding' where id = 5;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t_stu where id = 5;
+----+--------------+------+
| id | name         | age  |
+----+--------------+------+
|  5 | 小林coding   |   18 |
+----+--------------+------+
1 row in set (0.00 sec)

• 然后再次查询 id = 5 的记录，事务 A 就能看到事务 B 插入的记录了，且被自己更新过。这就产生了幻读。

整个过程的时序图：

• 事务 A 开始时，会创建一个 Read View；
• 然后，事务 B 新插入了一条记录；
• 接着，事务 A 通过 update 操作对该记录进行更新，此时这条新记录的**trx_id就变成了事务 A 的事务 id**；
• 之后事务 A 再使用普通 select 语句去查询这条记录时就可以看到这条记录了，于是就发生了幻读。

示例场景2：

• T1 时刻：事务 A 先执行「快照读语句」：select * from t_test where id > 100 得到了 3 条记录。
• T2 时刻：事务 B 往插入一个 id= 200 的记录并提交；
• T3 时刻：事务 A 再执行「当前读语句」 select * from t_test where id > 100 for update 就会得到 4 条记录，此时也发生了幻读现象。

场景2可以通过在开启事务之后，马上执行 select … for update 这类当前读的语句来避免，因为它会对记录加 next-key lock，从而避免其他事务插入一条新记录。

问：可重复读的实现原理？⭐

可重复读隔离级别是==启动事务时==生成一个 Read View，然后整个事务期间都在用这个 Read View。

例，假设事务 A （事务 id 为51）启动后，紧接着事务 B （事务 id 为52）也启动了，那这两个事务创建的 Read View 如下：

接着，在可重复读隔离级别下，事务 A 和事务 B 按顺序执行了以下操作：

• 事务 B 读取小林的账户余额记录，读到余额是 100 万；
• 事务 A 将小林的账户余额记录修改成 200 万，并没有提交事务；
• 事务 B 读取小林的账户余额记录，读到余额还是 100 万；
• 事务 A 提交事务；
• 事务 B 读取小林的账户余额记录，读到余额依然还是 100 万；

具体分析下这个过程：

• 事务 B 第一次读取时，发现该记录的trx_id < 事务 B 的 Read View 中的min_trx_id，所以该记录的该版本可见；
• 接着，事务 A 修改该记录(但还没提交事务)，此时 MySQL 会记录 undo log，以链表方式串联起来，形成版本链；

• 然后，事务 B 第二次读取，发现该记录的trx_id 在min_trx_id和max_trx_id之间，且在m_ids中存在该记录的trx_id，说明该记录是被还未提交的事务修改的，因此该版本不可见，就会顺着 undo log 链向旧版本寻找，直到某个版本的trx_id < min_trx_id 或 trx_id > min_trx_id 且不存在m_ids中。此处表现为读到了旧数据；
• 接着，事务 A 提交该事务；
• 但由于**「可重复读」，事务 B 第三次读取该记录时，还是基于启动事务时创建的Read View来判断当前版本**的记录是否可见，因此依然判定该记录为未提交，因此读到的依然是旧数据。

问：读已提交的实现原理？

读提交隔离级别是在**==每次读取数据时==，都会生成一个新的 Read View**。

还是之前那个例子，假设事务 A （事务 id 为51）启动后，紧接着事务 B （事务 id 为52）也启动了，接着按顺序执行了以下操作：

• 事务 B 读取数据（创建 Read View），小林的账户余额为 100 万；
• 事务 A 修改数据（还没提交事务），将小林的账户余额从 100 万修改成了 200 万；
• 事务 B 读取数据（创建 Read View），小林的账户余额为 100 万；
• 事务 A 提交事务；
• 事务 B 读取数据（创建 Read View），小林的账户余额为 200 万；

区别就在于事务 B 第三次读取数据时，会新建 Read View：

注意，此时m_ids中只有52了，事务 B 查询该记录时，trx_id=51，小于min_trx_id，所以该记录的该版本对事务 B 可见。

二、存储引擎

5.5.5 版本之后，InnoDB 是 MySQL 的默认存储引擎，且所有的存储引擎中只有 InnoDB 是事务性存储引擎，也就是说只有 InnoDB 支持事务。

1
2

-- 查询当前数据库支持的数据库引擎
show engines;

问：MySQL存储引擎架构？

MySQL 存储引擎采用的是 插件式架构 ，支持多种存储引擎，我们甚至可以为不同的数据库表设置不同的存储引擎以适应不同场景的需要。存储引擎是基于表的，而不是数据库。

还可以根据 MySQL 定义的存储引擎实现标准接口来编写一个属于自己的存储引擎。(像目前最常用的 InnoDB 其实刚开始就是一个第三方存储引擎，后面由于过于优秀，其被 Oracle 直接收购了。)

问：MyISAM 和 InnoDB 有何区别？

1. 是否支持行级锁

MyISAM 只有表级锁(table-level locking)，而 InnoDB 支持行级锁(row-level locking)和表级锁,默认为行级锁。也就是说，MyISAM 一锁就是锁住了整张表，并发时性能远不如 InnoDB。

2. 是否支持事务

MyISAM 不提供事务支持。

InnoDB 提供事务支持，实现了 SQL 标准定义了四个隔离级别，具有提交(commit)和回滚(rollback)事务的能力。并且，InnoDB 默认使用的 REPEATABLE-READ（可重复读）隔离级别是可以解决幻读问题发生的（基于 MVCC 和 Next-Key Lock）。

3. 是否支持外键

MyISAM 不支持，而 InnoDB 支持。

外键对于维护数据一致性非常有帮助，但是对性能有一定的损耗。因此，通常情况下，我们是不建议在实际生产项目中使用外键的，在业务代码中进行约束即可！

4. 是否支持数据库异常崩溃后的安全恢复

MyISAM 不支持，而 InnoDB 支持。

使用 InnoDB 的数据库在异常崩溃后，数据库重新启动的时候会保证数据库恢复到崩溃前的状态。这个恢复的过程依赖于 redo log 。

5. 是否支持 MVCC

MyISAM 不支持，而 InnoDB 支持。

MyISAM 连行级锁都不支持，而 MVCC 可以看作是行级锁的一个升级，可以有效减少加锁操作，提高性能。

6. 索引的实现不同

二者均采用B+Tree作为索引结构，但是两者的实现方式不太一样。

InnoDB 引擎中，其数据文件本身就是索引文件，支持聚簇索引。

而 MyISAM，索引文件和数据文件是分离的，其表数据文件本身就是按B+Tree组织的一个索引结构，树的叶节点 data 域保存了完整的数据记录，在索引检索的时候，首先按照 B+Tree 搜索算法搜索索引，如果指定的 Key 存在，则取出其 data 域的值，然后以 data 域的值为地址读取相应的数据记录。这被称为“非聚簇索引（非聚集索引）”。

详细需要看索引。

7. 性能差别

InnoDB 的性能比 MyISAM 更强大，不管是在读写混合模式下还是只读模式下，随着 CPU 核数的增加，InnoDB 的读写能力呈线性增长。MyISAM 因为读写不能并发，它的处理能力跟核数没关系。

问：如何选择 MyISAM 和 InnoDB、MEMORY？

• InnoDB⭐：如果应用对事务的完整性有较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询外，还包含很多更新、删除的操作，那么选择 InnoDB。
• MyISAM：如果应用以插入和查询操作为主，很少更新和删除，并且对事务的完整性、并发性要求不高，也不需要崩溃后安全恢复的话，那么也凑合能用。(现在被 MongoDB 替代了)
• MEMORY：通常用于临时表及缓存，将所有数据保存在内存，访问速度快。缺点就是对表的大小有限制，且安全性无法保障。(现在被 Redis 替代了)

绝大部分情况下选择 InnoDB 都是没有问题的。

三、索引⭐

3.1 索引结构

索引是帮助 MySQL 高效获取数据(快速查询和检索数据)的数据结构(有序)。

常见的索引结构有: B 树(也称 B- 树)，B+ 树 和 Hash、红黑树。在 MySQL 中，无论是 Innodb 还是 MyIsam，都使用了 B+ 树 作为索引结构。大部分都支持 B+ 树 索引。

InnoDB 的数据是按==「数据页」(16KB)==为单位来读写的，也就是说，当需要读一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存。

问：B-树 和 B+树 有何异同？

• B-树 的所有节点既存放键(key)也存放数据(data)，而 B+树 只有叶子节点存放 key 和 data，其他节点只存放 key；
• B-树 的叶子节点都是独立的；B+树 的叶子节点之间构成一个有序链表；
• B+树 的非叶子节点的索引也会同时存在在子节点中，并且是在子节点中所有索引的最大（或最小），这样虽然有大量的冗余节点，这样使得删除一个节点的时候，可以直接从叶子节点中删除，甚至可以不动非叶子节点。
• B-树 的检索可能还没有到达叶子节点，检索就结束了，查询波动比较大；而 B+树 的检索效率就很稳定了，任何查找都是从根节点到叶子节点的过程。

问：B+ Tree 相比于 B- Tree 的优点？

• **范围查询效率更高。**因为 B+ 树所有叶子节点间有一个链表进行连接，只需要查到头节点，然后往后遍历即可。但 B- 树就只能通过一个一个查找完成。
• **插入和删除效率更高。**因为 B+ 树有冗余节点，插入和删除时，树的结构不用怎么变化。
• 查询效率也更高一点。因为I/O操作会少一点嘛。

问：Hash 索引相比于 B+ 树的优缺点？

• Hash索引自身只需要存储对应的哈希值，所以索引内存结构非常紧凑，查询效率很高；

• 但由于是通过哈希映射进行索引，因此无法排序或进行范围查询。

问：B+ Tree 的特点？

• 只有叶子节点（最底层的节点）才存放了数据，非叶子节点（其他上层节）仅用来存放目录项作为索引。
• 非叶子节点分为不同层次，通过分层来降低每一层的搜索量；
• 所有节点按照索引键大小排序，构成一个双向链表，便于范围查询；

问：B+ Tree 索引一个元素的过程？

B+ 树如何实现快速查找主键为 6 的记录：

• 从根节点开始，通过二分法快速定位到符合页内范围包含查询值的页，因为查询的主键值为 6，在[1, 7)范围之间，所以到页 30 中查找更详细的目录项；
• 在非叶子节点（页30）中，继续通过二分法快速定位到符合页内范围包含查询值的页，主键值大于 5，所以就到叶子节点（页16）查找记录；
• 接着，在叶子节点（页16）中，通过槽查找记录时，使用二分法快速定位要查询的记录在哪个槽（哪个记录分组），定位到槽后，再遍历槽内的所有记录，找到主键为 6 的记录。

问：为什么 InnoDB 存储引擎选用 B+Tree 索引结构？

1. 相比于红黑树等二叉树结构，B+Tree 层级更少，因此搜索效率更高(因为磁盘I/O操作更少)；
2. 相比于 B-Tree ，因为 B-Tree 无论叶子节点还是非叶子节点，都会保存数据，这样会导致一页中存储的键值减少，指针也跟着减少，要保存同样的数据，只能增加树的高度，导致性能下降；其实我觉得，主要是**==减少了调页的I/O操作次数==**。
3. 相比于 Hash索引 ，B+Tree 支持范围匹配及排序操作，比如SELECT * FROM tb1 WHERE id < 500;，如果用 Hash索引，难道要把1-499都挨个hash定位吗？

注：

其实一开始我很不理解为啥 ==“1. 层级减少，搜索效率会变高”==，因为你就算层级变少了，但是节点内的键值对变多了啊，搜索的时候还是用二分，效率并没有减少啊！

后来，我知道了每次访问一个节点，其实就是需要一次调页操作的，也就是一次磁盘I/O，而单个节点内的键值对变多，显然会减少调页次数，也就提高了效率。

问：B+ 树不同高度的数据存储？

假设 B+ 树的高度为 2 的话，即有一个根结点和若干个叶子结点。这棵 B+ 树的存放总记录数为 = 根结点指针数 * 单个叶子节点记录行数。

如果一行记录的数据大小为 1k，那么单个叶子节点可以存的记录数 = 16k/1k = 16. 非叶子节点内存放多少指针呢？我们假设主键ID为 bigint 类型，长度为8字节(面试官问你int类型，一个int就是32位，4字节)，而指针大小在InnoDB源码中设置为6字节，所以就是 8+6 = 14 字节，16KB/14B = 16*1024B/14B = 1170

因此，一棵高度为2的B+树，能存放1170 * 16=18720条这样的数据记录。同理一棵高度为 3 的 B+ 树，能存放 1170 *1170 * 16 = 21902400，大概可以存放两千万左右的记录。B+ 树高度一般为 1-3 层，如果 B+ 到了 4 层，查询的时候会多查磁盘的次数，SQL 就会变慢。

3.2 索引类型

在 InnoDB 存储引擎中，根据索引的存储形式，又可以分为以下两种：

比如：select * from user where name='Tom'，会先去查找二级索引name，找到对应的主键id值，然后回表查询通过聚集索引拿到对应的行数据。

思考：以下 SQL语句，哪个执行效率高？为什么？

1 2 3

select * from user where id = 10; select * from user where name = 'Arm'; -- 备注：id为主键，name字段创建的有索引。

1显然效率更高。

问：索引类型？

索引从数据结构进行划分的分为：B+树索引、B-树索引、Hash索引、二叉树索引。

索引从物理存储的角度划分为：聚族索引和非聚族索引。

从逻辑的角度分为：主键索引、普通索引、唯一索引、联合索引。

问：InnoDB 主键索引的 B+Tree 高度有多高？

黑马视频。

问：聚集索引选取规则？

聚集索引有且仅有一个，它将索引与数据存储放到一起，索引结构的叶子节点中保存了行数据。

• 如果存在主键，主键索引就是聚集索引；
• 如果不存在主键，第一个唯一索引就是聚集索引；
• 如果二者都无，则 InnoDB 会自动生成一个 隐式自增的id 作为隐藏的聚集索引。

问：SQL性能分析方法？

1. SQL 执行频次
2. 慢查询日志
3. profiles详情
4. explain性能分析，可以查看执行计划

3.3 索引使用

索引法则

**单列索引：**一个索引值包含单个列

**联合索引：**一个索引包含了多个列

问：索引失效的情况？即，使用索引时需要的注意事项？⭐⭐🚩

1. 最左前缀法则

如果索引了多列(联合索引)，要遵守最左前缀法则。最左前缀法则指的是查询条件从索引的最左列开始，并且不跳过索引中的列。如果跳过某一列，索引将部分失效(后面的字段索引失效)。

如果创建了一个 (a, b, c) 联合索引：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

# 不会失效
where a=1；
where a=1 and b=2 and c=3；
where b=2 and a=1 and c=3;	# 查询优化器会自动进行排序
where a=1 and b=2；
# 部分生效
where a=1 and c=3
# 会失效
where b=2；
where c=3；
where b=2 and c=3；

其实联合索引(a, b, c)，是先按 a 排序，在 a 相同的情况再按 b 排序，在 b 相同的情况再按 c 排序。所以，b 和 c 是全局无序，局部相对有序的。

有一个比较特殊的查询条件：where a = 1 and c = 3 ，符合最左匹配吗？

严格意义上来说是属于索引截断，不同版本处理方式也不一样：

• MySQL 5.5 的话，前面 a 会走索引，在联合索引找到主键值后，开始回表，到主键索引读取数据行，Server 层从存储引擎层获取到数据行后，然后在 Server 层再比对 c 字段的值；
• 从 MySQL 5.6 之后，有一个索引下推功能，可以在存储引擎层进行索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，再返还给 Server 层，从而减少回表次数。

索引下推的基本原理：截断的字段不会在 Server 层进行条件判断，而是会被下推到「存储引擎层」进行条件判断（因为 c 字段的值是在 (a, b, c) 联合索引里的），然后过滤出符合条件的数据后再返回给 Server 层。由于在引擎层就过滤掉大量的数据，无需再回表读取数据来进行判断，减少回表次数，从而提升了性能。

因此，where a = 1 and c = 3 会部分用到索引，并且在 MySQL 5.6 后，还会用到索引下推。

详细

2. 范围查询

联合索引中，出现范围查询(>, <)，范围查询右侧的列索引失效。注意：若使用 (>=, <=)，则不会失效，因此建议使用 (>=, <=)。

3. **字符串不加引号：**字符串类型字段使用时，若索引值不加引号，索引将失效。

1
2

explain select * from user where phone = '17799990015';	-- 不失效
explain select * from user where phone = 17799990015;	-- 失效

4. 模糊查询：如果仅仅是尾部模糊匹配，索引不会失效；如果是头部模糊匹配，索引失效。因为排序时是按字典序。

1
2

explain select * from user where prefession like '软件%'	-- 不失效
explain select * from user where prefession like '%工程'  -- 失效

5. **or连接的条件：**用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。即，只有or两边的条件均有索引，整体才会生效，否则，均不生效。

6. **数据分布影响：**如果 MySQL 评估使用索引比全表查询更慢，则不使用索引。

1
2
3

-- 数据范围：00-20
explain select * from user where phone = '17799990005';	-- 全表查询
explain select * from user where phone = '17799990015';	-- 索引查询

7. 对索引进行表达式计算：因为索引保存的是索引字段的原始值，而不是 id + 1 表达式计算后的值，所以无法走索引，只能通过把索引字段的取值都取出来，然后依次进行表达式的计算来进行条件判断，因此采用的就是全表扫描的方式。

1
2

explain select * from t_user where id + 1 = 10; # 索引失效
							 where id = 10 - 1; # 索引不失效

建议参考：索引失效有哪些？

问：如何查看 MySQL 是否用到了索引？🚩

在查询语句前加上 explain 关键字。

问：优化数据库查询操作的做法？即 优化索引的方法？

• 前缀索引优化；
• 覆盖索引优化；
• 主键索引最好是自增的；
• 防止索引失效

1. 使用前缀索引是为了减小索引字段大小，可以增加一个索引页中存储的索引值数目；
2. 尽量使用覆盖索引，避免使用select *，会大量造成回表查询，原因见下。
3. 主键索引最好是自增的，这样的话，每次插入一条新记录，都是追加操作，不需要重新移动数据；
4. 索引时，要避免索引失效。

覆盖索引、回表查询⭐🚩

如果一个索引覆盖所有需要查询的字段的值，就称之为**覆盖索引**。我们知道在 InnoDB 存储引擎中，如果不是主键索引，叶子节点存储的是主键+列值。最终还是要“回表”，也就是要通过主键再查找一次，这样就会比较慢。

**覆盖索引，即需要查询的字段正好是索引中的字段，那么直接根据该索引，就可以查到数据了，而无需回表查询。**因此，尽量避免使用select *，除非你联合索引的条件很多很多。

问：什么是回表查询？什么时候会触发回表查询？

回表查询是指在查询过程中，如果需要获取的数据不在查询语句的索引列中，那么就需要通过回表操作来获取额外的数据。回表操作会根据查询语句中的主键或唯一索引定位行数据，然后再根据行数据中的其他列获取需要的数据。

例：《黑马》P84 讲的很好！

如主键索引，如果一条 SQL 需要查询主键，那么正好根据主键索引就可以查到主键。

再如普通索引，如果一条 SQL 需要查询 name，name 字段正好有索引， 那么直接根据这个索引就可以查到数据，也无需回表。

问：联合索引应用场景？为什么要建联合索引？⭐🚩

• 多条件联合查询：当查询语句中需要同时使用多个列进行查询时，可以使用联合索引来提高查询效率。
• 减少开销：建一个联合索引(a, b, c)，实际相当于建了(a)，(a, b)，(a, b, c)三个索引。每多一个索引，都会增加写操作的开销和磁盘空间的开销。对于大量数据的表，使用联合索引会大大的减少开销！
• 覆盖索引：当查询语句只需要查询索引列和联合索引中的列时，可以使用联合索引来避免回表操作，从而提高查询效率。
• 排序操作：当查询语句需要对多个列进行频繁排序时，可以使用联合索引来提高排序效率。例如，一个学生表包含了学生ID、姓名、年龄、成绩等列，如果需要按照成绩和年龄进行排序，可以使用联合索引（成绩，年龄）来提高排序效率。

问：一张表，有四个字段(id, username, password, status)，由于数据量大，需要对以下 SQL 语句进行优化，该如何进行才是最优方案？

1	select id, username, password from user where username='itcast';

**需要建立索引：**针对 username 和 password 建立联合索引，这样的话，查到username=‘itcast’时，val值为id，username和password也知道，直接返回即可，无需回表查询。

前缀索引

问：前缀索引是什么？

字段类型为字符串时，索引很长的字符串，会让索引变得很大，导致一个索引页中的索引数量变少，层级就会变高，导致查询时，浪费大量的磁盘IO，影响查询效率。

前缀索引就是只对字符串的一部分前缀，建立索引，可以减小索引字段大小，增加一个索引页中存储的索引数量，有效提高索引的查询速度。

1

create index idx_xxx on table_name(column(n))	# column(n)表示列column只取前n个字符构建索引

不过，前缀索引有一定的局限性，例如：

• order by、group by 无法使用前缀索引；
• 由于只存储了部分前缀，所以也无法把前缀索引用作覆盖索引(覆盖索引需要全部的数据)。

索引下推

问：什么是索引下推？

索引下推（Index Condition Pushdown） 是 MySQL 5.6 版本中提供的一项索引优化功能，可以在非聚簇索引遍历过程中，对索引中包含的字段在引擎层先做判断，过滤掉不符合条件的记录，减少回表次数。

索引下推并不适用于所有查询，只有在查询条件中包含索引字段和非索引字段的情况下才能发挥作用。

核心思想就是引擎层利用索引字段先过滤一次，然后判断非索引字段是否匹配，然后再返回给server层进行回表查询拿到整行数据。

索引设计原则

问：索引设计原则有哪些？

1. 针对数据量大、查询频繁的表建立索引；

2. 针对常作为查询条件(where)、排序(order by)、分组(group by)操作的字段建立索引；

3. 尽量建立唯一索引；

4. 针对字段较长的字符串类型的字段，建立前缀索引；

5. 尽量使用联合索引，节省存储空间(一个联合索引比多个单列索引要省空间。因为如果是联合索引，多个字段在一个索引上，那么将会节约很大磁盘空间，且修改数据的操作效率也会提升。)，查询时可以使用覆盖索引，提高查询效率(避免回表)；

6. 索引字段尽量避免为null，可改用0、1、true、false代替；

7. 限制每张表的索引数量。索引太多不仅会降低插入和更新的效率，还有可能降低查询性能！这是因为 MySQL 优化器在选择如何优化查询时，会根据统一信息，对每一个可以用到的索引来进行评估，以生成出一个最好的执行计划，如果同时有很多个索引都可以用于查询，就会增加 MySQL 优化器生成执行计划的时间，同样会降低查询性能。

8. 被频繁更新的字段应该慎重建立索引。虽然索引能带来查询上的效率，但是维护索引的成本也是不小的。 如果一个字段不被经常查询，反而被经常修改，那么就更不应该在这种字段上建立索引了。

9. 字段中存在大量重复数据，不需要创建索引，比如性别字段，只有男女，如果数据库表中，男女的记录分布均匀，那么无论搜索哪个值都可能得到一半的数据。在这些情况下，还不如不要索引，因为 MySQL 还有一个查询优化器，查询优化器发现某个值出现在表的数据行中的百分比很高的时候，它一般会忽略索引，进行全表扫描。

问：索引越多越好吗？

No.

1. 索引需要占用磁盘空间：索引是一种数据结构，需要占用磁盘空间。如果表的大小很大，并且有很多索引，那么索引可能会占用大量的磁盘空间，增加了存储成本。
2. 索引需要维护：每次对表中的数据进行插入、更新或删除操作时，MySQL都需要更新索引。这会增加写操作的开销，降低写操作的性能。
3. 索引可能会降低查询性能：虽然索引可以加快查询速度，但如果索引覆盖的列很少，或者查询条件不够特定，那么使用索引可能会降低查询性能。因为MySQL可能需要扫描大量的索引来找到符合条件的数据，而这种扫描操作可能比全表扫描更耗时。
4. 索引可能会导致锁竞争：当多个用户同时查询或修改同一个表时，索引可能会导致锁竞争，从而影响并发性能。

问：uuid 和 自增id 做索引有什么区别？

• 自增id 占用的空间更小，比如long，int型；uuid 是32位的字符串，占用空间就很大了；
• 主键占用空间越大，B+ 树的高度可能就会更高，导致 uuid 方案的 I/O 操作变多，效率降低；
• uuid 是无序的，导致插入新数据时，B+ 树调整的频次更多，插入效率就降低了；
• 自增 id 是有序的，缺点就是容易泄露数据量，以及多个表合并时、分布式架构时，id 容易冲突。

四、SQL优化

其实2-7都是跟索引相关。

4.1 插入数据

• insert优化：
  • 批量插入，若特别大量(百万级)，建议使用load指令而非insert；
  • 手动提交事务；
  • 主键顺序插入，因为乱序插入可能会发生页分裂和页合并。

4.2 主键优化

• 尽量降低主键的长度；
• 插入数据时，尽量选择顺序插入，选择使用auto_increment自增主键；
• 尽量不要使用UUID做主键或是其他自然主键，如身份证号，因为这些主键不是顺序的，插入时相当于乱序，且这些主键较长；
• 尽量避免对主键的修改。

4.3 order by 优化

using index：直接通过索引返回数据，性能高；

using filesort：需要将返回的结果在排序缓冲区重新排序。

• 对order by字段建立索引，默认是升序排列，查询时需要按照建立索引时指定的排列顺序，否则还是会额外的排序(filesort)；
• 也要遵循最左前缀法则，尽量使用覆盖索引；
• 不可避免出现filesort时，大数据排序时，可以适当增大排序缓冲区大小。

4.4 group by 优化

• 对group by字段建立索引，避免using temporary；
• 也要遵循最左前缀法则。

4.5 limit 优化

• 覆盖索引+子查询

4.6 count 优化

MyISAM 是把count结果记录在磁盘上，需要的时候直接读取。

InnoDB 需要一条一条读取数据，再累计计数，没有什么好的优化方法。

用法：

• count(主键)：遍历整张表，把每一行的主键id(不可能为null)的值都取出，然后进行累加；
• count(字段)：
  • 没有not null约束：遍历整张表，取出每行的值，服务层判断是否为null，不为null的累加；
  • 有not null约束：遍历整张表，取出每行的值，直接累加。
• **count(1)：**InnoDB 遍历整张表，不取值，对每一行放一个1，直接按行累加。
• **count(*)：**等价于 count(0)，InnoDB 并不把所有字段取出，专门做了优化，不取值，直接按行累加。(使用 count(*) 时，MySQL 会将 * 参数转化为参数 0 来处理)。

问：count() 是什么？

count() 是一个聚合函数，函数的参数不仅可以是字段名，也可以是其他任意表达式，该函数作用是统计符合查询条件的记录中，函数指定的参数不为 NULL 的记录有多少个。

假设 count() 函数的参数是字段名，如下：

1

select count(name) from t_order;

这条语句是统计「t_order 表中，name 字段不为 NULL 的记录」有多少个。也就是说，如果某一条记录中的 name 字段的值为 NULL，则就不会被统计进去。

再来假设 count() 函数的参数是数字 1 这个表达式，如下：

1

select count(1) from t_order;

这条语句是统计「 t_order 表中，1 这个表达式不为 NULL 的记录」有多少个。

1 这个表达式就是单纯数字，它永远都不是 NULL，所以上面这条语句，其实是在统计 t_order 表中有多少个记录。

问：count(1)、count(*)、count(字段)，哪个效率最高？⭐🚩

**==效率排序：==**count(字段) < counts(索引字段) < count(1) ≈ count(*)，所以尽量选择count(*)。

其实，尽量不要使用count来统计，可以使用：

• show table status 估算；
• 单独建个表，用来计数

4.7 update 优化

• update 更新时，更新条件要按照索引，这样加的才是行锁，否则是表锁，会降低数据库的并行性能。

五、视图 & 存储过程 & 触发器（未）

5.1 视图

视图是一种虚拟存在的表。视图只保存了查询的 SQL 逻辑，不保存查询结果。所以在创建视图的时候，主要工作就落在创建这条 SQL 查询语句上。

1
2
3
4
5
6
7
8

# 创建视图
create or replace view stu_v_1 as select id, name from student where id <= 10;

# 查询视图，此时就会输出id, name 两列的数据，即简化了SQL查询语句
select * from stu_v_1;

# 带检查选项的创建，此时向视图插入 id>10的就会报错
create or replace view stu_v_1 as select id, name from student where id <= 10 with local check option;

视图的作用

• **简化操作。**无需每次都指定全部条件。

• **安全。**通过视图，用户只能查询和修改他们所见到的数据。

• **数据独立。**屏蔽基表变化对业务的影响。

5.2 存储过程

语法

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

# 创建 无返回值
create procedure 过程名
begin
	sql语句;
end;

# 调用
call 过程名;

# 查看
select * from information_schema.ROUTINES where ROUTINE_SCHEMA = 'itcast';
show create procedure 过程名;
# 删除
drop procedure if exists 过程名;

5.3 存储函数

就是有返回值的存储过程。

六、锁

锁是协调多个进程或线程并发访问某一资源的机制。

• **全局锁：**锁定数据库中的所有表；（粒度最大的锁）
• **表级锁：**每次操作锁住整张表；针对非索引字段；
• **行级锁：**每次操作锁住对应的行数据；针对索引字段；（粒度最小的锁）

6.1 全局锁

问：全局锁的应用场景？

全局锁是对整个数据库实例加锁，加锁后数据库变成只读状态(DQL可执行)，DML、DDL语句都会被阻塞。典型的使用场景是做全库的逻辑备份，对所有的表进行锁定，从而获得一致性视图，保证数据的完整性。

1
2

flush tables with read lock;
unlock tables;

但是加全局锁是个非常重的操作：

• 若在主库上备份，那么业务就得停摆；
• 若在从库上备份，那么备份期间从库不能执行主库同步，会导致主从延迟。

在 InnoDB 上备份时，加上参数–single-transaction参数可以完成不加锁的一致性数据备份。这种方式实际上是通过快照实现的。

6.2 表级锁

• **表共享锁(read lock)：**大家(包括自己)都可以读，但都不可以写
• **表排他锁(write lock)：**自己可以读写，别人不可以读写

1
2
3
4
5

# 加锁
lock tables 表名... read/write

# 释放锁
unlock tables /客户端断开连接

• 元数据锁(meta data lock，MDL)：MDL加锁过程是系统自动控制，无需显式使用，在访问一张表的时候会自动加上。MDL锁主要作用是维护表元数据的数据一致性，在表上有活动事务时，不可以对元数据进行写入操作。

对表进行增删改查时，加MDL读锁(共享锁)；对表结构进行变更操作时，加MDL写锁(排他锁)。

• 意向锁⭐：为了避免DML在执行时，加的行锁与表锁的冲突，在InnoDB中引入了意向锁，使表锁不用检查每行数据是否已被加锁，==使用意向锁来减少表锁对行锁的检查==。
  • **意向共享锁(IS)：**与表共享锁(read)兼容，与表排他锁(write)互斥；
  • **意向排他锁(IX)：**与表共享锁(read)及表排他锁(write)都互斥。

意向锁是有数据引擎自己维护的，用户无法手动操作意向锁，在为数据行加共享/排他锁之前，InnoDB 会先获取该数据行所在在数据表的对应意向锁。

==意向锁之间不会互斥。==

意向锁和共享锁和排它锁互斥（这里指的是表级别的共享锁和排他锁，意向锁不会与行级的共享锁和排他锁互斥）。

问：意向锁有什么作用？

使用表锁之前，如果已经有行锁存在，就会冲突，因此需要先检查是否有行锁，但是一行一行扫描的效率太低，此时就会用到意向锁。

在加行锁之后，会对表加一个意向锁，之后再加表锁时，只需检查是否有意向锁即可。当检查意向锁和要加的表锁是兼容的，那直接加，如果冲突，就会阻塞，直到意向锁释放。

例：事务A对表中某行进行修改，此时会自动加上行锁，同时还会对表加上意向排他锁，此时若事务B向表加锁(共享锁/排他锁)，都不能成功，因为有意向排他锁。

问：共享锁和排他锁？

• 共享锁（S 锁）：事务在读取时获得；允许多个事务同时获取（锁兼容）。
• 排他锁（X 锁）：事务在修改的时获得；不允许多个事务同时获取。如果一个记录已经被加了排他锁，那其他事务不能再对这条事务加任何类型的锁（锁不兼容）。只允许获得该锁的事务读写，不允许其他事务操作。

6.3 行级锁

分类：

• **记录锁：**锁定单行记录的锁，防止其他事务对该行进行update和delete。在RC、RR隔离级别下都支持。
• **间隙锁：**锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读。在RR隔离级别下支持。
• **临键锁⭐：**行锁和间隙锁的组合，锁住该行数据，及该行前的间隙。在RR隔离级别下支持。
• 插入意向锁⭐：如果某个间隙被间隙锁锁定，其他事务进行插入时就会阻塞，直到间隙锁释放，在此期间会生成一个插入意向锁，表明有事务想在某个区间插入新记录，但是现在处于等待状态。插入意向锁是特殊的间隙锁，不互斥。

间隙锁的唯一目的是：防止其他事务插入该间隙，解决幻读。间隙锁是共享锁，即允许多个事务在同一间隙上采用间隙锁。

问：临键锁的范围？

• 唯一索引的等值查询，数据存在时，会加行锁；
• 唯一索引的等值查询，数据不存在，会对查询条件主键所在的间隙加间隙锁；
• 普通索引的等值查询，数据存在时，会对该数据前后第一个不满足条件的两端之间的所有间隙加间隙锁，相同数据的行都加行锁，这是因为普通索引可能会插入相同的数据，所以这些间隙都得加锁；如果是范围查询，那么该范围内存在的数据的行都加行锁，其余区间都加间隙锁。

问：为什么会产生死锁？

关键点：

• Innodb 引擎为了解决**「可重复读」隔离级别下的幻读问题，引出了next-key 锁**，它是记录锁和间隙锁的组合。

• 行锁的释放时机是在事务提交（commit）后，锁才会被释放，并不是一条语句执行完就释放行锁。

例，表中数据范围[1001-1006]，一个事务要插入订单 1007 ，另外一个事务要插入订单 1008，要先查询该订单是否存在，不存在才插入记录：

• 为了防止幻读嘛，查询时(注意这不是普通的快照读！)，select ... for update 语句会加临键锁，此时退化成间隙锁，间隙锁与间隙锁之间是兼容的，因此此时A、B都获得了间隙锁；
• 插入时，insert语句会在插入间隙上获取插入意向锁，而==插入意向锁与间隙锁是冲突的==，获得插入意向锁后，需要等待间隙锁释放，此时A、B都获得了插入意向锁，但都等待彼此的间隙锁释放，造成死锁。
• 满足了死锁的四个条件：互斥、保持与请求、不可被抢占、循环等待，因此发生了死锁。

**注：**为什么间隙锁与间隙锁是兼容的？

间隙锁的意义只在于阻止区间被插入，因此是可以共存的。共享和排他的间隙锁是没有区别的，他们相互不冲突，且功能相同，即两个事务可以同时持有包含共同间隙的间隙锁。

==另外，==虽然相同范围的间隙锁是多个事务相互兼容的，但对于==记录锁==，还是要考虑 X 型与 S 型关系，X 型的记录锁与 X 型的记录锁是冲突的！！！

问：如何避免死锁？

死锁的四个必要条件：互斥、保持与请求、不可被抢占、循环等待。只要系统发生死锁，这些条件必然成立，但是只要破坏任意一个条件就死锁就不会成立。

在数据库层面，有两种策略通过**「打破循环等待条件」**来解除死锁状态：

• 设置事务等待锁的超时时间。当一个事务的等待时间超时，就对这个事务进行回滚，于是锁就释放了，另一个事务就可以继续执行了。
• 开启主动死锁检测。主动死锁检测在发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。

七、MVCC

7.1 概念

问：当前读和快照读有什么区别？

• **快照读：**一致性非锁定读，就是普通的select语句；
• **当前读：**一致性锁定读，会给行加X锁或S锁。

1
2
3
4
5
6
7
8

# 对读的记录加一个X锁
SELECT...FOR UPDATE
# 对读的记录加一个S锁
SELECT...LOCK IN SHARE MODE
# 对修改的记录加一个X锁
INSERT...
UPDATE...
DELETE...

快照读时，若读取的记录正在进行update/delete操作，快照读不会等待其X锁的释放，而是会去读取快照。只有在事务隔离级别 RC(读取已提交) 和 RR（可重读）下，InnoDB 才会使用快照读：

• 在 RC 级别下，对于快照数据，一致性非锁定读总是读取被锁定行的最新一份快照数据。
• 在 RR 级别下，对于快照数据，一致性非锁定读总是读取本事务开始时的行数据版本。

快照读比较适合对于数据一致性要求不是特别高且追求极致性能的业务场景。

当前读时，读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前事务，所以会加锁。

问：什么是MVCC？

**多版本并发控制。**指维护一个数据的多个版本，使得读写操作没有冲突，快照读为实现MVCC提供了一个非阻塞读功能。

7.2 MVCC实现

MVCC的实现依赖于数据库中：三个隐藏字段、undo log日志、readView。

7.2.1 隐藏字段

• DB_TRX_ID：最后一次插入或更新改行的事务ID。
• DR_ROLL_PTR：回滚指针，指向上个版本，即undo log。
• DB_ROW_ID：隐藏主键。如果没有设置主键且没有唯一非空索引时，会使用该ID生成聚集索引。

7.2.2 undo log

回滚日志。在 insert、update、delete的时候产生的便于数据回滚的日志。

• insert undo log：insert时产生的undo log。只在回滚时需要，事务提交后，可立即被删除。
• update undo log：update/delete时产生的undo log。不仅在回滚时需要，在快照读时也需要，因此不会被立即删除。

7.2.3 readview

ReadView(读视图)是快照读 SQL 执行时 MVCC 提取数据的依据，记录并维护系统当前活跃的事务(未提交的)ID。

包含以下字段：

• **m_low_limit_id：**目前出现过的最大的事务 ID+1，即下一个将被分配的事务 ID。大于等于这个 ID 的数据版本均不可见

• **m_up_limit_id：**活跃事务列表 m_ids 中最小的事务 ID，如果 m_ids 为空，则 m_up_limit_id 为 m_low_limit_id。小于这个 ID 的数据版本均可见

• m_ids：Read View 创建时其他未提交的活跃事务 ID 列表。创建 Read View 时，将当前未提交事务 ID 记录下来，后续即使它们修改了记录行的值，对于当前事务也是不可见的。m_ids 不包括当前事务自己和已提交的事务（正在内存中）

• **m_creator_trx_id：**创建该 Read View 的事务 ID

问：MVCC的实现原理？⭐⭐

MVCC 的实现主要依赖：

• Read View；
• 聚簇索引中的跟事务相关的两个隐藏列[trx_id, undo_log]。

==Read View 的结构：==

• creator_trx_id：指的是创建该 Read View 的事务的事务 id；
• m_ids：指的是在创建 Read View 时，当前数据库中「活跃事务」的事务 id 列表。注意是一个列表，“活跃事务”指的就是，启动了但还没提交的事务；
• **min_trx_id：**指的是在创建 Read View 时，当前数据库中「活跃事务」中事务 id 最小的事务，也就是 m_ids 的最小值；
• max_trx_id：这个并不是 m_ids 的最大值，而是创建 Read View 时当前数据库中应该给下一个事务的 id 值，也就是全局事务中最大的事务 id 值 + 1。

==聚簇索引的两个隐藏列：==

• trx_id：当一个事务对某条聚簇索引记录进行改动时，就会把该事务的事务 id 记录在 trx_id 隐藏列里；
• roll_pointer：每次对某条聚簇索引记录进行改动时，都会把旧版本的记录写入到 undo 日志中，然后roll_pointer 指向旧版本记录，通过它找到修改前的记录。

可以看到，聚簇索引中每行记录都会有个trx_id，可以将**所有记录的trx_id**划分为三种情况：

一个事务去访问记录时，自己的更新总是可见的，除此之外：

• 若当前记录的trx_id < Read View 中的min_trx_id，说明是创建Read View之前已经提交的事务生成的，所以该记录对当前事务可见；
• 若当前记录的trx_id >= Read View 中的max_trx_id，说明是创建Read View之后才启动的事务生成的，所以该记录对当前事务不可见；
• 若当前记录的trx_id 在min_trx_id和max_trx_id之间，需要判断trx_id是否存在m_ids列表中：
  • 若存在，表示该事务依然活跃(也就是还没提交呢)，所以该记录对当前事务不可见，就会顺着 undo log 链寻找旧版本数据；
  • 若不存在，表示该事务在当前事务创建Read View之前已经提交了，所以该记录对当前事务可见。

这种通过「版本链」来控制并发事务访问同一个记录时的行为就叫 MVCC（多版本并发控制）。

问：RC和RR隔离级别下MVCC的差异？

• 在 RC 隔离级别下的 每次select 查询前都生成一个Read View (m_ids 列表)

• 在 RR 隔离级别下只在事务开始后 第一次select 数据前生成一个Read View（m_ids 列表）

问：MVCC+临键锁 防止幻读？

1、执行普通 select，此时会以 MVCC 快照读的方式读取数据

在快照读的情况下，RR 隔离级别只会在事务开启后的第一次查询生成 Read View ，并使用至事务提交。所以在生成 Read View 之后其它事务所做的更新、插入记录版本对当前事务并不可见，实现了可重复读和防止快照读下的 “幻读”

2、执行 select…for update/lock in share mode、insert、update、delete 等当前读

在当前读下，读取的都是最新的数据，如果其它事务有插入新的记录，并且刚好在当前事务查询范围内，就会产生幻读！InnoDB 使用 Next-key Lockopen in new window 来防止这种情况。**当执行当前读时，会锁定读取到的记录的同时，锁定它们的间隙，防止其它事务在查询范围内插入数据。**只要我不让你插入，就不会发生幻读。

其实就是：

• 快照读时，通过只在第一次查询时生成Read View，来防止幻读；
• 当前读时，通过加临键锁，拒绝数据插入，来防止幻读。

八、数据类型

这点之前没记，现在补充一下~

8.1 表空间的文件结构

行

数据库表中的记录都是按行（row）进行存放的，每行记录根据不同的行格式，有不同的存储结构。

页

InnoDB 的数据是按**「页」为单位来读写**，一次I/O操作读取一页，默认每个页的大小为 16KB，也就是最多能保证 16KB 的连续存储空间。页是 InnoDB 存储引擎磁盘管理的最小单元，意味着数据库每次读写都是以 16KB 为单位的，一次最少从磁盘中读取 16K 的内容到内存中，一次最少把内存中的 16K 内容刷新到磁盘中。

区

数据量大的时候，为某个索引分配空间的时候就不再按照页为单位分配了，而是按照区（extent）为单位分配。每个区的大小为 1MB，对于 16KB 的页来说，连续的 64 个页会被划为一个区，这样就使得链表中相邻的页的物理位置也相邻，就能使用**顺序 I/O **了。

段

表空间是由各个段（segment）组成的，段是由多个区（extent）组成的。段一般分为数据段、索引段和回滚段等。

8.2 行格式

现在用的都是 Compact 行格式，一条完整的记录分为「记录的额外信息」和「记录的真实数据」两个部分。

九、日志

更新语句的流程会涉及到 undo log（回滚日志）、redo log（重做日志） 、bin log（归档日志）这三种日志。

• undo log（回滚日志）：是 Innodb 存储引擎层生成的日志，实现了事务中的原子性，主要用于事务回滚和 MVCC；
• redo log（重做日志）：是 Innodb 存储引擎层生成的日志，实现了事务中的持久性，主要用于掉电等故障恢复；
• bin log（归档日志）：是 Server 层生成的日志，主要用于数据备份和主从复制。

undo log 和 redo log

问：undo log 是啥？有啥用？

undo log 是一种用于回退撤销的日志。在事务没提交之前，MySQL 会先把更新前的数据记录到 undo log 日志文件里面，当事务回滚时，可以利用 undo log 来进行回滚。

每当 InnoDB 引擎对一条记录进行更新(修改、删除、新增)时，要把回滚时需要的信息都记录到 undo log 里。

另外，undo log 还可以串成链表，称为版本链，用来实现 MVCC。

因此，undo log 共两大作用：

• 实现事务回滚，保障事务的原子性。事务处理过程中，如果出现了错误或者用户执 行了 ROLLBACK 语句，MySQL 可以利用 undo log 中的历史数据将数据恢复到事务开始之前的状态。
• 实现 MVCC（多版本并发控制）的关键因素之一。MVCC 是通过 ReadView + undo log 实现的。undo log 为每条记录保存多份历史数据，MySQL 在执行快照读（普通 select 语句）的时候，会根据事务的 Read View 里的信息，顺着 undo log 的版本链找到满足其可见性的记录。

问：WAL 技术？

说明 redo log 有啥用之前，需要先介绍 Buffer pool 和 WAL 技术。

Buffer pool 相当于数据库中的缓存，以页为单位，读取数据时也先从这里读，更新数据时，也先尝试更新 Buffer pool 中的数据页。若 Buffer pool 中的页相比原数据库中的页做了修改，就称为脏页。

内存中的数据若不刷盘，断电了就消失了，所以为了防止断电导致数据丢失，在修改 Buffer pool 中的页之后，会将本次对这个页的修改以 redo log 的形式记录下来，然后定时由后台线程将缓存在 Buffer Pool 的脏页刷新到磁盘里，这称为 WAL(Write-Ahead Logging)技术。

WAL 技术指的是，MySQL 的写操作并不是立刻写到磁盘上，而是先写日志，然后在合适的时间再写到磁盘上。

问：redo log是啥？有啥用？

redo log 是重做日志，记录事务提交时数据页的物理修改。记录了某个数据页做了什么修改，比如对 XXX 表空间中的 YYY 数据页 ZZZ 偏移量的地方做了AAA 更新，每当执行一个事务就会产生这样的一条或者多条物理日志。

在事务提交时，只要先将 redo log 持久化到磁盘即可，不需要等到将缓存在 Buffer Pool 里的脏页数据持久化到磁盘。当系统崩溃时，虽然脏页数据没有持久化，但是 redo log 已经持久化，接着 MySQL 重启后，可以根据 redo log 的内容，将所有数据恢复到最新的状态。

问：undo log 和 redo log 有啥区别？⭐⭐

• redo log 记录了此次事务「提交后」的数据状态，记录的是更新之后的值；
• undo log 记录了此次事务「开始前」的数据状态，记录的是更新之前的值；

事务提交之前发生了崩溃，重启后会通过 undo log 回滚事务；

事务提交之后发生了崩溃，重启后会通过 redo log 恢复事务。

所以，undo log 保证了原子性，redo log 保证了持久性。

问：redo log 会满吗？

会。redo log 类似 Redis 的 repl_backlog，也是个环形缓冲区，当写满了之后，就会触发 buffer pool 的刷盘操作，然后擦除旧的 redo log，就可以继续写入了~

bin log

问：什么是 bin log？

bin log 是由 MySQL 的 Server 层生成的。

bin log 文件记录了所有数据库表结构变更和表数据修改的日志，不会记录查询类的操作，以二进制形式保存在磁盘上。

问：为什么有了 bin log 还要有 redo log？这俩有啥区别？

这跟 MySQL 的时间线有关系。

最开始 MySQL 里并没有 InnoDB 引擎，MySQL 自带的引擎是 MyISAM，但是 MyISAM 没有 crash-safe 的能力，bin log 日志只能用于归档。

而 InnoDB 是另一个公司以插件形式引入 MySQL 的，既然只依靠 bin log 是没有 crash-safe 能力的，所以 InnoDB 使用 redo log 来实现 crash-safe 能力。

区别：

1. 适用对象不同

• bin log 是 MySQL 的 Server 层实现的，所有的存储引擎都可以使用；
• redo log 是 Innodb 存储引擎实现的，也就是独有的。

2. 文件格式不同

• bin log 有 3 种格式类型，分别是 STATEMENT（默认格式）、ROW、 MIXED。区别如下：

  • STATEMENT：每一条修改数据的 SQL 都会被记录到 binlog 中（相当于记录了逻辑操作，所以针对这种格式， binlog 可以称为逻辑日志），主从复制中 slave 端再根据 SQL 语句重现。但 STATEMENT 有动态函数的问题，比如你用了 uuid 或者 now 这些函数，你在主库上执行的结果并不是你在从库执行的结果，这种随时在变的函数会导致复制的数据不一致；
  • ROW：记录每行数据最终被修改成什么样了（这种格式的日志，就不能称为逻辑日志了），不会出现 STATEMENT 下动态函数的问题。但 ROW 的缺点是每行数据的变化结果都会被记录，比如执行批量 update 语句，更新多少行数据就会产生多少条记录，使 binlog 文件过大，而在 STATEMENT 格式下只会记录一个 update 语句而已；
  • MIXED：包含了 STATEMENT 和 ROW 模式，它会根据不同的情况自动使用 ROW 模式和 STATEMENT 模式；

• redo log 是物理日志，记录的是在某个数据页做了什么修改，比如对 XXX 表空间中的 YYY 数据页 ZZZ 偏移量的地方做了AAA 更新；

3. 写入方式不同

• bin log 是追加写，写满一个文件，就创建一个新的文件继续写，不会覆盖以前的日志，保存的是全量的日志。
• redo log 是循环写，日志空间大小是固定，全部写满就从头开始，保存未被刷入磁盘的脏页日志。

4. 用途不同

• bin log 用于备份恢复、主从复制；
• redo log 用于掉电等故障恢复。

问：如果不小心整个数据库的数据被删除了，能使用 redo log 文件恢复数据吗？

不可以使用 redo log 文件恢复，只能使用 bin log 文件恢复。

因为 redo log 文件是循环写，是会边写边擦除日志的，只记录未被刷入磁盘的数据的物理日志，已经刷入磁盘的数据都会从 redo log 文件里擦除。

bin log 文件保存的是全量的日志，也就是保存了所有数据变更的情况，理论上只要记录在 bin log 上的数据，都可以恢复，所以如果不小心整个数据库的数据被删除了，得用 bin log 文件恢复数据。

问：执行 update 的过程？

1. 执行器负责具体执行，会调用存储引擎的接口，通过主键索引树搜索获取 id = 1 这一行记录：
   • 如果 id=1 这一行所在的数据页本来就在 buffer pool 中，就直接返回给执行器更新；
   • 如果记录不在 buffer pool，将数据页从磁盘读入到 buffer pool，返回记录给执行器。
2. 执行器得到聚簇索引记录后，会看一下更新前的记录和更新后的记录是否一样：
   • 如果一样的话就不进行后续更新流程；
   • 如果不一样的话就把更新前的记录和更新后的记录都当作参数传给 InnoDB 层，让 InnoDB 真正的执行更新记录的操作；
3. 开启事务，InnoDB 层更新记录前，首先要记录相应的 undo log，因为这是更新操作，需要把被更新的列的旧值记下来。
4. InnoDB 层开始更新记录，会先更新内存（同时标记为脏页），然后将记录写到 redo log 里面，这个时候更新就算完成了。为了减少磁盘I/O，不会立即将脏页写入磁盘，后续由后台线程选择一个合适的时机将脏页写入到磁盘。这就是 WAL 技术。
5. 至此，一条记录更新完了。
6. 在一条更新语句执行完成后，然后开始记录该语句对应的 bin log，此时记录的 bin log 会被保存到 bin log cache，并没有刷新到硬盘上的 bin log 文件，在事务提交时才会统一将该事务运行过程中的所有 bin log 刷新到硬盘。
7. 事务进行两阶段提交。

问：什么是两阶段提交？

两阶段提交把单个事务的提交拆分成了 2 个阶段，分别是**「准备（Prepare）阶段」和「提交（Commit）阶段」**。

MySQL 使用了内部 XA 事务，由 bin log 作为协调者，存储引擎是参与者。

• prepare 阶段：将 XID(内部 XA 事务的 ID) 写入到 redo log，同时将 redo log 对应的事务状态设置为 prepare，然后将 redo log 持久化到磁盘；
• commit 阶段：把 XID 写入到 bin log，然后将 bin log 持久化到磁盘，接着调用引擎的提交事务接口，将 redo log 状态设置为 commit。

这个过程中，出现异常无非就俩时刻，时刻 A 和 时刻 B，不管是哪个，redo log 都处于 prepare 状态。

在 MySQL 重启后会按顺序扫描 redo log 文件，碰到处于 prepare 状态的 redo log，就拿着 redo log 中的 XID 去 bin log 查看是否存在此 XID：

• 若 bin log 中没有此 XID，说明异常发生时 redo log 完成了刷盘，但 bin log 还没，因此回滚事务。对应了 时刻 A；
• 若 bin log 中有此 XID，说明异常发生时都完成了刷盘，则提交该事务。对应时刻 B。

可以看出：两阶段提交是以 bin log 写成功为事务提交成功的标识的。

问：为啥要两阶段提交？

事务提交后，redo log 和 bin log 都要持久化到磁盘，但是这两个是独立的逻辑，可能出现半成功的状态，这样就造成两份日志之间的逻辑不一致：

• 如果在将 redo log 刷入到磁盘之后，MySQL 突然宕机了，而 bin log 还没有来得及写入。重启后，主库按照 redo log 恢复，从库按照 bin log 恢复，主从库就不一致了；
• 如果在将 bin log 刷入到磁盘之后，MySQL 突然宕机了，而 redo log 还没有来得及写入。重启后，主库按照 redo log 恢复，从库按照 bin log 恢复，主从库就不一致了。

为避免两份日志出现不一致，因此采用两阶段提交。

问：两阶段提交有啥缺点？

I/O 操作次数多。这是因为每次事务提交都至少需要两次刷盘。

十、内存

基础

问：为啥要有 Buffer Pool？

MySQL 的数据存储在磁盘中，若每次操作都直接操作磁盘，效率就会很低。

因此，加一层缓存，将读出的数据页缓存到内存中，下次用到的话直接访问内存即可。Innodb 通过**缓冲池(Buffer Pool)**机制实现：

• 当读取数据时，如果数据存在于 Buffer Pool 中，客户端就会直接读取 Buffer Pool 中的数据，否则再去磁盘中读取；
• 当修改数据时，首先是修改 Buffer Pool 中数据所在的页，然后将其页设置为脏页，最后由后台线程将脏页写入到磁盘。

问：Buffer Pool 缓存了什么？

Innodb 按页存储数据，以页作为磁盘和内存交互的基本单位，一个页的默认大小为 16KB。因此，Buffer Pool 同样需要按「页」来划分。

InnoDB 会为 Buffer Pool 申请一片连续的内存空间，然后按照默认的16KB的大小划分出一个个的页，Buffer Pool 中的页就叫做缓存页。此时这些缓存页都是空闲的，之后随着程序的运行，才会有磁盘上的页被缓存到 Buffer Pool 中。

Buffer Pool 除了缓存**「索引页」和「数据页」**，还包括了 undo 页，插入缓存、自适应哈希索引、锁信息等等。

问：查询一条数据，只需要缓存一条数据吗？

No！查询记录时，通过索引找到磁盘上的页，然后会把整页加载，并存入 Buffer Pool。

一方面是因为，索引只能定位到页；另一方面，若每次IO读入了整页，却只保留一条数据，也挺浪费的。。

管理

问：如何管理空闲页？

因为有的页面被使用，有的页面没被使用，总不能每次要找到个空闲页就遍历一遍吧？

解决方案类似Go的内存管理。

通过一个带头双向链表 Free，将空闲缓存页的控制块存起来：

• Free 链表的头节点：包含链表的头节点地址，尾节点地址，以及当前链表中节点的数量等信息；
• Free 链表的节点：一个个控制块。

问：如何管理脏页？

类似 Free 链表，设计了 Flush 链表存储脏页，后台线程可以遍历 Flush 链表，将脏页写入到磁盘。

问：缓存更新策略？

类似 LRU。

问：脏页什么时候会被刷盘？⭐⭐

InnoDB 的更新操作采用的是 WAL(Write Ahead Log) 策略，即先写日志，再写入磁盘，通过 redo log 日志让 MySQL 拥有了崩溃恢复能力。

下面几种情况会触发脏页的刷新：

• 当 redo log 日志满了的情况下，会主动触发脏页刷新到磁盘；
• Buffer Pool 空间不足时，需要将一部分数据页淘汰掉，如果淘汰的是脏页，需要先将脏页同步到磁盘；
• MySQL 认为空闲时，后台线程会定期将适量的脏页刷入到磁盘；
• MySQL 正常关闭之前，会把所有的脏页刷入到磁盘。

十一、集群架构

主从复制

问：为什么要集群？

• 数据备份；
• 故障转移；
• 读写分离，减轻主库读写压力。

问：主从复制是如何实现的？

MySQL 的主从复制依赖于 bin log，复制过程：

• 写入 bin log：主库在事务提交时，会把**数据变更(增删改)**记录在 bin log 中；
• 同步 bin log：从库创建 I/O 线程，请求获取主库 bin log；
• 发送 bin log：主库创建一个 log dump 线程，将 bin log 发送给从库；
• 重放 bin log：从库将获取的 bin log 写入从库的中继日志(relay log)，同时创建线程，读取并重放 relay log，更新从库中的数据。

问：从库是越多越好吗？

当然不是。

因为从库数量增加，从库连接上来的 I/O 线程也比较多，主库也要创建同样多的 log dump 线程来处理复制的请求，对主库资源消耗比较高，同时还受限于主库的网络带宽。

所以在实际使用中，1 个主库一般跟 2～3 个从库（1 套数据库，1 主 2 从 1 备主），这就是一主多从的 MySQL 集群结构。

问：主从复制有哪些模型？/如何保持主从间的数据一致性？

• 同步复制：MySQL 主库提交事务的线程要等待所有从库的复制成功响应，才返回客户端结果。实际没法用；
• **异步复制(默认)：**主库不用等 bin log 同步到从库就返回结果。缺点就是，主库宕机可能会丢失部分数据；
• **半同步复制：**主库等 bin log 复制到一部分从库再返回结果。兼顾上述方案，就算主库宕机了，也不会丢失数据。

分库分表

问：为什么要分库？为什么要分表？

1. 为什么要分库？

如果业务量剧增，数据库可能会出现性能瓶颈，这时候我们就需要考虑拆分数据库。从这两方面来看：

• 磁盘存储

业务量剧增，MySQL单机磁盘容量会撑爆，因此可以拆成多个数据库；

• 并发连接支撑

数据库连接数是有限的。在高并发的场景下，大量请求访问数据库，MySQL单机是扛不住的！高并发场景下，会出现too many connections报错。

2. 为什么要分表？

假如你的单表数据量非常大，存储和查询的性能就会遇到瓶颈了(由于B+树)，如果你做了很多优化之后还是无法提升效率的时候，就需要考虑做分表了。一般千万级别数据量，就需要分表。

问：什么时候就要进行分库分表了？

对于MySQL，InnoDB存储引擎的话，单表最多可以存储10亿级数据。

但是的话，如果真的存储这么多，性能就会非常差。一般数据量千万级别，B+树索引高度就会到3层以上了，查询的时候会多查磁盘的次数，SQL就会变慢。

阿里巴巴的《Java开发手册》提出：

单表行数超过500万行或者单表容量超过2GB，才推荐进行分库分表。

那我们是不是等到数据量到达五百万，才开始分库分表呢？

不是这样的，我们应该提前规划分库分表，如果估算3年后，你的表都不会到达这个五百万，则不需要分库分表。

MySQL服务器如果配置更好，是不是可以超过这个500万这个量级，才考虑分库分表？

虽然配置更好，可能数据量大之后，性能还是不错，但是如果持续发展的话，还是要考虑分库分表

一般什么类型业务表需要才分库分表？

通用是一些流水表、用户表等才考虑分库分表，如果是一些配置类的表，则完全不用考虑，因为不太可能到达这个量级。

问：有哪些分库、分表方式？

• 垂直分库：以表为依据，根据业务，将不同的表拆分到不同的库中。
  • 每个库的表结构都不一样；
  • 每个库的数据也不一样；
  • 所有库的并集是全量数据。

• 垂直分表：以字段为依据，根据字段属性，将不同字段拆分到不同表中。
  • 每个表的结构都不一样；
  • 每个表的数据也不一样，一般通过一列(主键/外键)关联；
  • 所有表的并集是全量数据。

• 水平分库⭐：以字段为依据，按照一定策略，将一个库的数据拆分到多个库中。
  • 每个库的表结构都一样；
  • 每个库的数据都不一样；
  • 所有库的并集是全量数据。

• 水平分表⭐：以字段为依据，按照一定策略，将一个表的数据拆分到多个表中。

  • 每个表的表结构都一样；
  • 每个表的数据都不一样；
  • 所有表的并集是全量数据。
  

总结：

• 垂直拆分的关注点在于 业务相关性；

• 水平拆分指的是将单一数据表按照某一种规则拆分到多个数据库和多个数据表中，关注点在 数据的特点。

问：水平分片的规则有哪些？

• 范围分片：划分多个范围区间，落在相应区间的数据存入相应的库或表；
• 取模分片：指定的字段值 % 节点数量，落到哪个节点就存到哪个；
• 一致性hash：增加节点或删除节点时，取模分片会导致大量数据迁移；一致性hash中，只会影响一小部分数据的迁移。

读写分离

就是，只有主节点负责写请求，其他节点负责读请求，可以降低单台服务器的压力。

• 一主一从：一主负责写，一从负责读；
• 双主双从：一主负责写，一主两从负责读。并且，每组主从间互为主备，一组挂了，另一组就会顶上，实现高可用。

分布式 ID

当分库分表之后，同一个逻辑表的数据被分布到多个库中，这时如果使用数据库自增字段作为主键，那么只能保证在这个库中是唯一的，无法保证全局的唯一性。那么假如设计用户系统的时候，使用自增 ID 作为用户 ID，就可能出现两个用户有两个相同的 ID，这是不可接受的，那么就需要生成全局唯一的 ID。

1. UUID

不建议使用 UUID 作为数据库主键，因为：

• ID 有序更利于索引数据的插入，而 UUID 是无序的，造成了多余的数据移动的开销；
• UUID 不具备业务含义；
• UUID 是由 32 个 16 进制数字组成的字符串(128位)，如果作为数据库主键使用比较耗费空间。

2. ==雪花算法(Snowflake)==

Snowflake 的核心思想是将 64 位的二进制数字分成若干部分，每一部分都存储有特定含义的数据，比如说时间戳、机器 ID、序列号等等，最终生成全局唯一的有序 ID。可以自定义各字段代表的含义和位数。

原理知道了，那工程上是怎么实现呢？

• 一种是嵌入到业务代码里，也就是分布在业务服务器中。
• 一种是作为独立的服务部署，这也就是我们常说的发号器服务。

Snowflake 算法设计的非常简单且巧妙，性能上也足够高效，同时也能够生成具有全局唯一性、单调递增性和有业务含义的 ID，但是它也有一些缺点：

• 其中最大的缺点就是它依赖于系统的时间戳，一旦系统时间不准，就有可能生成重复的 ID。所以如果我们发现系统时钟不准，就可以让发号器暂时拒绝发号，直到时钟准确为止；
• 另外，如果请求发号器的 QPS 不高，比如说发号器每毫秒只发一个 ID，就会造成生成 ID 的末位永远是 1，那么在分库分表时如果使用 ID 作为分区键就会造成库表分配的不均匀。 这一点，也是我在实际项目中踩过的坑，而解决办法主要有两个：
  • 时间戳不记录毫秒而是记录秒，这样在一个时间区间里可以多发出几个号，避免出现分库分表时数据分配不均；
  • 生成的序列号的起始号可以做一下随机，这一秒是 21，下一秒是 30，这样就会尽量的均衡了。

十二、面试题

问：执行一条MySQL语句，非常慢，可能的原因？

分两种情况：

1. 大多数情况正常，偶尔很慢

• 数据库在刷盘，例如将 redo log、buffer 刷新到磁盘。因为采用了 WAL 技术：也就是更新数据时先写入 redo log，等空闲时再将 redo log 写入磁盘。刷脏页有以下场景：
  • redo log 写满了，需要刷盘；
  • buffer 不够了，需要淘汰一些页，而如果要淘汰的页刚好是脏页，就会刷盘；
  • 空闲时；
• 遇到锁，拿不到锁，得阻塞等待。可以用 show processlist 这个命令来查看当前的状态。

2. 一直执行得很慢

• 没用上索引，导致全表查询，还得回表，就很慢；
• 表中数据太多了，索引层数过高，建议分表；
• 分析慢查询日志；
• 数据库走错索引了，此时可以强制数据库走某个索引。

问：通过 select from limit 查询数据时，查询 0-10 和 990-1000 的效率一样吗？

limit 查询方式对应 limit offset, size，即从 offset 处开始查找 size 条数据。例如，当执行以下两条语句时：

1
2

select * from page order by id limit 0, 10;
select * from page order by id limit 6000000, 10;

• 第一条会获取到第0到10条完整行数据；
• 第二条则要先获得第0到(6000000 + 10)条完整行数据，返回给server层之后根据offset的值挨个抛弃，最后只留下最后面的size条，也就是10条数据。

由于获取到很多无用的数据，效率是不一样的。

如何优化呢？

1. 优化查询语句

优化查询语句：当select后面是*号时，需要拷贝完整的行信息，拷贝完整数据跟只拷贝行数据里的其中一两个列字段耗时是不同的，所以可以优化查询语句为：

1

select * from page where id >=(select id from page order by id limit 6000000, 1) order by id limit 10;

但这种方法也只是缓解，当 Offset 增长到百万千万级别，就也不太行了，这就涉及到了深度分页问题。

2. 深度分页

这需要从背后的需求出发，不同的需求有不同的解决方案。

• 取出全表数据

1

select * from page;

因为数据量较大，mysql根本没办法一次性获取到全部数据，肯定超时报错。

解决方法：可以将所有的数据根据id主键进行排序，然后分批次取，将当前批次的最大 id 作为下次筛选的条件进行查询。如下伪代码：

这个操作，可以通过主键索引，每次定位到id在哪，然后往后遍历100个数据，这样不管是多少万的数据，查询性能都很稳定。

• 做分页展示

啥样的分页展示能到百万千万展示量啊？想想谷歌才提供多少页？去跟产品经理battle吧还是。。

问：执行一条MySQL语句，过程？

• 连接器：建立连接，管理连接、校验用户身份；
• 查询缓存：查询语句如果命中查询缓存则直接返回，否则继续往下执行。MySQL 8.0 已删除该模块；
• 解析 SQL：通过解析器对 SQL 查询语句进行词法分析、语法分析，然后构建语法树，方便后续模块读取表名、字段、语句类型；
• 执行 SQL：执行 SQL 共有三个阶段：
  • 预处理阶段：检查表或字段是否存在；将 select * 中的 * 符号扩展为表上的所有列。
  • 优化阶段：基于查询成本的考虑，选择查询成本最小的执行计划(选择索引等)；
  • 执行阶段：根据执行计划执行 SQL 查询语句，从存储引擎读取记录，返回给客户端。