数据库基础

#数据库

关系型数据库

关系数据库特点

ER 模型（实体关系模型）和概念模型图

ER 模型，全称为实体关系模型（Entity-Relationship Model），由美籍华裔计算机科学家陈品山先生提出，是概念数据模型的高层描述方式，如下图所示。
image.png|700

实际项目开发中，我们可以利用数据库建模工具（如：PowerDesigner）来绘制概念数据模型（其本质就是 ER 模型），然后再设置好目标数据库系统，将概念模型转换成物理模型，最终生成创建二维表的 SQL（很多工具都可以根据我们设计的物理模型图以及设定的目标数据库来导出 SQL 或直接生成数据表）。

image.png

关系数据库产品

RDBMS 术语

如 MySQL 为关系型数据库 (Relational Database Management System), 这种所谓的 " 关系型 " 可以理解为 " 表格 " 的概念, 一个关系型数据库由一个或数个表格组成, 如图所示的一个表格：
image.png

数据库范式？

范式理论是设计关系型数据库中二维表的指导思想。

  1. 第一范式：数据表的每个列的值域都是由原子值组成的，不能够再分割。
  2. 第二范式：数据表里的所有数据都要和该数据表的键（主键与候选键）有完全依赖关系。
  3. 第三范式：所有非键属性都只和候选键有相关性，也就是说非键属性之间应该是独立无关的。

实际工作中，出于效率的考虑，我们在设计表时很有可能做出反范式设计，即故意降低方式级别，增加冗余数据来获得更好的操作性能。

第一范式

第⼀范式就是属性不可分割，每个字段都应该是不可再拆分的。⽐如⼀个字段是姓名（NAME），在国内的话通常理解都是姓名是⼀个不可再拆分的单位，这时候就符合第⼀范式；但是在国外的话还要分为 FIRST NAME 和 LASTNAME，这时候姓名这个字段就是还可以拆分为更⼩的单位的字段，就不符合第⼀范式了

第二范式（主键约束）

第⼆范式就是要求表中要有主键，表中其他其他字段都依赖于主键，因此第二范式只要记住主键约束就好了。
比如说有⼀个表是学⽣表，学⽣表中有⼀个值是唯⼀的字段学号，那么学⽣表中的其他所有字段都可以根据这个学号字段去获取，依赖主键的意思也就是相关的意思，因为学号的值是唯⼀的，因此就不会造成存储的信息对不上的问题，即学⽣ 001 的姓名不会存到学⽣ 002 那⾥去 。

第三范式（外键约束）

第三范式就是要求表中不能有其他表中存在的、存储相同信息的字段，通常实现是通过外键去建⽴关联，因此第三范式只要记住外键约束就好了。
比如说有⼀个表是学⽣表，学⽣表中有学号，姓名等字段，那如果要把他的系编
号，系主任也存到这个学⽣表中，那就会造成数据⼤量冗余，⼀是这些信息在系信息表中已存在，⼆是系中有 1000 个学⽣的话这些信息就要存 1000 遍。因此第三范式的做法是在学⽣表中增加⼀个系编号的字段做外键，与系信息表做关联。

数据完整性

  1. 实体完整性 - 每个实体都是独一无二的
    • 主键（primary key） / 唯一约束（unique）
  2. 引用完整性（参照完整性）- 关系中不允许引用不存在的实体
    • 外键（foreign key）
  3. 域（domain）完整性 - 数据是有效的

    • 数据类型及长度

    • 非空约束（not null）

    • 默认值约束（default）

    • 检查约束（check）

      说明：在 MySQL 8.x 以前，检查约束并不起作用。

数据一致性（事务）

事务概述

事务是指满足 ACID 特性的一组操作，可以通过 Commit 提交一个事务，也可以使用 Rollback 进行回滚。

一系列对数据库进行读/写的操作，这些操作要么全都成功，要么全都失败。

什么是 ACID？

ACID，指数据库事务正确执行的四个基本要素的缩写。包含：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）。一个支持事务（Transaction）的数据库，必须要具有这四种特性，否则在事务过程（Transaction processing）当中无法保证数据的正确性，交易过程极可能达不到交易方的要求。

  1. A Atomiccity 原子性

事务被视为不可分割的最小单元，事务的所有操作要么全部提交成功，要么全部失败回滚；回滚可以用回滚日志来实现，回滚日志记录着事务所执行的修改操作，在回滚时反向执行这些修改操作即可

  1. C Consistency 一致性

数据库在事务执行前后都保持一致性状态。在一致性状态下，所有事务对同一个数据的读取结果都是相同的。

其主要特征是保护性和不变性 (Preserving an Invariant)，以转账案例为例，假设有五个账户，每个账户余额是 100 元，那么五个账户总额是 500 元，如果在这个 5 个账户之间同时发生多个转账，无论并发多少个，比如在 A 与 B 账户之间转账 5 元，在 C 与 D 账户之间转账 10 元，在 B 与 E 之间转账 15 元，五个账户总额也应该还是 500 元，这就是保护性和不变性。

  1. I Isolation 隔离性

一个事务所做的修改在最终提交之前，对其他事务是不可见的；如果有两个事务，运行在相同的时间内，执行相同的功能，事务的隔离性将确保每一事务在系统中认为只有该事务在使用系统。这种属性有时称为串行化，为了防止事务操作间的混淆，必须串行化或序列化请求，使得在同一时间仅有一个请求用于同一数据。

  1. D Durability 持久性
    • 一旦事务提交，则其所做的修改将会永远保存到数据库中，即使系统发生崩溃，事务执行的结果也不能丢失
    • 系统发生崩溃可以用重做日志进行恢复，从而实现持久性。与回滚日志记录数据的逻辑修改不同，重做日志记录的是数据页的物理修改。

MySQL 中的事务操作

start transaction

commit

rollback

show variables like 'transaction_isolation';

可以看出，MySQL 默认的事务隔离级别是 REPEATABLE-READ。

+-----------------------+-----------------+
| Variable_name | Value |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+

set session transaction isolation level read committed;

关系型数据库的事务是一个很大的话题，因为当存在多个并发事务访问数据时，就有可能出现三类读数据的问题（脏读、不可重复读、幻读）和两类更新数据的问题（第一类丢失更新、第二类丢失更新）。想了解这五类问题的，可以阅读 CSDN 网站上的 《Java面试题全集（上）》 一文的第 80 题。为了避免这些问题，关系型数据库底层是有对应的锁机制的，按锁定对象不同可以分为表级锁和行级锁，按并发事务锁定关系可以分为共享锁和独占锁。然而直接使用锁是非常麻烦的，为此数据库为用户提供了自动锁机制，只要用户指定适当的事务隔离级别，数据库就会通过分析 SQL 语句，然后为事务访问的资源加上合适的锁。此外，数据库还会维护这些锁通过各种手段提高系统的性能，这些对用户来说都是透明的。想了解 MySQL 事务和锁的细节知识，推荐大家阅读进阶读物 《高性能MySQL》，这也是数据库方面的经典书籍。

ANSI/ISO SQL 92 标准定义了 4 个等级的事务隔离级别，如下表所示。需要说明的是，事务隔离级别和数据访问的并发性是对立的，事务隔离级别越高并发性就越差。所以要根据具体的应用来确定到底使用哪种事务隔离级别，这个地方没有万能的原则。

image.png

索引

什么是索引？

索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。索引的实现通常使用 B 树或变种的 B+ 树。

索引的优缺点？

优点

  1. 通过创建索引，可以在查询的过程中，提高系统的性能
  2. 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性
  3. 在使用分组和排序进行数据检索时，可以减少查询中分组和排序的时间

缺点

  1. 创建索引和维护索引要耗费时间，而且时间随着数据量的增加而增大
  2. 索引需要占用物理空间，如果要建立聚簇索引，所需要的空间会更大
  3. 在对表中的数据进行增加删除和修改时需要耗费较多的时间，因为索引也要动态地维护

数据库面试题

Sqlite 相关题

我有一个播放列表，可能有上万首歌，支持插入下一首，要求用数据库存储

如何设计这个模块？(2022 年 大宇无限)

Sqlite3写性能优化-每秒百万条写入