Mysql存儲引擎--InnoDB/Myisam/Memory

Mysql存儲引擎

存儲引擎解釋

首先确定一點，存儲引擎的概念是MySQL裡面才有的，不是所有的關系型資料庫都有存儲引擎這個概念，後面我們還會說，但是現在要确定這一點。　　

在講清楚什麼是存儲引擎之前，我們先來個比喻，我們都知道錄制一個視訊檔案，可以轉換成不同的格式，例如mp4，avi，wmv等，而存在我們電腦的磁盤上也會存在于不同類型的檔案系統中如windows裡常見的ntfs、fat32，存在于linux裡常見的ext3，ext4，xfs，但是，給我們或者使用者看懂實際視訊内容都是一樣的。直覺差別是，占用系統的空間大小與清晰程度可能不一樣。

那麼資料庫表裡的資料存儲在資料庫裡及磁盤上和上述的視訊格式及存儲磁盤檔案的系統格式特征類似，也有很多種存儲方式。

但是對于使用者和應用程式來說同樣一張表的資料，無論用什麼引擎來存儲，使用者能夠看到的資料是一樣的。不同儲引擎存取，引擎功能，占用空間大小，讀取性能等可能有差別。說白了，存儲引擎就是在如何存儲資料、提取資料、更新資料等技術方法的實作上，底層的實作方式不同，那麼就會呈現出不同存儲引擎有着一些自己獨有的特點和功能，對應着不同的存取機制。

因為在關系資料庫中資料的存儲是以表的形式存儲的，是以存儲引擎也可以稱為表類型(即：對表的存儲、操作等的實作方法不同)，表是什麼，表本質上就是磁盤上的檔案。

其實MySQL支援多種存儲引擎，每種引擎有着一些自己獨特的功能，使用者在使用的時候，可以根據自己的業務場景來使用不同的存儲引擎，其中MySQL最常用的存儲引擎為：MyISAM和InnoDB。

在詳細介紹這些存儲引擎之前，我們先來看看MySQL的整個工作流程，看一下存儲引擎在哪裡，MySQL都做了哪些事情。

看下圖：這是我在MySQL官方手冊上拿下來的(手冊你如果能夠玩好，将來你就能做一個NB的DBA~~~)

Mysql存儲引擎--InnoDB/Myisam/Memory

中文版

Mysql存儲引擎--InnoDB/Myisam/Memory

資料操作：DML

資料定義語言：DDI

資料控制語言：DCL

1.show engines; 檢視存儲引擎

Mysql存儲引擎--InnoDB/Myisam/Memory

2.show variables like “storage_engine%”; 檢視目前正在使用的引擎

Mysql存儲引擎--InnoDB/Myisam/Memory

注意：在存儲大檔案的時候，可以存到資料庫，但是盡量别直接存到資料庫裡面，會影響資料庫的效率，我們就存檔案的路徑、位址，使用者想要這個大檔案，我們可以到對應的路徑下取讀取這個檔案給使用者。

InnoDB存儲引擎

InnoDB mysql 5.6以上預設的存儲方式支援行級鎖，表級鎖，外鍵，事務保證資料安全資料的完整性而設定的概念樹tree-加速查詢(樹形結構(資料+樹)+表結構)

staff.frm – frame 表結構

staff.ibd innodb data

InnoDB引擎特點

InnoDB引擎
        介紹：InnoDB引擎是MySQL資料庫的另一個重要的存儲引擎，正稱為目前MySQL AB所發行新版的标準，被包含在所有二進制安裝包裡。和其他的存儲引擎相比，InnoDB引擎的優點是支援相容ACID的事務(類似于PostGreSQL)，以及參數完整性(即對外鍵的支援)。Oracle公司與2005年10月收購了Innobase。Innobase采用雙認證授權。它使用GNU發行，也允許其他想将InnoDB結合到商業軟體的團體獲得授權。

InnoDB引擎特點：
        1.支援事務：支援4個事務隔離界别，支援多版本讀。
        2.行級鎖定(更新時一般是鎖定目前行)：通過索引實作，全表掃描仍然會是表鎖，注意間隙鎖的影響。
        3.讀寫阻塞與事務隔離級别相關(有多個級别，這就不介紹啦~)。
        4.具體非常高效的緩存特性：能緩存索引，也能緩存資料。
        5.整個表和主鍵與Cluster方式存儲，組成一顆平衡樹。(了解)
        6.所有SecondaryIndex都會儲存主鍵資訊。(了解)
        7.支援分區，表空間，類似oracle資料庫。
        8.支援外鍵限制，不支援全文索引(5.5之前)，以後的都支援了。
        9.和MyISAM引擎比較，InnoDB對硬體資源要求還是比較高的。
        
        小結：三個重要功能：Supports transactions，row-level locking，and foreign keys 
        外鍵--行級鎖--事物

InnoDB引擎适用的生産業務場景
        1.需要事務支援(具有較好的事務特性，例銀行業務)
        2.行級鎖定對高并發有很好的适應能力，但需要確定查詢是通過索引完成。
        3.資料更新較為頻繁的場景，如：BBS(論壇)、SNS(社交平台)、微網誌等
        4.資料一緻性要求較高的業務，例如：充值轉賬，銀行卡轉賬。
        5.硬體裝置記憶體較大，可以利用InnoDB較好的緩存能力來提高記憶體使用率，
          盡可能減少磁盤IO，可以通過一些參數來設定，這個就不細講啦~~~
        6.相比MyISAM引擎，Innodb引擎更消耗資源，速度沒有MyISAM引擎快

InnoDB引擎調優精要
        1.主鍵盡可能小，避免給Secondery index帶來過大的空間負擔。
        2.避免全表掃描，因為會使用表鎖。
        3.盡可能緩存所有的索引和資料，提高響應速度，較少磁盤IO消耗。
        4.在大批量小插入的時候，盡量自己控制事務而不要使用autocommit自動送出，有開關可以控制送出方式。
        5合理設定innodb_flush_log_at_trx_commit參數值，不要過度追求安全性。
        如果innodb_flush_log_at_trx_commit的值為0，log buffer每秒就會被刷寫日志檔案到磁盤，送出事務的時候不做任何操作。
        6.避免主鍵更新，因為這會帶來大量的資料移動。

InnoDB 存儲引擎将資料放在一個邏輯的表空間中,這個表空間就像黑盒一樣由 InnoDB 存儲引擎自身來管理。從 MySQL 4.1(包括 4.1)版本開始,可以将每個 InnoDB 存儲引擎的表單獨存放到一個獨立的 ibd 檔案中。此外,InnoDB 存儲引擎支援将裸裝置(row disk)用于建立其表空間。

InnoDB 通過使用多版本并發控制(MVCC)來獲得高并發性,并且實作了 SQL 标準的 4 種隔離級别,預設為REPEATABLE 級别,同時使用一種稱為 netx-key locking 的政策來避免幻讀(phantom)現象的産生。除此之外,InnoDB 存儲引擎還提供了插入緩沖(insert buffer)、二次寫(double write)、自适應哈希索引(adaptive hash index)、預讀(read ahead) 等高性能和高可用的功能。

對于表中資料的存儲,InnoDB 存儲引擎采用了聚集(clustered)的方式,每張表都是按主鍵的順序進行存儲的,如果沒有顯式地在表定義時指定主鍵,InnoDB 存儲引擎會為每一行生成一個 6 位元組的 ROWID,并以此作為主鍵。

InnoDB存儲引擎是MySQL資料庫最為常用的一種引擎,Facebook、Google、Yahoo 等公司的成功應用已經證明了 InnoDB 存儲引擎具備高可用性、高性能以及高可擴充性。對其底層實作的掌握和了解也需要時間和技術的積累。如果想深入了解 InnoDB 存儲引擎的工作原理、實作和應用,可以參考《MySQL 技術内幕:InnoDB 存儲引擎》一書。

Myisam存儲引擎

MyISAM 5.5版本以下預設的存儲方式，支援表級鎖，不支援事務樹tree-加速查詢(樹形結構+資料+表結構)

staff1.frm – frame 表結構

staff1.MYD – data資料

staff1.MYI – index 索引

MyISAM引擎特點：

1.不支援事務
	事務是指邏輯上的一組操作，組成這組操作的各個單元，要麼全成功要麼全失敗。
	
2.表級鎖定
	資料更新時鎖定整個表：其鎖定機制是表級鎖定，也就是對表中的一個資料進行操作都會将這個表鎖定，其他人不能操作這個表，這雖然可以讓鎖定的實作成本很小但是也同時大大降低了其并發性能。
	
3.讀寫互相阻塞
	不僅會在寫入的時候阻塞讀取，MyISAM還會再讀取的時候阻塞寫入，但讀本身并不會阻塞另外的讀。
	
4.隻會緩存索引
	MyISAM可以通過key_buffer_size的值來提高緩存索引，以大大提高通路性能減少磁盤IO，但是這個緩存區隻會緩存索引，而不會緩存資料。
        
5.讀取速度較快
	占用資源相對較少

6.不支援外鍵限制，但隻是全文索引

7.MyISAM引擎是MySQL5.5版本之前的預設引擎，是對最初的ISAM引擎優化的産物。

MyISAM引擎适用的生産業務場景
        1.不需要事務支援的業務(例如轉賬就不行，充值也不行)
        2.一般為讀資料比較多的應用，讀寫都頻繁場景不适合，讀多或者寫多的都适合。
        3.讀寫并發通路都相對較低的業務(純讀純寫高并發也可以)(鎖定機制問題)
        4.資料修改相對較少的業務(阻塞問題)
        5.以讀為主的業務，例如：www.blog,圖檔資訊資料庫，使用者資料庫，商品庫等業務
        6.對資料一緻性要求不是很高的業務。
        7.中小型的網站部分業務會用。
        小結：單一對資料庫的操作都可以示用MyISAM，所謂單一就是盡量純讀，或純寫(insert,update,delete)等。

MyISAM引擎調優精要
        1.設定合适的索引(緩存機制)(where、join後面的列建立索引，重複值比較少的建索引等)
        2.調整讀寫優先級，根據實際需求確定重要操作更優先執行，讀寫的時候可以通過參數設定優先級。
        3.啟用延遲插入改善大批量寫入性能(降低寫入頻率，盡可能多條資料一次性寫入)。
        4.盡量順序操作讓insert資料都寫入到尾部，較少阻塞。
        5.分解大的操作，降低單個操作的阻塞時間，就像作業系統控制cpu分片一樣。
        6.降低并發數(減少對MySQL通路)，某些高并發場景通過應用進行排隊隊列機制Q隊列。
        7.對于相對靜态(更改不頻繁)的資料庫資料，充分利用Query Cache(可以通過配置檔案配置)或memcached緩存服務可以極大的提高通路頻率。
        8.MyISAM的Count隻有在全表掃描的時候特别高效，帶有其他條件的count都需要進行實際的資料通路。
        9.可以把主從同步的主庫使用innodb，從庫使用MyISAM引擎。主庫寫，從庫讀可以(不推薦，有些麻煩的地方，市場上有人這麼用)。

其它介紹

不支援事務、表鎖設計、支援全文索引,
主要面向一些OLAP 數 據庫應用,在 MySQL 5.5.8 版本之前是預設的存儲引擎(除 Windows 版本外)。
資料庫系統與檔案系統一個很大的不同在于對事務的支援,
MyISAM 存儲引擎是不支援事務的。
究其根本,這也并不難了解。
使用者在所有的應用中是否都需要事務呢?
在資料倉庫中,如果沒有 **ETL** 這些操作,
隻是簡單地通過報表查詢還需要事務的支援嗎?
此外,MyISAM 存儲引擎的 另一個與衆不同的地方是,
它的緩沖池隻緩存(cache)索引檔案,而不緩存資料檔案,
這與大多數的資料庫都不相同。

其它引擎介紹

NDB 存儲引擎

2003 年,MySQL AB 公司從 Sony Ericsson 公司收購了 NDB 存儲引擎。 NDB 存儲引擎是一個叢集存儲引擎,類似于 Oracle 的 RAC 叢集,不過與 Oracle RAC 的 share everything 結構不同的是,其結構是 share nothing 的叢集架構,是以能提供更進階别的高可用性。NDB 存儲引擎的特點是資料全部放在記憶體中(從 5.1 版本開始,可以将非索引數據放在磁盤上),是以主鍵查找(primary key lookups)的速度極快,并且能夠線上添加 NDB 資料存儲節點(data node)以便線性地提高資料庫性能。由此可見,NDB 存儲引擎是高可用、高性能、高可擴充性的資料庫叢集系統,其面向的也是 OLTP 的資料庫應用類型。

Memory 存儲引擎

正如其名,Memory 存儲引擎中的資料都存放在記憶體中,資料庫重啟或發生崩潰,表中的資料都将消失。它非常适合于存儲 OLTP 資料庫應用中臨時資料的臨時表,也可以作為 OLAP 資料庫應用中資料倉庫的次元表。Memory 存儲引擎預設使用哈希索引,而不是通常熟悉的B+ 樹索引。

MEMORY 記憶體中的儲存斷開連接配接可能會丢失基于hash

Infobright 存儲引擎

第三方的存儲引擎。其特點是存儲是按照列而非行的,是以非常适合 OLAP 的資料庫應用。其官方網站是這裡,上面有不少成功的資料倉庫案例可供分析。

NTSE 存儲引擎

網易公司開發的面向其内部使用的存儲引擎。目前的版本不支援事務, 但提供壓縮、行級緩存等特性,不久的将來會實作面向記憶體的事務支援。

#BLACKHOLE

黑洞存儲引擎，可以應用于主備複制中的分發主庫。

MySQL 資料庫還有很多其他存儲引擎,上述隻是列舉了最為常用的一些引擎。如果你喜歡,完全可以編寫專屬于自己的引擎,這就是開源賦予我們的能力,也是開源的魅力所在。

存儲引擎的使用

1.建立表時指定引擎

create table innodb_t2(id, int) engine=innodb;

2.在配置檔案中指定預設的存儲引擎

linux:vim /etc/my.cnf windows:my.ini檔案

[mysqld]

default-storage-engine=INNODB #配置預設引擎，現在用的mysql預設基本都是InnoDB，是以其實都可以不用配置了

innodb_file_per_table=1 #表示獨立表空間存儲，可以不寫

3.不同引擎在建立表的時候生成檔案也不同

#下面是使用四個不同的引擎來建立的表

create table t1(id int)engine=innodb;

create table t2(id int)engine=myisam;

create table t3(id int)engine=memory;

create table t4(id int)engine=blackhole;

Mysql存儲引擎--InnoDB/Myisam/Memory

1.db.opt檔案：用來記錄該庫的預設字元集編碼和字元集排序規則用的。也就是說如果你建立資料庫指定預設字元集和排序規則，那麼後續建立的表如果沒有指定字元集和排序規則，那麼該建立的表将采用db.opt檔案中指定的屬性。

2.字尾名為.frm的檔案：這個檔案主要是用來描述資料表結構(id,name字段等)和字段長度等資訊

3.字尾名為.ibd的檔案：這個檔案主要儲存的是采用獨立表儲存模式時儲存資料庫的資料資訊和索引資訊；

4.字尾名為.MYD（MYData）的檔案：從名字可以看出，這個是存儲資料庫資料資訊的檔案，主要是存儲采用獨立表儲存模式時存儲的資料資訊；

5.字尾名為.MYI的檔案：這個檔案主要儲存的是資料庫的索引資訊；

6.ibdata1檔案:主要作用也是儲存資料資訊和索引資訊，這個檔案在mysql安裝目錄的data檔案夾下。，

從上面可以看出，.ibd儲存的是資料資訊和索引資訊，ibdata1檔案也是存儲資料資訊和索引資訊，.MYD和.MYI也是分别儲存資料資訊和索引資訊，那他們之間有什麼差別呢？

主要差別是再于資料庫的存儲引擎不一樣，如果儲存引擎采用的是MyISAM，則生成的資料檔案為表名.frm、表名.MYD、表名的MYI;而儲存引擎如果是innoDB，開啟了innodb_file_per_table＝1,也就是采用獨立儲存的模式，生成的檔案是表名.frm、表名.ibd，如果采用共存儲模式的，資料資訊和索引資訊都存儲在ibdata1(在裡面進行分類，從外面看是一個檔案)中;

在進行資料恢複的時候，如果用的是MYISAM資料引擎，那麼資料很好恢複，隻要将相應.frm, .MYD, .MYI檔案拷貝過去即可。但是如果是innodb的話，則每一個資料表都是一個單獨的檔案，隻将相應的.frm和.ibd檔案拷貝過去是不夠的，必須在你的ibd檔案的tablespace id和ibdata1檔案中的元資訊的tablespace id一緻才可以。

msyql人家設定的規則就是這樣存儲表的，使用人家的系統，就要了解人家的規則。

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

Oracle中不存在引擎的概念，資料處理大緻可以分成兩大類：聯機事務處理OLTP（on-line transaction processing）、聯機分析處理OLAP（On-Line Analytical Processing）。OLTP是傳統的關系型資料庫的主要應用，主要是基本的、日常的事務處理，例如銀行交易。OLAP是資料倉庫系統的主要應用，支援複雜的分析操作，側重決策支援，并且提供直覺易懂的查詢結果。

Mysql存儲引擎--InnoDB/Myisam/Memory