MySql儲存引擎

標籤：

前言

在資料庫中存的就是一張張有著千絲萬縷關係的表，所以表設計的好壞，將直接影響著整個資料庫。而在設計表的時候，我們都會關注一個問題，使用什麼儲存引擎。等一下，儲存引擎？什麼是儲存引擎？

什麼是儲存引擎？

關聯式資料庫表是用於儲存和組織資訊的資料結構，可以將表理解為由行和列組成的表格，類似於Excel的試算表的形式。有的表簡單，有的表複雜，有的表根本不用來儲存任何長期的資料，有的表讀取時非常快，但是插入資料時去很差；而我們在實際開發過程中，就可能需要各種各樣的表，不同的表，就意味著儲存不同類型的資料，資料的處理上也會存在著差異，那麼。對於MySQL來說，它提供了很多種類型的儲存引擎，我們可以根據對資料處理的需求，選擇不同的儲存引擎，從而最大限度的利用MySQL強大的功能。

儲存引擎說白了就是如何儲存資料、如何為儲存的資料建立索引和如何更新、查詢資料等技術的實現方法。因為在關聯式資料庫中資料的儲存是以表的形式儲存的，所以儲存引擎也可以稱為表類型（即儲存和操作此表的類型）。

在Oracle 和SQL Server等資料庫中只有一種儲存引擎，所有資料存放區管理機制都是一樣的。而MySql資料庫提供了多種儲存引擎。使用者可以根據不同的需求為資料表選擇不同的儲存引擎，使用者也可以根據自己的需要編寫自己的儲存引擎。

InnoDB是MySql預設的儲存引擎

MySql的操作

• 顯示mysql支援的操作引擎

show engines;

• 顯示表的結構

DESC sina_oauthuser

• 建立表時，指定儲存引擎

或：

Create table tableName(columnName(列名1)  type(資料類型)  attri(屬性設定),columnName(列名2)  type(資料類型)  attri(屬性設定),……..) engine = engineName

• 查看錶的儲存引擎

SHOW CREATE TABLE sina_cookie

• 查看錶的目前狀態

show table status like ‘sina_cookie‘

• 修改表的儲存引擎

Alter table tableName engine =engineName

MyISAM

在mysql用戶端中，使用以下命令可以查看MySQL支援的引擎。

MyISAM表是獨立於作業系統的，這說明可以輕鬆地將其從Windows伺服器移植到Linux伺服器；每當我們建立一個MyISAM引擎的表時，就會在本地磁碟上建立三個檔案，檔案名稱就是表名。例如，我建立了一個MyISAM引擎的tb_Demo表，那麼就會產生以下三個檔案：

tb_demo.frm，儲存表定義；
tb_demo.MYD，儲存資料；
tb_demo.MYI，儲存索引。

MyISAM表無法處理事務，這就意味著有交易處理需求的表，不能使用MyISAM儲存引擎。MyISAM儲存引擎特別適合在以下幾種情況下使用：

• 選擇密集型的表。MyISAM儲存引擎在篩選大量資料時非常迅速，這是它最突出的優點。
• 插入密集型的表。MyISAM的並發插入特性允許同時選擇和插入資料。例如：MyISAM儲存引擎很適合管理郵件或Web伺服器日誌資料。

InnoDB

InnoDB是一個健壯的事務型儲存引擎，這種儲存引擎已經被很多互連網公司使用，為使用者操作非常大的資料存放區提供了一個強大的解決方案。我的電腦上安裝的MySQL 5.6.13版，InnoDB就是作為預設的儲存引擎。InnoDB還引入了行級鎖定和外鍵約束，在以下場合下，使用InnoDB是最理想的選擇：

• 更新密集的表。InnoDB儲存引擎特別適合處理多重並發的更新要求。
• 事務。InnoDB儲存引擎是支援事務的標準MySQL儲存引擎。
• 自動災難恢複。與其它儲存引擎不同，InnoDB表能夠自動從災難中恢複。
• 外鍵約束。MySQL支援外鍵的儲存引擎只有InnoDB。
• 支援自動增加列AUTO_INCREMENT屬性。

一般來說，如果需要事務支援，並且有較高的並發讀取頻率，InnoDB是不錯的選擇。

MEMORY

使用MySQL Memory儲存引擎的出發點是速度。為得到最快的回應時間，採用的邏輯儲存介質是系統記憶體。雖然在記憶體中儲存表資料確實會提供很高的效能，但當mysqld守護進程崩潰時，所有的Memory資料都會丟失。獲得速度的同時也帶來了一些缺陷。它要求儲存在Memory資料表裡的資料使用的是長度不變的格式，這意味著不能使用BLOB和TEXT這樣的長度可變的資料類型，VARCHAR是一種長度可變的類型，但因為它在MySQL內部當做長度固定不變的CHAR類型，所以可以使用。

一般在以下幾種情況下使用Memory儲存引擎：

• 目標資料較小，而且被非常頻繁地訪問。在記憶體中存放資料，所以會造成記憶體的使用，可以通過參數max_heap_table_size控制Memory表的大小，設定此參數，就可以限制Memory表的最大大小。
• 如果資料是臨時的，而且要求必須立即可用，那麼就可以存放在記憶體表中。
• 儲存在Memory表中的資料如果突然丟失，不會對應用服務產生實質的負面影響。

Memory同時支援散列索引和B樹索引。B樹索引的優於散列索引的是，可以使用部分查詢和通配查詢，也可以使用<、>和>=等操作符方便資料採礦。散列索引進行“相等比較”非常快，但是對“範圍比較”的速度就慢多了，因此散列索引值適合使用在=和<>的操作符中，不適合在<或>操作符中，也同樣不適合用在order by子句中。

可以在表建立時利用USING子句指定要使用的版本。例如：

create table users(    id smallint unsigned not null auto_increment,    username varchar(15) not null,    pwd varchar(15) not null,    index using hash (username),    primary key (id))engine=memory;

上述代碼建立了一個表，在username欄位上使用了HASH散列索引。下面的代碼就建立一個表，使用BTREE索引。

create table users(    id smallint unsigned not null auto_increment,    username varchar(15) not null,    pwd varchar(15) not null,    index using btree (username),    primary key (id))engine=memory;

MERGE

MERGE儲存引擎是一組MyISAM表的組合，這些MyISAM表結構必須完全相同，儘管其使用不如其它引擎突出，但是在某些情況下非常有用。說白了，Merge表就是幾個相同MyISAM表的彙總器；Merge表中並沒有資料，對Merge類型的表可以進行查詢、更新、刪除操作，這些操作實際上是對內部的MyISAM表進行操作。Merge儲存引擎的使用情境。

對於伺服器日誌這種資訊，一般常用的儲存策略是將資料分成很多表，每個名稱與特定的時間端相關。例如：可以用12個相同的表來儲存伺服器日誌資料，每個表用對應各個月份的名字來命名。當有必要基於所有12個日誌表的資料來產生報表，這意味著需要編寫並更新多表查詢，以反映這些表中的資訊。與其編寫這些可能出現錯誤的查詢，不如將這些表合并起來使用一條查詢，之後再刪除Merge表，而不影響原來的資料，刪除Merge表只是刪除Merge表的定義，對內部的表沒有任何影響。

下面就通過一個簡單的例子來說說如何建立引擎為merge類型的表。

create table tb_log1(    id int unsigned not null auto_increment,     log varchar(45),    primary key(id)) engine=myisam;

insert into tb_log1(log) values(‘tb_log1_1‘);insert into tb_log1(log) values(‘tb_log1_2‘);insert into tb_log1(log) values(‘tb_log1_3‘);insert into tb_log1(log) values(‘tb_log1_4‘);insert into tb_log1(log) values(‘tb_log1_5‘);

create table tb_log2(    id int unsigned not null auto_increment,    log varchar(45),    primary key(id)) engine=myisam;

insert into tb_log2(log) values(‘tb_log2_1‘);insert into tb_log2(log) values(‘tb_log2_2‘);insert into tb_log2(log) values(‘tb_log2_3‘);insert into tb_log2(log) values(‘tb_log2_4‘);

先建立兩個引擎為myisam（必須為myisam引擎）的表。插入上述資料，然後建立merge表，進行merge操作。

create table tb_merge(    id int unsigned not null auto_increment,     log varchar(45),     primary key(id))engine=merge     union(tb_log1,tb_log2) insert_method=last;

這樣就得到了一個引擎為merge的表，並且合并了tb_log1和tb_log2兩個表。查詢tb_merge表，可以得到以下資料：

+----+-----------+| id | log       |+----+-----------+|  1 | tb_log1_1 ||  2 | tb_log1_2 ||  3 | tb_log1_3 ||  4 | tb_log1_4 ||  5 | tb_log1_5 ||  1 | tb_log2_1 ||  2 | tb_log2_2 ||  3 | tb_log2_3 ||  4 | tb_log2_4 |+----+-----------+

現在我們主要來解釋一下上面MERGE表的建表語句。

• ENGINE=MERGE

指明使用MERGE引擎，有些同學可能見ENGINE=MRG_MyISAM的例子，也是對的，它們是一回事。

• UNION=(t1, t2)

指明了MERGE表中掛接了些哪表，可以通過alter table的方式修改UNION的值，以實現增刪MERGE表子表的功能。比如：

 alter table tb_merge engine=merge union(tb_log1) insert_method=last;

• INSERT_METHOD=LAST

INSERT_METHOD指明插入方式，取值可以是：0 不允許插入；FIRST 插入到UNION中的第一個表； LAST插入到UNION中的最後一個表。

• MERGE表及構成MERGE資料表結構的各成員資料表必須具有完全一樣的結構。每一個成員資料表的資料列必須按照同樣的順序定義同樣的名字和類型，索引也必須按照同樣的順序和同樣的方式定義。

ARCHIVE

Archive是歸檔的意思，在歸檔之後很多的進階功能就不再支援了，僅僅支援最基本的插入和查詢兩種功能。在MySQL 5.5版以前，Archive是不支援索引，但是在MySQL 5.5以後的版本中就開始支援索引了。Archive擁有很好的壓縮機制，它使用zlib壓縮庫，在記錄被請求時會即時壓縮，所以它經常被用來當做倉庫使用。

儲存引擎的一些問題

• 如何查看伺服器有哪些儲存引擎可以使用？

為確定你的MySQL伺服器可以用哪些儲存引擎，執行如下命令： show engines;這個命令就能搞定了。

• 如何選擇合適的儲存引擎？

選擇標準可以分為：

（1）是否需要支援事務；
（2）是否需要使用熱備；
（3）崩潰恢複：能否接受崩潰；
（4）是否需要外鍵支援；

然後按照標準，選擇對應的儲存引擎即可。

• InnoDB與MyISAM

https://www.pureweber.com/article/myisam-vs-innodb/

MySql儲存引擎