第5章 索引与数据完整性[60页]
合集下载
0005 数据库完整性
5.3 用户定义的完整性
用户定义的完整性
not null create table student ( sno char( 5 ), sname varchar( 20 ) not null, ssex char( 2 ), sage smallint, sdept varchar(15), constraint PK_student_sno primary key( sno ) )
参照完整性检查和违约处理
insert into t1 values(1) insert into t1 values(1) insert into t1 values(2) insert into t1 values(3) insert into t2 values(1) insert into t2 values(1) insert into t2 values(2) insert into t2 values(3) insert into t2 values(4) 拒绝
5.2 参照完整性
参照完整性
参照完整性规则 若属性 （ 或属性组 ） F是基本关系 的外 若属性（或属性组） 是基本关系 是基本关系R的外 码它与基本关系S的主码 相对应（ 的主码K 码它与基本关系 的主码 s相对应（ 基本 关系R和 可能是相同的关系 可能是相同的关系） 则对于R 关系 和S可能是相同的关系），则对于 中每个元组在F上的值必须为 上的值必须为： 中每个元组在 上的值必须为： 或者取空值 或者取空值 或者等于 中某个元组的主码值。 或者等于S中某个元组的主码值 或者等于 中某个元组的主码值。
实体完整性定义 创建表的时候用 创建表的时候用primary key定义 定义
示例：创建 示例：创建student表，将属性 定义为主码 表 将属性sno定义为主码 create table student ( sno char(5),constraint PK_stu_sno primary key, sname varchar(20) not null, ssex char(2), sage smallint, sdept varchar(20)， ) onstraint PK_stu_sno primary key(sno) c
第五章 索引与数据完整性约束
第5章索引与数据完整性约束一、索引的用途与概念1.MySQL中访问表中的行，最常用的是顺序访问和索引访问。
2.索引的概念：根据表中一列或若干列按照一定顺序建立的列值与记录之间的对应关系表。
（存储在索引文件中）像不像图书的目录？3.索引的用途：在表的某些列上创建了索引之后查找数据时可以直接根据该列上的索引找到对应行的位置，从而加快了查找速度。
4.一个表中可以创建多个索引，基于一个字段也可以创建多个索引。
二、索引的分类1.B树索引：最常用a)普通索引i.只包含索引关键字indexii.对建立索引的列没有唯一性要求b)唯一性索引i.关键字uniqueii.创建索引的列，或列的组合值必须唯一c)主键索引i.关键字primary keyii.只能在创建主键约束时自动创建d)全文索引i.关键字fulltextii.只能在varchar或text类型的列上创建iii.对于大规模的数据集，通过alter table或create table命令创建全文索引要比把记录插入带有全文索引的空表更快。
2.HASH（哈希）索引：速度较快a)不需要建立树结构，但是所有的值都保存在一个列表中，这个列表指向相关页和行。
三、创建索引1.create index语句创建索引a)格式：create [unique|fulltext|spatial] index 索引名[using btree|hash]on table_name(索引列[(length)] [asc|desc] )其中：using子句表示索引的类型，默认为btree; length表示使用索引列的前多少个字符创建索引，这在索引列值较长的情况下比较有用。
如在blob或text列上创建索引。
b)例题：例5.1 create index st on student(stuname(4) asc);查看索引的命令: show index from 表名例如：show index from student;例5.2 创建复合索引。
索引默认值与数据完整性
9
6.1.3
索引的创建
索引选项设置：
PAD_INDEX FILLFACTOR = fillfactor IGNORE_DUP_KEY DROP_EXISTING STATISTICS_NORECOMPUTE SORT_IN_TEMPDB
10
6.1.3
索引的创建
创建索引需要注意的两点：
只要满足索引条件，就可以在计算列上创建索 引，但计算列必须具有确定性（精确），且不 text 能包含text等数据类型的列。 可以在视图上创建索引，索引视图能自动反映 出创建索引后对基表数据所作的修改。
21
6.2.2 默认值对象的定义、使用与删除 默认值对象的定义、
3. 默认值对象的删除
如果要删除一个默认值对象，首先应解除默认值对象与用户定义 类型及表字段的绑定关系，然后才能删除该默认值对象。 (1) 利用sp_unbindefault解除绑定关系 语法格式：
sp_unbindefault [@objname =] 'object_name' [, [@futureonly =] 'futureonly_flag']
XS表设计器界面 表设计器界面
7
6.1.3
索引的创建
(5) 在“索引／键”属性页点击“新建”按钮 点击“ 点击 新建”按钮，然后，系统将自 动创建新索引的名称，用户也可自己定义索引名，在“列名”下选 择要创建索引的列，在此选择“姓名”这一列，最多可选择16 列。
8
6.1.3
索引的创建
2．利用SQL命令建立索引 ．利用 命令建立索引
索引、默认值与数据完整性
本章主要内容
索引 通过索引提高查询速度，类似于书的目录 默认值 方便用户的使用 数据完整性 数据库中的数据在逻辑上的一致性和准确性。 主要包括域完整性、实体完整性和参照完整 性三个方面。
07 索引与数据完整性
索引的分类
在SQL Server 2008中提供的索引类型主要有以下几类：聚集索 引、非聚集索引、唯一索引、包含性列索引、索引视图、全文索引 、空间索引、筛选索引、和XML索引。 按照存储结构的不同，可以将索引分为两类：聚集索引和非聚集索 引。
索引的操作
索引创建的操作步骤如下： （ 1 ） 启 动 SQL Server Management Studio ， 并 连 接 到 SQL Server 2008数据库。
索引与数据完整性
本讲大纲： 1、索引的概念 2、索引的优缺点 3、索引的分类 4、索引的操作 5、索引的分析与维护 6、全文索引
7、数据完整性
索引的概念
索引是为了加速对表中数据行的检索而创建的一种分散存储 结构。它是针对一个表而建立的，每个索引页面中的行都含有逻 辑指针，指向数据表中的物理位置，以便加速Байду номын сангаас索物理数据。因 此，对表中的列是否创建索引，将对查询速度有很大的影响。一 个表的存储是由两部分组成的，一部分用来存放表的数据页，另 一部分存放索引页。从中找到所需数据的指针，然后直接通过该 指针从数据页面中读取数据，从而提高查询速度。
数据完整性
数据完整性是SQL Server用于保证数据库中数据一致性的一种机制 ，防止非法数据存入数据库。具体地数据完整性主要体现在以下几 点。 数据类型准确无误。 数据取值符合规定的范围。 多个数据表之间的数据不存在冲突。
索引的分析与维护
索引的分析： 使用SHOWPLAN语句 显示查询语句的执行信息，包含查询过程中连接表时所采取的每个 步骤以及选择哪个索引。 使用STATISTICS IO语句 STATISTICS IO语句表示使 SQL Server 显示有关由 Transact-SQL 语句生成的磁盘活动量的信息。 索引的维护： 使用DBCC SHOWCONTIG语句 显示指定表的数据和索引的碎片信息。当对表进行大量的修改或添 加数据后，应该执行此语句来查看有无碎片。 使用DBCC DBREINDEX语句 DBCC DBREINDEX表示对指定数据库中的表重新生成一个或多个索 引。
第5章 第一讲 数据完整性
第五章 数据库完整性
5.1 实体完整性 5.2 参照完整性 5.3 用户定义的完整性 5.4 完整性约束命名字句 5.5 触发器 5.6 小结
An Introduction to Database System
5.1 实体完整性
？
5.1.1 实体完整性定义
关系数据库的实体完整性的定义，有两种方式： 关系数据库的实体完整性的定义，有两种方式： 创建表时： 多属性构成主键问题】 ① 创建表时：Primary Key【单/多属性构成主键问题】 【 多属性构成主键问题 ② 修改表定义时：添加Primary Key 约束 修改表定义时：添加 【先前表定义时需将该字段设置为非空】 先前表定义时需将该字段设置为非空】 具体实例请参照： 具体实例请参照：第三章 数据定义部分的教学内容
An Introduction to Database System
5.1 实体完整性
5.1.2 实体完整性检查和违约处理
如何保证？ 如何保证？
核查记录主码值是否唯一的方法：全表扫描， 核查记录主码值是否唯一的方法：全表扫描，即依次判 断表中的每一条记录的主码值是否与即将插入的记录主码 值重复。（教材P153页） 值重复。（教材 。（教材 页 全表扫描一般情况是十分耗时， 全表扫描一般情况是十分耗时，尤其在数据记录量较多 的情况下。为了避免全表扫描， 的情况下。为了避免全表扫描，RDBMS核心一般都在主 核心一般都在主 码字段自动建立 树的聚簇索引 从而提高检查效率。 码字段自动建立B+树的聚簇索引，从而提高检查效率。 自动建立 树的聚簇索引，
An Introduction to Database System
数据库完整性
为维护数据库的完整性， 为维护数据库的完整性，DBMS必须 必须
第5章 索引与数据完整性
5.1 索 引
图5.2 位图索引
5.1.1索引的分类 在下列情况中，位图索引比B*树索引更有利： 当表包含数百万行且键列的基数很低（即该列中重复的值很多）时； 当查询经常使用涉及OR运算符的多个WHERE条件组合时； 当键列上存在只读或很少的更新操作时。
表5-1 B树索引与位图中维护索引 在OEM中维护索引的操作，除了一些特殊的信息不能再修改，其他与创建索引的操作相 同。 在如图5.3所示的界面中，选择要维护的索引，单击“编辑”按钮，进入“编辑索引”界 面， 如图5.10所示。在各个选项页面上重新指定相应的设置，确认无误后，单击“应用”按钮， 完成修改。
5.1 索 引
B*树操作更适合索引动态表的联机事务处理环境，而位图索引更适合在大型静态 表上使用复杂查询的数据仓库环境。
5.1.2 使用索引的原则
创建索引应考虑的问题： (1)索引能够提高查询性能并降低DML操作速度。始终使表所需的索引数保持最少。 (2)将索引放在一个单独的表空间中，不要放在有撤销段、临时段和表的表空间中。 (3)对大型索引而言，避免生成重做日志可显著提高性能。可以考虑使用 NOLOGGING子句创建大型索引。 (4)由于索引项比索引行小，索引每个索引块中可能包含更多的项。因此，INITRANS 参数在索引中通常应设置的比在对应的表中高。 当用户考虑在表中使用索引时，应遵循下列一些基本的原则： (1) 在表中插入数据后创建索引 (2) 索引正确的表和列 (3) 合理安排索引列——列的排序，将最常用的列放到前面 (4) 限制表中索引的数量 (5) 指定索引数据块空间的使用 (6) 根据索引大小设置存储参数
5.1.3 创建索引
创建索引有三种方法：随数据库表创建、使用Oracle 10g管理控制台工具单独创建和使 用SQL命令创建索引。 1. 随数据库表一起创建索引 在创建数据库表时，如果表中包含有唯一关键字或主关键字，则Oracle 10g自动为这两 种关键字所包含的列建立索引。如果不特别指定，系统将默认为该索引定义一个名字。表 建立之后，实际上就是在表XS的列XH上建立了一个索引，并赋予名称PK_XH。如图5.3所 示。这种方法创建的索引是非排序索引，既正向索引，以B*树形式存储。
第5章 索引与数据完整性
SQL Server 2000实用教程
2012年7月29日
5.2 数据完整性
5.2.1 域完整性 域完整性也称为列完整性，是指定一个数 据集对某个列是否有效和确定是否允许为空值。 通常使用有效性检查强制域完整性，也可以通 过限定列中允许的数据类型、格式或可能值的 范围来强制数据完整性。
SQL Server 2000实用教程
SQL Server 2000实用教程 2012年7月29日
5.1 索引
5.1.1 索引的概念 1. 索引的概念 索引是一个单独的，物理的数据库结构。它由某个表中 的一列或者若干列的值，以及这些值的记录在数据表中存 储位置的物理地址所组成。 当SQL Server进行数据查询时，查询优化器会自动 计算现有的几种查询方案，通过方案的开销大小和速度快 慢决定方案是否被执行。如果使用索引查找的速度不如直 接扫描表格速度快时，SQL Server会使用直接扫描表格的 方法。建立不能被SQL Server使用的索引会给系统增加负 担，索引要占用存储空间，而且为了自动维护索引，在插 入、删除或者更新数据的时候，SQL Server还要花费额外 的操作来维护索引的有效性。
SQL Server 2000实用教程
2012年7月29日
5.1 索引
但是，建立索引也会消耗硬盘空间并招致开销和维 护 成 本 。 当 在 建 立 索 引 的 列 上 修 改 数 据 时 ， SQL Server将会更新相关的索引，从而花费额外的时间和资 源。因此，不要创建不经常使用的索引，不要在含有大 量重复数据的列上创建索引，也不要在定义为 text、 ntext 或者 image 数据类型的列上创建索引。 建立索引应该遵循以下原则： · 在主键上创建索引 · 在经常需要检索的字段上创建索引 · 在外键上创建索引 · · 在经常要排序的列上创建索引
第5章 索引与数据完整性约束(gai)
界面方式创建和删除索引添加索引列图54添加索引列515515索引对查询的影响目前本书实例中所涉及的表最多只有几十行的数据所以有没有建立索引还体会不到查询速度上的差异可是当一个表里有成千上万行数据的时候差异就非常明显了
第5章 索引与数据完整性约束
5.1 索引 5.2 数据完整性约束
5.1 索引
5.1.1 索引的分类
CREATE TABLE XS_KC ( 学号 CHAR(6) NOT NULL, 课程号 CHAR(3) NOT NULL, 成绩 TINYINT(1), 学分 TINYINT(1), PRIMARY KEY(学号,课程号), INDEX CJ(成绩) );
练习：用 alter table 语句创建前面练习1至 练习4 中索引
练习：用 create table 语句创建前面练习1至 练习 4 中索引
5.1.3 删除索引
1. 使用DROP INDEX语句删除索引 语法格式：
DROP INDEX index_name ON tbl_name
这个语句语法非常简单，index_name为要删除的索引名，tb1_name为索引所在的 表。 【例5.6】 删除XS表上的XS_XH索引。
● ALTER TABLE XS ADD INDEX XS_XM USING BTREE (姓名);
Байду номын сангаас
【例5.4】 以XS表为例（假设XS表中主键未定），创建这样的索引，以加速表 的检索速度：
ALTER TABLE XS ADD PRIMARY KEY(学号), ADD INDEX mark(出生日期,性别);
练习1：根据yggl数据库的employees表中的 departmentid列建立一个升序索引depart_ind。 练习2：根据yggl数据库的employees表的name列 和address列上建立一个复合索引ad_ind。
第5章 索引与数据完整性约束
5.1 索引 5.2 数据完整性约束
5.1 索引
5.1.1 索引的分类
CREATE TABLE XS_KC ( 学号 CHAR(6) NOT NULL, 课程号 CHAR(3) NOT NULL, 成绩 TINYINT(1), 学分 TINYINT(1), PRIMARY KEY(学号,课程号), INDEX CJ(成绩) );
练习：用 alter table 语句创建前面练习1至 练习4 中索引
练习：用 create table 语句创建前面练习1至 练习 4 中索引
5.1.3 删除索引
1. 使用DROP INDEX语句删除索引 语法格式：
DROP INDEX index_name ON tbl_name
这个语句语法非常简单，index_name为要删除的索引名，tb1_name为索引所在的 表。 【例5.6】 删除XS表上的XS_XH索引。
● ALTER TABLE XS ADD INDEX XS_XM USING BTREE (姓名);
Байду номын сангаас
【例5.4】 以XS表为例（假设XS表中主键未定），创建这样的索引，以加速表 的检索速度：
ALTER TABLE XS ADD PRIMARY KEY(学号), ADD INDEX mark(出生日期,性别);
练习1：根据yggl数据库的employees表中的 departmentid列建立一个升序索引depart_ind。 练习2：根据yggl数据库的employees表的name列 和address列上建立一个复合索引ad_ind。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
01 索引的分类
索引的分类
（1）索引按存储方法分类，可以分为两类：B*树索引和位图索引。 B*树索引的存储结构类似图书的索引结构，有分支和叶两种类型的存储数据块，分支块相当于图书的大 目录，叶块相当于索引到的具体的书页。Oracle用B*树（B*-tree）机制存储索引条目，以保证用最短路 径访问键值。默认情况下大多使用B*树索引，该索引就是通常所说的唯一索引、逆序索引等。 位图索引主要用来节省空间，减少Oracle对数据块的访问。它采用位图偏移方式来与表的行ID号对应。 采用位图索引一般是在重复值太多的表字段情况下。位图索引在实际密集型OLTP（数据事务处理）中 用得比较少，因为OLTP会对表进行大量的删除、修改和新建操作，Oracle每次进行操作都会对要操作 的数据块加锁，所以多人操作时很容易产生数据块锁、等待、甚至死锁现象。在OLAP（数据分析处理） 中应用位图索引有优势，因为OLAP中大部分是对数据库的查询操作，而且一般采用数据仓库技术，所 以大量数据采用位图索引时节省空间比较明显。当创建表的命令中包含有唯一性关键字时，不能创建位 图索引。创建全局分区索引时也不能选用位图索引。
索引的分类
（2）索引按功能和索引对象可分为以下六种类型。
唯一索引。唯一索引意味着不会有两行记录相同的索引键值。唯一索引表中的记录没有RowID，所以不能再对 ① 其建立其他索引。在Oracle 11g中，要建立唯一索引，必须在表中设置主关键字，建立了唯一索引的表只按照
该唯一索引结构排序。
② 非唯一索引。不对索引列的值进行唯一性限制的称为非唯一索引。
必须在要索引的表上具有INDEX权限。
必须具有CREATE ANY INDEX权限。
语法格式为：
CREATE [UNIQUE∣BITMAP] INDEX
/*索引类型*/
[<用户方案名>.]<索引名>
ON <表名>(<列名> | <列名表达式> [ASC∣DESC] [,…n])
[LOGGING | NOLOGGING]
③ 分区索引。所谓分区索引是指索引可以分散地存在于多个不同的表空间中，其优点是可以提高数据查询的效率。
④ 未排序索引。未排序索引也称为正向索引。由于Oracle 11g数据库中的行是按升序排序的，因此创建索引时不 必指定对其排序而使用默认的顺序。
⑤ 逆序索引。逆序索引也称为反向索引。该索引同样保持索引列按顺序排列，但是颠倒已索引的每列的字节。
创建索引
（3）所有选项设置完后单击“确定”按钮完成索引的创建，索引创建完后单击XSB表，在“索引”选项页可以看到 新创建的索引XSB_NAME_INDEX，如图5.3所示。
创建索引
2．以命令方式创建索引
使用SQL命令可以灵活方便地创建索引。在使用SQL命令创建索引时，必须满足下列条件之一。
索引的表或簇必须在自己的模式中。
（1）
合理安排索引列。在CREATE INDEX 语句中，列的排序会影响查询的性能，
通常将最常用的列放在前面。创建一 （3）
个索引来提高多列查询时，应该清楚 地了解这个多列索引对什么列的存取 有效、对什么列的存取无效。
索引正确的表和列。如果经常检
索的内容仅为包含大量数据的表
（2） 中少于15%的行，就需要创建索引。
第5章 索引与数据完整性
索引
索引
在Oracle 11g中，索引是一种供服务器在表中快速查找一行的数据库结构。在数据库中建立索引主要有以下作 用：
（1）快速存取数据；
（3）实现表与表之间的参 照完整性；
（2）既可以改善数据库性 能，又可以保证列值的唯 一性；
（4）在使用ORDER BY、 GROUP BY子句进行数据检索 时，利用索引可以减少排序 和分组的时间。
⑥
பைடு நூலகம்
基于函数的索引。基于函数的索引是指索引中的一列或者多列是一个函数或者表达式，索引根据函数或者表 达式计算索引列的值。可以将基于函数的索引创建成为位图索引。
02 使用索引的原则
使用索引的原则
在表中插入数据后创建索引。在表中插入数 据后，创建索引效率将更高。因为如果在装 载数据之前创建索引，那么插入每行时 Oracle都必须更改索引。
（4）
根据索引大小设置存储参数。创建
索引之前应先估计索引的大小，以 便更好地规划和管理磁盘空间。单
（6）
个索引项的最大值大约是数据块大
小的一半。
指定索引数据块空间。创建索引时，索
（5）
引的数据块是用表中现存的值填充的， 直到达到PCTREE为止。因此，如果打算
将许多行插入到被索引的表中，PCTREE
为了改善多个表的相互关系，常
常使用索引列进行关系连接。
使用索引的原则
例如，当在A、B、C三列上创建索引时，实际得到的顺序如下： A AB ABC 所以，可以获得A列的索引、A和B列结合的索引以及A、B、C三列结合的索引。不能得到的顺序如下： B BC C
使用索引的原则
限制表中索引的数量。尽管表可以有任意数 量的索引，但是索引越多，在修改表中的数 据时对索引做出相应更改的工作量也越大， 效率也就越低。因此，应及时删除目前不用 的索引。
创建索引
说明：
（１）
UNIQUE：指定索引所 基于的列（或多列）值 必须唯一。默认的索引 是非唯一索引。Oracle 建议不要在表上显式定 义UNIQUE索引。
/*指定是否创建相应的日志记录*/
[COMPUTE STATISTICS]
/*生成统计信息*/
[COMPAESS | NOCOMPRESS]
/*对复合索引进行压缩*/
[TABLESPACE <表空间名>]
/*索引所属表空间*/
[SORT | NOSORT]
/*指定是否对表进行排序*/
[REVERSE]
就应设置得大一点。不能给索引指定
PCTUSED。
03 创建索引
1．以界面方式创建索引 2．以命令方式创建索引
创建索引
1．以界面方式创建索引
以在XSB表的“姓名”列创建索引为例，使用SQL Developer创建索引的操作过程如下。 （1）启动SQL Developer，展开连接myorcl，右击要创建索引的XSB表，选择“索引”菜单下的“创建索引”子菜 单项，如图5.1所示。 （2）在弹出的“创建索引”窗口中创建索引，如图5.2所示。