数据库优化面试.docx

数据库优化面试.docx

《数据库优化面试.docx》由会员分享，可在线阅读，更多相关《数据库优化面试.docx（11页珍藏版）》请在冰豆网上搜索。

数据库优化面试

1.主键超键候选键外键

主键：

数据库表中对储存数据对象予以唯一和完整标识的数据列或属性的组合。

一个数据列只能有一个主键，且主键的取值不能缺失，即不能为空值（Null）。

超键：

在关系中能唯一标识元组的属性集称为关系模式的超键。

一个属性可以为作为一个超键，多个属性组合在一起也可以作为一个超键。

超键包含候选键和主键。

候选键：

是最小超键，即没有冗余元素的超键。

外键：

在一个表中存在的另一个表的主键称此表的外键。

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

数据库事务transanction正确执行的四个基本要素。

ACID,原子性（Atomicity）、一致性（Correspondence）、隔离性（Isolation）、持久性（Durability）。

原子性:

整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。

事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

一致性:

在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。

隔离性:

隔离状态执行事务，使它们好像是系统在给定时间内执行的唯一操作。

如果有两个事务，运行在相同的时间内，执行相同的功能，事务的隔离性将确保每一事务在系统中认为只有该事务在使用系统。

这种属性有时称为串行化，为了防止事务操作间的混淆，必须串行化或序列化请求，使得在同一时间仅有一个请求用于同一数据。

持久性:

在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。

3.视图的作用，视图可以更改么？

视图是虚拟的表，与包含数据的表不一样，视图只包含使用时动态检索数据的查询；不包含任何列或数据。

使用视图可以简化复杂的sql操作，隐藏具体的细节，保护数据；视图创建后，可以使用与表相同的方式利用它们。

视图不能被索引，也不能有关联的触发器或默认值，如果视图本身内有orderby则对视图再次orderby将被覆盖。

创建视图：

createviewXXXasXXXXXXXXXXXXXX;

对于某些视图比如未使用联结子查询分组聚集函数DistinctUnion等，是可以对其更新的，对视图的更新将对基表进行更新；但是视图主要用于简化检索，保护数据，并不用于更新，而且大部分视图都不可以更新。

4.drop,delete与truncate的区别

drop直接删掉表truncate删除表中数据，再插入时自增长id又从1开始delete删除表中数据，可以加where字句。

（1）DELETE语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作。

TRUNCATETABLE则一次性地从表中删除所有的数据并不把单独的删除操作记录记入日志保存，删除行是不能恢复的。

并且在删除的过程中不会激活与表有关的删除触发器。

执行速度快。

（2）表和索引所占空间。

当表被TRUNCATE后，这个表和索引所占用的空间会恢复到初始大小，而DELETE操作不会减少表或索引所占用的空间。

drop语句将表所占用的空间全释放掉。

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

（4）应用范围。

TRUNCATE只能对TABLE；DELETE可以是table和view

（5）TRUNCATE和DELETE只删除数据，而DROP则删除整个表（结构和数据）。

（6）truncate与不带where的delete：

只删除数据，而不删除表的结构（定义）drop语句将删除表的结构被依赖的约束（constrain）,触发器（trigger）索引（index）;依赖于该表的存储过程/函数将被保留，但其状态会变为：

invalid。

（7）delete语句为DML（datamaintainLanguage）,这个操作会被放到rollbacksegment中,事务提交后才生效。

如果有相应的tigger,执行的时候将被触发。

（8）truncate、drop是DLL（datadefinelanguage）,操作立即生效，原数据不放到rollbacksegment中，不能回滚

（9）在没有备份情况下，谨慎使用drop与truncate。

要删除部分数据行采用delete且注意结合where来约束影响范围。

回滚段要足够大。

要删除表用drop;若想保留表而将表中数据删除，如果于事务无关，用truncate即可实现。

如果和事务有关，或老师想触发trigger,还是用delete。

（10）Truncatetable表名速度快,而且效率高,因为:

truncatetable在功能上与不带WHERE子句的DELETE语句相同：

二者均删除表中的全部行。

但TRUNCATETABLE比DELETE速度快，且使用的系统和事务日志资源少。

DELETE语句每次删除一行，并在事务日志中为所删除的每行记录一项。

TRUNCATETABLE通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。

（11）TRUNCATETABLE删除表中的所有行，但表结构及其列、约束、索引等保持不变。

新行标识所用的计数值重置为该列的种子。

如果想保留标识计数值，请改用DELETE。

如果要删除表定义及其数据，请使用DROPTABLE语句。

（12）对于由FOREIGNKEY约束引用的表，不能使用TRUNCATETABLE，而应使用不带WHERE子句的DELETE语句。

由于TRUNCATETABLE不记录在日志中，所以它不能激活触发器。

5.索引的工作原理及其种类

数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。

索引的实现通常使用B树及其变种B+树。

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

这种数据结构，就是索引。

为表设置索引要付出代价的：

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

上图展示了一种可能的索引方式。

左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址（注意逻辑上相邻的记录在磁盘上也并不是一定物理相邻的）。

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

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

第一，通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

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

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

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

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

也许会有人要问：

增加索引有如此多的优点，为什么不对表中的每一个列创建一个索引呢？

因为，增加索引也有许多不利的方面。

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

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

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

索引是建立在数据库表中的某些列的上面。

在创建索引的时候，应该考虑在哪些列上可以创建索引，在哪些列上不能创建索引。

一般来说，应该在这些列上创建索引：

在经常需要搜索的列上，可以加快搜索的速度；在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。

同样，对于有些列不应该创建索引。

一般来说，不应该创建索引的的这些列具有下列特点：

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

这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。

相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。

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

这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。

增加索引，并不能明显加快检索速度。

第三，对于那些定义为text,image和bit数据类型的列不应该增加索引。

这是因为，这些列的数据量要么相当大，要么取值很少。

第四，当修改性能远远大于检索性能时，不应该创建索引。

这是因为，修改性能和检索性能是互相矛盾的。

当增加索引时，会提高检索性能，但是会降低修改性能。

当减少索引时，会提高修改性能，降低检索性能。

因此，当修改性能远远大于检索性能时，不应该创建索引。

根据数据库的功能，可以在数据库设计器中创建三种索引：

唯一索引、主键索引和聚集索引。

唯一索引

唯一索引是不允许其中任何两行具有相同索引值的索引。

当现有数据中存在重复的键值时，大多数数据库不允许将新创建的唯一索引与表一起保存。

数据库还可能防止添加将在表中创建重复键值的新数据。

例如，如果在employee表中职员的姓（lname）上创建了唯一索引，则任何两个员工都不能同姓。

 主键索引 数据库表经常有一列或列组合，其值唯一标识表中的每一行。

该列称为表的主键。

在数据库关系图中为表定义主键将自动创建主键索引，主键索引是唯一索引的特定类型。

该索引要求主键中的每个值都唯一。

当在查询中使用主键索引时，它还允许对数据的快速访问。

 聚集索引 在聚集索引中，表中行的物理顺序与键值的逻辑（索引）顺序相同。

一个表只能包含一个聚集索引。

如果某索引不是聚集索引，则表中行的物理顺序与键值的逻辑顺序不匹配。

与非聚集索引相比，聚集索引通常提供更快的数据访问速度。

局部性原理与磁盘预读

由于存储介质的特性，磁盘本身存取就比主存慢很多，再加上机械运动耗费，磁盘的存取速度往往是主存的几百分分之一，因此为了提高效率，要尽量减少磁盘I/O。

为了达到这个目的，磁盘往往不是严格按需读取，而是每次都会预读，即使只需要一个字节，磁盘也会从这个位置开始，顺序向后读取一定长度的数据放入内存。

这样做的理论依据是计算机科学中著名的局部性原理：

当一个数据被用到时，其附近的数据也通常会马上被使用。

程序运行期间所需要的数据通常比较集中。

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

预读的长度一般为页（page）的整倍数。

页是计算机管理存储器的逻辑块，硬件及操作系统往往将主存和磁盘存储区分割为连续的大小相等的块，每个存储块称为一页（在许多操作系统中，页得大小通常为4k），主存和磁盘以页为单位交换数据。

当程序要读取的数据不在主存中时，会触发一个缺页异常，此时系统会向磁盘发出读盘信号，磁盘会找到数据的起始位置并向后连续读取一页或几页载入内存中，然后异常返回，程序继续运行。

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

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

上文说过一般使用磁盘I/O次数评价索引结构的优劣。

先从B-Tree分析，根据B-Tree的定义，可知检索一次最多需要访问h个节点。

数