MySQL：儲存引擎介紹

標籤：varchar   選擇   lookup   介紹   mysq   分析   分發   片段   最佳化器   

前言，什麼是儲存引擎

mysql中建立的庫===>檔案夾庫中建立的表===>檔案

　　現實生活中我們用來儲存資料的檔案有不同的類型，每種檔案類型對應各自不同的處理機制：比如處理文本用txt類型，處理表格用excel，處理圖片用png等

　　資料庫中的表也應該有不同的類型，表的類型不同，會對應mysql不同的存取機制，表類型又稱為儲存引擎。

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

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

 

　　SQL 解析器、SQL 最佳化器、緩衝池、儲存引擎等組件在每個資料庫中都存在,但不是每 個資料庫都有這麼多儲存引擎。MySQL 的外掛程式式儲存引擎可以讓儲存引擎層的開發人員設 計他們希望的儲存層,例如,有的應用需要滿足事務的要求,有的應用則不需要對事務有這 麼強的要求 ;有的希望資料能持久儲存,有的只希望放在記憶體中,臨時並快速地提供對資料 的查詢。

一，MySQL儲存引擎

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

　　是MySQL中各種儲存引擎的對比。

1.MyISAM：

　　這種引擎是mysql最早提供的。它不支援事務，也不支援外鍵，尤其是訪問速度快。這種引擎又可以分為靜態MyISAM、動態MyISAM 和壓縮MyISAM三種：

　　1) 靜態MyISAM：如果資料表中的各資料列的長度都是預先固定好的，伺服器將自動選擇這種表類型。因為資料表中每一條記錄所佔用的空間都是一樣的，所以這種表存取和更新的效率非常高。 當資料受損時，恢複工作也比較容易做。這種儲存方式的優點是儲存非常迅速，容易緩衝，出現故障容易恢複；缺點是佔用的空間通常比動態表多。

　　 2) 動態MyISAM：如果資料表中出現varchar、xxxtext或xxxBLOB欄位時，伺服器將自動選擇這種表類型。相對於靜態MyISAM，這種表格儲存體空間比較小，但由於每條記錄的長度不一，所以 多次修改資料後，資料表中的資料就可能離散的儲存在記憶體中，進而導致執行效率下降。同時，記憶體中也可能會出現很多片段。因此，這種類型的表要經常用optimize table命令或者myisamchk -r命令 或 最佳化工具來整理片段、改善效能，並且出現故障的時候恢複相對比較困難。

　　3) 壓縮MyISAM：以上說到的兩種類型的表都可以用myisamchk工具壓縮。這種類型的表進一步減小了佔用的儲存，但是這種表壓縮之後不能再被修改。另外，因為是壓縮資料，所以這種表在讀取的時候要先時行解壓縮。但是，不管是何種MyISAM表，目前它都不支援事務，行級鎖和外鍵約束的功能。

2.Merge：

　　這種類型是MyISAM類型的一種變種。合并表是將幾個相同的MyISAM表合并為一個虛表。常應用於日誌和資料倉儲。

3.InnoDB：

　　InnoDB表類型可以看作是對MyISAM的進一步更新產品，它提供了事務、行級鎖機制和外鍵約束的功能。對比MyISAM的儲存引擎，InnoDB寫的處理效率差一些，並且會佔用更多 的磁碟空間以保留資料和索引。

4.memory：

　　這種類型的資料表只存在於記憶體中。它使用HASH索引，所以資料的存取速度非常快。因為是存在於記憶體中，所以這種類型常應用於暫存資料表中，但是一旦伺服器關閉，表中的資料就會丟失，但表還會繼續存在。預設情況下，memory資料表使用散列索引，利用這種索引進行“相等比較”非常快，但是對“範圍比較”的速度就慢多了。因此，散列索引值適合使用在"="和"<=>"的操作符中，不適合使用在"<"或">"操作符中，也同樣不適合用在order by字句裡。如果確實要使用"<"或">"或betwen操作符，可以使用btree索引來加快速度。

　　儲存在MEMORY資料表裡的資料行使用的是長度不變的格式，因此加快處理速度，這意味著不能使用BLOB和TEXT這樣的長度可變的資料類型。VARCHAR是一種長度可變的類型，但因為它在MySQL內部當作長度固定不變的CHAR類型，所以可以使用。

　　使用USING HASH/BTREE來指定特定到索引：create index mem_hash using hash on tab_memory(city_id);

5.archive：

　　這種類型只支援select 和 insert語句，而且不支援索引。常應用於日誌記錄和彙總分析方面。

6.BLACK HOLE：

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

7.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 的資料庫應用類型。

8.NEST儲存引擎：

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

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

二，儲存引擎如何選擇

是否支援事務檢索和添加速度鎖機制緩衝是否支援全文索引是否支援外鍵

三，MyISAM和InnoDB對比

四，什麼時候使用MyISAM和InnoDB

    MyISAM：讀事務要求不高，以查詢和插入為主，可以使用這個引擎來建立表，例如各種統計表。　　InnoDB：對事務要求高，儲存的是重要的資料例如交易資料，支付資料等，對使用者重要的資料，建議使用InnoDB。

五，對儲存引擎的操作1.查看資料庫預設的儲存引擎：

show engines;   show variables like ‘default_storage_engine‘;

 

2.查看錶的儲存引擎：

　　1) 顯示表的建立語句：

show create table tablename;

 

2) 顯示表的目前狀態值：

show table status like ‘tablename’ \G

　　

3) 設定或修改表的儲存引擎

• 建立資料庫表時設定儲存儲存引擎的基本文法是：

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

　　

• 修改儲存引擎，可以用命令 

Alter table tableName engine = engineName

4）練習

建立四個表，分別使用innodb，myisam，memory，blackhole儲存引擎，進行插入資料測試

MariaDB [db1]> create table t1(id int)engine=innodb;MariaDB [db1]> create table t2(id int)engine=myisam;MariaDB [db1]> create table t3(id int)engine=memory;MariaDB [db1]> create table t4(id int)engine=blackhole;MariaDB [db1]> quit[[email protected] db1]# ls /var/lib/mysql/db1/ #發現後兩種儲存引擎只有表結構，無資料db.opt  t1.frm  t1.ibd  t2.MYD  t2.MYI  t2.frm  t3.frm  t4.frm#memory，在重啟mysql或者重啟機器後，表內資料清空#blackhole，往表插入入任何資料，都相當於丟入黑洞，表內永遠不存記錄

　　

五，配置和資料檔案

1.設定檔預設位置

　　　　Linux: /etc/my.cnf　　　　Windows: my.ini

2.資料檔案位置

　　　1) 查看資料檔案位置的命令： show variables like ‘%datadir%‘ ;

　　　2) 資料檔案格式：

　　　　　　InnoDB：frm（儲存的表結構）、ibd（儲存的資料和索引）

　　　　　　MyISAM：frm（儲存的表結構）、MYD（儲存的資料）、MYI（儲存的索引）

六，資料庫表設計

1.第一範式

　　1) 概念：列不可分。每一列都是不可分割的基本資料項目。

　　2) 例子：假設我們有一個學生表，欄位包括：id,name,age,contact，如下：

　　  

　　   當我們需要根據QQ來查詢學生的時候，就查詢不出，所以以上的設計就不符合1NF。我們可以將contact欄位拆分為phone和QQ，如下：

 　　

　　這樣就滿足1NF了。

2.第二範式　　　

　　1) 概念：1NF的基礎上面，非主屬性完全依賴於主關鍵字。

　　2) 例子：學生表：(學號, 姓名, 年齡, 課程名稱, 成績, 學分) ，從欄位可以看出，此表聯合主鍵是（學號，課程名稱）。

　　　存在如下決定關係：

　　　　1：(學號, 課程名稱) → (姓名, 年齡, 成績, 學分)

　　　　2：(課程名稱) → (學分)

　　　　3：(學號) → (姓名, 年齡)

　　其中，姓名、年齡、學分是部分依賴於主鍵的，而成績是完全依賴於主鍵的，存在部分依賴關係，所以不滿足第二範式。

　　這會造成如下問題：

　　　　(1) 資料冗餘：

　　　　　　同一門課程由n個學生選修，"學分"就重複n-1次；同一個學生選修了m門課程，姓名和年齡就重複了m-1次。

　　　　(2) 更新異常：

　　　　　　若調整了某門課程的學分，資料表中所有行的"學分"值都要更新，否則會出現同一門課程學分不同的情況。

　　　　(3) 插入異常：

　　　　　　假設要開設一門新的課程，暫時還沒有人選修。這樣，由於還沒有"學號"關鍵字，課程名稱和學分也無法記錄入資料 庫。

　　　　(4) 刪除異常：

　　　　　　假設一批學生已經完成課程的選修，這些選修記錄就應該從資料庫表中刪除。但是，與此同時，課程名稱和學分資訊也被刪除了。很顯然，這也會導致插入異常。

　　問題就在於存在非主屬性對主鍵的部分依賴。

　　解決辦法：把原表(學號, 姓名, 年齡, 課程名稱, 成績, 學分)分成三個表：

　　　　　　　學生：Student(學號, 姓名, 年齡)；

　　　　　　　課程：Course(課程名稱, 學分)；

　　　　　　  選課關係：SelectCourse(學號, 課程名稱, 成績)。

3.第三範式

　　1) 概念：2NF的基礎上，屬性不依賴於其它非主屬性 , 消除傳遞依賴。第三範式又可描述為：表中不存在可以確定其他非關鍵字的非關鍵字段。

　　2) 例子：學生表：(學號, 姓名, 年齡, 所在學院, 學院地點, 學院電話)，主鍵必然是學號。

　　　　     由於主鍵是單一屬性，所以非主屬性完全依賴於主鍵，所以必然滿足第二範式。但是存在如下傳遞依賴：

　　　　　　(學號) → (所在學院) → (學院地點, 學院電話)，

　　　　　 學院地點 和 學院電話傳遞依賴於學號，而學院地點和學院電話都是非關鍵字段，即表中出現了“某一非關鍵字段可以確定出其它非關鍵字段”的情況，於是違反了第三範式。　　　　　　　　　　

　　   解決辦法：

　　　　把原表分成兩個表：

　　　　　　學生：(學號, 姓名, 年齡, 所在學院)；

　　　　　　學院：(學院, 地點, 電話)。

MySQL：儲存引擎介紹