sqlserver 索引的一些总结(4)
来源:asp之家 时间:2012-08-21 11:03:31
聚集表非聚集索引
当表上存在聚集索引时,任何非聚集索引的叶节点不再是包含指针值,而是包含聚集索引的索引值。
以前面的T_Pet表为例,假设T_Pet使用animal列作为非聚集索引,那么它的索引表非聚集索引结构如下图所示:
图15索引表非聚集索引
通过上图,我们发现非聚集索引通过双向链表连接,而且叶节点包含索引表的索引值。
如果我们要查找animal = ‘Dog'的信息,首先我们遍历第一层索引,然后数据库判断Dog属于Cat范围的索引,接着遍历第二层索引,然后找到Dog索引获取其中的保存的索引值,然后根据索引值获取相应数据页中的数据。
接下来我们修改之前的employees表,首先我们删除之前的堆表非聚集索引,然后增加索引表的非聚集索引,具体SQL代码如下:
代码如下:
ALTER TABLE employees
DROP CONSTRAINT employees_pk
ALTER TABLE employees
ADD CONSTRAINT employees_pk PRIMARY KEY CLUSTERED (employee_id)
GO
SELECT * FROM employees
WHERE employee_id=29976
图16查询计划
索引的有效性
SQL Server每执行一个查询,首先要检查该查询是否存在执行计划,如果没有,则要生成一个执行计划,那么什么是执行计划呢?简单来说,它能帮助SQL Server制定一个最优的查询计划。(关于查询计划请参考这里)
下面我们将通过具体的例子说明SQL Server中索引的使用,首先我们定义一个表testIndex,它包含三个字段testIndex,bitValue和filler,具体的SQL代码如下:
代码如下:
-----------------------------------------------------------
---- Index Usefulness sample
-----------------------------------------------------------
CREATE TABLE testIndex
(
testIndex int identity(1,1) constraint PKtestIndex primary key,
bitValue bit,
filler char(2000) not null default (replicate('A',2000))
)
CREATE INDEX XtestIndex_bitValue on testIndex(bitValue)
GO
INSERT INTO testIndex(bitValue)
VALUES (0)
GO 20000 --runs current batch 20000 times.
INSERT INTO testIndex(bitValue)
VALUES (1)
GO 10 --puts 10 rows into table with value 1
接着我们查询表中bitValue = 0的数据行,而且表中bitValue = 0的数据有2000行。
代码如下:
SELECT *
FROM testIndex
WHERE bitValue = 0
图17查询计划
现在我们查询bitValue = 1的数据行。
SELECT *FROM testIndexWHERE bitValue = 1
图18查询计划
现在我们注意到对同一个表不同数据查询,居然执行截然不同的查询计划,这究竟是什么原因导致的呢?
我们可以通过使用DBCC SHOW_STATISTICS查看到表中索引的详细使用情况,具体SQL代码如下:
代码如下:
UPDATE STATISTICS dbo.testIndex
DBCC SHOW_STATISTICS('dbo.testIndex', 'XtestIndex_bitValue')
WITH HISTOGRAM
图19直方图
通过上面的直方图,我们知道SQL Server估计bitValue = 0数据行行有约19989行,而bitValue = 1估计约21;SQL Server优化器根据数据量估算值,采取不同的执行计划,从而到达最优的查询性能,由于bitValue = 0数据量大,SQL Server只能提供扫描聚集索引获取相应数据行,而bitValue = 1实际数据行只有10行,SQL Server首先通过键查找bitValue = 1的数据行,然后嵌套循环联接到聚集索引获得余下数据行。
总结 完整实例代码:
代码如下:
-- =============================================
-- Author: JKhuang
-- Create date: 04/20/2012
-- Description: Create sample for Clustered and
-- Nonclustered index.
-- =============================================
-----------------------------------------------------------
---- Create T_Pet table in tempdb with NONCLUSTERED INDEX.
-----------------------------------------------------------
USE tempdb
CREATE TABLE T_Pet
(
animal VARCHAR(20),
[name] VARCHAR(20),
sex CHAR(1),
age INT
)
CREATE UNIQUE NONCLUSTERED INDEX T_PetonAnimal1_NonClterIdx ON T_Pet (animal)
CREATE UNIQUE CLUSTERED INDEX T_PetonAnimal1_ClterIdx ON T_Pet (animal)
-----------------------------------------------------------
---- Insert data into data table.
-----------------------------------------------------------
DECLARE @i int
SET @i=0
WHILE(@i<1000000)
BEGIN
INSERT INTO T_Pet (
animal,
[name],
sex,
age
)
SELECT [dbo].random_string(11) animal,
[dbo].random_string(11) [name],
'F' sex,
cast(floor(rand()*5) as int) age
SET @i=@i+1
END
INSERT INTO T_Pet VALUES('Aardark', 'Hello', 'F', 1)
INSERT INTO T_Pet VALUES('Cat', 'Kitty', 'F', 2)
INSERT INTO T_Pet VALUES('Horse', 'Ma', 'F', 1)
INSERT INTO T_Pet VALUES('Turtles', 'SiSi', 'F', 4)
INSERT INTO T_Pet VALUES('Dog', 'Tomma', 'F', 2)
INSERT INTO T_Pet VALUES('Donkey', 'YoYo', 'F', 3)
SET STATISTICS PROFILE ON
SET STATISTICS TIME ON
SELECT animal, [name], sex, age
FROM T_Pet
WHERE animal = 'Cat'
SET STATISTICS PROFILE OFF
SET STATISTICS TIME OFF
-----------------------------------------------------------
---- Create employees table in tempdb.
-----------------------------------------------------------
CREATE TABLE employees (
employee_id NUMERIC NOT NULL,
first_name VARCHAR(1000) NOT NULL,
last_name VARCHAR(900) NOT NULL,
date_of_birth DATETIME ,
phone_number VARCHAR(1000) NOT NULL,
junk CHAR(1000) ,
--PK constraint defaults to clustered
CONSTRAINT employees_pk PRIMARY KEY (employee_id)
);
GO
-----------------------------------------------------------
---- Insert data into data table.
-----------------------------------------------------------
CREATE VIEW rand_helper AS SELECT RND=RAND();
GO
---- Generates random string function.
CREATE FUNCTION random_string (@maxlen int) RETURNS VARCHAR(255)
AS BEGIN
DECLARE @rv VARCHAR(255)
DECLARE @loop int
DECLARE @len int
SET @len = (SELECT CAST(rnd * (@maxlen-3) AS INT) +3
FROM rand_helper)
SET @rv = ''
SET @loop = 0
WHILE @loop < @len BEGIN
SET @rv = @rv
+ CHAR(CAST((SELECT rnd
FROM rand_helper) * 26 AS INT )+97)
IF @loop = 0 BEGIN
SET @rv = UPPER(@rv)
END
SET @loop = @loop +1;
END
RETURN @rv
END
GO
---- Generates random date function.
CREATE FUNCTION random_date (@mindaysago int, @maxdaysago int)
RETURNS VARCHAR(255)
AS BEGIN
DECLARE @rv datetime
SET @rv = (SELECT GetDate()
- rnd * (@maxdaysago-@mindaysago)
- @mindaysago
FROM rand_helper)
RETURN @rv
END
GO
---- Generates random int function.
CREATE FUNCTION random_int (@min int, @max int) RETURNS INT
AS BEGIN
DECLARE @rv INT
SET @rv = (SELECT rnd * (@max) + @min
FROM rand_helper)
RETURN @rv
END
GO
---- Inserts data into employees table.
WITH generator (n) as
(
select 1
union all
select n + 1 from generator
where N < 30000
)
INSERT INTO employees (employee_id
, first_name, last_name
, date_of_birth, phone_number, junk)
select n employee_id
, [dbo].random_string(11) first_name
, [dbo].random_string(11) last_name
, [dbo].random_date(20*365, 60*365) dob
, 'N/A' phone
, 'junk' junk
from generator
OPTION (MAXRECURSION 30000)
-----------------------------------------------------------
---- Index Usefulness sample
-----------------------------------------------------------
CREATE TABLE testIndex
(
testIndex int identity(1,1) constraint PKtestIndex primary key,
bitValue bit,
filler char(2000) not null default (replicate('A',2000))
)
CREATE INDEX XtestIndex_bitValue on testIndex(bitValue)
GO
INSERT INTO testIndex(bitValue)
VALUES (0)
GO 20000 --runs current batch 20000 times.
INSERT INTO testIndex(bitValue)
VALUES (1)
GO 10 --puts 10 rows into table with value 1
SELECT filler
FROM testIndex
WHERE bitValue = 1
UPDATE STATISTICS dbo.testIndex
DBCC SHOW_STATISTICS('dbo.testIndex', 'XtestIndex_bitValue')
WITH HISTOGRAM