大數據學習之二 MYSQL進階

文章目錄

• 5 MySQL體系結構
• • 5.1 MyISAM
  • 5.2 Innodb
  • • 5.2.1 系統表空間與獨立表空間要如何選擇
    • 5.2.2 Innodb儲存引擎的特性
    • 5.2.3 Innodb狀態檢查
    • 5.2.4 適用場景
    • #5.2.4 (拓展) 什麼是鎖定
    • • 5.2.3.1 什麼是鎖？
      • 5.2.3.2 鎖定的類型：
      • 5.2.3.3 寫入鎖定和讀取鎖定的相容關係（對一行的兼容性情況）
      • 5.2.3.4 鎖定的粒度：
      • 5.2.3.5 阻塞與死鎖
  • #5.3 CSV儲存引擎
  • • ##5.3. 1 CSV存儲引擎的特點
    • 5.3.2 CSV存儲引擎的適用場景
  • 5.4 Archive存儲引擎
  • • 5.4 .1 檔案系統儲存特性
    • 5.4.2 Archive儲存引擎的特性
    • 5.4.3 Archive儲存引擎使用場景
  • 5.5 Memory儲存引擎
  • • 5.5.1 檔案系統儲存特點
    • 5.5.2 Memory的功能特點
    • 5.5.3 Memory中容易混淆的概念
    • 5.5.4 Memory的使用情境
  ##5.6 Federated儲存引擎
  • 5.6.1 Federated的特性
    • 5.6.2 Federated如何使用
    • 5.6.3 Federated的適用場景
6 如何選擇正確的儲存引擎

#接上一篇文章

5 MySQL體系架構

# 以下我們選一些比較常用的存儲引擎進行簡單的說明，mysql所使用的儲存引擎會對資料庫的效能產生直接的影響，也希望各位能仔細的了解儲存引擎的一些特點，完了之後才使用儲存引擎。

5.1 MyISAM

MyISAM在MySQL5.5之前版本是預設的儲存引擎。由於這個原因，還有很多伺服器在使用MyISAM這個儲存引擎。同時，MyISAM目前是許多系統表，臨時表所使用的儲存引擎，這裡說的臨時表不是我們透過

create table建立出來的表，是指在排序、分組等操作中，當數量超過一定的大小之後，有查詢優化器建立的臨時表。 MyISAM儲存引擎是由MYD和MYI組成，MYD是資料檔案的副檔名，MYI是索引檔案的副檔名，這個儲存引擎是將資料表儲存在以這兩個為副檔名的資料檔案和索引文件中。

特性：

並發性與鎖定層級
• MyISAM使用的是表格層級鎖定，並非行級鎖，這也意味著對錶中的資料進行修改時，需要對整個表進行加鎖，而在對錶的讀取時也對所有的表加共享鎖，從這裡我們可以看到，使用MyISAM做引擎的表讀取和寫入兩種操作是互斥的。由此可以看到MyISAM對於讀寫的並發操作並不會很好。如果只對於只讀取操作的話，就並發性而言，效能也還不錯，因為共用鎖定不會阻塞共用鎖定。
  
表格損壞修復
• MyISAM支援因任意意外關閉而損壞的MyISAM表，進行檢查和修復操作，但是這裡說的修復並不是資料的恢復，因為MyISAM並不是一種事務性的儲存引擎，所以它不能進行交易復原所需的相關日誌，所以要注意MyISAM表的復原可能會造成資料的遺失。
  我們可以透過
  check table tablename對表格進行檢查，透過repair table tablename對表格進行還原。
MyISAM表支援的索引類型
• MyISAM支援全文索引，並且在mysql5.7版本之前唯一原生就支援全文索引的官方儲存引擎。
  
MyISAM表支援資料壓縮
• 如果MyISAM表示一張很大的唯讀表，也就是在表創建完導入資料後，就不會對表進行任何修改操作了，那我們就可以對錶進行壓縮了，以減少磁碟I/O。我們可以使用
  myisampack指令來壓縮表。壓縮是對錶獨立進行壓縮的，因此讀取一行資料的時候，不必對整個表進行解壓縮。

限制：

版本如儲存大表則要修改MAX_Rows 和AVG_ROW_LENGTH
• 版本> mysql5.0時預設支援位元256TB

適用場景：

非事務性應用
• 只讀類別應用（報表之類）
• 空間類別應用程式

5.2 Innodb#

Innodb是MySQL5.5及之後版本預設的儲存引擎，Innodb是交易儲存的儲存引擎，也就是說支援事務的處理。
Innodb有自己的表空間的概念，然後資料是儲存在表空間中的，是由innodb_file_per_table這個參數來決定的，如果這個參數是為ON#則會對每個Innodb表建立一個副檔名為ibd的系統文件，如果這個參數為OFF時，則會把資料儲存到系統的共用表空間，也就是ibdataX，X代表的時一個數字，預設從1開始。
看這個參數的指令為：
show variables like 'innodb_file_per_table';
修改這個參數的指令為：
set global innodb_file_per_table=off;

5.2.1 系統表空間與獨立表空間要如何選擇

比較：

##系統表空間獨立表空間#無法簡單的收縮檔案大小可以透過會產生IO瓶頸可以同時向多個檔案刷新資料

	optimize table指令縮小系統檔案

建議：

對Innodb使用獨立表空間

把原來存在於系統表空間中的表轉移到獨立表空間中的方法。

步驟：

使用mysqldump匯出所有資料庫表格資料
1. 停止MySQL服務，修改參數，並刪除Innodb相關檔案
2. #重新啟動MySQL服務，重建Innodb系統表空間
3. 重新匯入資料

#5.2.2 Innodb儲存引擎的特性

#Innodb是一種事務性儲存引擎
• 完全支援事務的ACID特性（之前介紹過的原子性，一致性等）
• Redo Log 和Undo Log
• Redo Log實現了交易的持久性，有兩個部分組成，其中一個是記憶體中的工作日誌持久緩衝區，是由innodb_log_buffer_size決定它的大小，另一個是重構日誌文件，也就是我們在文件系統中看到的ib_logflie的相關文件。 Undo Log實現了事務的原子性，在事務失敗時進行回滾操作。 Redo Log是順序讀寫的，Undo Log是隨機讀寫的，如果可以的話可以將資料儲存在固態硬碟中，以提高效能。
  
Innodb支援行級鎖
• 行級鎖和表級鎖是不一樣的，行級鎖的特點就是可以最大程度的支援並發，行級鎖是由儲存引擎層實現的。
  

5.2.3 Innodb狀態檢查

可以使用下列指令對Innodb狀態進行檢查：

show engine innodb status

5.2.4 適用場景

Innodb適合大多數OLTP應用，因為在mysql5.7版本之後，Innodb已經支援了全文索引和空間函數。

5.2.4 (拓展) 什麼是鎖定

5.2.3.1 什麼是鎖定？

鎖定最主要作用是管理共享資源的並發存取
• 鎖定用於實現交易的隔離性

5.2 .3.2 鎖的型別：

共享鎖（也稱為讀鎖）
• 獨佔鎖（也稱為寫鎖）

5.2.3.3 寫入鎖定和讀取鎖定的相容關係（對一行的兼容性情況）

寫鎖讀取鎖定寫入鎖定#不相容############# ######讀取鎖定######不相容######相容#############

在實際情況中，可能與上表的結果會有所不同，主要是因為Innodb中的鎖機制是很複雜的一樣東西，還有很多鎖的存在影響最終的結果。

5.2.3.4 鎖定的粒度：

• 表格級鎖定
• 行級鎖定

• 
  
  
• 
  
• 
  #5.2.3.5 阻塞和死鎖
• 
  阻塞：阻塞是因為不同鎖之間的兼容性的關係，在有些時刻一個事務中的鎖需要等待另一事務的鎖釋放，它所佔用的資源形成了阻塞。
死鎖：死鎖是指兩個或兩個以上的事務執行過程中，相互佔用了對方等待的資源而產生的一種異常。從定義中可以看到，處在阻塞中的多個事務佔用了被阻塞的事務等待的資源，而死鎖是多個阻塞的事務互相佔用了對方等待的資源。

5.3 CSV儲存引擎

CSV儲存引擎可以將csv文件作為mysql的表文件來處理，這種儲存引擎的儲存格式就是普通的csv文件，在csv儲存引擎的資料儲存方式非常的由特點，如果我們把表格儲存在MyISAM或Innodb中，其資料檔案我們是不能直接檢視的，因為這兩種檔案的儲存是以二進位的格式來儲存的，而CSV儲存引擎則不同，CSV的資料是以文字的方式儲存在檔案中的，也就是我們可以透過查看檔案的命令來查看，如more，或是使用vi指令來查看編輯csv儲存引擎中的表，只要符合CSV檔案的格式和要求，我們就不用擔心損壞資料。
當我們在mysql中建立了CSV儲存引擎表時，我們應該可以看到3個檔案系統中的檔案。這3個都是以表名為檔名，但會分別以csv，csm，frm為後綴，其中csv檔就是CSV儲存引擎中的資料檔。 csm檔案儲存表的元資料和表狀態和資料量。 frm檔案儲存表結構資訊。
5.3.1 CSV儲存引擎的特點

#最大的特點是以CSV格式進行資料儲存

CSV中的每一列都是以

,來分隔的，而且文字的內容是以雙引號來引起來的，如下圖所示：

所有欄位必須都是不能為NULL的

在建表的時候所有的列都必須是非空的，不能儲存為NULL的值

不支援索引 不適合大表，不適合線上處理

可以對資料檔案直接編輯
• 儲存文字檔案內容5.3.2 CSV儲存引擎的適用場景
• CSV儲存引擎適合作為資料交換的中間表

5.4 Archive儲存引擎

5.4.1 檔案系統儲存特性

Archive儲存引擎會快取所有的寫，並且利用zlib對插入的行進行壓縮，因此Archive儲存引擎相對於MyISAM儲存引擎的表更加節省磁碟I/O，對於同樣數量級的數據，Archive儲存引擎相對於MyISAM和Innodb更加節省儲存空間。一個幾T的Innodb的表儲存在Archive儲存引擎當中，可能只需要幾百兆的儲存空間。 Archive儲存引擎的表格的資料是以ARZ為後綴的一個文件，和其他引擎一樣，也存在一個以frm為後綴的系統文件，用於儲存表格的結構資訊。

######5.4.2 Archive儲存引擎的特點#############只支援###insert###和###select###動作### ###只允許在自增ID列上加索引############5.4.3 Archive儲存引擎使用場景#########場景1：日誌和資料擷取類資料### 因為Archive不支援修改和刪除，而我們ORDB一定會對資料進行修改的，但是對於一些倉庫類型的應用，或者一些特殊的表，還是有用的，比如說，記錄日誌的表或者是資料收集類別的表，因為它需要收集大量數據，所以比較適合使用Archive儲存引擎。因為Archive儲存引擎在所有引擎中來說，它的儲存空間是最小的，但是還是要注意，即使在資料擷取或日誌的應用中，Archive儲存引擎是無法對這些資料進行更新的，所以在記錄日誌或者在資料擷取類別應用中對資料進行修改的話，可能也無法使用Archive儲存引擎。 #########5.5 Memory儲存引擎############5.5.1 檔案系統儲存特性######

Memory儲存引擎也稱為HEAP儲存引擎，所以資料都保存在記憶體中， 這就表示這中資料的表是一次性的，一旦MySQL服務重啟，所有Memory儲存引擎的資料都會消失，但是表結構會保留下來，因為在Memory儲存引擎下建立表，只會產生一個frm系統文件，該文件是用來保存表結構的。這就是為什麼重啟MySQL伺服器資料會遺失，表結構不會的原因。
從它的檔案儲存特點我們可以知道，Memory儲存引擎的I/O效率會比MyISAM高很多，因為MyISAM只有索引會保存在記憶體中，而資料則由作業系統來快取的，而Memory存儲引擎所有資料和索引都保存在記憶體中，下面我們來看看Memory儲存引擎的功能特性。

5.5.2 Memory的功能特點

功能特徵：

• 支援HASH索引（預設）和BTree索引
  如果是HASH索引在做等值查詢的時候會非常的快，如果是做範圍查詢的話就無法使用HASH索引了，所以在表創建的時候我們需要注意，如果表需要大量的等值查詢就用HASH索引，範圍查詢就使用BTree索引。不同索引類型會對效能產生很大的影響。
• 所有欄位都為固定長度 varchar(10) = char(10)
  這就要求我們在定義表結構時，一定要符合要求最小的欄位長度，否則浪費大量的記憶體。
• 不支援BLOG和TEXT等大字段
• Memory儲存引擎使用表格級鎖定
• 最大大小由max_heap_table_size參數決定
  這個參數的預設值只有16兆，如果我們要在Memory儲存引擎表中儲存大量數據，就要修改這個參數，而這個參數修改是對已經存在的Memory儲存引擎的表是不生效的，如果需要對存在表生效的話就需要通過對已經存在的表進行重建。

5.5.3 Memory中容易混淆的概念

#Memory儲存引擎表：
對所有的系統都可以使用，它並不是一種臨時表。
臨時表：
臨時表分為兩種，一種是查詢優化器在優化查詢時所使用的系統使用臨時表，也就是內部臨時表，系統使用臨時表在超過限制（使用BLOB或TEXT大字段）時使用MyISAM臨時表，未超限制使用Memory表。
另一種是透過指令create temporary table建立的暫存表，建立的表可以使用任何儲存引擎。
無論是哪一種臨時表，只對內部可見。

5.5.4 Memory的使用場景

• #用於尋找或映射表，例如郵編和地區的對應表
• 用於保存資料分析中產生的中間表
• 用於快取週期聚合資料的結果表

Memory資料易遺失，所以要求資料可再生。

5.6 Federated儲存引擎

5.6.1 Federated的特性

• 提供了存取遠端MySQL伺服器上表的方法
  由於Federated儲存引擎只是在本地建立了到遠端伺服器的一個連接，所以可以說我們所要訪問的表全部還是放在遠端伺服器上，在本地並不儲存資料。每次存取Federated儲存引擎表的時候，查詢都會被傳送到遠端伺服器上執行，並從遠端的MySQL伺服器上取得相關的資料。
• 本地不儲存數據，資料全部放到遠端伺服器上
• 本地需要保存表格結構和遠端伺服器的連接資訊
  因此也會在系統中存在一個frm文件，用於存於遠端資訊以及如何連接遠端表的相關資訊。

5.6.2 Federated如何使用

Federated儲存引擎可以實現SQL Server連接伺服器的功能，但由於本身的效能並不太好，通常可以透過複製等實現相同的目的，所以在目前的MySQL版本中，Federated儲存引擎預設是禁止的。如果需要使用Federated儲存引擎，則需要在/usr/local/mysql/my.cnf#中加入federated=1，接著重啟MySQL伺服器，我們可以透過show engine來確認目前MySQL伺服器是否支援Federated儲存引擎。
而在create table語句中使用下面的連接字串，
mysql://user_name[:password]@host_name[:port_num]/db_name/tbl_name
遠端伺服器綁定連線：
grant select,update,insert,delete on remote.remote_fet to fred_link@'127.0.0.1' identified by '123456'
就可以決定的遠端伺服器的相關資訊以及相關的資料庫表的一些資訊。

5.6.3 Federated的適用場景

• #偶爾的統計分析及手動查詢
  由於Federated的效能較慢，只適用於偶爾的統計分析及手作查詢。

6 如何選擇正確的儲存引擎

參考條件：

• 交易
• 備份
• 崩潰復原
• 儲存引擎的特有特性
  盡量避免混合使用儲存引擎。

更多相關免費學習推薦：##mysql教學(影片)

#

以上是大數據學習之二 MYSQL進階的詳細內容。更多資訊請關注PHP中文網其他相關文章！