MySQL 資料庫儲存引擎的選用
最近在工作上遇到一個資料庫的問題，主要是 MySQL 儲存引擎的選用。但我對各種儲存引擎其實不甚瞭解，在應用上往往也是用預設值，現在面臨了嚴重的問題，只好查資料來幫我釐清觀念。
這篇文章主要討論 Memory, MyISAM, InnoDB 三種儲存引擎，因為我只選用這三種。首先，先看個我做了很久的表格吧！
| 項目 | MyISAM | InnoDB | Memory |
|---|---|---|---|
| 空間限制 | 無 | 64TB | 記憶體 |
| transaction | x | 有 | x |
| 大量 Insert 速度 | 高 | 低 | 高 |
| 設置外來鍵 | x | 有 | x |
| 鎖定層級 | 資料表 | 資料列 | 資料表 |
| 二元樹索引 | 有 | 有 | 不知 |
| 雜湊索引 | x | 有 | 有 |
| 全文搜尋索引 | 有 | x | x |
| 資料壓縮 | 有 | x | x |
| 資料快取 | x | 有 | 有 |
| 索引快取 | 有 | 有 | 有 |
| 記憶體佔用 | 低 | 高 | 中 |
| 磁碟佔用 | 低 | 高 | x |
對於 MyISAM 來說，最大的好處是成本低，而且可以 create views ，這是其他儲存引擎辦不到的，但缺點就是鎖定層級以 table 為單位，而且不支援 transaction ，這些地方輸給 InnoDB 。不過 InnoDB 也是有缺點像是不支援 FULLTEXT 的索引，且記憶體佔用多、磁碟空間耗用大…等等。
我找到一篇文章針對 MyISAM, InnoDB 和 Falcon 來做比較，在這裡面 MyISAM和 InnoDB 表現都沒有差很多，唯獨在測 READ_PK_RANGE 和 READ_KEY_POINT 時候， MyISAM 爛掉了(不過主角其實是 Falcon 因為它被打趴了)。原因是：There MyISAM shows bad scalability with increasing count of thread. I think the reason is pread system call MyISAM uses to access data and retrieving from OS cache is not scaled.
InnoDB vs MyISAM vs Falcon benchmarks – part 1
而 Memory 儲存引擎的最大優點就是快、快、很快、不會對硬碟頻繁讀寫、並且用 HASH 雜湊索引(但不曉得有沒有 Btree 二元樹索引)！另外它有個特性，就是會在硬碟建立一個 .frm 檔，目的是為了存資料表的 scheme ，但是每一筆 record 還是儲存在記憶體中，這也意味著如果斷電或是關機，資料就會消失不見。
實際應用：
假設我有兩個不同類型的 Table ，分別儲存 App Data 和 Session ，我有大量連線，從伺服器上的紀錄看來，開機半天左右，總共處理近九百萬個連線(這是實際數據)。
| 系統開機至現在共進行 8,944,853 次查詢 | |||
|---|---|---|---|
| 總共 | 每小時 | 每分 | 每秒 |
| 8,945 k | 806.51 k | 12.73 k | 224.19 |
而 App Data 的資料表作用和 Session 資料表個作用分別如下：
每個 App 啟動時，都會有一個 Session ID，而每筆 Session 都被當成一筆 Record Insert 到 Table 中做紀錄，當 Session 起始/結束的時候，才把更新的資料寫到 App Data 中。
對於 Session 個資料表的處理，我採用 Memory 為儲存引擎，因為 Session 掉了並不可惜，但卻可以換來極佳的效率，而 App Data 的資料表，我則是採用 InnoDB ，雖然相較於 MyISAM 會花上更多的 Cost 而且效率較差，但是他提供很好的鎖定(以row為單位)以及安全的復原機制，另外也支援外來鍵的設定。
推薦閱讀文章：
MySQL Storage Engine Architecture, Part 3: Details and Comparison
13.4. The MEMORY (HEAP) Storage Engine
MySQL Storage Engines
真用心
k 完那一堆蚯蚓文…囧!
我有蚯蚓恐懼症…Or2
其實可以用 MyISAM
再搭配 memcached 作資料快取，
效能會提昇很多
MyISAM 是否誤植為 MyIASM ？
當時打錯了，已經更正，非常謝謝您。
你好：
想請問一下，關於db搜尋優化的問題
如果有一商品檔的 table，筆數約一百萬筆
消費者如果要搜尋關鍵字，再搭配排序 (一般購物網站的模式 例如：搜尋衣服 依照金額高低排序)
我勢必要在語法上，用 商品名稱 like ‘%關鍵字%’ ，再搭配 order by
偏偏 like 和 order by這兩個，都會讓搜尋時間變長
有嘗試把 商品名稱 做 fulltext，再搭配 match against的語法
雖然測試結果有快很多，但是卻遇到中文斷詞的問題
比如說：有一商品名稱 “我要成為海賊王魯夫”，
我下關鍵字 “海賊王”，就搜尋不到，
如果商品名稱是 “我要成為 海賊王 魯夫”，才可以搜尋得到
後來有找一下斷詞的套件，但是覺得套件的規則太主觀了，可能不適用
想詢問一下你的意見，有無其他方式可以優化關鍵字搜尋
其他的大型拍賣網站，商品數一定是數千萬數億的商品
可是搜尋速度卻很快，他們的做法又為何呢？
Hi, 您好，首先謝謝您的提問，
關於您提到的搜尋的部分，like ‘%關鍵字%’ 都會造成 full table scan 所以速度會慢都是正常的。
所以各大網站不會直接對資料庫下這樣的 SQL Query ，而是透過搜尋引擎來實現，例如使用 Sphinx 。
另外斷詞開源套件目前我聽過比較知名是「結巴」
https://github.com/fukuball/jieba-php
謝謝你的建議 我在try看看!!