null与索引问题

Question

在表t有千万条数据。
表t的a列中的数据中： 0.1%的值是null。
假如在表t的a字段上建了索引index_a ,问题：
1，索引index_a的叶子节点中包含null值吗？
2，'select * from t where a is null' 这条sql的话会用到索引index_a吗？
   假如这条sql用不上索引index_a的 话，
这条sql执行速度和没有索引index_a时是一样的吗？

Answer 1

在表t的a字段上建了索引index_a ,

Answer 2

复合索引只要不全为null是会出现在索引的叶节点，在某些时候可能有用

Answer 3

1，索引index_a的叶子节点中包含null值吗？
  >>
  包含2，'select * from t where a is null' 这条sql的话会用到索引index_a吗？
  假如这条sql用不上索引index_a的 话，
 >>
 看数据密度, NULL 值很多的话也许会放弃index_a的seek,转为Clustered Index scan.
 否则是 index_a Seek + Key Lookup. 这条sql执行速度和没有索引index_a时是一样的吗？
>>
  参看2回复.

Answer 4

1，索引index_a的叶子节点中包含null值吗？
包含。
2，'select * from t where a is null' 这条sql的话会用到索引index_a吗？ 
在索引中 NULL = NULL。0.1%的值是null，说明 null 的重复率不高，是可以使用索引的。
以上只是理论。具体还是要看执行计划。

Answer 5

执行计划的话有时不是很准，
在有些朋友口中两个答案都与8楼相反1：不包含2：null 的重复率不高，也不会用到该索引。
oracle的话答案也是1：不包含2：null 的重复率不高，也不会用到该索引。我糊涂了？？

Answer 6

以下的是在 sql server 2k5 中所作的实验，可以看到 select * from #tab where c1 is null 语句使用
index seed 的。这至少可以证明我的看法。create table #tab (c1 int,c2 char(4000));
insert into #tab
 select 1,'A' union all select 2,'A' union all select null,'B' union all
 select 3,'C' union all select 4,'D' union all select 5,'B' union all
 select 6,'E' union all select 7,'F' union all select null,'G' union all
 select 10,'H' union all select 9,'I' union all select 8,'J'
create clustered index ix_c on #tab(c1)--drop table #tabselect * from #tab where c1 is null

Answer 7

2000 和 2005/08 是不一样的
2000 是B树, 不包含NULL
2005/08 是B+权, 包含NULL楼主去建一个1万记录的表, 然然在一个值全部是NULL值的列上建索引, 比较一下 2000 和 2005/08 的索引大小就可以发现它们是有很大差异的

Answer 8

楼主可以看下非聚集索引的存储。篇幅和时间有限，只列出了些与主题相关的信息,没有做太多的注释。IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb(id INT IDENTITY,
n INT NULL,
v VARCHAR(10) NULL)
GO
SET NOCOUNT ON
INSERT tb SELECT 1,'a'
UNION ALL SELECT NULL,'b'
UNION ALL SELECT NULL,'c'
UNION ALL SELECT 2,'d'
UNION ALL SELECT 2,'e'
SET NOCOUNT OFF
GO
CREATE INDEX idx1 ON tb(n)
GO
DBCC IND(test,tb,-1)
/*
显示了表上的索引信息及页面，index_id=0为堆
*/
GO
SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID('tb')
/*
显示了表上的索引,index_id=0为堆
*/GO
SELECT hobt_id FROM sys.partitions WHERE object_id = OBJECT_ID('tb') AND index_id>1
/*
非聚集索引id between 2 and 250, 查找出索引容器id = 72057594041532416
*/ 
SELECT * FROM sys.system_internals_allocation_units WHERE container_id =72057594041532416
/*
找出索引管理参数信息, first_page, root_page, first_iam_page 依次是
0x490000000100 0x490000000100 0x5A0000000100
即
73,73,90
*/
GO
DBCC TRACEON(3604)
GO
DBCC EXTENTINFO(test,tb,2)
/*
page id 73, 一致的
*/
GO
DBCC TRACEON(3604)
GO
DBCC PAGE(test,1,73,3)
/*
fieldid pageid row level n(key) heap RID(key) keyHashValue
1 73 0 0 NULL 0xAE00000001000100 (b000885ced10)
1 73 1 0 NULL 0xAE00000001000200 (b0004b0fc03b)
1 73 2 0 1 0xAE00000001000000 (b00024e7f418)
1 73 3 0 2 0xAE00000001000300 (b10017664744)
1 73 4 0 2 0xAE00000001000400 (b100d0f0060b)可以看到，null也在非聚集索引中存储.
仔细看 HEAP RID的值，第三行， 0xAE00000001000000 其实就是指代了堆中的第一行
*/GO
DBCC TRACEON(3604) 
GO
DBCC EXTENTINFO(test,tb)
/*
看到还有174的数据页面id, partitionid=72057594041466880
*/
GO
SELECT * FROM sys.system_internals_allocation_units WHERE container_id =72057594041466880
/*
first page  = 0xAE0000000100 即 174
first iam page = 0x290000000100 即 41
*/
GO
DBCC TRACEON(3604)
GO
DBCC PAGE(test,1,174,3)
/*
可以看到数据行信息
*/
GO
DBCC PAGE(test,1,41,3)
/*
Slot 0 = (1:174)  
我生成的数据比较少，生成较多的数据，多几个页面后，可以发现first_iam_page中存储堆的页面信息，一个slot 一个数据页id 
*/
GO

Answer 9

1，索引index_a的叶子节点中包含null值吗？ sql server 2005\2008包含null值。
oracle不包含null值。2，'select * from t where a is null' 这条sql的话会用到索引index_a吗？ 
  假如这条sql用不上索引index_a的 话， 
这条sql执行速度和没有索引index_a时是一样的吗？是否能用上这个索引要看统计值的准确程度，你描述的场景应该可以用到索引。如果用不上索引只能全表扫描，速度应该要比利用索引的慢很多。