数据库笔记 2021-11-02 09:35 label 数据库

数据库三大范式

1. 第一范式(确保每列保持原子性)

   第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值，就说明该数据库表满足了第一范式。

2. 第二范式(确保表中的每列都和主键相关)

   第二范式在第一范式的基础之上更进一层。第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。

   比如要设计一个订单信息表，因为订单中可能会有多种商品，所以要将订单编号和商品编号作为数据库表的联合主键。这样就产生一个问题：这个表中是以订单编号和商品编号作为联合主键。这样在该表中商品名称、单位、商品价格等信息不与该表的主键相关，而仅仅是与商品编号相关。

   所以在这里违反了第二范式的设计原则。而如果把这个订单信息表进行拆分，把商品信息分离到另一个表中，把订单项目表也分离到另一个表中，就非常完美了。

3. 第三范式(确保每列都和主键列直接相关,而不是间接相关)

   第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。

   比如在设计一个订单数据表的时候，可以将客户编号作为一个外键和订单表建立相应的关系。而不可以在订单表中添加关于客户其它信息（比如姓名、所属公司等）的字段。

数据库事务的四个特性

事务具有四个特性:原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)、持久性(Durability)。这四个特性简称为ACID原则。

1. 原子性

   事务必须是原子工作单元,事务中的操作要么全部执行,要么全都不执行,不能只完成部分操作。原子性在数据库系统中由恢复机制来实现。

2. 一致性

   事务开始之前,数据库处于一致性的状态;事务结束后,数据库必须仍处于一致性状态。数据库一致性的定义是由用户负责的。例如,在银行转账中,用户可以定义转账前后两个账户金额之和保持不变。

3. 隔离性

   系统必须保证事务不受其他并发执行事务的影响,即当多个事务同时运行时,各事务之间相互隔离,不可互相干扰。事务查看数据时数据所处的状态,要么是另一个并发事务修改它之前的状态,要么是另一个并发事务修改它之后的状态,事务不会查看中间状态的数据。隔离性通过系统的并发控制机制实现。

4. 持久性

   一个已完成的事务对数据所做的任何变动在系统中是永久有效的,即使该事务产生的修改不正确,错误也将一直保持。持久性通过恢复机制实现,发生故障时,可以通过日志等手段恢复数据库信息。

数据库事务的作用

当我们需要批量执行sql语句修改多条数据时，为了确保数据的一致性（比如张三给李四转账100元，张三的金额-100，李四金额+100），必须加入事务的使用用以在完整且正确执行完所有sql操作后，再提交对数据的修改。

假设当执行完第一条sql后突然断电引发操作异常，在没有执行commit之前，数据均不会修改。

数据库锁

主要有：表级锁和行级锁

1. 读锁

   可以并发读，但不可以并发写。读锁期间，没释放锁前不能进行写操作。

    lock table [TableName] read;
    unlock tables;

2. 写锁

    lock table [TableName] write;
    unlock tables;

常用数据库引擎的区别

InnoDB存储引擎

InnoDB是事务型数据库的首选引擎，支持事务安全表（ACID），支持行锁定和外键。

主要特性：

1. InnoDB给MySQL提供了具有提交、回滚和崩溃恢复能力的事物安全（ACID兼容）存储引擎。InnoDB锁定在行级并且也在SELECT语句中提供一个类似Oracle的非锁定读。这些功能增加了多用户部署和性能。在SQL查询中，可以自由地将InnoDB类型的表和其他MySQL的表类型混合起来，甚至在同一个查询中也可以混合。

2. InnoDB是为处理巨大数据量的最大性能设计。它的CPU效率可能是任何其他基于磁盘的关系型数据库引擎锁不能匹敌的。

3. InnoDB支持外键完整性约束，存储表中的数据时，每张表的存储都按主键顺序存放，如果没有显示在表定义时指定主键，InnoDB会为每一行生成一个6字节的ROWID，并以此作为主键。

4. InnoDB不创建目录，使用InnoDB时，MySQL将在MySQL数据目录下创建一个名为ibdata1的10MB大小的自动扩展数据文件，以及两个名为ib_logfile0和ib_logfile1的5MB大小的日志文件。

MyISAM存储引擎

MyISAM基于ISAM存储引擎，并对其进行扩展。它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。MyISAM拥有较高的插入、查询速度，但不支持事务。

使用MyISAM引擎创建数据库，将产生3个文件。文件的名字以表名字开始，扩展名之处文件类型：frm文件存储表定义、数据文件的扩展名为.MYD（MYData）、索引文件的扩展名时.MYI（MYIndex）

主要特性：

1. 大文件（达到63位文件长度）在支持大文件的文件系统和操作系统上被支持

2. 当把删除、更新和插入操作混合使用的时候，动态尺寸的行产生更少碎片。这要通过合并相邻被删除的块，以及若下一个块被删除，就扩展到下一块自动完成。

3. 每个MyISAM表最大索引数是64，这可以通过重新编译来改变。每个索引最大的列数是16。

4. BLOB和TEXT列可以被索引。

5. NULL被允许在索引的列中，这个值占每个键的0~1个字节。

6. 每个MyISAM类型的表都有一个AUTO_INCREMENT的内部列，当INSERT和UPDATE操作的时候该列被更新，同时AUTO_INCREMENT列将被刷新。所以说，MyISAM类型表的AUTO_INCREMENT列更新比InnoDB类型的AUTO_INCREMENT更快。

7. 可以把数据文件和索引文件放在不同目录。

8. 每个字符列可以有不同的字符集。

9. 有VARCHAR的表可以固定或动态记录长度。VARCHAR和CHAR列可以多达64KB。

MEMORY存储引擎

MEMORY存储引擎将表中的数据存储到内存中，未查询和引用其他表数据提供快速访问。

主要特性：

1. MEMORY表的每个表可以有多达32个索引，每个索引16列，以及500字节的最大键长度。

2. MEMORY存储引擎执行HASH和BTREE缩影。

3. 可以在一个MEMORY表中有非唯一键值。

4. MEMORY表使用一个固定的记录长度格式。

5. MEMORY不支持BLOB或TEXT列。

6. MEMORY支持AUTO_INCREMENT列和对可包含NULL值的列的索引。

7. MEMORY表在所有客户端之间共享。

8. 当不再需要MEMORY表的内容时，要释放被MEMORY表使用的内存，应该执行DELETE FROM或TRUNCATE TABLE，或者删除整个表（使用DROP TABLE）

Archive存储引擎

如果只有INSERT和SELECT操作，可以选择Archive，Archive支持高并发的插入操作，但是本身不是事务安全的。Archive非常适合存储归档数据，如记录日志信息可以使用Archive。