Sybase清理海量历史数据(单张表几亿数据真实环境使用中)

之前在试验了bcp,传统的正向delete方法皆不可取的情况下，考虑使用select into 反向做法，把要保留的数据通过select into 法重新创建对象，

达到了数据快速进入新表，并对新表进行索引默认值重建，即达到了数据清理，也达到了数据清理后索引不会失效的目的，还腾出了碎片空间。

前期采用了静态的处理方式，在由于不同地方部署版本不同导致数据库结构也不完全相同情况下，需要ddlgen方式拿回外网的ddl脚本然后对静态清理程序进行对比修改，非常繁琐，头晕眼花对的，后来考虑到sybase的对象定义信息都保留的系统数据字典中，通过分析数据字典的方式进行对象的动态重构，从而省去了程序对照修改的累事。

以下看我实际处理的步骤思路以及具体脚本：

1.清理程序基本保证在深夜时间可以清理完毕，但是为了保证第二天海量数据运营级的数据库能够正常运行，所以做到了清理程序支持断点续清，清理未完也不影响第二天应用正常使用的目的。所以额外增加了清理日志表：

IF EXISTS (SELECT 1 FROM sysobjects o WHERE o.name = 'dba_delete_history_data' AND o.type = 'U')
drop table dba_delete_history_data

go


create table dba_delete_history_data (
id char(32) not null ,--流水id
table_name varchar(100) not null ,--表名
start_time datetime null ,--清理开始时间
end_time datetime null ,--清理结束时间
status int not null --清理结果状态
)
lock datarows
go

-----------------------------------------------------------------------------------------------

2.单个对象表的清理封装过程up_delete_history_table

if exists (select 1
from sysobjects
where id = object_id('up_delete_history_table')
and type = 'P')
drop procedure up_delete_history_table
go

create procedure up_delete_history_table
@objname varchar(100),--对象表名
@condition_name varchar(1000),--清理条件
@is_bak varchar(1),--清理后是否备份原表
@server_lock_scheme varchar(50) --服务的锁级别配置
as
declare @start_time datetime
declare @end_time datetime
declare @indid int
declare @keys varchar(1024)
declare @msg varchar(1024)
declare @create_tmp_str varchar(1024)
declare @drop_temp varchar(1024)
declare @lock_datarows_str varchar(100)
declare @pk_str varchar(1024)
declare @start_str varchar(100)
declare @default_str varchar(1024)
declare @index_str varchar(1024)
declare @grant_str varchar(1024)
declare @ddl_str varchar(1024)
declare @rename_str varchar(1024)
declare @end_str varchar(100)
declare @column_name varchar(1024)
declare @default_name varchar(1024)
declare @new_id char(32)
declare @data_str varchar(10)

declare @sysstat2 int

declare @tab_lock_scheme varchar(50)

begin
set nocount on
set @start_time=getdate()
set @data_str=right(convert(varchar(10),getdate(),112),4)
select @new_id =newid()
insert into dba_delete_history_data
select @new_id,@objname,@start_time,null,0

set @start_str =@objname+' data cleaning start......'
print @start_str

set @drop_temp="if exists(select 1 from sysobjects where type='U' and name='"+@objname+"_tmp') begin truncate table "+@objname+"_tmp drop table "+@objname+"_tmp end"
exec (@drop_temp)

set @create_tmp_str='select * into '+@objname+'_tmp from '+@objname+' '+@condition_name
exec (@create_tmp_str)

if @@error != 0
return -1

select @sysstat2 = sysstat2 from sysobjects where name = @objnameand type = 'U'

if @sysstat2 & 8192 = 8192
select @tab_lock_scheme = 'allpages'
else if @sysstat2 & 16384 = 16384
select @tab_lock_scheme = 'datapages'
else if @sysstat2 & 32768 = 32768
select @tab_lock_scheme = 'datarows'

if(@server_lock_scheme<>@tab_lock_scheme)
begin
set @lock_datarows_str='alter table '+@objname+'_tmp lock '+@tab_lock_scheme
exec (@lock_datarows_str)
end



create table #temp
(
id int identity,
column_name varchar(200),
default_name varchar(200)
)
declare @n int
declare @rows int
select @n=1
insert #temp(column_name,default_name)
select c.name,d.text
from syscomments d,sysprocedures p ,syscolumns c,sysobjects o
where d.id = c.cdefault and p.id=d.id and c.id=o.id and o.type='U' and o.name=@objname
and p.sequence=0 and p.status & 4096 =4096
select @rows = @@rowcount
while @n <= @rows
begin


select @default_str ='alter table '+@objname+'_tmp replace '+column_name+' '+default_name
from #temp where id = @n
exec (@default_str)
select @n = @n + 1
end
drop table #temp



if @@trancount = 0
begin set chained off end
set transaction isolation level 1
if @objname like "%.%.%" and substring(@objname, 1, charindex(".", @objname) - 1) != db_name()
begin raiserror 17460 end
if not exists (select id from sysobjects where id = object_id(@objname))
begin raiserror 17461 end
select @indid = min(indid)
from sysindexes where id = object_id(@objname) and indid > 0 and indid < 255
if @indid is null
begin
exec sp_getmessage 17640, @msg output
print @msg
end
while @indid is not null
begin
declare @i int
declare @thiskey varchar(30)
declare @sorder char(4)
declare @lastindid int
select @keys = "", @i = 1

while @i <= 31
begin
select @thiskey = index_col(@objname, @indid, @i)
if (@thiskey is null)
begin
goto keysdone
end
if @i > 1
begin
select @keys = @keys + ", "
end
select @keys = @keys + @thiskey
select @sorder = index_colorder(@objname, @indid, @i)
if (@sorder = "desc")
select @keys = @keys + " " + @sorder
select @i = @i + 1
end
keysdone:
if exists(select 1 from sysindexes where id = object_id(@objname) and indid = @indid and status & 2048 = 2048)
begin
select @pk_str='alter table '+@objname+'_tmp add constraint '+name+' primary key nonclustered ('+@keys+')'
from sysindexes where id = object_id(@objname) and indid = @indid and status & 2048 = 2048
exec (@pk_str)
end
if exists(select 1 from sysindexes where id = object_id(@objname) and indid = @indid and status & 2048 <> 2048)
begin
select @index_str='create index '+name+' on '+@objname+'_tmp('+@keys+')'
from sysindexes where id = object_id(@objname) and indid = @indid and status & 2048 <> 2048
exec (@index_str)
end
select @lastindid = @indid
select @indid = null
select @indid = min(indid) from sysindexes where id = object_id(@objname) and indid > @lastindid and indid < 255
end


create table #temp_protect
(
id int identity,
table_name varchar(200),
user_name varchar(200),
action int,
protecttype int
)
declare @protect_n int
declare @protect_rows int
declare @protect_str varchar(200)
select @protect_n=1
insert #temp_protect(table_name,user_name,action,protecttype)
select o.name,u.name,p.action,p.protecttype
from sysprotects p,sysobjects o,sysusers u where p.id=o.id and u.uid=p.uid and o.name=@objname


select @protect_rows = @@rowcount
while @protect_n <= @protect_rows
begin
select @protect_str=(case when protecttype=0 then 'grant with grant '
when protecttype=1 then 'grant '
when protecttype=2 then 'revoke ' end) +
(case when action=151 then 'references'
when action=167 then 'set proxy'
when action=187 then 'set statistics on'
when action=188 then 'set statistics off'
when action=193 then 'select'
when action=195 then 'insert'
when action=196 then 'delete'
when action=197 then 'update'
when action=198 then 'create table'
when action=203 then 'create database'
when action=205 then 'grant'
when action=206 then 'revoke'
when action=207 then 'create view'
when action=221 then 'create trigger'
when action=222 then 'create procedure'
when action=224 then 'execute'
when action=228 then 'dump database'
when action=233 then 'create default'
when action=235 then 'dump transaction'
when action=236 then 'create rule'
when action=253 then 'connect'
when action=282 then 'delete statistics'
when action=317 then 'dbcc'
when action=320 then 'truncate table'
when action=326 then 'update statistics'
when action=347 then 'set tracing' end) +
' on '+
table_name+
'_tmp to '+
user_name
from #temp_protect where id = @protect_n
exec (@protect_str)
select @protect_n = @protect_n + 1
end
drop table #temp_protect


if(lower(@is_bak)='y')
begin
set @drop_temp="if exists(select 1 from sysobjects where type='U' and name='"+@objname+'_'+@data_str+"') begin truncate table "+@objname+'_'+@data_str+" drop table "+@objname+'_'+@data_str+" end"
exec (@drop_temp)
set @rename_str='exec sp_rename '+@objname+','+@objname+'_'+@data_str
exec (@rename_str)
set @rename_str=''
end
if(lower(@is_bak)='n')
begin
set @ddl_str='truncate table '+@objname
exec (@ddl_str)
set @ddl_str='drop table '+@objname
exec (@ddl_str)
end


set @rename_str='exec sp_rename '+@objname+'_tmp,'+@objname
exec (@rename_str)


set @end_str=@objname+' data cleaning complete!'
print @end_str
set @end_time=getdate()
update dba_delete_history_data
set status=1,end_time=@end_time
where id=@new_id
set nocount off
end
go


sp_procxmode up_delete_history_table, anymode
go

----------------------------------------------------------------------------------------------------------

3.多表传入解放人机交互时间作为整体任务，

进行同一分割时间点的批任务清理过程up_delete_history_data

if exists (select 1
from sysobjects
where id = object_id('up_delete_history_data')
and type = 'P')
drop procedure up_delete_history_data
go

create procedure up_delete_history_data
@objnames varchar(8000), --多表字符串，表名间用 逗号隔开
@history_time varchar(20), --清理时间点
@is_bak varchar(1) --是否备份原表标记
as
declare @condition_name varchar(1000)
declare @objname varchar(50)
declare @lock_scheme varchar(50)
select @objnames=','+@objnames+','
begin
if(lower(@is_bak) not in ('n','y'))
begin
print "the third parameter means is bakup all source tables, must be 'n' or 'y'"
return -1
end
if @objnames is null or char_length(@objnames)=0
begin
print "please input source tables"
return -1
end
if @history_time is null or @history_time>getdate()
begin
print "please input history_time and histroy_time must less than current date"
return -1
end
select @lock_scheme=value2 from master..sysconfigures where config=327 and name='lock scheme'

if charindex(',ew_sending_queue,',@objnames) > 0
begin
set @objname='ew_sending_queue'
set @condition_name='where send_time > convert(char(10),dateadd (dd ,-5,getdate()),111)'
exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme
end
if charindex(',sms_sending,',@objnames) > 0
begin
set @objname='sms_sending'
set @condition_name='where send_time > '''+@history_time+''''
exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme
end
if charindex(',sms_receiving,',@objnames) > 0
begin
set @objname='sms_receiving'
set @condition_name='where receive_time > '''+@history_time+''''
exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme
end
if charindex(',sms_timing,',@objnames) > 0
begin
set @objname='sms_timing'
set @condition_name='where creation_time > convert(char(10),dateadd (dd ,-5,getdate()),111)'
exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme
end
if charindex(',si_record,',@objnames) > 0
begin
set @objname='si_record'
set @condition_name='where creation_time > '''+@history_time+''''
exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme
end
if charindex(',msg_comment,',@objnames) > 0
begin
set @objname='msg_comment'
set @condition_name='where creation_time > '''+@history_time+''''
exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme
end
if charindex(',msg_homework,',@objnames) > 0
begin
set @objname='msg_homework'
set @condition_name='where creation_time > '''+@history_time+''''
exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme
end
if charindex(',msg_notice,',@objnames) > 0
begin
set @objname='msg_notice'
set @condition_name='where creation_time > '''+@history_time+''''
exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme
end
if charindex(',msg_sending,',@objnames) > 0
begin
set @objname='msg_sending'
set @condition_name='where creation_time > '''+@history_time+''''
exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme
end
if charindex(',msg_receiving,',@objnames) > 0
begin
set @objname='msg_receiving'
set @condition_name='where creation_time > '''+@history_time+''''
exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme
end
if charindex(',ex_fjabt_student_record,',@objnames) > 0
begin
set @objname='ex_fjabt_student_record'
set @condition_name='where creation_time > '''+@history_time+''''
exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme
end
if charindex(',ex_fjabt_teacher_record,',@objnames) > 0
begin
set @objname='ex_fjabt_teacher_record'
set @condition_name='where creation_time > '''+@history_time+''''
exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme
end
if charindex(',adc_interface_log,',@objnames) > 0
begin
set @objname='adc_interface_log'
set @condition_name='where creation_time > '''+@history_time+''''
exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme
end
if charindex(',adc_userbind_log,',@objnames) > 0
begin
set @objname='adc_userbind_log'
set @condition_name='where creation_time > '''+@history_time+''''
exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme
end
if charindex(',fee_pay_detail,',@objnames) > 0
begin
set @objname='fee_pay_detail'
set @condition_name='where pay_time > '''+@history_time+''''
exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme
end
if charindex(',log_card_login,',@objnames) > 0
begin
set @objname='log_card_login'
set @condition_name='where creation_time > '''+@history_time+''''
exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme
end
if charindex(',sys_operation_log,',@objnames) > 0
begin
set @objname='sys_operation_log'
set @condition_name='where creation_time > '''+@history_time+''''
exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme
end
if charindex(',st_student_signin,',@objnames) > 0
begin
set @objname='st_student_signin'
set @condition_name='where statistic_date > '''+@history_time+''''
exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme
end
if charindex(',st_teacher_signin,',@objnames) > 0
begin
set @objname='st_teacher_signin'
set @condition_name='where statistic_date > '''+@history_time+''''
exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme
end
if charindex(',st_device,',@objnames) > 0
begin
set @objname='st_device'
set @condition_name='where stat_date > '''+@history_time+''''
exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme
end
if charindex(',st_fjabt_student_record,',@objnames) > 0
begin
set @objname='st_fjabt_student_record'
set @condition_name='(index pk_st_fjabt_student_record) where statistic_date > '''+@history_time+''''
exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme
end
if charindex(',st_fjabt_teacher_record,',@objnames) > 0
begin
set @objname='st_fjabt_teacher_record'
set @condition_name='(index pk_st_fjabt_teacher_record) where statistic_date > '''+@history_time+''''
exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme
end
if charindex(',st_school,',@objnames) > 0
begin
set @objname='st_school'
set @condition_name='where statistic_date > '''+@history_time+''''
exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme
end
if charindex(',st_user,',@objnames) > 0
begin
set @objname='st_user'
set @condition_name='where statistic_date > '''+@history_time+''''
exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme
end
if charindex(',log_module_access,',@objnames) > 0
begin
set @objname='log_module_access'
set @condition_name='where creation_time > '''+@history_time+''''
exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme
end
if charindex(',log_user_operation,',@objnames) > 0
begin
set @objname='log_user_operation'
set @condition_name='where creation_time > '''+@history_time+''''
exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme
end
if charindex(',eb_login_log,',@objnames) > 0
begin
set @objname='eb_login_log'
set @condition_name='where login_time > '''+@history_time+''''
exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme
end
if charindex(',fp_call_bill,',@objnames) > 0
begin
set @objname='fp_call_bill'
set @condition_name='where creation_time > '''+@history_time+''''
exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme
end
if charindex(',st_card_class,',@objnames) > 0
begin
set @objname='st_card_class'
set @condition_name='(index pk_st_card_class) where stat_date > '''+@history_time+''''
exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme
end
if charindex(',st_student_class,',@objnames) > 0
begin
set @objname='st_student_class'
set @condition_name='(index pk_st_student_class) where stat_date > '''+@history_time+''''
exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme
end
if charindex(',st_sms_analysis_class,',@objnames) > 0
begin
set @objname='st_sms_analysis_class'
set @condition_name='(index pk_st_sms_analysis_class) where stat_date > '''+@history_time+''''
exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme
end
if charindex(',st_student_record,',@objnames) > 0
begin
set @objname='st_student_record'
set @condition_name='(index idx_school_id) where month >= convert(int,datepart(year,+'''+@history_time+'''))*100+convert(int,datepart(month,+'''+@history_time+'''))'
exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme
end
end
go


sp_procxmode up_delete_history_data, anymode
go

-------------------------------------------------------------------------------