2,400 萬筆短網址資料的 MariaDB Table Scheme Optimization：從複合主鍵改成單一主鍵

Roga Lin

2026-08-25 17:00:50 +08:00

前言

zzb.bz 是我在 2009 年設計的短網址服務，第一版開發完成之後，除了幾年換一次皮以外，都沒什麼大變動，最近我在整理它程式和資料庫，打算優化一些原本我沒做好的地方。以下這篇文章是針對 zzb.bz 資料表優化的工作紀錄。

在 zzb.bz 網站中，最重要的資料表是 users_redirection 這個資料表負責紀錄所有的短網址和對應的原始網址，從 2009 到現在，它已累積約：

24,194,539 筆資料
約 4.2 GB 資料
約 1.6 GB 索引

這次 migration 的目標看起來很簡單：

把原本的複合主鍵 (custom, date) 改成單一主鍵 (custom)，同時保留 table partition 的設計。

備註：

(custom) 欄位代表短網址 (可以由系統產生或是自定義)

(date) 資料產生的日期

但實際做下去才發現，事情沒有想像中單純。

原始資料表設計

原本的主鍵和 partition 大致如下：

PRIMARY KEY (custom, date)

PARTITION BY RANGE (MONTH(date)) (
    PARTITION p0  VALUES LESS THAN (1),
    PARTITION p1  VALUES LESS THAN (2),
    ...
    PARTITION p11 VALUES LESS THAN (12),
    PARTITION p12 VALUES LESS THAN (13)
);

老實說，當初採用 (custom, date) 當作主鍵的具體原因我已經忘了，不過因為這個主鍵的關係，後端的 MariaDB 在切 partition 的時候也被這限制住了：

如果要切分 partition 的話， PRIMARY KEY 和 UNIQUE KEY 都必須包含 partition expression 使用的欄位。

第一個問題：月份 partition 並不是年月 partition

原本使用：

PARTITION BY RANGE (MONTH(date))

這個設計會把不同年份的相同月份放進同一個 partition。

例如：

2024-08
2025-08
2026-08

全部都會進入同一個 8 月 partition，雖然這個設計可以把資料分散到 12 個區塊，但不適合：

刪除某個日期以前的舊資料
按年份歸檔
加速指定年月區間
只掃描近期資料

而且短網址最常見的查詢其實是：

SELECT *
FROM users_redirection
WHERE custom = ?
LIMIT 1;

這個查詢沒有 date 條件，所以原本的月份 partition 並不能有效進行 partition pruning。

新方案：真正依照 `custom` 做 KEY partition

新的設計改成比較單純的 (其實我一開始就該這樣做，但我忘記為什麼當初為什麼要硬加上日期)：

PRIMARY KEY (custom)

PARTITION BY KEY (custom)
PARTITIONS 12;

這樣有幾個效果：

custom 可以成為真正的單欄主鍵。
MariaDB 可以保證每個短網址只有一筆資料。
相同 custom 一定落在相同 partition。
WHERE custom = ? 通常只需要查一個 partition。
仍保留 12 個實體 partitions。

但一樣有些問題：

無法再依月份直接刪除或歸檔 (這不重要)
只依 date 或 status 查詢時，仍可能掃描全部 partitions (能接受的取捨)

建立新表

對 2,400 萬筆的正式資料表直接執行大型 ALTER TABLE 風險太高，因此採用建立新表再切換的方式。

先複製結構：

CREATE TABLE users_redirection_new
LIKE users_redirection;

因為這樣會複製過來原始包含月份的 partition 設計，所以要先移除一次 partition ：

ALTER TABLE users_redirection_new
REMOVE PARTITIONING;

再把主鍵改成 custom 單一欄位：

ALTER TABLE users_redirection_new
    DROP PRIMARY KEY,
    MODIFY custom VARCHAR(16)
        CHARACTER SET utf8mb4
        COLLATE utf8mb4_bin
        NOT NULL,
    ADD PRIMARY KEY (custom);

然後建立新的 partitions ：

ALTER TABLE users_redirection_new
PARTITION BY KEY (custom)
PARTITIONS 12;

最後確認：

SHOW CREATE TABLE users_redirection_new\G

應該能看到：

PRIMARY KEY (`custom`)
...
PARTITION BY KEY (`custom`)
PARTITIONS 12

第二個問題：原本的複合主鍵其實已經讓重複資料進來了

原本的主鍵是：

PRIMARY KEY (custom, date)

所以只要日期不同，相同的 custom 就被允許重複出現。

實際檢查，結果發現：

重複 custom 群組：333,023 組
涉及資料：675,589 筆
若每個短網址只留一筆，需要處理 342,566 筆
單一 custom 最多出現 5 次
而且不過這個統計還牽涉到原本欄位的 collation (utf8mb4_general_ci)。

第三個問題：大小寫應該代表不同短網址

舊表的 custom 使用 utf8mb4_general_ci 當作 collation，這是 case-insensitive，所以：

abc
ABC
Abc

會被視為相同字串，而這也不是我所希望的，大小寫不同應該被視為不同的短網址，這是當初建立資料表的疏忽。

新表把 collation，改成： utf8mb4_bin 才能正確區分大小寫。

Deduplication 不是單純刪除資料

發現重複資料後，我又直接面臨另一個問題：同一個 custom 有多筆時，到底要保留哪一筆？

因為可能：

保留日期最新的一筆
保留日期最舊的一筆
保留 status = 1 的一筆
保留未被停用的一筆
保留 hit 最高的一筆
將 hit 加總後保留最新的一筆

尤其當同一個 custom 指向不同原始 URL 時，我也不能知道哪個原始 URL 才是該保留的，經過思考，我這次決定保留「日期最新的一筆」作為 migration 規則，至於舊的衝突資料就只好忍痛說再見了…

備份原始資料

在 DB 進行各類 migration 比較安全的做法是原表保持不動，在搬入新表時排除重複資料。

如此即使新表建立失敗、資料驗證失敗或應用程式切換失敗，舊表仍是完整的 rollback 來源。

搬移時只複製：

沒有重複的資料
重複資料中指定保留的那一筆

搬移期間的寫入一致性

這個短網址服務並不是只有建立短網址時才會寫入資料庫。

還包含：

每次 redirect 增加 hit
snapshot worker 批次更新原始網址網站的 title 和 description
使用者刪除或停用短網址
後台 abuse 操作

如果直接：

INSERT INTO new_table
SELECT ...
FROM old_table;

複製過程中舊表仍持續更新，新表和舊表最後一定可能不一致。

對這次規模與系統複雜度而言，採用一段時間的 maintenance window 是最簡單、最容易驗證的方案。

所以我除了把網站關掉以外，也把所有背景與隱性寫入都停止，排定計畫後，直接關站半小時，然後搞定之後再開站。

至於真正在商轉的服務，我建議要設計一套嚴謹的 zero downtime 的 migration 流程。

驗證 partition pruning

新的主要查詢應使用單一 partition：

EXPLAIN PARTITIONS
SELECT *
FROM users_redirection_new
WHERE custom = '測試短網址';

理想情況下，partitions 欄位只會顯示一個 partition。

再檢查資料是否平均分布：

SELECT
    PARTITION_NAME,
    TABLE_ROWS,
    DATA_LENGTH,
    INDEX_LENGTH
FROM information_schema.PARTITIONS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'users_redirection_new'
ORDER BY PARTITION_ORDINAL_POSITION;

更換資料表

資料與應用程式測試全部通過後，使用一次 RENAME TABLE 完成切換：

RENAME TABLE
    users_redirection TO users_redirection_backup,
    users_redirection_new TO users_redirection;

同一個 RENAME TABLE statement 是原子的，應用程式不會看到中間某一刻資料表不存在。

切換後執行：

ANALYZE TABLE users_redirection;

並再次確認：

SHOW CREATE TABLE users_redirection\G

保險起見，舊表會觀察一段時間再刪除。

小結

其實這次要把「把複合主鍵改成單一主鍵」只是想清理歷史的技術債，但沒想到後續工作其實不少，尤其是我已經忘記當初為什麼這麼做，也很擔心改東壞西…

不過幸好本次算是平安收工，清理技術債真的要小心謹慎就是了 :P

前言