分表过程.docx
《分表过程.docx》由会员分享，可在线阅读，更多相关《分表过程.docx（14页珍藏版）》请在冰豆网上搜索。
分表过程
分表过程整理
第一步：
先查订单主表的是否有外键约束，
select
a.nameas约束名,
object_name（b.parent_object_id）as外键表,
d.nameas外键列,
object_name（b.referenced_object_id）as主健表,
c.nameas主键列
fromsys.foreign_keysA
innerjoinsys.foreign_key_columnsBonA.object_id=b.constraint_object_id
innerjoinsys.columnsConB.parent_object_id=C.object_idandB.parent_column_id=C.column_id
innerjoinsys.columnsDonB.referenced_object_id=d.object_idandB.referenced_column_id=D.column_id
whereobject_name（B.referenced_object_id）='Hishop_Orders';
如果存在外键约束需要删除
ALTERTABLEdbo.Hishop_OrderGiftsDROPCONSTRAINTFK_Hishop_OrderGifts_Orders
ALTERTABLEdbo.Hishop_OrderItemsDROPCONSTRAINTFK_Hishop_OrderItems_Orders
ALTERTABLEdbo.Hishop_OrderRefundDROPCONSTRAINTFK_Hishop_OrderRefund_Orders
ALTERTABLEdbo.Hishop_OrderReplaceDROPCONSTRAINTFK_Hishop_OrderReplace_Orders
ALTERTABLEdbo.Hishop_OrderSendNoteDROPCONSTRAINTFK_Hishop_OrderSendNote_Orders
第二步：
删掉之前的创建视图和触发器
dropviewmvw_Hishop_OrderItems_2015;
dropviewmvw_Hishop_OrderItems_2016;
dropviewmvw_Hishop_OrderItems_2017;
dropviewmvw_Hishop_Orders_2015;
dropviewmvw_Hishop_Orders_2016;
dropviewmvw_Hishop_Orders_2017;
droptriggertgr_Hishop_Orders_insert;
droptriggertgr_Hishop_OrderItems_insert;
第三步：
订单分表转移订单年度历史数据（2015、2016）
订单主表查询2015年的数据转存新表Hishop_Orders_2015并建立相对应的索引，转存的表不需要约束
select*intoHishop_Orders_2015fromHishop_Orderswherehisyear='2015'
CREATENONCLUSTEREDINDEX[Hishop_Orders_Index2]ON[dbo].[Hishop_Orders_2015]
（[PaymentTypeId]ASC）
CREATENONCLUSTEREDINDEX[Hishop_Orders_Index3]ON[dbo].[Hishop_Orders_2015]
（[Username]ASC）
CREATENONCLUSTEREDINDEX[Hishop_Orders_Index4]ON[dbo].[Hishop_Orders_2015]
（[UserId]ASC）
CREATENONCLUSTEREDINDEX[idxHishop_Orders_OrderIdExt]ON[dbo].[Hishop_Orders_2015]
（[OrderIdExt]ASC）
CREATENONCLUSTEREDINDEX[NonClusteredIndex-20170426-025047]ON[Hishop_Orders_2015]
（[HisYear]ASC）
altertableHishop_Orders_2015addconstraint[Hishop_Orders_Index_2015]primarykey（orderID）
订单子表查询2015年的数据转存新表Hishop_OrderItems__2015并建立相对应的索引，转存的表不需要约束
select*intoHishop_OrderItems_2015fromHishop_OrderItemswherehisyear='2015'
CREATENONCLUSTEREDINDEX[Hishop_Orders_Index5]ON[dbo].[Hishop_OrderItems_2015]
（[ProductId]ASC）
CREATENONCLUSTEREDINDEX[IDX_ORDERID_OrderItems]ON[dbo].[Hishop_OrderItems_2015]
（[OrderId]ASC）
CREATECLUSTEREDINDEX[idxHishop_OrderItems_GuidId]ON[dbo].[Hishop_OrderItems_2015]
（[GuidId]ASC）
CREATENONCLUSTEREDINDEX[IDX_ORDERID_PRODUCTID]ON[dbo].[Hishop_OrderItems_2015]
（[OrderId]ASC,[ProductId]ASC,[SkuId]ASC,[SKU]ASC）
CREATENONCLUSTEREDINDEX[IDX_SKU]ON[dbo].[Hishop_OrderItems_2016]
（[SKU]ASC）
CREATENONCLUSTEREDINDEX[IDX_SKUID_PRODUCTID]ON[dbo].[Hishop_OrderItems_2016]
（[SkuId]ASC,[ProductId]ASC）
CREATENONCLUSTEREDINDEX[idxHishop_OrderItems_OrderIdExt]ON[dbo].[Hishop_OrderItems_2015]
（[OrderIdExt]ASC）
CREATENONCLUSTEREDINDEX[PK_Hishop_OrderItemsMain]ON[dbo].[Hishop_OrderItems_2015]
（[MainOrderId]ASC）
altertableHishop_OrderItems_2015addconstraint[Hishop_OrderItems_Index_2015]primarykey（OrderId,SkuId）
并把年份及表名写入相对应的一张新表中，方便页面下拉进行调用后选择历史数据。
createtablehishop_HisyearTable
（
HIDintidentity（1,1）primarykey,
HisYearvarchar（4）notnull,
TableNamevarchar（30）notnull,
TableTypevarchar（30）notnull
）
insertintohishop_HisyearTable（HisYear,TableName,TableType）
values（'2015','Hishop_Orders_2015','orders'）;
insertintohishop_HisyearTable（HisYear,TableName,TableType）
values（'2016','Hishop_Orders_2016','orders'）;
insertintohishop_HisyearTable（HisYear,TableName,TableType）
values（'2017','Hishop_Orders','orders'）;
insertintohishop_HisyearTable（HisYear,TableName,TableType）
values（'2015','Hishop_OrderItems_2015','items'）;
insertintohishop_HisyearTable（HisYear,TableName,TableType）
values（'2016','Hishop_OrderItems_2016','items'）;
insertintohishop_HisyearTable（HisYear,TableName,TableType）
values（'2017','Hishop_OrderItems','items'）;
go
第四步：
创建订单（主表所有视图、子表所有视图），将当前年的订单Hishop_OrderItems+Hishop_OrderItems_2015+Hishop_OrderItems_2016合并，这种视图用于部分查询所有的订单，
createviewvw_hishop_orders_allas
select*fromHishop_Orders_2015
unionall
select*fromHishop_Orders_2016
unionall
select*fromHishop_Orders;
go
createviewvw_hishop_orderitems_allas
select*fromHishop_Orderitems_2015
unionall
select*fromHishop_Orderitems_2016
unionall
select*fromHishop_Orderitems;
go
第五步：
创建分表SQL
1.创建表，记录订单历史表的SQL语句
createtableCarryOver_Sql
（
CIDintidentity（1,1）primarykey,
TableNamevarchar（30）notnull,
SqlTxtvarchar（8000）notnull
）
insertintoCarryOver_Sql（TableName,SqlTxt）values（'orders',
'select*intoMainTablefromHishop_Orderswherehisyear=''LastYear''
CREATENONCLUSTEREDINDEX[Hishop_Orders_Index2_LastYear]ON[dbo].[MainTable]
（[PaymentTypeId]ASC）
CREATENONCLUSTEREDINDEX[Hishop_Orders_Index3_LastYear]ON[dbo].[MainTable]
（[Username]ASC）
CREATENONCLUSTEREDINDEX[Hishop_Orders_Index4_LastYear]ON[dbo].[MainTable]
（[UserId]ASC）
CREATENONCLUSTEREDINDEX[idxHishop_Orders_OrderIdExt_LastYear]ON[dbo].[MainTable]
（[OrderIdExt]ASC）
CREATENONCLUSTEREDINDEX[NonClusteredIndex-LastYear]ON[MainTable]
（[HisYear]ASC）
altertableMainTableaddconstraint[Hishop_Orders_Index_LastYear]primarykey（orderID）'）
insertintoCarryOver_Sql（TableName,SqlTxt）values（'items',
'select*intoDetTablefromHishop_OrderItemswherehisyear=''LastYear''
CREATENONCLUSTEREDINDEX[Hishop_Orders_Index5_LastYear]ON[dbo].[DetTable]
（[ProductId]ASC）
CREATENONCLUSTEREDINDEX[IDX_ORDERID_OrderItems_LastYear]ON[dbo].[DetTable]
（[OrderId]ASC）
CREATECLUSTEREDINDEX[idxHishop_OrderItems_GuidId_LastYear]ON[dbo].[DetTable]
（[GuidId]ASC）
CREATENONCLUSTEREDINDEX[IDX_ORDERID_PRODUCTID_LastYear]ON[dbo].[DetTable]
（[OrderId]ASC,[ProductId]ASC,[SkuId]ASC,[SKU]ASC）
CREATENONCLUSTEREDINDEX[IDX_SKU_LastYear]ON[dbo].[DetTable]
（[SKU]ASC）
CREATENONCLUSTEREDINDEX[IDX_SKUID_PRODUCTID_LastYear]ON[dbo].[DetTable]
（[SkuId]ASC,[ProductId]ASC）
CREATENONCLUSTEREDINDEX[idxHishop_OrderItems_OrderIdExt_LastYear]ON[dbo].[DetTable]
（[OrderIdExt]ASC）
CREATENONCLUSTEREDINDEX[PK_Hishop_OrderItemsMain_LastYear]ON[dbo].[DetTable]
（[MainOrderId]ASC）
altertableDetTableaddconstraint[Hishop_OrderItems_Index_LastYear]primarykey（OrderId,SkuId）'）go
第六步：
执行存储过程（每年1月1号早上6点执行）订单表数据转为历史数据
dropprocproc_CarryOver
go
--按当前系统时间年份创建主订单表和子订单表对应上一年度历史年度数据表
--并把主订单表和子订单表对应上一年度的历史数据导入到对应新建的上一年度历史年度数据表中
--然后在主订单表和子订单表中删掉上一年度历史年度数据
--同时更新主订单表和子订单表的查询全部历史年度数据视图
--年份与历史数据表关系表
createprocproc_CarryOver
as
declare
--记录上一年份
@LastYearvarchar（4）,
--记录当前年份
@ThisYearvarchar（4）,
--主表名称
@MainTablevarchar（30）='Hishop_Orders',
--从表名称
@DetTablevarchar（30）='Hishop_OrderItems',
--主表脚本
@MainTableSqlvarchar（8000）,
--从表脚本
@DetTableSqlvarchar（8000）,
--主视图名称
@MainViewvarchar（30）='vw_Hishop_Orders_all',
--从视图名称
@DetViewvarchar（30）='vw_Hishop_OrderItems_all',
@TableNamevarchar（30）
declare@temptable
（
TableNamevarchar（30）
）
begin
begintry
begintransaction
begin
--记录上一年份
set@LastYear=CONVERT（nvarchar（4）,YEAR（GETDATE（））-1）
set@ThisYear=CONVERT（nvarchar（4）,YEAR（GETDATE（）））
set@MainTable=@MainTable+'_'+@LastYear
set@DetTable=@DetTable+'_'+@LastYear
select@MainTableSql=SqlTxtfromCarryOver_SqlwhereTableName='orders'
select@DetTableSql=SqlTxtfromCarryOver_SqlwhereTableName='items'
--判断是否存在上年的历史年度数据表，有则删除
ifOBJECT_ID（@DetTable,'U'）isnotnull
exec（'droptable'+@DetTable）
ifOBJECT_ID（@MainTable,'U'）isnotnull
exec（'droptable'+@MainTable）
--新建上年的历史年度数据表
--set@MainTableSql=REPLACE（@MainTableSql,'Hishop_Orders',@MainTable）
--exec（@MainTableSql）
--set@DetTableSql=REPLACE（@DetTableSql,'Hishop_OrderItems',@DetTable）
--exec（@DetTableSql）
----导入历史数据
--set@DetTableSql='select*into'+@DetTable+'fromHishop_OrderItemswherehisyear='+@LastYear+'';
--exec（@DetTableSql）
--set@MainTableSql='select*into'+@MainTable+'fromHishop_Orderswherehisyear='+@LastYear+'';
--exec（@MainTableSql）
set@MainTableSql=REPLACE（@MainTableSql,'MainTable',@MainTable）
set@MainTableSql=REPLACE（@MainTableSql,'LastYear',@LastYear）
exec（@MainTableSql）
set@DetTableSql=REPLACE（@DetTableSql,'DetTable',@DetTable）
set@DetTableSql=REPLACE（@DetTableSql,'LastYear',@LastYear）
exec（@DetTableSql）
--修改关系表
updatehishop_HisyearTablesethisyear=@ThisYearwherehisyear=@LastYear
insertintohishop_HisyearTable（hisyear,tablename,tabletype）values（@LastYear,@MainTable,'orders'）
insertintohishop_HisyearTable（hisyear,tablename,tabletype）values（@LastYear,@DetTable,'items'）
--修改视图
ifOBJECT_ID（@MainView,'V'）isnotnull
exec（'dropview'+@MainView）
ifOBJECT_ID（@DetView,'V'）isnotnull
exec（'dropview'+@DetView）
set@MainTableSql='createviewvw_Hishop_Orders_allas'
insertinto@tempselectTableNamefromhishop_HisyearTablewhereTableType='orders'
whileexists（selectTableNamefrom@temp）
begin
setrowcount1
select@TableName=TableNamefrom@temp
set@MainTableSql=@MainTableSql+'select*from'+@TableName+'unionall'
setrowcount0
deletefrom@tempwhereTableName=@TableName
end
set@MainTableSql=LEFT（@MainTableSql,LEN（@MainTableSql）-10）
exec（@MainTableSql）
set@DetTableSql='createviewvw_Hishop_OrderItems_allas'
insertinto@tempselectTableNamefromhishop_HisyearTablewhereTableType='items'
whileexists（selectTableNamefrom@temp）
begin
setrowcount1
select@TableName=TableNamefrom@temp
set@DetTableSql=@DetTableSql+'select*fr
升级会员 