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

Posted in DB
4 comments on “MySQL 資料庫儲存引擎的選用
  1. fredsl says:

    真用心
    k 完那一堆蚯蚓文…囧!
    我有蚯蚓恐懼症…Or2

  2. 野貓 says:

    其實可以用 MyISAM
    再搭配 memcached 作資料快取,
    效能會提昇很多

  3. 路人甲 says:

    MyISAM 是否誤植為 MyIASM ?

1 Pings/Trackbacks for "MySQL 資料庫儲存引擎的選用"
  1. […] 可參考這篇文章http://blog.roga.tw/2008/11/19/1288 本篇發表於 Linux, Mysql。將永久鍊結加入書籤。 ← Mysql隨筆小記_校對為utf8_unicode_ci與utf8_general_ci的差別 […]

Leave a Reply

Your email address will not be published.