如果说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%的匹配率(因为该列值并没有限制为唯一),所以我感觉也不一定就会用到索引!它会比较各种情况的效率