萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 模擬條件索引:觸發器維護過濾索引表實戰

什麼是條件索引?

條件索引(Partial/Filtered Index)僅為滿足特定 WHERE 條件的行建立索引,優點:體積小、維護成本低、查詢特定範圍極快。PostgreSQL 原生支援 CREATE INDEX ... WHERE condition,MySQL 不支援。

模擬方案:基礎表 + 過濾索引表 + 觸發器同步

1. 基礎表

CREATE TABLE girl1 (
    id INT NOT NULL PRIMARY KEY,
    rank INT NOT NULL DEFAULT 0
) ENGINE=InnoDB;

2. 過濾索引表(僅存 rank 10-100 的行)

CREATE TABLE girl1_filtered_index (
    id INT NOT NULL,
    rank INT NOT NULL DEFAULT 0,
    PRIMARY KEY (id),
    KEY idx_rank (rank)
) ENGINE=InnoDB;

3. 三個觸發器維護同步

DELIMITER $$

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$$

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$$

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

DELIMITER ;

效能驗證

導入 10 萬筆測試資料,基礎表 10 萬行,過濾索引表僅 640 行。

-- 查詢 rank 20-60
SELECT a.id, a.rank FROM girl1 AS a
WHERE a.id IN (SELECT b.id FROM girl1_filtered_index AS b WHERE b.rank BETWEEN 20 AND 60)
LIMIT 20;

掃描行數從 10 萬降至 640,查詢延遲從 ~1.8ms 降至 ~0.8ms。

適用場景與注意事項

  • 適用於高選擇性、查詢模式固定的熱點範圍查詢。
  • 觸發器帶來寫入額外開銷(每行額外 1-3 次索引維護),寫入密集場景需權衡。
  • MySQL 8.0 可考慮函數索引或隱藏索引替代部分場景。
  • 嚴格保證觸發器邏輯覆蓋所有 DML 路徑,避免主從不一致。
copyright © 萬盛學電腦網 all rights reserved