数据库技术与并发（笔记）

数据库技术与并发

1、数据库基本知识

（1）主键与外键

主键：数据表属性中可以唯一表示行数据的一个属性，比如学生信息表中的学号；

外键：如果两个表的公共关键字在一个表中是主键，那么这个公共关键字被称为另一个表的外键。由此可见，外键表示了两个表之间的关联。以另一个表的外键作主键的表被称为主表，具有此外键的表被称为主表的从表。外键又称作外关键字。

（2）SQL中的join语句与多表查询

作用：JOIN的作用就是通过主键外键关系拼接两个表，从而实现对多表进行联合查询；

举例： （1）普通的联合查询： SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo  from Persons, Orders where Persons.Id_P = Orders.Id_P  ;

（2）JOIN方式的联合查询： 上述用于查询两个表（Persons,Orders）中主键外键相等的行，也可以使用join语句实现，如下：

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo from Persons INNER JOIN Orders ON Persons.Id_P = Orders.Id_P;

JOIN分类：

上述是内连接，除此之外，JOIN还有其他的连接方式：

内连接图示：

FROM A INNER JOIN B on A.Key=B.Key

左外连接图示：

右外链接图示：

全外连接图示：

交叉连接：

语句：cross join；

注意：没有on条件

多表查询需要通过join实现，一般格式都是：

（1）两表联合查询：

FROM A a INNER JOIN B b on a.Key=b.Key WHERE ……；

join后面跟着on，用来确定联合条件（a、b分别是表的别名）；

（2）上述是两表联合，下面介绍三表联合，一般如下：

FROM A a INNER JOIN B b ON a.Key=b.Key INNER JOIN C c ON c.Key=a.Key WHERE ……；

就是将两表联合的结果作为一个表，继续与第三个表联合查询。

（3）having语句

在 SQL 中where和having的区别：

Where 是一个约束声明，使用Where约束来自数据库的数据，Where是在结果返回之前起作用的，Where中不能使用聚合函数；

Having是一个过滤声明，是在查询返回结果集以后对查询结果进行的过滤操作，在Having中可以使用聚合函数；

注：在查询过程中聚合语句(sum、min、max、avg、count)要比having子句优先执行。而where子句在查询过程中执行优先级高于聚合语

句。

HAVING语句后面应该是：组条件表达式（多个通过 AND、OR 或 NOT 组合在一起。）；

总结：where由于不能和聚合函数（sum、min、avg、count）一起使用，故需引入having子句；

having使用举例：

SELECT Customer,SUM(OrderPrice) FROM Orders
WHERE Customer='Bush' OR Customer='Adams'
GROUP BY Customer
HAVING SUM(OrderPrice)>1500

SUM在where执行之后，HAVING执行之前有效；

（4）SQL中的<=>作用

1）和=号的相同点：

像常规的=运算符一样，两个值进行比较，结果是0（不等于）或1（相等）;换句话说：'A'<=>'B'得0和'a'<=>'a‘得1。

2）和=号的不同点：

和=运算符不同的是，NULL可以作为<=>的比较对象，但=号运算符不能把NULL作为比较对象。所以当比较对象是NULL时请使用<=>。

例如：'a' <=> NULL 得0 NULL<=> NULL 得出 1；

或者有一个对象可能为空，也可以使用，如：where Date<=>12；其中Date可能为空，所以要使用<=>。

（5）ER模型和关系模型

ER模型： ER模型即实体联系模型， ER模型的基本元素是：实体、联系和属性 ；

实体：是一个数据对象，指应用中可以区别的客观存在的

事物。（ER模型中的实体往往是指实体集）实体集指同一类实体构成的集合；

关系模型：

关系模型是用二维表的形式表示实体和实体间联系的数据模型；关系模型中，字段称为属性，字段值称为属性值，记录类型称为关系模型；有时也称关系为表格，元组为行，属性为列。

（6）SQL语句中的NULL应用

SQL语句中，若要使用条件为空的判断，需要如下：

where xx is NULL 或 where xx is not NULL；

不可以使用xx=NULL或xx!=NULL的判断，因为NULL是类型不确定的，在SQL语句中比较符号左右都是类型确定且相同的；

（7）SQL语句中的模糊查询

一般模糊查询语句：SELECT 字段 FROM 表 WHERE 某字段 Like 条件；

关于条件，SQL提供了四种匹配模式：

①%：表示任意0个或多个字符。可匹配任意类型和长度的字符；

例如，SELECT * FROM [user] WHERE u_name LIKE '%三%'，将会把u_name为“张三”，“张猫三”、“三脚猫”，“唐三藏”等等有“三”的记录全找出来。

②_ ： 表示任意单个字符。匹配单个任意字符，它常用来限制表达式的字符长度语句：
比如 SELECT * FROM [user] WHERE u_name LIKE '_三_'，只找出“唐三藏”这样u_name为三个字且中间一个字是“三”的；

③[ ] ：表示括号内所列字符中的一个字符（类似正则表达式）。指定一个字符、字符串或范围，要求所匹配对象为它们中的任一个。
比如 SELECT * FROM [user] WHERE u_name LIKE '[张李王]三'
将找出“张三”、“李三”、“王三”（而不是“张李王三”）；

④[^ ] ：表示不在括号所列之内的单个字符。其取值和 [] 相同，但它要求所匹配对象为指定字符以外的任一个字符。
比如 SELECT * FROM [user] WHERE u_name LIKE '[^张李王]三'
将找出不姓“张”、“李”、“王”的“赵三”、“孙三”等；

（9）SQL语句中个关键词执行顺序

一般都是：from->where->group by->having->select->obder by；

2、一般数据库若出现日志满了，会出现什么情况，是否还能使用？

答：只能执行查询等读操作，不能执行更改，备份等写操作，原因是任何写操作都要记录日志，也就是说基本上处于不能使用的状态。

3、在一条SQL语句中同时对两个数据库中的不同表进行操作,有什么好的解决方法吗?

答：解决办法：事务和存储过程。即将操作多个表的操作放入到事务中进行处理。

4、数据库触发器怎么工作的?

答：触发器主要是通过事件进行触发而被执行的，当对某一表进行诸如UPDATE、 INSERT、 DELETE 这些操作时，数据库就会自动执行触发器所定

义的SQL 语句，从而确保对数据的处理必须符合由这些SQL 语句所定义的规则。

5、数据库的主索引、唯一索引、候选索引、普通索引

答：主索引：主索引是指定字段或表达式中不允许出现重复值的索引，其索引表达式值能够唯一标识每个记录处理顺序，一个表只能创建一个主索引;

候选索引：和主索引具有相同特性，要求字段值的惟一性，一个表可建立多个候选索引;

普通索引：不仅允许字段出现重复值，且索引项中也允许出现重复值，一个表可建立多个普通索引;

惟一索引：指索引项的惟一，而非字段值的惟一。指定字段的首次出现值为基础，一个表可建立多个惟一索引.

6、数据库视图

答：视图是虚表，是从一个或几个基本表（或视图）中导出的表，在系统的数据字典中仅存放了视图的定义，不存放视图对应的数据。

视图是原始数据库数据的一种变换，是查看表中数据的另外一种方式。可以将视图看成是一个移动的窗口，通过它可以看到感兴趣的数据。

视图是从一个或多个实际表中获得的，这些表的数据存放在数据库中。那些用于产生视图的表叫做该视图的基表。一个视图也可以从另一个视图中产

生。视图本质上只用于查询，如果用于增删改也是先查表在进行其他操作（因为视图自身没有数据）；

总结：视图是在表的基础上产生的，相当于表上的一个窗口，级别低于表。并且视图基本只用于查询；

7、数据库三级模式

答：三级模式结构：外模式（又叫子模式）、模式、内模式 ；

一、模式（Schema） 

定义：也称逻辑模式，是数据库中全体数据的逻辑结构和特征的描述，是所有用户的公共数据视图。
理解： 
① 一个数据库只有一个模式； 
② 是数据库数据在逻辑级上的视图； 
③ 数据库模式以某一种数据模型为基础； 
④ 定义模式时不仅要定义数据的逻辑结构（如数据记录由哪些数据项构成，数据项的名字、类型、取值范围等），而且要定义与数据有关的安全性、完整性要求，定义这些数据之间的联系。

二、外模式（External Schema） 
定义：也称子模式（Subschema）或用户模式，是数据库用户（包括应用程序员和最终用户）能够看见和使用的局部数据的逻辑结构和特征的描述，是数据库用户的数据视图，是与某一应用有关的数据的逻辑表示。
理解： 
① 一个数据库可以有多个外模式； 
② 外模式就是用户视图； 
③ 外模式是保证数据安全性的一个有力措施。 

三、内模式（Internal Schema） 
定义：也称存储模式（Storage Schema），它是数据物理结构和存储方式的描述，是数据在数据库内部的表示方式（例如，记录的存储方式是顺序存储、按照B树结构存储还是按hash方法存储；索引按照什么方式组织；数据是否压缩存储，是否加密；数据的存储记录结构有何规定）。
理解： 
① 一个数据库只有一个内模式； 
② 一个表可能由多个文件组成，如：数据文件、索引文件。 
它是数据库管理系统(DBMS)对数据库中数据进行有效组织和管理的方法 
其目的有： 
① 为了减少数据冗余，实现数据共享； 
② 为了提高存取效率，改善性能。

8、数据库解决并发操作带来的数据不一致性问题，一般采用的方法是？

答：针对并发控制一般采用 封锁、时间戳、乐观控制法。但是商用的DBMS一般采用的是封锁，使用锁机制，每次只允许一个任务对其操作，操作完成后解锁，才允许下一个任务对其进行操作。

9、数据库4种隔离级别

答：在数据库操作中，为了有效保证并发读取数据的正确性，提出的事务隔离级别。
数据库事务的隔离级别有4个，由低到高依次为Read uncommitted(未授权读取、读未提交)、Read committed（授权读取、读提交）、Repeatable read（可重复读取）、Serializable（序列化），这四个级别可以逐个解决脏读、不可重复读、幻读这几类问题。

之所以提出事务隔离级别，是因为在一个事务执行过程中，可能会出现以下几种情况:
1、更新丢失
两个事务都同时更新一行数据，一个事务对数据的更新把另一个事务对数据的更新覆盖了。这是因为系统没有执行任何的锁操作，因此并发事务并没有被隔离开来。

2、脏读
一个事务读取到了另一个事务未提交的数据操作结果。

3、不可重复读（Non-repeatable Reads）：一个事务对同一行数据重复读取两次，但是却得到了不同的结果。
包括以下情况：
（1） 虚读：事务T1读取某一数据后，事务T2对其做了修改，当事务T1再次读该数据时得到与前一次不同的值。
（2） 幻读（Phantom Reads）：事务在操作过程中进行两次查询，第二次查询的结果包含了第一次查询中未出现的数据或者缺少了第一次查询中出现的数据（这里并不要求两次查询的SQL语句相同）。这是因为在两次查询过程中有另外一个事务插入数据造成的。

下面介绍一下这几种事务隔离级别的区别以及可能出现的问题：
Read uncommitted(未授权读取、读未提交)：
如果一个事务已经开始写数据，则另外一个事务则不允许同时进行写操作，但允许其他事务读此行数据。该隔离级别可以通过“排他写锁”实现。
避免了更新丢失，却可能出现脏读。也就是说事务B读取到了事务A未提交的数据。

Read committed（授权读取、读提交）：
读取数据的事务允许其他事务继续访问该行数据，但是未提交的写事务将会禁止其他事务访问该行。
该隔离级别避免了脏读，但是却可能出现不可重复读。事务A事先读取了数据，事务B紧接了更新了数据，并提交了事务，而事务A再次读取该数据时，数据已经发生了改变。

Repeatable read（可重复读取）：
读取数据的事务将会禁止写事务（但允许读事务），写事务则禁止任何其他事务。
避免了不可重复读取和脏读，但是有时可能出现幻读。这可以通过“共享读锁”和“排他写锁”实现。

Serializable（序列化）：
提供严格的事务隔离。它要求事务序列化执行，事务只能一个接着一个地执行，但不能并发执行。如果仅仅通过“行级锁”是无法实现事务序列化的，必须通过其他机制保证新插入的数据不会被刚执行查询操作的事务访问到。
序列化是最高的事务隔离级别，同时代价也花费最高，性能很低，一般很少使用，在该级别下，事务顺序执行，不仅可以避免脏读、不可重复读，还避免了幻像读。

隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大。对于多数应用程序，可以优先考虑把数据库系统的隔离级别设为Read Committed。它能够避免脏读取，而且具有较好的并发性能。尽管它会导致不可重复读、幻读和第二类丢失更新这些并发问题，在可能出现这类问题的个别场合，可以由应用程序采用悲观锁或乐观锁来控制。

大多数数据库的默认级别就是Read committed，比如Sql Server , Oracle。
MySQL的默认隔离级别就是Repeatable read。

10、悲观锁和乐观锁

答：通俗解释就是：

悲观锁(Pessimistic Lock)，顾名思义，就是很悲观，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会block直到它拿到锁。传统的关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。

乐观锁(Optimistic Lock)， 顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号等机制。乐观锁适用于多读的应用类型，这样可以提高吞吐量，像数据库如果提供类似于write_condition机制的其实都是提供的乐观锁。

两种锁各有优缺点，不可认为一种好于另一种，像乐观锁适用于写比较少的情况下，即冲突真的很少发生的时候，这样可以省去了锁的开销，加大了系

统的整个吞吐量。但如果经常产生冲突，上层应用会不断的进行retry，这样反倒是降低了性能，所以这种情况下用悲观锁就比较合适。

乐观锁实现：为数据增加一个版本标识，一般是通过为数据库表增加一个数字类型的 “version” 字段或者时间戳类型来实现。当读取数据时，将

version字段的值一同读出，数据每更新一次，对此version值加1。当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的

version值进行比对，如果数据库表当前版本号与第一次取出来的version值相等，则予以更新，否则认为是过期数据。用下面的一张图来说明：

悲观锁实现：

需要使用数据库的锁机制，比如SQL SERVER 的TABLOCKX（排它表锁） 此选项被选中时，SQL Server 将在整个表上置排它锁直至该命令或事务结

束，这将防止其他进程读取或修改表中的数据。

11、bernstein条件

答：就是讲两个过程如果有数据冲突(Data hazard),那么就没法并行执行。比如，一个过程指令的运行会修改另一个过程指令的变量参数，那么该两者就无法并行。常见的s1:x=a+b;s2:y=c+x;前者影响后者，无法并行。

12、数据库操作语句类型

答：有4种：DQL、DML、DDL、DCL；

DQL：数据库查询语句，SELECT；

DML：数据库操纵语句，INSERT、DELETE、UPDATE；

DDL：数据库定义语句，CREATE；

DCL：数据库控制语句，数据控制语言DCL用来授予或回收访问数据库的某种特权，并控制数据库操纵事务发生的时间及效果，对数据库实行监视

等。例如： 1）GRANT：授权；REVOKE：收回所授权限    2） ROLLBACK [WORK] TO [SAVEPOINT]：回退到某一点    3) COMMIT [WORK]：提交（提交数据有三种类型：显

式提交、隐式提交及自动提交）。

13、B树索引和 HASH 索引的差异

答；如下：

（1）HASH索引只用于使用 = 或 <=> 操作符的等式比较。如果一定要使用范围查询 的话，只能使用B TREE索引；

（2）优化器不能使用 Hash 索引来加速 order by 操作；

（3）使用 Hash 索引时 MySQL 不能确定在两个值之间大约有多少行。如果将一 个MyISAM表改为的 Hash 索引 memory 表，

会影响一些查询的执行效率；

（4）Hash索引只能使用整个关键字来搜索一行。

14、如何处理几十万条并发数据

答：常见的方法有：多主机分布式处理、缓存技术、多线程

15、数据模型

答：数据模型的组成要素有：
1）数据结构，描述数据库的组成对象以及对象之间的联系，数据结构是所描述的对象类型的集合，是对系统静态特征的描述；
2）数据操作，是指对数据库中各种对象的实例允许执行的操作的集合，主要有查询和更新；
3）数据的完整性约束条件，是一组完整性规则的集合。完整性规则是给定的数据模型中数据及其联系所具有的之约和依存规则，用以限定符合数据模型的数据库状态以及状态的变化，以保证数据的正确、有效、相容。

16、数据库第一、二、三范式（1NF、2NF、3NF）

答：参考：http://blog.csdn.net/famousdt/article/details/6921622

1NF：强调的是列的原子性，即列不能够再分成其他几列；

2NF：非主键列是否完全依赖于主键，还是依赖于主键的一部分；

3NF：非主键列是直接依赖于主键，还是直接依赖于非主键列。

17、数据库系统的核心与基础

答：1）数据库系统的核心和基础，是数据模型,现有的数据库系统均是基于某种数据模型的;
2）数据库系统的核心是数据库管理系统。

18、关系模型的三类完整性约束

答：关系模型有三类完整性约束：实体完整性、参照完整性和用户定义的完整性；

19、数据库触发器与存储过程

答：触发器：

（1）触发器定义：触发器是一种特殊的存储过程，它在插入，删除或修改特定表中的数据时触发执行或激活，它比数据库本身标准的功能有更精细和更复杂的数据控制能力。
注意事项：
1）只有表才可以支持触发器，视图和临时表都不支持触发器；
2）每个表的每个事件只支持一个触发器，因此每个表最多支持6个触发器；
3）单一触发器不能与多个操作相关；
4）触发器不能更新和覆盖，如果想更新一个触发器必须先删除，再创建；

（2）为什么要使用触发器？
每当增加一个顾客到某个数据库表时，都检查其电话号码格式是否正确，州的缩写是否为大写 
每当订购一个产品时，都在库存数量中减去订购数量
无论何时删除一行，都在某个存档表中保留一个副本
共同的特点就是都需要在某个表发生更改时自动处理，触发器是MySQL响应以下任意语句而自动执行的一条MySQL语句
（3）如何使用触发器？
create trigger newproduct after insert on products
for each row select ‘product added’
newproduct：触发器的名字
after:一个操作发生之前或之后执行

存储过程：

存储过程就是为以后的使用而保存的一条或者多条MySQL语句的集合
为什么使用存储过程？
1）通过把处理封装在容易使用的单元，简化复杂的操作
2）简化对变动的管理。如果表名、列名或业务逻辑有变化，只需要更改存储过程的代码。使用它的人员甚至不需要知道这些变化
3）提高性能
总结起来就是简单，安全，高性能
如何使用存储过程？
创建存储过程：
create procedure productpricing()
begin
select avg(prod_price) as priceaverage
from products；
end；
使用存储过程：
call productpricing()

20、SQL语句中尽量避免使用or作为条件连接

答：应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如： 

select id from t where num=10 or num=20;

可以这样查询： 
select id from t where num=10
union all 
select id from t where num=20;

有时候，中间的连接符也可以直接用union，其与union all的区别是：

union会自动压缩多个结果集合中的重复结果，而union all则将所有的结果全部显示出来，不管是不是重复。

两者查找速度：

Union因为要进行重复值扫描，所以效率低。如果合并没有刻意要删除重复行，那么就使用Union All；

21、池化技术——线程池、连接池、内存池

答：（1）池化技术：池化技术简单点来说，就是提前保存大量的资源，以备不时之需。

（2）线程池：

①线程池的原理很简单，类似于操作系统中的缓冲区的概念，它的流程：先启动若干数量的线程，并让这些线程都处于睡眠状态，当客户端有一个新请求时，就会唤醒线程池中的某一个睡眠线程，让它来处理客户端的这个请求，当处理完这个请求后，线程又处于睡眠状态。

②为什么要预先创建若干线程，而不是在需要的时候再创建？

答：因为在数据量很大的条件下，某一时刻可能有大量的（上百个）并发请求，而线程创建的过程是比较耗时的，若此时对每个请求都新创建一个线程，那么会耗费大量的时间，造成拥塞。

（3）连接池：

①常见的数据库oracle、SQL server都有连接池技术，数据库连接池是在数据库启动时建立足够的数据库连接，并将这些连接组成一个连接池（简单说：在一个“池”里放了好多半成品的数据库联接对象），由应用程序动态地对池中的连接进行申请、使用和释放。对于多于连接池数据库连接数的并发请求，则在请求队列中排队等待。并且应用程序可以根据池中连接的使用率，动态增加或减少池中的连接数，这个增加减少由数据库连接池管理线程进行操作。

②为什么要创建连接池？

答：因为需要连接数据库时再创建连接，然后用完就释放的方式会造成很多重复的数据库连接释放操作，且容易因为忘记释放而长期占用链接资源的缺陷。而用数据库连接池负责分配、管理和释放数据库连接，它允许应用程序重复使用一个现有的数据库连接，而不是再重新建立一个；释放空闲时间超过最大空闲时间的数据库连接来避免因为没有释放数据库连接而引起的数据库连接遗漏。这项技术能明显提高对数据库操作的性能。

（4）内存池：

①内存池是一种内存分配方式。通常我们习惯直接使用new、malloc等API申请分配内存，这样做的缺点在于：由于所申请内存块的大小不定，当频繁使用时会造成大量的内存碎片并进而降低性能。

②一个解决方法是内存池：在启动的时候，一个内存池(Memory Pool)分配一块很大的内存，并将会将这个大块分成较小的块。每次你从内存池申请内存空间时，它会从先前已经分配的块中得到，而不是从操作系统。最大的优势在于：
1）非常少(几没有) 堆碎片；
2）比通常的内存申请/释放(比如通过malloc, new等)的方式快。

22、常见的数据备份方式

答：常见的数据备份方式有：完全备份（Full backup）、增量备份（ Incremental backup）、差异备份（Differential backup）。具体如下：