MySQL

1、说一说三大范式

• 「第一范式」：数据库中的字段具有原子性，不可再分，并且是单一职责
• 「第二范式」：「建立在第一范式的基础上」，第二范式要求数据库表中的每个实例或行必须可以被惟一地区分。为实现区分通常需要为表加上一个列，以存储各个实例的惟一标识。这个惟一属性列被称为主键
• 「第三范式」：「建立在第一，第二范式的基础上」，确保每列都和主键列直接相关，而不是间接相关不存在其他表的非主键信息

2、InnoDB和MyISAM区别

InnoDB支持事务，MyISAM不支持

InnoDB 支持外键，而 MyISAM 不支持

InnoDB是聚集索引，使用B+Tree作为索引结构，数据文件是和索引绑在一起的，必须要有主键。「MyISAM是非聚集索引」，也是使用B+Tree作为索引结构，索引和数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。

InnoDB 不保存表的具体行数。MyISAM 用一个变量保存了整个表的行数。

Innodb 有 「redolog」 日志文件，MyISAM 没有。

Innodb存储文件有frm、ibd，而Myisam是frm、MYD、MYI

• Innodb：frm是表定义文件，ibd是数据文件
• Myisam：frm是表定义文件，myd是数据文件，myi是索引文件

InnoDB 支持表、行锁，而 MyISAM 支持表级锁

InnoDB 必须有唯一索引(主键),如果没有指定的话 InnoDB 会自己生成一个隐藏列Row_id来充当默认主键，MyISAM 可以没有

3、二叉树、B树、B+树

二叉查找树由于存在退化成链表的可能性，会使得查询操作的时间复杂度从 O(logn)降低为 O(n)。

而且会随着插入的元素越多，树的高度也变高，意味着需要磁盘 IO 操作的次数就越多，这样导致查询性能严重下降，再加上不能范围查询，所以不适合作为数据库的索引结构。

为了解决降低树的高度的问题，后面就出来了 B 树，它不再限制一个节点就只能有 2 个子节点，而是允许 M 个子节点 (M>2)，从而降低树的高度。

B 树的每一个节点最多可以包括 M 个子节点，M 称为 B 树的阶，所以 B 树就是一个多叉树。

B+ 树就是对 B 树做了一个升级，

InnoDB 的 B+Tree 索引分为主索引和辅助索引。主索引的叶子节点 data 域记录着完整的数据记录，这种索引方式被称为聚簇索引。因为无法把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。

辅助索引的叶子节点的 data 域记录着主键的值，因此在使用辅助索引进行查找时，需要先查找到主键值，然后再到主索引中进行查找，这个过程也被称作回表。

B+ 树与 B 树差异的点，主要是以下这几点：

• 叶子节点（最底部的节点）才会存放实际数据（索引+记录），非叶子节点只会存放索引；
• 所有索引都会在叶子节点出现，叶子节点之间构成一个有序链表；
• 非叶子节点的索引也会同时存在在子节点中，并且是在子节点中所有索引的最大（或最小）。
• 非叶子节点中有多少个子节点，就有多少个索引；

B+ 树只在叶子结点储存数据，非叶子结点不存具体数据，只存 key，查询更稳定，增大了广度，而一个节点就是磁盘一个内存页，内存页大小固定，那么相比 B 树，B- 树这些可以存更多的索引结点，宽度更大，树更矮，节点小，拉取一次数据的磁盘 IO 次数少，并且 B+ 树只需要去遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，效率更高。

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

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

执行更新语句时redolog和binlog的配合流程：

用以下语句来举例，c 字段无索引，id 为主键索引

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

1）执行器先找引擎取 id=2 这一行。id 是主键，引擎直接用树搜索找到这一行的数据。
如果 id=2 这一行所在的数据页本来就在内存（buffer pool）中，就直接返回给执行器；「不在内存」中，需要先从磁盘「读入内存」，然后再返回

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

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

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

5）执行器调用引擎的**「提交事务」**接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，然后返回给执行器，同时引擎内部开启一些异步刷盘流程。

上面的更新过程，其实就是MySQL事务的2阶段提交的描述了，2阶段提交方案是保证binlog和redolog一致性的一种手段。

2阶段如何保证一致性：

• 情况一：一阶段提交成功了 ，即写入redolog并处于prepare状态了，写入binlog之前崩溃
  此时已经写了redolog，处于prepare状态，还没有写binlog，这时候如果崩溃恢复，直接回滚事务即可，这样主备就是一致的，相当于没有执行该事务。
• 情况二：一阶段提交成功，写完binlog之后崩溃
  此时，redolog处于prepare状态，binlog已经写入，这时候检查binlog中的事务是否存在且完整，如果存在且完整，则直接提交事务，如果不存在或者不完整，则回滚事务。
• 情况三：prepare成功，binlog写入成功，但是在redolog日志中置为commit状态时崩溃了
  此种情况重启后的方案同情况二。

6、索引失效的场景有哪些

• 在索引上做任何操作（计算、函数、自动/手动类型转换），会导致索引失效而转向全表扫描
• 索引字段上使用（！= 或者 < >）判断时，会导致索引失效而转向全表扫描
• 索引字段上使用 is null / is not null 判断时，会导致索引失效而转向全表扫描
• 索引字段使用like以通配符开头（‘%字符串’）时，会导致索引失效而转向全表扫描，也是最左前缀原则。
• 索引字段是字符串，但查询时不加单引号，会导致索引失效而转向全表扫描
• 索引字段使用 or 时，会导致索引失效而转向全表扫描

7、什么是回表

回表就是先通过数据库索引扫描出该索引树中数据所在的行，取到主键 id，再通过主键 id 取出主键索引数中的数据，即基于非主键索引的查询需要多扫描一棵索引树.

8、什么是索引下推?

索引下推（INDEX CONDITION PUSHDOWN，简称ICP）是在MySQL5.6针对扫描二级索引的一项优化改进。 如果存在某些被索引的列的判断条件时，MySQL 将这一部分判断条件传递给存储引擎，然后由存储引擎通过判断索引是否符合 MySQL 服务器传递的条件，「只有当索引符合条件时才会将数据检索出来返回给 MySQL 服务器」

索引下推仅在索引类型是联合索引时生效，且遵循最左匹配原则。

查看索引下推是否开启

select @@optimizer_switch

查看索引下推是否生效
需要使用一张表里的二级索引进行验证，如下图是表中有sender和sender_type组成的二级索引的explain结果。

• Extra列是Using where代表ICP未生效
  

• Extra列是Using index Condition代表ICP生效

索引下推生效了

关闭索引下推

set optimizer_switch = 'index_condition_pushdown=off'

开启索引下推

set optimizer_switch = 'index_condition_pushdown=on'

9、什么是覆盖索引？

覆盖索引（covering index）是一种避免回表查询的优化策略：只需要在一颗索引树上就能获取SQL所需的所有列数据，无需回表，速度更快。

具体的实现方式：

• 将被查询的列建立联合索引，这样就可以直接返回索引中的数据，不需要再通过聚集索引去定位行中的其它列数据，避免回表，例如

  针对表user中的name,level,age这三个字段建立了联合索引,使用如下查询语句：
  select name,level,age from t where name=1 ;
   上面的查询语句仅通过索引树就可以获取到所需的全部列数据，无需回表。

覆盖索引的定义和注意事项：

• 如果一个索引包含了所有需要查询的字段的值（无需回表查询），这个索引就叫覆盖索引。
• MySQL只能使用B+Tree索引做覆盖索引（因为只有B+树能存储索引列值）

10、什么是最左前缀原则?

最左前缀是指，在 where 条件中出现的字段，「如果只有组合索引中的部分列，则这部分列的触发索引顺序」，是按照定义索引的时候的顺序从前到后触发，最左面一个列触发不了，之后的所有列索引都无法触发。

比如有一个 (a,b,c) 的组合索引

#a,b 会命中该组合索引
where a = 1 and b = 1

#a 会命中该组合索引, c 不会
where a = 1 and c = 1

#不会命中该组合索引
where b = 1 and c = 1

11、事务的隔离级别有哪些

• 「读提交」:即能够**「读取到那些已经提交」**的数据
• 「读未提交」:即能够**「读取到没有被提交」**的数据
• 「可重复读」:可重复读指的是在一个事务内，最开始读到的数据和事务结束前的任意时刻读到的同一批数据都是一致的
• 「可串行化」:最高事务隔离级别，不管多少事务，都是依次按序一个一个执行

12、binlog、undolog、relaylog、redolog分别是做什么的?

• binlog 是归档日志，属于 Server 层的日志，是一个二进制格式的文件，用于记录用户对数据库更新的SQL语句信息。主要用途：主从复制、数据恢复。
• undolog 是 InnoDB 存储引擎的日志，用于保证数据的原子性，「保存了事务发生之前的数据的一个版本，也就是说记录的是数据是修改之前的数据，可以用于回滚」，同时可以提供多版本并发控制下的读（MVCC）。主要用途：事务回滚、实现多版本控制(MVCC)
• relaylog 是中继日志，「在主从同步的时候使用到」，它是一个中介临时的日志文件，用于存储从master节点同步过来的binlog日志内容。master 主节点的 binlog 传到 slave 从节点后，被写入 relay log 里，从节点的 slave sql 线程从 relaylog 里读取日志然后应用到 slave 从节点本地。从服务器 I/O 线程将主服务器的二进制日志读取过来记录到从服务器本地文件，然后 SQL 线程会读取 relay-log 日志的内容并应用到从服务器，从而使从服务器和主服务器的数据保持一致。
• redolog 是 「InnoDB 存储引擎所特有的一种日志」，用于记录事务操作的变化，记录的是数据修改之后的值，不管事务是否提交都会记录下来。当有增删改相关的操作时，会先记录到 Innodb 中，并修改缓存页中的数据，「等到 mysql 闲下来的时候才会真正的将 redolog 中的数据写入到磁盘当中」。

13、MVCC有什么作用？

MVCC:多版本并发控制，是现代数据库(包括 MySQL、Oracle、PostgreSQL 等)引擎实现中常用的处理读写冲突的手段，目的在于提高数据库高并发场景下的吞吐性能。

在 MVCC 协议下，每个读操作会看到一个一致性的快照，「这个快照是基于整个库的」，并且可以实现非阻塞的读，用于支持读提交和可重复读隔离级别的实现。

MVCC 允许数据具有多个版本，这个版本可以是时间戳或者是全局递增的事务 ID，在同一个时间点，不同的事务看到的数据是不同的，这个修改的数据是记录在 undolog 中的。

14、SQL慢的原因有哪些

表数据量太大，在做全表扫描

SQL复杂度高

缺乏索引

数据库本身在做磁盘读写(数据库在刷新脏页)

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

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

16、分布式事务怎么实现

分布式理论

• 「本地消息表」
• 「消息事务」
• 「二阶段提交」
• 「三阶段提交」
• 「TCC」
• 「最大努力通知」
• 「Seata 框架

17、SQL调优思路

「表结构优化」

• 拆分字段
• 字段类型的选择
• 字段类型大小的限制
• 合理的增加冗余字段
• 新建字段一定要有默认值

「索引方面」

• 索引字段的选择
• 利用好mysql支持的索引下推，覆盖索引等功能
• 唯一索引和普通索引的选择

「查询语句方面」

• 避免索引失效
• 合理的书写where条件字段顺序
• 小表驱动大表
• 可以使用force index()防止优化器选错索引

「分库分表」

18、脏读、幻读、不可重复读

脏读：读到了其它事务还未正式提交的数据。
幻读： 事务在做范围查询过程中，有另外一个事务对范围内新增或删除了记录（INSERT、DELETE），导致范围查询的结果条数不一致。
不可重复读：一个事务在执行过程中多次读取同一个数据发现不一致。（因为其它事务对该数据进行了修改）

幻读是不可重复读的一种特殊场景：当事务没有获取范围锁的情况下执行SELECT...WHERE 操作可能会产生幻读。

19、InnoDB如何解决脏读、幻读、不可重复读？（MVCC）

通过MVCC机制解决脏读和不可重复读，通过MVCC+间隙锁解决幻读。

脏读的解决
在·读已提交·的隔离级别下，事务只能读取到其它事务已经提交的数据版本，因此如果一个事务在读取数据时，另一个事务已经修改了这些数据但尚未提交，那么读取事务将不会看到这些未提交的更改。
当事务在“读已提交”隔离级别下执行读取操作时，InnoDB获取当前最新的全局事务ID，这个ID表示在当前时刻所有已提交事务的最新状态。 InnoDB会检查每个数据行的版本，如果该版本是由一个小于或等于当前事务ID的事务修改的，并且该事务已提交，则这个版本是可见的。 这保证了事务只能看到在它开始之前已经提交的数据版本。

不可重复读的解决 在RR这种隔离级别下，当使用快照读进行数据读取的时候，只会在第一次读取的时候生成一个Read View，后续所有的快照读都是用的同一个快照，所以就不会发生不可重复读的问题了。

幻读的解决 InnoDB的RR级别中，基于MVCC+间隙锁，在某种程度上可以避免幻读的发生，但是无法完全避免。

20、索引的分类

• 按数据结构：B+tree索引、Hash索引、Full-text索引。
• 按物理存储：聚簇索引（主键索引）、非聚簇索引（二级索引、辅助索引）。
• 按字段个数：单列索引、联合索引。
• 按字段特性：主键索引、唯一索引、普通索引、前缀索引。