MyISAM 引擎提供鮮為人知的 PACK_KEYS 選項,可對索引進行前綴壓縮,大幅縮小索引體積、提升讀取速度,代價是寫入變慢。本文從底層存儲原理出發,解析何時開啟、何時禁用、如何驗證效果。
| 值 | 行為 | |
|---|---|---|
0 | 禁用所有鍵壓縮 | |
1 | 壓縮字符串 + 數字(整數/浮點) | |
DEFAULT | 僅壓縮長 CHAR/VARCHAR 列(MyISAM 預設) |
| 場景 | 建議 | 理由 |
|---|---|---|
| 字符串列有大量共同前綴(URL、路徑、編碼 ID) | PACK_KEYS=1 | 壓縮率極高,索引體積可降 50%+ |
| 整數主鍵/外鍵連續或步長固定 | PACK_KEYS=1 | 高位字節相同,前綴壓縮有效 |
| 隨機 UUID、MD5、雜湊值 | PACK_KEYS=0 | 無共同前綴,反增 1 字節/鍵開銷 |
| 高頻 INSERT/UPDATE 表 | PACK_KEYS=0 | 壓縮/解壓 CPU 開銷大,寫入延遲顯著上升 |
| 唯讀/歸檔/日志表 | PACK_KEYS=1 | 寫入極少,讀取受益最大 |
-- 1. 建立測試表
CREATE TABLE t_packed (
id INT AUTO_INCREMENT PRIMARY KEY,
path VARCHAR(200),
KEY idx_path (path)
) ENGINE=MyISAM PACK_KEYS=1;
CREATE TABLE t_unpacked LIKE t_packed;
ALTER TABLE t_unpacked PACK_KEYS=0;
-- 2. 插入 100 萬條模擬路徑
INSERT INTO t_packed (path) SELECT CONCAT('/var/www/html/project/module_', LPAD(FLOOR(RAND()*100),3,'0'), '/file_', LPAD(seq,6,'0'), '.html') FROM seq_1_to_1000000;
INSERT INTO t_unpacked SELECT * FROM t_packed;
-- 3. 對比索引大小
SELECT TABLE_NAME, INDEX_NAME, STAT_VALUE AS idx_size_bytes
FROM information_schema.INNODB_SYS_INDEXES -- MyISAM 請查 MYISAM_INDEX_STATISTICS 或 SHOW TABLE STATUS
WHERE TABLE_NAME IN ('t_packed','t_unpacked');
-- 4. 查詢效能對比
SET profiling=1;
SELECT * FROM t_packed WHERE path LIKE '/var/www/html/project/module_001/%';
SELECT * FROM t_unpacked WHERE path LIKE '/var/www/html/project/module_001/%';
SHOW PROFILES;
-- 線上修改(會重建表,大表請用 pt-online-schema-change) ALTER TABLE my_table PACK_KEYS=1; -- 或重建表指定 CREATE TABLE new_table (...) ENGINE=MyISAM PACK_KEYS=1; INSERT INTO new_table SELECT * FROM old_table; RENAME TABLE old_table TO old_table_bak, new_table TO old_table;
PACK_KEYS = 「讀多寫少 + 高前綴相似度」的專屬優化開關。 生產環境 90% 以上表不適合開啟。僅在歸檔日志、唯讀報表、靜態資源索引等場景,經壓測驗證「索引體積↓、QPS↑、寫入延遲可接受」後方可啟用。切記:測試數據 ≠ 生產數據分布,必須用生產抽樣數據驗證。