MySQL

代码小郭...大约 10 分钟

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 的情况下，一条更新语句是怎么执行的?

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

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

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

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

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

7、什么是回表

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

8、什么是索引下推?

如果存在某些被索引的列的判断条件时，MySQL 将这一部分判断条件传递给存储引擎，然后由存储引擎通过判断索引是否符合 MySQL 服务器传递的条件，「只有当索引符合条件时才会将数据检索出来返回给 MySQL 服务器」

9、什么是覆盖索引？

覆盖索引（covering index）指一个查询语句的执行只用从索引中就能够取得，不必从数据表中读取，可以减少回表的次数。比如:

select id from t where age = 1;

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

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 中的数据写入到磁盘当中」。