知识库——数据库

1. 数据库三范式

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

三大范式只是一般设计数据库的基本理念，可以设计冗余较小、存储查询效率高的表结构。
但不能一味的去追求数据库设计范式，数据库设计应多关注需求和性能，重要程度：需求 - 性能 - 表结构。比如有时候添加一个冗余的字段可以大大提高查询性能。

2. 五大约束

• 主键约束：唯一，非空
• 唯一约束：唯一，可为空，但也只能有一个
• 默认约束
• 外键约束
• 非空约束

3. 事务隔离级别

• 读未提交：另一个事务能看到这个事务未提交的数据。脏读、幻读、不可重复读；

• 读提交：事务提交后才能被另一个事务读到。避免了脏读。大多数数据库模式隔离级别；

• 可重复读：保证一个事务不能被另外一个事务读取未提交的数据之外，还避免了不可重复读，但不能避免幻读，比如第二次会读到新增的行（MySQL 默认隔离级别，幻读使用 mvvc 版本并发控制解决了）；

• 序列化：事务顺序执行，最高隔离级别。

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

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

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

5. 事务传播机制

• REQUIRED 如果当前方法没有事务则加入事务,没有则创建一个事务（Spring默认的事务传播类型）；
• NOT_SUPPORTED 不支持事务,如果当前有事务则挂起事务运行；
• REQUIREDS_NEW 新建一个事务并在这个事务中运行,如果当前存在事务就把当前事务挂起,新建的 事务的提交与回滚一挂起事务没有联系,不会影响挂起事务的操作；
• MANDATORY 强制当前方法使用事务运行,如果当前没有事务则抛出异常；
• NEVER 当前方法不能存在事务,即非事务运行,如果存在事务则抛出异常；
• SUPPORTS 支持当前事务,如果当前没事务也支持非事务状态运行；
• NESTED 如果当前存在事务,则在嵌套事务内执行,嵌套事务的提交与回滚与父事务没有任务关系,反之,当父事务提交嵌套事务也一起提交,父事务回滚会也回滚嵌套事务的,如果当前没有事务,则新建一个事务运行。

6. 索引B+树和hash区别

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

7. B树和B+树区别

参考视频 https://www.bilibili.com/video/BV1Aa4y1j7a4

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

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

9. MyISAM 和 InnoDB区别

• MyISAM 是非事务安全的，InnoDB 是事务安全的
• MyISAM 锁粒度是表级的，而 InnoDB 支持行级锁
• MyISAM 支持全文索引，而 InnoDB 不支持
• MyISAM 相对简单，效率上优于 InnoDB，如果应用执行大量 select 操作适合使用
• InnoDB 支持事务管理，具备 ACID 事务特性，如果应用需要大量 insert 和 update 操作适合使用

10. explain 执行计划

10.1. 作用

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

10.2. 输出各字段意思

• 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 字段显示。

11. 数据库优化

服务器优化

• 调整查询缓存配置参数，可调大到内存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，前提是确定结果集没有重复数据

12. 大表查询优化

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

13. 分区、分表和分库

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

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

14. 常见分区分表策略

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

参考

• [1] https://www.bilibili.com/video/BV1Aa4y1j7a4
• [2] 聚簇索引和非聚簇索引

勘误更新

GitHub链接