数据库基础原理

数据库原理

• 数据库（Database）可以理解是一种特殊格式的数据文件

  • 数据以一种特殊组织形式的二进制格式存储在数据库中

• 数据库管理系统（Database Management System）是对数据库进行管理的一组软件系统

• 结构化查询语言（Struct Query Language，SQL），是一种允许我们在关系型数据库系统上查询和操作数据的语言

• 开发者通过SQL -> 数据库管理系统 -> 操作系统 -> 数据库（文件）

• 关键术语：

  • 表：也称关系表，表是数据库中存储数据的组织形式
  • 字段：即表中的列，也称为属性
  • 记录：即表中的行，一行数据称为一条记录
  • NULL值：null表示不存在的值，或暂时未知的值
    • null不是空字符串
    • null与其他值进行运算，结果总为null
  • 键：
    • 超键：能唯一标识记录的属性集
    • 候选键：属于超键，是不含多余属性的超键，是最小的超键
    • 主键：属于候选键，能唯一标识记录的一个字段或者多个字段的组合
      • 主键是唯一的
      • 主键不能为null
    • 唯一键：一个或多个字段的集合，唯一标识数据库表中的数据
      • 防止列中的复制值
      • 列中只能有一个null
      • 一个表中可以有多个唯一键
    • 外键：建立与其他表的联系，一个表中的某些字段是另外表的主键或唯一键，这些字段称为外键
      • 外键可以为null
      • 外键也可以是本表的原有字段

• 完整性约束

  • 单表约束：
    • not null约束：保证属性值为非null
    • primary key约束：保证属性值必须是唯一的
    • unique约束：保证属性值必须是唯一的
    • check约束：保证属性的取值在一个范围内
    • default约束：保证属性值存在一个默认值
  • 参照性完整性约束（外键约束）：
    • 保证一个表中给定的属性集上的取值也在另一个表的特定属性集的取值中出现

• SQL：

  1. 运算符

  2. 别名

  3. where

     • 条件过滤

  4. like模糊匹配

     • % ：匹配任意多个字符
     • _ ：匹配任意一个字符
     • 转义字符：如果被匹配的字符串中包含%或_ ，可以通过escape关键字来定义转义字符

  5. 集合运算

     • 使用union、intersect、except运算对应数学集合论中的并集、交集、差集运算
     • union对两个结果进行合并运算，自动去重，不包括重复行数据
     • union all对两个结果进行合并运算，包括重复行数据

  6. order by排序

     • asc升序，desc降序，默认是升序

  7. 分页

     • limit x offset y

       从y + 1开始，查x条数据

     • limit x，y

       从x + 1开始，查y条数据

  8. 聚合函数

     • 用于对表中的数据进行统计。
     • 常用的：
       1. count：计数
       2. sum：求和，必须是数值类型（整数、浮点数等）
       3. max，min：找出最大最小值
       4. avg：求平均值，必须是数值类型

  9. group by分组

     对查询结果根据指定的字段进行分组

     使用group by后，select中只能出现分组后的整体信息，不能出现组内的详细信息

     注意：没有出现在group by中的属性如果出现在select子句中，它只能出现在集合函数中，否则查询语法报错

     顺序：

     1. 先where
     2. 再group by
     3. 再select选择返回group by分组的数据

  10. having

      having是对group by分组后的数据进行条件过滤

      如果使用having必须使用group by一起使用

      where和having的区别：

      1. where是对原始记录的过滤，where应用于表中的个别行，符合where语句在进行分组
      2. having用在分组后的记录过滤，并且having子句中的查询条件只能包含在group by子句中的列或集合函数。

  11. 多表关联查询

      在关系型数据库中，各个表之间可能存在一定的关联关系（外键），就需要联合多张表进行查询我们需要的数据

  12. 笛卡尔积

      在数据库中，对于两张表A、B，它们的笛卡尔积是：行数是AB的乘积，列数是AB的和

  13. JOIN

      join 一张表 on 条件

      where和on的区别：

      1. on用于join连接的时候，根据两张表的连接条件进行过滤
      2. where用在最终结果记录的条件过滤

  14. 自然连接

      natural join

      企业开发基本不使用

  15. 自连接

      一张表自己和自己关联起来查询数据

  16. 内连接

      使用inner join 进行内连接，inner可以省略，内连接就是笛卡尔积的常规连接

  17. 外连接

      外连接分为：

      1. 左连接：

         只保留左表的记录，右表中的记录匹配不上则为null

      2. 右连接：

         与左连接对称

      3. 全连接：

         右表中的记录匹配不上则为null,左表中的记录匹配不上则为null

         MYSQL中可以通过左连接 + union + 右连接 实现全连接

  18. 子查询

      又称嵌套子查询，在一个查询中嵌套另一个查询

• 视图：

  • 视图就是把查询存放成数据库的一个对象，即视图对象
  • 视图是一个虚表，不存储具体的数据，因此一旦基本表中的数据发生变化，从视图查询出的数据也随之改变了
  • 视图是采用select语句来构造的，在当一个视图的时候，只是把其定义存放在系统的数据字典中，直到用户使用视图时才去求对应的数据
  • 视图只保存了查询的SQL逻辑，不保存查询结果

• 触发器：

  • 触发器是存储在数据库中的一组SQL语句，每当与表相关联的事件发生时，就会触发SQL执行，例如：增，删，改
  • 触发器是与表相关的命名数据库对象，每当表上出现特定事件时，将激活该对象
  • 触发器由数据库系统自动执行，无需程序员手动调用
  • 优点：
    1. 帮助记录操作日志‘
    2. 对操作前进行数据合法性检验
  • 缺点：
    1. 可读性差
    2. 增加性能开销

• 存储过程：

  • 存储过程就是作为可执行对象存放在数据库中的一个或多个SQL命令。并在数据库内执行
  • 是一组为了完成特定功能的SQL集合
  • 经编译以后保存在数据库中
  • 可带参数，也可返回结果

• 数据库设计：

  • ER模型：

    • 实体（Entity），关系（Relationship）
    • 实体：将现实世界事务特征抽象为数据库表，一个表就是一个实体
    • 数据库中实体之间的关系通过外键来关联

  • 设计范式：

    • 第一范式：

      表中的字段是原子的，不可分割的

    • 第二范式：

      表中要有主键

    • 第三范式：

      消除部份依赖和传递依赖

• 索引：

  • 索引概念：

    通过数据库索引，可以快速的检索到记录在磁盘中的具体位置

    当表中的数据非常多时，可以使用索引来提高查询数据的效率

    数据库索引的最主要作用是为了提高查询性能

    概念：

    1. 索引是对数据表中一列或多列的值进行排序的一种结构，可提高待定数据的查询速度
    2. 索引是一个单独的、存储在磁盘上的数控结构文件，包含着数据表中所有记录的引用指针

    索引作用：

    1. 索引中的属性值必须有序，则可以通过二分查找的方式快速定位到数据文件的磁盘地址
    2. 如果没有索引，查询将会全表扫描，导致性能急速下降

  • 索引物理结构划分：

    索引可分为：

    1. 顺序索引
    2. B树索引
    3. 哈希索引

    商用数据库的索引主要是后两者

  • 顺序索引：

    顺序索引按顺序存储搜索码的值，并且每个搜索码与包含该搜索码的记录关联起来

  • B树索引：

    访问一个节点就要跟磁盘之间发生一次I/O，而IO操作通常很耗时

    InnoDB操作磁盘的最小单位是页（page）（或者叫一个磁盘块），大小默认是16KB。那么一个树节点就是16KB

    让每个节点存储更多的数据，不仅能提升页的空间利用率，还会极大地降低树的深度，也是为了减少IO次数，减少访问时间。此时，我们的树就不再是二叉，而是多叉，也称多路

    B树（Balanced Tree），即多路平衡查找树。节点存储键值，数据地址和节点引用

    在索引中插入或删除数据时，节点可能会发生分裂和合并

    节点的分裂和合并，其实就是InnoDB页的分裂和合并

    分裂和合并往往需要数据的迁移，频繁操作会导致性能下降

    B树索引特点：

    1. 分叉数（路数）永远比一个节点中存储的关键字多一
    2. 数据存储在每一个节点上

  • B+树索引：

    B+树索引的特点是：

    1. 关键字的数量和路数相等的
    2. 只有叶子节点才存储实际的数据，非叶子节点不存储数据
    3. 叶子节点通过双向链表进行连接，是一个有序链表

    B+树带来的优势：

    1. 它是B树的变种，有和B树一样的优势：每个节点存储更多关键字；路数更多，树的深度更小，IO更少；
    2. 扫库、扫表能力更强（如果对全表扫描，只需要遍历叶子节点就可以了，不需要遍历整棵B+树就能拿到所有数据）
    3. B+树的磁盘读写能力更强（根节点和枝节点不保存数据，所以一个节点可以保存更多的关键字，一次IO加载的关键字更多）
    4. 排序能力更强（叶子节点形成了双向链表）
    5. 效率更稳定（永远都是在叶子节点拿取数据，IO次数是固定的）、
    6. 索引文件很大，索引查找过程就要产生IO消耗
    7. B+树具有更少的检索次数。即磁盘IO次数更少，带来都性能损耗更小，查询效率更高
    8. 叶子节点形成了双向链表，利于范围查询

    每张InnoDB的表有两个文件：

    1. .frm文件：存储表结构
    2. .ibd文件：以主键为索引来组织数据的存储，所以索引文件和数据文件是在同一个.ibd文件

    InnoDB的聚集索引就是索引键值的逻辑顺序跟表数据行的物理存储顺序是一致的

    InnoDB组织数据的方式就是聚集索引组织表。如果一张表创建了主键索引，那么这个主键索引就是聚集索引，决定数据行的物理存储顺序

    注意：

    1. 如果定义了主键，InnoDB会选择主键作为聚集索引
    2. 如果没有显示定义主键，则会选择第一个不包含NULL的唯一索引作为主键索引
    3. 如果都没有，InnoDB会选择内置6字节长的ROWID作为隐藏的聚集索引

    在InnoDB的聚集索引中，辅助索引的叶子节点存储的是搜索码和主键id

    回表：

    ​ 针对非主键索引的

    ​ 先通过非主键索引来找主键id再去查主键索引，相当于找了两棵树，称为回表

    联合索引：

    ​ 也叫组合索引，多个字段建立的索引，搜索码为多个字段。

    ​ 联合索引是复合的数据结构，按照从左到右建立搜索树

    覆盖索引：

    ​ 如果select的数据列只用从索引中就能够获取，就不必再去数据区读取，这时候的索引就叫做覆盖索引，避免了回表

    ​ 覆盖索引可以提高查询性能

    索引层次：

    ​ 层次一般不会超过三层

    ​ 根据每页空间为16KB和树的层级推算：单表存储的量级为2000~5000万，超过则导致索引层级增加，查询性能降低

    索引失效：

    1. 使用全表扫描比索引查询更快，就不会考虑索引（查询优化器会选择）
    2. 使用来联合索引时，违背最左前缀原则就不会使用该索引，
    3. 在索引列上使用计算、函数、类型转换等操作，就不会使用索引
    4. LIKE以通配符%开头，也不会使用索引
    5. 字符串不加单引号，也不会使用索引，因为会进行类型转换
    6. 条件中有OR，需要满足OR连接的任意一个条件列都使用索引
    7. 不等于（！= ，<，>）不会使用索引
    8. is not null 不会使用索引

  • 哈希索引：

    以K - V的形式检索数据，也就是说，它会根据索引字段生成哈希码和指针，指针指向数据

    特点：

    1. 查询速度快
    2. 不支持范围查询
    3. 容易发生哈希冲突

  • 创建索引&删除索引：

    create drop

  • 索引的优缺点：

    优点：

    1. 创建唯一索引，保证每行数据的唯一性
    2. 加快了查询速度
    3. 实现数据的参考完整性，加速表与表之间的连接
    4. 减少分组查询中分组和排序的时间

    缺点：

    1. 创建和维护索引需要时间
    2. 索引占据空间

• 事务：

  简单理解：一组SQL在事务中执行，要么全部成功，要么全部失败

  事务是一个逻辑工作单元，有四个特性

  ACID特性：

  1. 原子性（Atomicity）：

     事务是一个原子操作，不可分割，要么全部成功，要么全部失败，部分失败会回滚

  2. 一致性（Consistency）：

     事物的执行必须使数据库从一个一致状态转换到另一个状态。这意味着事务必须满足所有约束条件，以保持数据的完整性和一致性

  3. 隔离性（Isolation）：并发事务的执行不能相互干扰

  4. 持久性（Durability）：一旦事务完成提交，其结果就是永久性的

  事务隔离级别：

  1. 读未提交（Read Uncommitted，RU）：

     所有事务可以看到其他未提交事务的执行结果。

     该隔离级别很少用于实际应用，性能一般，还可能出现脏读

     脏读：读到未提交的数据

  2. 读已提交（Read Committed，RC）：

     所有事务只能看到已经提交事务的执行结果。

     但可能导致不可重复读：事务A多次读，事务B在A读的过程中做了改变，然后A读的数据不一致

  3. 可重复读（Repeatable Read，RR）：

     MYSQL默认隔离级别

     可以通过共享读锁和排他写锁来实现该隔离级别

     读写数据的事务不可同时进行

     避免了不可重复读和脏读，但可能出现幻读

     幻读：是指在一个事务内，两次执行相同的查询，由于其他并发事务插入或删除了的行，可能会得到不同的结果集

     幻读和脏读的区别：

     1. 幻读侧重于数据范围
     2. 脏读侧重的是事务回滚导致的脏数据

  4. 可串行化（Serializable）：

     事务只能一个一个运行

     性能很低，避免了脏读、幻读、不可重复读

  级别越高，并发性越差，安全性全好：

  ​ 隔离级别：序列化 > 可重复读 > 提交读 > 未提交读

  自动事务：

  ​ 每条SQL就是一个事务，完成时自动提交

  显示事务：

  ​ START TRANSACTION

  ​ COMMIT/ROLL BACK

  隐式事务：

  ​ SQL Server发出某个SQL命令时，自动启动事务

• 锁：

  数据库支持多种锁类型

  1. 按锁类型分：共享锁、排它锁、意向锁
     • 共享锁（读锁，S锁）：可以同时读，但不能在读的时候修改
     • 排他锁（写锁，X锁）：只允许一个事务去修改数据，同时不允许其他事务对这个数据进行任何操作
     • 意向锁（I锁）：是表级锁的一种，由数据库引擎自行维护的，用户自己无法操作意向锁
       • 意向锁将检查行锁的时间复杂度由O（n）变成O（1），加锁的具体做法就是当一个事务想要获取表中某一行的（共享/排他）锁时，它会自动尝试给当前表加上意向（共享/排他）锁，用于防止全表扫描的一种锁
       • 意向共享锁（IS锁-表级锁）：事务想给一个数据行加行级共享锁之前必须先获取该表的IS锁。
       • 意向排他锁（IX锁-表级锁）：事务想给一个数据行加行级排他锁之前必须先获取该表的IX锁。
       • 意向锁之间时互相兼容的
       • 意向锁和其他锁兼容互斥性：意向锁和普通的排他/共享锁（表锁）互斥，意向锁不与行级的排他/共享锁互斥
  2. 按锁粒度分：全局锁、表级锁、页级锁、行级锁
     • 全局锁就是对整个数据库实例加锁，主要被备份工具使用
     • 表级锁：控制对整张表的并发访问
     • 页级锁：每次锁定相邻的一组记录
     • 行级锁：锁定表中的一行记录
       1. RecordLock（记录锁）：锁定单个行记录
          • 记录锁就是行级别的S、X锁
       2. GapLock（间隙锁）：锁定索引记录间隙，确保索引记录的间隙不变
          • 解决幻读有两种方式：MVCC和间隙锁
       3. Next-key Lock（临键锁，记录锁+间隙锁）：锁定数据前后范围，并且锁定记录本身
          • 在RR级别，InnoDB对于行采用的是Next-Key Lock，在查询的列是唯一索引时，降级为记录锁
  3. 按锁性能分：乐观锁、悲观锁