如何從資料庫層面檢測兩表內容的一致性,層面一致性
一般來說呢,如何檢測兩張表的內容是否一致,這樣的需求大多在從機上體現,以保證資料一致性。方法無非有兩個,第一呢就是從資料庫著手,第二呢就是從應用程式端著手。 我這裡羅列了些如何從資料庫層面來解決此類問題的方法。
當然第一步就是檢查記錄數是否一致,否則不用想任何其他方法了。
這裡我們用兩張表t1_old,t1_new來示範。
表結構: CREATE TABLE t1_old ( id int(11) NOT NULL, log_time timestamp DEFAULT NULL) ; CREATE TABLE t1_new ( id int(11) NOT NULL, log_time timestamp DEFAULT NULL) ;兩表的記錄數都為100條。mysql> select count(*) from t1_old;+----------+| count(*) |+----------+| 100 |+----------+1 row in set (0.31 sec)mysql> select count(*) from t1_new;+----------+| count(*) |+----------+| 100 |+----------+1 row in set (0.00 sec)
方法一:用加法然後去重。
由於Union 本身具備把上下兩條串連的記錄做唯一性排序,所以這樣檢測來的非常簡單。mysql> select count(*) from (select * from t1_old union select * from t1_new) as T;+----------+| count(*) |+----------+| 100 |+----------+1 row in set (0.06 sec)這裡的記錄數為100,初步證明兩表內容一致。但是,這個方法有個BUG,在某些情形下不能簡單表示結果集一致。比如:mysql> create table t1_old1 (id int);Query OK, 0 rows affected (0.27 sec)mysql> create table t1_new1(id int);Query OK, 0 rows affected (0.09 sec)mysql> insert into t1_old1 values (1),(2),(3),(5);Query OK, 4 rows affected (0.15 sec)Records: 4 Duplicates: 0 Warnings: 0mysql> insert into t1_new1 values (2),(2),(3),(5); Query OK, 4 rows affected (0.02 sec)Records: 4 Duplicates: 0 Warnings: 0mysql> select * from t1_old1;+------+| id |+------+| 1 || 2 || 3 || 5 |+------+4 rows in set (0.00 sec)mysql> select * from t1_new1;+------+| id |+------+| 2 || 2 || 3 || 5 |+------+4 rows in set (0.00 sec)mysql> select count(*) from (select * from t1_old1 union select * from t1_new1) as T;+----------+| count(*) |+----------+| 4 |+----------+1 row in set (0.00 sec)mysql> 所以在這點上,這個方法等於是無效。
方法二: 用減法來歸零。
由於MySQL 沒有提供減法操作符,這裡我們換做PostgreSQL來檢測。t_girl=# select count(*) from (select * from t1_old except select * from t1_new) as T; count ------- 0(1 row)Time: 1.809 ms這裡檢測出來結果是0,那麼證明兩表的內容一致。 那麼我們可以針對第一種方法提到的另外一種情況做檢測:t_girl=# select count(*) from (select * from t1_old1 except select * from t1_new1) as T; count ------- 1(1 row)Time: 9.837 msOK,這裡檢測出來結果不對,那麼就直接給出不一致的結論。
第三種: 用全表JOIN,這個也是最爛的做法了,當然我這裡指的是在表記錄數超級多的情形下。
當然這點我也用PostgreSQL來示範t_girl=# select count(*) from t1_old as a full outer join t1_new as b using (id,log_time) where a.id is null or b.id is null; count ------- 0(1 row)Time: 5.002 mst_girl=# 結果為0,證明內容一致。
第四種: 用checksum校正。
比如在MySQL 裡面,如果兩張表的checksum值一致,那麼內容也就一致。mysql> checksum table t1_old;+---------------+----------+| Table | Checksum |+---------------+----------+| t_girl.t1_old | 60614552 |+---------------+----------+1 row in set (0.00 sec)mysql> checksum table t1_new;+---------------+----------+| Table | Checksum |+---------------+----------+| t_girl.t1_new | 60614552 |+---------------+----------+1 row in set (0.00 sec)但是這種方法也只局限於兩表結構一摸一樣。 比如,我修改下表t1_old的欄位類型,那麼checksum的值也就不一樣了。mysql> alter table t1_old modify id bigint;Query OK, 100 rows affected (0.23 sec)Records: 100 Duplicates: 0 Warnings: 0mysql> checksum table t1_old;+---------------+------------+| Table | Checksum |+---------------+------------+| t_girl.t1_old | 3211623989 |+---------------+------------+1 row in set (0.00 sec)mysql> checksum table t1_new;+---------------+----------+| Table | Checksum |+---------------+----------+| t_girl.t1_new | 60614552 |+---------------+----------+1 row in set (0.00 sec)
所以從上面幾種資料庫提供的方法來看,用減法來歸零相對來說比較可靠,其他的方法比較適合在特定的情形下來檢測。
怎檢查資料庫中資料的一致性
資料庫一致性檢查(dbcc)提供了一些命令用於檢查資料庫的邏輯和物理一致性。Dbcc主要有兩個功能:
使用checkstorage 或 checktable 及 checkdb 在頁一級和行一級檢查頁鏈及資料指標。
使用checkstorage, checkalloc, 或 checkverify, tablealloc, 及indexalloc
檢查頁分配。
在下列情況中需要使用 dbcc 命令: 作為資料庫日常維護工作的一部分, 資料庫內部結構的完整性決定於sa 或dbo 定期地運行
dbcc 檢查。 在系統報錯以後, 確定資料庫是否有損壞。 在備份資料庫之前, 確保備份的完整性。 如果懷疑資料庫有損壞時, 例如,
使用某個表時報出表損壞的資訊, 可以使用 dbcc 確定資料庫中其他表是否也有損壞。
下面是dbcc的簡單用法: dbcc checktable (table_name) 檢查指定的表,
檢查索引和資料頁是否正確連結, 索引是否正確排序, 所有指標是否一致, 每頁的資料資訊是否合理, 頁位移是否合理。 dbcc
checkdb (database_name) 對指定資料庫的所有表做和checktable 一樣的檢查。 dbcc
checkalloc (database_name,fix|nofix) 檢查指定資料庫, 是否所有頁面被正確分配,
是否被分配的頁面沒被使用。當使用"fix"選項時,在檢查資料庫的同時會自動修複有問題的頁面。(若資料庫資料量很大,則該過程會持續很長時間。)
dbcc tablealloc (table_name,fix|nofix) 檢查指定的表, 是否所有頁面被正確分配,
是否被分配的頁面沒被使用。是 checkalloc 的縮小版本,
對指定的表做完整性檢查。當使用"fix"選項時,在檢查資料表的同時會自動修複資料表中有問題的頁面。
關於上述命令的其它選項及詳細使用方法和checkstorage, checkverify, indexalloc
的詳細使用方法, 請參閱有關命令手冊。 舉例1: Unix平台檢查pubs2資料庫的一致性 單一使用者模式啟動Server:
$SYBASE/install startserver -f RUN_server_name -m
vi dbcc_db.sqluse mastergosp_dboption pubs2,"single user",truegouse pubs2gocheckpoint go dbcc checkdb(pubs2)godbcc checkalloc(pubs2,fix)godbcc checkcatalog(pubs2)gouse mastergosp_dboption pubs2,"single user",falsegouse pubs2gocheckpointgoquit go isql -Usa -Pxxxxxx -SSYBASE dbcc_db.out
grep Msg dbcc_db.out
舉例2: Unix平台檢查pubs2資料庫中titles表的一致性
怎檢查資料庫中資料的一致性
資料庫一致性檢查(dbcc)提供了一些命令用於檢查資料庫的邏輯和物理一致性。Dbcc主要有兩個功能:使用checkstorage 或 checktable 及 checkdb 在頁一級和行一級檢查頁鏈及資料指標。使用checkstorage, checkalloc, 或 checkverify, tablealloc, 及indexalloc檢查頁分配。 在下列情況中需要使用 dbcc 命令: 作為資料庫日常維護工作的一部分, 資料庫內部結構的完整性決定於sa 或dbo 定期地運行dbcc 檢查。 在系統報錯以後, 確定資料庫是否有損壞。 在備份資料庫之前, 確保備份的完整性。 如果懷疑資料庫有損壞時, 例如,使用某個表時報出表損壞的資訊, 可以使用 dbcc 確定資料庫中其他表是否也有損壞。 下面是dbcc的簡單用法: dbcc checktable (table_name) 檢查指定的表,檢查索引和資料頁是否正確連結, 索引是否正確排序, 所有指標是否一致, 每頁的資料資訊是否合理, 頁位移是否合理。 dbcccheckdb (database_name) 對指定資料庫的所有表做和checktable 一樣的檢查。 dbcccheckalloc (database_name,fix|nofix) 檢查指定資料庫, 是否所有頁面被正確分配,是否被分配的頁面沒被使用。當使用"fix"選項時,在檢查資料庫的同時會自動修複有問題的頁面。(若資料庫資料量很大,則該過程會持續很長時間。)dbcc tablealloc (table_name,fix|nofix) 檢查指定的表, 是否所有頁面被正確分配,是否被分配的頁面沒被使用。是 checkalloc 的縮小版本,對指定的表做完整性檢查。當使用"fix"選項時,在檢查資料表的同時會自動修複資料表中有問題的頁面。 關於上述命令的其它選項及詳細使用方法和checkstorage, checkverify, indexalloc的詳細使用方法, 請參閱有關命令手冊。