返回信息流☆─────────────────────────────────────☆
atian25 (天猪天狗|阿天) 于 (Fri Nov 14 18:30:46 2008) 提到:
CREATE TABLE dbo.TaskResultBaseTestData200805
(
fdiId numeric(10,0) IDENTITY,
fdiTaskId numeric(10,0) NOT NULL,
fdcIsSummary varchar(2) NOT NULL,
fdiFinishFlag numeric(10,0) NULL,
fdiTestLogId numeric(10,0) NULL,
fdiTestTypeId numeric(10,0) NULL,
fdiTestItemId numeric(10,0) NULL,
fdiTestGroupLogId numeric(10,0) NULL,
fdiTaskLogId numeric(10,0) NULL,
fdiResultCode numeric(10,0) NULL,
fddStartTime datetime NULL,
fddFinishTime datetime NULL,
fdiRtdSerialNo1 numeric(10,0) NULL,
fdiRtdSerialNo2 numeric(10,0) NULL,
fdiRtdSerialNo3 numeric(10,0) NULL,
fdcTelNo1 varchar(20) NULL,
fdcTelNo2 varchar(20) NULL,
fdcTelNo3 varchar(20) NULL,
time1 datetime NULL,
time2 datetime NULL,
time3 datetime NULL,
time4 datetime NULL,
time5 datetime NULL,
time6 datetime NULL,
time7 datetime NULL,
time8 datetime NULL,
time9 datetime NULL,
time10 datetime NULL,
temp1 varchar(20) NULL,
temp2 varchar(20) NULL,
temp3 varchar(20) NULL,
temp4 varchar(20) NULL,
temp5 varchar(20) NULL
)
LOCK DATAROWS
WITH IDENTITY_GAP=1
go
IF OBJECT_ID('dbo.TaskResultBaseTestData200805') IS NOT NULL
PRINT '<<< CREATED TABLE dbo.TaskResultBaseTestData200805 >>>'
ELSE
PRINT '<<< FAILED CREATING TABLE dbo.TaskResultBaseTestData200805 >>>'
go
EXEC sp_cachestrategy 'elvis_gz','dbo.TaskResultBaseTestData200805','table only','mru','off'
go
CREATE NONCLUSTERED INDEX index_fdiTestTypeId
ON dbo.TaskResultBaseTestData200805(fdcIsSummary,fdiTestTypeId,fddStartTime,fddFinishTime,fdiResultCode)
go
IF EXISTS (SELECT * FROM sysindexes WHERE id=OBJECT_ID('dbo.TaskResultBaseTestData200805') AND name='index_fdiTestTypeId')
PRINT '<<< CREATED INDEX dbo.TaskResultBaseTestData200805.index_fdiTestTypeId >>>'
ELSE
PRINT '<<< FAILED CREATING INDEX dbo.TaskResultBaseTestData200805.index_fdiTestTypeId >>>'
go
CREATE NONCLUSTERED INDEX index_fdiTestItemId
ON dbo.TaskResultBaseTestData200805(fdcIsSummary,fdiTestItemId,fddStartTime,fddFinishTime,fdiResultCode)
go
IF EXISTS (SELECT * FROM sysindexes WHERE id=OBJECT_ID('dbo.TaskResultBaseTestData200805') AND name='index_fdiTestItemId')
PRINT '<<< CREATED INDEX dbo.TaskResultBaseTestData200805.index_fdiTestItemId >>>'
ELSE
PRINT '<<< FAILED CREATING INDEX dbo.TaskResultBaseTestData200805.index_fdiTestItemId >>>'
go
CREATE NONCLUSTERED INDEX index_fdiRtdSerialNo1
ON dbo.TaskResultBaseTestData200805(fdiRtdSerialNo1)
go
IF EXISTS (SELECT * FROM sysindexes WHERE id=OBJECT_ID('dbo.TaskResultBaseTestData200805') AND name='index_fdiRtdSerialNo1')
PRINT '<<< CREATED INDEX dbo.TaskResultBaseTestData200805.index_fdiRtdSerialNo1 >>>'
ELSE
PRINT '<<< FAILED CREATING INDEX dbo.TaskResultBaseTestData200805.index_fdiRtdSerialNo1 >>>'
go
CREATE NONCLUSTERED INDEX index_fdiRtdSerialNo2
ON dbo.TaskResultBaseTestData200805(fdiRtdSerialNo2)
go
IF EXISTS (SELECT * FROM sysindexes WHERE id=OBJECT_ID('dbo.TaskResultBaseTestData200805') AND name='index_fdiRtdSerialNo2')
PRINT '<<< CREATED INDEX dbo.TaskResultBaseTestData200805.index_fdiRtdSerialNo2 >>>'
ELSE
PRINT '<<< FAILED CREATING INDEX dbo.TaskResultBaseTestData200805.index_fdiRtdSerialNo2 >>>'
go
CREATE NONCLUSTERED INDEX index_fdiRtdSerialNo3
ON dbo.TaskResultBaseTestData200805(fdiRtdSerialNo3)
go
IF EXISTS (SELECT * FROM sysindexes WHERE id=OBJECT_ID('dbo.TaskResultBaseTestData200805') AND name='index_fdiRtdSerialNo3')
PRINT '<<< CREATED INDEX dbo.TaskResultBaseTestData200805.index_fdiRtdSerialNo3 >>>'
ELSE
PRINT '<<< FAILED CREATING INDEX dbo.TaskResultBaseTestData200805.index_fdiRtdSerialNo3 >>>'
go
CREATE NONCLUSTERED INDEX index_fdcTelNo1
ON dbo.TaskResultBaseTestData200805(fdcTelNo1)
go
IF EXISTS (SELECT * FROM sysindexes WHERE id=OBJECT_ID('dbo.TaskResultBaseTestData200805') AND name='index_fdcTelNo1')
PRINT '<<< CREATED INDEX dbo.TaskResultBaseTestData200805.index_fdcTelNo1 >>>'
ELSE
PRINT '<<< FAILED CREATING INDEX dbo.TaskResultBaseTestData200805.index_fdcTelNo1 >>>'
go
CREATE NONCLUSTERED INDEX index_fdcTelNo2
ON dbo.TaskResultBaseTestData200805(fdcTelNo2)
go
IF EXISTS (SELECT * FROM sysindexes WHERE id=OBJECT_ID('dbo.TaskResultBaseTestData200805') AND name='index_fdcTelNo2')
PRINT '<<< CREATED INDEX dbo.TaskResultBaseTestData200805.index_fdcTelNo2 >>>'
ELSE
PRINT '<<< FAILED CREATING INDEX dbo.TaskResultBaseTestData200805.index_fdcTelNo2 >>>'
go
CREATE NONCLUSTERED INDEX index_fdcTelNo3
ON dbo.TaskResultBaseTestData200805(fdcTelNo3)
go
IF EXISTS (SELECT * FROM sysindexes WHERE id=OBJECT_ID('dbo.TaskResultBaseTestData200805') AND name='index_fdcTelNo3')
PRINT '<<< CREATED INDEX dbo.TaskResultBaseTestData200805.index_fdcTelNo3 >>>'
ELSE
PRINT '<<< FAILED CREATING INDEX dbo.TaskResultBaseTestData200805.index_fdcTelNo3 >>>'
go
CREATE NONCLUSTERED INDEX index_fddStartTime
ON dbo.TaskResultBaseTestData200805(fddStartTime,fddFinishTime)
go
IF EXISTS (SELECT * FROM sysindexes WHERE id=OBJECT_ID('dbo.TaskResultBaseTestData200805') AND name='index_fddStartTime')
PRINT '<<< CREATED INDEX dbo.TaskResultBaseTestData200805.index_fddStartTime >>>'
ELSE
PRINT '<<< FAILED CREATING INDEX dbo.TaskResultBaseTestData200805.index_fddStartTime >>>'
go
CREATE NONCLUSTERED INDEX index_fdiResultCode
ON dbo.TaskResultBaseTestData200805(fdiResultCode)
go
IF EXISTS (SELECT * FROM sysindexes WHERE id=OBJECT_ID('dbo.TaskResultBaseTestData200805') AND name='index_fdiResultCode')
PRINT '<<< CREATED INDEX dbo.TaskResultBaseTestData200805.index_fdiResultCode >>>'
ELSE
PRINT '<<< FAILED CREATING INDEX dbo.TaskResultBaseTestData200805.index_fdiResultCode >>>'
go
CREATE CLUSTERED INDEX clu_index_fddStartTime200805
ON dbo.TaskResultBaseTestData200805(fddStartTime DESC)
WITH ALLOW_DUP_ROW
go
IF EXISTS (SELECT * FROM sysindexes WHERE id=OBJECT_ID('dbo.TaskResultBaseTestData200805') AND name='clu_index_fddStartTime200805')
PRINT '<<< CREATED INDEX dbo.TaskResultBaseTestData200805.clu_index_fddStartTime200805 >>>'
ELSE
PRINT '<<< FAILED CREATING INDEX dbo.TaskResultBaseTestData200805.clu_index_fddStartTime200805 >>>'
go
CREATE NONCLUSTERED INDEX index_fdiTestLogId
ON dbo.TaskResultBaseTestData200805(fdiTestLogId DESC)
go
IF EXISTS (SELECT * FROM sysindexes WHERE id=OBJECT_ID('dbo.TaskResultBaseTestData200805') AND name='index_fdiTestLogId')
PRINT '<<< CREATED INDEX dbo.TaskResultBaseTestData200805.index_fdiTestLogId >>>'
ELSE
PRINT '<<< FAILED CREATING INDEX dbo.TaskResultBaseTestData200805.index_fdiTestLogId >>>'
go
CREATE NONCLUSTERED INDEX index_fdiTestTypeId2
ON dbo.TaskResultBaseTestData200805(fdiTestTypeId)
go
IF EXISTS (SELECT * FROM sysindexes WHERE id=OBJECT_ID('dbo.TaskResultBaseTestData200805') AND name='index_fdiTestTypeId2')
PRINT '<<< CREATED INDEX dbo.TaskResultBaseTestData200805.index_fdiTestTypeId2 >>>'
ELSE
PRINT '<<< FAILED CREATING INDEX dbo.TaskResultBaseTestData200805.index_fdiTestTypeId2 >>>'
go
CREATE NONCLUSTERED INDEX index_fdiTestGroupLogId
ON dbo.TaskResultBaseTestData200805(fdiTestGroupLogId DESC)
go
IF EXISTS (SELECT * FROM sysindexes WHERE id=OBJECT_ID('dbo.TaskResultBaseTestData200805') AND name='index_fdiTestGroupLogId')
PRINT '<<< CREATED INDEX dbo.TaskResultBaseTestData200805.index_fdiTestGroupLogId >>>'
ELSE
PRINT '<<< FAILED CREATING INDEX dbo.TaskResultBaseTestData200805.index_fdiTestGroupLogId >>>'
go
-------------------------------------------
select T.fdiId, T.fdiTestLogId, T.fdiTestTypeId, T.fdiTaskLogId,T.fdiTaskId, T.fdiTestGroupLogId, T.fdiTestItemId, T.fdiResultCode, T.fddStartTime,T.fddFinishTime,T.fdiRtdSerialNo1,
T.fdiRtdSerialNo2, T.fdiRtdSerialNo3, T.fdcTelNo1,T.fdcTelNo2, T.fdcTelNo3, T.time1, T.time2, T.time3, T.time4, T.time5, T.time6, T.time7,T.time8, T.time9, T.time10, T.temp1, T.temp2, T.temp3, T.temp4, T.temp5
FROM TaskResultBaseTestData200805 T
where 1=1 and T.fddStartTime between '2008-05-01 16:45:00' and '2008-05-02 16:45:00' and T.fdiTestTypeId =1
-------------------------------------------
数据库是sybase,数据量100w,上面的sql查询出1.1w条,在dbms中居然用了12s(我用DBArtisan)
☆─────────────────────────────────────☆
atian25 (天猪天狗|阿天) 于 (Fri Nov 14 18:47:55 2008) 提到:
top 100 都用了2s,很不合理.
执行计划如下:
*******************************
Beginning selection of qualifying indexes for table 'TaskResultBaseTestData200805',
correlation name 'T', varno = 0, objectid 1323200783.
The table (Datarows) has 973271 rows, 115268 pages,
The table's Data Page Cluster Ratio 0.999990
Table scan cost is 973271 rows, 127757 pages,
using no data prefetch (size 2K I/O),
in data cache 'default data cache' (cacheid 0) with LRU replacement
Selecting best index for the SEARCH CLAUSE:
TaskResultBaseTestData200805.fddStartTime <= May 2 2008 4:45:00:000PM
TaskResultBaseTestData200805.fddStartTime >= May 1 2008 4:45:00:000PM
Estimated selectivity for fddStartTime,
selectivity = 0.018402, upper limit = 0.052631.
Estimating selectivity of index 'index_fddStartTime', indid 10
scan selectivity 0.018402, filter selectivity 0.018402
17910 rows, 3953 pages, index height 3,
Data Row Cluster Ratio 0.912482,
Index Page Cluster Ratio 0.998685,
Data Page Cluster Ratio 0.946087
Estimating selectivity of index 'clu_index_fddStartTime200805', indid 12
scan selectivity 0.018402, filter selectivity 0.018402
17910 rows, 2630 pages, index height 2,
Data Row Cluster Ratio 0.989110,
Index Page Cluster Ratio 0.998197,
Data Page Cluster Ratio 0.946751
The best qualifying index is 'clu_index_fddStartTime200805' (indid 12)
costing 2630 pages,
with an estimate of 17910 rows to be returned per scan of the table,
using no index prefetch (size 2K I/O) on leaf pages,
in index cache 'default data cache' (cacheid 0) with LRU replacement
using no data prefetch (size 2K I/O),
in data cache 'default data cache' (cacheid 0) with LRU replacement
Search argument selectivity is 0.018402.
*******************************
QUERY IS CONNECTED
Number of tables in join: 1
Number of tables considered at a time: 1
Table count setting: 0 (default value used)
0 -
NEW PLAN (total cost = 51538):
varno=0 (TaskResultBaseTestData200805) indexid=12 (clu_index_fddStartTime200805)
path=0x0x8ac8e000 pathtype=sclause
method=NESTED ITERATION
scanthreads=1
outerrows=1 rows=17910 joinsel=1.000000 scanpgs=2630
index_prefetch=NO index_iosize=2 index_bufreplace=LRU
data_prefetch=NO data_iosize=2 data_bufreplace=LRU
scanlio=2630 scanpio=2571
corder=11
TOTAL # PERMUTATIONS: 1
TOTAL # PLANS CONSIDERED: 2
CACHE USED BY THIS PLAN:
CacheID = 0: (2K) 2571 (4K) 0 (8K) 0 (16K) 0
FINAL PLAN (total cost = 51538):
varno=0 (TaskResultBaseTestData200805) indexid=12 (clu_index_fddStartTime200805)
path=0x0x8ac8e000 pathtype=sclause
method=NESTED ITERATION
scanthreads=1
outerrows=1 rows=17910 joinsel=1.000000 scanpgs=2630
index_prefetch=NO index_iosize=2 index_bufreplace=LRU
data_prefetch=NO data_iosize=2 data_bufreplace=LRU
scanlio=2630 scanpio=2571
corder=11
QUERY PLAN FOR STATEMENT 1 (at line 1).
STEP 1
The type of query is SELECT.
FROM TABLE
TaskResultBaseTestData200805
T
Nested iteration.
Using Clustered Index.
Index : clu_index_fddStartTime200805
Forward scan.
Positioning by key.
Keys are:
fddStartTime DESC
Using I/O Size 2 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.
☆─────────────────────────────────────☆
coolfantasy (Cool) 于 (Fri Nov 14 19:52:56 2008) 提到:
跟包博士讨论了 结论是: 没有结论....
☆─────────────────────────────────────☆
wks (cloverprince) 于 (Fri Nov 14 22:19:16 2008) 提到:
oh my dog
☆─────────────────────────────────────☆
atian25 (天猪天狗|阿天) 于 (Fri Nov 14 23:16:16 2008) 提到:
谁本地建个帮我测下...
mysql什么都成
☆─────────────────────────────────────☆
flyingmiao (amiao) 于 (Fri Nov 14 23:58:00 2008) 提到:
建了也没数据啊。。
【 在 atian25 的大作中提到: 】
: 谁本地建个帮我测下...
: mysql什么都成
☆─────────────────────────────────────☆
atian25 (天猪天狗|阿天) 于 (Sat Nov 15 00:08:42 2008) 提到:
自己随机造咯...
☆─────────────────────────────────────☆
atian25 (天猪天狗|阿天) 于 (Sat Nov 15 09:45:35 2008) 提到:
数据:
http://41.dc.ftn.qq.com/ftn_handler/97867a0dcae040bbb6f8fa1246075d8a03fd1b76888a63a56f84f5bae2f5620969ce3b455048bbe2ab227ada79b5f2bb5808cbbc5c749fbfa43685fbc44e57c9/data.rar?k=0538393886c0a59f59b3353813340a4d090c5a5a510d5903145c0e0c03190c045b5e145a00550d4f09005c0957025d520009080f353c3806584c581647554a62
(提取码 9898548b)
☆─────────────────────────────────────☆
flyingmiao (amiao) 于 (Sat Nov 15 09:47:56 2008) 提到:
好，待我装个MYSQL先。。。。。
【 在 atian25 的大作中提到: 】
: 数据:
: http://41.dc.ftn.qq.com/ftn_handler/97867a0dcae040bbb6f8fa1246075d8a03fd1b76888a63a56f84f5bae2f5620969ce3b455048bbe2ab227ada79b5f2bb5808cbbc5c749fbfa43685fbc44e57c9/data.rar?k=0538393886c0a59f59b3353813340a4d090c5a5a510d5903145c0e0c03190c045b5e145a00550d4f09005c0957025d520009080f353c3806584c581647554a62
: (提取码 9898548b)
☆─────────────────────────────────────☆
atian25 (天猪天狗|阿天) 于 (Sat Nov 15 09:52:42 2008) 提到:
随便你用啥数据库...
☆─────────────────────────────────────☆
witkey (Lost) 于 (Sat Nov 15 17:20:41 2008) 提到:
如果统计时间的精确性 要求不高，而且查询条件比较固定的话，可以新做一张表，每天12：00，24：00定时把你需要统计结果的数据预先读取放到新表里面去。
展现统计结果的时候，直接从新表中读数据，这样应该很快。
说错了 别拍我[em40]
【 在 atian25 的大作中提到: 】
: CREATE TABLE dbo.TaskResultBaseTestData200805
: (
: fdiId numeric(10,0) IDENTITY,
: ...................
☆─────────────────────────────────────☆
sunmoonstar (摔锅啦) 于 (Sat Nov 15 17:47:11 2008) 提到:
感觉这种规模的数据不到1秒应该在1秒以内
创建index是否会失败了？
☆─────────────────────────────────────☆
atian25 (天猪天狗|阿天) 于 (Sat Nov 15 19:01:02 2008) 提到:
我上面说的已经是我本机的调试数据了.
为了排除一切可能的影响,只配置了唯一的一个sybase服务及库,也是唯一的一个表,所以不存在锁竞争等影响。tempdb只设置了512M,应该是够了,也配置了专门的cache,100M
而且该表也只设置了fddStartTime这一个聚集索引。
你说的这种方式是基础的,肯定考虑过了.实际情况是现在一个月90w的数据,已经做了月分表了.我觉得没理由这么慢,以前做的系统都是日分表的,一天都200w的数据,都没感觉这么慢.
我现在只是想确定下,同样的数据量,同样的查询下,你们的数据库会需要多少秒.
【 在 witkey 的大作中提到: 】
: 如果统计时间的精确性 要求不高，而且查询条件比较固定的话，可以新做一张表，每天12：00，24：00定时把你需要统计结果的数据预先读取放到新表里面去。
: 展现统计结果的时候，直接从新表中读数据，这样应该很快。
: 说错了 别拍我[em40]
☆─────────────────────────────────────☆
atian25 (天猪天狗|阿天) 于 (Sat Nov 15 19:02:27 2008) 提到:
我也觉得不应该超过1s.
可以排除读写竞争,锁竞争,cpu竞争等因素了.
表本身也是没问题的,都做过排除性的测试了.
【 在 sunmoonstar 的大作中提到: 】
: 感觉这种规模的数据不到1秒应该在1秒以内
: 创建index是否会失败了？
☆─────────────────────────────────────☆
coolfantasy (Cool) 于 (Wed Nov 26 10:54:25 2008) 提到:
RP。。。
这是一条镜像帖。来源：北邮人论坛 / soft-design / #34591同步于 2009/5/24
SoftDesign机器人发帖
[合集] 【COOL IN】数据库查询优化
FadeToBlack
2009/5/24镜像同步0 回复
订阅后，新回复会通过你的通知中心匿名送达。
0 条回复
暂无回复 · 你可以订阅本帖等待新回复。