Mariadb

MariaDB事件自動將早於特定時間的數據移動 到另一個沒有臨時表的表?

  • May 14, 2021

我正在嘗試設置一個事件,該事件會自動將超過指定時間的數據從大量使用的表移動到某個“存檔”表以提高性能。

到目前為止,我只遇到過答案,這些答案是基於一個WHERE同時用於語句INSERT INTO SELECTDELETE語句的子句。由於兩個語句之間的時間流逝,從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分鐘的數據失去。

不,您不必冒失去數據的風險!這就是TRANSACTIONs 的用途!

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 LEVELMariaDB的預設設置是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;

比如說,你有SELECT10 條記錄。這是一個日誌表,所以它會不斷更新……

在時間 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

引用自:https://dba.stackexchange.com/questions/291402