MySQL

事起

當時我們一個項目，要不停地從第三方系統拿一些監控數據存起來，只保留2個月。數據量2千萬+。這個數據量，查起來就比較慢了，我們就開始優化。

思路就是將表分區，我們是按照8周，將單表分了8個區，每周一都會將最早一周的分區Drop掉，然後新建下一周的分區。

定時任務這一塊，我們當時也沒想著用Quartz，直接用MySQL的時間觸發器。

準備

為寫本文檔，我是在本機用5.7版本，中間會報一些錯誤，解決如下：

創建Function的時候，報1418錯誤：

set global log_bin_trust_function_creators=TRUE;

永久解決

my.cnf中添加

log_bin_trust_routine_creators=1;

1055

執行

select @@sql_mode

結果如下：

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

在my.cnf中添加如下，把粗體的刪掉

sql_mode= STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

時間觸發器

先建一張表tuser，id自增，username，intime，建表語句略。

建一個Function，專門insert數據。

函數

注意，我的5.7默認是關閉event的，如下

Event關閉

要開啟：

set global event_scheduler=1;

永久開啟也是在配置文件增加一項即可。

創建一個event，每秒執行一下adduser函數

每秒執行

結果如下：

Event執行結果

也可以指定開始時間：

指定時間Event

分區

分區

在本例中，我就讓表4天一分區了，先建兩個分區。

錯誤信息

報這個錯誤是因為我們是按照入表時間這個欄位來分的，但是分區要求這個欄位必須是主鍵或主鍵的一個。

所以我們把原來單個id主鍵索引刪掉，改成id、intime為主鍵即可。我重新建了表，id是uuid，與intime一起為主鍵。

向表裡插入數據如下：

表數據

再執行分區語句成功。

按照分區的原則，本表一共兩個分區，每個分區有4條數據，查詢結果如下：

分區信息

正好對應。

插入一條14號的數據：

分區信息

也沒問題。

接著我們刪除第一個分區：

alter table tuser drop partition p1;

分區內的數據一併刪除：

刪除分區之後

再創建一個分區

alter table tuser add partition (partition p3 values less than (TO_DAYS("2017-09-27")));

注意粗體，我們新建的分區必須要在原分區之後追加，雖然我們刪除了p1分區，但是這裡也不能建p1，必須要大於p2。日期也要大於p2的日期

我們增加一條23號的數據，被分到了新建的p3。

分區信息

沒有問題。

至此，解決方案的各項技術點都已經測試通過了，可以整合了。

整合方案

思路很簡單了，每7天某個時間點觸發Event，執行一個存儲過程，先取出最早的分區，drop，然後創建一個最大分區號+1，日期也+7的分區。

本文為了演示，就按照4天1分區，一共2個分區的要求來寫了。

存儲過程

大家在實際工作中，主要是注意分區的序號和日期，要+上合適的數字。

我重新建了表和分區p1、p2，執行存儲過程，結果如下：

記錄為0是我之前把數據都清了一下。

接著是創建Event

Event

至此完結。

喜歡這篇文章嗎？立刻分享出去讓更多人知道吧！