软件开发工程师知识整理（数据库）

1.3 数据库

数据库（DB）：长期保存在计算机的存储设备上，按照一定规则组织起来，可以被用于或应用共享的数据集合。

数据库管理系统（DBMS）：指一种操作和管理数据库的大型软件，用于建立、使用和维护数据库，对数据库进行统一管理和控制，以保证数据库的安全性和完整性。用户通过数据库管理系统访问数据库中的数据。

数据库系统（DBS）：在计算机系统中引入数据库后的系统，通常由计算机硬件、软件、数据库管理系统和数据库管理员组成。

SQL：结构化查询语言

1.3.1 数据库分类

• 关系数据库：MySQL、Oracle、DB2、SQL Sever、PostgreSQL
• 非关系数据库：MongoDB

1.3.2 事物

事务：数据库事务是构成单一逻辑工作单元的操作集合。

• 数据库事务可以包含一个或多个数据库操作，但这些操作构成一个逻辑上的整体。
• 构成逻辑整体的这些数据库操作，要么全部执行成功，要么全部不执行。
• 构成事务的所有操作，要么全都对数据库产生影响，要么全都不产生影响，即不管事务是否执行成功，数据库总能保持一致性状态。
• 以上即使在数据库出现故障以及并发事务存在的情况下依然成立。

事务的ACID特性

• 原子性(Atomicity)：事务中的所有操作作为一个整体像原子一样不可分割，要么全部成功，要么全部失败。
• 一致性(Consistency)：事务的执行结果必须使数据库从一个一致性状态到另一个一致性状态。
  一致性状态是指：
  • 系统的状态满足数据的完整性约束(主码，参照完整性，check约束等)
  • 系统的状态反应数据库本应描述的现实世界的真实状态，比如转账前后两个账户的金额总和应该保持不变。
• 隔离性(Isolation)：并发执行的事务不会相互影响，其对数据库的影响和它们串行执行时一样。比如多个用户同时往一个账户转账，最后账户的结果应该和他们按先后次序转账的结果一样。
• 持久性(Durability)：事务一旦提交，其对数据库的更新就是持久的。任何事务或系统故障都不会导致数据丢失。

数据库事务的4种隔离级别（从低到高）

• 读未提交(READ UNCOMMITTED)：这种隔离级别，其他事务会读到当前事务未提交的数据。
• 读已提交(READ COMMITTED)：这种隔离级别下，其他事务只能读到当前事务已经提交的数据。
• 可重复读(REPEATABLE READ)：这种隔离级别下，其他事务不能进行update操作，但是可以进行add操作。MySQL默认的隔离级别
• 串行化(SERIALIZABLE)：这种隔离级别下事务依次执行（单线程），会造成大量的超时和阻塞等待。

事务具有隔离性，理论上来说事务之间的执行不应该相互产生影响，其对数据库的影响应该和它们串行执行时一样。然而完全的隔离性会导致系统并发性能很低，降低对资源的利用率，因而实际上对隔离性的要求会有所放宽，这也会一定程度造成对数据库一致性要求降低。事务的隔离级别越低，可能出现的并发异常越多，但是通常而言系统能提供的并发能力越强。

对应关系只是理论上的，对于特定的数据库实现不一定准确，比如MySql的Innodb存储引擎通过Next-Key Locking技术在可重复读级别就消除了幻读的可能。

所有事务隔离级别都不允许出现脏写，而串行化可以避免所有可能出现的并发异常，但是会极大的降低系统的并发处理能力。

数据库并发异常

• 脏写：一个客户端覆盖写入了另一个客户端尚未提交的写入。几乎所有的事务实现都可以防止脏写。
• 脏读：A事务对表数据更新，B事务读到了，然后A事务回滚，则B事务读到的数据为‘脏数据‘。
• 不可重复读：A事务读取表中的数据，B事务更改了该表中的数据，并提交，这样A事务再次刷新，得到的数据前后不一致。
• 幻读：A事务正在统计表中数据，B事务增加了几条数据，这样数据突然增加，好像产生了幻觉。
• 更新丢失：A事务读取表中数据，B事务进来更新一条数据结束，A事务再次更新相同行数据。

1.3.3 范式

范式：当设计关系数据库时，需要遵从不同的规范和要求，设计出合理的关系型数据库，这些不同的规范被称为范式，越高的范式数据库冗余越小。一般来说，数据库只需满足第三范式(3NF）就行了。

• 第一范式（1NF）：数据库表的每一列都是不可分割的原子数据项，而不能是集合，数组，记录等非原子数据项。
• 第二范式（2NF）：要求数据库表中的每个实例或记录必须可以被唯一地区分。
• 第三范式（3NF）：要求一个关系中不包含已在其它关系已包含的非主关键字信息。
• 巴斯-科德范式（BCNF）：任何非主属性不能对主键子集依赖。
• 第四范式(4NF）
• 第五范式（5NF，又称完美范式）

范式的优点：

• 消除重复数据
• 减少数据冗余
• 保持数据的一致性

范式的缺点：

• 使查询变得相当复杂

1.1.4 反范式

范式越高，设计的表越可能越多，关系可能越复杂，但性能却不一定越好。

反范式的设计模式中，可以允许适当的数据冗余，用这个冗余可以缩短查询获取数据的时间。

优点：

• 减少数据库的连接次数
• 可以更好的利用索引进行筛选和排序，从而减少I/O数据量，提高查询效率

缺点：

• 数据存在重复冗余，存在部分空间浪费。
• 为了保持数据一致性，必须维护冗余部分，增加了维护的复杂性。

常见的数据库反范式技术：

• 增加冗余列：在多个表中保留相同的列。
• 增加派生列：表中增加由本表或其他表中数据计算生成的列。
• 表水平分割：根据一列或多列的值将数据放到多个独立的表中。
• 表垂直分割：对表进行分割，将主键和一部分列放到一个表中，将主键和其他列放在另一个表中。

1.3.5 索引

索引：索引是对数据库表中一列或多列的值进行排序的一种结构。

为什么数据要用索引？

提升检索速度、加速表与表之间的连接。

索引的种类有哪些？分别的特点是什么？

• 普通索引：加速查询
• 唯一索引：加速查询 + 列值唯一 + 可以为null
• 主键索引：加速查询 + 列值唯一 + 不可为null + 表中只有一个
• 组合索引：多列值组成一个索引，专用于组合搜索，效率大于索引合并
• 全文索引：对文本的内容进行分词，进行搜索

索引的使用原则

• 在大表建立索引才有意义。
• 在WHERE字句或连接条件经常引用的列建立索引。
• 索引的层次不要超过4层。
• 如果某属性经常做最大值或最小值等聚焦函数，那么考虑为该属性建立索引。
• 经常出现在关键字ORDER BY、GROUP BY、DISTINCT后面的字段，最好建立索引。
• 索引应该建立在小字段上，CLOB、TEXT、IMAGE和BIT的数据类型不适合建立索引。
• 表的主键、外键必须建立索引。
• 创建了主键和唯一性约束后会创建唯一索引。
• 多索引列的时候，由左到右索引。
• 删除无用索引。索引有助提高检索性能，但是过多或不当的索引会导致系统低效。

1.3.6 存储过程

存储过程是用户定义的一系列SQL语句的集合，涉及特定表或其他对象的任务，用户可以调用存储过程。

存储过程用于执行特定操作，可以接受输入参数、输出参数、返回单个或多个结果集。

存储过程的优点

• 增强了SQL语句的功能和灵活性。

• 存储过程可以保证数据的安全性。

• 不需要反复建立一系列处理步骤，保证了数据的完整性。

• 极大的改善数据库性能。

• 降低了网络的通信量，客户端调用存储过程只需要传存储过程名和相关参数即可，与传输SQL语 句相比自然数据量少了很多。

• 可以实现集中控制，当规则发生改变时，只需要修改存储过程就可以啦。

存储过程的缺点

• 调试不是很方便
• 可能没有创建存储过程的权利
• 重新编译问题
• 移植性问题

1.3.7 函数

函数通常是数据库已定义的方法，它接收参数并返回某种类型的值并且不涉及特定用户表。

函数和存储过程的区别

• 标识符不同：函数的标识符为FUNCTION，存储过程为PROCEDURE。
• 返回值不同：函数的有且只有一个返回值，存储过程可以有多个返回值。
• 函数可以在SELECT语句中直接使用，而存储过程不行。
• 存储过程无返回值类型，不能将结果赋值给变量。函数有返回值类型，在调用函数时，处理在SELECT语句中，其他情况都必须把函数的返回值赋值给对象。

函数和存储过程都可以有输入参数，都是由一系列SQL语句组成的。

1.3.8 触发器

触发器（TRIGGER）是数据库提供给程序员和DBA 用来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，是用户定义在表上的一类由事件驱动的特殊过程。触发器的执行不是由程序调用，也不是由手工启动，而是由事件来触发的。其中，事件是指用户对表的增（INSERT）、删（DELETE）、改（即更新UPDATE）等操作。触发器经常被用于加强数据的完整性约束和业务规则等。

• 触发器在数据库里以独立的对象存储。
• 触发器是当某个事件发生时自动地隐式运行。
• 触发器被事件触发。运行触发器叫作触发或点火（FIRING），用户不能直接调用触发器。
• 触发器不能接收参数。

触发器和存储过程的区别

• 存储过程是由用户或者应用程序显式调用的。
• 触发器是不能被直接调用的，而是由一个事件来触发运行，即触发器是当某个事件发生时隐式调用的。

触发器的作用

• 可维护数据库的安全性、一致性和完整性。
• 可在写入数据表前，强制检验或转换数据。
• 当触发器发生错误时，异常的结果会被撤销。
• 部分数据库管理系统可以针对数据定义语言（DDL）使用触发器，称为DDL 触发器，还可以针对视图定义替代触发器（INSTEAD OF）。

触发器的优点

• 触发器可通过数据库中的相关表实现级联更改。
• 触发器可以评估数据修改前后的表的状态，并根据其差异采取对策。

触发器的缺点

• 滥用触发器会造成数据库及应用程序的维护困难。

1.3.9 视图

视图是由数据库的基本表中选取处理的数据组成的逻辑窗口，它不同于基本表，它是一个虚拟表，其内容有查询定义。

数据库只存储视图的定义，不存储视图的数据。

只有在使用视图时，才会执行视图的定义，从基本表中查询数据。

视图的作用

• 逻辑上的独立性，屏蔽了真实表的结构带来的影响。
• 安全性，用户只能查询和修改能看到的数据。
• 简化结构，执行复杂的查询操作。
• 使用户能以多角度、更灵活地观察和共享同一数据。

视图的优点

• 使用户将注意力集中在其关心的数据上，而不是全部数据，提供运行效率和用户满意度。
• 视图可以使多表多视图下的复杂查询变得简单可行。
• 视图增加了数据的安全性。

视图的缺点

• 性能差：视图的一个简单查询，数据库也要把它变成一个复杂的结合体，需要花费一定的时间。
• 修改限制：当用户试图修改视图的某些信息时，数据库必须把它转化为对基本表的某些信息的修改，对于比较复杂的试图，可能是不可修改的。

1.3.10 SQL注入

SQL注入：通过把SQL命令插入到Web表单提交或输入域名或页面请求的查询字符串，最终达到欺骗服务器执行恶意SQL命令的目的。

永远不要信任用户的输入，必须认定用户的输入的数据是不安全的，对用户输入的数据都必须进行过滤处理。

注意：

• 永远不要相信用户的输入，对所有输入进行效验。
• 永远不要使用动态拼装SQL，可以使用参数化的SQL或直接使用存储过程进行数据查询和存取。
• 永远不要使用管理员权限连接数据库，应为每个应用赋予单独的权限。
• 对敏感信息进行加密处理。
• 应用的异常信息应该尽可能少的提示，最好使用自定义错误信息对原始错误进行包装。

1.3.11 锁

锁：锁机制用于对共享资源的并发访问，用于多用户环境下，可以保证数据库的完整性和一致性。

• 共享锁（S锁）：如果事务T对数据A加上共享锁后，则其他事务只能对A再加共享锁，不能加排他锁。获准共享锁的事务只能读数据，不能修改数据。
• 排他锁（X锁）：如果事务T对数据A加上排他锁后，则其他事务不能再对A加任任何类型的封锁。获准排他锁的事务既能读数据，又能修改数据。

表级锁

表级别的锁定是MySQL各存储引擎中最大颗粒度的锁定机制。该锁定机制最大的特点是实现逻辑非常简单，带来的系统负面影响最小。所以获取锁和释放锁的速度很快。由于表级锁一次会将整个表锁定，所以可以很好的避免困扰我们的死锁问题。当然，锁定颗粒度大所带来最大的负面影响就是出现锁定资源争用的概率也会最高，致使并大度大打折扣。

行级锁

行级锁定最大的特点就是锁定对象的颗粒度很小，也是目前各大数据库管理软件所实现的锁定颗粒度最小的。由于锁定颗粒度很小，所以发生锁定资源争用的概率也最小，能够给予应用程序尽可能大的并发处理能力而提高一些需要高并发应用系统的整体性能。虽然能够在并发处理能力上面有较大的优势，但是行级锁定也因此带来了不少弊端。由于锁定资源的颗粒度很小，所以每次获取锁和释放锁需要做的事情也更多，带来的消耗自然也就更大了。此外，行级锁定也最容易发生死锁。

页面锁

页级锁定是MySQL中比较独特的一种锁定级别，在其他数据库管理软件中也并不是太常见。页级锁定的特点是锁定颗粒度介于行级锁定与表级锁之间，所以获取锁定所需要的资源开销，以及所能提供的并发处理能力也同样是介于上面二者之间。另外，页级锁定和行级锁定一样，会发生死锁。在数据库实现资源锁定的过程中，随着锁定资源颗粒度的减小，锁定相同数据量的数据所需要消耗的内存数量是越来越多的，实现算法也会越来越复杂。不过，随着锁定资源颗粒度的减小，应用程序的访问请求遇到锁等待的可能性也会随之降低，系统整体并发度也随之提升。

悲观锁

总是假设最坏的情况，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会阻塞直到它拿到锁（共享资源每次只给一个线程使用，其它线程阻塞，用完后再把资源转让给其它线程）。传统的关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。

乐观锁

总是假设最好的情况，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号机制和CAS算法实现。乐观锁适用于多读的应用类型，这样可以提高吞吐量，像数据库提供的类似于write_condition机制，其实都是提供的乐观锁。