MySQL 模擬條件索引 (Partial Index) 實戰：觸發器維護過濾索引表提升範圍查詢效能

MySQL 原生不支援 CREATE INDEX ... WHERE condition (Partial Index)，但可透過觸發器同步維護一張只含符合條件資料的索引表，實現相同效果：大幅縮小掃描範圍、提升範圍查詢速度。

核心架構

• 基礎表 girl1(id, rank)：全量資料 10 萬列，rank 0~100 均勻分布。
• 過濾索引表 girl1_filtered_index(id, rank)：僅保留 rank 10~100 的 640 列 (約 0.64%)。
• 三個觸發器：AFTER INSERT/UPDATE/DELETE 同步維護過濾表。

觸發器定義

DELIMITER $$
-- INSERT
CREATE TRIGGER filtered_insert AFTER INSERT ON girl1 FOR EACH ROW
BEGIN
  IF NEW.rank BETWEEN 10 AND 100 THEN
    INSERT INTO girl1_filtered_index VALUES (NEW.id, NEW.rank);
  END IF;
END$$

-- UPDATE
CREATE TRIGGER filtered_update AFTER UPDATE ON girl1 FOR EACH ROW
BEGIN
  IF NEW.rank BETWEEN 10 AND 100 THEN
    REPLACE INTO girl1_filtered_index VALUES (NEW.id, NEW.rank);
  ELSE
    DELETE FROM girl1_filtered_index WHERE id = OLD.id;
  END IF;
END$$

-- DELETE
CREATE TRIGGER filtered_delete AFTER DELETE ON girl1 FOR EACH ROW
BEGIN
  DELETE FROM girl1_filtered_index WHERE id = OLD.id;
END$$
DELIMITER ;

查詢改寫

-- 原查詢：全表掃描 10 萬列
SELECT * FROM girl1 WHERE rank BETWEEN 20 AND 60 LIMIT 20;

-- 改寫：先查過濾表取 ID，再回主表
SELECT a.id, a.rank 
FROM girl1 a
WHERE a.id IN (SELECT b.id FROM girl1_filtered_index b WHERE b.rank BETWEEN 20 AND 60)
LIMIT 20;

效能對比 (PostgreSQL 原生 Partial Index 為參考基準)

適用場景與權衡

• ✅ 適用：大表、高頻範圍查詢、過濾條件固定、寫入頻率中低。
• ❌ 不適用：寫入極高 (觸發器開銷大)、過濾條件動態變化、資料分布極不均勻。
• 維護成本：需確保觸發器邏輯無 Bug，否則主從表資料不一致極難排查。

此模式在 MySQL 8.0 前是實現 Partial Index 的唯一可行方案，8.0+ 可考慮 FUNCTIONAL INDEX 或 INVISIBLE INDEX 配合優化器提示替代。