91麻豆精品国产91久久久久久,狠狠色丁香久久婷婷综合

介紹MYSQL進階的第二篇文章

免費學習推薦：mysql視頻教程

5 MySQL體系結構

介紹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 系統表空間和獨立表空間要如何選擇

比較：

系統表空間	獨立表空間
無法簡單的收縮文件大小	可以通過optimize table命令收縮系統文件
會產生IO瓶頸	可以同時向多個文件刷新數據

建議：

對Innodb使用獨立表空間

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

使用mysqldump導出所有數據庫表數據
停止MySQL服務，修改參數，并刪除Innodb相關文件
重啟MySQL服務，重建Innodb系統表空間
重新導入數據

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存儲引擎適合作為數據交換的中間表
介紹MYSQL進階的第二篇文章

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的適用場景