从一个有亿万条数据表里面提起数据的速度

Question

我现在有一个表A,表里面有至少一亿条数据,有10几个字段,但是要命的是,我可能要inner join其他的表找出符合条件的记录这个速度不可想象...
根据表A里面的一个字段entertime,来筛选出今天一天的数据,也就是entertime>datediff(day,-1,getdate())
速度很慢,我现在还没有简历索引,想听听大家还有没有更完美的解决方案,我不是DBA,所以关于DBA的解决方案,我做不了~~~比如现在10点,那么按照常规逻辑就是执行存储过程8:00-9:00;9:00-10:00,执行两次,如果下午就要执行更多次
累计起来时间就太长聚集索引已经建立,但不是这个字段,是DBA建立的,不能更改

Answer 1

entertime,来筛选出今天一天的数据,也就是entertime>datediff(day,-1,getdate()) 改用：convert(varchar(10),entertime,120)=convert(varchar(10),getdate(),120)在entertime字段上建立索引

Answer 2

entertime上面建立索引,
2005的话,可以考虑分区实现,
然后每个小时执行的结果需要保留么,
如果需要保存,建议保存在一张单独的表里面,
还有就是,
原始表中的数据如果历史数据不需要的话,可以做适当的迁移.

Answer 3

INNER JOIN 其实也没啥,关键是要在 JOIN的栏位上建立索引,提高索引的选择性.尽量缩小查询的结果,这种超大表上若是没有正确的索引是超级恐怖的。你们DBA应该会想办法吧，这么大的表，该分区了。

Answer 4

分区需要DBA解决,建立文件组,有没有其他比较方便的解决方案???

Answer 5

该分区了.聚集索引也要有一个了.entertime 这上面要索引也比较好.

Answer 6

PK与PK之间的join 还是 可观.

Answer 7

一亿条记录，就算是10个int字段这表也有几个G了，
可以要求DBA把这个表改成分区表了，这本来就是他的工作，要人提醒才去做，已经算是失职了根据查询条件建立索引是必要的，
另外，查询语句中表的顺序、查询条件中字段的顺序对查询的效率也是有影响的，即使是innser join也一样

Answer 8

  不知道你的主键是什么，如果主键是identity字段，那么就好办，可以用下面方法：
1.估计一下一天insert的数据，比如一天insert 100000 条；
2.取最接近一天insert的数据，比如120000 条数据 插入临时表，select top 120000 * into #tmp from tb order by id desc,再对这个表进行查询，这样就可以对少量数据进行查询，效率不言自明了
  以上只是主键为identity的方案，若主键不是identity，那就需要在entertime上建立索引了

Answer 9

entertime上面建立索引
将表中的部分数据存放到另外一个历史数据表或做适当的迁移.

Answer 10

我也知道分区啊,但是我没有这权利...
DBA估计也不会去做,所以只好自己发解决方案了~

Answer 11

那就手动分割表了.反正只是出report吧....

Answer 12

用sqlserver2005分区表来分割数据
http://blog.csdn.net/jinjazz/archive/2008/06/24/2582235.aspx

Answer 13

那你家的DBA都做什么了,
1亿的数据量还不分区,....
OM+

Answer 14

快做分区表吧
不是太难！
给你个例子 ！ 如果你们不动手不会进步的
-- 进行演示操作前, 先备份, 以便可以在演示完成后, 恢复到原始状态
USE master
-- 备份
BACKUP DATABASE AdventureWorks
TO DISK = 'AdventureWorks.bak'
WITH FORMAT---- 恢复
--RESTORE DATABASE AdventureWorks
-- FROM DISK = 'AdventureWorks.bak'
-- WITH REPLACE
GO--=========================================
-- 转换为分区表
--=========================================
USE AdventureWorks
GO-- 1. 创建分区函数
--    a. 适用于存储历史存档记录的分区表的分区函数
DECLARE @dt datetime
SET @dt = '20020101'
CREATE PARTITION FUNCTION PF_HistoryArchive(datetime)
AS RANGE RIGHT
FOR VALUES(
@dt, 
DATEADD(Year, 1, @dt))--    b. 适用于存储历史记录的分区表的分区函数
--DECLARE @dt datetime
SET @dt = '20030901'
CREATE PARTITION FUNCTION PF_History(datetime)
AS RANGE RIGHT
FOR VALUES(
@dt, 
DATEADD(Month, 1, @dt), DATEADD(Month, 2, @dt), DATEADD(Month, 3, @dt),
DATEADD(Month, 4, @dt), DATEADD(Month, 5, @dt), DATEADD(Month, 6, @dt),
DATEADD(Month, 7, @dt), DATEADD(Month, 8, @dt), DATEADD(Month, 9, @dt),
DATEADD(Month, 10, @dt), DATEADD(Month, 11, @dt), DATEADD(Month, 12, @dt))
GO-- 2. 创建分区架构
--    a. 适用于存储历史存档记录的分区表的分区架构
CREATE PARTITION SCHEME PS_HistoryArchive
AS PARTITION PF_HistoryArchive
TO([PRIMARY], [PRIMARY], [PRIMARY])--    b. 适用于存储历史记录的分区表的分区架构
CREATE PARTITION SCHEME PS_History
AS PARTITION PF_History
TO([PRIMARY], [PRIMARY], 
[PRIMARY], [PRIMARY], [PRIMARY],
[PRIMARY], [PRIMARY], [PRIMARY],
[PRIMARY], [PRIMARY], [PRIMARY],
[PRIMARY], [PRIMARY], [PRIMARY])
GO-- 3. 删除索引
--    a. 删除存储历史存档记录的表中的索引
DROP INDEX Production.TransactionHistoryArchive.IX_TransactionHistoryArchive_ProductID
DROP INDEX Production.TransactionHistoryArchive.IX_TransactionHistoryArchive_ReferenceOrderID_ReferenceOrderLineID--    b. 删除存储历史记录的表中的索引
DROP INDEX Production.TransactionHistory.IX_TransactionHistory_ProductID
DROP INDEX Production.TransactionHistory.IX_TransactionHistory_ReferenceOrderID_ReferenceOrderLineID
GO-- 4. 转换为分区表
--    a. 将存储历史存档记录的表转换为分区表
ALTER TABLE Production.TransactionHistoryArchive
DROP CONSTRAINT PK_TransactionHistoryArchive_TransactionID
WITH(
MOVE TO PS_HistoryArchive(TransactionDate))--    b.将存储历史记录的表转换为分区表
ALTER TABLE Production.TransactionHistory
DROP CONSTRAINT PK_TransactionHistory_TransactionID
WITH(
MOVE TO PS_History(TransactionDate))
GO-- 5. 恢复主键
--    a. 恢复存储历史存档记录的分区表的主键
ALTER TABLE Production.TransactionHistoryArchive
ADD CONSTRAINT PK_TransactionHistoryArchive_TransactionID
PRIMARY KEY CLUSTERED(
TransactionID,
TransactionDate)--    b. 恢复存储历史记录的分区表的主键
ALTER TABLE Production.TransactionHistory
ADD CONSTRAINT PK_TransactionHistory_TransactionID
PRIMARY KEY CLUSTERED(
TransactionID,
TransactionDate)
GO-- 6. 恢复索引
--    a. 恢复存储历史存档记录的分区表的索引
CREATE INDEX IX_TransactionHistoryArchive_ProductID 
ON Production.TransactionHistoryArchive(
ProductID)CREATE INDEX IX_TransactionHistoryArchive_ReferenceOrderID_ReferenceOrderLineID
ON Production.TransactionHistoryArchive(
ReferenceOrderID,
ReferenceOrderLineID)--    b. 恢复存储历史记录的分区表的索引
CREATE INDEX IX_TransactionHistory_ProductID 
ON Production.TransactionHistory(
ProductID)CREATE INDEX IX_TransactionHistory_ReferenceOrderID_ReferenceOrderLineID
ON Production.TransactionHistory(
ReferenceOrderID,
ReferenceOrderLineID)
GO-- 7. 查看分区表的相关信息
SELECT
SchemaName = S.name,
TableName = TB.name,
PartitionScheme = PS.name,
PartitionFunction = PF.name,
PartitionFunctionRangeType = CASE
WHEN boundary_value_on_right = 0 THEN 'LEFT'
ELSE 'RIGHT' END,
PartitionFunctionFanout = PF.fanout,
SchemaID = S.schema_id,
ObjectID = TB.object_id,
PartitionSchemeID = PS.data_space_id,
PartitionFunctionID = PS.function_id
FROM sys.schemas S
INNER JOIN sys.tables TB
ON S.schema_id = TB.schema_id
INNER JOIN sys.indexes IDX
on TB.object_id = IDX.object_id
AND IDX.index_id < 2
INNER JOIN sys.partition_schemes PS
ON PS.data_space_id = IDX.data_space_id
INNER JOIN sys.partition_functions PF
ON PS.function_id = PF.function_id
GO--=========================================
-- 移动分区表数据
--=========================================
-- 1. 为存储历史存档记录的分区表增加分区, 并接受从历史记录分区表移动过来的数据
--    a. 修改分区架构, 增加用以接受新分区的文件组
ALTER PARTITION SCHEME PS_HistoryArchive
NEXT USED [PRIMARY]--    b. 修改分区函数, 增加分区用以接受从历史记录分区表移动过来的数据
DECLARE @dt datetime
SET @dt = '20030901'
ALTER PARTITION FUNCTION PF_HistoryArchive()
SPLIT RANGE(@dt)--    c. 将历史记录表中的过期数据移动到历史存档记录表中
ALTER TABLE Production.TransactionHistory
SWITCH PARTITION 2
TO Production.TransactionHistoryArchive PARTITION $PARTITION.PF_HistoryArchive(@dt)--    d. 将接受到的数据与原来的分区合并
ALTER PARTITION FUNCTION PF_HistoryArchive()
MERGE RANGE(@dt)
GO-- 2. 将存储历史记录的分区表中不包含数据的分区删除, 并增加新的分区以接受新数据
--    a. 合并不包含数据的分区
DECLARE @dt datetime
SET @dt = '20030901'
ALTER PARTITION FUNCTION PF_History()
MERGE RANGE(@dt)--    b.  修改分区架构, 增加用以接受新分区的文件组
ALTER PARTITION SCHEME PS_History
NEXT USED [PRIMARY]--    c. 修改分区函数, 增加分区用以接受新数据
SET @dt = '20041001'
ALTER PARTITION FUNCTION PF_History()
SPLIT RANGE(@dt)
GO
--=========================================
-- 清除历史存档记录中的过期数据
--=========================================
-- 1. 创建用于保存过期的历史存档数据的表
CREATE TABLE Production.TransactionHistoryArchive_2001_temp(
TransactionID int NOT NULL,
ProductID int NOT NULL,
ReferenceOrderID int NOT NULL,
ReferenceOrderLineID int NOT NULL
DEFAULT ((0)),
TransactionDate datetime NOT NULL
DEFAULT (GETDATE()),
TransactionType nchar(1) NOT NULL,
Quantity int NOT NULL,
ActualCost money NOT NULL,
ModifiedDate datetime NOT NULL
DEFAULT (GETDATE()),
CONSTRAINT PK_TransactionHistoryArchive_2001_temp_TransactionID
PRIMARY KEY CLUSTERED(
TransactionID,
TransactionDate)
)-- 2. 将数据从历史存档记录分区表移动到第1步创建的表中
ALTER TABLE Production.TransactionHistoryArchive
SWITCH PARTITION 1
TO Production.TransactionHistoryArchive_2001_temp-- 3. 删除不再包含数据的分区
DECLARE @dt datetime
SET @dt = '20020101'
ALTER PARTITION FUNCTION PF_HistoryArchive()
MERGE RANGE(@dt)-- 4. 修改分区架构, 增加用以接受新分区的文件组
ALTER PARTITION SCHEME PS_HistoryArchive
NEXT USED [PRIMARY]-- 5. 修改分区函数, 增加分区用以接受新数据
SET @dt = '20040101'
ALTER PARTITION FUNCTION PF_HistoryArchive()
SPLIT RANGE(@dt)

Answer 15

这不是什么优化之类能解决的了,而是数据库设计上的问题.一亿条以上记录，本身已经非常庞大的数据了,不分区,不分表,DBA在做什么?/
可以要求DBA把这个表改成分区表了，这本来就是他的工作，要人提醒才去做，已经算是失职了

Answer 16

学习学习，我这里也有张8800多万的表要整理，大小有20G，my god，幸亏这个表只是记录日志，用的极少。