SQL 千万级大数据，按年份分表并删除原表数据

原有数据量主表38w，子表1200w

最终分表成为主表为当年数据，将2015,2016分表存储

--下面分表过程

--先查订单主表的是否有外键约束，

select
a.name as 约束名,
object_name(b.parent_object_id) as 外键表,
d.name as 外键列,
object_name(b.referenced_object_id) as 主健表,
c.name as 主键列
from sys.foreign_keys A
inner join sys.foreign_key_columns B on A.object_id=b.constraint_object_id
inner join sys.columns C on B.parent_object_id=C.object_id and B.parent_column_id=C.column_id 
inner join sys.columns D on B.referenced_object_id=d.object_id and B.referenced_column_id=D.column_id 
where object_name(B.referenced_object_id)='Hishop_Orders';
--删除约束
ALTER TABLE dbo.Hishop_OrderGifts  DROP CONSTRAINT FK_Hishop_OrderGifts_Orders
ALTER TABLE dbo.Hishop_OrderItems  DROP CONSTRAINT FK_Hishop_OrderItems_Orders
ALTER TABLE dbo.Hishop_OrderRefund  DROP CONSTRAINT FK_Hishop_OrderRefund_Orders
ALTER TABLE dbo.Hishop_OrderReplace  DROP CONSTRAINT FK_Hishop_OrderReplace_Orders

ALTER TABLE dbo.Hishop_OrderSendNote  DROP CONSTRAINT FK_Hishop_OrderSendNote_Orders

按年份整表架构copy 并数据copy （经过测试速度快）

select * into Hishop_Orders_2016 from Hishop_Orders  where hisyear='2016

--建立索引
CREATE NONCLUSTERED INDEX [Hishop_Orders_Index2] ON [dbo].[Hishop_Orders_2016]
([PaymentTypeId] ASC)
CREATE NONCLUSTERED INDEX [Hishop_Orders_Index3] ON [dbo].[Hishop_Orders_2016]
([Username] ASC)
CREATE NONCLUSTERED INDEX [Hishop_Orders_Index4] ON [dbo].[Hishop_Orders_2016]
([UserId] ASC)
 --建立主键
alter table Hishop_Orders_2016 add constraint [Hishop_Orders_Index_2016] primary key(orderID)

  

执行历史删除（因主订单表数据转存）需要将主表数据删除，大数据如何删除

因建立了索引问题，先删除索引在删数据 在建立索引 这样删除 800W数据 16分钟 

先删除索引，主键不删

DROP INDEX [Hishop_Orders_Index5] ON [dbo].[Hishop_OrderItems]
DROP INDEX [IDX_ORDERID_OrderItems] ON [dbo].[Hishop_OrderItems]
DROP INDEX [IDX_ORDERID_PRODUCTID] ON [dbo].[Hishop_OrderItems]
DROP INDEX [IDX_SKU] ON [dbo].[Hishop_OrderItems]
DROP INDEX [IDX_SKUID_PRODUCTID] ON [dbo].[Hishop_OrderItems]
DROP INDEX [idxHishop_OrderItems_GuidId] ON [dbo].[Hishop_OrderItems] WITH ( ONLINE = OFF )
DROP INDEX [idxHishop_OrderItems_OrderIdExt] ON [dbo].[Hishop_OrderItems]
DROP INDEX [PK_Hishop_OrderItemsMain] ON [dbo].[Hishop_OrderItems]

在按年份删除只留当年数据
delete [dbo].[Hishop_OrderItems] where HisYear=2016 
delete [dbo].[Hishop_OrderItems] where HisYear=2015 

在把索引建立回来
CREATE NONCLUSTERED INDEX [Hishop_Orders_Index5] ON [dbo].[Hishop_OrderItems]([ProductId] ASC)
CREATE NONCLUSTERED INDEX [IDX_ORDERID_OrderItems] ON [dbo].[Hishop_OrderItems]([OrderId] ASC)
CREATE NONCLUSTERED INDEX [IDX_ORDERID_PRODUCTID] ON [dbo].[Hishop_OrderItems]
([OrderId] ASC,[ProductId] ASC,[SkuId] ASC,[SKU] ASC)
CREATE NONCLUSTERED INDEX [IDX_SKU] ON [dbo].[Hishop_OrderItems]([SKU] ASC)
CREATE NONCLUSTERED INDEX [IDX_SKUID_PRODUCTID] ON [dbo].[Hishop_OrderItems]([SkuId] ASC,[ProductId] ASC)
CREATE CLUSTERED INDEX [idxHishop_OrderItems_GuidId] ON [dbo].[Hishop_OrderItems]([GuidId] ASC)
CREATE NONCLUSTERED INDEX [idxHishop_OrderItems_OrderIdExt] ON [dbo].[Hishop_OrderItems]([OrderIdExt] ASC)
CREATE NONCLUSTERED INDEX [PK_Hishop_OrderItemsMain] ON [dbo].[Hishop_OrderItems]([MainOrderId] ASC)

分表完成