大学计算机数据库知识点复习.docx
《大学计算机数据库知识点复习.docx》由会员分享，可在线阅读，更多相关《大学计算机数据库知识点复习.docx（22页珍藏版）》请在冰豆网上搜索。
大学计算机数据库知识点复习
1.为性能而设计
1.1现状：
在技术挑战的刺激下，忽视目标，重视手段，忽视数据质量，重视按期交付的功能。
1.2提高性能的方法：
（1）数据结构的设计
对数据表进行规范化，可以保持适当的冗余量，建立有用的索引，尽量减少表与表之间的关联，运用存储过程等。
（2）处理流程
采用异步处理可以允许用户发出操作之后去做其他工作，数据库进行集中的批处理，当处理完毕后通知用户。
采用同步处理时用户必须等待数据库操作并返回结果，期间不能做其他操作，适合数据库进行实时交易。
要根据系统的需求选择一种合适的手段，这回影响物理结构的设计。
（3）数据集中化
分布式数据存储具有更灵活的体系结构，但是有缺点：
远程数据的透明引用访问代价很高。
不同的数据源数据结合极为困难
存取结构复杂，保密性不易控制
因此，将数据集中化会提高数据库熟读，并且在部署上服务离数据越近，速度会越快。
离数据越近，访问速度越快。
代码里DBMS核心越近运行越快，充分利用每次数据库的访问。
Block使用充分，命中率高，所需IO操作少。
（4）保持数据库连接的稳定。
连接稳定减少交互
（5）充分利用每次的数据库访问，减少服务与数据库之间的交互
（6）优化SQL语句
把逻辑放入到查询SQL中，而不是SQL宿主语言？
？
中。
优化SQL语句的结构
（7）谨慎的使用自定义函数
不当的位置会使自定义函数执行次数过多而造成性能下降
（8）构建稳定的应用需要防御是编程，但在合理的情况下可以使用进攻式编程。
如通过leftjoin返回的各种情况做判断（检查哪个字段错误）
限用boolean型字段
约束应明确说明：
数据中存在隐含约束是一种不良设计
1.3提高性能的原则
（1）减少数据访问（减少磁盘访问）
a.创建并使用正确的索引
建立索引通常应该考虑：
索引对于查询的效率提高多少
索引对DML（INSERT,UPDATE,DELETE）附加的开销有多少
索引应该建在哪些列上
哪些情况能用到索引
b.只通过索引访问数据
需要检索的数据全部在索引中
c.优化SQL执行计划
设计适合业务逻辑的良好SQL（表数量，join数量）
d.代码中使用函数始终应该首选DBMS自带函数，代码的执行越接近DBMS核心，则代码执行速度越快
e.慎用自定义函数。
（自定义函数中可能隐藏有查询，阻碍了基于代价的优化器（CBO）对整个查询的优化效果，子查询隐藏在函数中，优化器“鞭长莫及”）
（2）返回更少数据（减少网络传输或磁盘访问）（不需要的数据不要取出来）
a.数据分页处理（限制每次取出数据的数量）
客户端（应用程序或浏览器）分页
应用服务器分页
数据库SQL分页
b.只返回需要的字段
通过去除不必要的返回字段可以提高性能
减少数据在网络上传输开销
减少服务器数据处理开销
减少客户端内存占用
字段变更时提前发现问题，减少程序bug
如果访问的所有字段刚好在一个索引里面，则可以使用纯索引访问提高性能
（3）减少交互次数（减少网络传输）（需要的数据以尽量少的交互次数全部取出）
a.批量处理数据，每次数据库连接应该完成尽可能多的工作，充分利用每次数据库访问
b.使用INList代替多个where，减少SQL查询的次数
c.设置每次返回结果的FetchSize
可以加大fetchsize,这样可以减少结果数据传输的交互次数及服务器数据准备时间，提高性能
d.使用存储过程
大型数据库一般都支持存储过程，合理的利用存储过程也可以提高系统性能。
（各个DBMS内部变成方式有较大差异，存储过程移植性不好，存储过程中会包含一定的业务逻辑，使得系统维护和管理更加困难，所以普通业务逻辑尽量不要使用存储过程）
（4）减少服务器CPU开销（减少CPU及内存开销）
a.使用绑定变量
绑定变量是指SQL中对编号的值采用变量参数的形式提交，而不是在SQL中直接拼写对应的值（java里面的preparedStatement）
b.合理使用排序
你需要注意是否一定要这么做了，大记录集排序不仅增加了CPU开销，而且可能会由于内存不足发生硬盘排序的现象，当发生硬盘排序时性能会急剧下降。
c.减少模糊查找，例如like
d.不要把太多的复杂计算交给数据库
大量复杂运算在数据库里不利于数据库高并发处理
（5）优化数据库物理存储及其环境
a.选择性能优良的数据库服务器
b.数据库物理存储的考虑
客户离数据越近，访问速度越快
数据分散磁盘存储，减小单个磁盘的访问压力，增加数据库对于并发的处理能力
c.数据表的分区
1.4各个DB的比较
1.MySQL
MySQL是最受欢迎的开源SQL数据库管理系统，
MySQL是一个快速的、多线程、多用户和健壮的SQL数据库服务器。
MySQL服务器支持关键任务、重负载生产系统的使用，也可以将它嵌入到一个大配置（mass-deployed）的软件中去。
与其他数据库管理系统相比，MySQL具有以下优势：
（1）MySQL是一个关系数据库管理系统。
（2）MySQL是开源的。
（3）MySQL服务器是一个快速的、可靠的和易于使用的数据库服务器。
（4）MySQL服务器工作在客户/服务器或嵌入系统中。
（5）有大量的MySQL软件可以使用。
2.SQLServer
SQLServer是由微软开发的数据库管理系统，是Web上最流行的用于存储数据的数据库，它已广泛用于电子商务、银行、保险、电力等与数据库有关的行业。
目前最新版本是SQLServer2005，它只能在Windows上运行，操作系统的系统稳定性对数据库十分重要。
并行实施和共存模型并不成熟，很难处理日益增多的用户数和数据卷，伸缩性有限。
SQLServer提供了众多的Web和电子商务功能，如对XML和Internet标准的丰富支持，通过Web对数据进行轻松安全的访问，具有强大的、灵活的、基于Web的和安全的应用程序管理等。
而且，由于其易操作性及其友好的操作界面，深受广大用户的喜爱。
3.Oracle
最新的Oracle8主要增加了对象技术，成为关系—对象数据库系统。
目前，Oracle产品覆盖了大、中、小型机等几十种机型，Oracle数据库成为世界上使用最广泛的关系数据系统之一。
Oracle数据库产品具有以下优良特性。
（1）兼容性
Oracle产品采用标准SQL，并经过美国国家标准技术所（NIST）测试。
与IBMSQL/DS、DB2、INGRES、IDMS/R等兼容。
（2）可移植性
Oracle的产品可运行于很宽范围的硬件与操作系统平台上。
可以安装在70种以上不同的大、中、小型机上;可在VMS、DOS、UNIX、Windows等多种操作系统下工作。
（3）可联结性
Oracle能与多种通讯网络相连，支持各种协议（TCP/IP、DECnet、LU6.2等）。
（4）高生产率
Oracle产品提供了多种开发工具，能极大地方便用户进行进一步的开发。
（5）开放性
Oracle良好的兼容性、可移植性、可连接性和高生产率使OracleRDBMS具有良好的开放性。
4.Sybase
Sybase主要有三种版本：
一是UNIX操作系统下运行的版本;二是NovellNetware环境下运行的版本;三是WindowsNT环境下运行的版本。
对UNIX操作系统，目前应用最广泛的是SYBASE10及SYABSE11forSCOUNIX。
Sybase数据库的特点：
（1）它是基于客户/服务器体系结构的数据库。
（2）它是真正开放的数据库。
（3）它是一种高性能的数据库。
5.DB2
DB2是内嵌于IBM的AS/400系统上的数据库管理系统，直接由硬件支持。
它支持标准的SQL语言，具有与异种数据库相连的GATEWAY。
因此它具有速度快、可靠性好的优点。
但是，只有硬件平台选择了IBM的AS/400，才能选择使用DB2数据库管理系统。
DB2能在所有主流平台上运行（包括Windows），最适于海量数据。
DB2在企业级的应用最为广泛，在全球的500家最大的企业中，几乎85%以上都用DB2数据库服务器，而国内到1997年约占5%。
1.5DB的体系结构
2.SQL优化：
买BMW的优化（仔细看那道BMW的题，有类似的题，估计只会改一点点）
关于DISTINCT，嵌套SQL，EXISTS和IN的差异
（1）注意在做否定意义的查询是小心进入陷阱：
如，没有选修’B2’课程的学说：
selectstudents.*
fromstudents,grades
wherestudents.sno=grades.sno
ANDgradeso<>‘B2’
上面的查询方式是错误的，正确方式见下方：
select*fromstudents
wherenotexists（select*fromgrades
wheregrades.sno=students.snoANDcno=’B2’）
（2）
JOIN：
如果表中有至少一个匹配，则返回行（INNERJOIN）
LEFTJOIN：
即使右表中没有匹配，也从坐标返回所有的行
RIGHTJOIN：
即使坐标中没有匹配，也从右表返回所有的行
FULLJOIN：
只要其中一个表中存在匹配，就返回行
（3）oracle里的rownum
不应该
selectempname,salary
fromemployees
wherestatus!
=‘EXECUTIVE’
andrownum<=5
orderbysalarydesc
而应该
select*
from（selectempname,salary
fromemployees
wherestatus!
=‘EXECUTIVE’
orderbysalarydesc）
whererownum<=5
SQL优化
（1）在关系操作层完成尽量多的工作，对于不完全的关系操作，加倍留意查询的编写。
（2）标准SQL执行顺序
1.select2.from3.where4.groupby5.orderby（41235）
（3）避免在高层distinct，在实现distinct的过程中，同样也是需要分组的，然后再从魅族数据中取出一条返回给客户端
（4）IN先产生中间结果集；EXIST相当于循环进行子查询
使用依据：
如果子查询结果集较大则用EXIST,否则用IN。
原则就是IN是否能过滤掉较多的数据
IN：
先进行内层子查询，再进行外层查询（有一个中间结果集，外层查询的时候使用中间结果集进行查询）。
内外层查询间无关联，内层查询过程不使用外层的变量。
EXIST：
先通过外层查询条件进行过滤，再进行内层子查询，内外层查询之间有关联。
一般内层中会使用到外层的变量。
选择两者的原则：
尽量在查询初期过滤掉更多的数据（IN首先通过子查询条件过滤，EXIST首先通过外层查询条件过滤）。
例子：
BMW
关于DISTINCT,嵌套SQL，EXISTS和IN的差异
优化器借助关系理论提供的语义无误的原始查询进行有效的等价变换，在数据处理真正被执行的时候发生。
优化器的有效范围：
优化器需要借助数据库中找到的信息
能够进行数学意义上的等价变换
优化器考虑整体响应时间
优化器改善的是独立的查询
（如果是若干小查询，会各个优化；如果是一个大查询，会整体优化）
selectdistinctc.custname
fromcustomersc
joinorderso
ono.custid=c.custid
joinorderdetailod
onod.ordid=o.ordid
joinarticlesa
ona.artid=od.artid
wherec.city=‘Nanjing’
anda.artname=‘BMW’
ando.ordered>=somefunc/*函数，返回六个月前的具体日期*/
古老的自然连接方式：
selectdistinctc.custname
fromcustomersc,
orderso,
orderdetailod,
articlesa
wherec.city=‘Nanjing’andc.custid=o.custid
ando.ordid=od.ordid
andod.artid=a.aritd
anda.artname=‘BMW’
ando.ordered>=somefunc
摆脱dixtinct关联子查询：
o.custid需要有索引
selectc.custname
fromcustomersc
wherec.city=‘Nanjing’
andexists（selectnull
fromorderso,
orderdetailod,
articlesa
wherea.artname=‘BMW’
anda.artid=od.artid
andod.ordid=o.ordid
ando.custid=c.custid
ando.ordered>=somfunc）
非关联子查询：
内层查询不在依赖外层查询，只需要执行一次；o.custid不需要有索引
selectcustname
fromcustomers
wherecity=‘Nanjing’
andcustidin（selecto.custid
fromorderso,
orderdetailod,
articlesa
wherea.artname=‘BMW’
anda.artid=od.artid
andod.ordid=o.ordid
ando.ordered>=somefunc）
exits适合内小外大的查询，in适合内大外小的查询
selectcustname
fromcustomc
wherecity=‘Nanjing’andcustidin（selecto.custid
fromorderso
whereordered>=somefunc
orderedin（selectod.artid
fromorderdetailod,articlea
where））
SQL优化：
1）避免在最高层使用distinct
2）针对分辨率最强的条件使用exist或in
3）尽快提出不需要的数据，查询后续阶段必须处理的数据量就越少，查询效率就越高
4）所有影响聚合函数结果的条件都应在having子句中
5）任何无关聚合条件都应该放在where子句中
6）减少groupby必须执行排序操作处理的数据量
selectc.custname,o.ordid,os.status,os.statusdate
fromcustomersc,
orderso,
orderstatusos
whereo.ordid=os.ordid
andnotexists（selectnull
fromorderstatusos2
whereos2.status='COMPLETE'
andos2.ordid=o.ordid）
andos.statusdate=（selectmax（statusdate）
fromorderstatusos3
whereos3.ordid=o.ordid）
ando.custid=c.custid
selectc.custname,o.ordid,os.status,os.statusdate
fromcustomersc,
orderso,
orderstatusos,
（selectordid,max（statusdate）laststatusdate
fromorderstatus
groupbyordid）x
whereo.ordid=os.ordid
andos.statusdate=x.laststatusdate
andos.ordid=x.ordid
andos.status!
='COMPLETE'
ando.custid=c.custid
3.索引
索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。
索引分为聚簇索引和非聚簇索引两种，聚簇索引是按照数据存放的物理位置为顺序的，而非聚簇索引就不一样了；聚簇索引能提高多行检索的速度，而非聚簇索引对于单行的检索很快。
索引目的：
提高查询效率
结构：
B树
什么时候使用B数索引：
仅当要通过索引访问表中很少一部分行，比如处理表中多行，可以使用索引而不用表
索引的几个关键点：
使用索引可能可以提高查询效率，但是只是可能，没有适当设计的索引可能会降低查询速度，有时使用所以带来的维护代价可能高于使用它因为加快查询获得的好处
索引是一种以原子粒度访问数据的手段，而不是为了检索大量数据的
在某些列上使用索引可能会导致其他列的查询变慢
系统地对表的外键加上索引的做法非常普遍，提高并发
为每个外键建立索引，可能会导致多余索引
索引的5种优点：
1.通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性
2.可以大大加快数据的检索速度，这也是创建索引的最主要的原因
3.可以加速表和表之间的连接，特别是在实现数据的参考完整性方面
4.在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间
5.通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。
为什么不每一列建立索引
1.创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加
2.索引需要占物理空间，除了数据表占数据空间之外，每个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。
3.当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度
这些列应该建立索引：
在经常需要搜索的列上，可以加快搜索的速度
在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构
在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度
在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其制定的范围是连续的；外键建索引由于连接加快还会减少死锁几率
在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引排序，加快排序查询时间
在经常使用在WHERE子句中的列上面创建索引，加快调价的判断速度
这些列不应该建立索引：
1.对于那些在查询中很少使用或者参与的列不应该创建索引。
这事因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。
相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。
2.对于那些只有很少数据值的列也不应该增加索引。
这事因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大，增加索引，并不能明显加快检索速度。
3.对于那些定义为text,image和bit数据类型的列不应该增加索引。
这是因为，这些列的数据量要么相当大，要么取值很少，不利于使用索引。
4.当修改性能远远大于检索性能时，不应该创建索引。
这是因为，修改性能和检索性能是互相矛盾的。
当增加索引时，会提高检索性能，但是会降低修改性能。
当减少索引时，会提高修改性能，降低检索性能。
因此，当修改性能远远大于检索性能时，不应该创建索引。
为什么没有使用我的索引？
（ppt）
情况1：
我们在使用B+树索引，而且谓词中没有使用索引的最前列
T，T（X,Y）上有索引，做SELECT*FROMTWHEREY=5
跳跃式索引（仅CBO）
情况2：
使用SELECTCOUNT（*）FROMT，而且T上有索引，但是优化器仍然全表扫描
不带任何条件的count会引起全表扫描。
情况3：
对于一个有索引的列作出函数查询
Select*fromtwheref（indexed_col）=value
情况4：
隐形函数查询
不等于符”<>”会限制索引，引起全表扫描，如果改成or就可以使用索引了。
isnull查询条件也会屏蔽索引。
情况5：
此时如果用了索引，实际反而会更慢
数据量本来不够大
oracle自己计算后认为不用索引更合算
则CBO不会选择用索引
情况6：
没有正确的统计信息，造成CBO无法做出正确的选择
表分析就是收集表和索引的信息，生成的统计信息会存在user_tables这个视图。
CBO根据这些信息决定SQL最佳的执行路径。
总结：
归根到底，不适用索引的通常原因就是不能使用索引，使用索引会返回不正确的结果，或者，不该使用索引，如果使用了索引就会变得更慢。
其他：
1.对于两个公有一字段的表，如果在做外表的表上对该字段建立索引，则该索引不会被使用因为外表的数据访问方式是全表扫描。
2.查询使用了两个条件用or连接，如果条件1中的字段有索引而条件2中字段没有，则仍会全表扫描。
4.物理组织的优化
物理组织的优化（如何访问堆文件，过程是什么），堆文件和IOT，分区的特性
4.1堆文件
文件结构：
堆文件就是一般的表，使用”heap”的结构，数据没有特定的顺序；
读取/访问方式：
获取表中的数据是按命中率来得到的。
没有明确的先后之分，在进行全表扫描时，并不是先插入的数据就先
升级会员 