MySQL 將資料塞到 memory 的作法

要加快取資料的速度, 沒有別的方法, 不是先將常用的資料塞到記憶體裡等待查詢, 就是準備好 cache 並先查詢常用的 query, 將結果存在 cache 裡 (俗稱 warm up), 之後再查就會快了。query cache 是 MySQL 能如此快速的原因之一, MySQL 執行 query 前會先查 query cache, SQL 內容一字不差的話, 就直接取值來用。

事先將資料塞入記憶體的作法有幾種, 目前最常用的兩招是:

• 資料不大的話, 就開個 table 用 MEMORY engine 存, 記得將 max_heap_table_size 設大一點。若嫌 MySQL 重開後要重填資料很麻煩, 可以先將資料存在 my_table_on_disk, 照以下步驟產生資料, 三個 SQL 而已:
• CREATE TABLE my_table_in_memory LIKE my_table_on_disk;
• ALTER TABLE my_table_in_memory ENGINE = MEMORY;
• INSERT INTO my_table_in_memory SELECT * FROM my_table;
• 若資料很大或是某些欄位無法存到記憶體裡, 就改對常用的幾個欄位建 covering index。若 engine 能用 MyISAM 的話, 就能用「LOAD INDEX INTO CACHE my_table_on_disk」事先載入 index 到記憶體裡。

使用 MEMORY engine 的注意事項:

• 注意 varchar / char 的長度限制, MEMORY engine 沒有 varchar, 會自動轉成 char, 沒設好可是很揮霍的。
• 用 MEMORY engine 時, 沒指定 index type 的話, 預設用 HASH 而不是 B-Tree。用 = 或 IN 查詢時應該會比用 B-TREE 快, 不過重點是用 HASH 比較省空間, 不管欄位大小為何, hash 後都是一樣大的。但是 HASH 不支援 range query。

用 covering index 的注意事項:

• load index into cache 不是永久性的, 資料有可能被其它 table 的 index 擠走。在意的話, 最好設多個不同的 key cache, 將不希望被擠出 key cache 的 index 放入獨自的 key cache。

其它相關心得:

• MyISAM 無法將資料事先載入記憶體, 而是讓 OS 管 file cache (MyISAM 資料本身是一個大檔案), 先用 select 掃一次 table, 之後操作的確會變快不少, 但之後無法掌握各段資料是否在記憶體裡。所以才會有上面提的 MEMORY engine 和 covering index + load index 的作法。
• InnoDB 有將資料存在 cache 裡, 目前還沒參透 InnoDB 的情況, 可以調的東西太多, 不好上手。