索引

• 创建表

-- 创建表
CREATE TABLE authors(
    auth_id int IDENTITY(1,1) NOT NULL,
    auth_name varchar(20) NOT NULL,
    auth_gender tinyint NOT NULL,
    auth_phone varchar(15) Null,
    auth_note varchar(100) Null
);

创建索引

• 【例1】在authors表中auth_phone列上创建一个名称为Idx_phone的唯一聚集索引，降序排列，填充因子为30%。

CREATE UNIQUE CLUSTERED INDEX Idx_phone
ON authors(auth_phone DESC)
WITH
FILLFACTOR = 30;

• 【例2】在authors表中auth_name和auth_gender列上创建一个名称为Idx_nameAndgender的唯一非聚集组合索引，升序排列，填充因子为10%

CREATE UNIQUE NONCLUSTERED INDEX Idx_nameAndgender
ON authors(auth_name,auth_gender)
WITH
FILLFACTOR = 10;

维护和管理索引

• 显示索引信息

【例3】使用存储过程查看数据库中member表中定义的索引信息

-- 显示索引信息
GO
exec sp_helpindex 'authors';

执行结果如图：

image.png

图中显示了authors表中的索引信息：

• index_name : 指定索引名称，这里创建了2个不同名称索引。

• index_description : 包含索引描述信息，唯一性约束、聚集索引等。

• index_keys : 包含了索引所在表中的列。

• 查看索引统计信息

  【例4】使用DBCC SHOW_STATISTICS命令来查看authors表中Idx_phone索引的统计信息。

-- 查看索引统计信息
DBCC SHOW_STATISTICS ('newdb.dbo.authors',Idx_phone);

执行结果如图：

image.png

图中统计信息包含3个部分：

• 统计标题信息：主要博爱阔表中的行数、统计抽样行数、索引列的平均长度等。

• 统计密度信息：主要包括索引列前缀集选择性、平均长度等信息。

• 统计直方图信息：即为宣誓直方图时的信息。

• 重命名索引

  【例5】将authors表中的索引名称idx_nameAndgender更改为multi_index

--重命名索引
GO
exec sp_rename 'authors.idx_nameAndgender','multi_index','index';

• 删除索引

  【例6】删除表authors中的索引multi_index，

--删除索引
GO
exec sp_helpindex 'authors'
DROP INDEX authors.multi_index
exec sp_helpindex 'authors'

---

问题：

1. 索引对数据库性能如此重要，该如何使用它？

为数据选择索引是一项复杂任务。
如果索引列较少，则需要的磁盘空间和维护开销都少。
如果在一个大表上创建了多种组合索引，文件就会膨胀的很快。
而另一方面，索引较多则可覆盖更多查询。可能需要试验若干不同的设计，才能找到最有效的索引。
可以添加修改和删除索引而不影响数据库架构或应用程序设计。
因此，应该尝试多个不同的索引，从而建立最优的索引。

2. 为什么要使用短索引？

对字符串类型的字段进行索引，如果可能应该指定一个前缀长度。
例如，如果有一个char(255)列，如果在前10个或30个字符内，多数值是唯一的，则不需要对整个列进行索引。
短索引不仅可以提高查询速度而且可以节省磁盘空间和减少I/O操作。