MySQL?日志表改造為分區(qū)表
前言
業(yè)務(wù)有一張日志表，只需要保留 3 個(gè)月的數(shù)據(jù)，僅 3 月的數(shù)據(jù)就占用 80G 的存儲(chǔ)空間，如果不定期清理那么磁盤容納不下，但是每次清理的時(shí)候，使用 DELETE 刪除非常慢，還會(huì)產(chǎn)生大量的 Binlog 日志，而且刪除后會(huì)產(chǎn)生大量的空間碎片，回收需要重建表，期間還會(huì)造成臨時(shí)空間增長（Online DDL 排序需要使用臨時(shí)空間）需要先擴(kuò)磁盤，等待空間收縮后再縮容，非常麻煩。
了解到這張表幾乎不會(huì)查詢，只會(huì)在某種特殊情況下才會(huì)查詢，所以非常適合使用分區(qū)表。所以就提出將普通表改造成分區(qū)表的方案，本文將介紹整個(gè)過程，如果業(yè)務(wù)也有相似的場景，可以作為參考。
1. 分區(qū)表改造方法
分區(qū)表改造，需要全程鎖表，業(yè)務(wù)表示無法給出窗口時(shí)間，所以需要借助 OnlineDDL 工具，通過無鎖變更的方式來改造。這里使用的工具是 gh-ost 它的原理大致如下：
官方圖解 (https://github.com/github/gh-ost)
主要執(zhí)行過程：
- 檢查是否有外鍵觸發(fā)器及主鍵信息；
- 檢查是否主庫或從庫，是否開啟 log_slave_updates 以及 binlog 信息；
- 檢查 gho 和 ghc 結(jié)尾的臨時(shí)表是否存在；
- 創(chuàng)建 ghc 結(jié)尾的表，存數(shù)據(jù)遷移的信息，以及 binlog 信息等；
- 初始化 stream 的連接，添加 binlog 的監(jiān)聽；
- 根據(jù) alter 語句創(chuàng)建 gho 結(jié)尾的幽靈表；
- 開啟遷移數(shù)據(jù)，按照主鍵把源表數(shù)據(jù)寫入到 gho 結(jié)尾的表上，以及 binlog apply；
- 進(jìn)入 cut-over 階段，鎖住主庫的源表，等待 binlog 應(yīng)用完畢，然后替換 gh-ost 表為源表；
- 清理 ghc 表，刪除 socket 文件。
cut-over 即表 rename 階段，gh-ost 利用了 MySQL 的一個(gè)特性，原子性的 rename 請求，在所有被 blocked 的請求中，rename 優(yōu)先級(jí)永遠(yuǎn)是最高的。gh-ost 基于此設(shè)計(jì)了該方案：一個(gè)連接對(duì)原表加鎖，另啟一個(gè)連接嘗試 rename 操作，此時(shí)會(huì)被阻塞住，當(dāng)釋放 lock 的時(shí)候，rename 會(huì)首先被執(zhí)行，其他被阻塞的請求會(huì)繼續(xù)應(yīng)用到新表。
2. 操作步驟
下方為脫敏后的表結(jié)構(gòu)，目前已有 80G 的數(shù)據(jù)，業(yè)務(wù)依賴 created_at 作為保留日期參考字段，目前有 4～8 月的數(shù)據(jù)。
CREATE TABLE `xxxx_log` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id', `user_id` bigint(20) NOT NULL COMMENT '用戶id', `user_name` varchar(60) DEFAULT NULL COMMENT '用戶名', `user_ip` varchar(60) NOT NULL COMMENT '用戶ip', `service_ip` varchar(60) NOT NULL COMMENT '服務(wù)端ip', `url` varchar(500) NOT NULL COMMENT '訪問url', `req_method` varchar(60) DEFAULT NULL COMMENT '請求類型', `access_time` bigint(20) DEFAULT NULL COMMENT '請求時(shí)間', `service_id` varchar(60) DEFAULT NULL COMMENT '服務(wù)id', `parameter` varchar(500) DEFAULT NULL COMMENT '請求參數(shù)', `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創(chuàng)建時(shí)間', `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時(shí)間', `api_id` bigint(20) DEFAULT NULL COMMENT '資源ID', `request_result` varchar(200) DEFAULT NULL COMMENT '請求結(jié)果', `response_param` text COMMENT '響應(yīng)出參' PRIMARY KEY (`id`), KEY `idx_user_id` (`user_id`) USING BTREE, KEY `idx_created_at` (`created_at`) USING BTREE, KEY `idx_service_id` (`service_id`) USING BTREE, KEY `idx_server_id` (`server_id`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='請求日志表';
2.1 調(diào)整主鍵
調(diào)整主鍵，該操作不會(huì)鎖表，不會(huì)影響用戶寫入，但是會(huì)造成一定負(fù)載，建議業(yè)務(wù)低峰執(zhí)行：
ALTER TABLE xxxx_log DROP PRIMARY KEY, ADD PRIMARY KEY (id, created_at), ALGORITHM=INPLACE, LOCK=NONE;
2.2 無鎖變更
gh-ost 的使用方法參加之前的文檔：
無鎖變更工具使用說明：MySQL gh-ost DDL 變更工具
分區(qū)表執(zhí)行的 DDL 語句如下：
ALTER TABLE xxxx_log
PARTITION BY RANGE(to_days(created_at)) (
PARTITION p2024_01 VALUES LESS THAN (to_days('2024-02-01')),
PARTITION p2024_02 VALUES LESS THAN (to_days('2024-03-01')),
PARTITION p2024_03 VALUES LESS THAN (to_days('2024-04-01')),
PARTITION p2024_04 VALUES LESS THAN (to_days('2024-05-01')),
PARTITION p2024_05 VALUES LESS THAN (to_days('2024-06-01')),
PARTITION p2024_06 VALUES LESS THAN (to_days('2024-07-01')),
PARTITION p2024_07 VALUES LESS THAN (to_days('2024-08-01')),
PARTITION p2024_08 VALUES LESS THAN (to_days('2024-09-01')),
PARTITION p2024_09 VALUES LESS THAN (to_days('2024-10-01')),
PARTITION p2024_10 VALUES LESS THAN (to_days('2024-11-01')),
PARTITION p2024_11 VALUES LESS THAN (to_days('2024-12-01')),
PARTITION p2024_12 VALUES LESS THAN (to_days('2025-01-01'))
);
執(zhí)行完后，該表就被改造為分區(qū)表。
2.3 回滾策略
調(diào)整主鍵，由于 id 本身就是唯一的，所以對(duì)業(yè)務(wù)來說沒有影響，不需要回滾。
調(diào)整分區(qū)表，從剛才的原理介紹可以了解到，整個(gè)過程只會(huì)增加負(fù)載，在 copy 數(shù)據(jù)到影子表的過程中，切換后還可以選擇保留原表，測試無誤后刪除，隨時(shí)可以再 rname 回去。
3. 分區(qū)表維護(hù)
3.1 創(chuàng)建分區(qū)
需要提前創(chuàng)建好分區(qū)，否則插入數(shù)據(jù)會(huì)失敗，調(diào)整分區(qū)表的語句，已經(jīng)創(chuàng)建了 2024 年整年的分區(qū)，所以到 2025 年之前，需要提前創(chuàng)建好 2025 年的分區(qū)，這個(gè)業(yè)務(wù)負(fù)責(zé)人和 DBA 都需要注意，否則會(huì)造成故障，分區(qū)要提前創(chuàng)建。
-- 創(chuàng)建 2025 年的分區(qū) SQL 語句。
ALTER TABLE xxxx_log ADD PARTITION (
PARTITION p2025_01 VALUES LESS THAN (to_days('2025-02-01')),
PARTITION p2025_02 VALUES LESS THAN (to_days('2025-03-01')),
PARTITION p2025_03 VALUES LESS THAN (to_days('2025-04-01')),
PARTITION p2025_04 VALUES LESS THAN (to_days('2025-05-01')),
PARTITION p2025_05 VALUES LESS THAN (to_days('2025-06-01')),
PARTITION p2025_06 VALUES LESS THAN (to_days('2025-07-01')),
PARTITION p2025_07 VALUES LESS THAN (to_days('2025-08-01')),
PARTITION p2025_08 VALUES LESS THAN (to_days('2025-09-01')),
PARTITION p2025_09 VALUES LESS THAN (to_days('2025-10-01')),
PARTITION p2025_10 VALUES LESS THAN (to_days('2025-11-01')),
PARTITION p2025_11 VALUES LESS THAN (to_days('2025-12-01')),
PARTITION p2025_12 VALUES LESS THAN (to_days('2026-01-01'))
);
3.2 刪除分區(qū)
清理數(shù)據(jù)，了解業(yè)務(wù)只需要保留 3 個(gè)月的數(shù)據(jù)，那么可以直接 drop 分區(qū)清理數(shù)據(jù)，比如清理 2024 年第一季度的數(shù)據(jù)。
ALTER TABLE xxxx_log DROP PARTITION p2024_01, p2024_02, p2024_03;
3.3 分區(qū)表查詢
分區(qū)表查詢的方式和普通表沒有差別，不過建議查詢時(shí)帶上分區(qū)字段，否則查詢要掃描所有的分區(qū)，會(huì)比較慢。當(dāng)然也可以直接選擇在某個(gè)分區(qū)里面查詢。
-- 在 p2024_04 查詢最大和最小的 created_at SELECT max(created_at), min(created_at) FROM xxxx_log PARTITION (p2024_04);
后記
這類日志表類型的表，需要定期清理和歸檔，且業(yè)務(wù)平時(shí)也不會(huì)查詢，歷史數(shù)據(jù)都是靜態(tài)的，分區(qū)表的特性就比較友好。改造為分區(qū)表后可大幅提升可維護(hù)性。
到此這篇關(guān)于MySQL 日志表改造為分區(qū)表的文章就介紹到這了,更多相關(guān)MySQL 日志表改分區(qū)表內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
mysqli多查詢特性 實(shí)現(xiàn)多條sql語句查詢
mysqli相對(duì)于mysql有很多優(yōu)勢,mysqli連接數(shù)據(jù)庫和mysqli預(yù)處理prepare使用,不僅如此，mysqli更是支持多查詢特性2012-12-12
MySQL中的行級(jí)鎖、表級(jí)鎖、頁級(jí)鎖
這篇文章主要介紹了MySQL中的行級(jí)鎖、表級(jí)鎖、頁級(jí)鎖，以及分享了多種避免死鎖的方法，感興趣的小伙伴們可以參考一下2016-01-01
MySQL備份是數(shù)據(jù)庫管理的核心環(huán)節(jié)之一,通過備份能夠有效地防止數(shù)據(jù)丟失,確保數(shù)據(jù)的安全和恢復(fù)能力,備份的方式多種多樣,本文給大家介紹了四種常見的MySQL備份表的方式,需要的朋友可以參考下2026-03-03
Mysql 常用的時(shí)間日期及轉(zhuǎn)換函數(shù)小結(jié)
本文是腳本之家小編給大家總結(jié)的一些常用的mysql時(shí)間日期以及轉(zhuǎn)換函數(shù)，非常不錯(cuò)，具有一定的參考借鑒價(jià)值，需要的朋友參考下吧2018-05-05
mysql中獲取一天、一周、一月時(shí)間數(shù)據(jù)的各種sql語句寫法
今天抽時(shí)間整理了一篇mysql中與天、周、月有關(guān)的時(shí)間數(shù)據(jù)的sql語句的各種寫法，部分是收集資料，全部手工整理，自己學(xué)習(xí)的同時(shí)，分享給大家，并首先默認(rèn)創(chuàng)建一個(gè)表、插入2條數(shù)據(jù)，便于部分?jǐn)?shù)據(jù)的測試，其中部分名詞或函數(shù)進(jìn)行了解釋說明。直入主題2014-05-05
在mysql中，其實(shí)有不少方法和函數(shù)是很有用的，這次介紹一個(gè)叫coalesce的，拼寫十分麻煩，但其實(shí)作用是將返回傳入的參數(shù)中第一個(gè)非null的值，下面這篇文章主要給大家介紹了在mysql中coalesce()使用技巧的相關(guān)資料，需要的朋友可以參考下。2017-06-06
本文給大家介紹微信開發(fā)過程中mysql字符編碼問題，本文介紹的非常詳細(xì)，感興趣的朋友一起來學(xué)習(xí)吧2015-08-08
遠(yuǎn)程連接mysql數(shù)據(jù)庫注意事項(xiàng)記錄(遠(yuǎn)程連接慢skip-name-resolve)
有時(shí)候我們需要遠(yuǎn)程連接mysql數(shù)據(jù)庫，就需要注意下面的問題，方便大家解決，腳本之家小編特為大家準(zhǔn)備了一些資料2012-07-07
查詢緩存絕不返回過期數(shù)據(jù)。當(dāng)數(shù)據(jù)被修改后，在查詢緩存中的任何相關(guān)詞條均被轉(zhuǎn)儲(chǔ)清除。2009-08-08
通過mysql show processlist 命令檢查mysql鎖的方法
show processlist 命令非常實(shí)用，有時(shí)候mysql經(jīng)常跑到50%以上或更多，就需要用這個(gè)命令看哪個(gè)sql語句占用資源比較多，就知道哪個(gè)網(wǎng)站的程序問題了。2010-03-03