s*g
2 楼
比如说设计一个银行的数据库,
有一个table叫transaction(transactionID, accountID, date, ...)
-------------
这个表显然经常需要更新,主要是一条一条的insert操作
这样看来,为了performance,尽量减少index,那么不应该在accountID上建index
不然,每次insert都需要更新这个index(?)
然而,用户(和银行)也需要查询某账户近期的所有transactions
这样似乎又需要给accountID建一个index
想请教一下实际设计中是怎么做的?谢谢!
有一个table叫transaction(transactionID, accountID, date, ...)
-------------
这个表显然经常需要更新,主要是一条一条的insert操作
这样看来,为了performance,尽量减少index,那么不应该在accountID上建index
不然,每次insert都需要更新这个index(?)
然而,用户(和银行)也需要查询某账户近期的所有transactions
这样似乎又需要给accountID建一个index
想请教一下实际设计中是怎么做的?谢谢!
K*y
3 楼
不懂,觉得挺好看的
t*n
4 楼
Which database are you using? If it is SQL Server, it looks like accountID+
date is a good candidate for the clustered index. In SQL Server, a clustered
index is actually the table itself, so have no fear of inserting.
【在 s******g 的大作中提到】
: 比如说设计一个银行的数据库,
: 有一个table叫transaction(transactionID, accountID, date, ...)
: -------------
: 这个表显然经常需要更新,主要是一条一条的insert操作
: 这样看来,为了performance,尽量减少index,那么不应该在accountID上建index
: 不然,每次insert都需要更新这个index(?)
: 然而,用户(和银行)也需要查询某账户近期的所有transactions
: 这样似乎又需要给accountID建一个index
: 想请教一下实际设计中是怎么做的?谢谢!
date is a good candidate for the clustered index. In SQL Server, a clustered
index is actually the table itself, so have no fear of inserting.
【在 s******g 的大作中提到】
: 比如说设计一个银行的数据库,
: 有一个table叫transaction(transactionID, accountID, date, ...)
: -------------
: 这个表显然经常需要更新,主要是一条一条的insert操作
: 这样看来,为了performance,尽量减少index,那么不应该在accountID上建index
: 不然,每次insert都需要更新这个index(?)
: 然而,用户(和银行)也需要查询某账户近期的所有transactions
: 这样似乎又需要给accountID建一个index
: 想请教一下实际设计中是怎么做的?谢谢!
z*3
6 楼
这个个人感觉还是一个比较复杂的问题,如果一般的index的话是指b+ tree,并不是每
次insert都会产生balance tree的操作。只有某个node全充满的时候才会,进行
balance tree操作,或许还有left rotation和right rotation的操作。
如果使用mysql的话,可以设定经常插入的数据库的engine是inndo,不要每次insert都
commit,先insert若干条,再commit,然后用master-slave replication把数据同步到
,经常查询的一个数据库,而这个数据库的engine是myisam。
oracle的话,个人感觉可以对date,建立一个bitmap index,这样的话,其实不会有
tree的运算,每次只是没有操作或是更新一下某个gap的up或low bound。然后建一个
job,每隔一段时间dbms_stats.gather_stats,这样的话,可以尽快更新execution
plan,达到更好的效果。
次insert都会产生balance tree的操作。只有某个node全充满的时候才会,进行
balance tree操作,或许还有left rotation和right rotation的操作。
如果使用mysql的话,可以设定经常插入的数据库的engine是inndo,不要每次insert都
commit,先insert若干条,再commit,然后用master-slave replication把数据同步到
,经常查询的一个数据库,而这个数据库的engine是myisam。
oracle的话,个人感觉可以对date,建立一个bitmap index,这样的话,其实不会有
tree的运算,每次只是没有操作或是更新一下某个gap的up或low bound。然后建一个
job,每隔一段时间dbms_stats.gather_stats,这样的话,可以尽快更新execution
plan,达到更好的效果。
w*e
8 楼
不太同意
要是clustered index的话, 经常insert会产生fragmentation的(internal and
external), 对吧?
所以才有rebuild or reorganize index.....
(不是"no fear of insertting")
clustered
【在 t****n 的大作中提到】
: Which database are you using? If it is SQL Server, it looks like accountID+
: date is a good candidate for the clustered index. In SQL Server, a clustered
: index is actually the table itself, so have no fear of inserting.
要是clustered index的话, 经常insert会产生fragmentation的(internal and
external), 对吧?
所以才有rebuild or reorganize index.....
(不是"no fear of insertting")
clustered
【在 t****n 的大作中提到】
: Which database are you using? If it is SQL Server, it looks like accountID+
: date is a good candidate for the clustered index. In SQL Server, a clustered
: index is actually the table itself, so have no fear of inserting.
a*p
11 楼
真好!狂赞!
s*e
12 楼
bitmap index on date field, if date is really timestamp, that is abusing
bitmap index.
bitmap index.
B*g
14 楼
minimum index != no index
b-tree index is ok
【在 s******g 的大作中提到】
: 比如说设计一个银行的数据库,
: 有一个table叫transaction(transactionID, accountID, date, ...)
: -------------
: 这个表显然经常需要更新,主要是一条一条的insert操作
: 这样看来,为了performance,尽量减少index,那么不应该在accountID上建index
: 不然,每次insert都需要更新这个index(?)
: 然而,用户(和银行)也需要查询某账户近期的所有transactions
: 这样似乎又需要给accountID建一个index
: 想请教一下实际设计中是怎么做的?谢谢!
b-tree index is ok
【在 s******g 的大作中提到】
: 比如说设计一个银行的数据库,
: 有一个table叫transaction(transactionID, accountID, date, ...)
: -------------
: 这个表显然经常需要更新,主要是一条一条的insert操作
: 这样看来,为了performance,尽量减少index,那么不应该在accountID上建index
: 不然,每次insert都需要更新这个index(?)
: 然而,用户(和银行)也需要查询某账户近期的所有transactions
: 这样似乎又需要给accountID建一个index
: 想请教一下实际设计中是怎么做的?谢谢!
j*n
15 楼
that's why there is another thing called fill factor. which can reduce
fragmentation / page split problem.
for me, i'd use combination of transactionID, accountID, and date for the
clustered index. why? most likely transactionID is incremental. it
guarantees new row is appended into the last data page. what about "hot spot
"? it is not a problem for SQL Server 7.0 and later versions if less 1,000
inserts per second.
【在 w*******e 的大作中提到】
: 不太同意
: 要是clustered index的话, 经常insert会产生fragmentation的(internal and
: external), 对吧?
: 所以才有rebuild or reorganize index.....
: (不是"no fear of insertting")
:
: clustered
fragmentation / page split problem.
for me, i'd use combination of transactionID, accountID, and date for the
clustered index. why? most likely transactionID is incremental. it
guarantees new row is appended into the last data page. what about "hot spot
"? it is not a problem for SQL Server 7.0 and later versions if less 1,000
inserts per second.
【在 w*******e 的大作中提到】
: 不太同意
: 要是clustered index的话, 经常insert会产生fragmentation的(internal and
: external), 对吧?
: 所以才有rebuild or reorganize index.....
: (不是"no fear of insertting")
:
: clustered
w*e
16 楼
恩, fill factor能"减少"fragmentation, 但是
这是银行的transaction, 你认为多少fill factor合适?
spot
【在 j*****n 的大作中提到】
: that's why there is another thing called fill factor. which can reduce
: fragmentation / page split problem.
: for me, i'd use combination of transactionID, accountID, and date for the
: clustered index. why? most likely transactionID is incremental. it
: guarantees new row is appended into the last data page. what about "hot spot
: "? it is not a problem for SQL Server 7.0 and later versions if less 1,000
: inserts per second.
这是银行的transaction, 你认为多少fill factor合适?
spot
【在 j*****n 的大作中提到】
: that's why there is another thing called fill factor. which can reduce
: fragmentation / page split problem.
: for me, i'd use combination of transactionID, accountID, and date for the
: clustered index. why? most likely transactionID is incremental. it
: guarantees new row is appended into the last data page. what about "hot spot
: "? it is not a problem for SQL Server 7.0 and later versions if less 1,000
: inserts per second.
B*g
17 楼
不明白。how "guarantees new row is appended into the last data page" will
imporve the search based on accountID?
spot
【在 j*****n 的大作中提到】
: that's why there is another thing called fill factor. which can reduce
: fragmentation / page split problem.
: for me, i'd use combination of transactionID, accountID, and date for the
: clustered index. why? most likely transactionID is incremental. it
: guarantees new row is appended into the last data page. what about "hot spot
: "? it is not a problem for SQL Server 7.0 and later versions if less 1,000
: inserts per second.
imporve the search based on accountID?
spot
【在 j*****n 的大作中提到】
: that's why there is another thing called fill factor. which can reduce
: fragmentation / page split problem.
: for me, i'd use combination of transactionID, accountID, and date for the
: clustered index. why? most likely transactionID is incremental. it
: guarantees new row is appended into the last data page. what about "hot spot
: "? it is not a problem for SQL Server 7.0 and later versions if less 1,000
: inserts per second.
B*g
20 楼
I thought when create clustered index on TransID, AcctID, Date, the data
will stored based on TransID (I believe it is unique) order, so nothing
ordered based on AcctID.
【在 j*****n 的大作中提到】
: less fragmentation will improve performance, right?
: accountid is in the clustered index, index seek will perform.
will stored based on TransID (I believe it is unique) order, so nothing
ordered based on AcctID.
【在 j*****n 的大作中提到】
: less fragmentation will improve performance, right?
: accountid is in the clustered index, index seek will perform.
j*n
21 楼
hmm... you right. however, sql serve can still use B tree instead of hash
tree.
if you doubt with that, we can go for non-clustered index with accountid +
date with fill factor = 80%; then reindex nightly. should work.
【在 B*****g 的大作中提到】
: I thought when create clustered index on TransID, AcctID, Date, the data
: will stored based on TransID (I believe it is unique) order, so nothing
: ordered based on AcctID.
tree.
if you doubt with that, we can go for non-clustered index with accountid +
date with fill factor = 80%; then reindex nightly. should work.
【在 B*****g 的大作中提到】
: I thought when create clustered index on TransID, AcctID, Date, the data
: will stored based on TransID (I believe it is unique) order, so nothing
: ordered based on AcctID.
相关阅读
关于M$ Access文件的问题how to remove oracle completely数据库方向的UNIV。?the most stupid questionoracle client side programSQL Server 2000: 计算时间差怎样精确到小时[转载] visual basic 6.0 问题,谢谢帮忙Oracle Certificate怎样从网页上往 英文access数据库输入中文请问各位大虾,哪里可以downloand MS Sqlserver 2000的英文版,多谢[转载] 关于网站数据库的一个问题how's job market for Oracle oca/ocp?disk access and DB2drop a table求助:sql server 2000, 这句话怎么写?一个SQL query的问题SQL 2000 怎么对一个database进行audit?help on oemappIs there anyone familiar with data warehouse?help about SQL for ACCESS