大數(shù)據(jù)之MYSQL進階的知識點有哪些

這篇文章主要介紹了大數(shù)據(jù)之MYSQL進階的知識點有哪些，具有一定借鑒價值，感興趣的朋友可以參考下，希望大家閱讀完這篇文章之后大有收獲，下面讓小編帶著大家一起了解一下。

文章目錄

• 5 MySQL體系結(jié)構(gòu)

• 
  

• 5.6.1 Federated的特點

• 5.6.2 Federated如何使用

• 5.6.3 Federated的適用場景

• 5.5.1 文件系統(tǒng)存儲特點

• 5.5.2 Memory的功能特點

• 5.5.3 Memory中容易混淆的概念

• 5.5.4 Memory的使用場景

• 5.4.1 文件系統(tǒng)存儲特點

• 5.4.2 Archive存儲引擎的特點

• 5.4.3 Archive存儲引擎使用場景

• 5.3.1 CSV存儲引擎的特點

• 5.3.2 CSV存儲引擎的適用場景

• 5.2.1 系統(tǒng)表空間和獨立表空間要如何選擇

• 5.2.2 Innodb存儲引擎的特性

• 5.2.3 Innodb狀態(tài)檢查

• 5.2.4 適用場景

• 5.2.4 (拓展) 什么是鎖

• 
  

• 5.2.3.1 什么是鎖？

• 5.2.3.2 鎖的類型：

• 5.2.3.3 寫鎖和讀鎖的兼容關(guān)系（對一行的兼容性情況）

• 5.2.3.4 鎖的粒度：

• 5.2.3.5 阻塞和死鎖

• 5.1 MyISAM

• 5.2 Innodb

• 
  

• 5.3 CSV存儲引擎

• 
  

• 5.4 Archive存儲引擎

• 
  

• 5.5 Memory存儲引擎

• 
  

• 5.6 Federated存儲引擎

• 
  

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

接上一篇文章

5 MySQL體系結(jié)構(gòu)

下面我們選一些比較常用的存儲引擎進行簡單的說明，mysql所使用的存儲引擎會對數(shù)據(jù)庫的性能產(chǎn)生直接的影響，還希望各位能仔細的了解存儲引擎的一些特點，完了之后才使用存儲引擎。

5.1 MyISAM

MyISAM在MySQL5.5之前版本是默認的存儲引擎。由于這個原因，還有很多服務器在使用MyISAM這個存儲引擎。同時，MyISAM目前是很多系統(tǒng)表，臨時表所使用的存儲引擎，這里說的臨時表不是我們通過create table創(chuàng)建出來的表，是指在排序、分組等操作中，當數(shù)量超過一定的大小之后，有查詢優(yōu)化器建立的臨時表。
MyISAM存儲引擎是由MYD和MYI組成，MYD是數(shù)據(jù)文件的擴展名，MYI是索引文件的擴展名，這個存儲引擎是將表存儲在以這兩個為擴展名的數(shù)據(jù)文件和索引文件中。

特性：

• 并發(fā)性與鎖級別
  MyISAM使用的是表級鎖，并不是行級鎖，這也就意味著對表中的數(shù)據(jù)進行修改時，需要對整個表進行加鎖，而在對表的讀取時也對所有的表加共享鎖，從這里我們可以看到，使用MyISAM做引擎的表讀取和寫入兩種操作是互斥的。由此可以看到MyISAM對于讀寫的并發(fā)操作并不會很好。如果只對于只讀取操作的話，就并發(fā)性而言，性能也還不錯，因為共享鎖不會阻塞共享鎖。

• 表損壞修復
  MyISAM支持由于任意意外關(guān)閉而損壞的MyISAM表，進行檢查和修復操作，但是這里說的修復并不是數(shù)據(jù)的恢復，因為MyISAM并不是一種事務性的存儲引擎，所以它不能進行事務恢復所需要的相關(guān)日志，所以要注意MyISAM表的恢復可能會造成數(shù)據(jù)的丟失。
  我們可以通過check table tablename對表進行檢查，通過repair table tablename對表進行恢復。

• MyISAM表支持的索引類型
  MyISAM支持全文索引，并且在mysql5.7版本之前唯一原生就支持全文索引的官方存儲引擎。

• MyISAM表支持數(shù)據(jù)壓縮
  如果MyISAM表示一張很大的只讀表，也就是在表創(chuàng)建完導入數(shù)據(jù)后，就不會對表進行任何修改操作了，那我們就可以對表進行壓縮了，以減少磁盤I/O。 我們可以使用myisampack命令來對表進行壓縮。壓縮是對表獨立進行壓縮的，因此讀取一行數(shù)據(jù)的時候，不必對整個表進行解壓。

限制：

• 版本 < mysql5.0時默認表大小為4G

• 如存儲大表則要修改MAX_Rows 和 AVG_ROW_LENGTH

• 版本 > mysql5.0時默認支持位256TB

適用場景：

• 非事務性應用

• 只讀類應用（報表之類）

• 空間類應用

5.2 Innodb

Innodb是MySQL5.5及之后版本默認的存儲引擎，Innodb是事務存儲的存儲引擎，也就是說支持事務的處理。
Innodb有自己的表空間的概念，然后數(shù)據(jù)是存儲在表空間之中的，是由innodb_file_per_table這個參數(shù)來決定的，如果這個參數(shù)為ON，則會對每個Innodb表建立一個擴展名為ibd的系統(tǒng)文件，如果這個參數(shù)為OFF時，則會把數(shù)據(jù)存儲到系統(tǒng)的共享表空間，也就是ibdataX，X代表的時一個數(shù)字，默認從1開始。
查看這個參數(shù)的命令為：
show variables like 'innodb_file_per_table';
修改這個參數(shù)的命令為：
set global innodb_file_per_table=off;

5.2.1 系統(tǒng)表空間和獨立表空間要如何選擇

比較：

建議：

• 對Innodb使用獨立表空間

把原來存在于系統(tǒng)表空間中的表轉(zhuǎn)移到獨立表空間中的方法。
步驟：

1. 使用mysqldump導出所有數(shù)據(jù)庫表數(shù)據(jù)

2. 停止MySQL服務，修改參數(shù)，并刪除Innodb相關(guān)文件

3. 重啟MySQL服務，重建Innodb系統(tǒng)表空間

4. 重新導入數(shù)據(jù)

5.2.2 Innodb存儲引擎的特性

• Innodb是一種事務性存儲引擎

• 完全支持事務的ACID特性（之前介紹過的原子性，一致性等）

• Redo Log 和 Undo Log
  Redo Log實現(xiàn)了事務的持久性，有兩部分組成，其中一個是內(nèi)存中的工作日志持久緩沖區(qū)，是由innodb_log_buffer_size決定它的大小，另一個是重構(gòu)日志文件，也就是我們在文件系統(tǒng)中看到的ib_logflie的相關(guān)文件。Undo Log實現(xiàn)了事務的原子性，在事務失敗時進行回滾操作。Redo Log是順序讀寫的，Undo Log是隨機讀寫的，如果可以的話可以將數(shù)據(jù)存儲在固態(tài)硬盤中，以提高性能。

• Innodb支持行級鎖
  行級鎖和表級鎖是不一樣的，行級鎖的特點就是可以最大程度的支持并發(fā)，行級鎖是由存儲引擎層實現(xiàn)的。

5.2.3 Innodb狀態(tài)檢查

可以使用以下命令對Innodb狀態(tài)進行檢查：
show engine innodb status

5.2.4 適用場景

Innodb適合于大多數(shù)OLTP應用，因為在mysql5.7版本之后，Innodb已經(jīng)支持了全文索引和空間函數(shù)。

5.2.4 (拓展) 什么是鎖

5.2.3.1 什么是鎖？

• 鎖最主要作用是管理共享資源的并發(fā)訪問

• 鎖用于實現(xiàn)事務的隔離性

5.2.3.2 鎖的類型：

• 共享鎖（也稱讀鎖）

• 獨占鎖（也稱寫鎖）

5.2.3.3 寫鎖和讀鎖的兼容關(guān)系（對一行的兼容性情況）

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

5.2.3.4 鎖的粒度：

• 表級鎖

• 行級鎖

5.2.3.5 阻塞和死鎖

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

5.3 CSV存儲引擎

CSV存儲引擎可以將csv文件作為mysql的表文件來處理，這種存儲引擎的存儲格式就是普通的csv文件，在csv存儲引擎的數(shù)據(jù)存儲方式非常的由特點，如果我們把表存儲在MyISAM或者Innodb中，其數(shù)據(jù)文件我們是不能直接查看的，因為這兩種文件的存儲是以二進制的格式來存儲的，而CSV存儲引擎則不同，CSV的數(shù)據(jù)是以文本的方式存儲在文件中的，也就是我們可以通過查看文件的命令來查看，如more，或者使用vi命令來查看編輯csv存儲引擎中的表，只要符合CSV文件的格式和要求，我們就不用擔心損壞數(shù)據(jù)。
當我們在mysql中建立了CSV存儲引擎表時，我們應該可以看到3個文件系統(tǒng)中的文件。這3個都是以表名為文件名，但是會分別以csv，csm，frm為后綴，其中csv文件就是CSV存儲引擎中的數(shù)據(jù)文件。csm文件存儲表的元數(shù)據(jù)和表狀態(tài)和數(shù)據(jù)量。frm文件存儲表結(jié)構(gòu)信息。

5.3.1 CSV存儲引擎的特點

• 最大的特點是以CSV格式進行數(shù)據(jù)存儲
  CSV中的每一列都是以,來分隔的，并且文本的內(nèi)容是以雙引號來引起來的，如下圖所示：
  

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

• 不支持索引
  不適合大表，不適合在線處理

• 可以對數(shù)據(jù)文件直接編輯
  保存文本文件內(nèi)容

5.3.2 CSV存儲引擎的適用場景

CSV存儲引擎適合作為數(shù)據(jù)交換的中間表

5.4 Archive存儲引擎

5.4.1 文件系統(tǒng)存儲特點

Archive存儲引擎會緩存所有的寫，并且利用zlib對插入的行進行壓縮，因此Archive存儲引擎相對于MyISAM存儲引擎的表更加節(jié)省磁盤I/O，對于同樣數(shù)量級的數(shù)據(jù)，Archive存儲引擎相對于MyISAM和Innodb更加節(jié)省存儲空間。一個幾T的Innodb的表存儲在Archive存儲引擎當中，可能只需要幾百兆的存儲空間。
Archive存儲引擎的表的數(shù)據(jù)是以ARZ為后綴的一個文件，和其他引擎一樣，也存在一個以frm為后綴的系統(tǒng)文件，用于存儲表的結(jié)構(gòu)信息。

5.4.2 Archive存儲引擎的特點

• 只支持insert和select操作

• 只允許在自增ID列上加索引

5.4.3 Archive存儲引擎使用場景

場景1：日志和數(shù)據(jù)采集類數(shù)據(jù)
因為Archive不支持修改和刪除，而我們ORDB一定會對數(shù)據(jù)進行修改的，但是對于一些倉庫類型的應用，或者一些特殊的表，還是有用的，比如說，記錄日志的表或者是數(shù)據(jù)采集類的表，因為它需要采集大量數(shù)據(jù)，所以比較適合使用Archive存儲引擎。因為Archive存儲引擎在所有引擎中來說，它的存儲空間是最小的，但是還是要注意，即使在數(shù)據(jù)采集或日志的應用中，Archive存儲引擎是無法對這些數(shù)據(jù)進行更新的，所以在記錄日志或者在數(shù)據(jù)采集類應用中對數(shù)據(jù)進行修改的話，可能也無法使用Archive存儲引擎。

5.5 Memory存儲引擎

5.5.1 文件系統(tǒng)存儲特點

Memory存儲引擎也稱之為HEAP存儲引擎，所以數(shù)據(jù)都保存在內(nèi)存中， 這就意味著這中數(shù)據(jù)的表是一次性的，一旦MySQL服務重啟，所有Memory存儲引擎的數(shù)據(jù)都會消失，但是表結(jié)構(gòu)會保留下來，因為在Memory存儲引擎下創(chuàng)建表，只會生成一個frm系統(tǒng)文件，該文件是用于保存表結(jié)構(gòu)的。這就是為什么重啟MySQL服務器數(shù)據(jù)會丟失，表結(jié)構(gòu)不會的原因。
從它的文件存儲特點我們可以知道，Memory存儲引擎的I/O效率會比MyISAM高很多，因為MyISAM只有索引會保存在內(nèi)存中，而數(shù)據(jù)則由操作系統(tǒng)來緩存的，而Memory存儲引擎所有數(shù)據(jù)和索引都保存在內(nèi)存中，下面我們看一下Memory存儲引擎的功能特點。

5.5.2 Memory的功能特點

功能特點：

• 支持HASH索引（默認）和BTree索引
  如果是HASH索引在做等值查詢的時候會非常的快，如果是做范圍查詢的話就無法使用HASH索引了，所以在表創(chuàng)建的時候我們需要注意，如果表需要大量的等值查詢就用HASH索引，范圍查詢就使用BTree索引。不同索引類型會對性能產(chǎn)生很大的影響。

• 所有字段都為固定長度 varchar(10) = char(10)
  這就要求我們在定義表結(jié)構(gòu)時，一定要符合要求最小的字段長度，否則浪費大量的內(nèi)存。

• 不支持BLOG和TEXT等大字段

• Memory存儲引擎使用表級鎖

• 最大大小由max_heap_table_size參數(shù)決定
  這個參數(shù)的默認值只有16兆，如果我們要在Memory存儲引擎表中存儲大量數(shù)據(jù)，就要修改這個參數(shù)，而這個參數(shù)修改是對已經(jīng)存在的Memory存儲引擎的表是不生效的，如果需要對存在表生效的話就需要通過對已經(jīng)存在的表進行重建。

5.5.3 Memory中容易混淆的概念

Memory存儲引擎表：
對所有的系統(tǒng)都可以使用，它并不是一種臨時表。
臨時表：
臨時表分為兩種，一種是查詢優(yōu)化器在優(yōu)化查詢時所使用的系統(tǒng)使用臨時表，也就是內(nèi)部臨時表，系統(tǒng)使用臨時表在超過限制（使用BLOB或TEXT大字段）時使用MyISAM臨時表，未超限制使用Memory表。
另一種是通過命令create temporary table建立的臨時表，建立的表可以使用任何存儲引擎。
無論是哪種臨時表，只對內(nèi)部可見。

5.5.4 Memory的使用場景

• 用于查找或者映射表，例如郵編和地區(qū)的對應表

• 用于保存數(shù)據(jù)分析中產(chǎn)生的中間表

• 用于緩存周期聚合數(shù)據(jù)的結(jié)果表

Memory數(shù)據(jù)易丟失，所以要求數(shù)據(jù)可再生。

5.6 Federated存儲引擎

5.6.1 Federated的特點

• 提供了訪問遠程MySQL服務器上表的方法
  由于Federated存儲引擎只是在本地建立了到遠程服務器的一個連接，所以可以說我們所要訪問的表全部還是放在遠程服務器上，在本地并不存儲數(shù)據(jù)。每次訪問Federated存儲引擎表的時候，查詢都會被發(fā)送到遠程服務器上運行，并從遠程的MySQL服務器上獲取相關(guān)的數(shù)據(jù)。

• 本地不存儲數(shù)據(jù)，數(shù)據(jù)全部放到遠程服務器上

• 本地需要保存表結(jié)構(gòu)和遠程服務器的連接信息
  因此也會在系統(tǒng)中存在一個frm文件，用于存于遠程信息以及如何連接遠程表的相關(guān)信息。

5.6.2 Federated如何使用

Federated存儲引擎可以實現(xiàn)SQL Server連接服務器的功能，但是由于本身的性能并不太好，通?？梢酝ㄟ^復制等實現(xiàn)相同的目的，所以在當前的MySQL版本中，F(xiàn)ederated存儲引擎默認是禁止的。如果需要使用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'
就可以決定查詢的遠程服務器的相關(guān)信息以及相關(guān)的數(shù)據(jù)庫表的一些信息。

5.6.3 Federated的適用場景

• 偶爾的統(tǒng)計分析及手工查詢
  由于Federated的性能較慢，只適用于偶爾的統(tǒng)計分析及手工查詢。

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

參考條件：

• 事務

• 備份

• 崩潰恢復

• 存儲引擎的特有特性
  盡量避免混合使用存儲引擎。

感謝你能夠認真閱讀完這篇文章，希望小編分享的“大數(shù)據(jù)之MYSQL進階的知識點有哪些”這篇文章對大家有幫助，同時也希望大家多多支持億速云，關(guān)注億速云行業(yè)資訊頻道，更多相關(guān)知識等著你來學習!