SQL学习--索引和约束的了解使用

索引特征及概念

　　索引是最为常用的改善数据库性能的技术。SQL Server引入索引主要是为了提高查询的效率，它能够加速ORDER BY和GROUP BY子句的操作，保证数据的唯一性，并加快表连接的速度。

索引的概念和创建原则

　　索引就是一个指向表中数据的指针，如果我们把书比作数据，那么各个章节就相对于数据库中的表，而书的目录就是索引了。

　　索引是在基本表的列上建立的一种数据库对象，它和基本表分开存储，它的建立或撤销对数据的内容毫无影响。

索引一经创建，就完全由系统自动选择和维护，不需要用户指定使用索引，也不需要用户执行打开索引或进行重新索引等操作。

在创建索引时，务必注意下列问题

（1）基本表的记录数量越多，记录越长，越有必要创建索引，这对加快查询速度十分有效。

（2）索引是为了加快查询速度而创建的，但同时会影响基本表的插入、删除或修改等更新活动的速度。因此只有那些对数据量大、查询频度高、实时性要求强的基本表才创建索引，且一张基本表上不要创建过多的索引。

（3）索引是建立在基本表列上的对象，对哪个列进行索引，通常根据列在WHERE、ORDER BY、GROUP BY子句中出现的频率决定。

不适合建索引的列的情况

（1）包含太多重复值的列

（2）查询中很少被引用的列

（3）值特别长的列

（4）具有很多null值的列

（5）需要经常插入、删除、修改的列

（6）记录较少的基本表

（7）需要进行频繁的大批量数据更新的基本表

索引的类型

1、从列的使用角度可将索引分为单列索引、唯一索引、复合索引三类。

2、从是否改变基本表记录的物理位置角度可分为聚集索引和非聚集索引两类。

分类

1、单列索引

      是对基本表的某一单独的列进行索引，通常应对每个基本表的主关键字建立单列索引。

2、唯一索引

      一旦在一个或多个列上建立了唯一索引，则不允许在表中相应的列上插入任何相同的取值。

3、复合索引

      是针对基本表中两个或两个以上列建立的索引。

4、聚集索引

      采用聚集索引会改变基本表中记录的物理存储顺序。

5、非聚集索引

      采用非聚集索引，表中记录的物理顺序与索引顺序不同，即表中的记录仍按实际插入的先后顺序排列，不按索引列排序。

聚集和非聚集索引的对比

1、存取速度

      从建立了聚集索引的表中取出数据要比建立了非聚集索引的表快，但相对而言，聚集索引会降低向表中插入、删除和修改数据的速度。

2、索引的数据         

      对聚索引的主要限制是每个表只能建立一个聚集索引，但是一个表可以有不止一个非聚集索引。

3、所需空间

      非聚集索引需要较多的硬盘空间和内存

创建和删除索引

1、使用企业管理器方法

         （1）通过点击“钥匙”图标创建，这种方式将在所选定的一个或多个列上创建一个聚集索引，是表的主关键字索引。

         （2）即使用索引管理菜单创建、修改、撤销索引。

                     鼠标右击——〉所有任务——〉管理索引

2、使用Transact SQL创建索引

     Transact SQL使用create index命令创建索引，常用的语法结构如下：

     create [unique]  [clustered|nonclustered] index <索引名> 

                 on   <表名> (<列名1> [次序] [，<列名2>[次序]]···)

     说明：

          unique:为表或视图创建唯一索引（不允许存在索引值相同的两行）。

          clustered:聚集索引。

          nonclustered:非聚集索引，默认值

          索引名：在实际使用时，，用户并不需要知道索引名，但在创建阶段，索引名应符合SQL Server的命名规则，并且在整个数据库中，索引名不能重复。

     例如：create unique clusteredindex myindex1

               on  成绩表（学号，课程号）

3、使用Transact SQL删除索引

      语法结构：drop indextable_name.index_name [,···n]

约束

　　设计数据库完整性的目的是为了防止数据库存在不符合语义的数据，防止错误信息的输入和输出。

　　SQL Server提供的用来实施数据完整性的途径主要是约束(Constraint)、标识列(Identity Column)、默认(Default)、规则(Rule)、触发器(Trigger)、数据类型(Data Type)、索引(Index)和存储过程(Stored Procedure)等

使用约束实施数据的完整性

　　约束的用途是限制用户输入到表中的数据的值的范围，一般分为列级约束和表级约束两种。要浏览某张表格上所有约束的信息，可以使用存储过程：

           sp_helpconstraint 表名

 

(1)  primary key  约束

     特征：

       a、创建primary key约束时，SQL Server会自动创建一个唯一的聚集索引；

       b、定义了primary key约束的字段的取值不能重复，并且不能取null值；

       c、每个表只能定义一个primary key约束；

       d、如果表中已经有了聚集索引，那么在创建primary key约束之前，要么指定所创建的是非聚集索引，要么删除已有的聚集索引

create table Student(
    id int not null,
    name  varchar(50) not null,
    constraint pk_Student primary key(id)
)

create table Student1(
    id int not null,
    name  varchar(50) not null,
    constraint pk_Student1 primary key nonclustered(id)
)

SQL Server中定义主键时，默认生成聚集索引，唯一的好处是主键列范围扫描/查找的效率比较高，但数据插入效率欠佳(聚集索引，非聚集索引，都得被维护一次)，并且主键列如果选择的不好，会影响其他非聚集索引的性能。

ORACLE中定义主键时，默认生成非聚集索引，不利于主键列的范围扫描/查找，但是对于数据插入效率更佳，这是不同数据库产品各自的权衡。

(2) Foreign  Key约束

    特征：

     a、一旦Foreign Key约束定义了某个字段，则该字段的取值必须参照(Reference)同一表或另一表中的Primary Key约束或Unique约束。

     b、Foreign Key约束不能自动建立索引。

create table Result(
    id int not null constraint pk_no references Student(id),
    rno int
)

create table tb3
(
c1 int,
c2 int,
c3 int, 
constraint PK_tb3 primary key (c1,c2)
);
                                                                                                                                       
create table tb4
(
c4 int constraint FK_tb4 foreign key references tb3(c1),
c5 int,
c6 int
);

最后，虽然一个表上可以创建多个外键，但通常出于性能考虑，不推荐使用外键，数据参照完整性可以在程序里完成

(3) default 约束

  特征：

      a.每一个字段只能有一个Default约束；

      b.default约束不能放在IDENTITY字段上或者timestamp字段上。

　声明：

　　默认约束创建默认值不仅可以使一个常数，也可以是函数及表达式及case表达式

 

create table #testdc(
    c0 int default 0,
    c1 int default case when suser_sname()='JOE' then 1 else 0 end
)
insert into #testdc default values
select * from #testdc

 

 

 

(4)Unique约束(用于不是主键但又要求不能有重复值出现的字段)   

　特征：

    a.一个表可以有多个Unique 约束；

    b. 按照Unique约束的要求，在一个表中不允许受约束列的字段上有相同的null值，因此最好将被定义了Unique约束的列定义为非空（允许被约束列的值为空）

    c.创建Unique约束时，系统自动创建了非聚集索引。

　d.唯一约束与主键约束的唯一区别在于，唯一约束允许NULL，SQL Server 中唯一键列，仅可以有一行为NULL

　　alter table Student add constraint un_name unique(name)

(5)Check约束

     特征：

     a.限制了向特定的字段列输入数据的类型；

     b.表级定义的Check约束可以对多个字段列进行核查。

create table efg(
    id bigint not null,
    name varchar(50) not null,
    age int,
    constraint chk_age check(age>17 and age<30)
)

(6)删除约束

　　如果需要将某个约束删除，可以使用alter table约束，其语法格式是：

    　　 alter table 表名

      　　drop constraint 约束名

约束与索引的关系

　　定义约束时，并没有定义数据库实现约束的方法，目前的关系型数据库系统，主键和唯一键约束借助唯一索引来实现，所以在创建主键/唯一键时，都会自动生成一个同名的索引。

　　那么由约束产生的唯一索引，和单独创建的唯一索引有什么联系和区别？

1.创建主键或唯一键约束时，数据库自动创建唯一索引

　　自动生成的该索引是无法删除的，因为这个索引要用于实现约束，在删除约束的时候，该索引也被删除。演示脚本如下：

--create table
CREATE TABLE TEST_CONS
(
ID             int,
CODE           varchar(100)
)
--insert data
INSERT INTO TEST_CONS
SELECT 1,'test1'
--add unique constraint
ALTER TABLE TEST_CONS
  ADD CONSTRAINT UQ_TEST_CONS_ID UNIQUE NONCLUSTERED(ID)
--retrieve constraint
SELECT *
  FROM sys.objects
 WHERE parent_object_id = object_id('TEST_CONS') AND type = 'UQ'
--查看约束，返回如下结果：
/*
name    object_id
UQ_TEST_CONS_ID 1243151474
*/
--retrieve index
SELECT *
  FROM sys.indexes
 WHERE object_id = object_id('TEST_CONS') AND type = 2  --2为非聚集索引
--查看约束产生的索引，返回如下结果：
/*
object_id   name
1227151417  UQ_TEST_CONS_ID
*/
--check constraint
INSERT INTO TEST_CONS
SELECT 1,'test1'
--如果插入重复值提示：UNIQUE KEY 约束，返回如下错误：
/*
消息，级别，状态，第行
违反了UNIQUE KEY 约束'UQ_TEST_CONS_ID'。不能在对象'dbo.TEST_CONS' 中插入重复键。
*/
 --drop index
 DROP INDEX UQ_TEST_CONS_ID ON TEST_CONS
--如果删除由约束产生的索引，返回如下错误：
/*
消息，级别，状态，第行
不允许对索引'TEST_CONS.UQ_TEST_CONS_ID' 显式地使用DROP INDEX。该索引正用于UNIQUE KEY 约束的强制执行。
*/
 --drop constraint
 ALTER TABLE TEST_CONS
  DROP CONSTRAINT UQ_TEST_CONS_ID
--如果删除约束，索引也被删除，以下查询返回空结果集：
--retrieve constraint
SELECT *
  FROM sys.objects
 WHERE parent_object_id = object_id('TEST_CONS') AND type = 'UQ'
--retrieve index
SELECT *
  FROM sys.indexes
 WHERE object_id = object_id('TEST_CONS') AND type = 2  --2为非聚集索引
--drop table
DROP TABLE TEST_CONS

另外，约束生成的索引，有些属性也是无法被修改的，比如：开关IGNORE_DUP_KEY，唯一的办法是：先删除约束，再重新定义约束/索引；单独定义的索引，则没有这个限制

在保证数据唯一性上，唯一索引、唯一约束并没有区别，那么应该使用约束还是索引？

　　约束定义通常出现在数据库逻辑结构设计阶段，即定义表结构时，索引定义通常出现在数据库物理结构设计/查询优化阶段。

　　从功能上来说唯一约束和唯一索引没有区别，但在数据库维护上则不太一样，对于唯一约束可以用唯一索引代替，以方便维护，但是主键约束则没法代替。

2. 先创建唯一索引，再创建该索引字段的唯一约束

这时数据库并不会使用已存在的唯一索引，此时会提示已存在同名索引，约束创建失败，如果指定不同名的约束，则会生成另个唯一索引。演示脚本如下：

--create table
CREATE TABLE TEST_CONS
(
ID             int,
CODE           varchar(100)
)
--insert data
INSERT INTO TEST_CONS
SELECT 1,'test1'
--create index
CREATE UNIQUE INDEX UQ_TEST_CONS_ID
ON TEST_CONS(ID)
--retrieve constraint
SELECT *
  FROM sys.objects
 WHERE parent_object_id = object_id('TEST_CONS') AND type = 'UQ'
                                                                                              
--retrieve index
SELECT *
  FROM sys.indexes
 WHERE object_id = object_id('TEST_CONS') AND type = 2  --2为非聚集索引
--check index
INSERT INTO TEST_CONS
SELECT 1,'test1'
--此时提示为：唯一索引
/*
消息2601，级别14，状态1，第1 行
不能在具有唯一索引'UQ_TEST_CONS_ID' 的对象'dbo.TEST_CONS' 中插入重复键的行。
*/
--add constraint
ALTER TABLE TEST_CONS
  ADD CONSTRAINT UQ_TEST_CONS_ID UNIQUE NONCLUSTERED(ID)
--此时无法创建与索引同名的唯一约束，因为约束会去生成同名的索引
/*
消息1913，级别16，状态1，第2 行
操作失败，因为在表'TEST_CONS' 上已存在名称为'UQ_TEST_CONS_ID' 的索引或统计信息。
消息1750，级别16，状态0，第2 行
无法创建约束。请参阅前面的错误消息。
*/
--add constraint
ALTER TABLE TEST_CONS
  ADD CONSTRAINT UQ_TEST_CONS_ID_1 UNIQUE NONCLUSTERED(ID)
--换个名字当然是可以成功的，但此时又生成了唯一索引UQ_TEST_CONS_ID_1
--drop table
DROP TABLE TEST_CONS

 索引的注意事项

不能将索引建立在位型(bit)或者BLOB型(text,ntext,image)数据结构的索引上。
　　为什么不能建立在位型上？--索引的目的在表中定位一行数据。sqlserver使用表中潜在数据的不同值创建了平衡树(B树)
　　那么如果一个列只有2个不同的值，那么实际上在定位上就没意义了，即使表中有上E的数据，那也只有2个值。
禁止在计算列上创建索引

　　因为计算列在数据库中不是真实存在的，并不存储真正的数据。计算列类似于一个视图，只有在查询时存在，其它时间不存在