最近做一個大資料量統計分析系統,以前已經做了一個大的描述
http://www.cnblogs.com/edobnet/archive/2005/04/24/144521.html
http://edobnet.cnblogs.com/archive/2005/11/11/273947.html
為了提高性能,對于中間表,的同步,采用初台全量同步,每天,增量同步的方案。
我介紹一下我們增量方案吧!
要增量,增量日志表是必需的,增量日志表的設計。
KEY(原業務表關鍵字),CREATE_DATE(變更時間),FLAG(資料修改與删除标志),USE_FLAG(增量表是否被使用的記錄,(一個存儲過濾使用一位))
每天增量同步時,做如下操作
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcNXZnFWbJ9CXt92YuM3ZvxmYuNmL3d3dvw1LcpDc0RHaiojIsJye.gif)
-- 如果增量日志表,所有标志位都已經使用,把增量日志表移到增量日志備份表裡
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcNXZnFWbJ9CXt92YuM3ZvxmYuNmL3d3dvw1LcpDc0RHaiojIsJye.gif)
INSERT LOG_MANUAL_INC_BAK
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcNXZnFWbJ9CXt92YuM3ZvxmYuNmL3d3dvw1LcpDc0RHaiojIsJye.gif)
SELECT *
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcNXZnFWbJ9CXt92YuM3ZvxmYuNmL3d3dvw1LcpDc0RHaiojIsJye.gif)
FROM LOG_MANUAL_INC
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcNXZnFWbJ9CXt92YuM3ZvxmYuNmL3d3dvw1LcpDc0RHaiojIsJye.gif)
WHERE LEFT (USING_FLAG, 4 ) = ' 1111 '
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcNXZnFWbJ9CXt92YuM3ZvxmYuNmL3d3dvw1LcpDc0RHaiojIsJye.gif)
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcNXZnFWbJ9CXt92YuM3ZvxmYuNmL3d3dvw1LcpDc0RHaiojIsJye.gif)
DELETE LOG_MANUAL_INC WHERE LEFT (USING_FLAG, 4 ) = ' 1111 '
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcNXZnFWbJ9CXt92YuM3ZvxmYuNmL3d3dvw1LcpDc0RHaiojIsJye.gif)
-- 建臨時中間表,友善同步(字段内容與真實表一樣)
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcNXZnFWbJ9CXt92YuM3ZvxmYuNmL3d3dvw1LcpDc0RHaiojIsJye.gif)
CREATE TABLE #TG_ENTRY (
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcNXZnFWbJ9CXt92YuM3ZvxmYuNmL3d3dvw1LcpDc0RHaiojIsJye.gif)
[ TE_ENTRY_ID ] [ char ] ( 18 ) NOT NULL
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcNXZnFWbJ9CXt92YuM3ZvxmYuNmL3d3dvw1LcpDc0RHaiojIsJye.gif)
..
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcNXZnFWbJ9CXt92YuM3ZvxmYuNmL3d3dvw1LcpDc0RHaiojIsJye.gif)
)
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcNXZnFWbJ9CXt92YuM3ZvxmYuNmL3d3dvw1LcpDc0RHaiojIsJye.gif)
GO
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcNXZnFWbJ9CXt92YuM3ZvxmYuNmL3d3dvw1LcpDc0RHaiojIsJye.gif)
-- -------------------将未整合記錄從中間表中删除
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcNXZnFWbJ9CXt92YuM3ZvxmYuNmL3d3dvw1LcpDc0RHaiojIsJye.gif)
-- SUBSTRING(USING_FLAG,2,1)為這次整合使用的标記位
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcNXZnFWbJ9CXt92YuM3ZvxmYuNmL3d3dvw1LcpDc0RHaiojIsJye.gif)
DELETE FROM TG_ENTRY WHERE TG_ENTRY_ID IN ( SELECT TG_ENTRY_ID FROM LOG_MANUAL_INC WHERE SUBSTRING (USING_FLAG, 2 , 1 ) <> 1 )
。。下面就可以做同步資料了,
。。。同步資料是從原業務表裡取,不過,資料範圍限定在,增量表裡的沒有做增量操作(并且操作類型為新加與修改的)的資料
也就同步多加一個WHERE條件,
WHERE LOG_MANUAL_INC.ARJ_MARK='M' and SUBSTRING(USING_FLAG,2,1)<>1
取好的資料是放在建立的臨時中間表,#TG_ENTRY
做好了同步操作,不要忘了更新增量表的标志。