MySQL存儲引擎介紹 w

一 存儲引擎解釋

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

　　在講清楚什麼是存儲引擎之前，我們先來個比喻，我們都知道錄製一個視頻文件，可以轉換成不同的格式，例如mp4，avi，wmv等，而存在我們電腦的磁碟上也會存在於不同類型的文件系統中如windows里常見的ntfs、fat32，存在於linux里常見的ext3，ext4，xfs，但是，給我們或者用戶看懂實際視頻內容都是一樣的。直觀區別是，占用系統的空間大小與清晰程度可能不一樣。
那麼資料庫表裡的數據存儲在資料庫里及磁碟上和上述的視頻格式及存儲磁碟文件的系統格式特征類似，也有很多種存儲方式。
　　但是對於用戶和應用程式來說同樣一張表的數據，無論用什麼引擎來存儲，用戶能夠看到的數據是一樣的。不同儲引擎存取，引擎功能，占用空間大小，讀取性能等可能有區別。說白了，存儲引擎就是在如何存儲數據、提取數據、更新數據等技術方法的實現上，底層的實現方式不同，那麼就會呈現出不同存儲引擎有著一些自己獨有的特點和功能，對應著不同的存取機制。
　　因為在關係資料庫中數據的存儲是以表的形式存儲的，所以存儲引擎也可以稱為表類型(即：對錶的存儲、操作等的實現方法不同)，表是什麼，表本質上就是磁碟上的文件。

　　其實MySQL支持多種存儲引擎，每種引擎有著一些自己獨特的功能，用戶在使用的時候，可以根據自己的業務場景來使用不同的存儲引擎，其中MySQL最常用的存儲引擎為：MyISAM和InnoDB。
在詳細介紹這些存儲引擎之前，我們先來看看MySQL的整個工作流程，看一下存儲引擎在哪裡，MySQL都做了哪些事情。

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

​ 　　　　　　

　　英文版的看著很難受啊超哥，好好好，給你來個中文的，看下圖：

　　來，看每個部分的解釋，看下圖：

　　搞明白整個過程之後，我們來細說一下這些存儲引擎，看下一節~~~

二 MySQL存儲引擎分類

　　前面我們對MySQL進行操作的時候並沒有指定存儲引擎，為什麼還能用呢，沒有指定還能用，說明什麼，說明MySQL預設有一個存儲引擎，我記得MySQL5.5之前，預設的存儲引擎是MyISAM，之後都改為InnoDB了，我們的重點就是這個InnoDB，也是公司中最常用的、最好用的引擎，但是還是一些公司在用MyISAM引擎，除了歷史原因之外，還有MyISAM的效率比InnoDB高一點，InnoDB在這方面做了很多優化，效率已經和MyISAM差不多了，但是InnoDB引擎還有好多其他的NB的功能，下麵我們就介紹幾種存儲引擎。

　　首先看一下咱們的預設存儲引擎：

　　不同的引擎代表著不同的存取機制，那麼有哪些引擎呢，連接進入mysql之後，查看MySQL支持的所有存儲引擎：

　　　　1、show engines；#查看MySQL所有的引擎，

　　　　2、show variables like "storage_engine%";查看當前正在使用的引擎

　　　　註意：在存儲大文件的時候，可以存到資料庫，但是儘量別直接存到資料庫裡面，會影響資料庫的效率，我們就存文件的路徑、地址，用戶想要這個大文件，我們可以到對應的路徑下取讀取這個文件給用戶。

　　幾種常用引擎介紹

　　　　　　1. MyISAM引擎　　

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)索引文件,而不緩存數據文件,這與 大多數的資料庫都不相同。

　　　　　　2. 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 存儲引擎將數據放在一個邏輯的表空間中,這個表空間就像黑盒一樣由 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 存儲引擎》一書。

　　　　　　總說事務，到底啥事事務？我們看看解釋：

事務介紹：
        簡單地說，事務就是指邏輯上的一組SQL語句操作，組成這組操作的各個SQL語句，執行時要麼全成功要麼全失敗。
        例如：你給我轉賬5塊錢，流程如下
            a.從你銀行卡取出5塊錢，剩餘計算money-5
            b.把上面5塊錢打入我的賬戶上，我收到5塊，剩餘計算money+5.
        上述轉賬的過程，對應的sql語句為：
                update 你_account set money=money-5 where name='你'；
                update 我_account set money=money+5 where name='我'；
        上述的兩條SQL操作，在事務中的操作就是要麼都執行，要麼都不執行，不然錢就對不上了。
        這就是事務的原子性(Atomicity)。
    事務的四大特性：
        1.原子性(Atomicity)
            事務是一個不可分割的單位，事務中的所有SQL等操作要麼都發生，要麼都不發生。
        2.一致性(Consistency)
            事務發生前和發生後，數據的完整性必須保持一致。
        3.隔離性(Isolation)
            當併發訪問資料庫時，一個正在執行的事務在執行完畢前，對於其他的會話是不可見的，多個併發事務之間的數據是相互隔離的。也就是其他人的操作在這個事務的執行過程中是看不到這個事務的執行結果的，也就是他們拿到的是這個事務執行之前的內容，等這個事務執行完才能拿到新的數據。
        4.持久性(Durability)
            一個事務一旦被提交，它對資料庫中的數據改變就是永久性的。如果出了錯誤，事務也不允撤銷，只能通過'補償性事務'。
        
    事務的開啟：
        資料庫預設事務是自動提交的，也就是發一條sql他就執行一條。如果想多條sql放在一個事務中執行，則需要使用事務進行處理。當我們開啟一個事務，並且沒有提交，mysql會自動回滾事務。或者我們使用rollback命令手動回滾事務。
        資料庫開啟事務的命令，我們後面會講到~~~

　　　　　　在介紹上面兩個引擎中涉及的兩個概念(OLTP和OLAP)的介紹

　　　　　　參考文章：https://blog.csdn.net/zhangzheng0413/article/details/8271322/

　　　　　　**3.其他引擎的介紹

#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+ 樹索引。

#Infobright 存儲引擎
第三方的存儲引擎。其特點是存儲是按照列而非行的,因此非常 適合 OLAP 的資料庫應用。其官方網站是 http://www.infobright.org/,上面有不少成功的數據 倉庫案例可供分析。

#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、不同引擎在創建表的時候生成文件的不同

　　　　創建四個表，分別使用innodb，myisam，memory，blackhole存儲引擎，進行插入數據測試

#下麵是使用四個不同的引擎來創建的表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;

　　　　通過四種引擎來創建的表，我們對照著其生成的文件來看一下區別，看下圖：

　　　　關於上面的文件作用介紹：

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的存儲引擎大概就說這麼多吧有興趣或者有基礎的同學，可以多去學一下MySQL官方手冊..收穫會有很多那麼我們就繼續回到我們的數據表操作去學習吧