范式

数据库的三范式是什么？

第一范式(1NF)

列（即属性，对应于表中的字段）不可再分。1NF 是所有关系型数据库的最基本要求 ，换句话说就是关系型数据库中创建的表一定满足第一范式。

第二范式(2NF)

2NF在1NF的基础上，消除了非主属性对于主属性的部分函数依赖。简单地说，表中的非主属性必须完全依赖于主属性，即不能存在仅依赖候选键一部分的属性。

例如：以下员工基本信息表中，使用部门名称和姓名作为复合主键，但部门地址只依赖于部门名称，它是一个部分依赖，并非完全依赖，即不满足第二范式的要求。数据存在冗余。

不符合第二范式

应当使用主键进行约束，将部门编号抽离成部门表，再将员工使用工号主键进行约束，此设计符合第二范式。如下图所示。

第二范式

第三范式(3NF)

第三范式（3NF）首先需要满足第二范式，非主键字段不能依赖于其他非主键字段。

不符合第三范式

上图的员工表中，存在传递依赖，即工号->职位->职位信息，不符合第三范式。此处在对职位进行修改时可能导致数据异常，所以我们将职位信息抽离为职位表，使其符合第三范式。如下图所示：

符合第三范式

总结

第一范式：属性（列）不可再分。
第二范式：在第一范式的基础上消除非主属性对主属性的部分函数依赖。
第三范式：在第二范式的基础上消除了非主属性对非主属性的传递函数依赖。

索引

索引是什么？

索引是一种用于快速查询和检索数据的数据结构。常见的索引结构有：B 树， B+树和 Hash。通俗地说，数据库索引好比是一本书前面的目录，能够加快对数据的查询速度。

索引的优缺点

优点

提高数据的检索速度，降低数据库IO成本：使用索引的意义在于通过缩小表中需要查询的记录数从而加快搜索的速度。
降低数据排序的成本，降低CPU消耗；索引之所以查的快，是因为先将数据排好序，若该字段正好需要排序，则正好降低了排序的成本。

缺点

占用存储空间：索引本质上也是一张表，记录了主键与索引字段，一般以索引文件的形式存储在磁盘上。
降低更新表的速度：表的数据发生变化，对应的索引也需要一起变更，否则索引指向的物理数据可能不对，这也是索引失效的原因之一。

常见的索引及其区别

二叉查找树

二叉查找树查询的时间复杂度为O(logN)，但是由于每个节点只能有两个，所以会导致树的结构又高又瘦，会增加磁盘的IO次数。如果想要减少磁盘的IO次数则必须减少树的高度，让又瘦又高的树变成又胖又矮的树。

B树🌲

B树也称B-树，即多路平衡查找树。在B树中，每个节点可以包含多个关键字和子节点，可以降低树的高度，减少磁盘IO的次数。

B+树🌲

B+ 树是 B 树的一种变体，B+树的非叶子节点中不会存储数据，数据都存储在叶子节点中。相同情况下可以比B树存储更多的关键字，磁盘IO次数更低。除此之外，B+树还有一个链表将叶子节点连接起来，可以实现范围查询。

哈希索引

哈希表是键值对的集合，通过键(key)可快速取出值(value)。通过哈希散列算法，我们可以快速检索数据（时间复杂度为O(1)）。虽然其检索效率很高，但是哈希算法可能会出现Hash冲突的问题，在极端情况下可能退化成一个链表（时间复杂度为O(n)），所以在数据库中使用Hash索引的性能是不稳定的。这还不是最致命的缺点，其最致命的缺点在于Hash索引不支持顺序和范围查询。例如我们要查询[200,210]这个区间的数据，就无法通过Hash索引实现。

综上所述，大部分数据库系统使用B+树作为索引，而不是B树或哈希索引。

B树和B+树的区别

B树的所有节点既存放键(key)也存放数据(data)，而B+树只有叶子节点存放键和数据，其他节点都只存放键。
B树的叶子节点都是独立的，B+树的叶子节点有一条链表指向与它相邻的叶子节点。
B树的检索的过程相当于对范围内每个节点的关键字做二分查找，可能还没有到达叶子节点，检索就结束了。而B+树的检索效率就很稳定了，任何查找都是从根节点到叶子节点的过程，叶子节点的顺序检索很明显。

密集索引和稀疏索引

密集索引

密集索引也称为稠密索引，它的定义为：密集索引文件中的每个搜索码值都对应一个索引值。通俗来讲就是叶子节点保存的不仅仅是键值信息，还保存了整行数据。密集索引决定了表的物理排列顺序，所以一个表中只能有一个密集索引。

稀疏索引

稀疏索引定义为：只为索引码的某些值建立索引项，可以理解为叶子节点仅保存了键值信息和该行数据的地址，有的稀疏索引仅保存了键值信息及其主键。定位到叶子节点后仍需通过地址或主键信息才能定位到数据。

不同数据库引擎下的密集索引和稀疏索引

此处以常用的MySQL数据库为例，对比InnoDB与MyISAM引擎索引的区别。

InnoDB

若一个主键被定义了，那么该主键则为密集索引；若没有主键被定义，则该表的第一个唯一非空索引作为密集索引。如果以上条件均不满足，InnoDB会生成一个隐藏的主键作为密集索引。

MyISAM

MyISAM索引和数据是分开保存。MyISAM的主键索引和辅助索引都是稀疏索引，B+树的叶子节点保存的是该行数据所在的地址，先根据索引找到数据所在的地址，再根据该地址去查找对应的数据。

MySQL索引失效的情况

like以%开头，索引无效；当like前缀没%，后缀有%时，索引有效。
or语句前后没有同时使用索引。当or左右查询字段只有一个是索引时，该索引失效；只有左右查询字段均为索引时，才会生效。
组合索引，不是使用第一列索引，索引失效。
数据类型出现隐式转化，比如varchar不加单引号可能会自动转化为int类型，使索引无效。
在索引列上使用IS NULL或IS NOT NULL，索引可能会失效。
在索引字段上使用not、<>、!=时不会用到索引。优化方法key<>0改为key>0 or key<0。
对索引字段进行计算操作、字段上使用函数，也会导致索引失效。
当全表扫描速度比索引速度快时，会使用全表扫描，索引失效。使用explain命令加在要分析的sql语句前，在执行结果中查看key这一列的值，如果为NULL，说明没有使用索引。

如何定位并优化慢查询SQL

定位

我们可以先通过慢日志定位到慢查询SQL，具体步骤如下：
1. SHOW VARIABLES LIKE '%QUERY%'，查询关于query的变量，查询结果如下图所示：
2. 根据slow_query_log_file对应的值找到对应路径下的慢日志，再根据慢日志查看是哪些SQL语句执行较慢。
此处对上图一些较为重要的参数做个解释：
- long_query_time：慢日志阈值，超过这个时间会被记录。（单位：秒）
- slow_query_log：打开/关闭慢日志
- slow_query_log_file：慢日志记录的位置
使用explain分析工具：

explain有三个关键字段，分别是type、extra和key。
- type：type是找到数据行的方式，type的值有很多，无需单独记忆，只需要清楚：若type的值是index或all时，则代表全表扫描，需要优化。
- extra：出现using filesort或者using temporary则需要优化。
- key：如果没有使用索引，则key为null，可能需要优化让其走索引。走了索引则key为索引名。

优化

我们可以修改SQL语句或尽量让SQL走索引。在实际使用中，MySQL中的查询优化器会决定使用哪个索引。（补充：我们可以在SQL语句后加force index(primary)可强制使用主键索引。）
根据最左匹配原则进行优化：MySQL会一直向右匹配直到遇到范围查询（> , < , between, like）就停止匹配，后面的数就没有机会使用到索引了。

举个例子：a = 3 and b = 4 and c > 5 and d = 6 如果建立(a, b, c, d)顺序的索引，则d是用不到索引的。因为碰到c > 5这个范围查询时，后面的数就会停止匹配。如果建立(a, b, d, c)顺序的索引，则可以触发索引。因为范围查询c > 5被放到了最后。补充说明，a、b、d的顺序是可以任意调整的。即：= 和in可以乱序，比如a = 1 and b = 2 and c = 3建立（a , b , c）索引可以任意顺序，MySQL查询优化器会帮你优化成索引可以识别的形式。

综上所述，范围查询在联合索引里应尽量放到最右侧。

索引是越多越好吗？

先说结论，索引不是越多越好。原因如下：

数据量小的表是不需要索引的，建立索引会增加额外的开销。
数据变更时需要维护索引，因此更多的索引意味着更高的维护成本。
索引本身是一种空间换时间的思想，因此更多的索引意味着需要更多的空间。

事务

数据库事务的四大特性

事务：多条SQL语句，要么全部成功，要么全部失败。

数据库事务的四大特性（ACID）如下：

原子性（Atomic）：事务所有的操作要么全部成功提交，要么全部失败回滚。

一致性（Consistency）：所有事务对一个数据的读取结果都是相同的。

隔离性（Isolation）：一个事务的操作在最终提交之间，对其他事务都是不可见的。

持久性（Durability）：一旦事务提交，其所作的修改都会永久保存到数据库中。

四大特性的关系：满足一致性，事务执行结果正确。无并发情况下，只要满足原子性，就能满足一致性。有并发情况下，不仅要满足原子性，还要满足隔离性，才能满足一致性。满足持久性，是为了应对数据库崩溃的情况。

事务并发访问一致性的问题

丢失修改（Lost to modify）：事务1和事务2都对同一个数据进行修改，事务1先修改，事务2再修改，这样事务2的修改就覆盖了事务1的修改。
脏读（Dirty read）：事务1修改一个数据，事务2随后读取了这个数据，如果事务1回滚了这次操作，事务2并不知道，那么事务2读到的就是脏数据。
不可重复读（Unrepeatable read）：事务1读取了一个数据，事务2对该数据进行了修改，如果事务1再次读取这个数据，此时读取的结果和第一次读取的结果不同，则称为不可重复读。
幻读（Phantom read）：事务1读取某个范围的数据，事务2在这个范围内插入了新的数据，事务1再次读取这个范围内的数据，发现读取的结果和第一次读取的结果不同，好像出现幻觉一样，这种现象就称为幻读。

不可重复读与幻读的区别在于：不可重复读的侧重点是修改，而幻读的重点在于新增或删除。

事务隔离级别

READ-UNCOMMITED(读取未提交)：最低的隔离级别，允许读取尚未提交的数据变更，即事务中的修改，即使没有提交，对其他事务也是可见的。该级别可能导致脏读、幻读或不可重复读。
READ-COMMITED(读取已提交)：允许读取已经提交的数据，即一个事务只能读取已提交事务所做的修改，也就是说，在修改被提交之前对其他事务是不可见的。该级别可以阻止脏读，但不能阻止幻读或不可重复读。
REPEATABLE-READ(可重复读)：保证在同一个事务中多次读取同样的数据结果是一致的，除非数据被自身事务所修改。该级别可以阻止脏读和不可重复读，但无法阻止幻读。
SERIALIZABLE(可串行化)：事务的最高隔离级别，强制事务串行执行，这样事务之间不会相互干扰，安全级别最高，但并发度最低。该级别可以防止脏读、不可重复读、幻读。

下面我用一个表总结事务的隔离级别和问题：

隔离级别	脏读	不可重复读	幻读
READ-UNCOMMITED	发生	发生	发生
READ-COMMITED	避免	发生	发生
REPEATABLE-READ	避免	避免	发生
SERIALIZABLE	避免	避免	避免

Oracle默认的事务隔离级别为READ-COMMITED即读取已提交，MySQL默认为REPEATABLE-READ可重复读，但MySQL的InnoDB引擎下的可重复读隔离级别却可以避免幻读。具体原因我们在下文进行介绍。在了解其原因之前，我们需要先补充一些前置知识。

MVCC

MVCC（Multi-Version Concurrency Control）：多版本并发控制，是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问，在编程语言中实现事务内存。 MVCC在MySQL InnoDB 中的实现主要是为了提高数据库并发性能，用更好的方式去处理读-写冲突，做到即使有读写冲突时，也能做到不加锁，非阻塞并发读。

当前读

当前读即上锁之后的增删改查语句，当前读记录的是数据的最新版本，并且读取之后还需要保证其他并发事务不能修改当前记录。

共享锁：select … lock in share mode, select … for update和排它锁：update, delete, insert都属于当前读。

快照读

像不加锁的select操作就是快照读，即不加锁的非阻塞读。快照读的前提是隔离级别不是串行级别，在串行级别下的快照读会退化成当前读。

之所以出现快照读是因为基于提高并发性能的考虑，快照读的实现基于多版本并发控制即MVCC，可以认为MVCC是行锁的一个变重，但它在很多情况下避免了加锁操作，降低了开销。既然是基于多版本，快照读可能读到的并不一定是数据的最新版本，有可能是历史版本。

undo日志

当对数据做了变更操作时，undo日志会存储着各个老版本的数据，当旧的事务要读取老版本数据时，会顺着undo链找到满足其可见性的数据。一般分为insert undo log，update undo log。

insert undo log：事务插入新数据时产生，仅在事务回滚时需要，事务提交后就可删除。
update undo log：事务修改或删除新数据时产生，不仅在事务回滚时需要，快照读也需要。

Read View

简单来说，Read View用来做可见性判断，决定事务看到的是哪个版本的数据。

RC（读取已提交）和RR（可重复读）隔离级别下MVCC（非阻塞读）的区别

在RC读取已提交隔离级别下：事务每次快照读(select)，都会创建一个新的快照（Read View）。其他事务修改了数据，下次快照读时用的是新的快照（Read View），不是之前的，所以就还会存在不可重复读和幻读的情况。

在RR可重复读隔离级别下：事务第一次快照读时，会生成一个有创建版本号的快照（Read View）。当事务再次快照读时，所读取Read View的创建版本号必须小于当前Read View的版本号，如果大于，则说明该快照是其他事务最新修改创建的，就不会去读它。所以再次快照读时，读的一直是之前的快照，就避免了不可重复读和幻读的情况。简而言之，即第一次select时更新这个Read View，以后不会再更新，后续所有的select都是复用这个Read View。所以能保证每次读取的一致性，即都是读取第一次读取到的内容。

RC（读取已提交）和RR（可重复读）隔离级别下MVCC（非阻塞读）如何实现？

存储在MySQL中的每个数据行都有隐藏的字段，此处对三个重要字段进行讲解。

DB_TRX_ID：标识最后一次对本行数据做修改的事务ID。
DB_ROLL_PTR：回滚指针，指向undo log中的记录。
DB_ROW_ID：若没有主键或唯一非空索引，会创建隐藏主键。

MVCC的实现依赖于隐藏字段、Read View、undo log，在内部实现中，InnoDB 通过数据行的 DB_TRX_ID 和 Read View 来判断数据的可见性，如不可见，则通过数据行的 DB_ROLL_PTR 找到 undo log 中的历史版本。每个事务读到的数据版本可能是不一样的，在同一个事务中，用户只能看到该事务创建 Read View 之前已经提交的修改和该事务本身做的修改。

InnoDB可重复读隔离级别下是如何避免幻读的？

在可重复读隔离级别下，InooDB采用的是next key lock，next key lock的组成是行锁+Gap锁。在读某个范围的数据时，不仅会锁住行，还会锁住行之间的间隙，所以其他事务不能在读取的范围内加入新的数据，因此解决了幻读问题。

Gap锁出现的条件：

在主键索引或唯一索引的情况下，如果where条件全部命中，则不会用Gap锁，只会加记录锁；如果where条件部分命中（包括范围查询）或全不命中，会加Gap锁。

在非唯一索引或者不走索引的情况下，会对所有的Gap上锁，就会锁住整张表。

MyISAM和InnoDB的区别

MySQL 5.5版本后默认的存储引擎为InnoDB。我们从锁、事务、外键、索引方面来具体看看其区别。

锁

MyISAM只有表级锁，其不支持行级锁，表级锁不会出现死锁，但触发锁冲突的概率高，并发度最低。而InnoDB同时支持行级锁和表级锁，默认为行级锁，锁更细粒度化，其并发度高，但加锁的开销也大，并且可能出现死锁。

InnoDB对select做了优化，不会自动上读锁，需要在语句后面加上"lock in share mode"才会加行级读锁，加上"for update"会加行级写锁。增删改数据会默认增加行级写锁。在数据commit之前不会释放锁。

InooDB还支持表级的意向读锁和表级的意向写锁，主要是为了在进行表级别操作时，不用去轮询每一行是否加了行级锁。

补充：对MyISAM而言，读锁与读锁之间可以兼容，读锁和写锁、写锁和写锁之间不能兼容。加锁命令：lock tables *** read/write；解锁命令：unlock tables。

是否支持事务？

MyISAM引擎不支持事务。InnoDB引擎支持事务。InooDB默认是自动提交事务的。命令show variables like 'autocommit'可查看其状态；set autocommit = 0/1可关闭或打开自动提交。或者使用begin/start transaction代码块也能使其不自动提交。

是否支持外键？

MyISAM不支持，而InnoDB 支持。不过在大多数场景下，我们不推荐在数据库层面使用外键，外键一般使用逻辑外键，在应用层面解决，除非项目有特别要求。

索引

InnoDB的表包含2个文件，一个是.frm文件，保存的是表的结构信息；一个是.ibd文件，保存的是索引和数据，索引和数据保存在一起。MyISAM的表包含3个文件，一个是.frm文件，保存的是表的结构信息；一个是.MYI文件，保存的是索引；一个是.MYD文件，保存的是数据，索引和数据是分开保存。

InnoDB的主键索引是密集索引，辅助索引是稀疏索引。MyISAM的主键索引和辅助索引都是稀疏索引。

MyISAM适合的场景

频繁执行全表count语句的场景。MyISAM用一个变量保存了整个表的行数；InnoDB需要扫描全表进行统计。
对数据进行增删改的频率不高，查询非常频繁的场景。因为增删改会MyISAM会锁住整张表，会降低性能。
不需要支持事务的场景。

InnoDB适合的场景

增删改数据较为频繁的场景。因为InnoDB支持行级锁，SQL中使用索引增删改无需锁住全表，性能比MyISAM好。
可靠性要求较高，要求支持事务的场景。

三大日志(binlog, redo log, undo log)

binlog

binlog，二进制日志，又称为归档日志，它主要用于记录MySQL表的逻辑变化过程。在实际应用中，常被用于主从复制和数据恢复。binlog的存在能够保证MySQL集群架构数据的一致性。

binlog的写入逻辑

事务执行的过程中，会先将日志写入binlog cache，提交时再将binlog cache写入到binlog文件中。事务提交后的写入只是写入到文件系统的page cache，并没有将数据永久保存到磁盘。如果需要持久化保存，则需要由操作系统调用fsync刷盘。这个时机是由操作系统决定的。

MySQL提供了配置来定义fsync的调用时机。具体如下：

sync_binlog = 0：每次提交事务只写入page cache，不执行fsync。
sync_binlog = 1：表示每次提交事务都会执行fsync。
sync_binlog = N（N > 1）：每次提交事务都写入page cache，累计多个事务才进行fsync。

结论：当sync_binlog = 1的时候，binlog日志不会丢失，但每次刷盘有较大的性能开销。当sync_binlog = N的时候，如果发生异常重启，会丢失N个事务的binlog日志。

binlog日志格式

STATEMENT：记录SQL语句的原文。但若语句中存在例如update_time=now()这样的非固定值，则在同步数据时会导致执行的结果与原来数据库中的数据不一致。
ROW：ROW基于行复制，会将SQL语句以及具体数据的内容都进行存储，比较占用空间，IO开销大，但可以解决STATEMENT数据不一致的问题。
MIXED：由MySQL来决定数据的格式，对于可能引发主备不一致的命令使用ROW格式，否则使用STATEMTNT。

undo log

insert undo log：事务插入新数据时产生，仅在事务回滚时需要，事务提交后就可删除。
update undo log：事务修改或删除新数据时产生，不仅在事务回滚时需要，快照读也需要。

redo log

redo log是InnoDB引擎特有的。它让MySQL拥有崩溃恢复的能力。若因为意外原因导致MySQL进程异常退出，重启时，InnoDB引擎会使用redo log恢复数据。

对于每一次更新操作，MySQL都需要写入磁盘，然后需要找到对应那条记录并更新。IO成本较高和查找成本都很高。为了提高性能，MySQL会将更新操作写入redo log，并更新内存。InnoDB引擎会在适当的时候将操作记录更新到磁盘。

刷盘时机

我们可以通过设置innodb_flush_log_at_trx_commit参数来指定redo log什么时机写入磁盘。InnoDB提供了以下三种刷盘时机：

0：表示每次事务提交时不进行刷盘操作。
1：表示每次事务提交时都将进行刷盘操作（默认值）。
2：表示每次事务提交时都只将redo log buffer的内容写入page cache。

redo log 写入逻辑

redo log文件内容是以顺序循环的方式写入文件，写满时则回溯到第一个文件，进行覆盖写。

redo-log

如图所示，Write Pos指针是记录当前的位置，一边写一边后移。写到最后一个文件末尾就回到ib-logfile-0的文件开头；

CheckPoint指针表示当前要擦除的位置。当Write Pos追上CheckPoint时，表示已经写满，此时日志不能再更新了，得先停下来擦除部分记录。这个时候，CheckPoint会往后推移，并且也是循环的，擦除记录前会把记录更新到数据文件。

如何设计一个关系型数据库？

文章的最后，我们来谈谈一个庞大的问题，如何设计一个关系型数据库？

在面试中，被问到这样的问题，面试者常常会一头雾水不知从何答起。其实这类问题主要考察的是同学们对数据库模块的熟悉程度，无需将所有技术细节都说出来，毕竟短时间内也无法回答那么多的内容。下面给出一个回答供同学们参考。

答：将一个关系型数据库划分为两大部分：第一部分为存储部分，相当于文件系统，将数据持久化到存储设备中；第二部分为程序实例，对数据进行逻辑上的管理。程序实例部分可以设计成多个模块：将数据的逻辑地址转化为物理地址的存储管理模块；优化执行效率的缓存模块；对SQL语句进行解析的SQL解析模块；记录对数据库操作的日志管理模块；进行多用户管理的权限划分模块；灾难恢复的容灾机制模块；优化数据查询效率的索引模块；使数据库支持多并发的锁模块。

数据库常见知识点以及面试题总结

目录

范式