语句执行过程

1.MySQL的框架有几个组件, 各是什么作用?

连接器：负责跟客户端建立连接、获取权限、维持和管理连接。
查询缓存：查询请求先访问缓存(key 是查询的语句，value 是查询的结果)。命中直接返回。不推荐使用缓存，更新会把缓存清除(关闭缓存：参数 query_cache_type 设置成 DEMAND)。
分析器：对 SQL 语句做解析，判断sql是否正确。
优化器：决定使用哪个索引，多表关联（join）的时候，决定各个表的连接顺序。
执行器：执行语句，先判断用户有无查询权限，使用表定义的存储引擎。

2.Server层和存储引擎层各是什么作用?

Server 层包括连接器、查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。
存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始成为了默认存储引擎。

3.you have an error in your SQL syntax 这个保存是在词法分析里还是在语法分析里报错?

语法分析

4.对于表的操作权限验证在哪里进行?

执行器

5.什么是WAL?

WAL 的全称是 Write-Ahead Logging，它的关键点就是先写日志，再写磁盘.
当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log里面，并更新内存，这个时候更新就算完成了。同时，InnoDB 引擎会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做

6.redo log相关点(InnoDB特有)

InnoDB 的 redo log 是固定大小的，比如可以配置为一组 4 个文件，每个文件的大小是 1GB，那么总共就可以记录 4GB 的操作。从头开始写，写到末尾就又回到开头循环写.
write pos 是当前记录的位置，一边写一边后移，写到第 3 号文件末尾后就回到 0 号文件开头。checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件。
write pos 和 checkpoint 之间的是还空着的部分，可以用来记录新的操作。如果 write pos 追上 checkpoint，表示redo log满了，这时候不能再执行新的更新，得停下来先擦掉一些记录，把 checkpoint 推进一下。有了 redo log，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为 crash-safe。

redo log执行过程示意图.png

7.bin log(Server层, 归档日志)

• 7.1 binlog的格式有哪几种？
• 1.statement
  binlog 里面记录的就是 SQL 语句的原文
• 2.row （推荐）
  记录的是操作的说明, 但是记录每一行的操作说明占用空间大且影响性能
• 3.mixed
  自动分类操作，记录为合适的类型

8.为什么会有两种日志

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

9.binlog 和 redo log的区别在哪?

• 1.redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。
• 2.redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。
• 3.redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

10.InnoDB执行时的内部流程

• 1.执行器先找引擎取 ID=2 这一行。ID 是主键，引擎直接用树搜索找到这一行。如果 ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。
• 2.执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的一行数据，再调用引擎接口写入这行新数据。
• 3.引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。
• 4.执行器生成这个操作的 binlog，并把 binlog 写入磁盘。

• 5.执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成。

  
  
  执行过程图示

11.什么是日志的两阶段提交?

将 redo log 的写入拆成了两个步骤：prepare 和 commit，这就是"两阶段提交"。

• 11.1 当日志写入lodolog之后，还未写入binlog，数据库crash，如何保证数据的一致性？
  binlog 还没写，redo log 也还没提交，所以崩溃恢复的时候，这个事务会回滚。这时候，binlog 还没写，所以也不会传到备库。
• 11.2 日志刚写入binlog但是还未提交，发生crash？
  如果 redo log 里面的事务是完整的，也就是已经有了 commit 标识，则直接提交；
  如果 redo log 里面的事务只有完整的 prepare，则判断对应的事务 binlog 是否存在并完整：
  a. 如果是，则提交事务；b. 否则，回滚事务。
• 11.3binlog的写入机制
  事务执行过程中，先把日志写到 binlog cache，事务提交的时候，再把 binlog cache 写到 binlog 文件中。

12.事务的特性?

ACID（Atomicity、Consistency、Isolation、Durability，即原子性、一致性、隔离性、持久性）。

13.事务的隔离级别?

• 读未提交(read uncommitted）是指，一个事务还没提交时，它做的变更就能被别的事务看到。
• 读提交(read committed) => rc是指，一个事务提交之后，它做的变更才会被其他事务看到。Oracle, SQL Server默认隔离级别
• 可重复读(repeatable read) => rr是指，一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。MySQL默认的隔离级别
• 串行化(serializable)，顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。
• 13.1 rr下会产生幻读，那么是如何解决幻读的？
  间隙锁：跟间隙锁存在冲突关系的，是“往这个间隙中插入一个记录”这个操作。间隙锁之间不存在冲突关系。

14. RC和RR的实现方式?

• “可重复读”隔离级别下，这个视图是在事务启动时创建的，整个事务存在期间都用这个视图。
• “读提交”隔离级别下，这个视图是在每个 SQL 语句开始执行的时候创建的。
• “读未提交”隔离级别下直接返回记录上的最新值，没有视图概念；
• “串行化”隔离级别下直接用加锁的方式来避免并行访问。

15.事务隔离的实现?

锁

• 15.1原子性的实现
  undo log: 每条记录在更新的时候都会同时记录一条回滚操作。记录上的最新值，通过回滚操作，都可以得到前一个状态的值。

16. 事务的启动方式?

• 1.显式的启动 begin 或 start transaction。配套的提交语句是 commit，回滚语句是 rollback。
• 2. set autocommit=0，这个命令会将这个线程的自动提交关掉。意味着如果你只执行一个 select 语句，这个事务就启动了，而且并不会自动提交。这个事务持续存在直到你主动执行 commit 或 rollback 语句，或者断开连接。(推荐设置为1)

17.三种常见的索引结构?

• 1.哈希表
  哈希表: 是一种以键 - 值（key-value）存储数据的结构，我们只要输入待查找的键即 key，就可以找到其对应的值即 Value。哈希的思路很简单，把值放在数组里，用一个哈希函数把 key 换算成一个确定的位置，然后把 value 放在数组的这个位置。
  局限性：哈希表并不是有序的, 所以只适用于只有等值查询的场景.(新建数据时只需要往后添加就可以,添加速度比较快)
  问题：不可避免地，多个 key 值经过哈希函数的换算，会出现同一个值的情况.(哈希碰撞)
  解决：处理这种情况的一种方法是，拉出一个链表。
  例子:

  
  
  哈希表举例
  
  

  图中，User2 和 User4 根据身份证号算出来的值都是 N，但没关系，后面还跟了一个链表。假设，这时候你要查 ID_card_n2 对应的名字是什么，处理步骤就是：首先，将 ID_card_n2 通过哈希函数算出 N；然后，按顺序遍历，找到 User2。

• 2.有序数组
  按照值的大小进行排序,查找时用二分法可以快速得到,但是更新数据时需要将插入位置后面的元素全都移动,成本太高,比较适用于静态数据.
• 3.搜索树
  每个节点的左儿子小于父节点，父节点又小于右儿子. innoDB的索引结构即为B+树

18.根据叶子节点内容对索引进行分类

• 1.主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引（clustered index）。
• 2.非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引（secondary index）。
• Tips:非主键索引存放值而不是地址，是为了防止出现主键插入或页分裂等情况需要重建时非主键也要跟着重建。
  
  B+索引图示

19.什么是页分裂、页合并?

B+ 树为了维护索引有序性，在插入新值的时候需要做必要的维护。以上面这个图为例，如果插入新的行 ID 值为 700，则只需要在 R5 的记录后面插入一个新记录。如果新插入的 ID 值为 400，就相对麻烦了，需要逻辑上挪动后面的数据，空出位置。而更糟的情况是，如果 R5 所在的数据页已经满了，根据 B+ 树的算法，这时候需要申请一个新的数据页，然后挪动部分数据过去。这个过程称为页分裂。
在这种情况下，性能自然会受影响。除了性能外，页分裂操作还影响数据页的利用率。原本放在一个页的数据，现在分到两个页中，整体空间利用率降低大约50%。
当相邻两个页由于删除了数据，利用率很低之后，会将数据页做合并。合并的过程，可以认为是分裂过程的逆过程。

20.基于主键索引和普通索引的查询有什么区别， 什么是回表？

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

21.为什么推荐用自增主键做索引？

• 1.主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。
• 2.每次插入一条新记录，都是追加操作，都不涉及到挪动其他记录，也不会触发叶子节点的分裂。

22.什么是索引覆盖?

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

23.什么是最左前缀原则？

MySQL中的索引可以以一定顺序引用多列，这种索引叫作联合索引。如User表的name和city加联合索引就是(name,city)，而最左前缀原则指的是，如果查询的时候查询条件精确匹配索引的左边连续一列或几列，则此列就可以被用到。

24.什么是索引下推？

索引下推（index condition pushdown) => ICP， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

25.如何重建索引?

mysql> alter table T engine=InnoDB;

26.什么是索引扩展？

MySQL InnoDB的二级索引（Secondary Index）会自动补齐主键，将主键列追加到二级索引列后面。详细一点来说，InnoDB的二级索引（Secondary Index）除了存储索引列key值，还存储着主键的值。
如存在主键索引 k_i1_i2, 二级索引 k_d ,二级索引k_d（d）的元组在InnoDB内部实际被扩展成（d,i1,i2），即包含主键值。
因此在设计主键的时候，常见的一条设计原则是要求主键字段尽量简短，以避免二级索引过大(因为二级索引会自动补齐主键字段)

27. 根据加锁范围，锁可以分为哪几类？

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

• 加锁规则:
  
  加锁规则

28. 全局锁是做什么的?

全局锁就是对整个数据库实例加锁，整个数据库处在只读状态，MySQL 提供了一个加全局读锁的方法，命令是 Flush tables with read lock (FTWRL)
全局锁的典型使用场景是，做全库逻辑备份。也就是把整库每个表都 select 出来存成文本。

29.MySQL有自带的逻辑备份工具，为什么还存在FTWRL？

官方自带的逻辑备份工具是 mysqldump。当 mysqldump 使用参数–single-transaction 的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。而由于 MVCC 的支持，这个过程中数据是可以正常更新的。
single-transaction 方法只适用于所有的表使用事务引擎的库。如果有的表使用了不支持事务的引擎，那么备份就只能通过 FTWRL 方法.

30.同样是只读，为什么不用set global readonly=true？

• 在有些系统中，readonly 的值会被用来做其他逻辑，比如用来判断一个库是主库还是备库。
• 在 slave 上 如果用户有超级权限的话 readonly 是失效的
• 如果执行 FTWRL 命令之后由于客户端发生异常断开，那么 MySQL 会自动释放这个全局锁，整个库回到可以正常更新的状态。而将整个库设置为 readonly 之后，如果客户端发生异常，则数据库就会一直保持 readonly 状态，这样会导致整个库长时间处于不可写状态，风险较高。

31.表级锁介绍？

MySQL 里面表级别的锁有两种：一种是表锁，一种是元数据锁（meta data lock，MDL)。

• MDL不需要显式的增加；
• MDL作用是防止DDL和DML并发的冲突
• 当对一个表做增删改查操作的时候，加 MDL 读锁, 当要对表做结构变更操作的时候，加 MDL 写锁。读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。
• 事务中的MDL锁会一直持续到事务结束才会释放

32.什么是两段锁协议？

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

33.什么是死锁？

当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态，称为死锁.

34.在RR的情况下，事务每次读取的都是快照的数据，那么其他事务更新后，当前事务再次更新时读取的数据时什么?

更新数据都是先读后写的，而这个读，只能读当前的值，称为“当前读”（current read）。

35.MVVC的规则?

• 1.版本未提交，不可见；
• 2.版本已提交，但是是在视图创建后提交的，不可见；
• 3.版本已提交，而且是在视图创建前提交的，可见。

36.RC, RR的一致性视图有什么区别？

• 在可重复读隔离级别下，只需要在事务开始的时候创建一致性视图，之后事务里的其他查询都共用这个一致性视图；
• 在读提交隔离级别下，每一个语句执行前都会重新算出一个新的视图。

37. 数据的更新过程？

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

38.什么情况下change buffer中的数据会写入到磁盘？

• 将 change buffer 中的操作应用到原数据页，得到最新结果的过程称为 merge。
• 1.访问这个数据页会触发 merge
• 2.系统有后台线程会定期 merge
• 3.在数据库正常关闭（shutdown）的过程中，也会执行 merge 操作。

39.什么条件下可以使用 change buffer ？

对于唯一索引来说，所有的更新操作都要先判断这个操作是否违反唯一性约束。比如，要插入 (4,400) 这个记录，就要先判断现在表中是否已经存在 k=4 的记录，而这必须要将数据页读入内存才能判断。如果都已经读入到内存了，那直接更新内存会更快，就没必要使用 change buffer 了。
因此，唯一索引的更新就不能使用 change buffer，实际上也只有普通索引可以使用。

40. change buffer和redo log的比较

以该条语句为例子： mysql> insert into t(id,k) values(id1,k1),(id2,k2);
假设k1 所在的数据页在内存 (InnoDB buffer pool) 中，k2 所在的数据页不在内存中。

• 1.Page 1 在内存中，直接更新内存；
• 2.Page 2 没有在内存中，就在内存的 change buffer 区域，记录下“我要往 Page 2 插入一行”这个信息
• 3.将上述两个动作记入 redo log 中。
  redo log 主要节省的是随机写磁盘的 IO 消耗（转成顺序写），而 change buffer 主要节省的则是随机读磁盘的 IO 消耗。

41.索引错用的原因之一

由于索引统计信息不准确导致的问题，可以用 analyze table 来解决。

并发事务索引统计信息不准确的模拟

delete 语句删掉了所有的数据，然后再通过 call idata() 插入了 10 万行数据，看上去是覆盖了原来的 10 万行。但是，session A 开启了事务并没有提交，所以之前插入的 10 万行数据是不能删除的。这样，之前的数据每一行数据都有两个版本，旧版本是 delete 之前的数据，新版本是标记为 deleted 的数据。索引 a 上的数据其实就有两份。不过这个是主键，主键是直接按照表的行数来估计的。而表的行数，优化器直接用的是 show table status 的值。

42.什么是脏页？

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

43.什么时候会刷脏页或者说脏页里的数据会落盘？

• 1.redo log 写满了，要 flush 脏页”，这种情况是 InnoDB 要尽量避免的。因为出现这种情况的时候，整个系统就不能再接受更新了，所有的更新都必须堵住。如果你从监控上看，这时候更新数会跌为 0。
• 2.内存不够用了，要先将脏页写到磁盘，比较常见
• 3.MySQL空闲时
• 4.MySQL关闭时，会把所有数据flush到磁盘。

44. 为什么delete数据之后，表没有变小？

delete 操作只会把对应的位置标记为可复用，但是不会回收空间，所以磁盘文件大小不会变。

45.如何解决44的问题？

可以通过重建表。转存数据、交换表名、删除旧表的操作
可以通过 25 的命令进行重建。

46. 5.6版本online DDL之后重建表的流程?

• 1.建立一个临时文件，扫描表 A 主键的所有数据页；
• 2.用数据页中表 A 的记录生成 B+ 树，存储到临时文件中；
• 3.生成临时文件的过程中，将所有对 A 的操作记录在一个日志文件（row log）中
• 4.临时文件生成后，将日志文件中的操作应用到临时文件，得到一个逻辑数据上与表 A 相同的数据文件
• 5.用临时文件替换表 A 的数据文件。

47. optimize table、analyze table 和 alter table 这三种方式重建表的区别?

• alter就是46的流程
• analyze table t 其实不是重建表，只是对表的索引信息做重新统计，没有修改数据，这个过程中加了 MDL 读锁；
• optimize table t 等于 recreate+analyze

48.为什么MyISAM比InnoDB的count(*)要更快？

• MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count(*) 的时候会直接返回这个数，效率很高；
• 而 InnoDB 引擎就麻烦了，它执行 count(*) 的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。(因为MVVC的原因，不好维护自己的计数器)

49. 索引失效是由什么导致的?

• 1.对索引字段做函数操作
• 2.隐式类型转换(比如 字段 为 varchar 类型，但是传值时传递的为 int 类型)
• 3.隐式字符编码转换(比如做连接查询时是字符集不一致)

50. 主从同步的流程？

主库 ： A 从库 ： B
备库 B 跟主库 A 之间维持了一个长连接。主库 A 内部有一个线程，专门用于服务备库 B 的这个长连接。一个事务日志同步的完整过程是这样的：

• 1.在备库 B 上通过 change master 命令，设置主库 A 的 IP、端口、用户名、密码，以及要从哪个位置开始请求 binlog，这个位置包含文件名和日志偏移量。
• 2.在备库 B 上执行 start slave 命令，这时候备库会启动两个线程，就是图中的 io_thread 和 sql_thread。其中 io_thread 负责与主库建立连接
• 3.主库 A 校验完用户名、密码后，开始按照备库 B 传过来的位置，从本地读取 binlog，发给 B。
• 4.备库 B 拿到 binlog 后，写到本地文件，称为中转日志（relay log）。

• 5.sql_thread 读取中转日志，解析出日志里的命令，并执行。

  
  
  主从同步流程

51.主从切换的过程？

seconds_behind_master: 备用机比主机延迟多少秒

• 1.判断备库 B 现在的 seconds_behind_master，如果小于某个值（比如 5 秒）继续下一步，否则持续重试这一步；（如果值比较大，会导致主备都一直处于只读状态时间较长）
• 2.把主库 A 改成只读状态，即把 readonly 设置为 true；
• 3.判断备库 B 的 seconds_behind_master 的值，直到这个值变成 0 为止；
• 4.把备库 B 改成可读写状态，也就是把 readonly 设置为 false；
• 5.把业务请求切到备库 B。
  以上是在可靠性优先的情况下进行的切换，如果是可用性优先则直接切换，不判断延迟时间

52.可不可以使用join，为什么？

• 如果可以使用 Index Nested-Loop Join 算法，也就是说可以用上被驱动表上的索引，其实是没问题的；
• 如果使用 Block Nested-Loop Join 算法，扫描行数就会过多。尤其是在大表上的 join 操作，这样可能要扫描被驱动表很多次，会占用大量的系统资源。所以这种 join 尽量不要用。
• 使用的什么算法可以从explain的Extra字段看到， 如果要用join，要是用小表做驱动表，大表为被驱动表

53.为什么union all 比union更高效？

union all不需要进行去重

Extra:

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