仓酷云

标题: MYSQL网页编程之怎样删除数据库中的冗余数据（翻译） [打印本页]

作者: 灵魂腐蚀 时间: 2015-1-16 22:37
标题: MYSQL网页编程之怎样删除数据库中的冗余数据（翻译）
对于现有业务，可以轻松移植到MySQL。当你需要替换掉老的硬件，当你需要削减历史遗留下的老系统的时候，选用MySQL对于财务部门来说更具吸引力。数据|数据库作为数据库的开辟者，我们常常面对着要找出及删除数据库中冗余数据的义务，假如数据库中有大批的冗余数据（占总数的百分比太多），数据的准确性和牢靠性将遭到影响，同时也影响着数据库的功能，那末怎样办理这个成绩呢？上面我将切磋关于这个成绩的这个办理计划，oracle也为我们供应了一个办理计划，可是Ｏｒａｃｌｅ供应的办理计划不敷完善，碰到多量量数据谁人办理计划事情起来很慢
应当怎样删除冗余数据呢？
在这里我们使用一个ＰＬ／ＳＱｌ计划（一个自界说的存储历程）大概一个ＳＱＬ语句的办理计划（利用一个剖析的函数ＲＡＮＫ（）和一个嵌套的子查询）来打消冗余数据然后把持应当保存的纪录

甚么是冗余数据？
冗余数据就是一个数据表中，这个表中的行包括了一些不异的值，这些值实际下去说应当是独一的（这些值一样平常来讲能断定一笔记录）比方，像社会保险号，姓与名的汇合．那末我们把这么含有不异信息的行中包括的数据叫做冗余数据，如今一切的数据库表中都有主键束缚，主键中纪录了一行纪录中的独一值，从数据库的角度来看，每行都是独一的，可是从我们用户角度看来，这些纪录都是不异的纪录，由于它们都包括不异的键值（Ｆｉｒｓｔ　Ｎａｍｅ　＋　Ｌａｓｔ　Ｎａｍｅ），即便他们有分歧的主键
IDLastNameFirstNameCityPhone
------------------------------------------------------
1005KriegerJeffSanRamon9252997100
1012KriegerJeffSanRamon9252997100
1017KriegerJeffSanRamon9252997100
那末这些冗余数据是怎样呈现的那？一般有两种情形：１.从分歧的表中加载大概兼并数据
经由过程图形化的用户接口来输出数据，然后由盘算机来天生一个独一的键，并做为这一笔记录的主键
那末如何找到冗余数据呢？让我们来创立一个叫作Ｃｕｓｔｏｍｅｒ　的表并向个中到场冗余数据，看表１，正如你所看到的，我们并没有在这个表上做甚么限定来避免冗余数据，上面这么代码创立了一个独一束缚，来避免冗余数据的天生
SQL
Listing1.创立Ｃｕｓｔｏｍｅｒ表
这个表中我们存心到场了冗余数据
DROPTABLECustomersCASCADECONSTRAINTS;
CREATETABLECustomers(
IdINTEGERNOTNULL,
LastNameVARCHAR2(15)NOTNULL,
FirstNameVARCHAR2(10),
AddressVARCHAR2(20),
CityVARCHAR2(15),
StateCHAR(2),
ZipVARCHAR2(10),
PhoneVARCHAR2(10),
RecDateDATE,
CONSTRAINTCustomers_PK
PRIMARYKEY(ID))
TABLESPACETALLYDATA;

INSERTINTOCustomers
VALUES(1000,Bradley,Tom,24503rdStr.#12,
SanFrancisco,CA,94114,4156679230,
TO_DATE(01-JAN-2000,DD-MON-YYYY));
INSERTINTOCustomers
VALUES(1001,Stone,Tony,12MainSt.Apt.3,
Oakland,CA,94342,5104562881,
TO_DATE(12-MAR-2001,DD-MON-YYYY));
INSERTINTOCustomers
VALUES(1002,Chang,Jim,42526thAve.,
Seattle,WA,98103,8182454400,
TO_DATE(15-JUN-2000,DD-MON-YYYY));
INSERTINTOCustomers
VALUES(1003,Loney,Julie,12KeithSt.,
CastroValley,CA,94546,5103300721,
TO_DATE(22-NOV-2000,DD-MON-YYYY));
INSERTINTOCustomers
VALUES(1004,King,Chuck,100BroadwaySt.,
PleasantHill,CA,95114,9254247701,
TO_DATE(14-APR-2001,DD-MON-YYYY));
INSERTINTOCustomers
VALUES(1005,Krieger,Jeff,120MercuryRd.,
SanRamon,CA,95114,9252997100,
TO_DATE(02-FEB-2001,DD-MON-YYYY));
INSERTINTOCustomers
VALUES(1006,Monroe,John,122WestAve.,
Phoenix,AZ,85004,9252997100,
TO_DATE(02-OCT-2000,DD-MON-YYYY));
INSERTINTOCustomers
VALUES(1007,Lord,Don,573HillView,
Atlanta,GA,30303,3036578900,
TO_DATE(12-DEC-2000,DD-MON-YYYY));
INSERTINTOCustomers
VALUES(1008,Griffith,David,10FultonRd.Apt.4,
SanFrancisco,CA,94121,7236578900,
TO_DATE(15-DEC-1999,DD-MON-YYYY));
INSERTINTOCustomers
VALUES(1009,Simon,Michael,573HillView,
SantaMonica,CA,90402,8185689200,
TO_DATE(22-MAY-1999,DD-MON-YYYY));
INSERTINTOCustomers
VALUES(1010,Simon,Michael,573HillView,
SantaMonica,CA,90402,8185689200,
TO_DATE(02-OCT-1999,DD-MON-YYYY));
INSERTINTOCustomers
VALUES(1011,Stone,Tony,12MainSt.Apt.3,
Oakland,CA,94342,5104562881,
TO_DATE(07-DEC-1999,DD-MON-YYYY));
INSERTINTOCustomers
VALUES(1012,Krieger,Jeff,120MercuryRd.,
SanRamon,CA,95114,9252997100,
TO_DATE(15-JUN-1999,DD-MON-YYYY));
INSERTINTOCustomers
VALUES(1013,Blue,Don,12250SaturnRd.,
Pleasanton,CA,95434,9252332400,
TO_DATE(09-SEP-1999,DD-MON-YYYY));
INSERTINTOCustomers
VALUES(1014,Stone,Tony,12MainSt.Apt.3,
Oakland,CA,94342,5104562881,
TO_DATE(11-APR-2000,DD-MON-YYYY));
INSERTINTOCustomers
VALUES(1015,Mason,Paul,53OrangeWay,
LasVegas,NV,89101,5104562881,
TO_DATE(04-JUL-2000,DD-MON-YYYY));
INSERTINTOCustomers
VALUES(1016,Stone,Tony,12MainSt.Apt.3,
Oakland,CA,94342,5104562881,
TO_DATE(30-DEC-2000,DD-MON-YYYY));
INSERTINTOCustomers
VALUES(1017,Krieger,Jeff,120MercuryRd.,
SanRamon,CA,95114,9252997100,
TO_DATE(11-JAN-2001,DD-MON-YYYY));
INSERTINTOCustomers
VALUES(1018,Blake,Becky,1099VenusSt.,
SaltLakeCity,UT,84106,8016543501,
TO_DATE(12-JUN-2001,DD-MON-YYYY));
INSERTINTOCustomers
VALUES(1019,Stone,Tony,12MainSt.Apt.3,
Oakland,CA,94342,5104562881,
TO_DATE(03-NOV-2000,DD-MON-YYYY));
INSERTINTOCustomers
VALUES(1020,Hill,Larry,2220BenchSt.,
SantaRosa,CA,94533,7072279800,
TO_DATE(24-AUG-2000,DD-MON-YYYY));
COMMIT;
看上面的代码我在姓，和名这两个字段上加上独一束缚，（固然你能够在创立表的时分加上这一束缚，来避免冗余数据）
ALTERTABLECustomers
ADDCONSTRAINTCustomers_LastFirst
UNIQUE(LastName,FirstName);
Ｃｕｓｔｏｍｅｒ表中的冗余键是ＬａｓｔＮａｍｅ和ＦｉｒｓｔＮａｍｅ的汇合，我们把含有冗余键的数据举行分组并举行统计．
SELECTLastName,FirstName,COUNT(*)FROMCustomers
GROUPBYLastName,FirstName
ORDERBYLastName,FirstName;
Ｌｉｓｔｉｎｇ　２显现了这条语句的输入，我们能够看到有三行的输入年夜于１，这也就意味着表中含有３组冗余数据．
Listing2.找出冗余
LASTNAMEFIRSTNAMECOUNT(*)
-----------------------------------
BlakeBecky1
BlueDon1
BradleyTom1
ChangJim1
GriffithDavid1
HillLarry1
KingChuck1
KriegerJeff3
LoneyJulie1
LordDon1
MasonPaul1
MonroeJohn1
SimonMichael2
StoneTony5
14rowsselected.
我们在语句中到场Ｈａｖｉｎｇ（）语句来过滤出非冗余数据．
SELECTLastName,FirstName,COUNT(*)
FROMCustomers
GROUPBYLastName,FirstName
HAVINGCOUNT(*)>1;
SQL
Listing3.过滤冗余
到场Ｈａｖｉｎｇ（）语句来过滤出非冗余数据．
LASTNAMEFIRSTNAMECOUNT(*)
-----------------------------------
KriegerJeff3
SimonMichael2
StoneTony5
3rowsselected.
Ｌｉｓｔｉｎｇ　３显现了以上代码的输出，只管云云，这些查询了局并没有显现出能标识每行的字段，我们将上一语句做为一个嵌套查询来显现标识这些纪录的ＩＤ
SELECTID,LastName,FirstName
FROMCustomers
WHERE(LastName,FirstName)IN
(SELECTLastName,FirstName
FROMCustomers
GROUPBYLastName,FirstName
HAVINGCOUNT(*)>1)
ORDERBYLastName,FirstName;
Ｌｉｓｔｉｎｇ　４显现出了以上代码的了局，这些查询显现了有三组冗余，共有十行，我们应当保存这些组中的1005，1009，1001这些纪录然后删除1012，1017，1010，1011，1016，1019，1014这些冗余的条目．
SQL
Listing4.找出独一的键
语句的输入
IDLASTNAMEFIRSTNAME
------------------------------
1005KriegerJeff
1012KriegerJeff
1017KriegerJeff
1009SimonMichael
1010SimonMichael
1001StoneTony
1011StoneTony
1016StoneTony
1019StoneTony
1014StoneTony
10rowsselected.
Ｏｒａｃｌｅ公司给出的一个办理计划
Ｏｒａｃｌｅ　公司给我们供应一个见删除冗余数据的一个计划，这个计划利用了Ｏｒａｃｌｅ公司本人的一个汇合函数ＭＩＮ（）大概ＭＡＸ（）来办理这一成绩ＭＩＮ（）函数能够失掉每组中（冗余的非冗余的），应保存的一切值．（正如我们所见，输出出不包括那些年夜ＩＤ的冗余值
SELECTMIN(ID)ASID,LastName,FirstName
FROMCustomers
GROUPBYLastName,FirstName;
这一条命令的输入
Listing5.OutputofMIN()query
这一条命令显现了一切的非冗余的数据，别的的行则应当被删除
IDLASTNAMEFIRSTNAME
------------------------------
1018BlakeBecky
1013BlueDon
1000BradleyTom
1002ChangJim
1008GriffithDavid
1020HillLarry
1004KingChuck
1005KriegerJeff
1003LoneyJulie
1007LordDon
1015MasonPaul
1006MonroeJohn
1009SimonMichael
1001StoneTony
14rowsselected.

如许你就能够删除那些不在这个表中的一切的行，一样将上一条语句作为一个子查询，机关一个语句
DELETEFROMCustomers
WHEREIDNOTIN
(SELECTMIN(ID)
FROMCustomers
GROUPBYLastName,FirstName);
只管云云，实际是可行的，可是这个计划并非那末无效，由于如许一来，ＤＢＭＳ要完成两个表的扫描来完成这项义务，关于大批的数据来讲，这几乎是不成行的，为了测试他的功能，我创立了Ｃｕｓｔｏｍｅｒ表，约莫有5000，00０行，45，000冗余行，（９％）以上这个命令运转了一个小时，没有输入了局，它耗尽了我的耐烦，以是我杀逝世了这个历程
这个计划的令外这个计划另有一个弱点，你不克不及把持每个组中你要保存的行

一种ＰＬ／ＳＱｌ办理计划：利用存储历程删除冗余数据，叫做ＤｅｌｅＤｕｐｌｉｃａｔｅ的存储历程，这个历程的布局很明晰的．
SQL
Listing6.TheDeleteDuplicatestoredprocedure
它将这些冗余行选择一到一个游标中，然后从表中掏出每个冗余行来举行与游标中的行举行比对，然后决意是不是删除
CREATEORREPLACEPROCEDUREDeleteDuplicates(
pCommitBatchSizeININTEGER:=5000)IS
CURSORcsr_DuplicatesIS
SELECTID,LastName,FirstName
FROMCustomers
WHERE(LastName,FirstName)IN
(SELECTLastName,FirstName
FROMCustomers
GROUPBYLastName,FirstName
HAVINGCOUNT(*)>1)
ORDERBYLastName,FirstName;
／＊保留上一次的姓和名＊／
vLastNameCustomers.LastName%TYPE:=NULL;
vFirstNameCustomers.FirstName%TYPE:=NULL;
vCounterINTEGER:=0;
BEGIN
FORvDuplicatesINcsr_Duplicates
LOOP
IFvLastNameISNULLOR
(vDuplicates.LastName!=vLastName
ORNVL(vDuplicates.FirstName,)!=NVL(vFirstName,))
THEN
／＊第一次掏出行大概是一个新行
保留它的姓和名的值＊／
vLastName:=vDuplicates.LastName;
vFirstName:=vDuplicates.FirstName;
ELSE
／＊冗余数据，删除它＊／
DELETE
FROMCustomers
WHEREID=vDuplicates.ID;
vCounter:=vCounter+1;
／＊提交了局＊／
/*CommiteverypCommitBatchSizerows*/
IFMOD(vCounter,pCommitBatchSize)=0
THEN
COMMIT;
ENDIF;
ENDIF;
ENDLOOP;
IFvCounter>0
THEN
COMMIT;
ENDIF;

DBMS_OUTPUT.PUT_LINE(TO_CHAR(vCounter)||
duplicateshavebeendeleted.);
EXCEPTION
WHENOTHERS
THEN
DBMS_OUTPUT.PUT_LINE(Error||
TO_CHAR(SQLCODE)||:||SQLERRM);
ROLLBACK;
ENDDeleteDuplicates;
它将冗余数据选择到一个游标中，并依据（ＬａｓｔＮａｍｅ，ＦｉｒｓｔＮａｍｅ）来分组（在我们这个计划中），然后翻开游标然后轮回地掏出每行，然后用与先前的掏出的键值举行对照，假如这是第一次掏出这个值，大概这个值不是冗余键，那末跳过这个纪录然后取下一个，否则的话，这就是这个组中的冗余纪录，以是删失落它．
让我们运转一下这个存储历程
BEGIN
DeleteDuplicates;
END;
/
SELECTLastName,FirstName,COUNT(*)
FROMCustomers
GROUPBYLastName,FirstName
HAVINGCOUNT(*)>1;
最初一个查询语句没有前往值，以是冗余数据没有了从表中取冗余数据的历程完整是由界说在csr_Duplicates这个游标中的ＳＱＬ语句来完成的，ＰＬ／ＳＱｌ只是用来完成删除冗余数，那末能不克不及完整用ＳＱＬ语句来完成呢？
二．ＳＱＬ办理计划，利用ＲＡＮＫ（）删除冗余数据
Ｏｒａｃｌｅ8i剖析函数ＲＡＮＫ（）来列举每个组中的元素，在我们的计划中，　我们使用这个计划，我们利用这个函数静态的把冗余数据一连的分列起来加上编号，组由Ｐａｒｔｉｎｔｉｏｎ　ｂｙ　这个语句来分隔，然后用Ｏｒｄｅｒ　ｂｙ　举行分组
SELECTID,LastName,FirstName,
RANK()OVER(PARTITIONBYLastName,
FirstNameORDERBYID)SeqNumber
FROMCustomers
ORDERBYLastName,FirstName;
SQL
Listing7.OutputofsingleSQLstatementthatusesRANK()
显现的是依据纪录的条数的个数来显现特别关于冗余数据
IDLASTNAMEFIRSTNAMESEQNUMBER
----------------------------------------
1018BlakeBecky1
1013BlueDon1
1000BradleyTom1
1002ChangJim1
1008GriffithDavid1
1020HillLarry1
1004KingChuck1
1005KriegerJeff1
1012KriegerJeff2
1017KriegerJeff3
1003LoneyJulie1
1007LordDon1
1015MasonPaul1
1006MonroeJohn1
1009SimonMichael1
1010SimonMichael2
1001StoneTony1
1011StoneTony2
1014StoneTony3
1016StoneTony4
1019StoneTony5
我们能够看一到，ＳｅｑＮｕｍｂｅｒ这一列中的数值，冗余数据是依据ＩＤ号由小到猛进行的排序，一切的冗余数据的ＳｑｌＮｕｍｂｅｒ都年夜于一，一切的非冗余数据都即是一，以是我们取本人所需，删除那末没用的
SELECTID,LastName,FirstName
FROM
(SELECTID,LastName,FirstName,
RANK()OVER(PARTITIONBYLastName,
FirstNameORDERBYID)ASSeqNumber
FROMCustomers)
WHERESeqNumber>1;

SQL
Listing8.冗余键的键值
有七行必需被删除
IDLASTNAMEFIRSTNAME
------------------------------
1012KriegerJeff
1017KriegerJeff
1010SimonMichael
1011StoneTony
1014StoneTony
1016StoneTony
1019StoneTony
7rowsselected.
这显现有七行必要删除，仍是用上一个表我测试了一下这个代码，它用了７７秒种就删除一切的数据筹办好了用Ｓｑｌ语句来删除冗余数据，版本一它实行了135秒
DELETE
FROMCUSTOMERS
WHEREIDIN
(SELECTID
FROM
(SELECTID,LastName,FirstName,
RANK()OVER(PARTITIONBYLastName,
FirstNameORDERBYID)ASSeqNumber
FROMCustomers)
WHERESeqNumber>1);
我们能够看到最初的两行语句对表中的数据举行了排序，这不是无效的，以是我们来优化一下最初一个查询语句，把Ｒａｎｋ（）函数使用到只含有冗余数据的组，而不是一切的列
上面这个语句是对照无效率的，固然它不像上一个查询那样精简
SELECTID,LastName,FirstName
FROM
(SELECTID,LastName,FirstName,
RANK()OVER(PARTITIONBYLastName,
FirstNameORDERBYID)ASSeqNumber
FROM
(SELECTID,LastName,FirstName
FROMCustomers
WHERE(LastName,FirstName)IN
(SELECTLastName,FirstName
FROMCustomers
GROUPBYLastName,FirstName
HAVINGCOUNT(*)>1)))
WHERESeqNumber>1;
选择冗余数据只用了２６秒钟，如许就进步了67％的功能，如许就进步
了将这个作为子查询的删除查询的效力，
DELETE
FROMCustomers
WHEREIDIN
(SELECTID
FROM
(SELECTID,LastName,FirstName,
RANK()OVER(PARTITIONBYLastName,
FirstNameORDERBYID)ASSeqNumber
FROM
(SELECTID,LastName,FirstName
FROMCustomers
WHERE(LastName,FirstName)IN
(SELECTLastName,FirstName
FROMCustomers
GROUPBYLastName,FirstName
HAVINGCOUNT(*)>1)))
WHERESeqNumber>1);
如今只用了47秒钟的就完成的下面的义务，比起上一个136秒，这是一个很年夜的前进，比拟之下，存储历程用了56秒，如许存储历程有些慢了利用ＰＬ／ＳＱＬ语句我们和我们以上的代码，会失掉更好的更准确的代码，和进步你代码的实行效力，固然关于从数据库中列举数据ＰＬ／ＳＱＬ关于Ｓｑｌ二者没有甚么不同，可是关于数据的对照上，ＰＬ／ＳＱＬ就比ＳＱＬ要快良多，可是假如冗余数据量对照小的话，我们只管利用ＳＱＬ而不利用ＰＬ／ＳＱＬ
假如你的数据表没有主键的话，那末你能够参考别的手艺
Ｒａｎｋ（）别的的办法
利用Ｒａｎｋ（）函数你能够对选择你所保存的数据，（大概是小ＩＤ的大概是年夜ＩＤ　的，就由ＲＥＣＤａｔｅ这个列来决意这类情形下，你能够把ＲＥｃｄａｔｅ到场到（Ｏｒｄｅｒ　ｂｙ　）子句中，倒序大概正序

这是一种保存最年夜Ｉｄ的一种办理计划
DELETE
FROMCustomers
WHEREIDIN
(SELECTID
FROM
(SELECTID,LastName,FirstName,
RANK()OVER(PARTITIONBYLastName,
FirstNameORDERBYRecDateDESC,ID)ASSeqNumber
FROM
(SELECTID,LastName,FirstName,RecDate
FROMCustomers
WHERE(LastName,FirstName)IN
(SELECTLastName,FirstName
FROMCustomers
GROUPBYLastName,FirstName
HAVINGCOUNT(*)>1)))
WHERESeqNumber>1);
这类手艺包管了你能够把持每个表中的保存的组，假定你有一个数据库，有一个促销大概有一个扣头信息，好比一个整体可使用这类促销５次，大概团体可使用这个扣头三次，为了指出要保存的组的个数，你能够在where和having子句中举行设置，那末你将删除一切年夜于你设置无数的冗余组
DELETE
FROMCustomers
WHEREIDIN
(SELECTID
FROM
(SELECTID,LastName,FirstName,
RANK()OVER(PARTITIONBYLastName,
FirstNameORDERBYID)ASSeqNumber
FROM
(SELECTID,LastName,FirstName
FROMCustomers
WHERE(LastName,FirstName)IN
(SELECTLastName,FirstName
FROMCustomers
GROUPBYLastName,FirstName
HAVINGCOUNT(*)>3)))
WHERESeqNumber>3);
Asyoucansee,usingtheRANK()functionallowsyoutoeliminateduplicatesina
singleSQLstatementandgivesyoumorecapabilitiesbyextendingthepowerofyour
queries.
正如你所见利用Ｒａｎｋ（）能够打消冗余数据并且能给你很年夜的可舒展性

DBaaS解决方案可以降低首次投入成本，对于那些小企业来说，他们往往认为内部部署的数据库成本太高，DBaaS的成本和灵活性优势对小企业吸引力更大，他们是云数据库解决方案的重点客户群体。

作者: 金色的骷髅 时间: 2015-1-18 18:01
一个是把SQL语句写到客户端,可以使用DataSet进行加工;

作者: 深爱那片海 时间: 2015-1-22 19:54
其中最有名的应该是row_number了。这个终于解决了用临时表生成序列号的历史，而且SQLServer2005的row_number比Oracle的更先进。因为它把Orderby集成到了一起，不用像Oracle那样还要用子查询进行封装。

作者: 因胸联盟 时间: 2015-1-31 10:14
只能告诉你，学好数据库语言和原理，多见识几种数据库软件，比一棵树上吊死要好。

作者: 只想知道 时间: 2015-2-6 18:59
原来的计算字段其实和虚拟字段很像。只是管理方面好了而已，性能方面提高不多。但是SQL2005提供了计算字段的持久化，这就提高了查询的性能，但是会加重insert和update的负担。OLTP慎用。OLAP可以大规模使用。

作者: 活着的死人 时间: 2015-2-18 08:29
理解了存储结构，再阅读下性能优化的章节基本上会对sqlserver有个清晰地认识

作者: 莫相离 时间: 2015-3-6 01:36
理解了存储结构，再阅读下性能优化的章节基本上会对sqlserver有个清晰地认识

作者: 小妖女 时间: 2015-3-12 18:29
如果我们从集合论（关系代数）的角度来看，一张数据库的表就是一组数据元的关系，而每个SQL语句会改变一种或数种关系，从而产生出新的数据元的关系（即产生新的表）。

作者: 小女巫 时间: 2015-3-20 01:37
可以动态传入参数，省却了动态SQL的拼写。

欢迎光临仓酷云 (http://ckuyun.com/)