sql语句优化 千万级记录 重整

[slayerbb]

帮忙优化 以下语句
目标：去除member_id和my_friend_id中非数字的所有记录
同时去除 交叉重复 以及 简单重复 的 重复值 保留max(id)该条记录。

表 test_unionpk 结构如下
id int identity pk
member_id varchar(25) with index
my_friend_id varchar(25) with index
create_date datetime default getdate()
isactive bit default 0
聚集索引为 id，其余还有几个相互column之间的非聚集索引

表的数据级别为 2千万+
SET NOCOUNT ON
declare @OperId int
, @OperFlag int
, @OperRows int
, @OperCurrent int
set @OperId=0
set @OperFlag=0
set @OperRows=10000
set @OperCurrent=9201230

while @OperFlag=0
begin
--第一步 将表中全角数字转化为半角。
begin tran
update Test_UnionPK set
member_id=replace(replace(
replace(replace(
replace(replace(
replace(replace(
replace(replace(member_id,'０',0),'１',1)
,'２',2),'３',3)
,'４',4),'５',5)
,'６',6),'７',7)
,'８',8),'９',9)
,my_friend_id=replace(replace(
replace(replace(
replace(replace(
replace(replace(
replace(replace(My_friend_id,'０',0),'１',1)
,'２',2),'３',3)
,'４',4),'５',5)
,'６',6),'７',7)
,'８',8),'９',9)
where isnumeric(member_id)=0 and isnumeric(my_friend_id)=0 and id<@OperCurrent+@OperRows and id>@OperCurrent
if @@error>0 rollback tran

-- 第二步 删除掉 表中member_id 或 my_friend_id中含非数字 的记录。

delete Test_UnionPK where id in (
select id
from Test_UnionPK where isnumeric(member_id)=0 and id<@OperCurrent+@OperRows and id>@OperCurrent
union all
select id as member_id
from Test_UnionPK where isnumeric(my_friend_id)=0 and id<@OperCurrent+@OperRows and id>@OperCurrent
)
if @@error>0 rollback tran

-- 第三步 删除掉 表中的重复记录，此重复记录包括 mid=Mid ,fid=Fid 以及 mid=Fid ,fid=Mid 这两种情况的重复。保证内部数据的唯一性。
delete Test_UnionPK where id<@OperCurrent+@OperRows and id>@OperCurrent and id not in(
select max(id) from Test_UnionPK b
where b.id<@OperCurrent+@OperRows and b.id>@OperCurrent
group by (
case when (convert(float,b.member_id)>convert(float,b.my_friend_id)
) then
b.my_Friend_id+b.member_id
else
b.member_id+b.my_Friend_id
end
)
)
If @@error>0 rollback tran
If @@RowCount=0
begin
if (select top 1 id from Test_UnionPK where id>@OperCurrent)>0 set @OperFlag=2
End
set @OperCurrent=@OperCurrent+@OperRows
end
commit tran
SET NOCOUNT Off

[hery2002]

[Quote=引用 19 楼 slayerbb 的回复:]
首先数据库版本是 2005 Standard Edition 所以无法分区和分组
其次记录很多估计在几百比一 这样。无论排除掉的记录和保存的记录都较大。
[/Quote]
--如果是这样的话,可以将表水平拆分么?
也就是说将一个表拆分成多个表,
每个表只存储几百万条纪录,然后在处理。
还有你那三段SQL中,
第二段和第三段貌似都有优化的空间.
看看前面的一些回复,

[jiahao_li]

分成两个语句处理：

--删除Member_ID和My_Friend_ID不为数字类型和Member_ID = My_Friend_ID的记录，建议在Member_ID和My_Friend_ID列上建立联合索引，以便下面语句利用索引扫描
DELETE FROM test_unionpk 
WHERE ISNUMERIC(Member_ID) = 0 OR ISNUMERIC(My_Friend_ID) = 0 OR MemberID = My_Friend_ID

--删除Member_ID、My_Friend_ID重复的记录
DELETE a FROM（
  SELECT id, MAX(id) OVER(PARTITION BY Member_ID, My_Friend_ID) AS 'mx' FROM test_unionpk 
）Tmp a
WHERE id < mx

[aft_jhong]

这几招不错 学了

[jiahao_li]

[Quote=引用 37 楼 slayerbb 的回复:]



当存在 重复 member_id,my_friend_id的时候是无法建立联合聚集索引的
[/Quote]
我的意思是如果可以的话建立联合索引，这样可以提高处理的速度，而不是一定要建立联合聚集索引。建立一般的非聚集且不唯一的索引就可以了。

[大头_衍]

太长了

[jack09596]

我来看看学习下

[slayerbb]

当存在 重复 member_id,my_friend_id的时候是无法建立联合聚集索引的

[hery2002]

使用SSIS的话,可以直接使用Fuzzy Grouping转换在package中过滤重复.
这样就可以不用建立聚合索引.

SQL Server 2005 数据转换服务中的模糊查找和模糊分组
http://msdn.microsoft.com/zh-cn/library/ms345128.aspx

[Zoezs]

mark

[oraclelogan]

[Quote=引用 21 楼 slayerbb 的回复:]
继续up。。。

为啥我的问题重来就没人解决过……

sigh。。。
[/Quote]

因为你的问题，太复杂了，一般人根本就没有碰到过，所以无从下手解决啊，等邹老大过来解决吧！

[super_iven]

UP

[ljk0000]

up!

[slayerbb]

从单个的字段 索引 到 多个字段的复合索引都有建立

现在执行起来 用ID来区分的话 很快

但最后一步 最终必须要全表一次性扫描匹配否则无法做到全部剔除。

现在是先区域剔除，逐步增大区域范围。剔除了大部分的重复了。

过阵子要吧数据库迁移到oracle 10g了
不晓得在迁移过程中是否还有一些动作可作呢。

[accpt16]

帮定

[jackesing]

学习!!

[jackeylxi]

[Quote=引用 38 楼 hery2002 的回复:]
使用SSIS的话,可以直接使用Fuzzy Grouping转换在package中过滤重复.
这样就可以不用建立聚合索引.

SQL Server 2005 数据转换服务中的模糊查找和模糊分组
http://msdn.microsoft.com/zh-cn/library/ms345128.aspx
[/Quote]

Fuzzy Grouping实在是太慢了，几万数据去重，其速度我都不能接受，别说几千万了。
或者我的用法不对，请指教。

[pbsql]

你每次处理1w条数据，然而又把所有操作都放到了一个事务中，这本身就互相矛盾

如果可能，建议减少每个事务处理的数据量

[cg2328380]

帮定，好长，好复杂啊

[timbear]

学习中～

[slayerbb]

事務已經分開了。
畢竟最後一起提交也太那個啥了
改成分步事務
逐步確認提交

估計也沒有多少優化空間了。
等新年結貼。。