MySQL | 总结

本文是对 MySQL 一个学习归纳

问题

1.MySQL 的架构图是如何的？
2.为什么不建议开启查询缓存？如何按需使用查询缓存？
3.什么是 binlog 和 redo log？为什么需要两个日志？他们有什么区别？
4. crash-safe 是指什么？
5.为什么需要 redolog?
6.MySQL 中什么是两阶段提交？
7.为什么不删除 binlog 只保留 redolog？
8.为什么 binlog 不能做到 crash-safe?
9.为什么 redolog 要在 prepare 阶段就落盘?

10.ACID 是指什么？
11.事务隔离级别有哪些？分别是什么意思？
12.幻读和不可重复读的区别？
13.并发事务一般有哪些问题？
14.针对并发事务的问题，应该如何解决？
15.什么是 MVCC？
16.MVCC 是如何工作的？
17.REPEATABLE READ（可重读）隔离级别下的 MVCC 是如何工作的？
18.MySQL 事务实现原理是什么？
19.如何设置 MySQL 的事务隔离级别？
20.如何手动操作事务？

21.什么是索引？
22.索引分别有什么优点和缺点？
23.索引有几种类型？
24.什么叫做回表？
25.联合索引的作用是什么？应该注意什么问题？
26.什么是最左匹配原则？他的生效原则是什么？
27.列值为 NULL 时，查询会使用到索引吗？
28.表有索引 date，语句 select * from t where year(date)>2018; 会触发索引吗？
29.什么是前缀索引？为什么要用前缀索引？
30.什么情况下适合使用前缀索引？如何确定前缀索引的长度？
31.什么是页？
32.索引的常见存储算法有哪些？
33.InnoDB 为什么要使用 B+ 树，而不是 B 树、Hash、红黑树或二叉树？
34.为什么 InnoDB 要使用 B+ 树来存储索引？

35.什么是锁？MySQL 中提供了几类锁？
36.什么是死锁？
37.如何处理死锁？
38.如何查看死锁？
39.如何避免死锁？
40.InnoDB 默认是如何对待死锁的？
41.如何开启死锁检测？
42.什么是全局锁？它的应用场景有哪些？
43.什么是共享锁？
44.什么是排它锁？
45.使用全局锁会导致什么问题？
46.如何设置数据库为全局只读锁？
47.除了 FTWRL 可以设置数据库只读外，还有什么别的方法？
48.FTWRL 和 set global readonly=true 有什么区别？
49.如何实现表锁？
50.悲观锁和乐观锁有什么区别？
51.乐观锁有什么优点和缺点？
52.InnoDB 存储引擎有几种锁算法？
53.InnoDB 如何实现行锁？
54.优化锁方面你有什么建议？

55.MySQL 有哪些重要的日志文件？
56.什么是脏页和干净页？
57.什么情况下会引发 MySQL 刷脏页（flush）的操作？
58.MySQL 刷脏页的速度很慢可能是什么原因？
59.如何控制 MySQL 只刷新当前脏页？
60.MySQL 的 WAL 技术是解决什么问题的？
61.为什么有时候会感觉 MySQL 偶尔卡一下？
62.redo log 和 binlog 是怎么关联的?
63.MySQL 怎么知道 binlog 是完整的?
64.MySQL 中可不可以只要 binlog，不要 redo log？
65.MySQL 中可不可以只要 redo log，不要 binlog？
66.为什么 binlog cache 是每个线程自己维护的，而 redo log buffer 是全局共用的？
67.在 MySQL 中用什么机制来优化随机读/写磁盘对 IO 的消耗？
68.
69.
60.
71.
72.
73.
74.
75.
76.
77.
78.
79.

总结

1.MySQL 的架构图是如何的？

从大体上来说，MySQL 可以分为 Server 层和存储引擎层两部分。

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

而存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持 InnoDB、
MyISAM、Memory 等多个存储引擎。现在最常用的存储引擎是 InnoDB，它从 MySQL
5.5.5 版本开始成为了默认存储引擎。

图示如下：

2.为什么不建议开启查询缓存？如何按需使用查询缓存？

查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清
空。因此很可能你费劲地把结果存起来，还没使用呢，就被一个更新全清空了。对于更新
压力大的数据库来说，查询缓存的命中率会非常低。除非你的业务就是有一张静态表，很
长时间才会更新一次。比如，一个系统配置表，那这张表上的查询才适合使用查询缓存

可以将参数 query_cache_type 设置成 DEMAND，这样对于默认的 SQL 语句都不使用查询缓存。而对于你确定要使用查询缓存的语句，可以用 SQL_CACHE 显式指定，像下面这个语句一样：

mysql> select SQL_CACHE * from T where ID=1

需要注意的是，MySQL 从 8.0 开始本直接将查询缓存的整块功能删掉了。

3.什么是 binlog 和 redo log？为什么需要两个日志？他们有什么区别？

binlog 和 redolog 都是 mysql 中的日志，他们分别作用于 MySQL 中的不同地方，binlog 是存在是 Server 层的日志，是所有引擎都可以使用的，而 redolog 是 InnoDB 引擎特有的。binlog 记录了对 MySQL 数据库执行更改的所有操作，但是不包 SELECT 和 SHOW 这类操作，因为这类操作对数据本身并没有修改。redo log 又称重做日志文件，用于记录事务操作的变化，记录的是数据修改之后的值，不管事务是否提交都会记录下来。

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

• redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。

• redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

4.crash-safe 是指什么？

crash-safe 就是指即使数据库崩溃发生异常重启，之前提交的记录都不会丢失，这个能力称为 crash-safe。

5.为什么需要 redolog?

因为更新数据的时候引擎并不是按条更新的，而是以页为最小单位更新。如果没有 redolog，每次更新一条数据都要把整页的数据刷新到磁盘。如果更新多条数据，很可能一次就要更新多页，而且这些 io 是随机 io，磁盘 i/o 就会多且慢。有了 redolog，就不需要每次直接按页更新磁盘，而是把更新写到 redolog 中，然后等空闲时间再把 redolog 中的更新写入到磁盘。这样做的好处是 redolog 是顺序写的，而且是按条不是按页写。所以虽然多了一步，实际上是比直接更新快的。

6.MySQL 中什么是两阶段提交？

当在 MySQL 中使用 InnoDB 引擎的同时引入了 redolog 日志，为了解决 MySQL 本身的 binlog 与 redolog 的一致性与协同问题，从而引入了两阶段提交法。在这里它大概是：
当一条命令执行完毕后

• 引擎将新的数据行更新到内存中，同时将这个更新操作记录到 redo log，此时 redo log 处于 prepare 状态，然后告知执行器执行完成了，随时可以提交事务。
• 执行器生成这个操作的 binlog,并把 binlog 写入磁盘
• 执行器调用引擎接口，引擎把刚刚写的 redo log 改成提交 commit 状态，更新完成。
  其中将 redolog 分为 prepare 和 commit 两个阶段提交，故称为两阶段提交法。
  图示如下：
  

7.为什么不删除 binlog 只保留 redolog？

• 一个原因是 redolog 是只用 InnoDB 引擎有，其他引擎不具有，所以必须保留 binlog。
• redolog 是循环写的，而 binlog 是追加写的，所以当 redolog 写满过后就只有将数据刷进磁盘，而这也就导致了它不能作为数据恢复的保障，相反由于 binlog 是追加写的而具有归档功能，我们就可以据此来做一些数据的恢复。

8.为什么 binlog 不能做到 crash-safe?

假如只有 binlog，有可能先提交事务再写 binlog，有可能事务提交数据更新之后数据库崩了，还没来得及写 binlog。我们都知道 binlog 一般用来做数据库的主从复制或恢复数据库，这样就导致主从数据库不一致或者无法恢复数据库了。同样即使先写 binlog 再提交事务更新数据库，还是有可能写 binlog 成功之后数据库崩掉而导致数据库更新失败，这样也会导致主从数据库不一致或者无法恢复数据库。所以只有 binlog 做不到 crash-safe。为了支持 crash-safe，需要 redolog，而且为了保证逻辑一致，事务提交需要两个阶段：prepare 阶段和 commit 阶段。写 redolog 并落入磁盘(prepare 状态)--> 写 binlog-->commit。commit 的时候是不会落盘的。

9.为什么 redolog 要在 prepare 阶段就落盘?

如果 binlog 写成功之后，将 redolog 置成 commit 的时候数据库崩了，如果在 commit 的时候 redolog 才落盘，由于事务是否成功以 binlog 为依据，上面的情况下事务是成功的，但是 redolog 没有写到磁盘，丢了。恢复之后数据库与 binlog 就不一致了。如果在 prepare 阶段落盘，上面的情况下 redolog 已经写入到文件了（在 prepare 阶段已经写盘了），恢复的时候不会丢数据。

10.ACID 是指什么？

ACID 是衡量事务的四个特性：

• 原子性（Atomicity，或称不可分割性）
• 一致性（Consistency）
• 隔离性（Isolation）
• 持久性（Durability）

原子性（Atomicity）
原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
一致性（Consistency）
事务前后数据的完整性必须保持一致。
隔离性（Isolation）
事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。
持久性（Durability）
持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响

11.事务隔离级别有哪些？分别是什么意思？

事务隔离级别：

• 读未提交（read　uncommitted）
• 读提交（read committed）
• 可重复读（repeatable read）
• 串行化（serializable ）

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

12.幻读和不可重复读的区别？

• 不可重复读的重点是修改：在同一事物中，同样的条件，第一次读的数据和第二次读的不一样。（因为中间其他事物提交了修改）
• 幻读的重点在于新增或者删除：再同一事物中，同样的条件，第一次和第二次读出来的记录不一样。（因为中间有其他事物提交了插入/删除）

13.并发事务一般有哪些问题？

• 更新丢失（Lost Update）：当两个或多个事务选择同一行，然后基于最初选定的值更新改行时，由于每个事务都不知道其他事务的存在，就会发生更新丢失问题，最后的更新覆盖了其他事务所作的更新。
• 脏读（Dirty Reads）：一个事务正在对一条记录进行修改，在这个事务完成并提交前，这条记录的数据就处于不一致的状态；这时，另一个事务也来读取同一条记录。如果不加以控制，第二个事务读取了这些脏数据，并据此做出进一步的处理，就会产生未提交的数据依赖关系，这种现象也被称为脏读。
• 不可重复读（Non-Repeatable Reads）：一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现其读出的数据已经发生了改变、或某些记录已经被删除了！这种现象就叫做“不可重复读” 。
• 幻读（Phantom Reads）： 一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读” 。

14.针对并发事务的问题，应该如何解决？

并发事务造成：脏读、不可重复读和幻读等问题 ，这些问题其实都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制来解决，解决方案如下：

• 加锁：在读取数据前，对其加锁，阻止其他事务对数据进行修改。
• 提供数据多版本并发控制（MultiVersion Concurrency Control，简称 MVCC 或 MCC），也称为多版本数据库：不用加任何锁， 通过一定机制生成一个数据请求时间点的一致性数据快照（Snapshot)， 并用这个快照来提供一定级别 （语句级或事务级） 的一致性读取，从用户的角度来看，好象是数据库可以提供同一数据的多个版本。

15.什么是 MVCC？

MVCC 全称是多版本并发控制系统，InnoDB 和 Falcon 存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）机制解决幻读问题。

16.MVCC 是如何工作的？

在每一行数据中额外保存两个隐藏的列：当前行创建时的版本号和删除时的版本号（可能为空，其实还有一列称为回滚指针，用于事务回滚，不在本文范畴）。这里的版本号并不是实际的时间值，而是系统版本号。每开始新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询每行记录的版本号进行比较。

每个事务又有自己的版本号，这样事务内执行 CRUD 操作时，就通过版本号的比较来达到数据版本控制的目的。

17.REPEATABLE READ（可重读）隔离级别下的 MVCC 是如何工作的？

• SELECT：InnoDB 会根据以下条件检查每一行记录：第一，InnoDB 只查找版本早于当前事务版本的数据行，这样可以确保事务读取的行要么是在开始事务之前已经存在要么是事务自身插入或者修改过的。第二，行的删除版本号要么未定义，要么大于当前事务版本号，这样可以确保事务读取到的行在事务开始之前未被删除。
• INSERT：InnoDB 为新插入的每一行保存当前系统版本号作为行版本号。
• DELETE：InnoDB 为删除的每一行保存当前系统版本号作为行删除标识。
• UPDATE：InnoDB 为插入的一行新纪录保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为删除标识保存这两个版本号，使大多数操作都不用加锁。它不足之处是每行记录都需要额外的存储空间，需要做更多的行检查工作和一些额外的维护工作。

18.MySQL 事务实现原理是什么？

事务的实现是基于数据库的存储引擎，不同的存储引擎对事务的支持程度不一样。MySQL 中支持事务的存储引擎有 InnoDB 和 NDB。 InnoDB 是高版本 MySQL 的默认的存储引擎，因此就以 InnoDB 的事务实现为例，InnoDB 是通过多版本并发控制（MVCC，Multiversion Concurrency Control ）解决不可重复读问题，加上间隙锁（也就是并发控制）解决幻读问题。因此 InnoDB 的 RR 隔离级别其实实现了串行化级别的效果，而且保留了比较好的并发性能。事务的隔离性是通过锁实现，而事务的原子性、一致性和持久性则是通过事务日志实现。

19.如何设置 MySQL 的事务隔离级别？

• InnoDB 默认的事务隔离是 repetable read（可重复读）；可以通过 set 作用域 transaction isolation level 事务隔离级别 来修改事务的隔离级别，比如：

  MySQL> set global transaction isolation level read committed; // 设置全局事务隔离级别为 read    committed  
  MySQL> set session transaction isolation level read committed; // 设置当前会话事务隔离级别为   read committed
  

• MySQL 事务隔离级别 MySQL.cnf 文件里设置的（默认目录 /etc/my.cnf），在文件的文末添加配置：

  transaction-isolation = REPEATABLE-READ

  可用的配置值：READ-UNCOMMITTED、READ-COMMITTED、REPEATABLE-READ、SERIALIZABLE。

20.如何手动操作事务？

InnoDB 默认是自动提交事务的，每一次 SQL 操作（非 select 操作）都会自动提交一个事务，如果要手动开启事务需要设置 set autocommit=0 禁止自动提交事务，相当于开启手动提交事务。
autocommit=0 表示禁止自动事务提交，在添加操作之后没有进行手动提交，默认情况下其他连接客户端是查询不到此条新增数据的。

想要手动操作事务，可以使用 begin 开启事务；rollback 回滚事务；commit 提交事务。具体使用示例如下：

begin;
insert person(uname,age) values('laowang',18);
rollback;
commit;

21.什么是索引？

索引是一种能帮助 MySQL 提高查询效率的数据结构。

22.索引分别有什么优点和缺点？

索引的优点如下：

• 快速访问数据表中的特定信息，提高检索速度。
• 创建唯一性索引，保证数据表中每一行数据的唯一性。
• 加速表与表之间的连接。
• 使用分组和排序进行数据检索时，可以显著减少查询中分组和排序的时间。

索引的缺点：

• 虽然提高了的查询速度，但却降低了更新表的速度，比如 update、insert，因为更新数据时，MySQL 不仅要更新数据，还要更新索引文件；
• 建立索引会占用磁盘文件的索引文件。

使用索引注意事项：

• 使用短索引，短索引不仅可以提高查询速度，更能节省磁盘空间和 I/O 操作；
• 索引列排序，MySQL 查询只使用一个索引，因此如果 where 子句中已经使用了索引的话，那么 order by 中的列是不会使用索引的，因此数据库默认排序可以符合要求的情况下，不要进行排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引；
• like 语句操作，一般情况下不鼓励使用 like 操作，如果非使用不可， 注意 like "%aaa%" 不会使用索引，而 like "aaa%"可以使用索引；
• 不要在列上进行运算；
• 不适用 NOT IN 和 <> 操作。

23.索引有几种类型？

MySQL 的索引有两种分类方式：逻辑分类和物理分类。 按照逻辑分类，索引可分为：

• 主键索引：一张表只能有一个主键索引，不允许重复、不允许为 NULL；
• 唯一索引：数据列不允许重复，允许为 NULL 值，一张表可有多个唯一索引，但是一个唯一索引只能包含一列，比如身份证号码、卡号等都可以作为唯一索引；
• 普通索引：一张表可以创建多个普通索引，一个普通索引可以包含多个字段，允许数据重复，允许 NULL 值插入；
• 全文索引：让搜索关键词更高效的一种索引。

按照物理分类，索引可分为：

• 聚集索引：一般是表中的主键索引，如果表中没有显示指定主键，则会选择表中的第一个不允许为 NULL 的唯一索引，如果还是没有的话，就采用 Innodb 存储引擎为每行数据内置的 6 字节 ROWID 作为聚集索引。每张表只有一个聚集索引，因为聚集索引的键值的逻辑顺序决定了表中相应行的物理顺序。聚集索引在精确查找和范围查找方面有良好的性能表现（相比于普通索引和全表扫描），聚集索引就显得弥足珍贵，聚集索引选择还是要慎重的（一般不会让没有语义的自增 id 充当聚集索引）；
• 非聚集索引：该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同（非主键的那一列），一个表中可以拥有多个非聚集索引。

各种索引的创建脚本如下：

-- 创建主键索引
alter table t add primary key add (`id`);
-- 创建唯一索引
alter table t add unique (`username`);
-- 创建普通索引
alter table t add index index_name (`username`);
-- 创建全文索引
alter table t add fulltext (`username`);

24.什么叫做回表？

普通索引查询到主键索引后，回到主键索引树搜索的过程，我们称为回表查询。

参考 SQL：

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

如果语句是 select * from T where ID=500，即主键查询方式，则只需要检索主键 ID 字段。

mysql>  select * from T where ID=500;
+-----+---+-------+
| id  | k | name  |
+-----+---+-------+
| 500 | 5 | name5 |
+-----+---+-------+

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

mysql> select * from T where k=5;
+-----+---+-------+
| id  | k | name  |
+-----+---+-------+
| 500 | 5 | name5 |
+-----+---+-------+

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

25.联合索引的作用是什么？应该注意什么问题？

联合索引的作用如下：

• 用于多字段查询，比如，建了一个 key(a,b,c) 的联合索引，那么实际等于建了 key(a)、key(a,b)、key(a,b,c) 等三个索引，我们知道，每多一个索引，就会多一些写操作和占用磁盘空间的开销，尤其是对大数据量的表来说，这可以减少一部分不必要的开销；
• 覆盖索引，比如，对于联合索引 key(a,b,c) 来说，如果使用 SQL：select a,b,c from table where a=1 and b = 1 ，就可以直接通过遍历索引取得数据，而无需回表查询，这就减少了随机的 IO 操作，减少随机的 IO 操作，可以有效的提升数据库查询的性能，是非常重要的数据库优化手段之一；
• 索引列越多，通过索引筛选出的数据越少。

需要注意的如下：

联合索引又叫复合索引，MySQL 中的联合索引，遵循最左匹配原则，比如，联合索引为 key(a,b,c)，则能触发索引的搜索组合是 a|ab|abc 这三种查询（这三种 where 中的顺序没要求，优化器会自动帮我们优化为 a，b，c 的顺序）。同时当遇到范围查询(>、<、between、like)就会导致后面的失效

26.什么是最左匹配原则？他的生效原则是什么？

最左匹配原则也叫最左前缀原则，是 MySQL 中的一个重要原则，说的是索引以最左边的为起点任何连续的索引都能匹配上，当遇到范围查询（>、<、between、like）就会停止匹配。 生效原则来看以下示例，比如表中有一个联合索引字段 index(a,b,c)：

• where a=1 只使用了索引 a；
• where a=1 and b=2 只使用了索引 a,b；
• where a=1 and b=2 and c=3 使用 a,b,c；
• where b=1 or where c=1 不使用索引；
• where a=1 and c=3 只使用了索引 a；
• where a=3 and b like 'xx%' and c=3 只使用了索引 a,b。

27.列值为 NULL 时，查询会使用到索引吗？

在 MySQL 5.6 以上的 InnoDB 存储引擎会正常触发索引。但为了兼容低版本的 MySQL 和兼容其他数据库存储引擎，不建议使用 NULL 值来存储和查询数据，建议设置列为 NOT NULL，并设置一个默认值，比如 0 和空字符串等，如果是 datetime 类型，可以设置成 1970-01-01 00:00:00 这样的特殊值。

28.表有索引 date，语句 select * from t where year(date)>2018; 会触发索引吗？

不会，在 mysql 中使用函数会使索引失效。

29.什么是前缀索引？为什么要用前缀索引？

• 前缀索引也叫局部索引，比如给身份证的前 10 位添加索引，类似这种给某列部分信息添加索引的方式叫做前缀索引。
• 前缀索引能有效减小索引文件的大小，让每个索引页可以保存更多的索引值，从而提高了索引查询的速度。但前缀索引也有它的缺点，不能在 order by 或者 group by 中触发前缀索引，也不能把它们用于覆盖索引。

30.什么情况下适合使用前缀索引？如何确定前缀索引的长度？

• 当字符串本身可能比较长，而且前几个字符就开始不相同，适合使用前缀索引；相反情况下不适合使用前缀索引，比如，整个字段的长度为 20，索引选择性为 0.9，而我们对前 10 个字符建立前缀索引其选择性也只有 0.5，那么我们需要继续加大前缀字符的长度，但是这个时候前缀索引的优势已经不明显，就没有创建前缀索引的必要了。
• 可以通过计算选择性来确定前缀索引的选择性，计算方法如下
  全列选择性：
  SELECT COUNT(DISTINCT column_name) / COUNT(*) FROM table_name;
  某一长度前缀的选择性
  SELECT COUNT(DISTINCT LEFT(column_name, prefix_length)) / COUNT(*) FROM table_name;
  当前缀的选择性越接近全列选择性的时候，索引效果越好。

31.什么是页？

页是计算机管理存储器的逻辑块，硬件及操作系统往往将主存和磁盘存储区分割为连续的大小相等的块，每个存储块称为一页。主存和磁盘以页为单位交换数据。数据库系统的设计者巧妙利用了磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点只需要一次磁盘 IO 就可以完全载入。

32.索引的常见存储算法有哪些？

• 哈希存储法：以 key、value 方式存储，把值存入数组中使用哈希值确认数据的位置，如果发生哈希冲突，使用链表存储数据；
• 有序数组存储法：按顺序存储，优点是可以使用二分法快速找到数据，缺点是更新效率，适合静态数据存储；
• 搜索树：以树的方式进行存储，查询性能好，更新速度快。

33.InnoDB 为什么要使用 B+ 树，而不是 B 树、Hash、红黑树或二叉树？

因为 B 树、Hash、红黑树或二叉树存在以下问题：

• B 树：不管叶子节点还是非叶子节点，都会保存数据，这样导致在非叶子节点中能保存的指针数量变少（有些资料也称为扇出），指针少的情况下要保存大量数据，只能增加树的高度，导致 IO 操作变多，查询性能变低；
• Hash：虽然可以快速定位，但是没有顺序，IO 复杂度高；
• 二叉树：树的高度不均匀，不能自平衡，查找效率跟数据有关（树的高度），并且 IO 代价高；
• 红黑树：树的高度随着数据量增加而增加，IO 代价高。

34.为什么 InnoDB 要使用 B+ 树来存储索引？

B+Tree 中的 B 是 Balance，是平衡的意思，它在经典 B Tree 的基础上进行了优化，增加了顺序访问指针，在 B+Tree 的每个叶子节点增加一个指向相邻叶子节点的指针，就形成了带有顺序访问指针的 B+Tree，这样就提高了区间访问性能：如果要查询 key 为从 18 到 49 的所有数据记录，当找到 18 后，只需顺着节点和指针顺序遍历就可以一次性访问到所有数据节点，极大提到了区间查询效率（无需返回上层父节点重复遍历查找减少 IO 操作）。

索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上，这样的话，索引查找过程中就要产生磁盘 IO 消耗，相对于内存存取，IO 存取的消耗要高几个数量级，所以索引的结构组织要尽量减少查找过程中磁盘 IO 的存取次数，从而提升索引效率。 综合所述，InnDB 只有采取 B+ 树的数据结构存储索引，才能提供数据库整体的操作性能。

35.什么是锁？MySQL 中提供了几类锁？

锁是实现数据库并发控制的重要手段，可以保证数据库在多人同时操作时能够正常运行。MySQL 提供了全局锁、行级锁、表级锁。其中 InnoDB 支持表级锁和行级锁，MyISAM 只支持表级锁。

36.什么是死锁？

是指两个或两个以上的进程在执行过程中，因争夺资源而造成的一种互相等待的现象,若无外力作用,它们都将无法推进下去。此时称系统处于死锁状态或系统产生了死锁，这些永远在互相等待的过程称为死锁。

37.如何处理死锁？

对待死锁常见的两种策略：

• 通过 innodblockwait_timeout 来设置超时时间，一直等待直到超时；
• 发起死锁检测，发现死锁之后，主动回滚死锁中的某一个事务，让其它事务继续执行。

38.如何查看死锁?

• 使用命令 show engine innodb status 查看最近的一次死锁。
• InnoDB Lock Monitor 打开锁监控，每 15s 输出一次日志。使用完毕后建议关闭，否则会影响数据库性能。

39.如何避免死锁？

• 为了在单个 InnoDB 表上执行多个并发写入操作时避免死锁，可以在事务开始时通过为预期要修改的每个元祖（行）使用 SELECT ... FOR UPDATE 语句来获取必要的锁，即使这些行的更改语句是在之后才执行的。
• 在事务中，如果要更新记录，应该直接申请足够级别的锁，即排他锁，而不应先申请共享锁、更新时再申请排他锁，因为这时候当用户再申请排他锁时，其他事务可能又已经获得了相同记录的共享锁，从而造成锁冲突，甚至死锁
• 如果事务需要修改或锁定多个表，则应在每个事务中以相同的顺序使用加锁语句。在应用中，如果不同的程序会并发存取多个表，应尽量约定以相同的顺序来访问表，这样可以大大降低产生死锁的机会
• 通过 SELECT ... LOCK IN SHARE MODE 获取行的读锁后，如果当前事务再需要对该记录进行更新操作，则很有可能造成死锁。
• 改变事务隔离级别。

40.InnoDB 默认是如何对待死锁的？

InnoDB 默认是使用设置死锁时间来让死锁超时的策略，默认 innodblockwait_timeout 设置的时长是 50s。

41.如何开启死锁检测？

设置 innodbdeadlockdetect 设置为 on 可以主动检测死锁，在 Innodb 中这个值默认就是 on 开启的状态。

42.什么是全局锁？它的应用场景有哪些？

全局锁就是对整个数据库实例加锁，它的典型使用场景就是做全库逻辑备份。 这个命令可以使整个库处于只读状态。使用该命令之后，数据更新语句、数据定义语句、更新类事务的提交语句等操作都会被阻塞。

43.什么是共享锁？

共享锁又称读锁 (read lock)，是读取操作创建的锁。其他用户可以并发读取数据，但任何事务都不能对数据进行修改（获取数据上的排他锁），直到已释放所有共享锁。当如果事务对读锁进行修改操作，很可能会造成死锁。

44.什么是排它锁？

排他锁 exclusive lock（也叫 writer lock）又称写锁。

若某个事物对某一行加上了排他锁，只能这个事务对其进行读写，在此事务结束之前，其他事务不能对其进行加任何锁，其他进程可以读取,不能进行写操作，需等待其释放。

排它锁是悲观锁的一种实现，在上面悲观锁也介绍过。

若事务 1 对数据对象 A 加上 X 锁，事务 1 可以读 A 也可以修改 A，其他事务不能再对 A 加任何锁，直到事物 1 释放 A 上的锁。这保证了其他事务在事物 1 释放 A 上的锁之前不能再读取和修改 A。排它锁会阻塞所有的排它锁和共享锁。

45.使用全局锁会导致什么问题？

如果在主库备份，在备份期间不能更新，业务停摆，所以更新业务会处于等待状态。

如果在从库备份，在备份期间不能执行主库同步的 binlog，导致主从延迟。

46.如何设置数据库为全局只读锁？

使用命令 flush tables with read lock（简称 FTWRL）就可以实现设置数据库为全局只读锁。

47.除了 FTWRL 可以设置数据库只读外，还有什么别的方法？

除了使用 FTWRL 外，还可以使用命令 set global readonly=true 设置数据库为只读。

48.FTWRL 和 set global readonly=true 有什么区别？

FTWRL 和 set global readonly=true 都是设置整个数据库为只读状态，但他们最大的区别就是，当执行 FTWRL 的客户端断开之后，整个数据库会取消只读，而 set global readonly=true 会一直让数据处于只读状态。

49.如何实现表锁？

MySQL 里标记锁有两种：表级锁、元数据锁（meta data lock）简称 MDL。表锁的语法是 lock tables t read/write。

可以用 unlock tables 主动释放锁，也可以在客户端断开的时候自动释放。lock tables 语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。

对于 InnoDB 这种支持行锁的引擎，一般不使用 lock tables 命令来控制并发，毕竟锁住整个表的影响面还是太大。

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

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

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

读锁之间不互斥，读写锁之间，写锁之间是互斥的，用来保证变更表结构操作的安全性。

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

50.悲观锁和乐观锁有什么区别？

顾名思义，就是很悲观，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会 block 直到它拿到锁。正因为如此，悲观锁需要耗费较多的时间，另外与乐观锁相对应的，悲观锁是由数据库自己实现了的，要用的时候，我们直接调用数据库的相关语句就可以了。

说到这里，由悲观锁涉及到的另外两个锁概念就出来了，它们就是共享锁与排它锁。共享锁和排它锁是悲观锁的不同的实现，它俩都属于悲观锁的范畴。

乐观锁是用数据版本（Version）记录机制实现，这是乐观锁最常用的一种实现方式。何谓数据版本？即为数据增加一个版本标识，一般是通过为数据库表增加一个数字类型的 version 字段来实现。当读取数据时，将 version 字段的值一同读出，数据每更新一次，对此 version 值加 1。当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的 version 值进行比对，如果数据库表当前版本号与第一次取出来的 version 值相等，则予以更新，否则认为是过期数据。

比如： 1、数据库表三个字段，分别是 id、value、version select id,value,version from t where id=#{id} 2、每次更新表中的 value 字段时，为了防止发生冲突，需要这样操作

update t
set value=2,version=version+1
where id=#{id} and version=#{version}

51.乐观锁有什么优点和缺点？

因为没有加锁所以乐观锁的优点就是执行性能高。它的缺点就是有可能产生 ABA 的问题，ABA 问题指的是有一个变量 V 初次读取的时候是 A 值，并且在准备赋值的时候检查到它仍然是 A 值，会误以为没有被修改会正常的执行修改操作，实际上这段时间它的值可能被改了其他值，之后又改回为 A 值，这个问题被称为 ABA 问题。

52.InnoDB 存储引擎有几种锁算法？

• Record Lock — 单个行记录上的锁；
• Gap Lock — 间隙锁，锁定一个范围，不包括记录本身；
• Next-Key Lock — 锁定一个范围，包括记录本身。

53.InnoDB 如何实现行锁？

行级锁是 MySQL 中粒度最小的一种锁，他能大大减少数据库操作的冲突。

INNODB 的行级锁有共享锁（S LOCK）和排他锁（X LOCK）两种。共享锁允许事物读一行记录，不允许任何线程对该行记录进行修改。排他锁允许当前事物删除或更新一行记录，其他线程不能操作该记录。

共享锁：SELECT ... LOCK IN SHARE MODE，MySQL 会对查询结果集中每行都添加共享锁，前提是当前线程没有对该结果集中的任何行使用排他锁，否则申请会阻塞。

排他锁：select * from t where id=1 for update，其中 id 字段必须有索引，MySQL 会对查询结果集中每行都添加排他锁，在事物操作中，任何对记录的更新与删除操作会自动加上排他锁。前提是当前没有线程对该结果集中的任何行使用排他锁或共享锁，否则申请会阻塞。

54.优化锁方面你有什么建议？

• 尽量使用较低的隔离级别。
• 精心设计索引， 并尽量使用索引访问数据， 使加锁更精确， 从而减少锁冲突的机会。
• 选择合理的事务大小，小事务发生锁冲突的几率也更小。
• 给记录集显示加锁时，最好一次性请求足够级别的锁。比如要修改数据的话，最好直接申请排他锁，而不是先申请共享锁，修改时再请求排他锁，这样容易产生死锁。
• 不同的程序访问一组表时，应尽量约定以相同的顺序访问各表，对一个表而言，尽可能以固定的顺序存取表中的行。这样可以大大减少死锁的机会。
• 尽量用相等条件访问数据，这样可以避免间隙锁对并发插入的影响。
• 不要申请超过实际需要的锁级别。
• 除非必须，查询时不要显示加锁。 MySQL 的 MVCC 可以实现事务中的查询不用加锁，优化事务性能；MVCC 只在 COMMITTED READ（读提交）和 REPEATABLE READ（可重复读）两种隔离级别下工作。
• 对于一些特定的事务，可以使用表锁来提高处理速度或减少死锁的可能。

55.MySQL 有哪些重要的日志文件？

MySQL 中的重要日志分为以下几个： ① 错误日志：用来记录 MySQL 服务器运行过程中的错误信息，比如，无法加载 MySQL 数据库的数据文件，或权限不正确等都会被记录在此，还有复制环境下，从服务器进程的信息也会被记录进错误日志。默认情况下，错误日志是开启的，且无法被禁止。默认情况下，错误日志是存储在数据库的数据文件目录中，名称为 hostname.err，其中 hostname 为服务器主机名。在 MySQL 5.5.7 之前，数据库管理员可以删除很长时间之前的错误日志，以节省服务器上的硬盘空间， MySQL 5.5.7 之后，服务器将关闭此项功能，只能使用重命名原来的错误日志文件，手动冲洗日志创建一个新的，命令为：

mv hostname.err hostname.err.old mysqladmin flush-logs

② 查询日志：查询日志在 MySQL 中被称为 general log(通用日志)，查询日志里的内容不要被“查询日志”误导，认为里面只存储 select 语句，其实不然，查询日志里面记录了数据库执行的所有命令，不管语句是否正确，都会被记录，具体原因如下:

• insert 查询为了避免数据冲突，如果此前插入过数据，当前插入的数据如果跟主键或唯一键的数据重复那肯定会报错；
• update 时也会查询因为更新的时候很可能会更新某一块数据；
• delete 查询，只删除符合条件的数据；

因此都会产生日志，在并发操作非常多的场景下，查询信息会非常多，那么如果都记录下来会导致 IO 非常大，影响 MySQL 性能，因此如果不是在调试环境下，是不建议开启查询日志功能的。

查询日志的开启有助于帮助我们分析哪些语句执行密集，执行密集的 select 语句对应的数据是否能够被缓存，同时也可以帮助我们分析问题，所以，我们可以根据自己的实际情况来决定是否开启查询日志。

查询日志模式是关闭的，可以通过以下命令开启查询日志：

set global generallog=1 set global logoutput='table';

general_log=1 为开启查询日志，0 为关闭查询日志，这个设置命令即时生效，不用重启 MySQL 服务器。

③ 慢日志：慢查询会导致 CPU、IOPS、内存消耗过高，当数据库遇到性能瓶颈时，大部分时间都是由于慢查询导致的。开启慢查询日志，可以让 MySQL 记录下查询超过指定时间的语句，之后运维人员通过定位分析，能够很好的优化数据库性能。默认情况下，慢查询日志是不开启的，只有手动开启了，慢查询才会被记录到慢查询日志中。使用如下命令记录当前数据库的慢查询语句：

set global slowquerylog='ON';

使用 set global slowquerylog='ON' 开启慢查询日志，只是对当前数据库有效，如果 MySQL 数据库重启后就会失效。所以如果要永久生效，就要修改配置文件 my.cnf，设置 slowquerylog=1 并重启 MySQL 服务器。

④ redo log（重做日志）：为了最大程度的避免数据写入时，因为 IO 瓶颈造成的性能问题，MySQL 采用了这样一种缓存机制，先将数据写入内存中，再批量把内存中的数据统一刷回磁盘。为了避免将数据刷回磁盘过程中，因为掉电或系统故障带来的数据丢失问题，InnoDB 采用 redo log 来解决此问题。

⑤ undo log（回滚日志）：用于存储日志被修改前的值，从而保证如果修改出现异常，可以使用 undo log 日志来实现回滚操作。 undo log 和 redo log 记录物理日志不一样，它是逻辑日志，可以认为当 delete 一条记录时，undo log 中会记录一条对应的 insert 记录，反之亦然，当 update 一条记录时，它记录一条对应相反的 update 记录，当执行 rollback 时，就可以从 undo log 中的逻辑记录读取到相应的内容并进行回滚。undo log 默认存放在共享表空间中，在 ySQL 5.6 中，undo log 的存放位置还可以通过变量 innodbundodirectory 来自定义存放目录，默认值为“.”表示 datadir 目录。

⑥ bin log（二进制日志）：是一个二进制文件，主要记录所有数据库表结构变更，比如，CREATE、ALTER TABLE 等，以及表数据修改，比如，INSERT、UPDATE、DELETE 的所有操作，bin log 中记录了对 MySQL 数据库执行更改的所有操作，并且记录了语句发生时间、执行时长、操作数据等其它额外信息，但是它不记录 SELECT、SHOW 等那些不修改数据的 SQL 语句。

binlog 的作用如下：

• 恢复（recovery）：某些数据的恢复需要二进制日志。比如，在一个数据库全备文件恢复后，用户可以通过二进制日志进行 point-in-time 的恢复；
• 复制（replication）：其原理与恢复类似，通过复制和执行二进制日志使一台远程的 MySQL 数据库（一般称为 slave 或者 standby）与一台 MySQL 数据库（一般称为 master 或者 primary）进行实时同步；
• 审计（audit）：用户可以通过二进制日志中的信息来进行审计，判断是否有对数据库进行注入攻击。

除了上面介绍的几个作用外，binlog 对于事务存储引擎的崩溃恢复也有非常重要的作用，在开启 binlog 的情况下，为了保证 binlog 与 redo 的一致性，MySQL 将采用事务的两阶段提交协议。当 MySQL 系统发生崩溃时，事务在存储引擎内部的状态可能为 prepared（准备状态）和 commit（提交状态）两种，对于 prepared 状态的事务，是进行提交操作还是进行回滚操作，这时需要参考 binlog，如果事务在 binlog 中存在，那么将其提交；如果不在 binlog 中存在，那么将其回滚，这样就保证了数据在主库和从库之间的一致性。

binlog 默认是关闭状态，可以在 MySQL 配置文件（my.cnf）中通过配置参数 log-bin = [base-name] 开启记录 binlog 日志，如果不指定 base-name，则默认二进制日志文件名为主机名，并以自增的数字作为后缀，比如：mysql-bin.000001，所在目录为数据库所在目录（datadir）。

通过以下命令来查询 binlog 是否开启：

show variables like 'log_%';

binlog 格式分为: STATEMENT、ROW 和 MIXED 三种：

• STATEMENT 格式的 binlog 记录的是数据库上执行的原生 SQL 语句。这种格式的优点是简单，简单地记录和执行这些语句，能够让主备保持同步，在主服务器上执行的 SQL 语句，在从服务器上执行同样的语句。另一个好处是二进制日志里的时间更加紧凑，所以相对而言，基于语句的复制模式不会使用太多带宽，同时也节约磁盘空间。并且通过 mysqlbinlog 工具容易读懂其中的内容。缺点就是同一条 SQL 在主库和从库上执行的时间可能稍微或很大不相同，因此在传输的二进制日志中，除了查询语句，还包括了一些元数据信息，如当前的时间戳。即便如此，还存在着一些无法被正确复制的 SQL。比如，使用 INSERT INTO TB1 VALUE(CUURENT_DATE()) 这一条使用函数的语句插入的数据复制到当前从服务器上来就会发生变化，存储过程和触发器在使用基于语句的复制模式时也可能存在问题；另外一个问题就是基于语句的复制必须是串行化的，比如：InnoDB 的 next-key 锁等，并不是所有的存储引擎都支持基于语句的复制；

• ROW 格式是从 MySQL 5.1 开始支持基于行的复制，也就是基于数据的复制，基于行的更改。这种方式会将实际数据记录在二进制日志中，它有其自身的一些优点和缺点，最大的好处是可以正确地复制每一行数据，一些语句可以被更加有效地复制，另外就是几乎没有基于行的复制模式无法处理的场景，对于所有的 SQL 构造、触发器、存储过程等都能正确执行；它的缺点就是二进制日志可能会很大，而且不直观，所以，你不能使用 mysqlbinlog 来查看二进制日志，也无法通过看二进制日志判断当前执行到那一条 SQL 语句。现在对于 ROW 格式的二进制日志基本是标配了，主要是因为它的优势远远大于缺点，并且由于 ROW 格式记录行数据，所以可以基于这种模式做一些 DBA 工具，比如数据恢复，不同数据库之间数据同步等；

• MIXED 也是 MySQL 默认使用的二进制日志记录方式，但 MIXED 格式默认采用基于语句的复制，一旦发现基于语句的无法精确的复制时，就会采用基于行的复制。比如用到 UUID()、USER()、CURRENTUSER()、ROWCOUNT() 等无法确定的函数。

56.什么是脏页和干净页？

MySQL 为了操作的性能优化，会把数据更新先放入内存中，之后再统一更新到磁盘。当内存数据和磁盘数据内容不一致的时候，我们称这个内存页为脏页；内存数据写到磁盘后，内存的数据和磁盘上的内容就一致了，我们称为“干净页”。

57.什么情况下会引发 MySQL 刷脏页（flush）的操作？

• 内存写满了，这个时候就会引发 flush 操作，对应到 InnoDB 就是 redo log 写满了；
• 系统的内存不足了，当需要新的内存页的时候，就会淘汰一些内存页，如果淘汰的是脏页这个时候就会触发 flush 操作；
• 系统空闲的时候，MySQL 会同步内存中的数据到磁盘也会触发 flush 操作；
• MySQL 服务关闭的时候也会刷脏页，触发 flush 操作。

58.MySQL 刷脏页的速度很慢可能是什么原因？

在 MySQL 中单独刷一个脏页的速度是很快的，如果发现刷脏页的速度很慢，说明触发了 MySQL 刷脏页的“连坐”机制，MySQL 的“连坐”机制是指当 MySQL 刷脏页的时候如果发现相邻的数据页也是脏页也会一起刷掉，而这个动作可以一直蔓延下去，这就是导致 MySQL 刷脏页慢的原因了。

59.如何控制 MySQL 只刷新当前脏页？

在 InnoDB 中设置 innodbflushneighbors 这个参数的值为 0，来规定 MySQL 只刷当前脏页，MySQL 8 这个值默认是 0。

60.MySQL 的 WAL 技术是解决什么问题的？

A.防止误删除，找回数据用的 B.容灾恢复，为了还原异常数据用的 C.事务处理，为了数据库的稳定性 D.为了降低 IO 成本 答：D 题目解析：WAL 技术的全称是 Write Ahead Logging（中文：预写式日志），是先写日志，再写磁盘的方式，因为每次更新都写磁盘的话 IO 成本很高，所以才有了 WAL 技术。

61.为什么有时候会感觉 MySQL 偶尔卡一下？

如果偶尔感觉 MySQL 卡一下，可能是 MySQL 正在刷脏页，正在把内存中的更新操作刷到磁盘中。

62.redo log 和 binlog 是怎么关联的?

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

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

63.MySQL 怎么知道 binlog 是完整的?

• statement 格式的 binlog，完整的标识是最后有 COMMIT 关键字。
• row 格式的 binlog，完整的标识是最后会有一个 XID event 关键字。

64.MySQL 中可不可以只要 binlog，不要 redo log？

不可以，binlog 没有崩溃恢复的能力。

65.MySQL 中可不可以只要 redo log，不要 binlog？

不可以，原因有以下两个：

• redo log 是循环写不能保证所有的历史数据，这些历史数据只能在 binlog 中找到；
• binlog 是高可用的基础，高可用的实现原理就是 binlog 复制。

66.为什么 binlog cache 是每个线程自己维护的，而 redo log buffer 是全局共用的？

因为 binlog 是不能“被打断的”，一个事务的 binlog 必须连续写，因此要整个事务完成后，再一起写到文件里。而 redo log 并没有这个要求，中间有生成的日志可以写到 redo log buffer 中，redo log buffer 中的内容还能“搭便车”，其他事务提交的时候可以被一起写到磁盘中。

67.在 MySQL 中用什么机制来优化随机读/写磁盘对 IO 的消耗？

redo log 是用来节省随机写磁盘的 IO 消耗，而 change buffer 主要是节省随机读磁盘的 IO 消耗。redo log 会把 MySQL 的更新操作先记录到内存中，之后再统一更新到磁盘，而 change buffer 也是把关键查询数据先加载到内存中，以便优化 MySQL 的查询。

参考
极客时间 《MySQL 实战 45 讲》
GitChat 《程序员的 MySQL 面试经典》