Mysql面经

1.Mysql执行的流程是什么？

• MySQL分两层：server层和数据库引擎层
• 执行流程自上而下：
  • 连接器：
    • TCP建立连接；
    • 连接器管理连接(空闲连接可以设置参数进行控制空闲时间，还可以通过指令查看被多少客户端连接、长连接占用内存问题：可以通过定期断开长连接，客户端主动重置连接）
    • 检验连接；
  • 查询缓存，查询命中就返回，否则就继续执行下步（8.0版本删除这模块，因为更新频繁的表，缓存命中很低，因为只要一个表更新，那么缓存就清除）
  • 解析SQL： 通过解析器对 SQL 查询语句进行词法分析、语法分析，然后构建语法树，方便后续模块读取表名、字段、语句类型；
  • 执行SQL：
    • 预处理阶段：检查表或字段是否存在；将 select * 中的 * 符号扩展为表上的所有列。
    • 优化阶段：基于查询成本的考虑， 选择查询成本最小的执行计划；（如果要知道优化器使用哪一个索引，可以通过explain指令指令进行查看，一般情况普通索引优于主键索引）
    • 执行阶段：根据执行计划执行 SQL 查询语句，从存储引擎读取记录，返回给客户端；
      • 主键索引查询 select * from product where id = 1; 让InnoDB引擎通过主键索引B+树搜索id=1的记录。
      • 全表扫描 select * from product where name = ‘iphone’; 查询条件没有用到索引，触发全表扫描，查询每一条记录判断是否满足条件。
      • 索引下推 （MySQL 5.6 推出的查询优化策略）

2.什么是回表操作？

• 当 MySQL 使用 辅助索引（二级索引） 查询数据时，如果所需的字段不在该索引中，就必须去 主键索引（聚簇索引） 所在的数据页提取完整的一行数据。这一过程称为回表操作。
• 如果查询的字段都在 覆盖索引 中，MySQL 就可以直接返回结果，避免回表，从而提升查询效率。

3.什么是索引下推？什么是覆盖索引？

• 索引下推是指在使用二级索引查询数据时，MySQL 在回表之前先判断其他查询条件是否成立。如果这些条件未成立，则可以跳过该二级索引进行回表操作；如果成立，则再进行回表操作以获取完整数据。这个可以在联合索引中使用来对里面的字段进行判断过滤不满足情况，减少回表次数。
• 覆盖索引就是在查询时使用了二级索引，如果查询的数据能在二级索引里查询得到，那么就不需要回表，这个过程就是覆盖索引。

4.什么是索引？

• 索引是可以在数据库存储引擎中快速获取数据的一种数据结构，就是类似数据库的目录。
• 按不同角度进行划分的有：

  • 按「数据结构」分类：B+tree索引、Hash索引、Full-text索引。

    • B+Tree vs B Tree(相同IO，B+树可以查看更多节点，查询更快）：B+Tree 只在叶子节点存储数据，而 B 树 的非叶子节点也要存储数据，所以 B+Tree 的单个节点的数据量更小，在相同的磁盘 I/O 次数下，就能查询更多的节点。另外，B+Tree 叶子节点采用的是双链表连接，适合 MySQL 中常见的基于范围的顺序查找，而 B 树无法做到这一点。
    • B+Tree vs 二叉树（结点个数有别——>树的高度有别）：对于有 N 个叶子节点的 B+Tree，其搜索复杂度为O(logdN)，其中 d 表示节点允许的最大子节点个数为 d 个。而二叉树的每个父节点的儿子节点个数只能是 2 个，意味着其搜索复杂度为 O(logN)，这已经比 B+Tree 高出不少，因此二叉树检索到目标数据所经历的磁盘 I/O 次数要更多。
    • B+Tree vs Hash(范围查询有别）：Hash 在做等值查询的时候效率贼快，搜索复杂度为 O(1)。 但是 Hash 表不适合做范围查询，它更适合做等值的查询。

  • 按「物理存储」分类：聚簇索引（主键索引）、二级索引（辅助索引）。

    • 二级索引就是除了主键外的其他字段的索引。

  • 按「字段特性」分类：主键索引、唯一索引、普通索引、前缀索引。

    • 主键索引就是建立在主键字段上的索引，通常在创建表的时候一起创建，一张表最多只有一个主键索引，索引列的值不允许有空值。
    • 唯一索引建立在 UNIQUE 字段上的索引，一张表可以有多个唯一索引，索引列的值必须唯一，但是允许有空值。
    • 普通索引就是建立在普通字段上的索引，既不要求字段为主键，也不要求字段为 UNIQUE。
    • 前缀索引是指对字符类型字段的前几个字符建立的索引，而不是在整个字段上建立的索引，前缀索引可以建立在字段类型为 char、 varchar、binary、varbinary 的列上。

  • 按「字段个数」分类：单列索引、联合索引。

    • 联合索引：多个字段联合组成的一个索引，然后就是在B+树查询的时候服从最左匹配原则，否则会失效；但在范围查询的索引（ >=、<=、BETWEEN、like）的时候就会停止其联合索引，但是前面的会生效，只是碰到就停止。

5.设计数据库的时候你需要注意什么？

• 合理的使用索引。
  • 索引需要占用物理内存，空间大；
  • 创建和维护耗时，且时间随数据量变大而增大；
  • B+树为了维护有序性，每次CRU时都要动态维护；

6.使用的索引的最佳场景一般是什么？不适用呢？

• 适用：
  • 字段唯一性；
  • 经常用于WHERE条件，GROUP BY 和ORDER BY等字段；
• 不索引：
  • 上面的表述取反一下；
  • 字段有大量重复数据或者数据分布均匀
  • 数据太少情况；
  • 经常要更新的字段不需要创建，因为每次B+树都得进行维护耗时；

7.索引优化有什么方法?

• 前缀索引优化；
  • 存储长字符时，确保前缀大部分一致，以显著减少存储空间并提高查询效率
• 覆盖索引优化；
  • 只需要查询少量字段，对这些字段创建联合索引 -> 覆盖索引，不回表。
• 主键索引最好是自增的；
  • 这样保证每次插入的数据都是按顺序的，不会出现重新移动数据情况。
• 索引最好设置为 NOT NULL；
• 防止索引失效；
  • 当我们使用左或者左右模糊匹配的时候，也就是 like %xx 或者 like %xx%这两种方式都会造成索引失效；
  • 当我们在查询条件中对索引列做了计算、函数、类型转换操作，这些情况下都会造成索引失效；
  • 联合索引要能正确使用需要遵循最左匹配原则，也就是按照最左优先的方式进行索引的匹配，否则就会导致索引失效。
  • 在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。

8.知道事务吧？说一下什么是事务。

• 事务（Transaction） 是一组要么全部执行、要么全部不执行的 SQL 操作；
• 特性有Atomicity（要么完成要么回滚） Consistency（数据前后一致） Isolation（并发时互不影响） Durability（数据永久保存）

9.Mysql中是如何保证这四个特性的？

• 持久性是通过 redo log （重做日志）来保证的；
• 原子性是通过 undo log（回滚日志） 来保证的；
• 隔离性是通过 MVCC（多版本并发控制） 或锁机制来保证的；
• 一致性则是通过持久性+原子性+隔离性来保证；

10.并发出现的问题有什么？

• 脏读（A读取B的修改，但B为提交)
• 不可重复读（一个事务读取同一个数据后，前后数据不一致）
• 幻读（A查询一个表中的数据量总和，但是查询前后发现数量不一致）

11.事务的隔离级别有什么？

自上而下的级别：

• 读未提交：事务未提交，其他事务都能看到——>可能发生脏读、不可重复读和幻读现象
• 读提交：事务提交后其他事务才能看到——>可能发生不可重复读和幻读现象
• 可重复读：一个事务执行过程中看到的数据一直跟事务启动看到的数据一致——>可能幻读
  • InnoDB默认的引擎隔离级别，但大部分避免了幻读。(解决方案)
    • 快照读（普通 select 语句）：通过MVCC解决幻读
    • 当前读（select … for update 等语句）：通过next-key lock（记录锁+间隙锁）
• 串行化：记录中加读写锁，如果发生读写冲突直接加锁阻塞

12.什么是MVCC？

• MVCC通过为数据的每个版本维护一个时间戳或版本号，使得不同的事务可以在同一时间看到数据的不同版本。在并发环境下，多个事务可以同时读取和修改数据，而不会相互阻塞，从而提高了数据库的并发性能。

• 核心的用法就是在每一个数据都有隐藏列，且列中的数据中有事务id，版本号等数据，当我的事务在读取数据的是时候会读取视图，并且根据自身的事务id 版本号进行一些判断，从而达到对一些数据的版本可见性的判断。

• 在 MVCC 中，事务在可重复读隔离级别下能够并发访问不同版本的数据，而不会相互干扰。只有在事务结束时，最新的版本才会被写入数据库。这样的设计提高了并发性能，同时确保了数据的一致性。

13.可重复读和读提交的实现机制有什么不同的？

• 「读提交」隔离级别是在每个 select 都会生成一个新的 Read View，也意味着，事务期间的多次读取同一条数据，前后两次读的数据可能会出现不一致，因为可能这期间另外一个事务修改了该记录，并提交了事务。
• 「可重复读」隔离级别是启动事务时生成一个 Read View，并且整个事务期间都在用这个 Read View，这样就保证了在事务期间读到的数据都是事务启动前的记录。

在可重复读隔离级别中，普通的 select 语句就是基于 MVCC 实现的快照读，也就是不会加锁的。而 select … for update 语句就不是快照读了，而是当前读了，也就是每次读都是拿到最新版本的数据，但是它会对读到的记录加上 next-key lock 锁。

13.MySQL中有哪些锁？（会话结束都会释放）

• 全局锁
  • FTWRL
• 表级锁
  • 表锁
  • 元数据锁（MDL）
  • 意向锁：意向锁用于指示事务希望在某个粒度上获取锁的意图，从而提高数据库的并发性和避免死锁，常用的有意向共享锁，意向独占锁
  • AOTU-INC锁：: AOTU-INC 锁用于确保在增量更新操作期间，只有一个事务可以对资源进行修改，从而避免数据不一致。当然在插入较多数据的时候会造成堵塞，所以现在就是有对于的轻量级锁进行自增（给该字段赋值一个自增的值，就把这个轻量级锁释放了，而不需要等待整个插入语句执行完后才释放锁）
• 行级锁(InnoDB 引擎是支持行级锁的，而 MyISAM 引擎并不支持行级锁)
  • Record Lock:锁定特定行，以防止其他事务对该行进行修改或删除。
  • Gap Lock：锁定两个记录之间的间隙，以防止其他事务在此间隙插入新记录。
  • Next-Key Lock： 同时锁定记录和其前面的间隙，防止其他事务对该记录的修改和在间隙中插入新记录。（当前读的场景）

14.全局锁的应用场景是什么？

• 主要用于数据库的备份，不会因为数据的变更导致备份数据不一样。
• 缺点就是数据过多备份太长时间，错过一些业务的请求。
  • 处理：在备份前开启事务，在可重复读的隔离级别的时候会产生ReadVIew，这样MVCC可以处理并发的情况，而且在后续的时候数据库中还可以插入最新的数据。

15.元数据锁的应用场景？

• MDL 是为了保证当用户对表执行 CRUD 操作时，防止其他线程对这个表结构做了变更。

• 释放的时候就是在事务提交的时候才释放，当然在执行期间就一直保存。

• 长事务场景：

  • 首先，线程 A 先启用了事务（但是一直不提交），然后执行一条 select 语句，此时就先对该表加上 MDL 读锁；
  • 然后，线程 B 也执行了同样的 select 语句，此时并不会阻塞，因为「读读」并不冲突；
  • 接着，线程 C 修改了表字段，此时由于线程 A 的事务并没有提交，也就是 MDL 读锁还在占用着，这时线程 C 就无法申请到 MDL 写锁，就会被阻塞，
  • 那么在线程 C 阻塞后，后续有对该表的 select 语句，就都会被阻塞，如果此时有大量该表的 select 语句的请求到来，就会有大量的线程被阻塞住，这时数据库的线程很快就会爆满了。

为什么线程 C 因为申请不到 MDL 写锁，而导致后续的申请读锁的查询操作也被阻塞？

• 这是因为申请 MDL 锁的操作会形成一个队列，队列中写锁获取优先级高于读锁，一旦出现 MDL 写锁等待，会阻塞后续该表的所有 CRUD 操作。

16.你知道MySQL中redolog、binlog、undolog区别与作用？

• undo log（回滚日志）：实现了事务中的原子性，主要用于事务回滚和 MVCC。

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

• redo log（重做日志）：实现了事务中的持久性，主要用于掉电等故障恢复；

  • 每当执行一个事务就会产生这样的一条或者多条物理日志。在事务提交时，只要先将 redo log 持久化到磁盘即可，可以不需要等到将缓存在 Buffer Pool 里的脏页数据持久化到磁盘。
  • 当系统崩溃时，虽然脏页数据没有持久化，但是 redo log 已经持久化，接着 MySQL 重启后，可以根据 redo log 的内容，将所有数据恢复到最新的状态。
  • 物理日志，记录的是数据的修改情况，如在什么地方时间修改了数据，在事务提价时刷盘

• binlog （归档日志）：是 Server 层生成的日志，主要用于数据备份和主从复制；

  • 在每次数据修改后立即写入，记录的是所有的数据操作。

17.undo log 是如何刷盘（持久化到磁盘）的？

• undolog在事务提交前记录，然后保存在bufferpool里面，然后当事务提交时，redolog会记录事务的操作并且刷入盘中，与此同时，我的bufferpool里面部分的undolog会根据情况将对应的数据写入磁盘，当我的事务结束后，我的undo log会被标记等待垃圾回收

18.Buffer Pool 缓存什么？为什么需要这个？查询一条记录，就只需要缓冲一条记录吗？

• 在 MySQL 启动的时候，InnoDB 会为 Buffer Pool 申请一片连续的内存空间，然后按照默认的16KB的大小划分出一个个的页， Buffer Pool 中的页就叫做缓存页。此时这些缓存页都是空闲的，之后随着程序的运行，才会有磁盘上的页数据被缓存到 Buffer Pool 中。用于缓存表数据和索引，提高读写性能。
• 缓存：
  • 当读取数据时，会先从这个BUffer pool里面读取数据，否则再去磁盘读取；如果要修改也是在当前的buffer pool查看修改，如果有旧数据直接修改并将页改成脏页，后续的情况就后台会根据情况将buffer pool中的修改写入磁盘中数据库的数据，主要减少频繁的IO。
  • 当我们查询一条记录时，InnoDB 是会把整个页的数据加载到 Buffer Pool 中，将页加载到 Buffer Pool 后，再通过页里的「页目录」去定位到某条具体的记录。
• 数据内容：包括未提交的事务数据（Undo Log）和已提交的数据。

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

19.redo log 和 undo log 区别在哪？

这两种日志是属于 InnoDB 存储引擎的日志，它们的区别在于：

• redo log 记录了此次事务「完成后」的数据状态，记录的是更新之后的值；
• undo log 记录了此次事务「开始前」的数据状态，记录的是更新之前的值；（MVCC会说）
• 事务提交之前发生了崩溃，重启后会通过 undo log 回滚事务，
• 事务提交之后发生了崩溃，重启后会通过 redo log 恢复事务。

20.redo log 要写到磁盘，数据也要写磁盘，为什么要多此一举？

• 主要为了保证事务的持久性问题。理由如下：
  • 写入 redo log 的方式使用了追加操作， 所以磁盘操作是顺序写，而写入数据需要先找到写入位置，然后才写到磁盘，所以磁盘操作是随机写。磁盘的「顺序写 」比「随机写」 高效的多，因此 redo log 写入磁盘的开销更小。
  • 实现事务的持久性，让 MySQL 有 crash-safe (能够保证 MySQL 在任何时间段突然崩溃，重启后之前已提交的记录都不会丢失）的能力。

21.产生的 redo log 是直接写入磁盘的吗？如果写满了又怎么办？

• 实际上， 执行一个事务的过程中，产生的 redo log 也不是直接写入磁盘的，因为这样会产生大量的 I/O 操作，而且磁盘的运行速度远慢于内存。所以，redo log 也有自己的缓存—— redo log buffer，每当产生一条 redo log 时，会先写入到 redo log buffer，后续在持久化到磁盘。

• 写满的情况就是redo log是以循环写的方式工作的，从头开始写，写到末尾又开头，一个循环。

• 底层的逻辑：当buffer log刷入磁盘，那么一些记录在redolog的记录就没有用，然后就是我的redolog用write pos表示当前写的位置，checkpoint表示擦除位置，当write追到checkpoint，那么久阻塞mysql然后对一些bufferlog刷入的数据在redolog进行标记擦除，为redolog的空间腾出位置，后续mysql恢复运行

20.redo log 什么时候刷盘?

• MySQL 正常关闭时；
• 当 redo log buffer 中记录的写入量大于 redo log buffer 内存空间的一半时，会触发刷盘；
• InnoDB 的后台线程每隔 1 秒，将 redo log buffer 持久化到磁盘。

21.redo log和bin log的区别是什么？

• binlog 是 MySQL 的 Server 层实现的日志，所有存储引擎都可以使用；redo log 是 Innodb 存储引擎实现的日志；
• 文件格式不一样；
• binlog 是追加写，写满一个文件，就创建一个新的文件继续写，不会覆盖以前的日志，保存的是全量的日志。redo log 是循环写，日志空间大小是固定，全部写满就从头开始，保存未被刷入磁盘的脏页日志。
• binlog 用于备份恢复、主从复制；redo log 用于掉电等故障恢复。

22.MySQL 的主从复制怎么实现？(异步复制)

• MySQL 的主从复制依赖于 binlog ，也就是记录 MySQL 上的所有变化并以二进制形式保存在磁盘上。复制的过程就是将 binlog 中的数据从主库传输到从库上。这个过程一般是异步的，也就是主库上执行事务操作的线程不会等待复制 binlog 的线程同步完成。
• 步骤：
  • 写入 Binlog：主库写 binlog 日志，提交事务，并更新本地存储数据
  • 同步 Binlog：把 binlog 复制到所有从库上，每个从库把 binlog 写到暂存日志中
  • 回放 Binlog：回放 binlog，并更新存储引擎中的数据
• 完成主从复制后，就可以写数据时只写主库，读数据时只读从库，不会影响请求。但是一旦主库宕机，数据就会发生丢失。

23. MySQL的主从复制的模型有哪些？

• 同步复制：MySQL 主库提交事务的线程要等待所有从库的复制成功响应，才返回客户端结果。这种方式在实际项目中，基本上没法用，原因有两个：一是性能很差，因为要复制到所有节点才返回响应；二是可用性也很差，主库和所有从库任何一个数据库出问题，都会影响业务。
• 异步复制（默认模型）：MySQL 主库提交事务的线程并不会等待 binlog 同步到各从库，就返回客户端结果。这种模式一旦主库宕机，数据就会发生丢失。
• 半同步复制：MySQL 5.7 版本之后增加的一种复制方式，介于两者之间，事务线程不用等待所有的从库复制成功响应，只要一部分复制成功响应回来就行，比如一主二从的集群，只要数据成功复制到任意一个从库上，主库的事务线程就可以返回给客户端。这种半同步复制的方式，兼顾了异步复制和同步复制的优点，即使出现主库宕机，至少还有一个从库有最新的数据，不存在数据丢失的风险。

24.binlog什么时候刷盘？

• binlog 什么时候刷盘？:事务执行过程中，先把日志写到 binlog cache（Server 层的 cache），事务提交的时候，再把 binlog cache 写到 binlog 文件中。，并清空 binlog cache。

25.数据库中的字段为什么要设置为NotNull呢？

答：

• 数据库的字段设置为 NOTNUll，可以确保数据的一致性，让开发更加集中统一，让开发更好的友好性；
• 设置为 NOTNUll，可以保证数据的完整性，如果说一个用户表中用户名设置为空，当这个字段没有被赋值的时候会出现用户没有用户名的情况，从而导致数据的不完整性；
• 设置为 NOTNUll 可以提高数据库的查询速度,在查询过程中不用去额外处理空值的情况，从而且可以快速定位查询；

26.你知道在MySQL中的范式吗？

答：

• 共三范式，第一范式规定数据库中数值具有原子性，不要一个列中值有多个，第二范式规定的是我的数据库中的所有非主键要完全依赖于整个主键而不是部分主键，而第三范式说的是我的非主键仅依赖于主键，不依赖其他非主键。

27.为什么要使用视图呢？什么是视图？

• 复杂的多表连接或聚合查询可以封装成视图，用户调用时更简单。
• 可以通过对一些常用的数据进行封装，从而包含隐私数据不被调用到，即使使用select * 也不会暴露隐密字段
• 代码复用，比如经常使用一个sql语句，那么可以将对应的数据结果保存到表里面，直接调用就可以，减少sql的书写，提高复用。

28.知道触发器吗，应用场景是什么？

• 简单概要就是我触发某个事件，然后就会调用别的预先设置和的处理，如关联表的数据更改等。

29. 大表数据查询，怎么优化？

• 优化shema、sql语句+索引；
• 第二加缓存，memcached, redis；
• 主从复制，读写分离；主库写，从库读
• 垂直拆分，根据你模块的耦合度，将一个大的系统分为多个小的系统，也就是分布式系统；（根据业务拆分）
• 水平切分，针对数据量大的表，这一步 麻烦， 能考验技术水平，要选择一个合理的sharding key, 为了有好的查询效率，表结构也要改动，

30.怎么查询慢sql日志？

• 记录执行时间超过某个临界值的sql日志快速定位。

31.主键使用UUID还是自增？

• 如果没有特殊含义的，推荐使用自增，这跟数据库表的结构有关，B+树是一个有顺序的结构，当自增时可以减少B+树去主动排序的性能消耗。