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

Question

帮忙优化 以下语句
目标：去除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=9201230while @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

Answer 1

SQL语句优化汇总
http://topic.csdn.net/u/20080716/11/2317d040-48e7-42da-822e-040b4c55b46d.html

Answer 2

--第一步的where条件可以改为这样
where id <@OperCurrent+@OperRows and id>@OperCurrent and isnumeric(member_id)=0 and isnumeric(my_friend_id)=0--第二步删除完全可以写两个语句，效率更好一些,where条件中，还是把id的条件放在前面--第三步个人认为把not in 改为exists效率会高一些，没有测试过delete b
from  Test_UnionPK b
where id <@OperCurrent+@OperRows and id>@OperCurrent and 
exists (select 1 from Test_UnionPK 
        where id <@OperCurrent+@OperRows and id>@OperCurrent 
          and (convert(float,b.member_id)>convert(float,b.my_friend_id) and my_Friend_id+member_id = b.my_Friend_id+b.member_id and id > b.id 
   or convert(float,b.member_id)<=convert(float,b.my_friend_id) and member_id+my_Friend_id = b.member_id+b.my_Friend_id and id > b.id 
  ) 
        )

Answer 3

重组了下第三步语句 主要是一个括号没括好 
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  
)
)

Answer 4

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 
) --建议写成下面这样。sql2005可能会找到更好的执行计划
delete Test_UnionPK
where id <@OperCurrent+@OperRows and id>@OperCurrent and (isnumeric(member_id)=0 or isnumeric(my_friend_id)=0)

Answer 5

第二步改如下
delete Test_UnionPK where id in 
(
   select id
     from Test_UnionPK where id<@OperCurrent+@OperRows and id>=@OperCurrent and isnumeric(member_id)=0
   union all
   select id 
     from Test_UnionPK where id<@OperCurrent+@OperRows and id>=@OperCurrent and isnumeric(my_friend_id)=0
)个人觉得 union all比 or 效率高很多。。特别是大数据的时候。。
补充  数据库是 2005 Standard Edition
不是 Enterprise Edition

Answer 6

你的这句delete把这个千万级的表扫了3遍。
虽然每一遍的效率都还可以，但加起来未必……这个也很难说，跟具体环境有关，保留意见先

Answer 7

up a 
up b
up c...唉，执行一半我的测试机器居然死机了。。还有大家有好的日志查看的软件么。。能用就好。。有点晕，为啥微软不提供个给大家用。。

Answer 8

1.确定你的异常纪录有多少?
如果只是100W:1之类的,建议使用临时表来处理。
首先在member_id和my_friend_id上建立索引,
然后将异常纪录剔除出来,单独处理.
这样避免多次扫描全表.
2.如果异常纪录比较多,
确定你数据库的版本,如果是2005以及以后的版本,建议先分区或者分组,
先将数据分开来处理.这样比较好一些.也就是建立已分区表来处理.
同时建立好索引.

Answer 9

首先数据库版本是 2005 Standard Edition 所以无法分区和分组其次记录很多估计在几百比一 这样。无论排除掉的记录和保存的记录都较大。  实在是很郁闷

Answer 10

再加100分…… 现在执行起来太费劲了内存和CPU居高不下。 耗时又太长  数据迁移对用户造成的影响太大

Answer 11

继续up  为啥我的问题重来就没人解决过……sigh

Answer 12

up up up
/..//\\fd\\fsa\fadfsaf\sdfsf/s/fasf/\sda\fs\/df/\af\ds\af\f\//\\/\/\/\/\/\/\/\//\\/\/\/
\///\\\\/\\\\/\\\\////\\\\\////\\\/\/\/\/\//\///\\\\///\\///\\\/\/\/\/\/\/\\\////\\\\\/\/

Answer 13

up up up
/..//\\fd\\fsa\fadfsaf\sdfsf/s/fasf/\sda\fs\/df/\af\ds\af\f\//\\/\/\/\/\/\/\/\//\\/\/\/
-----=======----------========------=======---------========---------=====-----=====--------\///\\\\/\\\\/\\\\////\\\\\////\\\/\/\/\/\//\///\\\\///\\///\\\/\/\/\/\/\/\\\////\\\\\/\/

Answer 14

--如果是这样的话,可以将表水平拆分么?
也就是说将一个表拆分成多个表,
每个表只存储几百万条纪录,然后在处理。
还有你那三段SQL中,
第二段和第三段貌似都有优化的空间.
看看前面的一些回复,

Answer 15

分成两个语句处理：--删除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

Answer 16

我对你的问题的第一步和第二步设计了一个SSIS的方案来执行。（不能贴图，只能口述了）
使用了两个表，设计如下CREATE TABLE [dbo].[test_unionpk](
[id] [int] IDENTITY(1,1) NOT NULL,
[member_id] [nchar](10) NULL,
[my_friend_id] [nchar](10) NULL,
[create_date] [smalldatetime] NOT NULL CONSTRAINT [DF_test_unionpk_create_date]  DEFAULT (getdate()),
[isactive] [bit] NOT NULL CONSTRAINT [DF_test_unionpk_isactive]  DEFAULT ((0)),
 CONSTRAINT [PK_test_unionpk] PRIMARY KEY CLUSTERED 
(
[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]CREATE TABLE [dbo].[test_unionpk2](
[id] [int] IDENTITY(1,1) NOT NULL,
[member_id] [int] NULL,
[my_friend_id] [int] NULL,
[create_date] [smalldatetime] NOT NULL CONSTRAINT [DF_test_unionpk_create_date2]  DEFAULT (getdate()),
[isactive] [bit] NOT NULL CONSTRAINT [DF_test_unionpk_isactive2]  DEFAULT ((0)),
 CONSTRAINT [PK_test_unionpk2] PRIMARY KEY NONCLUSTERED 
(
[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]test_unionpk表中包含18490693行数据，其中有18490693行数据的member_id和my_friend_id都包含全角数字(极端情况)，有一行数据包含字符'a'。
本次执行耗时250秒（数据库系统配置为：PC机。2G内存，CPU:E4500 2.2G）。SSIS包也很简单（不能贴图，只能口述）：
一个数据源从test_unionpk读出所有数据，传到“数据转换”数据流转换元素中，将member_id,和my_friend_id转换为整数型。并且将错误数据重定向（这样，不能转换为整数类型的数据就不会包含在输出中，即删除了不是数字的数据）。然后将“数据转换”的输出输入到test_unionpk2中。
这样，就简单的完成了转换全角数字和去除不是数字的行的任务。这里运用了数据转换中能识别全角数字的功能。
最后去重时，可以建立member_id,my_friend_id的联合聚集索引。然后使用Group by来去重。这里就不说了。不知道这样是否能满足你的性能要求。
好了，下班了，不多说了。

Answer 17

另外，补充一句。我是在客户端执行SSIS包的，执行时sql server的CPU占用在10%-20%

Answer 18

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

Answer 19

使用SSIS的话,可以直接使用Fuzzy Grouping转换在package中过滤重复.
这样就可以不用建立聚合索引.SQL Server 2005 数据转换服务中的模糊查找和模糊分组
http://msdn.microsoft.com/zh-cn/library/ms345128.aspx

Answer 20

从单个的字段 索引  到 多个字段的复合索引都有建立现在执行起来 用ID来区分的话 很快但最后一步 最终必须要全表一次性扫描匹配否则无法做到全部剔除。现在是先区域剔除，逐步增大区域范围。剔除了大部分的重复了。过阵子要吧数据库迁移到oracle 10g了
不晓得在迁移过程中是否还有一些动作可作呢。

Answer 21

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