MySQL進(jìn)階之體系結(jié)構(gòu)知識(shí)點(diǎn)

這篇文章將為大家詳細(xì)講解有關(guān)MySQL進(jìn)階之體系結(jié)構(gòu)知識(shí)點(diǎn)，小編覺得挺實(shí)用的，因此分享給大家做個(gè)參考，希望大家閱讀完這篇文章后可以有所收獲。

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

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

5.1 MyISAM

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

特性：

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

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

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

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

限制：

• 版本 < mysql5.0時(shí)默認(rèn)表大小為4G

• 如存儲(chǔ)大表則要修改MAX_Rows 和 AVG_ROW_LENGTH

• 版本 > mysql5.0時(shí)默認(rèn)支持位256TB

適用場景：

• 非事務(wù)性應(yīng)用

• 只讀類應(yīng)用（報(bào)表之類）

• 空間類應(yīng)用

5.2 Innodb

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

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

比較：

建議：

• 對(duì)Innodb使用獨(dú)立表空間

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

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

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

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

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

5.2.2 Innodb存儲(chǔ)引擎的特性

• Innodb是一種事務(wù)性存儲(chǔ)引擎

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

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

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

5.2.3 Innodb狀態(tài)檢查

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

5.2.4 適用場景

Innodb適合于大多數(shù)OLTP應(yīng)用，因?yàn)樵趍ysql5.7版本之后，Innodb已經(jīng)支持了全文索引和空間函數(shù)。

5.2.4 (拓展) 什么是鎖

5.2.3.1 什么是鎖？

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

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

5.2.3.2 鎖的類型：

• 共享鎖（也稱讀鎖）

• 獨(dú)占鎖（也稱寫鎖）

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

在實(shí)際情況中，可能與上表的結(jié)果會(huì)有所不同，主要是因?yàn)镮nnodb中的鎖機(jī)制是很復(fù)雜的一樣?xùn)|西，還有很多鎖的存在影響最終的結(jié)果。

5.2.3.4 鎖的粒度：

• 表級(jí)鎖

• 行級(jí)鎖

5.2.3.5 阻塞和死鎖

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

5.3 CSV存儲(chǔ)引擎

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

5.3.1 CSV存儲(chǔ)引擎的特點(diǎn)

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

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

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

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

5.3.2 CSV存儲(chǔ)引擎的適用場景

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

5.4 Archive存儲(chǔ)引擎

5.4.1 文件系統(tǒng)存儲(chǔ)特點(diǎn)

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

5.4.2 Archive存儲(chǔ)引擎的特點(diǎn)

• 只支持insert和select操作

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

5.4.3 Archive存儲(chǔ)引擎使用場景

場景1：日志和數(shù)據(jù)采集類數(shù)據(jù)
因?yàn)锳rchive不支持修改和刪除，而我們ORDB一定會(huì)對(duì)數(shù)據(jù)進(jìn)行修改的，但是對(duì)于一些倉庫類型的應(yīng)用，或者一些特殊的表，還是有用的，比如說，記錄日志的表或者是數(shù)據(jù)采集類的表，因?yàn)樗枰杉罅繑?shù)據(jù)，所以比較適合使用Archive存儲(chǔ)引擎。因?yàn)锳rchive存儲(chǔ)引擎在所有引擎中來說，它的存儲(chǔ)空間是最小的，但是還是要注意，即使在數(shù)據(jù)采集或日志的應(yīng)用中，Archive存儲(chǔ)引擎是無法對(duì)這些數(shù)據(jù)進(jìn)行更新的，所以在記錄日志或者在數(shù)據(jù)采集類應(yīng)用中對(duì)數(shù)據(jù)進(jìn)行修改的話，可能也無法使用Archive存儲(chǔ)引擎。

5.5 Memory存儲(chǔ)引擎

5.5.1 文件系統(tǒng)存儲(chǔ)特點(diǎn)

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

5.5.2 Memory的功能特點(diǎn)

功能特點(diǎn)：

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

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

• 不支持BLOG和TEXT等大字段

• Memory存儲(chǔ)引擎使用表級(jí)鎖

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

5.5.3 Memory中容易混淆的概念

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

5.5.4 Memory的使用場景

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

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

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

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

5.6 Federated存儲(chǔ)引擎

5.6.1 Federated的特點(diǎn)

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

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

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

5.6.2 Federated如何使用

Federated存儲(chǔ)引擎可以實(shí)現(xiàn)SQL Server連接服務(wù)器的功能，但是由于本身的性能并不太好，通?？梢酝ㄟ^復(fù)制等實(shí)現(xiàn)相同的目的，所以在當(dāng)前的MySQL版本中，F(xiàn)ederated存儲(chǔ)引擎默認(rèn)是禁止的。如果需要使用Federated存儲(chǔ)引擎，則需要在/usr/local/mysql/my.cnf中加入federated=1，接著重啟MySQL服務(wù)器，我們可以通過show engine來確認(rèn)當(dāng)前MySQL服務(wù)器是否支持Federated存儲(chǔ)引擎。
而在create table語句中使用下面的連接字符串，
mysql://user_name[:password]@host_name[:port_num]/db_name/tbl_name
遠(yuǎn)程服務(wù)器綁定連接：
grant select,update,insert,delete on remote.remote_fet to fred_link@'127.0.0.1' identified by '123456'
就可以決定查詢的遠(yuǎn)程服務(wù)器的相關(guān)信息以及相關(guān)的數(shù)據(jù)庫表的一些信息。

5.6.3 Federated的適用場景

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

6 如何選擇正確的存儲(chǔ)引擎

參考條件：

• 事務(wù)

• 備份

• 崩潰恢復(fù)

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

關(guān)于“MySQL進(jìn)階之體系結(jié)構(gòu)知識(shí)點(diǎn)”這篇文章就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，使各位可以學(xué)到更多知識(shí)，如果覺得文章不錯(cuò)，請把它分享出去讓更多的人看到。