Mysql 面试八股文

---

1.说一说三大范式

三范式简介

1NF：字段不可分；原子性，字段不可再分，否则就不上关系数据库

2NF：有主键，非主键字段依赖主键；唯一性，一个表只说明一个事物

3NF：非主键字段不能相互依赖；每列都与主键有直接关系，不存在传递依赖

详解

第一范式(1NF)：
即表的列具有原子性，不可再分割，即列的信息，不能分解，只要数据库是关系型数据库(mysql/oracle/db2/informix/sysbase/sql server)就自动的满足1NF。数据库表的每一列都是不可分割的原子数据项，而不能是集合，数组，记录等非原子数据项,如果实体中的某个属性有多个值时，必须拆分为不同的属性 。通俗理解即一个字段只存储一项信息。

第二范式(2NF):
第二范式是在第一范式的基础上建立起来的，即满足第二范式一定满足第一范式。第二范式要求数据库表中的每个实例或行必须可以被唯一区分。为实现区分通常需要我们设计一个主键来实现(这里的主键不包含业务逻辑)。

即满足第一范式前提，当存在多个主键的时候，才会发生不符合第二范式的情况。比如有两个主键，不能存在这样的属性，它只依赖于其中一个主键，这就是不符合第二范式。通俗理解是任意一个字段都只依赖表中的同一个字段。（涉及到表的拆分）

第三范式(3NF)
满足第三范式（3NF）必须先满足第二范式（2NF）。简而言之，第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主键字段。就是说，表的信息，如果能够被推导出来，就不应该单独的设计一个字段来存放(能尽量外键join就用外键join)。很多时候，我们为了满足第三范式往往会把一张表分成多张表。

即满足第二范式前提，如果某一属性依赖于其他非主键属性，而其他非主键属性又依赖于主键，那么这个属性就是间接依赖于主键，这被称作传递依赖于主属性。 通俗解释就是一张表最多只存两层同类型信息。

2.MyISAM 与 InnoDB 的区别是什么？

1. InnoDB支持事务，MyISAM不支持
2. InnoDB 支持外键，而 MyISAM 不支持
3. InnoDB是聚集索引，使用B+Tree作为索引结构，数据文件是和索引绑在一起的，必须要有主键。MyISAM是非聚集索引，也是使用B+Tree作为索引结构，索引和数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的
4. InnoDB 不保存表的具体行数。MyISAM 用一个变量保存了整个表的行数
5. Innodb 有 redolog 日志文件，MyISAM 没有
6. Innodb存储文件有frm、ibd，而Myisam是frm、MYD、MYI
   （1）Innodb：frm是表定义文件，ibd是数据文件
   （2）Myisam：frm是表定义文件，myd是数据文件，myi是索引文件
7. InnoDB 支持表、行锁，而 MyISAM 支持表级锁
8. InnoDB 必须有唯一索引(主键),如果没有指定的话 InnoDB 会自己生成一个隐藏列Row_id来充当默认主键，MyISAM 可以没有

3.为什么推荐使用自增 id 作为主键？

1. 普通索引使用的是B+树存放主键值，如果该值比较大，索引文件存储可能会变得比较大
2. 使用自增ID做主键维护在新增数据只要放在该页的最尾端就可以，直接按照顺序插入，不用刻意去维护
3. 页分裂容易，但是当前页快满时插入数据，会造成页分裂的现象，如果主键索引不是自增，那数据就可能会从中间插入，页的数据会频繁变动，导致页的分裂维护成本高

4.一条查询语句是怎么执行的?

1. 首先是客户端先建立连接
2. 查询本地缓存，如果缓存命中返回查询数据
3. 未命中，通过分析器分析该 sql 的语义是否正确，包括格式，表等等
4. 通过优化器优化该语句，比如选择索引，join 表的连接顺序
5. 验证权限，验证是否有该表的查询权限（有执行下一步，无权限则返回）
6. 通过执行器调用存储引擎执行该 sql，然后返回执行结果

5.使用 Innodb 的情况下，一条更新语句是怎么执行的?

用以下sql举例：

update T set c=c+1 where id=2;

1. 执行器先根据id=2通过存储引擎使用B+树找到这一行数据
   （1）如果 id=2 这一行所在的数据页本来就在内存中，就直接返回给执行器
   （2）不在内存中，需要先从磁盘读入内存，然后再返回

2. .执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的一行数据，再调用引擎接口写入这行新数据

3. 引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务

4. 执行器生成这个操作的 binlog，并把 binlog 写入磁盘

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

扩展：
1.binlog二进制日志是mysql-server层的，主要是做主从复制，时间点恢复使用。
2.redolog重做日志是InnoDB存储引擎层的，用来保证事务安全。
3.undolog回滚日志保存了事务发生之前的数据的一个版本，undolog有两个作用：提供回滚和多个行版本控制(MVCC)
4.MVCC:MVCC 的英文全称是 Multiversion Concurrency Control ，中文意思是多版本并发控制技术。原理是，通过数据行的多个版本管理来实现数据库的并发控制，简单来说就是保存数据的历史版本。可以通过比较版本号决定数据是否显示出来。读取数据的时候不需要加锁可以保证事务的隔离效果。

• 读写之间阻塞的问题，通过 MVCC 可以让读写互相不阻塞，读不相互阻塞，写不阻塞读，这样可以提升数据并发处理能力。
• 降低了死锁的概率，这个是因为 MVCC 采用了乐观锁的方式，读取数据时，不需要加锁，写操作，只需要锁定必要的行。
• 解决了一致性读的问题，当我们朝向某个数据库在时间点的快照是，只能看到这个时间点之前事务提交更新的结果，不能看到时间点之后事务提交的更新结果。

6.Innodb 事务为什么要两阶段提交?

1. 先写 redolog 后写binlog。假设在 redolog 写完，binlog 还没有写完的时候，MySQL 进程异常重启，这时候 binlog 里面就没有记录这个语句。然后你会发现，如果需要用这个 binlog 来恢复临时库的话，由于这个语句的 binlog 丢失，这个临时库就会少了这一次更新，恢复出来的这一行 c 的值就是 0，与原库的值不同。
2. 先写 binlog 后写 redolog。如果在 binlog 写完之后 crash，由于 redolog 还没写，崩溃恢复以后这个事务无效，所以这一行c的值是0。但是 binlog 里面已经记录了“把c从0改成1”这个日志。所以，在之后用 binlog 来恢复的时候就多了一个事务出来，恢复出来的这一行 c 的值就是 1，与原库的值不同。
3. 可以看到，如果不使用“两阶段提交”，那么数据库的状态就有可能和用它的日志恢复出来的库的状态不一致

7.什么是索引?

索引就「相当于我们字典中的目录」，可以极大的提高我们在数据库的查询效率。

8.索引失效的场景有哪些?

1. 最左前缀法则（带头索引不能死，中间索引不能断
2. 不要在索引上做任何操作（计算、函数、自动/手动类型转换），不然会导致索引失效而转向全表扫描
3. 不能继续使用索引中范围条件（bettween、<、>、in等）右边的列 如:

select a from user where c > 5 and b = 4；

4. 索引字段上使用（！= 或者 < >）判断时，会导致索引失效而转向全表扫描
5. 索引字段上使用 is null / is not null 判断时，会导致索引失效而转向全表扫描
6. 索引字段使用like以通配符开头（‘%字符串’）时，会导致索引失效而转向全表扫描，也是最左前缀原则
7. 索引字段是字符串，但查询时不加单引号，会导致索引失效而转向全表扫描
8. 索引字段使用 or 时，会导致索引失效而转向全表扫描

9.为什么采用 B+ 树,而不是 B-树

1. B+ 树只在叶子结点储存数据，非叶子结点不存具体数据，只存 key，查询更稳定，增大了广度，而一个节点就是磁盘一个内存页，内存页大小固定，那么相比 B 树，B- 树这些「可以存更多的索引结点」，宽度更大，树高矮，节点小，拉取一次数据的磁盘 IO 次数少，并且 B+ 树只需要去遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，效率更高。
2. B+树比B-树的优势三个总结：
   （1）单一节点存储更多的元素，使得查询的IO次数更少。
   （2）所有查询都要查找到叶子节点，查询性能稳定。
   （3）所有叶子节点形成有序链表，便于范围查询。

10.WAl 是什么?有什么好处?

1. WAL 就是 Write-Ahead Logging，其实就是「所有的修改都先被写入到日志中，然后再写磁盘」，用于保证数据操作的原子性和持久性
   好处:
   （1）读和写可以完全地并发执行，不会互相阻塞
   （2）先写入log中，磁盘写入从随机写变成顺序写，降低了client端的延迟，并且，由于顺序写大概率是在一个磁盘内，这样产生的io次数也减少了

11.什么是回表?

回表是先通过数据库索引扫描出该索引树中数据所在的行，获取主键ID，在通过主键ID取出主键索引树中的数据，非主键索引的查询需要多扫描一次索引树

12.什么是覆盖索引?

覆盖索引是指一个查询语句直接在索引中就能够获得，不必从表中读取，减少回表次数，例如：

select id from t where age = 1;

id为主键，age为普通索引，age这个索引存储的就是主键id，可以直接返回数据

13.什么是最左前缀原则?

最左前缀说的是，在Where条件中出现的字段，是要按照定义的时候顺序从前到后触发，最左面一个列触发不了，之后的所有列都无法触发
比如：「有一个 (a,b,c) 的组合索引」

where a = 1 and b = 1

此时 a,b 会命中该组合索引

where a = 1 and c = 1

此时 a 会命中该组合索引, c 不会

where b = 1 and c = 1

此时不会命中该组合索引

14.普通索引和唯一索引该怎么选择?

查询：

当普通索引为条件时查询到数据会一直扫描,直到扫完整张表 当唯一索引为查询条件时,查到该数据会直接返回,不会继续扫表

更新：
普通索引会直接将操作更新到 change buffer 中,然后结束
唯一索引需要判断数据是否冲突

15.什么是事务?其特性是什么?

事务是指一系列操作必须全部成功，要么全部失败

特性：

1. 原子性（Atomicity）：要么全部执行成功，要么全部不执行
2. 一致性（Consistency）：事务前后数据的完整性必须保持一致
3. 隔离性（Isolation）：隔离性是当多个事务同事触发时，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离
4. 持久性（Durability）：事务完成之后的改变是永久的

16.事务的隔离级别?

1. 读提交：读取数据的事务允许其他事务继续访问该行数据，但是未提交的写事务将会禁止其他事务访问该行。该隔离级别避免了脏读，但是却可能出现不可重复读。事务A事先读取了数据，事务B紧接了更新了数据，并提交了事务，而事务A再次读取该数据时，数据已经发生了改变
2. 读未提交：如果一个事务已经开始写数据，则另外一个事务则不允许同时进行写操作，但允许其他事务读此行数据。该隔离级别可以通过“排他写锁”实现。这样就避免了更新丢失，却可能出现脏读。也就是说事务B读取到了事务A未提交的数据
3. 可重复读：可重复读是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，即使第二个事务对数据进行修改，第一个事务两次读到的数据是一样的。这样就发生了在一个事务内两次读到的数据是一样的，因此称为是可重复读。读取数据的事务将会禁止写事务（但允许读事务），写事务则禁止任何其他事务。这样避免了不可重复读取和脏读，但是有时可能出现幻象读。（读取数据的事务）这可以通过“共享读锁”和“排他写锁”实现
4. 可串行化：提供严格的事务隔离。它要求事务序列化执行，事务只能一个接着一个地执行，但不能并发执行。如果仅仅通过“行级锁”是无法实现事务序列化的，必须通过其他机制保证新插入的数据不会被刚执行查询操作的事务访问到。序列化是最高的事务隔离级别，同时代价也花费最高，性能很低，一般很少使用，在该级别下，事务顺序执行，不仅可以避免脏读、不可重复读，还避免了幻像读

5. 脏读：脏读指的是读到了其他事务未提交的数据，未提交意味着这些数据可能会回滚，也就是可能最终不会存到数据库中，也就是不存在的数据。读到了并一定最终存在的数据，这就是脏读
6. 不可重复读：对比可重复读，不可重复读指的是在同一事务内，不同的时刻读到的同一批数据可能是不一样的
7. 幻读：幻读是针对数据插入（INSERT）操作来说的。假设事务A对某些行的内容作了更改，但是还未提交，此时事务B插入了与事务A更改前的记录相同的记录行，并且在事务A提交之前先提交了，而这时，在事务A中查询，会发现好像刚刚的更改对于某些数据未起作用，但其实是事务B刚插入进来的这就叫幻读

17.binlog 是做什么的?

1.binlog 是归档日志，属于 Server 层的日志，是一个二进制格式的文件，用于记录用户对数据库更新的SQL语句信息
2.主要作用：主从复制、数据恢复

18.undolog 是做什么的?

1.undolog 是 InnoDB 存储引擎的日志，用于保证数据的原子性，保存了事务发生之前的数据的一个版本，也就是说记录的是数据是修改之前的数据，可以用于回滚，同时可以提供多版本并发控制下的读（MVCC）
2. 主要作用：事务回滚、实现多版本控制(MVCC)

19.relaylog 是做什么的?

1.relaylog 是中继日志，在主从同步的时候使用到，它是一个中介临时的日志文件，用于存储从master节点同步过来的binlog日志内容

2.master 主节点的 binlog 传到 slave 从节点后，被写入 relay log 里，从节点的 slave sql 线程从 relaylog 里读取日志然后应用到 slave 从节点本地。从服务器 I/O 线程将主服务器的二进制日志读取过来记录到从服务器本地文件，然后 SQL 线程会读取 relay-log 日志的内容并应用到从服务器，从而使从服务器和主服务器的数据保持一致

20.redolog 是做什么的?

1.redolog 是 InnoDB 存储引擎所特有的一种日志，用于记录事务操作的变化，记录的是数据修改之后的值，不管事务是否提交都会记录下来
2.可以做数据恢复并且提供 crash-safe 能力
3.当有增删改相关的操作时，会先记录到 Innodb 中，并修改缓存页中的数据，等到 mysql 闲下来的时候才会真正的将 redolog 中的数据写入到磁盘当中

21.redolog 是怎么记录日志的?

1.InnoDB 的 redo log 是固定大小的，比如可以配置为一组4个文件，每个文件的大小是1GB，那么总共就可以记录4GB的操作。从头开始写，写到末尾就又回到开头循环写
2.所以，如果数据写满了但是还没有来得及将数据真正的刷入磁盘当中，那么就会发生内存抖动现象，从肉眼的角度来观察会发现 mysql 会宕机一会儿，此时就是正在刷盘了

22.redolog 和 binlog 的区别是什么?

1. redolog 是 Innodb 独有的日志，而 binlog 是 server 层的，所有的存储引擎都有使用到
2. redolog记录了具体的数值，对某个页做了什么修改，binlog 记录的操作内容
3. binlog大小达到上限或者 flush log 会生成一个新的文件，而 redolog 有固定大小只能循环利用
4. binlog 日志没有 crash-safe 的能力，只能用于归档。而 redo log 有 crash-safe 能力

23.说一说 mvcc 吧，有什么作用?

1. MVCC:多版本并发控制，是现代数据库(包括 MySQL、Oracle、PostgreSQL 等)引擎实现中常用的处理读写冲突的手段，目的在于提高数据库高并发场景下的吞吐性能
2. 在 MVCC 协议下，每个读操作会看到一个一致性的快照，这个快照是基于整个库的，并且可以实现非阻塞的读，用于支持读提交和可重复读隔离级别的实现
3. MVCC 允许数据具有多个版本，这个版本可以是时间戳或者是全局递增的事务 ID，在同一个时间点，不同的事务看到的数据是不同的，这个修改的数据是记录在 undolog 中的

24.一条 Sql 语句查询一直慢会是什么原因?

1. 没有用到索引：比如函数导致的索引失效，或者本身就没有加索引
2. 表数据量太大：考虑分库分表吧
3. 优化器选错了索引：考虑使用 force index 强制走索引

25.Mysql 主从之间是怎么同步数据的?

1. master 主库将此次更新的事件类型写入到主库的 binlog 文件中
2. master 创建 log dump 线程通知 slave 需要更新数据
3. slave 向 master 节点发送请求，将该 binlog 文件内容存到本地的 relaylog 中
4. slave 开启 sql 线程读取 relaylog 中的内容，将其中的内容在本地重新执行一遍，完成主从数据同步
   

同步策略
1.全同步复制：主库强制同步日志到从库，等全部从库执行完才返回客户端，性能差
2.半同步复制：主库收到至少一个从库确认就认为操作成功，从库写入日志成功返回ack确认

26.主从延迟要怎么解决?

1. 提高机器配置(王道)
2. MySQL 5.6 版本以后，提供了一种并行复制的方式，通过将 SQL 线程转换为多个 work 线程来进行重放
3. 在业务初期就选择合适的分库、分表策略
4. 避免长事务
5. 避免让数据库进行各种大量运算
6. 对于一些对延迟很敏感的业务直接使用主库读

27.删除表数据后表的大小却没有变动,这是为什么?

在使用 delete 删除数据时，其实对应的数据行并不是真正的删除，是逻辑删除，InnoDB 仅仅是将其标记成可复用的状态，所以表空间不会变小

28.为什么 VarChar 建议不要超过255?

1. 当定义varchar长度小于等于255时，长度标识位需要一个字节(utf-8编码)
2. 当大于255时，长度标识位需要两个字节，并且建立的索引也会失效

29.分布式式事务怎么实现?

1. 本地消息表
2. 消息事务
3. 二阶段提交
4. 三阶段提交
5. TCC
6. 最大努力通知
7. Seata 框架

30.Mysql 中有哪些锁?

以下并不全，主要理解下锁的意义即可
1.基于锁的属性分类：共享锁、排他锁
2.基于锁的粒度分类：表锁、行锁、记录锁、间隙锁、临键锁
3.基于锁的状态分类：意向共享锁、意向排它锁、死锁

31.为什么不要使用长事务?

1. 并发情况下，数据库连接池可能容易被撑爆
2. 容易造成大量的阻塞和超时
3. 执行时间长，可能造成主从延时
4. 回滚时间较长，事务越长，整个时间段内的事务越多
5. undolog 日志越来越大：长事务意味着系统里面会存在很老的事务视图。由于这些事务随时可能访问数据库里面的任何数据，所以这个事务提交之前，数据库里面它可能用到的回滚记录都必须保留，这就会导致大量占用存储空间

32.说说你的 Sql 调优思路吧

1. 表结构优化
   1.1：拆分字段
   1.2：字段类型的选择
   1.3：字段类型大小的限制
   1.4：合理的增加冗余字段
   1.5：新建字段一定要有默认值
2. 索引方面
   2.1：索引字段的选择
   2.2：利用好mysql支持的索引下推，覆盖索引等功能
   2.3：唯一索引和普通索引的选择
3. 查询语句方面
   3.1避免索引失效
   3.2合理的书写where条件字段顺序
   3.3小表驱动大表
   3.4可以使用force index()防止优化器选错索引
4. 分库分表