分表过程

分表过程整理第一步：先查订单主表的是否有外键约束，select as 约束名,object_name(b.parent_object_id) as 外键表, as 外键列,object_name(b.referenced_object_id) as 主健表, as 主键列from sys.foreign_keys Ainner join sys.foreign_key_columns B on A.object_id=b.constraint_object_idinner join sys.columns C on B.parent_object_id=C.object_id and B.parent_column_id=C.column_idinner join sys.columns D on B.referenced_object_id=d.object_id and B.referenced_column_id=D.column_idwhere object_name(B.referenced_object_id)='Hishop_Orders';如果存在外键约束需要删除ALTER TABLE dbo.Hishop_OrderGifts DROP CONSTRAINT FK_Hishop_OrderGifts_OrdersALTER TABLE dbo.Hishop_OrderItems DROP CONSTRAINTFK_Hishop_OrderItems_OrdersALTER TABLE dbo.Hishop_OrderRefund DROP CONSTRAINT FK_Hishop_OrderRefund_OrdersALTER TABLE dbo.Hishop_OrderReplace DROP CONSTRAINT FK_Hishop_OrderReplace_OrdersALTER TABLE dbo.Hishop_OrderSendNote DROP CONSTRAINT FK_Hishop_OrderSendNote_Orders第二步：删掉之前的创建视图和触发器drop view mvw_Hishop_OrderItems_2015;drop view mvw_Hishop_OrderItems_2016;drop view mvw_Hishop_OrderItems_2017;drop view mvw_Hishop_Orders_2015;drop view mvw_Hishop_Orders_2016;drop view mvw_Hishop_Orders_2017;drop trigger tgr_Hishop_Orders_insert;drop trigger tgr_Hishop_OrderItems_insert;第三步：订单分表转移订单年度历史数据（2015、2016）订单主表查询2015年的数据转存新表Hishop_Orders_2015 并建立相对应的索引，转存的表不需要约束select * into Hishop_Orders_2015 from Hishop_Orders where hisyear='2015' CREATE NONCLUSTERED INDEX [Hishop_Orders_Index2] ON [dbo].[Hishop_Orders_2015]([PaymentTypeId] ASC)CREATE NONCLUSTERED INDEX [Hishop_Orders_Index3] ON [dbo].[Hishop_Orders_2015]([Username] ASC)CREATE NONCLUSTERED INDEX [Hishop_Orders_Index4] ON [dbo].[Hishop_Orders_2015]([UserId] ASC)CREATE NONCLUSTERED INDEX [idxHishop_Orders_OrderIdExt] ON [dbo].[Hishop_Orders_2015]([OrderIdExt] ASC)CREATE NONCLUSTERED INDEX [NonClusteredIndex-20170426-025047] ON [Hishop_Orders_2015]([HisYear] ASC)alter table Hishop_Orders_2015 add constraint [Hishop_Orders_Index_2015] primary key(orderID)订单子表查询2015年的数据转存新表Hishop_OrderItems__2015 并建立相对应的索引，转存的表不需要约束select * into Hishop_OrderItems_2015 from Hishop_OrderItems wherehisyear='2015'CREATE NONCLUSTERED INDEX [Hishop_Orders_Index5] ON [dbo].[Hishop_OrderItems_2015]([ProductId] ASC)CREATE NONCLUSTERED INDEX [IDX_ORDERID_OrderItems] ON [dbo].[Hishop_OrderItems_2015]([OrderId] ASC)CREATE CLUSTERED INDEX [idxHishop_OrderItems_GuidId] ON [dbo].[Hishop_OrderItems_2015]([GuidId] ASC)CREATE NONCLUSTERED INDEX [IDX_ORDERID_PRODUCTID] ON [dbo].[Hishop_OrderItems_2015]([OrderId] ASC,[ProductId] ASC,[SkuId] ASC,[SKU] ASC)CREATE NONCLUSTERED INDEX [IDX_SKU] ON [dbo].[Hishop_OrderItems_2016] ([SKU] ASC)CREATE NONCLUSTERED INDEX [IDX_SKUID_PRODUCTID] ON [dbo].[Hishop_OrderItems_2016]([SkuId] ASC,[ProductId] ASC)CREATE NONCLUSTERED INDEX [idxHishop_OrderItems_OrderIdExt] ON [dbo].[Hishop_OrderItems_2015]([OrderIdExt] ASC)CREATE NONCLUSTERED INDEX [PK_Hishop_OrderItemsMain] ON[dbo].[Hishop_OrderItems_2015]([MainOrderId] ASC)alter table Hishop_OrderItems_2015 add constraint [Hishop_OrderItems_Index_2015] primary key(OrderId,SkuId)并把年份及表名写入相对应的一张新表中，方便页面下拉进行调用后选择历史数据。

create table hishop_HisyearTable(HID int identity(1,1) primary key,HisYear varchar(4) not null,TableName varchar(30) not null,TableType varchar(30) not null)insert into hishop_HisyearT able(HisYear,T ableName,T ableType)values('2015','Hishop_Orders_2015','orders');insert into hishop_HisyearT able(HisYear,T ableName,T ableType)values('2016','Hishop_Orders_2016','orders');insert into hishop_HisyearT able(HisYear,T ableName,T ableType)values('2017','Hishop_Orders','orders');insert into hishop_HisyearT able(HisYear,T ableName,T ableType)values('2015','Hishop_OrderItems_2015','items');insert into hishop_HisyearT able(HisYear,T ableName,T ableType)values('2016','Hishop_OrderItems_2016','items');insert into hishop_HisyearT able(HisYear,T ableName,T ableType)values('2017','Hishop_OrderItems','items');go第四步：创建订单（主表所有视图、子表所有视图），将当前年的订单Hishop_OrderItems+ Hishop_OrderItems_2015+ Hishop_OrderItems_2016合并，这种视图用于部分查询所有的订单，create view vw_hishop_orders_all asselect * from Hishop_Orders_2015union allselect * from Hishop_Orders_2016union allselect * from Hishop_Orders;gocreate view vw_hishop_orderitems_all asselect * from Hishop_Orderitems_2015union allselect * from Hishop_Orderitems_2016union allselect * from Hishop_Orderitems;go第五步：创建分表SQL1.创建表，记录订单历史表的SQL语句create table CarryOver_Sql(CID int identity(1,1) primary key,TableName varchar(30) not null,SqlTxt varchar(8000) not null)insert into CarryOver_Sql(TableName,SqlTxt) values ('orders','select * into MainT able from Hishop_Orders where hisyear=''LastYear'' CREATE NONCLUSTERED INDEX [Hishop_Orders_Index2_LastYear] ON [dbo].[MainT able]([PaymentTypeId] ASC)CREATE NONCLUSTERED INDEX [Hishop_Orders_Index3_LastYear] ON [dbo].[MainT able]([Username] ASC)CREATE NONCLUSTERED INDEX [Hishop_Orders_Index4_LastYear] ON [dbo].[MainT able]([UserId] ASC)CREATE NONCLUSTERED INDEX [idxHishop_Orders_OrderIdExt_LastYear] ON [dbo].[MainT able]( [OrderIdExt] ASC)CREATE NONCLUSTERED INDEX [NonClusteredIndex-LastYear] ON [MainT able] ( [HisYear] ASC)alter table MainT able add constraint [Hishop_Orders_Index_LastYear] primary key(orderID)')insert into CarryOver_Sql(TableName,SqlTxt) values ('items','select * into DetTable from Hishop_OrderItems where hisyear=''LastYear'' CREATE NONCLUSTERED INDEX [Hishop_Orders_Index5_LastYear] ON [dbo].[DetTable]( [ProductId] ASC)CREATE NONCLUSTERED INDEX [IDX_ORDERID_OrderItems_LastYear] ON [dbo].[DetTable]([OrderId] ASC)CREATE CLUSTERED INDEX [idxHishop_OrderItems_GuidId_LastYear] ON [dbo].[DetTable]([GuidId] ASC)CREATE NONCLUSTERED INDEX [IDX_ORDERID_PRODUCTID_LastYear] ON [dbo].[DetTable]([OrderId] ASC,[ProductId] ASC,[SkuId] ASC,[SKU] ASC)CREATE NONCLUSTERED INDEX [IDX_SKU_LastYear] ON [dbo].[DetTable] ([SKU] ASC)CREATE NONCLUSTERED INDEX [IDX_SKUID_PRODUCTID_LastYear] ON [dbo].[DetTable]([SkuId] ASC,[ProductId] ASC)CREATE NONCLUSTERED INDEX [idxHishop_OrderItems_OrderIdExt_LastYear] ON [dbo].[DetTable]([OrderIdExt] ASC)CREATE NONCLUSTERED INDEX [PK_Hishop_OrderItemsMain_LastYear] ON [dbo].[DetTable]([MainOrderId] ASC)alter table DetTable add constraint [Hishop_OrderItems_Index_LastYear] primary key(OrderId,SkuId)')go第六步：执行存储过程(每年1月1号早上6点执行) 订单表数据转为历史数据drop proc proc_CarryOvergo--按当前系统时间年份创建主订单表和子订单表对应上一年度历史年度数据表--并把主订单表和子订单表对应上一年度的历史数据导入到对应新建的上一年度历史年度数据表中--然后在主订单表和子订单表中删掉上一年度历史年度数据--同时更新主订单表和子订单表的查询全部历史年度数据视图--年份与历史数据表关系表create proc proc_CarryOverasdeclare--记录上一年份@LastYear varchar(4),--记录当前年份@ThisYear varchar(4),--主表名称@MainT able varchar(30)='Hishop_Orders',--从表名称@DetTable varchar(30)='Hishop_OrderItems',--主表脚本@MainT ableSql varchar(8000),--从表脚本@DetTableSql varchar(8000),--主视图名称@MainView varchar(30)='vw_Hishop_Orders_all',--从视图名称@DetView varchar(30)='vw_Hishop_OrderItems_all',@TableName varchar(30)declare @temp table(TableName varchar(30))beginbegin trybegin transactionbegin--记录上一年份set @LastYear = CONVERT(nvarchar(4), YEAR(GETDATE())-1)set @ThisYear = CONVERT(nvarchar(4), YEAR(GETDATE()))set @MainT able = @MainT able + '_' + @LastYearset @DetT able = @DetT able + '_' + @LastYearselect @MainT ableSql = SqlTxt from CarryOver_Sql where TableName = 'orders'select @DetT ableSql = SqlTxt from CarryOver_Sql where T ableName = 'items'--判断是否存在上年的历史年度数据表，有则删除if OBJECT_ID(@DetTable,'U') is not nullexec('drop table ' + @DetTable)if OBJECT_ID(@MainT able,'U') is not nullexec('drop table ' + @MainT able)--新建上年的历史年度数据表--set @MainT ableSql = REPLACE(@MainT ableSql,'Hishop_Orders',@MainT able)--exec(@MainT ableSql)--set @DetTableSql = REPLACE(@DetT ableSql,'Hishop_OrderItems',@DetT able)--exec(@DetT ableSql)----导入历史数据--set @DetT ableSql = 'select * into '+ @DetTable +' from Hishop_OrderItems where hisyear = '+ @LastYear +'';--exec(@DetT ableSql)--set @MainT ableSql = 'select * into '+ @MainT able +' from Hishop_Orders where hisyear = '+ @LastYear +'';--exec(@MainT ableSql)set @MainT ableSql = REPLACE(@MainT ableSql,'MainT able',@MainT able)set @MainT ableSql = REPLACE(@MainT ableSql,'LastYear',@LastYear)exec(@MainT ableSql)set @DetT ableSql = REPLACE(@DetTableSql,'DetTable',@DetTable)set @DetT ableSql = REPLACE(@DetTableSql,'LastYear',@LastYear)exec(@DetTableSql)--修改关系表update hishop_HisyearTable set hisyear = @ThisYear where hisyear = @LastYearinsert into hishop_HisyearTable(hisyear,tablename,tabletype) values(@LastYear,@MainT able,'orders')insert into hishop_HisyearTable(hisyear,tablename,tabletype) values(@LastYear,@DetT able,'items')--修改视图if OBJECT_ID(@MainView,'V') is not nullexec('drop view ' + @MainView)if OBJECT_ID(@DetView,'V') is not nullexec('drop view ' + @DetView)set @MainT ableSql = 'create view vw_Hishop_Orders_all as 'insert into @temp select TableName from hishop_HisyearT able where TableType = 'orders'while exists(select T ableName from @temp)beginset rowcount 1select @T ableName = T ableName from @tempset @MainT ableSql = @MainT ableSql + 'select * from ' + @TableName + ' union all 'set rowcount 0delete from @temp where T ableName = @TableNameendset @MainT ableSql = LEFT(@MainT ableSql, LEN(@MainT ableSql)- 10)exec(@MainT ableSql)set @DetT ableSql = 'create view vw_Hishop_OrderItems_all as 'insert into @temp select TableName from hishop_HisyearT able where TableType = 'items'while exists(select T ableName from @temp)beginset rowcount 1select @T ableName = T ableName from @tempset @DetTableSql = @DetTableSql + 'select * from ' + @TableName + ' union all 'set rowcount 0delete from @temp where T ableName = @TableNameendset @DetT ableSql = LEFT(@DetT ableSql, LEN(@DetT ableSql)- 10)exec(@DetTableSql)endcommit transactionend trybegin catchSELECT ERROR_MESSAGE(),ERROR_SEVERITY(),ERROR_STATE()rollback transactionend catchendgo第七步：执行历史删除（因主订单表数据转存）需要将主表数据删除，大数据如何删除因建立了索引问题，先删除索引在删数据在建立索引这样删除800W数据 16分钟原有数据量主表38w，子表1200w最终分表成为主表为当年数据，将2015,2016分表存储。