8.1 SQL Server索引简介

SQL Server Indexes（索引）

目录

• SQL Server Indexes（索引）
  • 简介
    • 作用
    • 定义
  • 索引分类
    • 聚集索引
    • 非聚集索引
    • 索引说明
  • 索引数据结构
    • 名词介绍
  • 索引为什么选择B+树
  • 索引设计原则
    • 是不是索引越多越好？
    • 什么情况下可以建立索引?
  • 使用索引
    • 创建索引
    • 查看索引
    • 重命名索引
      • 通过存储过程
      • 通过SQL Server Management Studio (SSMS)
    • 禁用索引
      • 简介
      • 示例
        • A）禁用一个索引示例
        • B）禁用表的所有索引
    • 启用和重建索引
      • 简介
      • 使用ALTER INDEX和CREATE INDEX语句
      • 使用DBCC DBREINDEX语句
      • 示例
    • 删除索引
      • 简介
      • 示例

简介

作用

提高查询速度。

定义

将结构化数据中的一部分信息提取出来，重新组织，使其变得有一定结构,我们将这部分信息称之为索引。

索引很类似一本字典里边的目录。

索引分类

聚集索引

聚集索引是一种索引，该索引中键值的逻辑顺序决定了表中相应行的物理顺序。聚集索引也称为聚簇索引（Clustered Index），聚集索引是物理地址连续存放的索引。

特点:只能有一个, 一般为主键（主键一定是聚集索引，聚集索引并不一定是主键）

什么情况下主键不是聚集索引呢？
在建表的时候，并没有加主键，这个时候如果说建立了一个聚集索引，再建立主键，那么这个时候
主键就不是聚集索引了。

非聚集索引

非聚集索引（NonClustered Index）是表中记录的物理顺序和逻辑顺序不同的索引 (此外还有空间索
引、筛选索引、XML索引)

特点:可以有多个(999)

索引说明

• 每张表上最大的聚集索引数为1；
• 每张表上最大的非聚集索引数为999；
• 每个索引最多能包含的键列数为16；
• 索引键记录大小最多为900字节

索引数据结构

在 SQL Server 数据库中，索引的存储是以B+树（注意和二叉树的区别）结构来存储的，又称索引
树，其节点类型为如下两种：

• 索引节点（Key）；
• 叶子节点( Key + Value)

索引节点按照层级关系，有时又可以分为根节点和中间节点，其本质是一样的，都只包含下一层节
点的入口值和入口指针；

叶子节点就不同了，它包含数据，这个数据可能是表中真实的数据行，也有可能是索引列值和行书
签，前者对应于聚集索引，后者对应于非聚集索引。

名词介绍

B+Tree：一种数据结构( 也是一种多路平衡搜索树 )
数据页：数据库保存数据的最小单位。(SQL Server一个数据页的大小是8K，一个表中所有的数据都被保存到一个个的数据页中)
索引组织表：大白话一张表有聚集索引就是索引组织表（把表中的数据页以 B+Tree 的方式组织起来）
索引表：一个索引对应一张索引表，索引表中每条数据都对应一张数据页。

索引为什么选择B+树

• B+树的磁盘读写代价更低：B+树的内部节点并没有指向关键字具体信息的指针，因此其内部节点相对B树更小，如果把所有同一内部节点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多，一次性读入内存的需要查找的关键字也就越多，相对IO读写次数就降低了。
• B+树的查询效率更加稳定：由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键
  字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。
• 由于B+树的数据都存储在叶子结点中，分支结点均为索引，方便扫库，只需要扫一遍叶子结点即
  可，但是B树因为其分支结点同样存储着数据，我们要找到具体的数据，需要进行一次中序遍历按序来扫，所以B+树更加适合在区间查询的情况，所以通常B+树用于数据库索引。

数据库索引采用B+树的主要原因是：B树在提高了IO性能的同时并没有解决元素遍历效率低下
的问题，正是为了解决这个问题，B+树应用而生。B+树只需要去遍历叶子节点就可以实现整棵树的遍
历。而且在数据库中基于范围的查询是非常频繁的，而B树不支持这样的操作或者说效率太低。

索引设计原则

是不是索引越多越好？

肯定不行。

• 索引也是需要空间存储，索引太多意味着占用的空间也越多。
• 索引页也需要系统维护，在增、删、改 数据时索引需要重新编排。就好像一本书某一页被撕掉了，对应的目录也需要重新进行编排。
• 索引堆积久了，由于维护数据过程中会产生过多的索引碎片，反而不利于数据查询。

什么情况下可以建立索引?

1. 主键一定要建
2. 外键一定要
3. 经常查询的列
4. 经常用作查询条件的列
5. 经常用在order by ,group by, distinct 后面的列
6. 重复值比较多的列不能建立索引
7. 对于 text,image,bit 这些类型的字段不能建立索引
8. 经常存取的列不要建立索引

使用索引

创建索引

语法：

create [unique] [clustered / nonclustered]
index index_name
on table_name(column_name1, column_name2, …)

unique：唯一索引
clustered：聚集索引
nonclustered ：非聚集索引
index_name：索引名称

-- 建立聚集索引
create clustered index idx_userinfo_Id on UserInfo(Id);
-- 创建非聚集索引(nonclustered 可省略)
create nonclustered index idx_userinfo_Account on UserInfo(Account);
-- 创建唯一非聚集索引
create unique nonclustered index idx_userinfo_pwd on UserInfo(Pwd);

唯一特点:索引字段必须唯一,但可以有一个值为NULL

查看索引

 exec sp_helpindex 'dbo.UserInfo'

重命名索引

通过存储过程

sp_rename是一个系统存储过程，允许您重命名当前数据库中任何用户创建的对象，包括表、索引和列。
重命名索引的语法：

EXEC sp_rename 
    index_name, 
    new_index_name, 
    N'INDEX';  

或者可以使用显式参数：

EXEC sp_rename 
    @objname = N'index_name', 
    @newname = N'new_index_name',   
    @objtype = N'INDEX';

比如，下面将表sales.customers的索引ix_customers_city改名为ix_cust_city:

EXEC sp_rename 
        @objname = N'sales.customers.ix_customers_city',
        @newname = N'ix_cust_city' ,
        @objtype = N'INDEX';

简写：

EXEC sp_rename 
        N'sales.customers.ix_customers_city',
        N'ix_cust_city' ,
        N'INDEX';

通过SQL Server Management Studio (SSMS)

首先，找到对应的数据库、表名和索引：
右单要改名的索引，然后选择rename。在下图中，我们将重命名索引ix_customers_name:

键入新名称并按enter键。下图显示了ix_customers_name索引更改为ix_cast_fullname：

禁用索引

简介

语法：
禁用一个索引：

ALTER INDEX index_name
ON table_name
DISABLE;

禁用表的全部索引：

ALTER INDEX ALL ON table_name
DISABLE;

查询优化器不会使用禁用的索引来查询。

禁用表上的索引时，SQL Server会在元数据中保留索引定义，在非聚集索引中保留索引统计信息。但是，如果在视图上禁用非聚集索引或聚集索引，SQL Server将物理删除所有索引数据。

如果禁用表的聚集索引，则在重新生成或删除索引之前，无法使用数据操作语言（如SELECT、INSERT、UPDATE和DELETE）访问表数据。

示例

让我们举一些禁用索引的例子，以便更好地理解。

A）禁用一个索引示例

使用ALTER INDEX禁用sales.customers上的ix_cust_city索引：

ALTER INDEX ix_cust_city 
ON sales.customers 
DISABLE;

因此，以下查找位于圣何塞的客户的查询无法利用禁用索引：

SELECT    
    first_name, 
    last_name, 
    city
FROM    
    sales.customers
WHERE 
    city = 'San Jose';

B）禁用表的所有索引

禁用sales.customers表的所有索引：

ALTER INDEX ALL ON sales.customers
DISABLE;

因此，无法再访问表中的数据：

SELECT * FROM sales.customers;

输出：

The query processor is unable to produce a plan because the index 
'PK__customer__CD65CB855363011F' on table or view 'customers' is disabled.

启用和重建索引

简介

有时，在对表进行大型更新之前，需要禁用索引。通过禁用索引，可以通过避免索引写入开销来加快更新过程。

完成对表的更新后，需要启用索引。由于索引已禁用，因此可以重新生成索引，但不能简单地启用它。因为在更新操作之后，需要重建索引以反映表中的新数据。

在SQL Server中，可以使用ALTER INDEX语句或DBCC DBREINDEX命令重建索引。

使用ALTER INDEX和CREATE INDEX语句

使用ALTER INDEX语句在表上重建索引：

ALTER INDEX index_name 
ON table_name  
REBUILD;

使用CREATE INDEX语句启用已被禁用的索引并重新创建它：

CREATE INDEX index_name 
ON table_name(column_list)
WITH(DROP_EXISTING=ON)

以下语句使用ALTER INDEX语句启用表上所有禁用的索引：

ALTER INDEX ALL ON table_name
REBUILD;

使用DBCC DBREINDEX语句

启用表中的一个索引：

DBCC DBREINDEX (table_name, index_name);

启用表中的所有索引：

DBCC DBREINDEX (table_name, " ");

示例

启用表sales.customers中的所有索引：

ALTER INDEX ALL ON sales.customers
REBUILD;

删除索引

简介

DROP INDEX语句从当前数据库中删除一个或多个索引。以下是DROP INDEX语句的语法：

DROP INDEX [IF EXISTS] index_name
ON table_name;

删除不存在的索引会报错。但是，可以使用IF EXISTS有条件地删除索引并避免报错。

请注意，IF EXISTS只在SQL Server 2016（13.x）以后的版本可用。

DROP INDEX不会删除由PRIMARY KEY或UNIQUE约束创建的索引。要删除与这些约束关联的索引，可以使用ALTER TABLE DROP CONSTRAINT语句删除约束。

同时从一个或多个表中删除多个索引的语法如下：

DROP INDEX [IF EXISTS] 
    index_name1 ON table_name1,
    index_name2 ON table_name2,
    ...;

示例

A）删除单个索引示例

DROP INDEX IF EXISTS ix_cust_email
ON sales.customers;

B）删除多个索引示例

DROP INDEX 
    ix_cust_city ON sales.customers,
    ix_cust_fullname ON sales.customers;