100 SQL2005跪求9亿数据存储方案~~~
数据库SQL2005
我有一个数据表 4个字段 4个字段都是浮点型 大概9亿记录 纯查询 不添加 查询的时候需要两个字段联合查询 两个字段联合才能唯一 是联合主键 查询的这个两个字段有规律是递增的
问题:
1. 9亿纯查询放一个表会不会性能很差?
2. 9亿数据查询 服务器需要多大的内存才能迅速?
3. 是按 两个字段递增规律存放在不同表好还是全部一起分区好?
4. 怎么分区?
各位大大跪求中.....
[解决办法]
第一个问题：分区是必须的，我想知道你的数据是什么？比如你可以按月来分区，按地区来分区，如果不知道你存放什么，很难分区，这部分请给出数据内容。9亿放一个表，有索引也快不了哪里去。
第二个问题：我记得当初我做BI的时候，2亿数据单表，不过列比你的多，需要40G空间，估计你的那个差不多，预计大概也要60G以上。
第三个问题：没必要这样分，直接放在分区就好了。分区自动会做处理。
第四个问题：这个和第一个问题合在一起，先要知道你的数据成分，然后才能确定分区方案，另外，联机丛书肯定有例子的。
补充一下，其实很少应用会存放9亿【活动】数据，很多都是【历史】、【静态】数据，对于这部分，在硬件层面，要把分区放到单独的数据文件中，然后把文件放到读性能高的磁盘上。缓解I/O压力。
另外，索引要考虑好，不过由于是2005，有些特性用不上，重点还是要看你的数据组成。
[解决办法]
--------------------------------------这里尽可能模拟楼主的情况-----------------------------------
--------------------------------------一切以说明问题为主，不钻牛角尖-----------------------------------------
-----------------------------------一切以数据来说明问题-----------------------------------------
--创建分区函数，300W一个分区，计划插入1500W数据做测试
create partition function MyPartitonFun1(int)
as range right for values(3000000,6000000,9000000,12000000,15000000);
--创建分区方案
create partition scheme MyPartitonScheme1
as partition MyPartitonFun1
all to ([primary])
--建表，分区表
create table TestWithPartition (
ID1 int identity(1,1),
ID2 decimal(18,5),
Col3 decimal(18,5),
Col4 decimal(18,5)
)on MyPartitonScheme1(ID1)
--建表，没有分区的表
create table TestWithNoPartition (
ID1 int identity(1,1),
ID2 decimal(18,5),
Col3 decimal(18,5),
Col4 decimal(18,5)
)
--写入数据，温馨提示，
--这里的ID1的意图是为了模拟楼主所说的“有规律增长”，模拟楼主的情况，并不是特意搞的自增列
--下班开始搞，插入到700W多的时候sqlserver报错，神马.net freamwork异常，之后重启sqlserver继续写
--总数据是1400W多，将近1500W
declare @i int
set @i=0
while @i<15000000
begin
insert into TestWithPartition(Col3,Col4) values (rand()*10000000000,rand()*10000000000)
insert into TestWithNoPartition(Col3,Col4) values (rand()*10000000000,rand()*10000000000)
set @i=@i+1
end
--更新ID2，模拟楼主的情况
update TestWithPartition set ID2=ID1+rand() where ID2 is null
update TestWithNoPartition set ID2=ID1+rand() where ID2 is null
--创建聚集索引
create clustered index index_1 on TestWithPartition (ID1)
create clustered index index_1 on TestWithNoPartition (ID1)
--查询分区表的数据分布
select convert(varchar(50), ps.name) as partition_scheme,
p.partition_number,
convert(varchar(10), ds2.name) as filegroup,
convert(varchar(19), isnull(v.value, ''), 120) as range_boundary,
str(p.rows, 9) as rows
from sys.indexes i
join sys.partition_schemes ps on i.data_space_id = ps.data_space_id
join sys.destination_data_spaces dds
on ps.data_space_id = dds.partition_scheme_id
join sys.data_spaces ds2 on dds.data_space_id = ds2.data_space_id
join sys.partitions p on dds.destination_id = p.partition_number
and p.object_id = i.object_id and p.index_id = i.index_id
join sys.partition_functions pf on ps.function_id = pf.function_id
LEFT JOIN sys.Partition_Range_values v on pf.function_id = v.function_id
and v.boundary_id = p.partition_number - pf.boundary_value_on_right
WHERE i.object_id = object_id('TestWithPartition')
and i.index_id in (0, 1)
order by p.partition_number
partition_schemepartition_numberfilegrouprange_boundaryrows
MyPartitonScheme11PRIMARY2999999
MyPartitonScheme12PRIMARY30000003000000
MyPartitonScheme13PRIMARY60000003000000
MyPartitonScheme14PRIMARY90000003000000
MyPartitonScheme15PRIMARY120000002191418
--开启I0，执行计划
set statistics io on
set statistics profile on
--清理缓存
dbcc dropcleanbuffers
select * from TestWithPartition where ID1='123456'
---从IO来看，确实差别不大啊，300W一个分区
(1 行受影响)
表 'TestWithPartition'。扫描计数 1，逻辑读取 3 次，物理读取 2 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
select * from TestWithNoPartition where ID1='123456'
(1 行受影响)
表 'TestWithNoPartition'。扫描计数 1，逻辑读取 3 次，物理读取 2 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
---------------------------------------------------这不由得使我想到了sqlserver的B树索引结构------------------------
