SQL Server 索引的疑问
最近在SQL Server 2008 R2上调优从SQL Server 2000升级上来的系统
所以经常接触索引,对索引优化也有一点认识和疑问,提几个问题大家讨论一下
1.网上到处都是说索引字段中不能有null值或者说不能在索引字段中使用is null 或者 is not null
在CSDN上还找到一个帖子说is null 可以is not null不可以,还给出了测试代码和测试数据
这个讨论应该不是空穴来风的,因为在我的系统上也曾经出现过一些索引中使用null的怪现象
我的一个表建立了一个非聚集索引(多字段的),我的一个查询使用到 is null 查询该索引的某个字段
(当然条件是复杂的了)结果非常慢,效率很低,后来没办法什么办法都试了,最后试到一个办法就是使用
SET ANSI_NULLS OFF选项,吧null的不可比较关闭,然后用=号查找效率就上来了,但最近升级到2008
后在联机文档中看到SET ANSI_NULLS OFF标志将在以后版本不在支持了,我现在想知道的确切答案是
到底什么情况下索引中字段与null的之间的比较不会降低效率,怎么提高这种索引字段中含有null的
查询的查询效率吗(难道非得使用默认值不能使用null吗),请大家给出样板数据和执行计划说明,实操一下
不然人云亦云理所当然,我在SQL Server 2008 R2 上测试是不管我怎么写查询条件
is null ,is not null, not in (),<> 等等都可以使用索引
2.还有一个就是全表扫描的问题,我今天特意想试一下怎么写查询条件能够避免全表扫描,结果试了一些
网上说会出现全表扫描的写法,结果我在执行计划中还是不能看到全表扫描,很奇怪啊,一个很明显会全表
扫描的例子 select * from table where a like '%1%' (a没有索引,table记录数在1万条左右)
结果在执行计划中也没看到全表扫描的计划,而是使用了聚集索引查找,我的table有聚集索引,但不是
在a字段上.后来不管我怎么写,还是没办法遇上全表扫描的计划,神啊,呵呵,想他出现的时候却不出现
不想他出现的时候却到来.有谁能给出一个样板例子(最好代码创建样板数据),在 SQL Server 2008 R2
上使用索引和不能使用索引的例子呢?
PS:网上很所优化的文章今天我试都是没效果的,或者说根本和文章说讲得不一样,很可能是因为现在
查询优化已经很智能了,以前很多不能优化的查询现在都可以优化了,举一个这次我优化中遇到的一个
问题说大家讨论一下,一个where如果有多个条件用and连接,那么这几个条件的先后顺序是有讲究的
例如把计算复杂度很高的写在前面(前还是后这个就要看SQL Server是怎么分析SQL产生执行计划了,
有人说是从后面往前分析,所以应该把简单的条件写在后面),那么就会大大增加运算时间,不知道SQL
的and 连接是不是断路的(例如.NET里面的andalso),因为第一个条件不成立我就没必要去计算第二个
条件了.我就曾经优化过一个if ... and ...这样的条件语句,把简单的判断写在前面,结果执行时间
大大降低,效率提高几倍.但奇怪的是我试了一下这样的写法:select * from tableA where a=1
and exists(select * from tableB where tableB.b=tableA.a)
这个查询不管我怎么调换两个条件的顺序,查询时间都是差不多的,优化器是怎么做到的啊!!!
难道优化器能够优化到分析每一个and的复杂度然后先执行复杂度低的???
[解决办法]
这个是实验代码，从4中，我们能够看到v(key)列中有null，说明了索引中也包含了null值。
最后的查询，通过 is null，发现也是走的索引查找。
--1.建表
if OBJECT_ID('t1') is not null
drop table t1
go
create table t1
(
id int primary key,
v varchar(20)
)
insert into t1
select 1 ,'aa' union all
select 2 ,'bb' union all
select 3 ,'cc' union all
select 4 ,'dd' union all
select 5 ,'ee' union all
select 6 ,'ff' union all
select 7 ,null union all
select 8 ,'gg' union all
select 9 ,null
go
--2.创建一个非聚集索引
create index idx_t1_v on t1(v)
go
--3.index_id = 2 为idx_t1_v索引
select *
from sys.indexes
where object_id = object_id('t1')
--hobt_id = 72057594041466880
select *
from sys.partitions
where index_id = 2
and object_id = object_id('t1')
--root_page = 0xAF0000000100
select *
from sys.system_internals_allocation_units
where container_id = 72057594041466880
--4.0100:文件id，而AF是16进制，转化为10进制是 175，pageId = 175
DBCC page(12, --数据库id : 10
1, --文件id: 1
175, --页id: 188
3) --with tableresults
/*
文件id 页id 索引行号 层级 v列的值 这个v列值所对应的行号 id列的hash值
FileIdPageIdRowLevelv (key)id (key)KeyHashValue
1 175 00 NULL7 (8d4dc9cd25b3)
1 175 10 NULL9 (fd07a7dffc59)
1 175 20 aa 1 (e5e354933dff)
1 175 30 bb 2 (df47e5a393e1)
1 175 40 cc 3 (36248ab30914)
1 175 50 dd 4 (40ee14f42575)
1 175 60 ee 5 (a98d7be4bf80)
1 175 70 ff 6 (9329cad4119e)
1 175 80 gg 8 (f34ca041b78d)
*/
--5.走的索引查找
select *
from t1
where v is null
--走的是索引扫描,之所以这里走索引,是因为在v列的非聚集索引中,也包含了所有的数据
select *
from t1
where v like '%a%'
