條件索引(Partial/Filtered Index)僅為滿足特定 WHERE 條件的行建立索引,優點:體積小、維護成本低、查詢特定範圍極快。PostgreSQL 原生支援 CREATE INDEX ... WHERE condition,MySQL 不支援。
CREATE TABLE girl1 (
id INT NOT NULL PRIMARY KEY,
rank INT NOT NULL DEFAULT 0
) ENGINE=InnoDB;
CREATE TABLE girl1_filtered_index (
id INT NOT NULL,
rank INT NOT NULL DEFAULT 0,
PRIMARY KEY (id),
KEY idx_rank (rank)
) ENGINE=InnoDB;
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。