数据库实验心得

1、数据库实验心得 我在 sqlserver 索引基础知识系列中 ,第一篇就讲了记录数据的基本 格式。 那里主要讲解的是，数据库的最小读存单元：数据页。 一个数据页是 8k 大小。 对于数据库来说， 它不会每次有一个数据页变化后， 就存到硬盘。 而是变化达到一定数量级后才会作这个操作。 这时候，数据库并不是以数据页来作为操作单元，而是以 64k 的 数据（8 个数据页，一个区）作为操作单元。 区是管理空间的基本单位。 一个区是八个物理上连续的页（即 64kb）。 这意味着sqlserver数据库中每mb有16个区。 为了使空间分配更有效， sqlserver 不会将所有区分配给包含少量 数据的表。

2、 sqlserver有两种类型的区：统一区，由单个对象所有。 区中的所有 8页只能由所属对象使用。 混合区，最多可由八个对象共享。 区中八页的每页可由不同的对象所有。 通常从混合区向新表或索引分配页。 当表或索引增长到 8 页时，将变成使用统一区进行后续分配。 如果对现有表创建索引，并且该表包含的行足以在索引中生成 页，则对该索引的所有分配都使用统一区进行 为何会这样呢？其实很简单：读或写 8kb 的时间与读或写 64kb 的 时间几乎相同。 在 8kb 到 64kb 范围之内，单个磁盘 i/o 传输操作所花的时间主要 是磁盘取数臂和读 / 写磁头运动的时间。 因此，从数学上来讲，当需要传输

3、64kb 以上的 sql 数据时，尽可 能地执行 64kb 磁盘传输是有益的，即分成数个 64k 的操作。 因为 64kb 传输基本上与 8kb 传输一样快，而每次传输的 sqlserver 数据是8kb传输的8倍。 我们通过一个实例来看有 and 操作符时候的最常见的一种情况。 我们有下面一个表， createtabledbo.member(member_nodbo.numeric_ididentity(1,1)n otnull,lastnamedbo.shortstringnotnull,firstnamedbo.shortstringn otnull,middleinitialdbo.l

4、etternull,streetdbo.shortstringnotnull,cit ydbo.shortstringnotnull,state_provdbo.statecodenotnull,country dbo.countrycodenotnull,mail_codedbo.mailcodenotnull,phone_n odbo.phonenumbernull,photographimagenull,issue_dtdatetime notnulldefault(getdate(),expr_dtdatetimenotnulldefault(dateadd(year, 1,getda

5、te(),region_nodbo.numeric_idnotnull,corp_nodbo.numer ic_idnull,prev_balancemoneynulldefault(0),curr_balancemoneynull default(0),member_codedbo.status_codenotnulldefault() 这 个表 具 备下面的四个索引：索引名细节索引的列 member_corporation_linknonclusteredlocatedonprimarycorp_nomember _identclustered,unique,primarykeylocat

6、edonprimarymember_nomember _region_linknonclusteredlocatedonprimaryregion_nomemberfirstnameno nclusteredlocatedonprimaryfirstname 当我们执行下面的 sql 查询时候， selectm.member_no,m.firstname,m.region_nofromdbo.memberasmwher em.firstnamelikek%andm.region_no6andm.member_no sqlserver 会根据索引方式，优化成下面方式来执行。 selecta.me

7、mber_no,a.firstname,b.region_nofrom(selectm.member_no,m. firstnamefromdbo.memberasmwherem.firstnamelikek%andm.member_n o6)b-这个查询可以直接使用 member_region_link 非聚集索引，而且这 个非聚集索引覆盖了所有查询列 -实际执行时，只需要逻辑读取10 次 wherea.member_no=b.member_no 不信，你可以看这两个 sql 的执行计 划，以及逻辑读信息，都是一样的。 其实上面的sql,如果优化成下面的方式，实际的逻辑读消耗也是 一样的。

8、为何sqlserver不会优化成下面的方式。 是因为 and 操作符优化的另外一个原则。 1/26 的数据和 1/6 的数据找交集的速度要比 1/52 的数据和 1/3 的 数据找交集速度要慢。 selecta.member_no,a.firstname,b.region_nofrom(selectm.member_no,m. firstnamefromdbo.memberasmwherem.firstnamelikek%-1/26数 据 )a,(selectm.member_no,m.region_nofromdbo.memberasmwherem.regi on_no6andm.membe

9、r_no6andm.member_no6andm.member_no6andm .member_no6andm.member_no 对于 0，1 的意义如下：如果存在 聚集索引，则index(O)强制执行聚集索引扫描，index(1)强制执行聚集索 引扫描或查找(使用性能最高的一种) 。 如果不存在聚集索引，则index(0)强制执行表扫描，index(1)被解释 为错误。 总结知识点：简单来说，我们可以这么理解： sqlserver 对于每一 条查询语句。 会根据实际索引情况(sysindexes系统表中存储这些信息)，分析 每种组合可能的成本。 然后选择它认为成本最小的一种。 作为它实际执

10、行的计划。 成本代价计算的一个主要组成部分是逻辑 i/o 的数量，特别是对于 单表的查询。 and 操作要满足所有条件， 这样，经常会要求对几个数据集作交集。 数据集越小，数据集的交集计算越节省成本。 的项目中，竟然出现了滥用聚集索引的问题。 看来没有培训最最基础的索引的意义，代价，使用场景，是一个 非常大的失误。 这篇博客就是从这个角度来罗列索引的基础知识。 使用索引的意义索引在数据库中的作用类似于目录在书籍中的作 用，用来提高查找信息的速度。 使用索引查找数据，无需对整表进行扫描，可以快速找到所需数 据。 使用索引的代价索引需要占用数据表以外的物理存储空间。 创建索引和维护索引要花费一定的

11、时间。 当对表进行更新操作时，索引需要被重建，这样降低了数据的维 护速度。 创建索引的列主键外键或在表联接操作中经常用到的列在经常查 询的字段上最好建立索引不创建索引的列很少在查询中被引用包含较 少的惟一值定义为 text 、ntext 或者 image 数据类型的列 heaps 是 stagingdata 的 很 好 选 择 ， 当 它 没 有 任 何 index 时 excellentforhighperformancedataloading(parallelbulkloadandparallelindex creationafterload)excellentasapartitiontoapartitionedvieworapartitionedta ble 聚集索引提高性能的方法，在前面几篇博客中分别提到过，下面只 是一个简单的大纲，细节请参看前面几篇博客。 何时创建聚集索引？ clusteredindex会提高大多数table的性能，尤 其是当它满足以下条件时： 独特,狭窄 ,静止：最重要的条件持续增长的， 最好是只向上增加 identitydate,identityguid(onlywhenusingnewsequentialid()function) 聚 集 索引唯一性(独特型的问题)由于聚集索引的b