【9. Mysql常见面试题(三)】

说几条 MySQL 对 SQL 的执行做的优化手段

​ 1、对 SQL 语句的优化，MySQL 会对我们的 SQL 语句做重写，包括条件化简， 比如常量传递、除没用的条件等等；还会将一些外连接转换为内连接，然后选择 成本最低的方式执行；对 IN 子查询会进行物化、物化表转连接查询、转换为半连接等方式进行。

​ 2、在 SQL 语句的执行过程中，MySQL 引入了索引条件下推。比如 where 后面的多个搜索条件都使用到了一个二级索引列，这些搜索条件中虽然出现了索 引列，有些却不能使用到索引，像 like ‘%…’查询，MySQL 为了避免不必要的回表， 从二级索引取得的索引记录，先做条件判断，如果条件不满足，则该二级索引记 录不会去回表，这样可以大量的减少回表操作的随机 IO 成本。

​ 3、在回表操作上，因为每次执行回表操作时都相当于要随机读取一个聚簇 索引页面，而这些随机 IO 带来的性能开销比较大。MySQL 中提出了一个名为 Disk-Sweep Multi-Range Read (MRR，多范围读取)的优化措施，即先读取一部分二 级索引记录，将它们的主键值排好序之后再统一执行回表操作。

​ 4、MySQL 在一般情况下执行一个查询时最多只会用到单个二级索引，但存在有特殊情况，也可能在一个查询中使用到多个二级索引，称之为：索引合并， 比如 Intersection 交集合并、Union 索引合并等等

InnoDB 引擎的三大特性是什么？

​ InnoDB 的三大特性是：Buffer Pool、自适应 Hash 索引、双写缓冲区。

​ 自适应 Hash 索引，InnoDB 存储引擎内部自己去监控索引表，如果监控到某 个索引经常用，那么就认为是热数据，然后内部自己创建一个 hash 索引，称之 为自适应哈希索引( Adaptive Hash Index,AHI)，创建以后，如果下次又查询到 这个索引，那么直接通过 hash 算法推导出记录的地址，直接一次就能查到数据。

​ InnoDB 存储引擎使用的哈希函数采用除法散列方式，其冲突机制采用链表 方式。

​ Buffer Pool，为了提高访问速度，MySQL 预先就分配/准备了许多这样的空间，为的就是与 MySQL 数据文件中的页做交换，来把数据文件中的页放到事先 准备好的内存中。数据的访问是按照页（默认为 16KB）的方式从数据文件中读lea取到 buffer pool 中。Buffer Pool 按照最少使用算法（LRU），来管理内存中的页。(LRU: 最近最少使用算法, LFU: 最近最少次使用算法)

​ Buffer Pool 实例允许有多个，每个实例都有一个专门的 mutex 保护。Buffer Pool 中缓存的数据页类型有: 索引页、数据页、undo 页、插入缓冲（insert buffer)、 自适应哈希索引、InnoDB 存储的锁信息、数据字典信息（data dictionary)等等。

​ 双写缓冲区，是一个位于系统表空间的存储区域，在写入时，InnoDB 先把 从缓冲池中的得到的 page 写入系统表空间的双写缓冲区。之后，再把 page 写 到.ibd 数据文件中相应的位置。如果在 page 写入数据文件的过程中发生意外崩 溃，InnoDB 在稍后的恢复过程中在 doublewrite buffer 中找到完好的 page 副本用 于恢复。

​ doublewrite 是顺序写，开销比较小。所以在正常的情况下, MySQL 写数据 page 时，会写两遍到磁盘上，第一遍是写到 doublewrite buffer，第二遍是从 doublewrite buffer 写到真正的数据文件中。

​ 它的主要作用是为了避免 partial page write（部分页写入）的问题。因为 InnoDB 的 page size 一般是 16KB，校验和写入到磁盘是以 page 为单位进行的。 而操作系统写文件是以 4KB 作为单位的，每写一个 page，操作系统需要写 4 个 块，中间发生了系统断电或系统崩溃，只有一部分页面是写入成功的。这时 page 数据出现不一样的情形，从而形成一个"断裂"的 page，使数据产生混乱。

redolog 和 binlog 的区别是什么？

参考下一题

MySQL 崩溃后的恢复为什么不用 binlog？

​ 1、这两者使用方式不一样

​ binlog 会记录表所有更改操作，包括更新删除数据，更改表结构等等，主要 用于人工恢复数据，而 redo log 对于我们是不可见的，它是 InnoDB 用于保证 crash-safe 能力的，也就是在事务提交后 MySQL 崩溃的话，可以保证事务的持久性，即事务提交后其更改是永久性的。

​ 一句话概括：binlog 是用作人工恢复数据，redo log 是 MySQL 自己使用， 用于保证在数据库崩溃时的事务持久性。

​ 2、redo log 是 InnoDB 引擎特有的，binlog 是 MySQL 的 Server 层实现的, 所有引擎都可以使用。

​ 3、redo log 是物理日志，记录的是“在某个数据页上做了什么修改”，恢复 的速度更快；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这的 c 字段加 1 ”，binlog 有三种日志记录格式 Row、SQL、混合模式。

​ 4、redo log 是“循环写”的日志文件，redo log 只会记录未刷盘的日志，已 经刷入磁盘的数据都会从 redo log 这个有限大小的日志文件里删除。binlog 是 追加日志，保存的是全量的日志。

​ 5、最重要的是，当数据库 crash 后，想要恢复未刷盘但已经写入 redo log 和 binlog 的数据到内存时，binlog 是无法恢复的。虽然 binlog 拥有全量的日志， 但没有一个标志让 innoDB 判断哪些数据已经入表(写入磁盘)，哪些数据还没有。

​ redo log 不一样，只要刷入磁盘的数据，都会从 redo log 中抹掉，数据 库重启后，直接把 redo log 中的数据都恢复至内存就可以了。

MySQL 如何实现事务的 ACID？

参考下一题

InnoDB 事务是如何通过日志来实现的？

​ 总的来说，事务的原子性是通过 undo log 来实现的，事务的持久性性是通 过 redo log 来实现的，事务的隔离性是通过读写锁+MVCC 来实现的。

​ 事务的一致性通过原子性、隔离性、持久性来保证。也就是说 ACID 四大特 性之中，C(一致性)是目的，A(原子性)、I(隔离性)、D(持久性)是手段，是为了保 证一致性(所以也经常有人称为CAID)，数据库提供的手段。数据库必须要实现 AID 三大特性，才有可能实现 一致性。同时一致性也需要应用程序的支持，应用程序在事务里故意写出违反约 束的代码，一致性还是无法保证的，例如，转账代码里从 A 账户扣钱而不给 B 账户加钱，那一致性还是无法保证。

​ 至于 InnoDB 事务是如何通过日志来实现的，简单来说，因为事务在修改页 时，要先记 undo，在记 undo 之前要记 undo 的 redo， 然后修改数据页，再 记数据页修改的 redo。 Redo（里面包括 undo 的修改） 一定要比数据页先持 久化到磁盘。

​ 当事务需要回滚时，因为有 undo，可以把数据页回滚到前镜像的状态，崩 溃恢复时，如果 redo log 中事务没有对应的 commit 记录，那么需要用 undo把该事务的修改回滚到事务开始之前。如果有 commit 记录，就用 redo 前滚到 该事务完成时并提交掉

​ 更详细的回答是：

​ redo 通常是物理日志，记录的是页的物理修改操作，用来恢复提交事务修 改的页操作。而 undo 是逻辑日志，根据每行记录进行记录，用来回滚记录到某 个特定的版本。

​ 当事务提交之后会把所有修改信息都会存到 redo 日志中。redo 日志由两部 分组成，一个是在内存里的 redo log buffer，另一个是在磁盘里的 redo log 文件。

​ mysql 为了提升性能不会把每次的修改都实时同步到磁盘，而是会先存到 Buffer Pool(缓冲池)里头，把这个当作缓存来用。然后使用后台线程去做缓冲池 和磁盘之间的同步。

​ 系统重启后读取 redo log 恢复最新数据。虽然 redo log 会在事务提交前做一 次磁盘写入，但是这种 IO 操作相比于 buffer pool 这种以页（16kb）为管理单位 的随机写入，它做的是几个字节的顺序写入，效率要高得多。

​ redo log buffer 中的数据，会在一个合适的时间点刷入到磁盘中。

​ 这个合适的时间点包括：

​ 1、MySQL 正常关闭的时候；

​ 2、MySQL 的后台线程每隔一段时间定时的将 redo log buffer 刷入到磁盘， 默认是每隔 1s 刷一次；(定时)

​ 3、当 redo log buffer 中的日志写入量超过 redo log buffer 内存的一半时， 即超过 8MB 时，会触发 redo log buffer 的刷盘；(定量)

​ 4、当事务提交时，根据配置的参数 innodb_flush_log_at_trx_commit 来决定是否刷盘。要严格保证数据不丢失，必须得保证 innodb_flush_log_at_trx_commit 配置为 1。

​ redo log 在进行数据重做时，只有读到了 commit 标识，才会认为这条 redo log 日志是完整的，才会进行数据重做，否则会认为这个 redo log 日志不完整， 不会进行数据重做。

​ undo log 和 redo log 记录物理日志不一样，它是逻辑日志。可以认为当 delete 一条记录时，undo log 中会记录一条对应的 insert 记录(反向操作, 如果update则依然是update)，反之亦然，当 update 一条记录时，它记录一条对应相反的 update 记录。当执行回滚时，就可以从 undo log 中的逻辑记录读取到相应的内容并进行回滚。

​ 而事务的隔离性，也可以通过 undo log 来实现的：当读取的某一行被其他事务锁定时(行读锁)，它可以从 undo log 中分析出该行记录以前的数据是什么，从而提供该 行版本信息，帮助用户实现一致性非锁定读取，这也是 MVCC 的实现机制的组成 部分。

什么是当前读和快照读？

当前读

​ 像 select lock in share mode(共享锁), select for update ; update, insert ,delete(排他锁)这些操作都是一种当前读，就是它读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。是 一种悲观锁的实现。

快照读

​ 像不加锁的 select 操作就是快照读，即不加锁的非阻塞读；快照读的前提是 隔离级别不是串行级别，串行级别下的快照读会退化成当前读；之所以出现快照 读的情况，是基于提高并发性能的考虑，快照读的实现是基于多版本并发控制， 即 MVCC。

什么是 MVCC？

​ MVCC (Multi-Version Concurrency Control) ，叫做基于多版本的并发控制协议。 他是和 LBCC（Lock-Based Concurrency Control）基于锁的并发控制概念是相对的。 MVCC 是乐观锁的一种实现方式，它在很多情况下，避免了加锁操作，降低了开 销；既然是基于多版本，即快照读可能读到的并不一定是数据的最新版本，而有 可能是之前的历史版本。

​ MVCC 最大的好处：**读不加锁，读写不冲突。**在读多写少的 OLTP 应用中， 读写不冲突是非常重要的，极大的增加了系统的并发性能，现阶段几乎所有的 R-DBMS 包括 MySQL，都支持了 MVCC。

MVCC 的底层实现原理是什么？

​ MVCC 实现原理主要是依赖记录中的隐式字段，undo 日志 ，Read View 来 实现的。

​ MySQL 中每行记录除了我们自定义的字段外，还有数据库隐式定义的 DB_TRX_ID,DB_ROLL_PTR,DB_ROW_ID 等字段。DB_TRX_ID 是最近修改(修改/插入) 事务 ID，记录创建这条记录/最后一次修改该记录的事务 ID。DB_ROLL_PTR，回 滚指针，用于配合 undo 日志，指向这条记录的上一个版本。

​ 不同事务或者相同事务的对同一记录的修改，会导致该记录的 undo log 成为 一条记录版本线性表，也就是版本链。

​ 事务进行快照读操作的时候产生一个 Read View，记录并维护系统当前活跃 事务的 ID，因为当每个事务开启时，都会被分配一个 ID, 这个 ID 是递增的，所 以最新的事务，ID 值越大。

​ Read View 主要是将要被修改的数据的最新记录中的 DB_TRX_ID（即当前事 务 ID）取出来(如果当前没有事务就是一个0)，与系统当前其他活跃事务的 ID 去对比（由 Read View 维护）， 如果 DB_TRX_ID 跟 Read View 的属性做了某些比较，不符合可见性，那就通过 DB_ROLL_PTR 回滚指针去取出 Undo Log 中的 DB_TRX_ID 再比较，即遍历链表的 DB_TRX_ID（从链首到链尾，即从最近的一次修改查起），直到找到满足特定条 件的 DB_TRX_ID, 那么这个 DB_TRX_ID 所在的旧记录就是当前事务能看见的最新 老版本。

​ RC,RR 级别下 Read View 生成时机的不同，造成 RC,RR 级别下快照读的结果 的不同。RC 隔离级别下，是每个快照读都会生成并获取最新的 Read View，也就 是说事务中，每次快照读都会新生成一个快照和 Read View, 这就是我们在 RC 级 别下的事务中可以看到别的事务提交的更新的原因；而在 RR 隔离级别下，则是 同一个事务中的第一个快照读才会创建 Read View, 之后的快照读获取的都是同一个 Read View，快照读生成 Read View 时，Read View 会记录此时所有其他活动 事务的快照，这些事务的修改对于当前事务都是不可见的。而早于 Read View 创 建的事务所做的修改均是可见。

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

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

什么是全局锁、共享锁、排它锁？

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

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

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

MySQL 中的表锁有哪些？

​ MySQL 里表级锁有两种：**普通表级锁、元数据锁（**meta data lock）简称 MDL 和 AUTO-INC 锁。表锁的语法是 lock tables t read/write。

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

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

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

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

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

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

​ AUTO-INC 锁，也就是在执行插入语句时就在表级别加一个 AUTO-INC 锁(所以我们一般使用自己的id生成策略)，然 后为每条待插入记录的 AUTO_INCREMENT 修饰的列分配递增的值。

InnoDB 引擎的行锁是怎么实现的？

​ InnoDB 是基于索引来完成行锁，在锁的算法实现上有三种：

• Record lock：单个行记录上的锁

• Gap lock：间隙锁，锁定一个范围，不包括记录本身

• Next-key lock：record+gap 锁定一个范围，包含记录本身

​ Gap 锁设计的目的是为了阻止多个事务将记录插入到同一范围内，而这会导 致幻读问题的产生，innodb 对于行的查询使用 next-key lock，Next-locking keying 是 Record lock 和 Gap lock 的组合。当查询的索引含有唯一属性时，将 next-key lock 降级为 record key。

​ 有两种方式显式关闭 gap 锁 ，第一种. 将事务隔离级别设置为 RC ；第二种. 将参数 innodb_locks_unsafe_for_binlog 设置为 1。

谈一下 MySQL 中的死锁

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

​ 如何查看死锁？

​ 使用命令 show engine innodb status 查看最近的一次死锁。

​ InnoDB Lock Monitor 打开锁监控，每 15s 输出一次日志。使用完毕后建议 关闭，否则会影响数据库性能。

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

​ 通过 innodblockwait_timeout 来设置超时时间，一直等待直到超时；

​ 发起死锁检测，发现死锁之后，主动回滚死锁中的某一个事务，让其它事务 继续执行。

简述下 MySQL8 中的新增特性有哪些

​ MySQL8 在功能上的我们需要关注增强主要有：1、账户与安全；2、索引 3、 InnoDB 增强。

​ 主要表现在：

​ 1、用户的创建与授权需要两条单独的 SQL 语句执行。认证插件更新。密码 管理和角色管理发生变化。

​ 2、隐藏索引，被隐藏的索引不会被优化器使用，但依然真实存在，主要用 于软删除，可以根据需要后续真正删除或者重新可视化。

​ 开始真正支持降序索引，以往的 MySQL 虽然支持降序索引，但是写盘的时 候依然是升序保存。MySQL8.0 中则是真正的按降序保存。

​ 不再对 group by 操作进行隐式排序。

​ 索引中可以使用函数表达式，创建表时创建一个函数索引，查询的时候使用 同样的函数就可以利用索引了。

​ 3、原子 ddl 操作，MySQL5.7 执行 drop 命令 drop table t1,t2; 如果 t1 存在， t2 不存在，会提示 t2 表不存在，但是 t1 表仍然会被删除，MySQL8.0 执行同样 的 drop 命令，会提示 t2 表不存在，而且 t1 表不会被删除，保证了原子性。

​ 自增列持久化，解决了之前的版本，主键重复的问题。MySQL5.7 及其以前 的版本，MySQL 服务器重启，会重新扫描表的主键最大值，如果之前已经删除过 id=100 的数据，但是表中当前记录的最大值如果是 99，那么经过扫描，下一条 记录的 id 是 100，而不是 101。MySQL8.0 则是每次在变化的时候，都会将自增 计数器的最大值写入 redo log，同时在每次检查点将其写入引擎私有的系统表。 则不会出现自增主键重复的问题。