讲义11：索引

--上课内容：第六章 视图及索引-- 6.2 索引的创建和使用 -- 6.2.1 索引的概念：索引是一种特殊类型的数据库对象，它保存着数据表中一列或几列组合的排序结构  -- 优点：   -- 1. 加快数据检索的速度   -- 2. 提高系统的性能    -- 3. 加速表与表之间的连接  -- 缺点：维护索引要占用磁盘空间，降低添加、删除和更新行的速度  -- 数据的存储方法：   -- 1. 数据行存储在数据页中，数据存储的基本单位是页，其大小是8KB 八个邻近的页称为一个扩展盘区   -- 2. 八种页类型：数据、索引、文本/图像、全局分配映射表与辅助全局分配映射表、页的可用空间、索引分配映射表、大容量更改映射表、差异更改映射表。  -- 数据的访问方法：两种方法   -- 1. 扫描表中所有的数据页———— 称为表扫描        -- 开始于表的起点，  一页页地扫描表中的所有行，   提取符合查询标准的行   -- 2. 使用指向页上数据的索引        -- 遍历索引树结构，找到查询所请求的列， 只提取符合查询标准的列  -- 创建索引的原则：   -- 1. 创建索引的字段要尽可能的小，可以获得较好的性能   -- 2. 在创建主键、唯一约束时自动创建唯一聚集索引、唯一索引，在创建聚集索引时，非聚集索引都会被重建   -- 3. 建议        -- 外键列可以建索引        -- 在经常查询的字段上最好建立索引        -- 对于那些查询中很少涉及的列、重复值比较多的列不要建索引        -- 对于定义为text，Image和Bit数据类型的列上不要建立索引  -- 分类：聚集索引和非聚集索引   -- 聚集索引：CLUSTERED  通过物理上的顺序进行排序而建立的索引     -- 一个表只能创建一个聚集索引     -- 聚集索引改变了行的物理存储顺序     -- 创建任何非聚集索引之前，必须先创建一个聚集索引   -- 非聚集索引：NONCLUSTERED  通过索引表（相当于书的目录）的方法建立的索引     -- 一个表可以创建多个非聚集索引     -- 非聚集索引不改变了行的物理存储顺序   -- 唯一索引：     -- 一个表创建唯一索引的字段或字段的组合必须唯一     -- 索引字段取值必须唯一   -- 复合索引：     -- 对多个字段组合创建索引     -- 一个复合索引最多可以有16个字段组合，不超过900Byte     -- 复合索引的字段必须在同一个表里 -- 6.2.2 在查询分析器里用Transact SQL语句 创建索引  -- 6.2.2.1 为已经存在的表创建索引    create table gamer(g_id int,g_name varchar(10),g_gender char(2),g_jf int)    insert into gamer values(1,'aaa1','男',200)    insert into gamer values(2,'aaa2','男',800)    insert into gamer values(3,'aaa3','男',2000)    insert into gamer values(4,'aaa4','男',1200)    insert into gamer values(6,'aaa6','女',8700)    insert into gamer values(5,'aaa5','男',5600)    insert into gamer values(11,'aaa7','女',1000)    select * from gamer   -- 语法：CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name     ON <object> ( column [ ASC | DESC ] [ ,...n ] )     [ INCLUDE ( column_name [ ,...n ] ) ]    [ WHERE <filter_predicate> ]    [ WITH ( <relational_index_option> [ ,...n ] ) ]    [ ON { partition_scheme_name ( column_name )          | filegroup_name          | default          }    ]    [ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ][ ; ]<object> ::={    [ database_name. [ schema_name ] . | schema_name. ]     table_or_view_name}<relational_index_option> ::={    PAD_INDEX = { ON | OFF }  | FILLFACTOR = fillfactor  | SORT_IN_TEMPDB = { ON | OFF }  | IGNORE_DUP_KEY = { ON | OFF }  | STATISTICS_NORECOMPUTE = { ON | OFF }  | STATISTICS_INCREMENTAL = { ON | OFF }  | DROP_EXISTING = { ON | OFF }  | ONLINE = { ON | OFF }  | ALLOW_ROW_LOCKS = { ON | OFF }  | ALLOW_PAGE_LOCKS = { ON | OFF }  | MAXDOP = max_degree_of_parallelism  | DATA_COMPRESSION = { NONE | ROW | PAGE}      [ ON PARTITIONS ( { <partition_number_expression> | <range> }      [ , ...n ] ) ]}CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name   ON <object> ( column [ ASC | DESC ] [ ,...n ] ) [WITH] [PAD_INDEX= { ON | OFF }],              使用由FILLFACTOR所指定的百分比[FILLFACTOR=80],                        每个索引页的页级别中可用空间的百分比  [STATISTICS_NORECOMPUTE= { ON | OFF }]  指定过期的索引统计不会自动重新计算[,...,n ]   -- 例: 创建一个唯一聚集索引——会改变表的物理存储位置    insert into gamer values(7,'aaa7','女',1000)    create UNIQUE clustered  INDEX  in_g1 on gamer(g_id)    insert into gamer values(8,'aaa7','女',1000)   -- 例: 创建一个唯一索引    create UNIQUE INDEX in_g2 on gamer(g_id)   -- 例: 创建一个复合索引    create INDEX in_g3 on gamer(g_id,g_name)   -- 例: 创建一个索引,指定过期的索引统计不自动重新计算    create INDEX in_g4 on gamer(g_id) WITH STATISTICS_NORECOMPUTE  -- 6.2.2.2 在创建表时创建索引   -- 例: 创建一个表、同时唯一聚集索引    create table gamer1(g_id int unique clustered,g_name varchar(10),g_jf int)     sp_helpindex gamer1   -- 例: 创建一个表，同时创建主键，系统自动创建唯一聚集索引    create table gamer2(g_id int primary key,g_name varchar(10),g_jf int)    sp_helpindex gamer2   -- 例: 创建一个表、同时创建唯一索引    create table gamer3(g_id int unique,g_name varchar(10),g_jf int)     sp_helpindex gamer3    insert into gamer3 values(8,'aaa8',1000)    insert into gamer3 values(6,'aaa6',2000)    select * from gamer3  ——说明其它索引是不改变物理存储结构的   -- 例: 创建一个表、同时创建唯一约束，系统自动创建唯一索引    create table gamer4(g_id int,g_name varchar(10),g_jf int constraint uk_id unique(g_id))     sp_helpindex gamer4   --例：创建一个表，为g_id 字段创建索引    create table gamer5(g_id int,g_name varchar(10),g_jf int)     create index IX_gamer5_gid on gamer5(g_id)    sp_helpindex gamer5    insert into gamer5 values(8,'aaa8',1000)    insert into gamer5 values(6,'aaa6',2000)  -- 6.2.2.3 查看索引相关信息   sp_helpindex 索引名所属的表名    -- 例：查看gamer表的索引   sp_helpindex gamer   -- 6.2.3 重命名索引:  sp_rename '表名.旧索引名','新索引名','index'   -- 例：gamer4表的uk_id索引改名为uk_gid   sp_rename 'gamer4.uk_id','uk_gid','index'   sp_helpindex gamer4  -- 6.2.4 删除索引:   DROP INDEX 表名.索引名    -- 例：删除gamer4表的uk_gid索引   drop INDEX gamer4.uk_gid       -- 说明在设置了主键或唯一约束的时候，不能删除相应索引   sp_helpindex gamer   drop INDEX gamer.in_g       --打开：SET STATISTICS TIME ON    --关闭：SET STATISTICS TIME OFF