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 請(qǐng)求，在所有被 blocked 的請(qǐng)求中，rename 優(yōu)先級(jí)永遠(yuǎn)是最高的。gh-ost 基于此設(shè)計(jì)了該方案：一個(gè)連接對(duì)原表加鎖，另啟一個(gè)連接嘗試 rename 操作，此時(shí)會(huì)被阻塞住，當(dāng)釋放 lock 的時(shí)候，rename 會(huì)首先被執(zhí)行，其他被阻塞的請(qǐng)求會(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 '請(qǐng)求類型', `access_time` bigint(20) DEFAULT NULL COMMENT '請(qǐng)求時(shí)間', `service_id` varchar(60) DEFAULT NULL COMMENT '服務(wù)id', `parameter` varchar(500) DEFAULT NULL COMMENT '請(qǐng)求參數(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 '請(qǐng)求結(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='請(qǐng)求日志表';
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)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
在關(guān)系型數(shù)據(jù)庫中，我們經(jīng)常是把數(shù)據(jù)存儲(chǔ)到多個(gè)相互關(guān)聯(lián)的表中，這些相互關(guān)聯(lián)的表通過指定的列發(fā)生聯(lián)系，下面這篇文章主要給大家介紹了關(guān)于mysql外連接與內(nèi)連接查詢的不同之處,需要的朋友可以參考下2021-06-06
Windows8下mysql 5.6.15 安裝配置方法圖文教程
這篇文章主要為大家詳細(xì)介紹了Windows8下mysql 5.6.15 安裝配置方法圖文教程，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2018-09-09
MySQL中設(shè)置服務(wù)器級(jí)別的默認(rèn)排序規(guī)則的方法
collation_server?是一個(gè)系統(tǒng)變量,它定義了服務(wù)器級(jí)別的默認(rèn)排序規(guī)則,本文主要介紹了MySQL中設(shè)置服務(wù)器級(jí)別的默認(rèn)排序規(guī)則的方法,具有一定的參考價(jià)值,感興趣的可以了解一下2024-08-08
mysql導(dǎo)出指定數(shù)據(jù)或部份數(shù)據(jù)的方法
mysql雖然可以使用mysqldump來進(jìn)行數(shù)據(jù)的到處，可是在很多場合的需求都不一樣，比如我只要導(dǎo)出某個(gè)字段呢？只要導(dǎo)出某些我需要的數(shù)據(jù)呢？2014-03-03
在本篇文章里小編給大家分享了關(guān)于Mysql解決數(shù)據(jù)庫N+1查詢問題的相關(guān)知識(shí)點(diǎn)內(nèi)容，需要的朋友們參考下。2019-07-07
記錄一個(gè)比較簡便的mysql的主從同步設(shè)置步驟，方便日后使用。2010-12-12
python中的mysql數(shù)據(jù)庫LIKE操作符詳解
LIKE操作符用于在WHERE子句中搜索列中的指定模式，like操作符的語法在文章開頭也給大家提到，通過兩種示例代碼給大家介紹python中的mysql數(shù)據(jù)庫LIKE操作符知識(shí)，感興趣的朋友跟隨小編一起看看吧2021-07-07
本文主要介紹了MySQL基于索引的壓力測試的實(shí)現(xiàn)，文中通過示例代碼介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2021-11-11
MySQL多表查詢、事務(wù)與索引的實(shí)踐與應(yīng)用操作
本文圍繞MySQL數(shù)據(jù)庫操作展開,通過構(gòu)建部門與員工管理、餐飲業(yè)務(wù)相關(guān)的數(shù)據(jù)庫表,并填充測試數(shù)據(jù),系統(tǒng)地闡述了多表查詢的多種方式,包括內(nèi)連接、外連接和不同類型的子查詢,同時(shí)介紹了事務(wù)的處理以及索引的創(chuàng)建、查詢和刪除操作,感興趣的朋友一起看看吧2025-04-04
本篇文章是對(duì)Mysql建表和索引使用規(guī)范進(jìn)行了詳細(xì)的分析介紹，需要的朋友參考下2013-06-06