MySQL 超大表 (億級) 管理實戰：分區、子分區、索引策略與模糊查詢困境

成果預覽

經分區存儲、索引優化、參數調優，**9000 萬筆資料單查詢 1-20ms**。但千人併發仍感壓力，優化無終點。

一、分區 + 子分區存儲

初版：按來源分區 + ID Hash 子分區

CREATE TABLE tmp_sampledata (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    username VARCHAR(32), passwd VARCHAR(32), email VARCHAR(64),
    nickname VARCHAR(32), siteid VARCHAR(32),
    src SMALLINT NOT NULL DEFAULT 0,
    PRIMARY KEY (id, src)
) ENGINE=MyISAM DEFAULT CHARSET=gbk
PARTITION BY LIST COLUMNS(src)
SUBPARTITION BY HASH(id) SUBPARTITIONS 5
(PARTITION p1 VALUES IN (1), PARTITION p2 VALUES IN (2), ...);

• `LOAD DATA INFILE` 8000 萬筆 **30 分鐘** (bulk_insert_buffer_size=8M)
• 查詢 `WHERE id=...` 走 PK 极快
• 查詢 `WHERE username='...'` **全表掃描 7400 萬行** → 不可用

加 username 索引

CREATE INDEX idx_username ON tmp_sampledata(username);

• 建索引耗時極長 (超過載入時間)
• 查詢 `username` 變 `ref` type，rows 80 → 可用
• **副作用**：索引佔用空間暴增

分區鍵優化：帶 src 過濾大幅減少掃描分區

EXPLAIN SELECT id FROM t WHERE username='abc' AND src IN (2,3);
-- rows: 20 (僅掃 2 個分區)
EXPLAIN SELECT id FROM t WHERE username='abc';
-- rows: 80 (掃全 8 分區)

二、改進分區：按業務查詢欄位分區

高頻查 `username`、`email` → 改為 **List 分區 src + Key 子分區 (username,email)**

CREATE TABLE tmp_sampledata (
    id BIGINT UNSIGNED NOT NULL,
    username VARCHAR(32) NOT NULL DEFAULT '',
    email VARCHAR(64) NOT NULL DEFAULT '',
    ...,
    src SMALLINT NOT NULL DEFAULT 0
) ENGINE=MyISAM DEFAULT CHARSET=gbk
PARTITION BY LIST COLUMNS(src)
SUBPARTITION BY KEY(username,email) SUBPARTITIONS 10
(PARTITION p1 VALUES IN (1), ...);

關鍵發現：建表時帶 PK/UK → LOAD DATA 極慢 (12h+)

**解法**：建表**不帶 PK/UK** → `LOAD DATA` **5 分鐘** → **事後建索引**

三、模糊查詢困境：`email LIKE '%163.com'`

• B-Tree 索引**無法支援前導萬用字元**
• Oracle 可用 Reverse Index，MySQL **不支援**
• 方案比較：
  1. **冗餘欄位存反轉字串** (`moc.361@`)，索引後 `LIKE 'moc.361%'` → 空間雙倍、寫入複雜
  2. **Fulltext 索引** → 分區表**不支援** (MySQL 5.6+ 僅非分區表支援)
  3. **自建倒排索引** (Lucene/ES) → 架構複雜度高
• **結論**：無完美方案，視業務接受度決定

四、最終索引建立策略 (事後建索引)

SET SESSION myisam_sort_buffer_size = 1048576000;  -- 1GB 排序緩衝
CREATE INDEX idx_username_src ON tmp_sampledata(username, src);  -- 7 分 13 秒
CREATE INDEX idx_email_src ON tmp_sampledata(email, src);        -- 10 分 48 秒
CREATE INDEX idx_src_username_email ON tmp_sampledata(src, username, email); -- 16 分 05 秒

調大 `myisam_sort_buffer_size` 從 8M → 1GB，**建索引速度大幅提升**。

五、持續改進方向

• 升級 MySQL 8.0 獲得 **Instant ADD COLUMN**、**Hash 索引**、**CTE** 等新特性
• 引入 **Read Replica** 分流讀查詢
• 冷熱資料分離：近期資料 MySQL，歷史資料 ClickHouse / Doris
• 寫入端採用 **Batch Insert** + **非同步複寫** 降低主庫壓力

結語

億級表管理核心：**分區策略對齊查詢模式、LOAD DATA 先載入後建索引、調大排序緩衝、接受模糊查詢妥協或引入搜尋引擎**。架構演進永遠在路上。