萬盛學電腦網

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

MyISAM PACK_KEYS 壓縮索引深度解析:前綴壓縮原理、適用場景與效能權衡

MyISAM 引擎提供鮮為人知的 PACK_KEYS 選項,可對索引進行前綴壓縮,大幅縮小索引體積、提升讀取速度,代價是寫入變慢。本文從底層存儲原理出發,解析何時開啟、何時禁用、如何驗證效果。

PACK_KEYS 三種模式

前綴壓縮存儲原理

索引鍵值按順序存儲於 B+ 樹葉子節點。對於相鄰鍵值,MyISAM 採用 前綴壓縮:

  1. 每個鍵值額外存 1 字節,記錄與前一鍵值「相同前綴長度」。
  2. 指向行的指針(通常 4-6 字節)以 高位字節優先 存儲於鍵值之後,進一步增加前綴相同機率。
  3. 若連續鍵值高度相似(如文件名 abc_001.pdf, abc_002.pdf),後續鍵值僅佔 2 字節(前綴長度 + 指針)。
  4. 若所有鍵值完全不同且無 NULL,每鍵額外佔 1 字節(前綴長度 0 標記)。

適用場景判斷

值行為
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;

修改現有表的 PACK_KEYS

-- 線上修改(會重建表,大表請用 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↑、寫入延遲可接受」後方可啟用。切記:測試數據 ≠ 生產數據分布,必須用生產抽樣數據驗證。

copyright © 萬盛學電腦網 all rights reserved