如果说SQL的执行计划与数据表中数据的多少有关，有人相信吗？
先看下面这样一个表：
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table test
go
--建表
create table test(id varchar(10) not null,name varchar(20) not null) on [PRIMARY]
go
--建主键、索引
ALTER TABLE test ADD CONSTRAINT PK_test PRIMARY KEY NONCLUSTERED (id) ON [PRIMARY]
CREATE INDEX IX_test_name ON test(name) ON [PRIMARY]
go
问题：对于上面这样一个表，下面的查询语句是否有区别？
1.
select * from test where name>'名称0000000001'
go
2.
declare @id varchar(20)
set @id='名称0000000001'
select * from test where name>@id
go
回复人： tx1icenhe(冒牌马可 V0.1) ( ) 信誉：100 2004-07-14 10:38:00 得分: 0
虽然老生常谈，但是确实有讨论必要，支持
SQL的执行计划与数据表中数据的多少有关，有人相信吗？
当然相信
Top
回复人： pbsql(风云) ( ) 信誉：106 2004-07-14 10:46:00 得分: 0
在查询分析器，可以看分析：（没有数据的情况）
SELECT成本：0%
Bookmark Lookup成本：49%
test.IX_test_name Index Seek成本：51%
下面来增加100条数据：
truncate table test
select top 100 identity(int,1,1) id into #t from syscolumns a,syscolumns b,syscolumns c
insert into test(id,name)
select right('0000000000'+cast(id as varchar),10),'名称'+right('0000000000'+cast(id as varchar),10) from #t
drop table #t
再看执行计划，1、2的执行计划没有变化。
但是如果增加10000条数据或是更多呢：
truncate table test
select top 10000 identity(int,1,1) id into #t from syscolumns a,syscolumns b,syscolumns c
insert into test(id,name)
select right('0000000000'+cast(id as varchar),10),'名称'+right('0000000000'+cast(id as varchar),10) from #t
drop table #t
再看执行计划，1、2的执行计划就有所不同：
1：
SELECT成本：0%
Table Scan成本：100%
2：
SELECT成本：5%
Table Scan成本：95%
Top
回复人： pbsql(风云) ( ) 信誉：106 2004-07-14 10:54:00 得分: 0
如果此时将数据全部删除：truncate table test
然后再看回头来看执行计划，情况又会有所不同：
1：
SELECT成本：0%
Table Scan成本：100%
（似乎是说索引没有用上）
2：
SELECT成本：0%
Bookmark Lookup成本：49%
test.IX_test_name Index Seek成本：51%
（会用到索引）
上面的情形有些解释不通啊
Top
回复人： lynx1111(任我行:一个PLMM看着就兴奋的男人) ( ) 信誉：95 2004-07-14 10:55:00 得分: 0
你以前不是发了一个类似的贴吗?
我相信SQL的执行计划与数据表中数据的多少有关.
Top
回复人： pbsql(风云) ( ) 信誉：106 2004-07-14 10:56:00 得分: 0
那个与这个有区别
Top
回复人： zonelive(peter) ( ) 信誉：99 2004-07-14 10:57:00 得分: 0
那只是一个估计值吧
Top
回复人： laker_tmj(laker) ( ) 信誉：100 2004-07-14 11:32:00 得分: 0
up
Top
回复人： wanyingsong(豌豆) ( ) 信誉：100 2004-07-14 11:38:00 得分: 0
学习ing！希望大家都写一些好东西，让小弟学习，呵呵
Top
回复人： pbsql(风云) ( ) 信誉：106 2004-07-14 12:02:00 得分: 0
执行计划虽是一个估计值，但从中可以分析SQL SERVER是怎么解析SQL语句并执行的，从而写最优的SQL语句
现在这个执行计划不定，怎么去判断怎样最优？
Top
回复人： leeboyan(★★宝宝★★) ( ) 信誉：100 2004-07-14 12:09:00 得分: 0
关注
Top
回复人： brucely() ( ) 信誉：100 2004-07-14 12:21:00 得分: 0
长学问
Top
回复人： zjcxc(邹建) ( ) 信誉：343 2004-07-14 12:45:00 得分: 0
测测
Top
回复人： zjcxc(邹建) ( ) 信誉：343 2004-07-14 12:50:00 得分: 0
怎么说呢,好像又是与条件相关
你把查询语句换成:
select * from test where name='名称0000000001'
结果又能利用上索引了.
Top
回复人： zjcxc(邹建) ( ) 信誉：343 2004-07-14 12:52:00 得分: 0
将条件换成<=,< 也一样可以利用上索引
看来好像跟表中的数据又发生了关系.
Top
回复人： zjcxc(邹建) ( ) 信誉：343 2004-07-14 12:59:00 得分: 0
我看应该是这样的
条件是:
name>'名称0000000001'
的话,因为'名称0000000001'这是一个固定条件,所以可以根据表中的记录情况,来确定是用索引快,还是直接扫描表快
或者可以说,因为你这是一个死条件,所以已经可以根据表中的现有记录直接出方案了,所以是跟表中的记录数有关.
而用变量的话,因为变量的值是变化的,即使是按你测试的语句那样,已经写好了变量的值,SQL也会认为是一个变化值,所以不能根据表中现有记录确定算法,所以就用了另一种优化方法.
Top
回复人： zjcxc(邹建) ( ) 信誉：343 2004-07-14 13:01:00 得分: 0
--同样的一万条记录,楼主还可以测试一下:
select * from test where name>'名称9000000001'
select * from test where name<'名称9000000001'
select * from test where name<'名称0000000001'
这些的执行计划都不一样,所以应该是:
如果条件的值是固定的,则执行计划的方案=条件+表中现有记录的值
Top
回复人： zjcxc(邹建) ( ) 信誉：343 2004-07-14 13:03:00 得分: 0
也可以理解为,条件中,使用固定值与使用变量值,SQL采用不同的算法来确定执行计划
Top
回复人： pbsql(风云) ( ) 信誉：106 2004-07-14 13:07:00 得分: 0
抛开用变量的那句不看，就看1：
为什么在没有数据和数据只有100条的情况下能用上索引，而在10000条时就用不上索引呢？
而在数据为10000条时若执行了truncate table test，似乎还是说索引没有用上，又怎么解释呢？
Top
回复人： zjcxc(邹建) ( ) 信誉：343 2004-07-14 13:13:00 得分: 0
只能这样解释,因为不用索引比用索引快
Top
回复人： pbsql(风云) ( ) 信誉：106 2004-07-14 13:18:00 得分: 0
好象不能这样解释吧，那岂不成了：数据少时（100条）用索引比不用索引快，数据多时（10000条）不用索引比用索引快？
Top
回复人： zjcxc(邹建) ( ) 信誉：343 2004-07-14 13:19:00 得分: 0
怪事了,我现在第二种方法也不用索引了.
Top
回复人： outwindows(窗外) ( ) 信誉：101 2004-07-14 13:21:00 得分: 0
mark...
Top
回复人： zjcxc(邹建) ( ) 信誉：343 2004-07-14 13:24:00 得分: 0
--有点说不清楚,1万条记录的时候,这个一样不能利用索引.
declare @id varchar(20)
set @id='名称0000005001'
select * from test where name>@id
Top
回复人： zjcxc(邹建) ( ) 信誉：343 2004-07-14 13:25:00 得分: 0
--就改这么一点点,索引也用不上.
declare @id varchar(20)
set @id='名称0000000002'
select * from test where name>@id
Top
回复人： zjcxc(邹建) ( ) 信誉：343 2004-07-14 13:28:00 得分: 0
用变量的方式,测试了几个临界值及中间值,似乎就 名称0000000001 的时候使用索引,其他都没有
Top
回复人： pbsql(风云) ( ) 信誉：106 2004-07-14 13:34:00 得分: 0
我的测试结果：（数据10000条）
用=时，1、2都能用上索引
用>、>=时，1、2都不能用上索引
用<、<=时，1能用上索引，2不能用上索引
真的是没法解释
Top
回复人： i9988(冒牌j9988 V0.1) ( ) 信誉：100 2004-07-14 13:56:00 得分: 0
我简单说说我的理解：
1、索引的实际操作比我们一般理解的复杂，索引的选用与否与查询条件、数据多少、数据值（关键值）的分布、索引字段的长度、是否有聚集索引、聚集索引字段的长度（如果有聚集索引）都有关。因为索引是否选用在默认情况下是自动的。
2、我们一般都能理解查询条件对索引是否选用的影响，这里不多说了。
3、要说其他方面，首先要理解数据页的概念，简单地说，不管数据还是索引都是放在8k（7.0版本以上）或者2k（7.0版本以下）为单位的页里，我假设大家都理解数据页的概念。
4、数据多少的影响主要是这样的，当数据比较少，极端的是都在一个页里，这样全表扫描只访问一个页，而通过索引至少访问两个页，用索引的速度显然不如全表扫描。而数据多的时候，通过索引放问怎么比全表扫描快，相信大家都是理解的。
5、数据值（关键值）的分布
要说这个就要说索引的结构了，只说2k版本（不同版本有所不同），是 B 树结构，B 树结构在这里很难说得清楚，干脆当大家都是理解的。
数据值（关键值）的分布主要的影响是可能使得B 树结构不再均衡，可能偏了，造成对不同键值的访问，都页的次数不同，速度也就有所差别，但是一般影响不大，但是如果向楼上几位只测试很少数据的情况，可能影响就大了。
6、索引字段的长度
因为页的大小固定，索引字段的长度越大，没页放的索引行就越少，索引的层数（B树的深度）就越大，需要访问的页数页就越多了。
7、是否有聚集索引
这个和非聚集索引的结构有关，如果有聚集索引，非聚集索引的索引行可能存放聚集索引的键值，否则，只能存放数据行的指针。因为聚集索引是按顺序存放的，前者的速度比后者快很多。
8、聚集索引字段的长度（如果有聚集索引）
由于有第7点和第6点，聚集索引字段的长度可能尤为重要，所以设计表的时候，聚集索引最好建立在尽可能短的字段上。
9、这里都是做简单的理解，数据库内部实际运作比这个理解复杂。
10、影响的因素还有很多，比如聚集索引是否唯一、组合索引的字段顺序等，本人理解有限，没办法都解释清楚。
Top
回复人： pbsql(风云) ( ) 信誉：106 2004-07-14 14:02:00 得分: 0
多谢J老师，慢慢体会
Top
回复人： i9988(冒牌j9988 V0.1) ( ) 信誉：100 2004-07-14 14:03:00 得分: 0
哈哈，冒牌成真了？
别看错了
Top
回复人： Limperator(珊儿) ( ) 信誉：97 2004-07-14 14:06:00 得分: 0
1.
select * from test(index=IX_test_name) where name>'名称0000000001'
go
2.
declare @id varchar(20)
set @id='名称0000000001'
select * from test(index=IX_test_name) where name>@id
go
试试上面两句, 和数据量还有没有关系?
Top
回复人： zjcxc(邹建) ( ) 信誉：343 2004-07-14 14:19:00 得分: 0
强制指定索引已经超出了本帖的讨论范围
本帖讨论的是SQL的执行计划与数据量的关系的问题.
强制指定索引已经指明了如何使用索引,所以有点偏题
Top
回复人： pbsql(风云) ( ) 信誉：106 2004-07-14 17:36:00 得分: 0
是的，本帖讨论SQL的执行计划与数据量的关系
Top
回复人： longtusoft(神灯之主) ( ) 信誉：100 2004-07-15 07:41:00 得分: 0
搂主的信誉为什么是106呀?
Top
回复人： Lwg0901(伤心人) ( ) 信誉：100 2004-07-15 08:08:00 得分: 0
为什么csdn的加入收藏夹用不了了呢？
郁闷。。。很多好帖都不能收藏。。。
Top
回复人： dotbyte() ( ) 信誉：100 2004-07-15 08:20:00 得分: 0
mark
Top
回复人： zheninchangjiang(徐震>愿母亲安息) ( ) 信誉：100 2004-07-15 08:22:00 得分: 0
不理解
Top
回复人： prcgolf(小鸟) ( ) 信誉：99 2004-07-15 09:27:00 得分: 0
UP
Top
回复人： meilian01(meilian) ( ) 信誉：100 2004-07-15 09:54:00 得分: 0
学习
Top
回复人： pcyy(妈妈我爱你) ( ) 信誉：100 2004-07-15 09:57:00 得分: 0
UP
Top
回复人： flyincs(我想回家……) ( ) 信誉：100 2004-07-15 13:12:00 得分: 0
学习
Top
回复人： futulove(福途￡爱) ( ) 信誉：100 2004-07-15 13:37:00 得分: 0
学习
顶一下
Top
回复人： panpass(潘帕斯草原的雄鹰) ( ) 信誉：100 2004-07-15 17:28:00 得分: 0
学习
Top
回复人： wgh166(涌金水牛) ( ) 信誉：100 2004-07-15 19:51:00 得分: 0
up
Top
回复人： YiYanXiYin(NullComeFullBack) ( ) 信誉：101 2004-07-15 20:33:00 得分: 0
哇！效率就是这样产生的！
Top
回复人： YiYanXiYin(NullComeFullBack) ( ) 信誉：101 2004-07-15 20:34:00 得分: 0
顺便问一下：成本是怎样得出来的？
Top
回复人： pascal21(犬夜叉) ( ) 信誉：100 2004-07-15 21:40:00 得分: 0
数据库技术QQ群：2966104
数据库技术QQ群：2966104
数据库技术QQ群：2966104
数据库技术QQ群：2966104
Top
回复人： yzqyzq(山羊) ( ) 信誉：100 2004-07-15 22:24:00 得分: 0
学习！
Top
回复人： tx1icenhe(冒牌马可 V0.1) ( ) 信誉：100 2004-07-20 10:18:00 得分: 0
顶
Top
回复人： wanyingsong(豌豆) ( ) 信誉：100 2004-07-20 10:35:00 得分: 0
有一个表，列有SEQ_NO INT 主键 建立了聚集索引，FINANCE_YEAR INT,FINANCE_MONTH,.....,又建立了索引（fiannce_year,finance_month)，表里面有27万条记录
为什么了我在查询分析器里面执行
select * from a_store_recent_month where finance_year=2003 and finance_month=4
执行计划使用的却没有使用到finance_year,finance_month的索引呢？
Top
回复人： pbsql(风云) ( ) 信誉：106 2004-07-20 11:15:00 得分: 0
不要完全相信执行计划
Top
回复人： wanyingsong(豌豆) ( ) 信誉：100 2004-07-20 11:21:00 得分: 0
但是确实是没有用到finance_year,finance_month索引呀，我测试了一下
select * from a_store_recent_month(index=IDX_RECMON_FYEARMONTH) where finance_year=2003 and finance_month=4
比
select * from a_store_recent_month where finance_year=2003 and finance_month=4
就要快很多
Top
回复人： outwindows(窗外) ( ) 信誉：101 2004-07-20 11:25:00 得分: 0
mark...
Top
回复人： pbsql(风云) ( ) 信誉：106 2004-07-20 11:32:00 得分: 0
所以说SQL的下个版本可能会要求指定index了
Top
回复人： pjy(古虫) ( ) 信誉：100 2004-07-20 11:49:00 得分: 0
是有些奇怪！
我使用搂主的例子，在10000条记录中执行查询
开始两个语句都是
select 成本6%
table scan成本94%
连续执行几遍，
语句2的成本没有变，
但语句1的成本变成
SELECT成本：0%
Bookmark Lookup成本：49%
test.IX_test_name Index Seek成本：51%
（会用到索引）
Top
回复人： CSDMN(冒牌经理 V0.1) ( ) 信誉：100 2004-07-20 11:51:00 得分: 0
实际的速度还跟缓存有关
Top
回复人： pbsql(风云) ( ) 信誉：106 2004-07-20 11:59:00 得分: 0
但是为什么对于同一个语句，=、>、>=、<、<=在索引使用上会有差别？
Top
回复人： pjy(古虫) ( ) 信誉：100 2004-07-20 12:00:00 得分: 0
实际上SQL SERVER查询分析器选择查询策略时会根据索引统计，我猜想语句1因为写死了查询的匹配值，所以在执行后，SQL SERVER自动记录了该值的索引统计匹配记录数；而制定变量的化，SQL SERVER使用了缺省的估计值('>'使用的是33%)
Top
回复人： pjy(古虫) ( ) 信誉：100 2004-07-20 13:11:00 得分: 0
看查询分析器的查询策略，语句1的预计行计数为30，语句2的为3000！
很奇怪，对于这个表的两条语句的查询，我感觉table scan应该比用索引还快一些呀！匹配行太多了！table scan 只需要大约33页，索引需要的开销大多了呀！
奇怪！
Top
回复人： pjy(古虫) ( ) 信誉：100 2004-07-20 13:45:00 得分: 0
我测试了，实际上查询分析器显示的查询策略并不完全正确！
我使用
print convert(char(30),getdate(),109)
select * from test where name>'名称0000000001'
go
print convert(char(30),getdate(),109)
declare @id varchar(20)
set @id='名称0000000001'
print convert(char(30),getdate(),109)
select * from test where name>@id
go
print convert(char(30),getdate(),109)
declare @id varchar(20)
set @id='名称0000000001'
select * from test where name=@id
go
print convert(char(30),getdate(),109)
执行信息：
07 20 2004 1:19:45:780PM
（所影响的行数为 9999 行）
07 20 2004 1:19:45:827PM
07 20 2004 1:19:45:827PM
（所影响的行数为 9999 行）
07 20 2004 1:19:45:890PM
（所影响的行数为 1 行）
07 20 2004 1:19:45:890PM
---------------------------------
语句1使用了47毫秒，语句2使用了63毫秒！
而使用
print convert(char(30),getdate(),109)
select * from test(index = IX_test_name) where name>'名称0000000001'
go
print convert(char(30),getdate(),109)
declare @id varchar(20)
set @id='名称0000000001'
print convert(char(30),getdate(),109)
select * from test(index = IX_test_name) where name>@id
go
print convert(char(30),getdate(),109)
declare @id varchar(20)
set @id='名称0000000001'
select * from test where name=@id
go
print convert(char(30),getdate(),109)
执行信息：
07 20 2004 1:32:22:653PM
（所影响的行数为 9999 行）
07 20 2004 1:32:22:700PM
07 20 2004 1:32:22:700PM
（所影响的行数为 9999 行）
07 20 2004 1:32:22:763PM
（所影响的行数为 1 行）
07 20 2004 1:32:22:763PM
－－－－－－－－－－－－－－－－－－－－－－－－
语句1使用了47毫秒，语句2使用了63毫秒
感觉两个语句，SQL SERVER在执行时都用到了索引！
（感觉sql server 2000的查询分析器提供的很多数据都不太准确，多次执行的数据不太一致，不如以前6.5版本的查询分析器详细。以上数据取的是稳定时的结果）
Top
回复人： mophi(追球) ( ) 信誉：99 2004-07-20 14:32:00 得分: 0
我的测试是：100条时不管理用那种方法都用索引了
但10000条时不管理用那种方法都不用索引了
Top
回复人： pjy(古虫) ( ) 信誉：100 2004-07-20 14:38:00 得分: 0
WO!
怎么测试结果都不同呀！
Top
回复人： solidpanther(╃╄╃我爱机器猫╄╃╄) ( ) 信誉：100 2004-07-20 14:50:00 得分: 0
收藏！
Top
回复人： yzqyzq(山羊) ( ) 信誉：100 2004-07-20 18:23:00 得分: 0
前几天做了上面的实验。最后从结论上来看SQL的执行计划与数据表中数据的多少有关好象是不对的。
/*--数据
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table test
go
--建表
create table test(id varchar(10) not null,name varchar(20) not null) on [PRIMARY]
go
--建主键、索引
ALTER TABLE test ADD CONSTRAINT PK_test PRIMARY KEY NONCLUSTERED (id) ON [PRIMARY]
CREATE INDEX IX_test_name ON test(name) ON [PRIMARY]
go
问题：对于上面这样一个表，下面的查询语句是否有区别？
1.
select * from test where name>='名称0000000001'
go
2.
declare @id varchar(20)
set @id='名称0000000001'
select * from test where name>@id
go
*/
这个是和索引有关系的不是说它的条件固定不固定
索引就好比我们看书一样，如果我们看的东西比较零散，
跳跃性比较大时使用目录是比较好的，
一看书的目录就可定位到要找的东西，不用一页一页翻。
对于
select * from test where name>='名称0000000001'语句使用索引反到不如全表扫描快
但是对于下面的语句就可以使用索引了。
select * from test where name in(select name from test where name like '%9')
它的跳跃性比较大，使用索引查询更快一些。
一般数据库会根据查询的条件选择相应的策略。
比如oracle数据库如果查询总数据的20%以下自动会使用到索引，如果大于20%会全表扫描。
Top
回复人： pbsql(风云) ( ) 信誉：106 2004-07-20 18:26:00 得分: 0
对于同一个语句，=、>、>=、<、<=在索引使用上会有差别又怎么解释？
Top
回复人： yzqyzq(山羊) ( ) 信誉：100 2004-07-20 18:35:00 得分: 0
同时可以比较下面的这些语句：
使用到索引
select * from test where name>='名称0000000001' and name<='名称0000000002'
select * from test where name>='名称0000000001' and name<='名称0000000031'
未使用索引
select * from test where name>='名称0000000001' and name<='名称0000000032'
这些我们都可以好好体会SQLserver的SQL语句的执行路线。
Top
回复人： yzqyzq(山羊) ( ) 信誉：100 2004-07-20 18:47:00 得分: 0
从上面可以看出SQLserver对于是使用索引还是全表扫描，有一定的算法，这个是根据所查数据在总数据所占的比例。
Top
回复人： prcgolf(小鸟) ( ) 信誉：99 2004-07-21 10:23:00 得分: 0
up
Top
回复人： evafly920() ( ) 信誉：100 2004-07-21 14:05:00 得分: 0
mark
Top
回复人： pjy(古虫) ( ) 信誉：100 2004-07-22 10:02:00 得分: 0
sql server对于'=','<','>'本身有一个缺省的匹配值，'>'和'<'默认的是33%
也有一套关于利用索引或表扫描的算法。
对于楼主的两条执行语句，我分析是表扫描要快的多，只需要大约33个数据页的开销！比33％的匹配率下的索引都应该快。SQL SERVER应该会选择表扫描的！
但实际执行后显示的数据确不是那么回事！
以前6.5的查询分析器可以看到详细的执行策略及I/O消耗，现在2000的好象看不到了！
郁闷！
Top
回复人： pjy(古虫) ( ) 信誉：100 2004-07-22 10:10:00 得分: 0
使用select * from test where name='名称0000000001'
SQL SERVER肯定是用到索引了！
但是如果用变量代替'名称0000000001'，SQL SERVER会认为有10%的匹配率（因为该列值并没有限制为唯一），所以我感觉也不一定就会用到索引！它会比较各种情况的效率！
扫一扫
2284
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
