数据库索引规则.docx

1、数据库索引规则数据库建立索引常用的规则如下：1、表的主键、外键必须有索引；2、数据量超过300的表应该有索引；3、经常与其他表进行连接的表，在连接字段上应该建立索引；4、经常出现在Where子句中的字段，特别是大表的字段，应该建立索引；5、索引应该建在选择性高的字段上；6、索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引；7、复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替：A、正确选择复合索引中的主列字段，一般是选择性较好的字段；B、复合索引的几个字段是否经常同时以AND方式出现在Where子句中？单字段查询是否 极少甚至没有？如果是，则可以建立复合索引；否则考虑单字段

2、索引；C、如果复合索引中包含的字段经常单独出现在Where子句中，则分解为多个单字段索引；D、如果复合索引所包含的字段超过3个，那么仔细考虑其必要性，考虑减少复合的字段；E、如果既有单字段索引，又有这几个字段上的复合索引，一般可以删除复合索引；8、频繁进行数据操作的表，不要建立太多的索引；9、删除无用的索引，避免对执行计划造成负面影响；以上是一些普遍的建立索引时的判断依据。一言以蔽之，索引的建立必须慎重，对每个索引的必要性都应该经过仔细分析，要有建立的依据。因为太多的索引与不充分、不正确的索引对性能都毫无益处：在表上建立的每个索引都会增加存储开销，索引对于插入、删除、更新操作也会增加处理上的开

3、销。另外，过多的复合索引，在有单字段索引的情况下，一般都是没有存在价值的；相反，还会降低数据增加删除时的性能，特别是对频繁更新的表来说，负面影响更大。复合索引优化和适用范围索引可以包含一个、两个或更多个列。两个或更多个列上的索引被称作复合索引。例如，以下语句创建一个具有两列的复合索引：CREATE INDEX nameON employee (emp_lname, emp_fname)如果第一列 不能单独提供较高的选择性，复合索引将会非常有用。例如，当许多雇员具有相同的姓氏时，emp_lname 和 emp_fname 上的复合索引非常有用。因为每个雇员都有唯一的 ID，所以 emp_id 和

4、 emp_lname 上的复合索引可能没有用处，因此列 emp_lname 不会提供任何附加选择性。利用索引中的附加列，您可以缩小搜索的范围，但使用一个具有两列的索引不同于使用两个单独的索引。复合索引的结构与电话簿类似，它首先按姓氏对雇员进行排序，然后按名字对所有姓氏相同的雇员进行排序。如果您知道姓氏，电话簿将非常有用，如果您知道名字和姓氏，电话簿则更为有用，但如果您只知道名字而不知道姓氏，电话簿将没有用处。压缩的 B 树索引方法可显著提高复合索引的性能。列顺序在创建复合索引时，应该仔细考虑列的顺序。对索引中的所有列执行搜索或仅对前几列执行搜索时，复合索引非常有用；仅对后面的任意列执行搜索时，

5、复合索引则没有用处。如果您很可能仅对一个列多次执行搜索，则该列应该是复合索引中的第一列。如果您很可能对一个两列索引中的两个列执行单独的搜索，则应该创建另一个仅包含第二列的索引。包含多个列的主键始终会自动以复合索引的形式创建索引，其列的顺序是它们在表定义中出现的顺序，而不是在主键定义中指定的顺序。您应该考虑将通过主键来执行的搜索，以确定哪一列应该排在最前面。在后面的任何被频繁搜索的主键列上，应该考虑添加额外的索引。例如，假设您在两个列上创建一个复合索引。一个列包含雇员的名字，另一个列包含雇员的姓氏。您可以创建一个先包含名字后包含姓氏的索引。或者，您也可以创建一个先包含姓氏后包含名字的索引。虽然这

6、两个索引以两个列组织信息，但它们具有不同的功能。CREATE INDEX fname_lnameON employee emp_fname, emp_lname;CREATE INDEX lname_fnameON employee emp_lname, emp_fname;假设您需要搜索名字 John。唯一有用的索引是在索引的第一列包含名字的索引。由于名字为 John 的雇员会出现在索引中的任意位置，因此先按姓氏再按名字组织的索引没有用处。如果您认为很可能需要仅按名字或仅按姓氏查找雇员，则应该创建这两个索引。或者，您也可以创建两个索引，一个索引仅包含一个列。但是，请注意，Adaptive S

7、erver Anywhere 在处理单个查询时只使用一个索引来对任何一个表进行访问。即使您知道名字和姓氏，Adaptive Server Anywhere 也可能需要读取额外的行，以查找包含正确姓氏的行。当您使用 CREATE INDEX 命令创建索引时（如上例所示），列会按命令中所示的顺序创建。主键索引和列顺序列在主键索引中的顺序被强制为与列在表定义中出现的顺序相同，这与 PRIMARY KEY 约束中指定的列顺序无关。此外，Adaptive Server Anywhere 还将强制一个附加约束：表的主键列必须位于每个行的开头。因此，如果主键被添加到现有表中，服务器就可以重写整个表以确保键列

8、位于每个行的开头。在多个列出现在主键中的情况下，您应该考虑所需的搜索类型。如果合适，应切换列在表定义中的顺序，使最常搜索的列排在最前面，或者根据需要为其它列创建单独的索引。复合索引和 ORDER BY缺省情况下，索引的列按升序排列，但您可以选择通过在 CREATE INDEX 语句中指定 DESC 来将这些列按降序排列。只要 ORDER BY 子句仅包含索引中的列，Adaptive Server Anywhere 就可以选择使用索引来优化 ORDER BY 查询。此外，索引列的排序方式必须与 ORDER BY 子句完全相同或完全相反。对于单列索引，这种排序方式始终会使查询可以得到优化，但复合索

9、引则需要稍微多考虑一些问题。下表显示了一个两列索引的可能性。索引列 可优化的 ORDER BY 查询 不可优化的 ORDER BY 查询ASC、ASC ASC、ASC 或 DESC、DESC ASC、DESC 或 DESC、ASCASC、DESC ASC、DESC 或 DESC、ASC ASC、ASC 或 DESC、DESCDESC、ASC DESC、ASC 或 ASC、DESC ASC、ASC 或 DESC、DESCDESC、DESC DESC、DESC 或 ASC、ASC ASC、DESC 或 DESC、ASC含有两个以上的列的索引遵循与上述规则相同的一般规则。例如，假设您具有以下索引：C

10、REATE INDEX idx_exampleON table1 (col1 ASC, col2 DESC, col3 ASC)在这种情况下，以下查询可以得到优化：SELECT col1, col2, col3 from table1ORDER BY col1 ASC, col2 DESC, col3 ASC和SELECT col1, col2, col3 from exampleORDER BY col1 DESC, col2 ASC, col3 DESC索引不用于优化在 ORDER BY 子句中具有 ASC 和 DESC 的其它任何模式的查询。例如：SELECT col1, col2, c

11、ol3 from table1ORDER BY col1 ASC, col2 ASC, col3 ASC不会得到优化。Oracle数据库的完整性约束规则详解添加时间:2007-4-22完整性约束用于增强数据的完整性，Oracle提供了5种完整性约束： Check NOT NULL Unique Primary Foreign key完整性约束是一种规则，不占用任何数据库空间。完整性约束存在数据字典中，在执行SQL或PL/SQL期间使用。用户可以指明约束是启用的还是禁用的，当约束启用时，他增强了数据的完整性，否则，则反之，但约束始终存在于数据字典中。禁用约束，使用ALTER语句:ALTER TA

12、BLE table_name DISABLE CONSTRAINT constraint_name;或ALTER TABLE policies DISABLE CONSTRAINT chk_gender如果要重新启用约束：ALTER TABLE policies ENABLE CONSTRAINT chk_gender删除约束:ALTER TABLE table_name DROP CONSTRAINT constraint_name或ALTER TABLE policies DROP CONSTRAINT chk_gender;Check 约束在数据列上Check 约束需要一个特殊的布尔条件

13、或者将数据列设置成TRUE，至少一个数据列的值是NULL，Check约束用于增强表中数据内容的简单的商业规则。用户使用 Check约束保证数据规则的一致性。Check约束可以涉及该行同属Check约束的其他数据列但不能涉及其他行或其他表，或调用函数SYSDATE, UID,USER,USERENV。如果用户的商业规则需要这类的数据检查，那么可以使用触发器。Check约束不保护LOB数据类型的数据列和对象、嵌套表、VARRY、ref等。单一数据列可以有多个Check约束保护，一个Check约束可以保护多个数据列。创建表的Check约束使用CREATE TABLE语句，更改表的约束使用ALTER

14、TABLE语句。语法：CONSTRAINT constraint_name CHECK (condition);Check约束可以被创建或增加为一个表约束，当Check约束保护多个数据列时，必须使用表约束语法。约束名是可选的并且如果这个名字不存在，那么oracle将产生一个以SYS_开始的唯一的名字。例：CREATE TABLE policies(policy_id NUMBER,holder_name VARCHAR2(40),gender VARCHAR2(1) constraint chk_gender CHECK (gender in (M,F),marital_status VARC

15、HAR2(1),date_of_birth DATE,constraint chk_marital CHECK (marital_status in(S,M,D,W);NOT NULL约束NOT NULL约束应用在单一的数据列上，并且他保护的数据列必须要有数据值。缺省状况下，ORACLE允许任何列都可以有NULL值。某些商业规则要求某数据列必须要有值，NOT NULL约束将确保该列的所有数据行都有值。例：CREATE TABLE policies(policy_id NUMBER,holder_name VARCHAR2(40) NOT NULL,gender VARCHAR2(1),mari

16、tal_status VARCHAR2(1),date_of_birth DATE NOT NULL);对于NOT NULL的ALTER TABLE语句与其他约束稍微有点不同。ALTER TABLE policies MODIFY holder_name NOT NULL唯一性约束(Unique constraint)唯一性约束可以保护表中多个数据列，保证在保护的数据列中任何两行的数据都不相同。唯一性约束与表一起创建，在唯一性约束创建后，可以使用ALTER TABLE语句修改。语法：column_name data_type CONSTRAINT constraint_name UNIQUE如

17、果唯一性约束保护多个数据列，那么唯一性约束要作为表约束增加。语法如下：CONSTRAINT constraint_name (column) UNIQUE USING INDEX TABLESPACE (tablespace_name) STORAGE (stored clause)唯一性约束由一个B-tree索引增强，所以可以在USING子串中为索引使用特殊特征，比如表空间或存储参数。CREATE TABLE语句在创建唯一性约束的同时也给目标数据列建立了一个唯一的索引。CREATE TABLE insured_autos(policy_id NUMBER CONSTRAINT pk_poli

18、cies PRIMARY KEY,vin VARCHAR2(10),coverage_begin DATE,coverage_term NUMBER,CONSTRAIN unique_auto UNIQUE (policy_id,vin) USING INDEX TABLESPACE index STORAGE (INITIAL 1M NEXT 10M PCTINCREASE 0);用户可以禁用未以性约束，但他仍然存在，禁用唯一性约束使用ALTER TABLE 语句。ALTER TABLE insured_autos DISABLE CONSTRAIN unique_name;删除唯一性约束，

19、使用ALTER TABLE.DROP CONSTRAIN语句：ALTER TABLE insured_autos DROP CONSTRAIN unique_name;注意用户不能删除在有外部键指向的表的唯一性约束。这种情况下用户必须首先禁用或删除外部键（foreign key)。删除或禁用唯一性约束通常同时删除相关联的唯一索引，因而降低了数据库性能。经常删除或禁用唯一性约束有可能导致丢失索引带来的性能错误。要避免这样错误，可以采取下面的步骤：1、在唯一性约束保护的数据列上创建非唯一性索引。2、添加唯一性约束。主键(Primary Key)约束表有唯一的主键约束。表的主键可以保护一个或多个列，

20、主键约束可与NOT NULL约束共同作用于每一数据列。NOT NULL约束和唯一性约束的组合将保证主键唯一地标识每一行。像唯一性约束一样，主键由B-tree索引增强。创建主键约束使用CREATE TABLE语句与表一起创建，如果表已经创建了，可以使用ALTER TABLE语句。CREATE TABLE policies(policy_id NUMBER CONSTRAINT pk_policies PRIMARY KEY,holder_name VARCHAR2(40),gender VARCHAR2(1),marital_status VARCHAR2(1),date_of_birth DA

21、TE);与唯一性约束一样，如果主键约束保护多个数据列，那么必须作为一个表约束创建。CREATE TABLE insured_autos(policy_id NUMBER,vin VARCHAR2(40),coverage_begin DATE,coverage_term NUMBER,CONSTRAINT pk_insured_autos PRIMARY KEY (policy_id,vin)USING INDEX TABLESPACE indexSTORAGE (INITIAL 1M NEXT 10M PCTINCREASE 0);禁用或删除主键必须与ALTER TABLE 语句一起使用:A

22、LTER TABLE policies DROP PRIMARY KEY;或ALTER TABLE policies DISABLE PRIMARY KEY;外部键约束（Foreign key constraint)外部键约束保护一个或多个数据列，保证每个数据行的数据包含一个或多个null值，或者在保护的数据列上同时拥有主键约束或唯一性约束。引用（主键或唯一性约束）约束可以保护同一个表，也可以保护不同的表。与主键和唯一性约束不同外部键不会隐式建立一个B-tree索引。在处理外部键时，我们常常使用术语父表（parent table)和子表（child table),父表表示被引用主键或唯一性约束

23、的表，子表表示引用主键和唯一性约束的表。创建外部键使用CREATE TABLE语句，如果表已经建立了，那么使用ALTER TABLE语句。CREATE TABLE insured_autos(policy_id NUMBER CONSTRAINT policy_fkREFERENCE policies(policy_idON DELETE CASCADE,vin VARCHAR2(40),coverage_begin DATE,coverage_term NUMBER,make VARCHAR2(30),model VARCHAR(30),year NUMBER,CONSTRAIN auto_

24、fk FROEIGN KEY (make,model,year)REFERENCES automobiles (make,model,year)ON DELETE SET NULL);ON DELETE子串告诉ORACLE如果父纪录（parent record)被删除后，子记录做什么。缺省情况下禁止在子记录还存在的情况下删除父纪录。外部键和NULL值在外部键约束保护的数据列中NULL值的处理可能产生不可预料的结果。ORACLE 使用ISO standar Match None规则增强外部键约束。这个规则规定如果任何外部键作用的数据列包含有一个NULL值，那么任何保留该键的数据列在父表中没有匹配

25、值。比如，在父表AUTOMOBILES中，主键作用于数据列 MAKE，MODEL，YEAR上，用户使用的表INSURED_AUTOS有一个外部约束指向AOTOMOBILES，注意在 INSURES_AUTOS中有一数据行的MODEL列为NULL值，这一行数据已经通过约束检查，即使MAKE列也没有显示在父表 AUTOMOBILES中，如下表：表1 AUTOMOBILES表2 INSURED_AUTOS延迟约束检验（Deferred Constraint Checking）约束检验分两种情况，一种是在每一条语句结束后检验数据是否满足约束条件，这种检验称为立即约束检验（immediately che

26、cking）,另一种是在事务处理完成之后对数据进行检验称之为延迟约束检验。在缺省情况下Oracle约束检验是立即检验（immediately checking),如果不满足约束将先是一条错误信息，但用户可以通过SET CONSTRAINT语句选择延迟约束检验。语法如下：SET CONSTRAINT constraint_name|ALL DEFEERRED|IMMEDIATE -;序列（Sequences）Oracle序列是一个连续的数字生成器。序列常用于人为的关键字，或给数据行排序否则数据行是无序的。像约束一样，序列只存在于数据字典中。序列号可以被设置为上升、下降，可以没有限制或重复使用直到

27、一个限制值。创建序列使用SET SEQUENCE语句。CREATE SEQUENCE schema sequence KEYWORDKEYWORD包括下面的值：删除序列使用DROP SEQUENCE语句。DROP SEQUENCE sequence_name索引(INDEXES)索引是一种可以提高查询性能的数据结构，在这一部分我们将讨论索引如何提高查询性能的。ORACLE提供了以下几种索引：B-Tree、哈希（hash)、位图（bitmap)等索引类型；基于原始表的索引；基于函数的索引；域（Domain）索引。实际应用中主要是B-Tree索引和位图索引，所以我们将集中讨论这两种索引类型。B-T

28、ree索引B-Tree索引是最普通的索引，缺省条件下建立的索引就是这种类型的索引。B- Tree索引可以是唯一或非唯一的，可以是单一的（基于一列）或连接的（多列）。B-Tree索引在检索高基数数据列（高基数数据列是指该列有很多不同的值）时提供了最好的性能。对于取出较小的数据B-Tree索引比全表检索提供了更有效的方法。但当检查的范围超过表的10%时就不能提高取回数据的性能。正如名字所暗示的那样，B-Tree索引是基于二元树的，由枝干块（branch block)和树叶块（leaf block)组成,枝干块包含了索引列（关键字）和另一索引的地址。树叶块包含了关键字和给表中每个匹配行的ROWID。位图索引位图索引主要用于决策支持系统或静态数据，不支持行级锁定。位图索引可以是简单的（单列）也可以是连接的（多列），但在实践中绝大多数是简单的。位图索引最好用于低到中群集（cardinality)列，在这些列上多位图索引可以与 AND或OR操作符结合使用。位图索引使用位图作为键值，对于表中的每一数据行位图包含了TRUE（1）、FALSE（0）、或NULL值。位图索引的位图存放在B-Tree结构的页节点中。B-Tree结构使查找位图非常方便和快速。另外，位图以一种压缩格式存放，因此占用的磁盘空间比B-Tree索引要小得多。