首页 > 挨踢(IT), 数据库 > 为数据量非常大的表添加外键的有效方法

为数据量非常大的表添加外键的有效方法

2012年7月20日 发表评论 阅读评论 391 人阅读    

　　前两天，测试组在造数据的时候，遇到一个挺有趣的问题。当时写了一个SQL，自我感觉良好，但是实际执行起来，效率及其低下。后来，经过我们的DBA的改造，效率得到了提升。著一小文以记之。

查看源代码
打印帮助
001--现在有两个表，结果如下：
002 
003--地区表，有两千条数据。主键area_id的值就是从1到2000。
004CREATE TABLE area (
005   area_id  DECIMAL(6, 0) PRIMARY KEY,
006   area_name VARCHAR(50)
007);
008 
009--人员表，有一亿条数据。主键person_id没有任何规律，数据差别也比较大。
010CREATE TABLE person (
011   person_id  DECIMAL(10, 0) PRIMARY KEY,
012   person_name VARCHAR(20) ,
013   rlt_area_id DECIMAL(6, 0)
014);
015 
016--给person添加外键。（在设计时，可能会设计这个外键。
017--但是，在创建数据库时，为了提高性能，并不一定会建立这个外键约束。）
018ALTER TABLE person ADD CONSTRAINT fk_ref FOREIGN KEY(rlt_area_id)
019    REFERENCES area(area_id) ON DELETE CASCADE ON UPDATE RESTRICT; 
020 
021     
022--在制造测试数据时，person中的rlt_area_id字段，没有添加相应的值。
023--操作：
024--使用SQL语句，更新person表中的rlt_area_id的值，
025--将人员尽可能的平均分配给每个地区。
026 
027--第一种想法
028--由于两个表直接除了area_id之外，其余没有明显的关联字段。
029--person_id相差较大，不可利用。person_name则是字符类型的。
030--所以，就需要构造出一个字段。
031--而 row_number就是一个不错的选择。所以，构造字段的SQL如下：
032SELECT row_number () OVER () AS rn, i.person_id FROM person i;
033 
034--然后，对于rn除以2000取模，就能和area_id构造一个相等的关联。
035--综合起来，SQL如下：
036UPDATE person p
037   SET p.rlt_area_id =
038          (SELECT area_id
039             FROM    area a
040                  LEFT JOIN
041                     (SELECT row_number () OVER () AS rn, i.person_id
042                        FROM person i) ip
043                  ON ip.person_id = p.person_id
044                 AND mod (ip.rn, 2000) + 1 = a.area_id
045           FETCH FIRST 1 ROWS ONLY);
046 
047--这个SQL满足条件，但是实际执行起来，效率却极其低下。
048--运行了十几分钟还没执行完成。后来，就直接停止了。
049 
050 
051--后来，在请教我们的DBA之后，根据这个思路，
052--把这个SQL分成了“三步走”战略，效率得到了很大提升。
053 
054--以下是使用临时字段进行更新的步骤：
055--增加临时字段记录序号
056ALTER TABLE person ADD rn INTEGER;
057reorg TABLE person;
058update person set rn = row_number() over();
059  
060 --如果area中的没有数据比较有规律的字段，则也可以题那就一个临时字段
061 --注：以下按照增加临时字段的情况来说明
062ALTER TABLE area ADD rn INTEGEr;
063reorg TABLE area;
064UPDATE area SET rn = row_number() over();
065  
066--创建索引提高更新速度
067CREATE INDEX idx_area_rn ON area(rn);
068  
069--查询维表记录数
070SELECT count(*) FROM area ;  --1955
071--更新事实表字段
072UPDATE person p
073   SET p.rlt_area_id =
074       (SELECT area_id FROM area a WHERE mod(p.rn, 1955) = a.rn);
075  
076--检查更新结果
077SELECT count(*) FROM person WHERE rlt_area_id IS NOT NULL;
078SELECT cnt, count(*)
079  FROM (SELECT rlt_area_id, count(*) cnt FROM person GROUP BY rlt_area_id)
080 GROUP BY cnt;
081  
082--删除临时字段及索引
083DROP INDEX idx_area_rn;
084ALTER TABLE person DROP COLUMN rn;
085ALTER TABLE area DROP COLUMN rn;
086reorg TABLE person;
087reorg TABLE area;
088  
089--更新的方法效率比较低，最好在生成的时候进行构造。
090--思路，先造出来一定量的数据，比如1024（2的十次方）条，
091--然后，反复执行下面这个SQL，就可以造出数据。
092--注意：personCount需要根据person表中的记录数，进行修改。
093INSERT INTO person
094  SELECT p.person_id + personCount, p.name, a.area_id
095    FROM (SELECT row_number() over() AS rn, ip.* FROM person ip) p,
096         (SELECT row_number() over() AS rn, ia.* FROM area ia) a
097   WHERE mod(p.rn, 2000) + 1 = a.rn;
098--这个SQL是我根据DBA给的SQL上补充的，不知道理解是否正确。
099--等回公司总部，向我们的DBA确认一下。
100 
101 
102 
103--注：
104reorg TABLE <tbschema>.<tablename>
105--有两个用途：
106--A、 通过重构行来消除“碎片”数据并压缩信息，对表进行重组。
107--     表名必须是用<dbschema>   全限定的。
108--B、reorg还有一个功能就是可以将表中的数据按照某个索引关键字的顺序排列，
109--     从而可以减少某些查询I/O的数量。
110 
111--另注：
112--创建表的SQL是我根据Oracle写的，添加外键的SQL是搜索的网上的。
113--所以，如果不对，敬请指正。谢谢！


作 者： D瓜哥，https://www.diguage.com/
原文链接：https://wordpress.diguage.com/archives/32.html
版权声明：非特殊声明均为本站原创作品，转载时请注明作者和原文链接。

分类: 挨踢(IT), 数据库 标签: , , ,
  1. 本文目前尚无任何评论.
  1. 本文目前尚无任何 trackbacks 和 pingbacks.