04 数据库

索引

1. 数据库索引

# 作用
  - 根据部分字段的值, 为数据生成一组排好序的数据结构;
  - 通过查找该数据结构, 可以帮助快速找到指定数据的所有字段;
# 种类
  - 全文索引, 只有 MyISAM 引擎支持;
  - 哈希索引, 存储内容是混乱的, 不支持范围查找;
  - B+ 树索引, 主流存储结构, 索引和数据分开存放;
  
  - 唯一索引, 索引字段的值唯一
  - 复合索引, 索引包含多个字段
  
  - 聚集索引, 一种存储方式, 即索引和数据保存在一个文件中(InnoDB)
  - 非聚集索引, 索引和数据保存在不同文件(MyISAM)
# 使用场景
  - 查询操作较多;
  - 插入, 删除, 修改操作较少 (创建和维护索引需要消耗计算资源);

2. B+树

# 数据存储的数据结构
  - 数据库底层使用 B+ 树实现;
# B+ 树
  - 非叶子节点: 只存索引和指针, 不存储数据, 索引来自下游节点的最小索引(冗余);
  - 叶子节点: 存储完整的索引和数据, 之间通过链表结构互相连接, 可以实现升序遍历, 范围查找;
  - 索引和真实数据分开存储, 扩大数据库允许的规模;
  - 在内存中, 只加载非叶子节点(冗余索引和指针);
  - 在磁盘中, 存储叶子节点(索引和数据);
# 查找过程
  - 根据查询语句判断所查字段是否有索引;
  - 在索引节点中用二分查找, 获得下游指针;
  - 通过指针找到下游节点, 直至叶子节点;
  - 在叶子节点中继续用二分查找, 获得结果;
# 其他数据结构
  - 二叉查找树: 
    * 每个节点存一个数据(和索引); 
    * 数据有序插入时不平衡, 效率低;
  - 二叉平衡树(红黑树) : 
    * 每个节点存一个数据(和索引), 子树不平衡时, 进行调整; 
    * 当数据过多时, 树的深度会很高, 效率低;
  - B 树: 
    * 每个节点存多个数据(和索引), 树的深度降低;
    * 要加载数据到内存中, 限制了数据库的规模;
  - Hash 表: 
    * 存储内容是混乱的, 不支持范围查找;
    * 如: 获得 > n 的数据;

3. 联合索引

# 联合索引
  - 将几个字段合并在一起, 作为数据的索引;
  - 在插入 B+ 树时, 是根据字段数据依次比较, 然后插入;
# 最左前缀法则
  - 要求在查询语句中, 指定条件应该安照联合索引的顺序从左到右依次给出;
  - 诺只给出索引中间的一个字段, 查询过程中无法使用索引结构;

数据库引擎

1. MyISAM 和 InnoDB 存储结构

# 存储方式不同 - 聚集和非聚集
  - MyISAM
    * 非聚集: 索引文件和数据文件分开存储;
    * frm 文件: 存储表结构 
    * MYI 文件: 存储索引和数据地址
    * MYD 文件: 存储数据
  - InnoDB
    * 聚集: 索引文件和数据文件存储在一起;
    * frm 文件: 存储表结构
    * ibd 文件: 存储索引和数据

2. MyISAM 和 InnoDB 区别

# 特点
  - INNODB : 当前默认, 安全性高, 支持事务处理, 多表多用户操作
  - MYISAM : 早些年使用, 节约空间, 速度较快

	MYISAM	INNODB
事务支持	不支持	支持
数据行锁定	不支持 (表锁定)	支持 (行锁定)
外键约束	不支持	支持
全文索引	支持	不支持
表空间大小	较小	较大, 约两倍
物理文件	*.frm 文件 ibd 文件	* frm 文件 * .MYD 文件 * .MYI 文件

事务

1. 数据库事务

# 事务 ACID 属性
  - 原子性: 事务提交的一系列操作是不可分割的整体, 要么完全执行, 要么完全不执行, 不能只执行一部分;
           (银行一方转钱, 一方收钱必须一起完成)
  - 一致性: 事务完成前后, 逻辑状态保持一致;
           (银行发生转账后, 钱的总量不变)
  - 隔离性: 当一个事务在进行时, 其他事务不会对其进行干扰;
  - 持久性: 在事务进行前后, 数据库内的数据是持久化的, 不会因为外界(断电等)因素造成丢失;

2. 事务异常情况

# 回滚丢失(第一类丢失)
  - 因为其他事务的回滚造成当前事务更新数据的丢失, 如:
    * 原始数据10, 事务A, B均读取到10;
    * 事务A更新到11并提交;
    * 事务B更新失败, 将原数据回滚到10;
    * 导致事务A的更新无效;
# 覆盖丢失(第二类丢失)
  - 因为两个事务先后提交, 后提交的事务数据覆盖了先提交的数据, 如:
    * 原始数据10, 事务A, B均读取到10;
    * 事务A更新到11并提交;
    * 事务B更新到12, 将数据11更新到12;
    * 导致事务A的更新无效;
# 脏读
  - 事务读取到了其他事务未提交前更新的中间数据, 该中间数据后续又因为事务失败而回滚, 造成了脏数据被读取, 如:
    * 原始数据10, 事务A读取到10;
    * 事务A更新到11, 但未提交;
    * 事务B读取到11, 将数据11更新到12;
    * 事务A失败, 原始数据回滚到10;
    * 事务B依然提交12, 导致最终结果包含事务A本应回滚掉的脏数据;
# 不可重复读
  - 同一事务先后读取同一条数据, 但是获得的数据值不一样, 如:
    * 原始数据10, 事务A, B读取到10;
    * 事务B将数据更新到11;
    * 事务A再次读取数据, 获得11, 与原来的10不一样;
# 幻读
  - 同一事务先后更新和读取整个表, 但是发现插入新的数据, 如:
    * 原始表有10条数据, 事务A更新所有数据为0;
    * 事务B将5条新的数据插入到表中;
    * 事务A再次读取数据, 发现15条数据, 其中5条数据没有归0;

3. 事务隔离级别

# 隔离级别
  - 数据库为了防止出现事务异常而设置的机制;
  - 默认隔离级别: Oracle(读已提交), MySql(可重复读)
# 读未提交
  - 最低的事务隔离级别;
  - 允许其他事务在未提交的情况下读;
  - 所有事务异常都不能防止;
# 读已提交
  - 当一个事务要读取数据时, 必须保证数据是已经被提交了;
  - 可以避免回滚丢失, 脏读;
# 可重复读
  - 当一个事务开始后, 先后读取的数据保持一致, 不受其他事务影响;
  - 可以避免不可重复读和覆盖丢失;
# 串行化
  - 最严格的事务隔离级别, 要求所有事务都串行化执行, 不能并发执行;
  - 避免所有事务异常, 但是牺牲了数据库并发访问的性能;

其他

1. 三大范式

# 第一范式 (1NF): 
  - 要求数据库表的每一字段都是不可分割的原子项;
  (如, 家庭信息(人口数,地址) -> 家庭人口, 家庭地址;)
# 第二范式（2NF）：
  - 满足1NF;
  - 非主键字段必须完全依赖于主键字段;
  - [ 这里的主键字段一般指联合主键, 数据库表中的每一列都必须和联合主键完全相关，而不能只与联合主键的一部分相关]
  (如满减活动的订单主键一般由 '订单号' + '产品号' 组成, 对于 '产品价格' 或 '产品折扣' 都是和主键完全相关的, 
   但 '订单生成时间', '产品产地' 等信息只与主键一部分相关, 应该拆出两个表)
# 第三范式（3NF）
  - 满足2NF;
  - 任何非主[属性]不依赖于其它非主属性（在2NF基础上消除传递依赖）
  - [ 第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。]
  (如, '学号' 和 '班主任工号', '班主任性别', '班主任年龄' 等都是完全依赖的, 但是它们的关系是间接的;
   '班主任性别', '班主任年龄' 直接依赖的是 '班主任工号', 然后才依赖到 '学号', 应该将间接依赖的属性拆分出来)

2. 联接查询

# 左联接 (left join)
  - 返回左表查询字段的所有数据, 以及右表查询字段中满足相等关系的数据;
# 右联接 (right join)
  - 返回右表查询字段的所有数据, 以及左表查询字段中满足相等关系的数据;
# 内联接 (inner join)
  - 只返回两表查询字段中满足相等关系的数据;

3. 外键

# 什么是外键
  - 将另一个表的主键字段关联到本表中的非主键字段, 如; 学生表(主键:学号, 外键:班主任工号) 教师表(主键:工号);
  - 包含外键的表, 被称为'主表'; 被关联的表, 被称为'从表';
  - 级联操作, 当修改某个表时, 另一个表也会改变;
  - 删除时, 必须先删除'从表', 再删除'主表';
# 作用
  - 保持一致性;
  - 减少表容量;
# 最佳实践
  - 生产上, 不建议在工作中使用物理外键, 一切外键概念在应用层解决;
  - 原因: DELETE 和 UPDATE 操作时需要考虑外键约束, 导致开发测试数据不方便; 

4. MVCC 机制

# 多版本并发控制
  - 用于解决不可重复读和一部分幻读;
  - 每个事务事务开始时会对已提交的数据行创建快照;
  - 在事务执行过程中, 事务都对该快照进行读取, 所以并发的事务之间对数据的读取和修改都是隔离的;
  - 每条数据都有创建版本和删除版本, 事务的读取, 更新, 删除和插入都需要根据版本号来修改:
    * 事务读取时, 读取 删除版本 > 当前事务版本, 创建版本 < 当前事务版本 的数据;
    * 事务插入时, 修改 创建版本 = 当前事务版本;
    * 事务删除时, 修改 删除版本 = 当前事务版本
    * 事务更新, 是删除和插入的组合;
# 快照读
  - MVCC 只保证了事务在 select 操作时, 可以并发访问数据, 避免所有问题(包括幻读);
  - 但是, 其他并发操作或非快照读取数据时, 依然有问题(幻读);

SQL 查询排查

1. 获得待优化的SQL语句

# 1. 开启慢查询日志, 设定合适的慢查询阈值
  SET GLOBAL slow_query_log=1;
  SET GLOBAL long_query_time=10;
# 2. 运行数据库, 慢查询被记录到日志文件中
# 3. 使用 mysqldumpslow 慢查询分析工具, 找到查询时间最长/访问次数最多的语句

2. SQL优化方式

# 服务器硬件优化 (磁盘->固态硬盘)
# 服务器系统优化 (windows->Linux)

# SQL本身优化
  - 用连接查询来代替子查询, 可以提高查询效率
    * 子查询将内层查询的结果作为外层查询的输入, 在这过程中, Mysql 需要创建临时表来保存子查询的结果, 并在外层查询结束后销毁临时表, 所以比较耗时;
    * 连接查询是在内存中完成多个表的连接, 并不需要创建临时表, 所以比较高效;

# 反范式化设计优化
  - 数据库的范式化设计
    * 通过拆表的方式, 尽可能地减少了原始数据表中的冗余;
    * 这种方式对数据的增加, 删除, 修改是有益的, 但对查询是没有益处的, 因为这样往往需要联表查询;
  - 反范式化设计
    * 反范式化设计就是允许数据表有适当的冗余, 把冗余字段添加到主表上, 以合并为一个表;
    * 这样用单表查询取代多表查询, 提高了数据读取的效率, 用空间换时间;

# 索引优化
  - 判断是否用到索引
    * 用 explain 分析 SQL 语句
    * 判断是否用到索引 (key非空?)
    * 判断是否完全用到所有索引字段 (计算 key_len==所有索引字段长度?)
  - 策略1: 尽量全值匹配
  - 策略2: 最佳左前缀原则 (查询从索引的最左前列开始并且不跳过索引中的列)
  - 策略3: 不在索引列上做任何操作 (操作会导致索引失效而转向全表扫描)
  - 策略4: 范围条件放最后 (有范围查询会导致后面的索引列全部失效)
  - 策略5: 尽量使用覆盖索引(只访问索引的查询，减少select *)
  - 策略6: 不等于要慎用 (在使用不等于时, 无法使用索引会导致全表扫描)
  - 策略7: 在字段为not null的情况下，使用is null 或 is not null 会导致索引失效
  - 策略8: like+(最前)通配符, 导致索引失效, 变成全表扫描的操作
  - 策略9: 字符串不加单引号索引失效
  - 策略10: OR改union效率高
  
  - 覆盖索引可以避免部分索引失效的问题