MariaDB事件自動將早於特定時間的數據移動 到另一個沒有臨時表的表?
我正在嘗試設置一個事件,該事件會自動將超過指定時間的數據從大量使用的表移動到某個“存檔”表以提高性能。
到目前為止,我只遇到過答案,這些答案是基於一個
WHERE
同時用於語句INSERT INTO SELECT
和DELETE
語句的子句。由於兩個語句之間的時間流逝,從WHERE
理論上講,該子句可能會產生不同的結果,從而導致數據失去。這是我的活動:
DELIMITER $$ CREATE EVENT IF NOT EXISTS archive_6_month_old_entries ON SCHEDULE EVERY 1 MINUTE STARTS CURRENT_TIMESTAMP DO begin START TRANSACTION; CREATE TEMPORARY TABLE data_to_move SELECT * FROM api_log WHERE (datum - UNIX_TIMESTAMP(DATE_ADD(NOW(), INTERVAL -6 MONTH))) < 0 LIMIT 5000; INSERT INTO api_log_archive SELECT * FROM data_to_move; DELETE FROM api_log WHERE id in (SELECT id FROM data_to_move); COMMIT; end $$ DELIMITER ;
沒有臨時表有沒有辦法做到這一點?
你說:
但是我們有一個問題,如果理論上insert查詢最多需要5分鐘,那麼INSERT INTO和DELETE語句的結果就會有差異,導致5分鐘的數據失去。
不,您不必冒失去數據的風險!這就是
TRANSACTION
s 的用途!InnoDB 使用(多版本並發控制 -
MVCC
)。您應該閱讀這個重要主題,以便更好地利用伺服器的功能。像這樣的東西會起作用:
SET TRANSACTION LEVEL SERIALIZABLE; BEGIN TRANSACTION; INSERT INTO api_log_archive SELECT * FROM api_log WHERE (datum - UNIX_TIMESTAMP(DATE_ADD(NOW(), INTERVAL -6 MONTH))) < 0; -- removed LIMIT 5000, it's not necessary DELETE FROM api_log WHERE (datum - UNIX_TIMESTAMP(DATE_ADD(NOW(), INTERVAL -6 MONTH))) < 0; -- removed LIMIT COMMIT; SET TRANSACTION LEVEL READ COMMITTED; -- or whatever your default is
將其放入
EVENT
並安排它在每天晚上(例如)03:30 執行。一旦您對您的活動在您需要的時間正常執行感到滿意,那麼您所要做的就是將其作為日常監控程序的一部分進行監控。
隔離級別
READ COMMITTED
下面簡單解釋一下事務隔離級別和事務隔離級別的區別SERIALIZABLE
:
TRANSACTION ISOLATION LEVEL
MariaDB的預設設置是READ COMMITTED。所以,在終端一,我做了以下
Terminal_1_> SHOW TRANSACTION ISOLATION LEVEL;
結果:
transaction_isolation ----------------------- read committed (1 row)
這就是我們想要的——所以現在我們發出這個命令:
Terminal_1_> SELECT * FROM t;
結果:
x | y ---+----- 1 | One 2 | Two (2 rows)
這正是我們想要的——所以,現在我們深入研究如下事務:
Terminal_1_> BEGIN TRANSACTION; BEGIN Terminal_1_> SELECT * FROM t; x | y ---+----- 1 | One 2 | Two (2 rows)
在這裡,我們通過切換到另一個終端 2 來“暫停”交易……
我們執行這個查詢:
SELECT pid, age(clock_timestamp(), query_start), usename, query FROM pg_stat_activity WHERE datname = 'test' AND query NOT ILIKE '%pg_stat_activity%' ORDER BY query_start desc;
結果(不是上述的活動查詢):
pid | age | usename | query ------+-----------------+----------+------------------ 3500 | 00:05:46.113281 | postgres | SELECT * FROM t; (1 row)
因此,我們可以看到我的
SELECT * FROM t;
查詢仍然處於活動狀態並且已經超過 5 分鐘。所以,現在,再次在終端 2 中,我們在表中插入一行:
INSERT INTO t VALUES (3, 'Three');
並且(仍然是 2 號航站樓),我檢查:
Terminal_2_> SELECT * FROM t; x | y ---+------- 1 | One 2 | Two 3 | Three (3 rows)
我回到終端 1 並發出 a
SELECT * FROM t;
,結果是:x | y ---+------- 1 | One 2 | Two 3 | Three (3 rows)
現在,查看
TRANSACTION ISOLATION LEVEL
設置為的行為SERIALIZABLE
。我們發出命令:
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE
我正在使用本地 PostgreSQL 實例,因為這是我唯一執行的東西,並且使用小提琴無法執行這些命令 - MariaDB 的等價物在這裡- 我讓你來使用 MariaDB 建構所需的測試.
所以,從終端1,我們檢查我們的設置:
SELECT name, setting, short_desc FROM pg_settings WHERE name ILIKE ('default%transaction%o%');
結果:
name | setting | short_desc -------------------------------+--------------+--------------------------------------------------------------- default_transaction_isolation | serializable | Sets the transaction isolation level of each new transaction. default_transaction_read_only | off | Sets the default read-only status of new transactions. (2 rows)
所以,我們可以走了。從 terminal_1_> (
SERIALIZABLE
) 我們執行;BEGIN TRANSACTION; SELECT * FROM t;
結果:
x | y ---+----- 1 | One 2 | Two (2 rows)
現在,從 terminal_2_> 我們再次對活動查詢進行檢查:
SELECT pid, age(clock_timestamp(), query_start), usename, query FROM pg_stat_activity WHERE datname = 'test' AND query NOT ILIKE '%pg_stat_activity%' ORDER BY query_start desc;
結果:
pid | age | usename | query -------+-----------------+----------+------------------ 11064 | 00:03:37.694149 | postgres | SELECT * FROM t; (1 row)
terminal_1_> 中的查詢坐在那裡,現在 > 3 分鐘前…
再次從終端_2_>,我們更新表:
INSERT INTO t VALUES (3, 'Three');
和:
Terminal_2_> SELECT * FROM t; x | y ---+------- 1 | One 2 | Two 3 | Three (3 rows)
現在我們回到terminal_1_> 並在同一個
SELECT * FROM t;
可序列化事務中第二次執行。我們得到:
Terminal_1_> SELECT * FROM t; x | y ---+----- 1 | One 2 | Two (2 rows)
該
SERIALIZABLE
事務具有與事務開始時相同的表視圖t
,儘管terminal_2_>
已經INSERT
編輯了一條新記錄……我敦促您嘗試不同的設置,以了解您的伺服器的功能以及如何最好地利用它們…
就您的問題而言,這意味著:
場景 1(您在伺服器上保留預設值 - 即
REPEATABLE READ
):在時間 t1,您執行以下命令:
CREATE TEMPORARY TABLE data_to_move SELECT * FROM api_log WHERE (datum - UNIX_TIMESTAMP(DATE_ADD(NOW(), INTERVAL -6 MONTH))) < 0 LIMIT 5000;
比如說,你有
SELECT
10 條記錄。這是一個日誌表,所以它會不斷更新……在時間 t2,您
INSERT
進入存檔表。INSERT INTO api_log_archive SELECT * FROM data_to_move;
在時間 t3,您從 api_log 中刪除
DELETE FROM api_log WHERE id in (SELECT id FROM data_to_move);
現在,您的流程將錯過任何在 t1 和 t3 之間老化超過 6 個月的記錄 - 所以,好吧,你說,我下次會撿起它們……
場景 2(您執行歸檔過程並
TRANSACTION ISOLATION LEVEL
設置為SERIALIZABLE
)。
CREATE TEMPORARY TABLE
對於初學者,您可以按如下方式剪切階段:SET TRANSACTION ISOLATION LEVEL SERIALIZABLE -- check out MariaDB syntax for this BEGIN TRANSACTION; INSERT INTO api_log_archive SELECT id FROM api_log WHERE (datum - UNIX_TIMESTAMP(DATE_ADD(NOW(), INTERVAL -6 MONTH))) < 0; DELETE FROM api_log WHERE id IN SELECT id FROM api_log WHERE (datum - UNIX_TIMESTAMP(DATE_ADD(NOW(), INTERVAL -6 MONTH))) < 0; COMMIT; -- clear up - set your transaction defaults back to your preferred default...
這樣做的好處是您沒有創建可能很大的成本
TEMPORARY TABLE
,您將步驟數從 3 減少到 2,並且您的系統將更加健壯。
- 還有另一種可能性-您可以在更新期間鎖定表-但是,這意味著
api_log
在事務期間不會對錶進行寫入-如果可以避免,通常認為這不是一個好習慣。如果您想探索這種可能性,那麼您可能想問另一個問題……這個小提琴應該有助於澄清事情 - PostgreSQL 具有有用(和智能顯式)的功能
TRANSACTION_TIMESTAMP
,並且CLOCK_TIMESTAMP
-READ COMMITTED
任何查詢都可以看到數據,CLOCK_TIMESTAMP
事務SERIALIZABLE
可以看到數據TRANSACTION TIMESTAMP
!