存档
今天下午在solaris10 ASE15.0.3平台上批量创建非聚簇索引时，报下面的错误：
Msg 1526, Level 17, State 5: Server 'SYB_NFJD_TEST', Procedure 'sp_helpdb', Line 674: Sort failure. The configure size for data cache (default data cache) is not sufficient to handle the sort buffers (128) requested. Please reconfigure the data cache or sort buffers and try the command again. (return status = -7)
用命令:sp_helpcache "default data cache"，发现高速数据缓冲大小为：8M，此为默认值。
执行命令将default data cache扩充至100M，sp_cacheconfig "default data cache","100m"
然后再重新执行一遍非聚簇索引的创建过程，成功完成了!
在error message & troubleshooting 中没有找到错误号1526的解释以及解决办法。
目前暂时没有想明白，为什么在创建非聚簇索引的时候需要用default data cache,而且默认数据缓存不足还会出错。难道default data cache要缓存某一层次的索引的页数据？
------------------------------------------------------------------------------------------------
以下关联一篇网上搜索的也是关于error：1526的信息
The failure is most likely happening on the remote server where the actual
physical table lives. It's certainly possible to create indexes on proxy
tables, but what really happens is that the physical index is created on the
remote server. Try logging in to the remote server where the actual table
resides. Check the memory settings on the remote server.
> Hi,
> I am trying to create a non clustered index on an "existing
> table"(proxy table). But it keeps failing with the error,
>
> Sort failure. The configure size for data cache (default
> data cache) is not
> sufficient to handle the sort buffers (30008) requested.
> Please reconfigure the
> data cache or sort buffers and try the command again.
>
> The data cache is configured 2 Gig, 800MB for 8K pool
> I have this configuration enough for most of the things.
>
> How can I make the non clustered index.
总结一下： 在建立非聚簇索引的时候，都涉及到default data cache数据缓存的使用。不管是在基本表还是在代理表上创建非聚簇索引，都会在物理表所在的ASE服务器上使用数据缓存。如果数据缓存不足，则会报 1526的错误信息。解决办法为用过程sp_cacheconfig增加default data cache即可。
————————————————————————————————-
—- 本文为andkylee个人原创，请在尊重作者劳动成果的前提下进行转载；
—- 转载务必注明原始出处 : http://www.dbainfo.net
—- 关键字：nonclustered index default data cache insufficient 1526
数据缓存不足 非聚簇索引
————————————————————————————————-
今天在sqlcentral群里面有人讨论主键和索引的区别，特地搜索了一下。找到一篇，特作收藏。
http://www.cnblogs.com/chinhr/archive/2007/09/30/911792.html
其实指定列的索引就相当于对指定的列进行排序，为什么要排序呢？因为 排序有利于对该列的查询，可以大大增加查询效率。（那么可能有人认为应该对所有的列排序，这样就可以增加整个数据库的查询效率？这样的想法是错误的，原因 是建立索引也是要消耗系统资源的，给每个表里的每个列都建立索引那么将对系统造成极大的负担，那就更别提效率了！）---------简单的说建立一个列 的索引，就相当与建立一个列的排序。
主键其实就是一个索引，但是这个索引跟一般的索引有所不同，不 同在于主键所在的列里的每一个的记录都是唯一的，也可以说不能在主键里出现相同的记录,在同一个表里只能有一个主键。（主键等于索引，索引不一定等于主 键）----------简单的说主键就是所在列不能出现相同记录的特殊索引，而且这个索引只能在表里出现一次。
1.主键一定是唯一性索引，唯一性索引并不一定就是主键；
2.一个表中可以有多个唯一性索引，但只能有一个主键；
3.主键列不允许空值，而唯一性索引列允许空值。
迄今已分析出来了sybase中索引（indid>1）的物理存储结构。
索引结构是B-Tree类型的。最顶部叫做根（root），最底层称为叶子（leaf）。一个表可能建有好几个非聚簇索引，这时indid依次为2，3，。。。递增。
对于一个索引，比如indid=2的那个。索引树状结构是分层次的，在sybase数据存储中用level表示，根部级别最高，叶子的级别最低。叶 子（leaf）的级别level为0，往上索引层level为1，再往上位2，。。。最后到达顶部root级别为（N-1，N为所有的层次数）。
不管APL还是DOL表，索引的每层（level）上的页面都是前后链接起来的，这一点有点像APL表中的数据页面上的前、后页链（data page link）。
以下简要演示分析索引结构的过程。
1.
设定成在终端显示dbcc结果信息。
1 2 | dbcc traceon(3604) go |
2.
查看syspartitions表的信息
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 | 1> select *from sysobjects 2> where name='PartitionTestTable' 3> go name id uid type userstat sysstat indexdel schemacnt sysstat2 crdate expdate deltrig instrig updtrig seltrig ckfirst cache audflags objspare versionts loginame identburnmax spacestate erlchgts ----------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------ ---------------------------------------------------------------------------- ----------- ----------- ---- -------- ------- -------- --------- ----------- -------------------------- -------------------------- ----------- ----------- ----------- ----------- ----------- ------ ----------- ----------- -------------------------- ------------------------------ ----------------------------------------- ---------- ------------------ PartitionTestTable 1223672376 1 U 0 99 2 0 73728 Feb 24 2010 4:43PM Feb 24 2010 4:43PM 0 0 0 0 0 0 0 0 NULL NULL NULL NULL NULL (1 row affected) 1> select * from syspartitions 2> where id = 1223672376 3> go name indid id partitionid segment status datoampage indoampage firstpage rootpage data_partitionid crdate cdataptnname ----------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------ ---------------------------------------------------------------------------- ------ ----------- ----------- ------- ----------- ----------- ----------- ----------- ----------- ---------------- -------------------------- --------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------ zhang 0 1223672376 1239672433 1 0 61288 0 61289 61997 0 Feb 24 2010 4:43PM NULL liu 0 1223672376 1255672490 1 0 61296 0 61297 62124 0 Feb 24 2010 4:43PM NULL wang 0 1223672376 1271672547 1 0 61304 0 61305 62260 0 Feb 24 2010 4:43PM NULL li 0 1223672376 1287672604 1 0 61312 0 61313 62385 0 Feb 24 2010 4:43PM NULL idx_PartitionTestTable_id_1431673117 2 1223672376 1431673117 1 2 0 62465 67256 66248 0 Mar 1 2010 11:19AM NULL idx_PartitionTestTable_name_1479673288 3 1223672376 1479673288 1 2 0 62721 69816 69386 0 Mar 1 2010 6:03PM NULL (6 rows affected) 1> |
表PartitionTestTable是在其上的id列建了4个分区的分区表，它有2个索引。idx_PartitionTestTable_id对id列索引，idx_PartitionTestTable_name对name列索引。
我们就分析idx_PartitionTestTable_id这个索引吧。通过syspartitions表我们可以得到四个比较有用的 datoampage，indoampage， firstpage ，rootpage。分别表示数据对象分配页的页号，索引对象分配页的页号，
索引叶子层上的第一页，索引根部的页号。（堆表信息中的firstpage，rootpage意思有些不同。分别表示：数据页的第一个、最后一页。）
有：datoampage=0,indoampage=62465,firstpage= 67256,rootpage=66248.
对于indoampage索引对象分配页，可以这么查看。（PartitionTestTable的objid为： 1223672376）
dbcc listoam(4,1223672376,2)
3.可以看出索引idx_PartitionTestTable_id在14个对象分配页allocation page上的分配情况如下：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | 1> dbcc listoam(4,1223672376,2) 2> go ----------------------------------------------------------------------------- Partition id: 1431673117 indid: 2 prevpg: 62465 nextpg: 62465 OAM pg cnt: 1 Entry cnt: 14 Row count information is not maintained for index pages. Used pgs: 716 Unused pgs: 11 Attribute entries: 10 OAM status bits set: (0x8000 (PG_OAMPG), 0x0008 (PG_OAMATTRIB), 0x0004 (PG_OAMSORT)) LAST SCANNED OAM PAGE: 0 ALLOCATION HINTS : 62465 0 0 0 0 0 0 0 0 0 0 0 0 0 0 OAM pg # 1: 62465 has the following 14 entries (allocpg:used/unused): [ 0] 62464: 9/ 6 63232: 24/ 0 63744: 8/ 0 64256: 0/ 0 [ 4] 64768: 16/ 0 65024: 16/ 0 65280: 16/ 0 65536: 16/ 0 [ 8] 65792:152/ 0 66048:240/ 0 66304: 96/ 0 66560: 48/ 0 [ 12] 66816: 40/ 0 67072: 35/ 5 There are 1 entries with zero used/unused values. ---- End of OAM chain for partition 1431673117 ---- DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role. |
（此处，暂时不解释以上结果中的情况！）
4.现在回到索引上，先从根部分析。rootpage=66248。
查看66248页上的16进制数据。
dbcc page(4,66248,1)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 | 1> dbcc page(4,66248,1)
2> go
Page not found in Cache: default data cache.
Page read from disk.
BUFFER:
Buffer header for buffer 0x28288000 (Mass head)
page=0x28287000 bdnew=0x00000000 bdold=0x00000000 bhash=0x00000000
bmass_next=0x00000000 bmass_prev=0x00000000 bdbid=4
bvirtpg= [ 0x28288070 vpgdevno=5 vpvpn=132496 vdisk=0x21DE1CB4 ]
bmass_head=0x28288000 bmass_tail=0x28288000
bcache_desc=0x2828B3F0 (cache name='default data cache')
bpool_desc=0x00000000 bdbtable=0x00000000
Mass bkeep=0 Mass bpawaited=0 Mass btripsleft=0 Mass btripsleft_orig=0
bmass_size=4096 (4K pool) bunref_cnt=0
bmass_stat=0x0800 (0x00000800 (MASS_NOTHASHED))
bbuf_stat=0x0 (0x00000000)
Buffer blpageno=66248 bpg_size=4k Mass blpageno=66248 (Buffer slot #: 0)
bxls_pin=0x00000000 bxls_next=0x00000000 bspid=0
bxls_flushseq=0 bxls_pinseq=0 bcurrxdes=0x00000000
Latch and the wait queue:
Latch (address: 0x28288020)
latchmode: 0x0 (FREE_LATCH)
latchowner: 0
latchnoofowners: 0
latchwaitq: 0x00000000 latchwaitqt: 0x00000000
Latch wait queue:
PAGE HEADER:
Page header for page 0x28287000
pageno=66248 nextpg=0 prevpg=0 ptnid=1431673117 timestamp=0000 0040a817
lastrowoff=62 level=2 indid=2 freeoff=77 minlen=15
page status bits: 0x80 (0x0080 (PG_FIXED))
DATA:
Offset 32 - row length=15 # varlen cols=0 Child page ID=67258
28287020 ( 0): 00be8301 00aff000 004100ba 060100 .........A.....
Row-Offset table for variable-length columns:
[<varcol number>, <offset from start of the row>, <varcol length>]
Offset 47 - row length=15 # varlen cols=0 Child page ID=65823
2828702F ( 0): 00be8301 00aff000 0041001f 010100 .........A.....
Row-Offset table for variable-length columns:
[<varcol number>, <offset from start of the row>, <varcol length>]
Offset 62 - row length=15 # varlen cols=0 Child page ID=66990
2828703E ( 0): 007b0703 00f0f200 009d00ae 050100 .{.............
Row-Offset table for variable-length columns:
[<varcol number>, <offset from start of the row>, <varcol length>]
OFFSET TABLE:
DBCC execution completed. If DBCC printed error messages, contact a user with
System Administrator (SA) role.
1> |
此时，可以看出索引根部的级别level为2。 也就是说索引还有中间层level=1和叶子层level=0.
看第二行数据，
Offset 47 - row length=15 # varlen cols=0 Child page ID=65823
2828702F ( 0): 00be8301 00aff000 0041001f 010100 .........A.....
Row-Offset table for variable-length columns:
[<varcol number>, <offset from start of the row>, <varcol length>]
它的子页面号是：65823。
再来查看65823的页面数据。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 | 1> dbcc page(4,65823,1)
2> go
Page not found in Cache: default data cache.
Page read from disk.
BUFFER:
Buffer header for buffer 0x28288000 (Mass head)
page=0x28287000 bdnew=0x00000000 bdold=0x00000000 bhash=0x00000000
bmass_next=0x00000000 bmass_prev=0x00000000 bdbid=4
bvirtpg= [ 0x28288070 vpgdevno=5 vpvpn=131646 vdisk=0x21DE1CB4 ]
bmass_head=0x28288000 bmass_tail=0x28288000
bcache_desc=0x2828B3F0 (cache name='default data cache')
bpool_desc=0x00000000 bdbtable=0x00000000
Mass bkeep=0 Mass bpawaited=0 Mass btripsleft=0 Mass btripsleft_orig=0
bmass_size=4096 (4K pool) bunref_cnt=0
bmass_stat=0x0800 (0x00000800 (MASS_NOTHASHED))
bbuf_stat=0x0 (0x00000000)
Buffer blpageno=65823 bpg_size=4k Mass blpageno=65823 (Buffer slot #: 0)
bxls_pin=0x00000000 bxls_next=0x00000000 bspid=0
bxls_flushseq=0 bxls_pinseq=0 bcurrxdes=0x00000000
Latch and the wait queue:
Latch (address: 0x28288020)
latchmode: 0x0 (FREE_LATCH)
latchowner: 0
latchnoofowners: 0
latchwaitq: 0x00000000 latchwaitqt: 0x00000000
Latch wait queue:
PAGE HEADER:
Page header for page 0x28287000
pageno=65823 nextpg=66990 prevpg=67258 ptnid=1431673117 timestamp=0000
0040a817
lastrowoff=4052 level=1 indid=2 freeoff=4067 minlen=15
page status bits: 0x80 (0x0080 (PG_FIXED))
DATA:
Offset 32 - row length=15 # varlen cols=0 Child page ID=65822
28287020 ( 0): 00be8301 00aff000 0041001e 010100 .........A.....
Row-Offset table for variable-length columns:
[<varcol number>, <offset from start of the row>, <varcol length>]
Offset 47 - row length=15 # varlen cols=0 Child page ID=66249
2828702F ( 0): 002f8501 0039f100 003300c9 020100 ./...9...3.....
Row-Offset table for variable-length columns:
[<varcol number>, <offset from start of the row>, <varcol length>]
Offset 62 - row length=15 # varlen cols=0 Child page ID=66250
2828703E ( 0): 00a08601 007bf100 00a700ca 020100 .....{.........
Row-Offset table for variable-length columns:
[<varcol number>, <offset from start of the row>, <varcol length>]
Offset 77 - row length=15 # varlen cols=0 Child page ID=66251
2828704D ( 0): 00118801 00f3f000 00c800cb 020100 ...............
Row-Offset table for variable-length columns:
[<varcol number>, <offset from start of the row>, <varcol length>]
Offset 92 - row length=15 # varlen cols=0 Child page ID=66252
2828705C ( 0): 00828901 00b0f000 00d100cc 020100 ...............
Row-Offset table for variable-length columns:
[<varcol number>, <offset from start of the row>, <varcol length>]
Offset 107 - row length=15 # varlen cols=0 Child page ID=66253
2828706B ( 0): 00f38a01 003af100 00b500cd 020100 .....:.........
Row-Offset table for variable-length columns:
[<varcol number>, <offset from start of the row>, <varcol length>] |
此页面65823是索引的中间层level=1.看第一行的数据。
Offset 32 - row length=15 # varlen cols=0 Child page ID=65822
28287020 ( 0): 00be8301 00aff000 0041001e 010100 .........A.....
Row-Offset table for variable-length columns:
[<varcol number>, <offset from start of the row>, <varcol length>]
页面：66248上的第二行就是指向该行。
继续查看它的子页面上的数据，65822。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 | 1> dbcc page(4,65822,1) 2> go Page not found in Cache: default data cache. Page read from disk. BUFFER: Buffer header for buffer 0x28288000 (Mass head) page=0x28287000 bdnew=0x00000000 bdold=0x00000000 bhash=0x00000000 bmass_next=0x00000000 bmass_prev=0x00000000 bdbid=4 bvirtpg= [ 0x28288070 vpgdevno=5 vpvpn=131644 vdisk=0x21DE1CB4 ] bmass_head=0x28288000 bmass_tail=0x28288000 bcache_desc=0x2828B3F0 (cache name='default data cache') bpool_desc=0x00000000 bdbtable=0x00000000 Mass bkeep=0 Mass bpawaited=0 Mass btripsleft=0 Mass btripsleft_orig=0 bmass_size=4096 (4K pool) bunref_cnt=0 bmass_stat=0x0800 (0x00000800 (MASS_NOTHASHED)) bbuf_stat=0x0 (0x00000000) Buffer blpageno=65822 bpg_size=4k Mass blpageno=65822 (Buffer slot #: 0) bxls_pin=0x00000000 bxls_next=0x00000000 bspid=0 bxls_flushseq=0 bxls_pinseq=0 bcurrxdes=0x00000000 Latch and the wait queue: Latch (address: 0x28288020) latchmode: 0x0 (FREE_LATCH) latchowner: 0 latchnoofowners: 0 latchwaitq: 0x00000000 latchwaitqt: 0x00000000 Latch wait queue: PAGE HEADER: Page header for page 0x28287000 pageno=65822 nextpg=66249 prevpg=65821 ptnid=1431673117 timestamp=0000 0040a817 lastrowoff=4080 level=0 indid=2 freeoff=4091 minlen=11 page status bits: 0x82 (0x0080 (PG_FIXED), 0x0002 (PG_LEAF)) DATA: Offset 32 - row length=11 # varlen cols=0 Data page RID=(61615, 65) 28287020 ( 0): 00be8301 00aff000 004100 .........A. Row-Offset table for variable-length columns: [<varcol number>, <offset from start of the row>, <varcol length>] Offset 43 - row length=11 # varlen cols=0 Data page RID=(61752, 198) 2828702B ( 0): 00bf8301 0038f100 00c600 .....8..... Row-Offset table for variable-length columns: [<varcol number>, <offset from start of the row>, <varcol length>] Offset 54 - row length=11 # varlen cols=0 Data page RID=(61818, 239) 28287036 ( 0): 00c08301 007af100 00ef00 .....z..... Row-Offset table for variable-length columns: [<varcol number>, <offset from start of the row>, <varcol length>] |
还是分析页面上的第一行。索引叶子层直接指向了数据页面，某一页面上的某一行。
Offset 32 - row length=11 # varlen cols=0 Data page RID=(61615, 65)
28287020 ( 0): 00be8301 00aff000 004100 .........A.
Row-Offset table for variable-length columns:
[<varcol number>, <offset from start of the row>, <varcol length>]
继续往下，dbcc page(4,61615,1)找到第65行数据。
Offset 1072 - row ID=65 row length=16 # varlen cols=1
28287430 ( 0): 0141be83 0100 1000 7a68616e 67020d08 .A......zhang...
28287440 ( 16):
Row-Offset table for variable-length columns:
[<varcol number>, <offset from start of the row>, <varcol length>]
[1, 8, 5]
可以看出00be8301 00 aff000 004100中的be8301 00 和0141be83 01001000 7a68616e 67020d08中的
be83 0100 是一致的。也就是说索引中间层level=1是指向叶子层上的第一页。并且包含索引键的数据99262。
从16进制数据中分析出来，页61615第65行的数据为：id=99262,name='zhang'。
暂时就分析这么多。可能有些地方说的不太明白。见谅！
————————————————————————————————
—- 本文为andkylee个人原创，请在尊重作者劳动成果的前提下进行转载；
—- 转载务必注明原始出处 : http://www.dbainfo.net
—- 关键字：索引 存储结构 分析 非聚簇 index storage non-clustered
————————————————————————————————
在sybase表上建立聚集索引可以提高键列的检索速度。这是索引的主要功能所在。
可是，聚集索引对于统计表上的行数count（*）有没有改善呢？ 答案是否定的。
请看我下面的测试代码！
建立一张临时表test3
1 | create table test3(id int not null,name varchar(30) null) |
向表中插入测试数据
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | insert into test3 select 1,'liu' go insert into test3 select 2,'zhang' go insert into test3 select 3,'wang' go insert into test3 select 4,'li' go 循环插入 insert into test4 select count(*)+id,name from test4 go 18 1> select count(*) from test4 2> go ----------- 524288 (1 row affected) 循环插入了524288条记录! |
打开查询计划和统计查询计划时间的选项
1 2 3 4 | set showplan on go set statistics time on go |
表上没有加任何索引的情况下。
select count(*) from test4 的查询计划为:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | 1> select count(*) from test4 2> go QUERY PLAN FOR STATEMENT 1 (at line 1). STEP 1 The type of query is SELECT. 2 operator(s) under root |ROOT:EMIT Operator (VA = 2) | | |SCALAR AGGREGATE Operator (VA = 1) | | Evaluate Ungrouped COUNT AGGREGATE. | | | | |SCAN Operator (VA = 0) | | | FROM TABLE | | | test4 | | | Table Scan. | | | Forward Scan. | | | Positioning at start of table. | | | Using I/O Size 32 Kbytes for data pages. | | | With MRU Buffer Replacement Strategy for data pages. Parse and Compile Time 0. Adaptive Server cpu time: 0 ms. ----------- 524288 Execution Time 1. Adaptive Server cpu time: 100 ms. Adaptive Server elapsed time: 156 ms. (1 row affected) |
select count(1) from test4 的查询计划为:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | 1> select count(1) from test4 2> go QUERY PLAN FOR STATEMENT 1 (at line 1). STEP 1 The type of query is SELECT. 2 operator(s) under root |ROOT:EMIT Operator (VA = 2) | | |SCALAR AGGREGATE Operator (VA = 1) | | Evaluate Ungrouped COUNT AGGREGATE. | | | | |SCAN Operator (VA = 0) | | | FROM TABLE | | | test4 | | | Table Scan. | | | Forward Scan. | | | Positioning at start of table. | | | Using I/O Size 32 Kbytes for data pages. | | | With MRU Buffer Replacement Strategy for data pages. Parse and Compile Time 0. Adaptive Server cpu time: 0 ms. ----------- 524288 Execution Time 1. Adaptive Server cpu time: 100 ms. Adaptive Server elapsed time: 220 ms. |
可以看出，count（*） 和count（1） 的执行计划是相同的。都执行了表扫描。
由于表上没有任何索引可供使用，select count(id) 和 select count(name) 都是执行了表扫描。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 | 1> select count(id) from test4 2> go QUERY PLAN FOR STATEMENT 1 (at line 1). STEP 1 The type of query is SELECT. 2 operator(s) under root |ROOT:EMIT Operator (VA = 2) | | |SCALAR AGGREGATE Operator (VA = 1) | | Evaluate Ungrouped COUNT AGGREGATE. | | | | |SCAN Operator (VA = 0) | | | FROM TABLE | | | test4 | | | Table Scan. | | | Forward Scan. | | | Positioning at start of table. | | | Using I/O Size 32 Kbytes for data pages. | | | With MRU Buffer Replacement Strategy for data pages. Parse and Compile Time 0. Adaptive Server cpu time: 0 ms. ----------- 524288 Execution Time 1. Adaptive Server cpu time: 100 ms. Adaptive Server elapsed time: 140 ms. (1 row affected) 1> select count(name) from test4 2> go QUERY PLAN FOR STATEMENT 1 (at line 1). STEP 1 The type of query is SELECT. 2 operator(s) under root |ROOT:EMIT Operator (VA = 2) | | |SCALAR AGGREGATE Operator (VA = 1) | | Evaluate Ungrouped COUNT AGGREGATE. | | | | |SCAN Operator (VA = 0) | | | FROM TABLE | | | test4 | | | Table Scan. | | | Forward Scan. | | | Positioning at start of table. | | | Using I/O Size 32 Kbytes for data pages. | | | With MRU Buffer Replacement Strategy for data pages. Parse and Compile Time 0. Adaptive Server cpu time: 0 ms. ----------- 524288 Execution Time 1. Adaptive Server cpu time: 100 ms. Adaptive Server elapsed time: 236 ms. (1 row affected) 1> |
下面考虑加入主键（聚集索引）pk_test4_id
1 2 | alter table test4 add constraint pk_test4_id primary key (id) go |
再次执行select count(*) from test4 和 select count(1) from test4
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 | 1> select count(*) from test4 2> go QUERY PLAN FOR STATEMENT 1 (at line 1). STEP 1 The type of query is SELECT. 2 operator(s) under root |ROOT:EMIT Operator (VA = 2) | | |SCALAR AGGREGATE Operator (VA = 1) | | Evaluate Ungrouped COUNT AGGREGATE. | | | | |SCAN Operator (VA = 0) | | | FROM TABLE | | | test4 | | | Table Scan. | | | Forward Scan. | | | Positioning at start of table. | | | Using I/O Size 32 Kbytes for data pages. | | | With MRU Buffer Replacement Strategy for data pages. Parse and Compile Time 0. Adaptive Server cpu time: 0 ms. ----------- 524288 Execution Time 2. Adaptive Server cpu time: 200 ms. Adaptive Server elapsed time: 736 ms. (1 row affected) 1> select count(1) from test4 2> go QUERY PLAN FOR STATEMENT 1 (at line 1). STEP 1 The type of query is SELECT. 2 operator(s) under root |ROOT:EMIT Operator (VA = 2) | | |SCALAR AGGREGATE Operator (VA = 1) | | Evaluate Ungrouped COUNT AGGREGATE. | | | | |SCAN Operator (VA = 0) | | | FROM TABLE | | | test4 | | | Table Scan. | | | Forward Scan. | | | Positioning at start of table. | | | Using I/O Size 32 Kbytes for data pages. | | | With MRU Buffer Replacement Strategy for data pages. Parse and Compile Time 0. Adaptive Server cpu time: 0 ms. ----------- 524288 Execution Time 0. Adaptive Server cpu time: 0 ms. Adaptive Server elapsed time: 500 ms. (1 row affected) 1> |
由上可以看出，聚集索引对于select count(*) 几乎没有扫描影响。堆表和聚集索引表上的count是没有什么区别的，甚至于聚集索引表上的IO还要多2（这是因为多了两个聚集索引的数据块造成的）。其 实聚集索引并没有单独的保留所有索引列的信息，而只是将表中的行的物理顺序按照聚集索引列的顺序整理了一下，因此对聚集索 引的扫描和对堆表的扫描是一样的，没有什么本质上的区别。
添加id列上的非聚集索引idx_test4_id
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 | 1> create index idx_test4_id on test4(id) 2> go 1> sp_help test4 2> go Name Owner Object_type Create_date ----- ----- ----------- ------------------- test4 dbo user table Feb 25 2010 3:44PM (1 row affected) Column_name Type Length Prec Scale Nulls Default_name Rule_name Access_Rule_name Computed_Column_object Identity ----------- ------- ------ ---- ----- ----- ------------ --------- ---------------- ---------------------- ---------- id int 4 NULL NULL 0 NULL NULL NULL NULL 0 name varchar 30 NULL NULL 1 NULL NULL NULL NULL 0 Object has the following indexes index_name index_keys index_description index_max_rows_per_page index_fillfactor index_reservepagegap index_created index_local ------------ ---------- ----------------- ----------------------- ---------------- -------------------- ------------------- ------------ pk_test4_id id clustered, unique 0 0 0 Feb 25 2010 4:04PM Global Index idx_test4_id id nonclustered 0 0 0 Feb 25 2010 4:52PM Global Index (2 rows affected) index_ptn_name index_ptn_seg ----------------------- ------------- pk_test4_id_1399673003 default idx_test4_id_1399673003 default (2 rows affected) No defined keys for this object. name type partition_type partitions partition_keys ----- ---------- -------------- ---------- -------------- test4 base table roundrobin 1 NULL (1 row affected) partition_name partition_id pages row_count segment create_date ---------------- ------------ ----- --------- ------- ------------------- test4_1399673003 1399673003 2132 524288 default Feb 25 2010 4:04PM Partition_Conditions -------------------- NULL Avg_pages Max_pages Min_pages Ratio(Max/Avg) Ratio(Min/Avg) ----------- ----------- ----------- --------------------------- --------------------------- 2132 2132 2132 1.000000 1.000000 Lock scheme Allpages The attribute 'exp_row_size' is not applicable to tables with allpages lock scheme. The attribute 'concurrency_opt_threshold' is not applicable to tables with allpages lock scheme. exp_row_size reservepagegap fillfactor max_rows_per_page identity_gap ascinserts ------------ -------------- ---------- ----------------- ------------ ----------- 0 0 0 0 0 0 (1 row affected) concurrency_opt_threshold optimistic_index_lock dealloc_first_txtpg ------------------------- --------------------- ------------------- 0 0 0 (return status = 0) |
此时再次执行select count(*) 和select count(1)。查询计划如下：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 | 1> select count(*) from test4 2> go QUERY PLAN FOR STATEMENT 1 (at line 1). STEP 1 The type of query is SELECT. 2 operator(s) under root |ROOT:EMIT Operator (VA = 2) | | |SCALAR AGGREGATE Operator (VA = 1) | | Evaluate Ungrouped COUNT AGGREGATE. | | | | |SCAN Operator (VA = 0) | | | FROM TABLE | | | test4 | | | Index : idx_test4_id | | | Forward Scan. | | | Positioning at index start. | | | Index contains all needed columns. Base table will not be read. | | | Using I/O Size 32 Kbytes for index leaf pages. | | | With MRU Buffer Replacement Strategy for index leaf pages. Parse and Compile Time 0. Adaptive Server cpu time: 0 ms. ----------- 524288 Execution Time 0. Adaptive Server cpu time: 0 ms. Adaptive Server elapsed time: 703 ms. (1 row affected) 1> select count(1) from test4 2> go QUERY PLAN FOR STATEMENT 1 (at line 1). STEP 1 The type of query is SELECT. 2 operator(s) under root |ROOT:EMIT Operator (VA = 2) | | |SCALAR AGGREGATE Operator (VA = 1) | | Evaluate Ungrouped COUNT AGGREGATE. | | | | |SCAN Operator (VA = 0) | | | FROM TABLE | | | test4 | | | Index : idx_test4_id | | | Forward Scan. | | | Positioning at index start. | | | Index contains all needed columns. Base table will not be read. | | | Using I/O Size 32 Kbytes for index leaf pages. | | | With MRU Buffer Replacement Strategy for index leaf pages. Parse and Compile Time 0. Adaptive Server cpu time: 0 ms. ----------- 524288 Execution Time 1. Adaptive Server cpu time: 100 ms. Adaptive Server elapsed time: 93 ms. (1 row affected) |
可以看出查询引擎使用了非聚集索引idx_test4_id ,执行时间明显减少。因为计算行数这个操作对于全表扫描或是非聚集索引的扫描结果是一样的，而相对来说非聚集索引的数据量是肯定会比表的数据量小很多的，同样的做一次全部扫描所花费的IO也就要少很多了。
select count(id) 也是利用了非聚集索引 idx_test4_id。
结论：
count(*)和count(1)执行的效率是完全一样的。
如果是对特定的列做count的话建立这个列的非聚集索引能对count有很大的帮助。
————————————————————————————————————
——— 本文为andkylee个人原创，请在尊重作者劳动成果的前提下进行转载；
——— 转载务必注明原始出处 : http://www.dbainfo.net
——— 关键字： sybase 聚簇索引 非聚簇索引 查询计划 行数 count
————————————————————————————————————