首页

本文参考网上各种博客以及《MySQL 是怎样运行的》一书，特别声明的是，我并没有看过任何关于 MySQL 的源码，我所见所学，皆是站在前人的基础。

一条 SQL 查询语句在 MySQL 中如何执行的？

算是一道经典面试题了，相当于计网的一条 URL 从输入到界面呈现发生了什么类似。

  1. 先检查语句是否有权限，在 8.0 版本以前，有权限会先检查缓存，但是这并不是一个很好的特性，所以在 8.0 版本后删除了该操作。
  2. 分析器进行词法分析，提取关键字，判断语法。
  3. 执行 sql ：
    1. 预处理阶段：检查查询语句的表、字段是否存在，将 * 扩展为表上所有列。
    2. 优化阶段：优化器生成执行方案
    3. 执行阶段：执行方案交给执行器进行权限校验后执行，返回执行结果。

InnoDB 和 MyISAM 的比较

  • 事务： InnoDB 是事务型的，可以使用 Commit 和 Rollback 。而 MyISAM 不支持事务。
  • 并发： InnoDB 支持行级锁，而 MyISAM 支持表级锁。
  • 外键： InnoDB 支持外键而 MyISAM 不支持
  • 崩溃恢复： InnoDB 崩溃恢复后发生损坏的概率比 MyISAM 高，而且恢复速度也慢。
  • InnoDB 更适合处理事务性应用程序，需要支持外键约束和具有高并发和可靠性； MyISAM 更适合处理只读或只写的应用程序，需要较好的性能和较少的系统资源。

一般如何对 SQL 优化？

这种问题把你能想到的都答上去，但是尽量要有条理。

  1. 设计表方面：尽量减少字符串使用，多用数字类型；使用字符串尽量用 varchar 节省空间。当索引列的重复数据占大多数时，建议删掉索引，因为使用这种索引并不会节省太多时间，相反有时回表太多导致性能下降。
  2. SQL 语句方面：少使用 select * ，尽量避免用 or 连接条件。
  3. 索引方面：对要查询的条件的列， order by 的字段建立索引，不要建立过多的索引，尽量使用组合索引。

怎么看执行计划，里面的字段是什么意思？

用 explain 命令，后面跟查询语句： explain select name from student; 。里面的字段，记不清，但是看到了我知道它代表什么意思。

  • id ：每个 select 语句都会分配一个唯一 id 。
  • select_type ：对应的查询类型，有 simple 、 union 、 primary 等
  • table ：表名
  • partitions ：匹配的分区信息
  • type ：表示这个查询对表执行查询时的访问方法，如 const 、 ref 、 eq_ref 等。
  • possible_key ：表示这个查询可能用到的索引
  • key ：实际用到的索引
  • key_len ：索引中形成的扫描区间和边界条件的列的长度
  • ref ：与索引列进行等值匹配的是什么， const 表示常数， func 表示函数
  • rows ：表示查询的估计行数
  • filtered ：查询优化器预测扇出记录有多少条记录符合其余条件的百分比。
  • extra ：记录一些额外信息，能够更准确理解 MySQL 如何执行给定查询语句。

为什么不用 B 树而是用 B + 树？

B + 树是 B 树的一种优化，B + 树非叶子节点存储的不是数据记录，而是目录记录。因为不存储数据，所以可以存储更多的目录记录，对应的叶子节点就可以存储更多的数据记录，使得整棵树层数更小，一般来说，一棵树都不会超过 4 层，所以查找起来非常快。其次在于所有记录在叶子节点，叶子节点的页连成双向链表，用于做范围查询有天然优势，而 B 树就很难做到。

个人还有个浅显的理解，在增删记录时，B + 树一般修改的只有非叶子节点，以删除一条记录为例，如果删除的记录是该页中键值大小在中间的记录，那么直接删了就行，并不会影响上面的目录记录。但是如果是 B 树，删除的记录在往往会影响下面节点的记录的位置。

什么是最左前缀原则和最左匹配原则？

  • 最左前缀：创建多列索引（组合索引），应该把 where 使用最频繁的列放在索引的最左边。
  • 最左匹配：创建的组合索引 (a,b,c) 相当于创建了索引 (a)、(a,b)、(a,b,c) 三个索引。

索引下推是什么？

MySQL5.6 默认开启的索引下推，在联合索引中，比如是 (a,b) 两个字段，再加上主键。假设有一条 sql 语句的过滤条件涉及到 a、b 两个，那么索引下推开启后，存储引擎先找到所有符合关于 a 条件的数据，再根据索引中已经存在的 b 进行过滤。找到符合条件的数据，最后在回表查询。

参考链接：https://mp.weixin.qq.com/s/87qsrj-_hG54uxcOlFr35Q

MySQL 调优你一般从哪些方面入手？

  • 查询 explain 执行计划
  • 索引的建立一定要合适，尽量使索引满足最左前缀原则，查询过程中尽量触发索引覆盖和索引下推。
  • 在读多写少的业务场景中，建立普通索引使用 change buffer 减少 IO 耗时。唯一索引并不能走 change buffer ，因为更新操作需要判断是否违反唯一性约束。
  • 如果遇到很长的字段做索引，如何优化：
    • 可以建立前缀索引，但是这可能会影响索引的选择性，需要根据实际情况来测试和调整
    • 建立哈希索引，将字段值映射到哈希值，再用哈希值做索引，但是这种不支持范围查询，只能精准查询。
    • 删除重复部分，考虑使不同字符之间区分度变高，如反转一下。

像 name like %xxx% 这种怎么优化？

看一下所有的查询条件，尽量将这种模糊匹配放到最后面，然后创建联合索引，多利用索引下推和索引覆盖。如果要查询的字段比较少，也可以建立联合索引，触发索引覆盖，因为二级索引的磁盘 IO 始终要少一些。

还可以建立全文索引（但是准确度会有损失），对于只有前面有 % 的模糊查询，看可以通过生成列 + 反转匹配规则来优化。

参考链接：https://mp.weixin.qq.com/s/ygvuP35B_sJAlBHuuEJhfg

count(*) 和 count(1) 的性能谁更好？

count 函数本质为：在 server 层维护一个 count ，然后存储引擎查到一条记录返回，判断 count 指定字段在记录里是否为 NULL ，不为 NULL 就 + 1。比如 count(name) 就要对返回的记录查询是否 name 为 NULL 。但是 count(1) 和 count(*) 其实都没啥区别，执行计划应该都是一样的，因为 1 和 * 都不是 NULL ，只要返回一条记录， count 直接 ++ 即可。

拓展一下： count(主键) 和 count(字段) 的效率反而更低一些，server 会读取返回的记录判断字段是否为 NULL 。

如果有二级索引的话，执行计划更愿意走二级索引，因为二级索引更小，花费的磁盘 IO 也更小。

如果一个表会很大，又想频繁执行 count(*) ，优化方面可以取近似值（通过 explain 查看估计的 row ，如果精确度要求不高的话），还可以专门维护一张计数表。

谈谈 Buffer Pool

为了缓存磁盘中的页，申请一片连续内存，叫做 Buffer Pool ，缓冲池的页与磁盘中的页一一对应。

页与控制块为一对，控制块（保存页的表空间号，页号，在缓冲池中地址，链表节点等）在缓冲池前面，页在后面，向内占用内存，直到用完或者产生内存碎片。判断一个页是否已经被加载到缓存中，MySQL 使用的是哈希表，key 为表空间号 + 页号，value 为控制块。

  • Free 链表：该链表上的页都是空闲的，还没被使用（修改）
  • Flush 链表：该链表上的页都是脏页，要实现持久化需要刷脏
  • LRU 链表：last recently used 链表，当缓冲池内存不够时，需要把那些不常使用的页回收重新分配。

链表里面的节点都是控制块。

  • 最简单的 LRU 链表：页被访问，该页就会被放到 LRU 链表最前面。回收时，直接把链表尾部的页回收。但是 InnoDB 可以预读，会导致问题

    • 线性预读：顺序访问某个区的页超过某个值，就会将下个区的所有页都读到缓冲池中
    • 随机预读：一个区 13 个连续的页面都被加载到缓冲区中，该区所有的页面都会被加载到缓冲区

    这就导致可能根本用不到的页跑到链表前面，把常用的节点挤掉，降低 Buffer Pool 命中率。

    同时，全表扫描也会导致这种问题，因为访问的页实在太多了，而且很多页都是因为要全表扫描而用几次就不用了。总结就是：

    • 加载的页可能用不到
    • 加载太多页导致常用的页被挤掉
    • 只要访问一次页，就要导致链表节点变动，开销太大

  • 分区的 LRU 链表：把链表前部分设为 young 区域，存储使用频率非常高的页，也叫做热数据。后部分设为 old 区域，存储使用频率不是很高的页，又叫做冷数据。同时一个页在一定时间间隔内被连续访问，就加载到 old 区，因为一个页有很多条记录，全表扫描在扫描一个页时会间隔很短的访问很多次。 还有一点：为了避免频繁的节点移动，只有被访问的缓冲页位于 young 区域 1/4 后面才会被移动到头部。

刷脏：

  1. 从 LRU 链表的冷数据中刷新一部分到磁盘中
  2. 从 flush 链表中刷新一部分到磁盘中

LRU 尾部的节点很容易被释放用于加载其他的页，在此之前，需要判断该页是否被修改，如果修改了，还需要先刷脏才行，这样其实是很慢的，而这种只刷新一个页面的操作叫做：BUF_FLUSH_SINGLE_PAGE。

其他：

  • 操作链表肯定是要上锁的，所以缓冲池可以被分为很多个 Buff Pool 实例，每个缓冲池实例维护各自的链表，提高并发量。
  • 5.7.5 之后，有了 Chunk 这个概念，表示一片连续内存的单位，Buff Pool 就是由若干个 Chunk 组成。一个 Chunk 包含了若干个页 + 控制块。所以服务器运行时就可以通过增删 Chunk 来改变缓冲池大小。

事物的隔离级别有哪些？

SQL 标准定义的隔离级别有：

  • 读未提交：可以读到其他未提交事务修改的数据，也会导致脏读、不可重复读、幻读。
  • 读提交：可以读到其他事务提交后的数据，会导致不可重复读、幻读。
  • 可重复读：先读 A，A 被其他事务修改然后提交后，再读 A 还是原来的数据。保证一个事务中同一个数据的一致性，会导致幻读（t1 查询，t2 插入并提交，t1 修改后可以查到，这就导致幻读）。
  • 串行化：性能最差，最安全。

redo log 的持久化策略

redo 日志的持久化策略有几种：

  • 后台每秒一次，将 redo log buffer 持久化到磁盘
  • MySQL 正常关闭时
  • 当 redo 日志大小超过 redo log buffer 大小一般时，就会持久化到磁盘中
  • 每次事务提交时，根据 commit 参数决定何时持久化到磁盘中。如果为 0，不会主动触发写入磁盘；如果为 1，主动持久化到磁盘；如果为 2，每次事务提交会写入 redo log 文件，本质上时写入 Page Cache 而不是写入磁盘。对于 2 而言， MySQL 崩了，但是只要操作系统不崩，数据也会从 page cache 写入到磁盘中。

redo log 的存储是怎样的？

redo 日志存储到文件中并使用循环存储，如果所有文件满了就会从头覆盖存储，覆盖是有条件的，如果不符合条件导致无法覆盖， MySQL 就会拒绝更新操作，从而阻塞。

一个 redo 日志能否覆盖，取决于 lsn 值，它是该条 redo 日志的偏移量。关于 lsn 记录，每次一组 redo 日志写入时都会记录 lsn，同时缓冲池中的 flush 链表中每个页的控制块都记录了两个变量 old_lsn 和 new_lsn。前者是页第一次被修改时记录的 lsn（flush 链表节点其实是按照 old_lsn 进行排序的，尾节点的 old_lsn 最小。应该有某种机制，保证并发环境下，小的 lsn 比大的 lsn 加载的页面先进入 flush 链表），后者是最近一次修改时记录的 lsn。当刷脏时，会将 flush 尾节点先刷磁盘，假设这个尾节点的 old_lsn 为 A，那么 redo 日志文件组中 lsn<A 的日志都会失效。当一个页刷脏后，磁盘中页的 header 会记录下此时的 new_lsn ，当通过 redo 日志恢复时，如果关于某个页的 redo 日志的 lsn<new_lsn ，也不用进行，直接跳过，加快速度。

binlog 和 redo log 的区别？

  • 适用对象不同： binlog 产生于 server 层，而 redo log 是 InnoDB 产生的
  • 文件格式不同： redo log 有专门的压缩，文件比较小； binlog 的文件格式比较多，有 STATEMENT 、 ROW 、 MIXED 三种。
    • STATEMENT ：只记录命令，这种缺点就是如果命令中使用了一些动态函数，比如 uuid 、 now 之类的，主从数据库可能出现不一致。
    • ROW ：记录行记录被改变后的结果，改动多少行就记录多少行，有时 binlog 文件会因为一条命令变得很大
    • MIXED ：包含了前两种模式，会根据不同的情况自动使用 STATEMENT 和 ROW 模式。
  • 写入方式不同： binlog 是追加， redo log 是循环写，文件大小是固定的。
  • 用途不同： binlog 用于备份恢复，主从复制； redo log 用于断电等故障恢复。

binlog 也能恢复数据，为什么还要 redo log ?

这是 MySQL 发展原因， InnoDB 不是一开始就有的， MySQL 默认得存储引擎是 MyISAM ，而此时就已经有 binlog 了，所以设计 binlog 时一开始也没有设计恢复数据页能力和一些必要的功能。 InnoDB 出来后可以支持事务，所以需要 redo log ， binlog 在恢复事务方面并没有 redo log 那样好， redo log 不仅需要记录事务导致哪些页发生变化，还要记录 undo log 变化的页，还要考虑日志刷脏问题。

而且 redo log 的出现，通过两阶段提交，保证了数据的完整：sql 更新操作将数据更新到内存，并且记录到 redo log 中，此时 redo log 处于 prepare 状态，执行器生成该操作的 binlog 并写入磁盘，此时 redo log 的状态才改为 commit 状态，完成更新。

看你简历写了熟悉 MVCC ，能说说是什么原理吗？

解释 MVCC 首先要回到事务隔离级别以及如何实现这种隔离级别：

  • 读未提交是事务可以读到其他事务修改但未提交的数据，会造成脏读，这种隔离级别实现直接读最新的数据即可。
  • 读提交是事务可以读到其他事务修改并提交后的数据，可重复读是连其他事务修改了的数据都不能读，这两者就是通过 MVCC 实现的。
  • 串行化直接加锁即可，性能最低，但最安全。

所以平时业务需要根据对数据一致性的不同要求设置不同的事务隔离。提到 MVCC ，是基于 undo 日志形成的版本链和 ReadView 实现的。

  • 版本链：每条记录都有一个隐藏属性 roll_pointer 可以指向上一个记录的版本，这个版本实质上就是 undo 日志，而 undo 日志本身也是记录，也有 roll_pointer ，从而就形成了版本链。还需要提到的就是，记录还有一个隐藏属性 trx_id ，表示该记录版本的事务 id 。
  • ReadView：产生的时机待会再说，器中包括了几个重要的属性
    • m_ids ：生成 ReadView 时，还在活跃的所有事务的 id ，活跃就是指还没有提交的事务。
    • min_trx_id ：生成 ReadView 时，还在活跃的最小的事务 id 。
    • max_trx_id ：生成 ReadView 时，下一个事务应该被分配的 id 。注意它并不是 m_ids 中的最大值。
    • creator_trx_id ：创建该 ReadView 的事务的 id 。

我们先看一下 MVCC 如何实现读提交的隔离性，这个级别的要求就是事务可以读到已提交的事务的修改。我们假设所有的事务都在对记录 A（此时 A 的 trx_id 为 80，该事务已经提交）进行修改和读取。当事务 100 修改了两次记录 A，那么关于记录 A 的版本链 100->100->80 。然后事务 90 要对记录 A 进行查询，此时就会响应 select 生成 ReadView ，那么这个 ReadView 的 m_ids 就包括了 100，然后先读到第一个 100，发现 100 已经在 m_ids 中，表示这个事务还没有提交，就不能读到它的修改的数据，就向下查找，直到找到 80 这个版本，发现已经提交了，可以读。

后来假设事务 100 已经提交了，那么事务 90 再执行查询语句，又会新生成一个 ReadView ，此时 m_ids 就不会包含 100 了，也就可以读到 100 的记录版本了。

再看一下如何实现可重复读的隔离级别，它要保证整个事务的数据都是一致的，其实对应的策略就是只生成一个 ReadView ，而不是每次查询都生成一个 ReadView 。

参考：《MySQL 是怎样运行的》第 21 章，作者 —— 小孩子 4919