-用法总结
下面的表总结了何时使用聚集索引或非聚集索引（很重要）：
动作描述 | 使用聚集索引 | 使用非聚集索引 |
列经常被分组排序 | 应 | 应 |
返回某范围内的数据 | 应 | 不应--物理顺序不同 |
一个或极少不同值 | 不应 | 不应--selectivity小 |
小数目的不同值 | 应 | 不应 |
大数目的不同值 | 不应 | 应 |
频繁更新的列 | 不应 | 应 |
外键列 | 应 | 应 |
主键列 | 应 | 应 |
频繁修改索引列 | 不应 | 应 |
--聚集索引vs非聚集索引
- 聚簇索引是行的物理顺序和索引的顺序是一致的。页级，低层等索引的各个级别上都包含实际的数据页。一个表只能是有一个聚簇索引。由于 update,delete语句要求相对多一些的读操作，因此聚簇索引常常能加速这样的操作。在至少有一个索引的表中，你应该有一个聚簇索引。
在下面的几个情况下，你可以考虑用聚簇索引：
- 例如： 某列包括的不同值的个数是有限的（但是不是极少的）顾客表的州名列有50个左右的不同州名的缩写值，可以使用聚簇索引。
- 例如： 对返回一定范围内值的列可以使用聚簇索引，比如用between,>,>=,<,<=等等来对列进行操作的列上。select * from sales where ord_date between ’5/1/93’ and ’6/1/93’
- 例如： 对查询时返回大量结果的列可以使用聚簇索引。SELECT * FROM phonebook WHERE last_name = ’Smith’
当有大量的行正在被插入表中时，要避免在本表一个自然增长（例如，identity列）的列上建立聚簇索引。如果你建立了聚簇的索引，那么insert的性能就会大大降低。因为每一个插入的行必须到表的最后，表的最后一个数据页。当一个数据正在被插入（这时这个数据页是被锁定的），所有的其他插入行必须等待直到当前的插入已经结束。一个索引的叶级页中包括实际的数据页，并且在硬盘上的数据页的次序是跟聚簇索引的逻辑次序一样的。
当有大量的行正在被插入表中时，要避免在本表一个自然增长（例如，identity列）的列上建立聚簇索引。如果你建立了聚簇的索引，那么insert的性能就会大大降低。因为每一个插入的行必须到表的最后，表的最后一个数据页。当一个数据正在被插入（这时这个数据页是被锁定的），所有的其他插入行必须等待直到当前的插入已经结束。一个索引的叶级页中包括实际的数据页，并且在硬盘上的数据页的次序是跟聚簇索引的逻辑次序一样的
一个非聚簇的索引就是行的物理次序与索引的次序是不同的。一个非聚簇索引的叶级包含了指向行数据页的指针。在一个表中可以有多个非聚簇索引，你可以在以下几个情况下考虑使用非聚簇索引。在有很多不同值的列上可以考虑使用非聚簇索引
例如：一个part_id列在一个part表中 select * from employee where emp_id = ’pcm9809f’
例如：查询语句中用order by 子句的列上可以考虑使用
- 这里有一个比较关键的概念 Bookmark Lookup 可参看【揭秘SQL Server 2000中的Bookmark Lookup】
虽然聚集和非聚集索引结构相似,但是一个非聚簇的索引就是行的物理次序与索引的次序是不同的.聚集索引叶节点包含的是实际的值;非聚集索引有两种情况
1.对于堆表:该指针是指向行的指针
2.对于聚集索引表:该指针叫做行定位器Bookmark
SQL Server在查找数据时，服务器先使用和使用聚集索引相同的查找方法找到该索引的行定位器 Bookmark，然后通过行定位器来找到所需要的数据，这种通过行定位器查找数据的方式就是Bookmark Lookup；
这里注意不是所有的在一个聚集表上使用非聚集索引进行查询,其性能低于在堆集上使用非聚集索引进行查询.因为当返回的字段包含了非聚集索引和聚集索引的列值，那么就会产生索引覆盖，而堆集上使用非聚集索引的返回字段只能是只身才会形成索引覆盖
3.索引覆盖:在基于非聚集索引查找数据时，还有另外一种情形，那就是如果放回的数据列就包含于索引的键值中，或者包含于索引的键值+聚集索引的键值中，那么就不会发生Bookup Lookup，因为找到索引项，就已经找到所需的数据了，没有必要再到数据行去找了。这种情况，叫做索引覆盖；请记住：无论您是否经常使用聚合索引的其他列，但其前导列一定要是使用最频繁的列。
可以看看这个例子【Sql Server Database Indexes and Execution Plans】 - 一个例子sp_spaceused 'order' 结果是3G大小谓词where date = '2009-12-10' 此时date字段上有非聚集索引,那么选择器将会1.自增列上建立聚集索引:对date字段上的非聚集索引扫描后，还需要去聚集索引树上seek一下 [子增列建立索引的问题]2.表没有聚集索引:去掉自增列上的主键聚集索引,此时表为堆,在非聚集索引扫描后直接就拿到ROWID(堆表的非聚集索引叶节点所存储的标 识所在行包括:FileID,pageID,SlotID)3.索引覆盖:将所有需要的字段都汇总到非聚集索引上比如
•
select
a,b
from
table
where
c;
--
2000中索引覆盖为
create
index
idx
on
t(c,a,b)
--
2005中索引覆盖为
create
index
idx
on
t(c) include (a,b)
通过扫描C键值所在的索引上层结构快速找到where条件所需的边界,然后扫描子叶层;循环扫描到a,b的记录位置
--
??我觉得这里有一个可以测试的地方就是到底是索引覆盖还是date字段上建立聚集索引好,上一篇文章中有一个查询性能比较：
1
. 返回行数较多：索引覆盖
>
聚集索引
>
表扫描
>
堆集的非聚集索引
>
聚集的非聚集索引
2
. 返回行数较少：索引覆盖
=
聚集索引
>
堆集的非聚集索引
>
聚集的非聚集索引
>
表扫描
• 测试
--
SQL Server 2005 Performance Tuning性能调校 代码列表 6.14：通过各种索引，测试所花的 IO 页数.sql
USE
Credit
GO
EXEC
spCleanIdx
'
Charge
'
--
要求返回 IO 的统计，也就是分页访问的数目
SET
STATISTICS
IO
ON
--
没有索引的页数
--
表 'charge'。扫描计数 1，逻辑读取 584，实际读取 0，读取前读取 0，LOB 逻辑读取 0，LOB 实际读取 0，LOB 读取前读取 0。
SELECT
charge_no
FROM
charge
WHERE
charge_amt
BETWEEN
20
AND
3000
--
通过聚簇索引查询的页数
--
表 'charge'。扫描计数 1，逻辑读取 419，实际读取 0，读取前读取 14，LOB 逻辑读取 0，LOB 实际读取 0，LOB 读取前读取 0。
CREATE
CLUSTERED
INDEX
ix_charge_amt
ON
Charge(charge_amt)
SELECT
charge_no
FROM
charge
WHERE
charge_amt
BETWEEN
20
AND
3000
DROP
INDEX
Charge.ix_charge_amt
--
强制通过非聚簇索引查询的页数,用错索引比不用索引糟糕很多倍
CREATE
INDEX
ix_charge_amt
ON
Charge(charge_amt)
--
表 'charge'。扫描计数 5，逻辑读取 60198，实际读取 0，读取前读取 3，LOB 逻辑读取 0，LOB 实际读取 0，LOB 读取前读取 0。
--
表 'Worktable'。扫描计数 0，逻辑读取 0，实际读取 0，读取前读取 0，LOB 逻辑读取 0，LOB 实际读取 0，LOB 读取前读取 0。
SELECT
charge_no
FROM
charge
WITH
(
INDEX
(ix_charge_amt))
WHERE
charge_amt
BETWEEN
20
AND
3000
DROP
INDEX
Charge.ix_charge_amt
--
通过字段顺序不适用的覆盖索引查询的页数
CREATE
INDEX
ix_charge_amt
ON
Charge(charge_no,charge_amt)
--
表 'charge'。扫描计数 1，逻辑读取 292，实际读取 0，读取前读取 0，LOB 逻辑读取 0，LOB 实际读取 0，LOB 读取前读取 0。
SELECT
charge_no
FROM
charge
WHERE
charge_amt
BETWEEN
20
AND
3000
DROP
INDEX
Charge.ix_charge_amt
--
通过覆盖索引查询的页数
CREATE
INDEX
ix_charge_amt
ON
Charge(charge_amt,charge_no)
--
表 'charge'。扫描计数 1，逻辑读取 175，实际读取 0，读取前读取 0，LOB 逻辑读取 0，LOB 实际读取 0，LOB 读取前读取 0。
SELECT
charge_no
FROM
charge
WHERE
charge_amt
BETWEEN
20
AND
3000
DROP
INDEX
Charge.ix_charge_amt
--
通过字段顺序不适用的覆盖索引查询的页数
CREATE
INDEX
ix_charge_amt
ON
Charge(charge_no) INCLUDE(charge_amt)
--
表 'charge'。扫描计数 1，逻辑读取 290，实际读取 0，读取前读取 0，LOB 逻辑读取 0，LOB 实际读取 0，LOB 读取前读取 0。
SELECT
charge_no
FROM
charge
WHERE
charge_amt
BETWEEN
20
AND
3000
DROP
INDEX
Charge.ix_charge_amt
--
透过子叶层覆盖索引查询的页数
CREATE
INDEX
ix_charge_amt
ON
Charge(charge_amt) INCLUDE(Charge_no)
--
表 'charge'。扫描计数 1，逻辑读取 174，实际读取 0，读取前读取 0，LOB 逻辑读取 0，LOB 实际读取 0，LOB 读取前读取 0。
SELECT
charge_no
FROM
charge
WHERE
charge_amt
BETWEEN
20
AND
3000
DROP
INDEX
Charge.ix_charge_amt
• 上边文章里还有些重点
• 一个堆集在sysindexes内有一行，其indid=0；
• 某个表和视图的聚集索引在sysindexes内有一行，其indid=1，root列指向聚集索引B树的顶端；
• 某个表或视图的非聚集索引在索引在sysindexes内也有一行，其indid值从2到250，root列指向非聚集索引B树的顶端；
• SQL Server 的数据文件中有一类是IAM，即索引分配映射表，它存储有关表和索引所使用的扩展盘区信息；
• Bookmark Lookup逻辑运算符和物理运算符使用书签（行 ID 或聚集键）在表或聚集索引内查找相应的行；
--是否值得建索引
无论在哪个数据库里都会有这样的疑问,但是这里永远有三个标准帮助我们来选择,他们是selectivity,density,distribution
• selectivity:首先要看需要建索引列的选择性,例如
select
*
from
test
where
id
=
1
--
假定select count(*) from test 是10000 那么这个的选择性就是 1/10000,选择性很高,适合建立索引
select
*
from
test
where
id
>
1
--
假定select count(*) from test 是10000 那么这个的选择性就是 9999/10000,选择性很低,不适合建立索引
除非在id字段是聚集索引,如果采用非聚集索引,反而变成需要读至少9999页以上,因为每读取一条记录时都要将整页读出,再从中取出目标记录,就算数据记录在同一页上也要读多次
• density:密度指键值唯一的记录条数分之一
select
1
/
(
select
count
(
distinct
id)
from
test)
--
当结果越小也就是唯一性越高,就越合适建立索引,也可以使用以下方法检测看传回的All Density值
Create
index
idx_id
on
test(id)
DBCC
Show_Statistics(test,idx_id)
• distribution:一个范围之内的记录条数,或者某个分区的记录条数
• 看看是否建多了索引
select * from sys.dm_db_index_usage_stats where object_id=object_id('table_name')• 一些分析索引缺失的视图 (SQL Server 2005 Performance Tuning性能调校(含光盘) P300)
•
select
*
from
sys.dm_db_missing_index_groups
select
*
from
sys.dm_db_missing_index_group_stats
select
*
from
sys.dm_db_missing_index_details
SELECT
mig.
*
, statement
AS
table_name,
column_id, column_name, column_usage
FROM
sys.dm_db_missing_index_details
AS
mid
CROSS
APPLY sys.dm_db_missing_index_columns (mid.index_handle)
INNER
JOIN
sys.dm_db_missing_index_groups
AS
mig
ON
mig.index_handle
=
mid.index_handle
ORDER
BY
mig.index_group_handle, mig.index_handle, column_id;
通过动态管理对象sys.dm_db_missing_index_details和sys.dm_db_missing_index_columns函数返回的结果呈现所需索引键数据行是相等(Equality),不相等(Inequality)或包容(Include)
sys.dm_db_missing_index_details视图会在Equality_Columns,Inequality_Columns或Include_Columns等行返回这些信息
sys.dm_db_missing_index_columns函数会在其column_usage数据行中返回此信息
所以最后的规则就是将Equality_Columns放在最前边,Inequality_Columns随后,然后把Include_Columns放到Include子句中
create index idx_test on test(Equality_Columns,Inequality_Columns) include (Include_Columns_1,Include_Columns_2)
--一些测试(这个是我看别人文章的总结,忘记出处了,抱歉)
1. 用聚合索引比用一般的主键作order by时，速度快了3/10。事实上，如果数据量很小的话，用聚集索引作为排序列要比使用非聚集索引速度快得明显的多；而数据量如果很大的话，如10万以上，则二者的速度差别不明显。
2. 时间搜索：使用聚合索引内的时间段，搜索时间会按数据占整个数据表的百分比成比例减少，而无论聚合索引使用了多少个
select
gid,fariqi,neibuyonghu,reader,title
from
Tgongwen
where
fariqi
>
''
2004
-
1
-
1
''
--用时：6343毫秒（提取100万条）
整年
select
gid,fariqi,neibuyonghu,reader,title
from
Tgongwen
where
fariqi
>
''
2004
-
6
-
6
''
--用时：3170毫秒（提取50万条）
半年
select
gid,fariqi,neibuyonghu,reader,title
from
Tgongwen
where
fariqi
=
''
2004
-
9
-
16
''
--用时：3326毫秒（和上句的结果一样.如果采集的数量一样,那么用大于号和等于号是一样的,和半年的数据量一样)
select
gid,fariqi,neibuyonghu,reader,title
from
Tgongwen
where
fariqi
>
''
2004
-
1
-
1
''
and
fariqi
<
''
2004
-
6
-
6
''
--用时：3280毫秒
半年
--得出以上速度的方法是：在各个select语句前加：
declare
@d
datetime
set
@d
=
getdate
()
--
SQL Query
select
[
语句执行花费时间(毫秒)
]
=
datediff
(ms,
@d
,
getdate
())
--碎片
• 读书笔记 - 高效维护数据库的关键技巧
--统计信息对索引使用的影响
• • 建立测试环境
•
•
• 建立测试环境
•
• USE
• Tempdb
• --
测试统计过期的结果
• SET
NOCOUNT
ON
• SET
STATISTICS
IO
OFF
• SET
STATISTICS
PROFILE
OFF
• CREATE
TABLE
tblTest(
UserId • INT
IDENTITY
(
1
,
1
)
PRIMARY
KEY
NONCLUSTERED
,
UserName • NVARCHAR
(
20
),
Gender • NCHAR
(
1
))
• --
一开始构造 100000 笔 '女' 一笔 '男' 的悬殊记录差异
• INSERT
tblTest
VALUES
(
'
Hello World
'
,
'
男
'
)
• DECLARE
@int
INT
• SET
@int
=
1
• WHILE
@int
<
100000
• BEGIN
• INSERT
tblTest
VALUES
(
'
Hello
'
+
CONVERT
(
NVARCHAR
,
@int
),
• --
CASE WHEN @int%2 = 0 THEN '男' ELSE '女' END
•
'
女
'
)
• SET
@int
=
@int
+
1
• END
•
•
• 执行计划建立,更新,删除命令
•
• --
• 执行计划建立,更新,删除命令
• ALTER
DATABASE
SET
• CREATE
STATISTICS
• DBCC
SHOW_STATISTICS
sp_help • '
et_order
'
• DBCC
SHOW_STATISTICS (
'
et_order
'
, idx_et_0);
• DROP
STATISTICS
sp_autostats
sp_createstats
• UPDATE
STATISTICS
• 统计数据的影响
--
此时建立索引所同时产生的统计会记录如此悬殊的比值
CREATE
INDEX
idxGender
ON
tblTest(Gender)
EXEC
sp_helpindex tblTest
--
没有单独的统计数据
EXEC
sp_helpstats tblTest
--
统计是正确的，索引合用于当下的查询
SET
STATISTICS
IO
ON
SELECT
*
FROM
tblTest
WHERE
Gender
=
'
男
'
--
强迫表扫描
SELECT
*
FROM
tblTest
WITH
(
INDEX
(
0
))
WHERE
Gender
=
'
男
'
SET
STATISTICS
IO
OFF
--
故意要求不要自动更新统计数据
--
EXEC sp_dboption 'Credit','Auto Update Statistics', { TRUE | FALSE} --针对整个表
EXEC
sp_autostats
'
tblTest
'
,
'
OFF
'
,idxGender
--
将记录改成 1:1
UPDATE
tblTest
SET
Gender
=
'
男
'
WHERE
UserID
%
2
=
0
SELECT
Gender,
COUNT
(
*
)
FROM
tblTest
GROUP
BY
Gender
--
比对一下用错索引时，两者的 I/O 差异
SET
STATISTICS
IO
ON
--
通过 SET STATISTICS PROFILE 输出的 Rows 和 EstimateRows
--
可以比较真实与估计的记录数差异
SET
STATISTICS
PROFILE
ON
SELECT
*
FROM
tblTest
WHERE
Gender
=
'
男
'
--
强迫表扫描
SELECT
*
FROM
tblTest
WITH
(
INDEX
(
0
))
WHERE
Gender
=
'
男
'
DBCC
SHOW_STATISTICS(tblTest,idxGender)
--
这个是建立在统计信息基础上的,上边把统计信息停止后,这个返回的结果是错误的
--
做完统计更新后，可以再试一次前述的范例
--
但要先清除旧的运行计划
UPDATE
STATISTICS
tblTest
DBCC
FREEPROCCACHE
--本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
