發表者討論內容

冷日
(冷日) 發表時間：2012/10/2 2:43

Webmaster

註冊日: 2008/2/19
來自:
發表數: 15771

[轉貼]MySQL 資料庫儲存引擎的選用

MySQL 資料庫儲存引擎的選用

最近在工作上遇到一個資料庫的問題，主要是 MySQL 儲存引擎的選用。但我對各種儲存引擎其實不甚瞭解，在應用上往往也是用預設值，現在面臨了嚴重的問題，只好查資料來幫我釐清觀念。

這篇文章主要討論 Memory, MyIASM, InnoDB 三種儲存引擎，因為我只選用這三種。首先，先看個我做了很久的表格吧！

項目	MyIASM	InnoDB	Memory
空間限制	無	64TB	記憶體
transaction	x	有	x
大量 Insert 速度	高	低	高
設置外來鍵	x	有	x
鎖定層級	資料表	資料列	資料表
二元樹索引	有	有	不知
雜湊索引	x	有	有
全文搜尋索引	有	x	x
資料壓縮	有	x	x
資料快取	x	有	有
索引快取	有	有	有
記憶體佔用	低	高	中
磁碟佔用	低	高	x

對於 MyIASM 來說，最大的好處是成本低，而且可以 create views ，這是其他儲存引擎辦不到的，但缺點就是鎖定層級以 table 為單位，而且不支援 transaction ，這些地方輸給 InnoDB 。不過 InnoDB 也是有缺點像是不支援 FULLTEXT 的索引，且記憶體佔用多、磁碟空間耗用大...等等。

我找到一篇文章針對 MyIASM, InnoDB 和 Falcon 來做比較，在這裡面 MyIASM和 InnoDB 表現都沒有差很多，唯獨在測 READ_PK_RANGE 和 READ_KEY_POINT 時候， MyIASM 爛掉了(不過主角其實是 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 ，雖然相較於 MyIASM 會花上更多的 Cost 而且效率較差，但是他提供很好的鎖定(以row為單位)以及安全的復原機制，另外也支援外來鍵的設定。

原文出處： roga's blog » MySQL 資料庫儲存引擎的選用

回覆

冷日
(冷日) 發表時間：2012/10/2 2:48

Webmaster

註冊日: 2008/2/19
來自:
發表數: 15771

[轉貼][Mysql進階]各Engine Type(MyISAM / InnoDB / Memory) 的特性說明

Mysql的 Engine Type有幾種常用到的，以下是

MySQL 5.0 Reference Manual中之資料，邊看邊快速的紀錄一下各項的差異和使用時機

設定引擎建，議用 ENGINE = MyISAM 方式指定，Type = 為較舊的方法(也支援)

■ MyISAM :

1.
效能優，預設引擎，為 Mysql最早使用的引擎

2. 使用MyISAM將具備三種檔案：

.frm (table format) .MYD(data file) .MYI(index file)

3. 所有資料是以low byte first儲存，數字型key是以high byte first儲存以利索引

4. 支援大檔 ( > 63 bit)

5. 支援42億(2^32次方) 資料筆數，如果MySQL編譯有加入--with-big-tables 選項可支援多一倍(2^32次方 * 2)資料量

6. 不支援事務處理，例如關聯式資料庫

7.AUTO_INCREMENT
更快（至少10%），序列的值被刪除之後就不能再利用

■ InnoDB:

1. 具備Commit, Rollback和當掉復原的事務處理能力，可保護使用者資料

2. 可進行row-level的鎖定同時維持nonlocking reads，以支援多人同時連線狀態

3. 可支援FOREIGN KEY

4.InnoDB 是專門為了大容量資料的最大效率進行設計的，CPU效能是其他的disk-based資料庫引擎所不及的

5.InnoDB 儲存他的表和 index在一個表空間中，此表空間可能包含幾個檔(有可能在不同disk partitions)，此點和MyISAM不同，MyISAM是一個表一個檔案，這差一點造成InnoDB表個可以非常大，即使是超過系統的file size(例如2GB)都可處理

6. 此為Windows安裝檔的默認值

7.InnoDB 上可處理存儲超過1TB的數據，可支援例如平均每秒800次插入/更新的負荷

8. 支援關聯資料庫

■ InnoDB 使用上的限制：

1. 不要將MyISAM轉成InnoDB table

2. 不要在NFS格式上將 InnoDB設定成data files或log file，檔案容易被鎖住

3. 一個表不能包含超過1000 欄

4.
內部key最大長度為 3500 bytes，MySQL限制為3072 Bytes

5. 每筆資料最長約為8000 Bytes，變長的欄位(例如Varchar/BLOB/TEXT)需小於4000 Bytes.

6. 即使InnoDB支援row size大於65535，也不能定義一個包含VARBINARY/VARCHAR的欄位總合大於65535 (會出現錯誤訊息)

7.
在依些舊系統中，檔案需小於 2GB，這並不限制InnoDB的使用，但你如果需要大的tablespace則需額外進行設定

8.InnoDB 的log file總共大小需小於4GB

9. 最小的tablespace 大小為10MB，最大的大小為64TB，這也是table的最大值

10. 不支援FULLTEXT

11.InnoDB 不保留每個表個的總數統計，也就是count(*)則必須進行index計算

12. 在InnoDB 中，AUTO_INCREMENT欄位如果存在，則"必須"定義一個index欄位包含AUTO_INCREMENT欄；在MyISAM 中，AUTO_INCREMENT欄位"可能"為index中的一欄

13.DELETE FROM Table 為逐筆資料刪除，非整個table刪除

14. 在InnoDB 中，TRUNCATE Table相同於DELETE Table，AUTO_INCREMENT不重置

■ Memory( 相同於HEAP):

1. 將所有數據保存在RAM中，可提供極快的訪問，但如果關機資料就會消失無法儲存

2. 每一個MEMORY Table關聯一個Disk file(.frm)

3. 常使用在 create temporary table上，連線結束後就釋放空間

4.
預設使用hash indexes(速度非常快且對於建立臨時表格非常有效)

5.MEMORY 不支持BLOB 或TEXT列

6.MEMORY 使用定長的儲存格式列

7. 可支援 AUTO_INCREMENT

■ 相關資料庫語法:

SHOW ENGINES ; // 顯示可使用的Engine

SET storage_engine=MYISAM; //
設定此連線時，create table預設的Engine

SHOW CREATE TABLE tbl_name; // 顯示資料庫建立語法

■ 其它參考:

各種Engine的比較表格有人整理好了，可參考
MySQL 資料庫儲存引擎的選用

原文出處： [Mysql進階]各Engine Type(MyISAM / InnoDB / Memory) 的特性說明 @ 麥克的學習紀錄 :: 痞客邦 PIXNET ::

回覆

冷日
(冷日) 發表時間：2012/10/2 2:53

Webmaster

註冊日: 2008/2/19
來自:
發表數: 15771

[轉貼] MySQL 中 MyISAM 引擎與 InnoDB 引擎性能比較

MySQL中MyISAM引擎與InnoDB引擎性能比較

MySQL中MyISAM引擎與InnoDB引擎性能比較，基本上我們可以考慮使用InnoDB來替代我們的MyISAM引擎。

MySQL表結構

CREATE TABLE `myisam` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(100) default NULL,
  `content` text,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=gbk;

CREATE TABLE `innodb` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(100) default NULL,
  `content` text,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;

數據內容

$name = "heiyeluren";
$content = "MySQL支持數個存儲引擎作為對不同表的類型的處理器。
         MySQL存儲引擎包括處理事務安全表的引擎和處理非事務安全表的引擎：
         · MyISAM管理非事務表。它提供高速存儲和檢索，以及全文搜索能力。
         MyISAM在所有MySQL配置裡被支持，它是默認的存儲引擎，除非你配置MySQL默認使用另外一個引擎。
         · MEMORY存儲引擎提供「內存中」表。
         MERGE存儲引擎允許集合將被處理同樣的MyISAM表作為一個單獨的表。
         就像MyISAM一樣，MEMORY和MERGE存儲引擎處理非事務表，這兩個引擎也都被默認包含在MySQL中。
         釋：MEMORY存儲引擎正式地被確定為HEAP引擎。
         · InnoDB和BDB存儲引擎提供事務安全表。
         BDB被包含在為支持它的操作系統發佈的MySQL-Max二進制分發版裡。
         InnoDB也默認被包括在所有MySQL 5.1二進制分發版裡，你可以按照喜好通過配置MySQL來允許或禁止任一引擎。
         · EXAMPLE存儲引擎是一個「存根」引擎，它不做什麼。
         你可以用這個引擎創建表，但沒有數據被存儲於其中或從其中檢索。
         這個引擎的目的是服務，在MySQL源代碼中的一個例子，它演示說明如何開始編寫新存儲引擎。
         同樣，它的主要興趣是對開發者。";

[插入數據-1]

(innodb_flush_log_at_trx_commit=1)
MyISAM 1W：3/s
InnoDB 1W：219/s

MyISAM 10W：29/s
InnoDB 10W：2092/s

MyISAM 100W：287/s
InnoDB 100W：沒敢測試

[插入數據-2]

(innodb_flush_log_at_trx_commit=0)
MyISAM 1W：3/s
InnoDB 1W：3/s

MyISAM 10W：30/s
InnoDB 10W：29/s

MyISAM 100W：273/s
InnoDB 100W：423/s

[插入數據3]

(innodb_buffer_pool_size=1024M)
InnoDB 1W：3/s
InnoDB 10W：33/s
InnoDB 100W：607/s

[插入數據4]

(innodb_buffer_pool_size=256M, innodb_flush_log_at_trx_commit=1, set autocommit=0)
InnoDB 1W：3/s
InnoDB 10W：26/s
InnoDB 100W：379/s

[MySQL 配置文件] (缺省配置)

# MySQL Server Instance Configuration File
[client]
port=3306

[mysql]
default-character-set=gbk

[mysqld]
port=3306
basedir="C:/mysql50/"
datadir="C:/mysql50/Data/"
default-character-set=gbk
default-storage-engine=INNODB
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
max_connections=100

query_cache_size=0
table_cache=256
tmp_table_size=50M
thread_cache_size=8
myisam_max_sort_file_size=100G
myisam_max_extra_sort_file_size=100G
myisam_sort_buffer_size=100M
key_buffer_size=82M
read_buffer_size=64K
read_rnd_buffer_size=256K
sort_buffer_size=256K

innodb_additional_mem_pool_size=4M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=2M
innodb_buffer_pool_size=159M
innodb_log_file_size=80M
innodb_thread_concurrency=8

總結

可以看出在MySQL 5.0里面，MyISAM和InnoDB存儲引擎性能差別並不是很大，針對InnoDB來說，影響性能的主要是 innodb_flush_log_at_trx_commit 這個選項，如果設置為1的話，那麼每次插入數據的時候都會自動提交，導致性能急劇下降，應該是跟刷新日誌有關係，設置為0效率能夠看到明顯提升，當然，同樣你可以SQL中提交「SET AUTOCOMMIT = 0」來設置達到好的性能。另外，還聽說通過設置innodb_buffer_pool_size能夠提升InnoDB的性能，但是我測試發現沒有特別明顯的提升。

基本上我們可以考慮使用InnoDB來替代我們的MyISAM引擎了，因為InnoDB自身很多良好的特點，比如事務支持、存儲過程、視圖、行級鎖定等等，在並發很多的情況下，相信InnoDB的表現肯定要比MyISAM強很多，當然，相應的在my.cnf中的配置也是比較關鍵的，良好的配置，能夠有效的加速你的應用。

如果不是很複雜的Web應用，非關鍵應用，還是可以繼續考慮MyISAM的，這個具體情況可以自己斟酌。

硬件配置

CPU : AMD2500+ (1.8G)
內存: 1G/現代
硬盤: 80G/IDE

軟件配置

OS : Windows XP SP2
SE : PHP5.2.1
DB : MySQL5.0.37
Web: IIS6

本篇文章来源于 PHP资讯原文链接：http://www.phpq.net/mysql/myisam-innodb.html

回覆

主題一覽的下方

討論串

前一個主題 | 下一個主題 | 頁首 |

| |

發表新主題

DB研討會 : [轉貼]MySQL 資料庫儲存引擎的選用

MySQL 資料庫儲存引擎的選用