主从表聚集索引设计问题

lcw321321 2009-09-24 10:07:05

以进销存为例，存在订单主表 OrderMaster(BillNO,Billdate,creator,customerId....)
从表 Orderdetail(BillNo,....)

Billno varchar(20) 唯一。
现在的情况是以BillNo 为主键为聚集索引。（每天增加的单1000－3000条左右。）
这样的优点对Join 链接从表有帮助。而且BillNo也是递增的也是不可更改的。符合聚集索引的条件
但是 "Billno 唯一" 这一点不符合聚集索引的条件，可是由于单据都是顺序存取，所以也基本满足可以将附近的数据都取到内存的原则，不知道这样理解对不？由于Billno 是按顺序增加，所以不会分散存到其它页去造成页撕裂，只会在末端新增页。那么填充因子是否可以设置成很大，如98以上。这样设置填充因子是否合理？

但是也考虑到这样的情况，用户查单，一般都是按日期条件查询，然后再在里面查找相应的Billno 。
再读取相应的明细。是否可以这样设计：以日期为聚集索引，然后以Billno 为非聚集索引。毕竟一旦用到主表，一般情况下都会用到从表这样设计索引对JOIN连接是否有影响？

尽管用户经常需要查某一张单的明细。似乎以BillNo 为非聚集索引最好（唯一，一次性取的数量少）。
但是由于非聚集索引的页仅仅指向一个地址，而并非一个值。如果经常使用单号来查数据的话。效能似乎并没有聚集索引效率高。不知道这样理解对不？
现在的疑惑，是用billDate 做聚集索引，还是BILLNO做聚集索引好？由于没有测试环境，所以无法模拟真实环境。希望大家给个意见

...全文

180 12 打赏收藏转发到动态举报

写回复

用AI写文章

12 条回复

切换为时间正序

请发表友善的回复…

发表回复

nzperfect 2010-01-21

打赏
举报

BillNO 设置为聚集索引. Billdate为非聚集索引.

Leshami 2010-01-21

打赏
举报

订单主表 OrderMaster(BillNO,Billdate,creator,customerId....)
从表 Orderdetail(BillNo,....)
主表:
如果主表上按BillNO比按Billdate访问的多建议在BillNO上建立主键，否则在Billdate上建立聚集索引。
当BillNO上建立主键的时候，则Billdate上建立非聚集索引，反之在Billdate建立聚集索引，BillNO建立非聚集索引。
如果考虑到经常有JOIN的话，还是建议在主表BillNO上建立主键，一般情况是这样。
[Quote=引用楼主 lcw321321 的回复:]
但是 "Billno 唯一" 这一点不符合聚集索引的条件，可是由于单据都是顺序存取，所以也基本满足可以将附近的数据都取到内存的原则，不知道这样理解对不？
[/Quote]
从表：
可以在从表上的Billno上建立聚集索引，然后Billno可以与其它键建立非聚集索引。
填充因子可以选择80，便于扩展，但会浪费一部分空间。

navy887 2009-10-01

打赏
举报

使用聚集索引
聚集索引确定表中数据的物理顺序。聚集索引类似于电话簿，后者按姓氏排列数据。由于聚集索引规定数据在表中的物理存储顺序，因此一个表只能包含一个聚集索引。但该索引可以包含多个列（组合索引），就像电话簿按姓氏和名字进行组织一样。

聚集索引对于那些经常要搜索范围值的列特别有效。使用聚集索引找到包含第一个值的行后，便可以确保包含后续索引值的行在物理相邻。例如，如果应用程序执行的一个查询经常检索某一日期范围内的记录，则使用聚集索引可以迅速找到包含开始日期的行，然后检索表中所有相邻的行，直到到达结束日期。这样有助于提高此类查询的性能。同样，如果对从表中检索的数据进行排序时经常要用到某一列，则可以将该表在该列上聚集（物理排序），避免每次查询该列时都进行排序，从而节省成本。

当索引值唯一时，使用聚集索引查找特定的行也很有效率。例如，使用唯一雇员 ID 列 emp_id 查找特定雇员的最快速的方法，是在 emp_id 列上创建聚集索引或 PRIMARY KEY 约束

使用非聚集索引
非聚集索引与课本中的索引类似。数据存储在一个地方，索引存储在另一个地方，索引带有指针指向数据的存储位置。索引中的项目按索引键值的顺序存储，而表中的信息按另一种顺序存储（这可以由聚集索引规定）。如果在表中未创建聚集索引，则无法保证这些行具有任何特定的顺序。

与使用书中索引的方式相似，Microsoft® SQL Server™ 2000 在搜索数据值时，先对非聚集索引进行搜索，找到数据值在表中的位置，然后从该位置直接检索数据。这使非聚集索引成为精确匹配查询的最佳方法，因为索引包含描述查询所搜索的数据值在表中的精确位置的条目。如果基础表使用聚集索引排序，则该位置为聚集键值；否则，该位置为包含行的文件号、页号和槽号的行 ID (RID)。例如，对于在 emp_id 列上有非聚集索引的表，如要搜索其雇员 ID (emp_id)，SQL Server 会在索引中查找这样一个条目，该条目精确列出匹配的 emp_id 列在表中的页和行，然后直接转到该页该行。