数据库基础知识

1.认识主键、外键、超键和候选键

主键：对数据对象进行唯一的标识，是个数据列或者属性的组合。

外键：在一个表中存在另一个表的主键叫外键。

超键：在全系中可以唯一标识元组的属性或属性集。

候选键：最小的超键，即没有冗余的超键。

元组：

属性：

2.数据库事务的四个特性

事务：

完整性约束：

原子性 Atomicity：整个事务的所有操作，要么全部完成，要么全部不完成，不可以停滞在某个操作中。一旦事务在执行中出现错误，会被回滚到事务开始前的状态（可以联系linux中的快照来理解）。

一致性 Correspondence：在事务开始之前和事务执行之后，数据库的完整性约束没有被破坏。

隔离性 Isolation：如果有两个事务同时执行，执行相同的功能时，隔离性就保证这两个事务是串行执行的，只允许在同一时间内只有一个请求在访问同一数据。

持久性 Durability：在事务完成后，该事务对数据库所做的更改会持久的保存在数据库中，不会被回滚。

3.视图

视图时虚拟的表，与数据表不一样，只包含动态检索数据的查询。

视图可以有和表一样的方式操作它。

视图不能被索引，创建视图的命令：create view XXX as XXXXXX;

4.drop,delete和truncate的区别

drop直接删除表 truncate删除表中数据，再插入时自增张id又从1开始 delete删除表中数据

（1）delete从表中每次删除一行，且作为事务记录在日志中，可以回滚。它是DML（data maintain language），delete操作会被放到rollback segement中，事务提交后才生效，如果有相应的trigger，则执行的时候会被触发。truncate table则一次性从表中删除全部数据，不做日志记录，也不能回滚，在执行删除的操作时不会激活与表有关的删除触发器，执行速度快。它同drop语句都是DLL（data define language），操作会立即生效。

（2）对表和索引的空间有影响。当表被truncate后，这个表和索引占用的空间会恢复到初始大小，而delete操作不会减少表或索引所占的空间，drop操作则将表所占的空间全释放掉。

（3）一般而言，drop > truncate > delete。

（4）应用范围。truncate只能对table进行操作，delete可以操作table和view。

（5）truncate和delete只能删除表中数据，drop可以删除整个表（数据和结构）。

新行标识：

（6）truncate与不带where的delete：只删除表数据，保留表结构，其列、约束、索引等不会改变，新行标识所用的计数值重置为该列的种子（truncate），标识计数值保留（delete）；drop则将二者都删除，并删除依赖该表的约束（constrain）、触发器（trigger）、索引（index），存储过程/函数将被保留，但其状态会变为：invalid。

（7）在没有备份的 情况下，谨慎使用drop和delete语句。要删除部分数据采用delete且注意结合where来约束影响范围。要删除表用drop；若想保留表而将数据删除，如果与事务无关，可用truncate实现；如果与事务有关且想触发trigger，则使用delete。

（8）truncate table 执行速度快切效率高。truncate与不带where的delete功能一样，二者均删除表中所有的数据。但truncate table使用系统和事务日志资源少，它通过释放存储表数据所用到的数据也来删除数据，并只在事务日志中记载数据页的释放，而delete每次删除一行都要在事务日志中为所删除的行记录一项。

（9）对于由外键约束引用的表，不能使用truncate table，而应使用不带where的delete语句。由于truncate table不记录在日志中，不能激活触发器。

5.索引

数据索引，是一种用于排序的数据结构，协助快速查询、更新数据库表中的数据。

除数据之外，数据库还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这些数据结构就是索引。

为表设置索引要付出代价的：一是增加了数据库的存储空间，二是在插入和修改数据时要花费较多的时间（因为索引要随之变动）。

图展示；额一种可能的索引方式。左边是数据库，右边是数据记录的物理地址（注意：逻辑上相邻的记录在磁盘上并不一定是物理相邻的）。

为了加快字段Col2的查找，可以维护一个右边所示的二叉树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以利用二叉树查找O（log2n）的复杂度内获取相应的数据。

创建索引可以大大提高系统的性能。

第一，通过创建唯一索引，看保证数据库表中每一行数据的唯一性，如学号。

第二，可以大大加快数据的检索速度，这也是创建索引的最主要的原因。

第三，可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。

第四，在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。

第五，通过使用索引，可以在查询过程中，使用优化隐藏器，提高系统的性能。

增加索引也会带来不利的一面。

第一，创建索引和维护索引要耗费时间，这种时间随着数据的增加而增加。

第二，除了数据表占数据空间外，索引也要占用物理空间，如果要建立聚簇索引，那么需要的空间就会更大。

第三，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

索引是建立在数据表中的某些列的上面的。在创建索引的时候，应该考虑在哪些列上创建索引，在那些列上不能创建索引。一般来说，应该在这些列上创建索引：

在经常需要搜索的列上可以加快搜索的速度；

在作为主键的列上，强制该列的唯一性和组织表中数据的排序结构；

在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；

在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；

在经常粗腰拍讯的列上创建索引，因为索引已经排序，这样的查询可以利用索引的排序，加快排序查询的速度；

在经常使用在where子句法人列上创建索引，加快条件的判断速度。

同样，对于一些列不应该创建索引，不该创建的索引的这些列具备以下特点：

第一，对于那些在查询中很少使用或者参考的列不应该创建索引。

第二，对于那些只有很少数据值的列也不应该增加索引。

　　   这是因为，由于这些列的取值很少，列入人事表中的性别列，在查询的结构中，结果集的数据航占了表中数据行的很大比例，即需要在表中搜索的数据行的比列很大。增加索引，并不能明显的加快检索速度。

第三，对于那些定义为text,mage和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当地大，要么取值很少。

第四，当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能时互相矛盾的。当增加索引是，会提高检索性能，但会降低修改性能。当减少索引是，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。

根据数据库的功能，可在数据库设计器中创建三种索引：唯一索引、主键索引和聚集索引。

唯一索引：

不允许其中任意两行具有相同索引值的索引。

当现有数据中存在重复的键值时，大多数数据库不允许将新创建的唯一索引与表一起保存。数据库还可能防止添加将在表中创建重复键值的新数据。例如，如果在employee表中职员的姓（name）上创建了唯一索引，则任何两个员工都得不能同姓。

主键索引：

数据库表中经常有一列或列组合，其值唯一表示表中的每一行。该列称为表的主键。在数据库关系图中为表定义主键将自动创建主键索引，主键索引是唯一索引的特定类型。该索引要求主键中的每个值都是唯一的。当在查询中使用主键索引时，它允许对数据的快速访问。

聚集索引：

在聚集索引中，表中行的物理顺序和键值的逻辑顺序是相同的，一个表只能包含一个聚集索引。

如果某索引不是聚集索引，则表中行的物理顺序与键值的逻辑顺序不匹配。与非聚集索引相比，聚集索引通常提供更快的数据访问速度。

6.局部性原理与磁盘预读

由于存储介质的特性，磁盘本身存取就比主存慢很多，再加上机械运动耗费，磁盘的存取速度往往是主存的几百分之一，因此为了提高效率，要尽量减少磁盘I/O。为了达到这个目的，磁盘往往不是严格按需读取，二是每次都会预读，即使只需要一个字节，磁盘也会从这个位置开始，顺序向后读取一定长度的数据放入内存。这样做的理论依据是计算机科学中著名的局部性原理：当一个数据被用到时，其附近的数据通常也会马上被使用。程序运行期间所需的数据通常比较集中。

由于磁盘顺序读取的效率很高（不需要寻道时间，只需很少的旋转时间），因此对于具有局部性的程序来说，预读可以提高I/O效率。

预读的长度一般为页（page）的整倍数。页是计算机管理存储器的逻辑块，硬件及操作系统往往将主存和磁盘存储分割为连续的大小相等的块，每个存储块称为一页（在许多操作系统中，页的大小通常为4K），主存和磁盘一页为单位交换数据。当程序要读取的数据不在内存中时，会触发一个缺页异常，此时系统会向磁盘发出读盘信号，磁盘会找到数据的起始位置并并向后读取一页或者几页载入内存中，当然异常返回，程序继续运行。

7.B-/+Tree索引的性能分析

到这里可以分析B-/+Tree索引的性能了。

上文说过一般使用磁盘I/O次数来评价索引结构的优劣。先从B-/+Tree分析，根据B-Tree的定义，可知检索一次最多需要访问h个结点。数据库系统的设计者巧妙利用了磁盘预读原理，将一个节点的大小设为一页大小，这样每个节点只需要一次I/Oj就可以完全载入。我可达到这个目的，在实际实现B-Tree还需要使用如下技巧：

每次新建节点时，直接申请一个页的空间，这样就保证一个节点物理上也存储在一个页里，加之计算机存储分配都是按页对齐的，就实现了一个node只需一次I/O。

B-Tree中一次检索最多需要h-1次I/O（根节点常驻内存），渐进复杂度为O(h)=O(logdN)。一般实际应用中，出度是非常大的数字，通常超过100，因此h非常小（通常不超过3）。

出度：

而红黑树这种结构，h明显要深的多。由于逻辑上很近的节点（父子）物理上可能很远，无法利用局部性，所以红黑树的I/O渐进复杂度也为O(h)，效率明显比B-Tree差很多。

8.连接的种类

查询分析器中执行：
--建表table1,table2：
create table table1(id int,name varchar(10))
create table table2(id int,score int)
insert into table1 select 1,'lee'
insert into table1 select 2,'zhang'
insert into table1 select 4,'wang'
insert into table2 select 1,90
insert into table2 select 2,100
insert into table2 select 3,70
如表
-------------------------------------------------
table1 | table2 |
-------------------------------------------------
id name |id score |
1 lee |1 90|
2 zhang| 2 100|
4 wang| 3 70|
-------------------------------------------------

外连接：包括左外联接、右外联接和完整外部联接。

左外联接：left join、left outer join

　　1）其结果集包括left outer 子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配的行，则在相关联的结果集中右表的所有列表均为空值（null）。

　　2）sql 语句：
　　　　select * from table1 left join table2 on table1.id=table2.id
　　　　-------------结果-------------
　　　　idnameidscore
　　　　------------------------------
　　　　1lee190
　　　　2zhang2100
　　　　4wangNULLNULL
　　　　-----------------------------
　　　　注释：包含table1的所有子句，根据指定条件返回table2相应的字段，不符合的以null显示

右连接：right join、right outer join

　　1）右外联接是与左外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配的行，则将为左表返回空值（null）。

　　2）sql 语句
　　　　select * from table1 right join table2 on table1.id=table2.id
　　　　-------------结果-------------
　　　　idnameidscore
　　　　------------------------------
　　　　1lee190
　　　　2zhang2100
　　　　NULLNULL370
　　　　------------------------------

　　　　注释：包含table2的所有子句，根据指定条件返回table1相应的字段，不符合的以null显示

完整外部联接：full join、join outer join

　　1）完整外部联接：返回左表和右表所有的行。当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值。

　　2）sql 语句
　　　　select * from table1 full join table2 on table1.id=table2.id
　　　　-------------结果-------------
　　　　idnameidscore
　　　　------------------------------
　　　　1lee190
　　　　2zhang2100
　　　　4wangNULLNULL
　　　　NULLNULL370
　　　　-----------------------------
　　　　注释：返回左右连接的和（见上左、右连接）

内连接（有where语句）：用比较运算符比较要联接列的值的联接。join、inner join

　　1）sql 语句
　　　　select * from table1 join table2 on table1.id=table2.id
　　　　-------------结果-------------
　　　　idnameidscore
　　　　------------------------------
　　　　1lee190
　　　　2zhang2100
　　　　------------------------------
　　　　注释：只返回符合条件的table1和table2的列

　　2）等价

　　　　A.select a.*,b.* from table1 a, table2 b where a.id = b.id

　　　　B.select * from table1 across join table2 where table1.id = table2.id  （注：cross join后加条件只能用where，不能用on）

完全连接：（没有where语句，又叫交叉连接）,cross join

　　1）没有where语句的交叉联接将产生笛卡尔积。第一个表的行数乘以第二个表的行数等于笛卡尔积结果集的大小。

　　2）sql语句
　　　　select * from table1 cross join table2
　　　　-------------结果-------------
　　　　idnameidscore
　　　　------------------------------
　　　　1lee190
　　　　2zhang190
　　　　4wang190
　　　　1lee2100
　　　　2zhang2100
　　　　4wang2100
　　　　1lee370
　　　　2zhang370
　　　　4wang370
　　　　------------------------------
　　　　注释：返回3*3=9条记录，即笛卡尔积

　3）等价（与下列执行效果相同）
　　　　A:select * from table1,table2

 9.数据库范式

第一范式（1NF）

　　在任何关系数据库中，第一范式（1NF）是对关系模式的基本要求，不满足第一范式（1NF）的数据库就不是关系数据库。

　　所谓第一范式（1NF）是指数据库表的每一列都是不可再分割的基本数据项，同一列中不能有多个值，即实体中的某个属性不能有多个值或者不能有重复的属性。如果出现重复的属性，就可能需要定义一个新的实体，新的实体由重复的属性组成，新实体与原实体之间为一对多关系。在第一范式（1NF）中表的每一行只包含一个实例的信息。

　　简而言之，第一范式就是没有重复的列。

第二范式（2NF）

　　建立在第一范式基础之上，即满足第二范式必须先满足第一范式。第二范式要求数据库表中的每个实例（行）必须可以被唯一地区分。为了实现区分通常需要加上一个列，以存储各个实体的唯一标识。这个唯一属性列被称为主关键字或主键、主码。

　　第二范式（2NF）要求实体的属性完全依赖于主关键字。完全依赖是指不能存在仅一部分属性依赖关键字，如果存在，那么这些属性应该和主关键字这一部分分离出来组成出来形成新的实体，新实体与原实体之间是一对多的关系。为了实现区分通常需要为表加上一个列，以存储各个实体的唯一标识。

　　简而言之，第二范式（2NF）即使非主属性非部分依赖主关键字。

第三范式（3NF）

　　满足第三范式（3NF）必须先满足第二范式（2NF）。第三范式要求一个数据库表不包含已在其它表中包含的非主属性关键字。

　　例如，在一个部门信息表中，其中每个部门有部门编号，部门名称，部门简介等，那么在员工表中包含部门编号这一属性就好了，不用再将部门名称、部门简介等与部门有关的信息再加入员工表中了。如果不存在部门表，则根据第三范式应该构建它，否则就会与大量的数据冗余。

　　简而言之，第三范式就是属性不依赖其他非主属性，即消除冗余。

10.数据库优化的思路

SQL语句优化：

　　1）避免在where语句中使用!=或者<>操作符，否则引擎会放弃使用索引而进行全表扫描。

　　2）避免使用在where语句对字段进行是否是null判断，也会单只引擎放弃使用索引而进行全表扫描。

　　　　select id from t where name is null

　　　　可在name上设置默认值为0，确保表中的num列没有null，然后这样查询：

　　　　select id from t where name=0

　　3）很多时候用exists代替in比较好。

　　4）用where子句代替having子句，因为having子句只会在检查所有记录之后才对结果集进行过滤。

10.数据库结构优化

　　1）范式优化：比如消除冗余，（节省空间）。

　　2）反范式优化：比如适当加冗余等（减少join）。

　　3）拆分表：分区将数据在物理上分割开，不同分区的数据可以制定存在处于不同磁盘上的数据文件中。这样，对表进行查询时，指需要在表分区中进行扫描，而不必进行全表扫描，明显缩短了查询时间，另外处于不同磁盘的分区也将对这个表的数据传输分散在不同的磁盘I/O，一个精心设置的分区可以将数据传输对磁盘I/O竞争均匀地分散开。对数据量大的数据表可采取此方法。可按月自动建立表分区。

　　4）拆分又分垂直拆分和水平拆分

　　　　案例：1.产品表（数据量10w，稳定）， 2.订单表（数据量200w，且有增长趋势），3.用户表（数据量100w，且有增长趋势）。以mysql为例讲述垂直拆分和水平拆分。mysql能容忍的数量级在百万级静态数据可以到千万静态数据。

　　垂直拆分

　　解决的问题：表与表之间的io竞争　　不解决的问题：单表中数据量增长出现的压力

　　方案:把产品表和用户表放到一个server上，订单表单独放到一个server上。

　　水平拆分

　　解决的问题：单表中数据增长带来的压力

　　不解决的问题：表与表之间的io竞争

　　方案：用户表通过性别拆分男用户表和女用户表，订单表通过已完成和完成中拆分成：已完成订单表、未完成订单表。产品表和未完成订单表放在一个server上，已完成订单表和男用户表放在一个server上，女用户表放在一个server中（女的爱购物）。

服务器硬件优化

　　多花钱。

11.存储过程和触发器的区别

　　触发器与存储过程很相似，二者都是SQL语句集，它们的唯一区别是触发器不能用execute语句调用，而是在用户执行Transact-SQL语句是自动触发（激活）执行。

　　触发器：是在一个修改了指定表中数据时执行的存储过程。通常通过创建触发器来强制实现不同表中的逻辑相关数据的引用完整性和一致性。由于 用户不能绕过触发器，所以可以用它来强制实施复杂的业务规则，以确保数据的完整性。触发器不同于存储过程，触发器主要是通过事件而执行触发而被执行的，而存储过程可以通过存储过程名称来直接调用。当对某一表进行诸如update、insert、delete操作时，sql server就会自动执行触发器所定义的SQL语句，从而确保对数据的处理必须符合这些SQL语句所定义的规则。