数据库

1. 数据库三范式

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

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

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

2. 五大约束

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

3. 事务隔离级别

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

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

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

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

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

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. 对比

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

9. MyISAM 和 InnoDB 的区别和选择

区别：

MyISAM 不支持事务，InnoDB 支持事务。这是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一；
MyISAM 锁粒度是表级的，而 InnoDB 支持行级锁；
MyISAM 不支持外键，，而 InnoDB 支持；
MyISAM 是非聚集索引， InnoDB 是聚集索引；
InnoDB 不保存表的具体行数，执行 select count(*) from table 时需要全表扫描。而MyISAM 用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快。

选择：

是否要支持事务，如果要请选择 InnoDB，如果不需要可以考虑 MyISAM；
如果表中绝大多数都只是读查询，可以考虑 MyISAM，如果既有读写也挺频繁，请使用InnoDB；
系统奔溃后，MyISAM恢复起来更困难，能否接受，不能接受就选 InnoDB。

参考

10. 日志模块

MySQL 有两个重要的日志模块 redo log（重做日志）和 binlog（归档日志），这两种日志有以下三点不同：

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 的映射关系