最近在工作上遇到一個資料庫的問題,主要是 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
謝謝你的建議 我在try看看!!
你好:
想請問一下,關於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
MyISAM 是否誤植為 MyIASM ?
當時打錯了,已經更正,非常謝謝您。
其實可以用 MyISAM
再搭配 memcached 作資料快取,
效能會提昇很多
真用心
k 完那一堆蚯蚓文…囧!
我有蚯蚓恐懼症…Or2