MySQL常用存儲(chǔ)引擎

MySQL常用存儲(chǔ)引擎之MyISAM

MySQL 5.5 之前版本默認(rèn)存儲(chǔ)引擎，在排序、分組等操作中，當(dāng)數(shù)量超過一定大小之后，由查詢優(yōu)化器建立的臨時(shí)表。

MyISAM 存儲(chǔ)引擎表由 MYD (表數(shù)據(jù)) 、MYI (表索引) 與 frm (表結(jié)構(gòu)) 組成。

MyISAM 特性

• 并發(fā)性與鎖級(jí)別

表級(jí)鎖

• 表損壞修復(fù)

check table tableName
repair table tabelName

• MyISAM 表支持的索引類型

fulltext

• MyISAM表支持?jǐn)?shù)據(jù)壓縮

myisampack

壓縮后的表無法進(jìn)行寫操作，只能進(jìn)行讀操作。

MyISAM限制

在 MySQL 版本小于 5.0 時(shí)，默認(rèn)表大小為 4G，如存儲(chǔ)大表則要修改表的 MAX_Rows 和 AVG_ROW_LENGTH 參數(shù)。

在MySQL版本大于5.0時(shí)，默認(rèn)支持為 256TB。

MyISAM 適用場(chǎng)景

非事務(wù)型應(yīng)用

只讀類應(yīng)用

空間類的應(yīng)用

MySQL常用存儲(chǔ)引擎之InnoDB

MySQL5.5 及之后版本默認(rèn)存儲(chǔ)引擎。

InnoDB 使用表空間進(jìn)行數(shù)據(jù)存儲(chǔ)

• 如果數(shù)據(jù)庫的配置參數(shù) innodb_file_pre_table => on，那么創(chuàng)建后的 InnoDB 引擎的表將產(chǎn)生 tableName.frm 和 tableName.ibd 文件。

• 如果數(shù)據(jù)庫的配置參數(shù) innodb_file_pre_table => off，那么創(chuàng)建后的  InnoDB 引擎的表將產(chǎn)生 ibdata*（共享表空間） 和 tableName.frm

那么，系統(tǒng)表空間和獨(dú)立表空間要如何選擇呢？

• innodb_file_pre_table => off 建立的表情況下，系統(tǒng)表空間無法簡(jiǎn)單的收縮系統(tǒng)文件大小，造成大量的空間浪費(fèi)，并且會(huì)產(chǎn)生大量的磁盤碎片，從而降低系統(tǒng)的性能。

• 獨(dú)立表空間可以通過 optimize table 命令收縮系統(tǒng)文件。

• 系統(tǒng)表空間會(huì)產(chǎn)生IO瓶頸

• 獨(dú)立表空間可以同時(shí)向多個(gè)文件刷新數(shù)據(jù)

建議對(duì) InnoDB 使用獨(dú)立表空間，即 innodb_file_pre_table => on

InnoDB 特性

• InnoDB 是一種事務(wù)性存儲(chǔ)引擎

• 完全支持事務(wù)的 ACID 特性

• InnoDB 支持行級(jí)鎖

行級(jí)鎖可以最大程度的支持并發(fā)

行級(jí)鎖是有存儲(chǔ)引擎層實(shí)現(xiàn)

• 鎖的類型：

• 共享鎖（也稱讀鎖）

• 獨(dú)占鎖（也稱寫鎖）

• InnoDB 狀態(tài)檢查

show engine innodb status

• 適用場(chǎng)景

InnoDB適用于大多數(shù) OLTP 應(yīng)用

MySQL常用存儲(chǔ)引擎之CSV

文件系統(tǒng)存儲(chǔ)特點(diǎn)

數(shù)據(jù)以文本方式存儲(chǔ)在文件中，他們分別包含有如下文件：

.csv 文件存儲(chǔ)表內(nèi)容

.csm 文件存儲(chǔ)標(biāo)的元數(shù)據(jù)如表狀態(tài)和數(shù)據(jù)量

.frm 文件存儲(chǔ)表結(jié)構(gòu)信息

CSV文件存儲(chǔ)的特點(diǎn)

• 以 csv 格式進(jìn)行數(shù)據(jù)存儲(chǔ)

• 所有列必須都是不能為 null 的數(shù)據(jù)

• 不支持索引

• 可以對(duì)數(shù)據(jù)文件直接編輯

適用場(chǎng)景

適合作為數(shù)據(jù)交換的中間表

MySQL常用存儲(chǔ)引擎之Archive

以 zlib 對(duì)表數(shù)據(jù)進(jìn)行壓縮，磁盤 I/O更少

數(shù)據(jù)存儲(chǔ)在 tableName.ARZ 為后綴的文件中

Archive 文件存儲(chǔ)特點(diǎn)

只支持 insert 和 select 操作

只允許在自增 ID 列上加索引

使用場(chǎng)景

日志和數(shù)據(jù)采集類應(yīng)用

MySQL常用存儲(chǔ)引擎之Memory

文件系統(tǒng)存儲(chǔ)特點(diǎn)

也稱 heap 存儲(chǔ)引擎，所以數(shù)據(jù)保存在內(nèi)存中。

功能特點(diǎn)

• 支持 hash 索引（等值查找快）和 Btree 索引（范圍查找快）

• 所有字段都為固定長(zhǎng)度，例如：varchar(10) = char(10)

• 不支持 BLOB/TEXT 等大字段

• Memory 存儲(chǔ)引擎使用表級(jí)鎖

• 最大大小由 max_heap_table_size 參數(shù)決定

使用場(chǎng)景

• 用于查找或者是映射表，例如：郵編和地區(qū)的對(duì)應(yīng)表

• 用于保存數(shù)據(jù)分析中產(chǎn)生的中間表

• 用于緩存周期性聚合數(shù)據(jù)的結(jié)果表

Memory 數(shù)據(jù)易丟失，所以要求數(shù)據(jù)可再生。

MySQL常用存儲(chǔ)引擎之Federated

默認(rèn)禁止，啟用需要在啟動(dòng)時(shí)增加 federated 參數(shù)。

mysql://user_name[:password]@host_name[:port_num]/db_name/tbl_name

功能特點(diǎn)

• 提供了訪問遠(yuǎn)程MySQL服務(wù)器上表的方法

• 本地不存儲(chǔ)數(shù)據(jù)，數(shù)據(jù)全部放到遠(yuǎn)程服務(wù)器上

• 本地需要保存表結(jié)構(gòu)和遠(yuǎn)程服務(wù)器的連接信息