Database

1. 数据库三范式

列不可分，确保表的每一列都是不可分割的原子数据项。作用：方便字段的维护、查询效率高、易于统计。
属性字段完全依赖（完全依赖指不能存在仅依赖主键的部分属性）于主键。作用：保证每行数据都是按主键划分的独立数据。
任何非主属性字段不依赖于其它非主属性字段。作用：减少表字段与数据存储，让相互依赖的非主键字段单独成为一张关系表，记录被依赖字段即可。

三大范式只是一般设计数据库的基本理念，可以让我们设计冗余较小、存储查询效率高的表结构。

但不能一味的去追求数据库设计范式，数据库设计应多关注需求和性能，重要程度：需求 -> 性能 -> 表结构。比如有时候添加一个冗余的字段可以大大提高查询性能。

2. 五大约束

主键约束：唯一，非空
唯一约束：唯一，可为空，但也只能有一个
默认约束：没有规定的其他值，会将默认值写入字段
外键约束
非空约束

3. 事务

3.1. 事务隔离级别

读未提交：另一个事务能看到这个事务未提交的数据。会有脏读、幻读、不可重复读问题；
读提交：事务提交后才能被另一个事务读到。避免了脏读问题。这是大多数数据库模式隔离级别；
可重复读：除了保证一个事务不能被另外一个事务读取未提交的数据之外，还避免了不可重复读，但不能避免幻读，比如第二次会读到新增的行。这是MySQL 默认隔离级别，幻读使用 mvvc 版本并发控制解决了；
序列化：事务顺序执行，是最高隔离级别。

各个隔离级别下产生问题的对比如下：

隔离级别	脏读	不可重复读	幻读
读未提交	√	√	√
读已提交	×	√	√
可重复读	×	×	√
序列化	×	×	×

3.2. 主流数据库的隔离级别

MySQL 默认的事务隔离级别是 REPEATABLE-READ，也就是可重复度；
Oracle 支持 READ COMMITTED 和 SERIALIZABLE这两种事务隔离级别。默认系统事务隔离级别是READ COMMITTED,也就是读已提交；
SQL Sever 默认隔离级别是 READ COMMITTED，也就是读已提交。

3.3. MVCC 多版本并发控制

数据库通常使用锁来实现隔离性，原始的锁只有读读操作才可以并发执行，读写、写读和写写操作都要堵塞，导致数据库并发性能差。使用 MVCC（Multiversion Concurrency Control）机制后，只有写写之间相互堵塞，其他三种方式都可以并行，读取数据通过快照的方式保存下来，这样读锁和写锁就不冲突了，不同事务只会看到自己特定版本的数据。

MySQL MVCC 只在 READ COMMITTED 和 REPEATABLE READ 两个隔离级别下工作。其他两个隔离级别够和MVCC不兼容, 因为 READ UNCOMMITTED 总是读取最新的数据行, 而不是符合当前事务版本的数据行。而 SERIALIZABLE 则会对所有读取的行都加锁。

3.4. ACID原则，事务的四个特性

原子性(atomicity)：一个事务要么全部提交成功，要么全部失败回滚，不能只执行其中的一部分操作（只管成功与否，不管是否正确）；
一致性(consistency)：事务的一致性指的是在一个事务执行之前和执行之后数据库都必须处于一致性状态。确保了任何事务都会使数据库从一种合法的状态变为另一种合法的状态（数据库各种约束规则起作用，代码自己实现保证符合逻辑，比如转账不能超出余额）；
隔离性(isolation)：并发环境下，每个事务都有各自完整的数据空间，对修改隔离，数据要么是修改前的状态，要么是修改后的状态，不能是中间的状态。
持久性(durability)：事务成功之后，对数据库的操作必须永久保存下来，必须生效。即使系统奔溃，也应能恢复到事物成功后的状态。

4. MySQL 基础架构

大体来说，MySQL 可以分为 Server 层和存储引擎层两部分。

Server 层包括连接器、查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。不同的存储引擎共用一个Server 层。

5. 数据库中的悲观锁和和乐观锁

悲观锁：假设每一次拿数据，都有认为会被修改。例如给数据库的行或表上锁，看以下 for update 的例子。此外注意 for update 要用在索引上，不然会锁表。

START TRANSACTION; # 开启事务
select * from table_name where id=1 for update;
UPDATE table_name SET name= 'nathan' WHERE id = 1;
COMMIT; # 提交事务

乐观锁：就是乐观认为每次去拿数据的时候都认为别人不会修改。更新时如果 version 变化了，更新不会成功。

update status set name='nathan',version=(version+1) where id=1 and version=1;

6. 索引B+树和hash区别

B+树是一个平衡的多叉树，从根节点到叶子节点逐级查找；
哈希索引是采用一定的哈希算法，把键值换算成新的哈希值，只需一次哈希算法即可立刻定位到相应的位置；
如果是等值查询，那么哈希索引明显有绝对优势；
如果是范围查询检索，这时候哈希索引就毫无用武之地了，哈希索引也没办法利用索引完成排序，也不支持多列联合索引的最左匹配规则，在有大量重复键值情况下，哈希索引的效率也是极低的，因为存在哈希碰撞问题；
在大多数场景下，都会有范围查询、排序、分组等查询特征，用B+树索引就可以了。

7. B树和B+树区别

这个视频讲解的比较好: https://www.bilibili.com/video/BV1Aa4y1j7a4

7.1. B树

B-tree 即 B树，有人又叫成 B-树。

所有键值分布在整颗树中（索引值和具体 data 都在每个节点里）；
任何一个关键字出现且只出现在一个结点中；
搜索有可能在非叶子结点结束（最好情况O(1)就能找到数据，找到自然就结束了，不需要一直到叶子结点）；
在关键字全集内做一次查找，性能逼近二分查找。

7.2. B+树

所有关键字存储在叶子节点，内部节点(非叶子节点)并不存储真正的 data；
为所有叶子结点增加了一个链指针。

8. 聚簇索引和非聚簇索引

参考：https://www.cnblogs.com/jiawen010/p/11805241.html

8.1. 聚簇索引

将数据存储与索引放到了一块，数据实际上存放在索引的叶子节点上，一个表只能有一个聚簇索引，通常是主键。

聚簇索引就是按照每张表的主键构造一颗 B+树，同时叶子节点中存放的就是整张表的行记录数据，也将聚集索引的叶子节点称为数据页。这个特性决定了索引组织表中数据也是索引的一部分，每张表只能拥有一个聚簇索引。

InnoDB 主键索引与行记录是存储在一起的，如果没有主键索引，则会使用 unique 索引，如果也没有 unique 索引，则会使用数据库内部的一个行的 id 来当作主键索引。

优点：
- 数据访问更快，因为聚簇索引将索引和数据保存在同一个 B+树中，因此从聚簇索引中获取数据比非聚簇索引更快。 - 聚簇索引对于主键的排序查找和范围查找速度非常快。

缺点：
- 插入速度严重依赖于插入顺序。按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于 InnoDB 表，我们一般都会定义一个自增的 ID 列为主键。 - 更新主键的代价很高，因为会导致被更新的行移动。因此，对于 InnoDB 表，我们一般定义主键为不可更新。 - 二级索引访问需要两次索引查找，第一次先找到主键值，第二次再根据主键值找到行数据。

8.2. 非聚簇索引

将数据存储与索引分开，索引结构的叶子节点指向了数据的对应行。

Innodb 表除了聚簇索引之外的非聚簇索引称之为辅助索引，辅助索引访问数据总是需要二次查找。辅助索引叶子节点存储的不再是行的物理位置，而是主键值。通过辅助索引首先找到的是主键值，再通过主键值找到数据行的数据页，再通过数据页中的 Page Directory 找到数据行。

Innodb 辅助索引的叶子节点包含了键值和相应行数据的聚簇索引键，不包含行记录的全部数据。

辅助索引的存在不影响数据在聚簇索引中的组织，所以一张表可以有多个辅助索引。在 Innodb 中有时也称辅助索引为二级索引。

MyISAM 使用非聚簇索引，索引与行记录是分开存储的，其主键索引与普通索引没有本质差异，非聚簇索引的两棵 B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别。由于索引树是独立的，通过辅助键检索无需访问主键的索引树。

8.3. 聚簇索引和非聚簇索引对比

下图示清晰的显示了聚簇索引和非聚簇索引的差异：

8.4. 回表和覆盖索引

从上图 InnoDB（聚蔟）表分布可以看到，如果我们要通过非主键索引查询行数据，首先要在辅助索引树得到主键值，然后再到主键索引树上通过主键查到对应的行数据，回到主键索引树搜索的过程，我们成为回表。

但是，如果我们只需要查询有辅助索引的字段，就不需要回表。比如还是以上图为例，在数据表查询 Name 字段为 Jobs，sql select Name from table where Name='Jobs'，因为辅助索引本身就是 Name 字段，所以就不用回表了。

由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

8.5. 最左前缀匹配原则

在联合索引中，只要插叙条件与联合索引从左到右字段顺序能匹配上，就能应用索引。

比如一个 user 表有列 name 和 age，建立联合索引（name，age），查询姓“李”的名字 sql 语句条件是“where name like '李%'”，这时能用上这个索引。不过此时

当你既有联合索引需求又有两段字段单独索引需要，那就要考虑空间占用了。比如字段 name 和 age 建立联合索引应该建（name，age）而不是（age，name），再为 age 建一个索引，因为 age 存储的数据比 name 小。

8.6. 短索引（前缀索引）

对于数据类型是字符串的字段，如果存储的字符串都是很长的而且前n个字符就能确定范围或者唯一，那么就可以创建短索引，节省空间。

CREATE INDEX index_name
    ON table_name (column_name(length))

9. MyISAM 和 InnoDB区别

MyISAM 是非事务安全的（不支持事务），InnoDB 是事务安全的
MyISAM 锁粒度是表级的，而 InnoDB 支持行级锁
MyISAM 支持全文索引，而 InnoDB 不支持（InnoDB 存储引擎在 MySQL 5.6.4 版本中也开始支持全文索引）
MyISAM 相对简单，效率上优于 InnoDB，如果应用执行大量 select 操作适合使用
InnoDB 支持事务管理，具备 ACID 事务特性，如果应用需要大量 insert 和 update 操作适合使用

10. 日志系统 redo log 和 binlog

redo log 是 InnoDB 引擎特有的日志，而 Server 层（连接、分析、优化和执行器部分）也有自己的日志，称为 binlog（归档日志）。

MyISAM 没有 crash-safe 的能力，binlog 日志只能用于归档；redo log 有 crash-safe 能力。

总结区别： - redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。 - redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。 - redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

11. explain 执行计划

11.1. 作用

表加载顺序
sql 查询类型
分析索引应用
多少行被优化器查询

11.2. explain 语句输出各字段意思

id：查询优先级，越大优先级越高，相同时由优化器决定
select_type：查询类型，如普通查询、联合查询和子查询
- SIMPLE：普通类型
- PRIMARY：查询语句包含任何子部分，最外层查询就被标记为 PRIMARY
- SUBQUERY：当 select 或 where 包含了子查询，该子查询被标记为 SUBQUERY
- DERIVED：包含在 from 子句中的子查询就会被标记为 DERIVED
- UNION：若第二个 select 出现在 UNION 之后，则被标记为 UNION
- UNION RESULT：从 UNION 表获取结果的 select
talbe：表名，不一定是真实存在的表，有别名显示别名，也有可能是临时表
partitions：查询匹配到的分区信息，对于非分区表值为 null，当查询分区表时，partitions 显示分区表命中的分区情况
type：查询使用的类型，性能从好到坏是 system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
- null：不需要访问任何表和索引，直接返回结果
- const/system：单条记录，系统会把匹配行中的其他列作为常数处理，如主键或唯一索引查询
- eq_ref：常出现于关联查询，使用主键或唯一索引
- ref：区别于 eq_ref ，表示使用非唯一性索引，可能会找到多个符合条件的行
- ref_or_null：类似于 ref ，区别是会额外搜索包含 NULL 的行
- index_merge：使用了索引合并优化方法，查询使用了两个以上的索引
- unique_subquery：替换了形式 value IN (SELECT primary_key FROM X)的 IN 子查询的ref
- index_subquery：区别于 unique_subquery，用于非唯一索引，可以返回重复值
- range：只检索给定范围的行，使用一个索引来选择行
- index：Index 与 ALL 其实都是读全表，区别在于 index 是遍历索引树读取，而ALL是从硬盘中读取
- ALL：将遍历全表以找到匹配的行，性能最差
ref：常见的有 const，func，null，字段名。当使用常量等值查询，显示 const；当关联查询时，会显示相应关联表的关联字段；如果查询条件使用了表达式、函数，或者条件列发生内部隐式转换，可能显示为 func；其他情况 null。
rows：表的统计信息和索引使用情况，估算要找到我们所需的记录，需要读取的行数。
filtered：百分比的值，表里符合条件的记录数的百分比。简单点说，这个字段表示存储引擎返回的数据在经过过滤后，剩下满足条件的记录数量的比例。
Extra：不适合在其他列中显示的信息，Explain 中的很多额外的信息会在 Extra 字段显示。

12. 数据库优化

服务器优化:

调整查询缓存配置参数，单机数据库可调大到内存80%
开启 sql 慢查询日志

sql 优化:

尽量只查必要的字段，而不是使用 select * 查询全部字段
注意索引的使用，避免索引失效或者查询数据量太大
连表查询操作应是大表 join 小表，即小表驱动大表
in 包含值不应过多
只需要一条数据时使用 limit 1
若排序字段没用到索引，尽量不适用排序
区分 exists 和 in。in 适用于外表大内表小情况；exists 适用于外表小内表大的情况
使用 not exists 而不是 not in，not in 在包含 null 值时会有问题，须自行排除
避免在 where 子句对字段进行 null 判断、表达式操作
不建议使用 % 前缀模糊查询（MyISAM 可使用全文索引）
避免隐式类型转换，比如字符串和数字
联合索引要遵守最左前缀法则
使用合理的分页方式提高效率，若 id 是自增可加条件大于上次最大 id，或者使用延迟关联查询
尽量使用 union all 代替 union，前提是确定结果集没有重复数据

13. 大表查询优化

添加合理的索引；
若是主键自增且按主键排序，每次分页查询带上上次最大 id 最为条件，缩小查询范围；
使用延迟关联优化，减少回表次数，通过使用覆盖索引查询返回需要的主键，再根据主键关联原表获得需要的数据。

14. 分区、分表和分库

分区：把一张表的数据分成 N 个区块，在逻辑上看最终只是一张表，但底层是由 N 个物理区块组成的；
分表：把一张表按一定的规则分解成 N 个具有独立存储空间的实体表。系统读写时需要根据定义好的规则得到对应的表名，然后操作它；
分库：垂直切分根据业务把不同表放到不同数据库中；水平切分将同表的数据分开存到多个数据库中。

按照系统演进，一般的优化思路为垂直分库 → 水平分库 → 读写分离

15. 常见分区分表策略

Range（范围）
Hash（哈希）
按时间拆分
Hash 之后按照分表个数取模
在认证库中保存数据库配置，建立一个 DB，这个 DB 单独保存 user_id 到 DB 的映射关系