1.MySQL引擎概述 1.1.什麼是存儲引擎？ 資料庫表裡的數據存儲在資料庫里及磁碟上，它跟視頻格式及存儲磁碟文件系統格式的特征類似，也有很多存儲方式。 但是，對於用戶和應用程式來說，同樣一張表的數據，無論採用什麼引擎來存儲，用戶看到的數據都是一樣的。對於不同的引擎存取，引擎功能、占用的空間大小、 ...
目錄
- 1.MySQL引擎概述
- 2.查看MySQL支持的存儲引擎
- 3.MySQL5.6支持的存儲引擎
- 4.MySQL常用存儲引擎特性對比
- 5.設置與更改MySQL的引擎
- 6.MyISAM引擎
- 7.InnoDB引擎
- 8.Memory存儲引擎
- 9.ARCHIVE存儲引擎
- 10.NDB存儲引擎
1.MySQL引擎概述
1.1.什麼是存儲引擎？
資料庫表裡的數據存儲在資料庫里及磁碟上，它跟視頻格式及存儲磁碟文件系統格式的特征類似，也有很多存儲方式。
但是，對於用戶和應用程式來說，同樣一張表的數據，無論採用什麼引擎來存儲，用戶看到的數據都是一樣的。對於不同的引擎存取，引擎功能、占用的空間大小、讀取性能等可能都有區別。
存儲引擎是MySQL資料庫用來處理不同表類型的SQL操作的組件。
MySQL早期最常用的存儲引擎為：MyISAM和InnoDB。目前，InnoDB是最常用的存儲引擎，也是MySQL5.6預設的存儲引擎。
1.2.MySQL存儲引擎的架構
MySQL的存儲引擎是MySQL資料庫的重要組成部分。MySQL的每種存儲引擎在MySQL里都是通過插件的方式使用的，可以輕易地從MySQL中進行載入和卸載，MySQL中可以同時支持多種存儲引擎。
MySQL體繫結構的組成部分：
1、連接池部分。
2、資料庫管理部分。
3、SQL介面、查詢分析器、優化器、緩存緩衝。
4、存儲引擎部分。
5、資料庫數據文件和各種日誌文件。
6、文件系統磁碟。
2.查看MySQL支持的存儲引擎
可以在MySQL中使用顯示引擎的命令來得到一個可用引擎的列表：
select version();
show engines;
命令的結果顯示了資料庫可用引擎的全部名單，以及在當前的資料庫中是否支持這些引擎，其中前四列比較重要，第一列是引擎名字，第二列是當前資料庫是否支持，第三列是描述，第四列表示是否支持事務。
3.MySQL5.6支持的存儲引擎
| 存儲引擎 | 說明（帶*的為重點） |
|---|---|
| InnoDB | InnoDB是MySQL5.6預設的存儲引擎，InnoDB支持事務，具有提交、回滾的功能，並且可以通過崩潰恢復能力來保護用戶的數據，讀寫數據是行級鎖定，可提升多用戶併發訪問的能力，InnoDB以集群的索引方式存儲用戶數據，基於主鍵方式查詢可提高I/O性能，InnoDB也支持外鍵，使得數據更完整、更安全。* |
| MyISAM | MyISAM是MySQL5.5.5以前預設的存儲引擎，曾經用的很多，現在用的少了，MyISAM僅支持表級鎖，讀寫性能都很有限。可用於只讀或者絕大多數以讀為主的業務場景。 |
| Memory | Memory以記憶體的方式存儲所有數據，訪問速度很快，不過其使用場景也是越來越少了。InnoDB的Buffer pool記憶體也可以緩存絕大多數的數據了。 |
| CSV | CSV這個引擎所對應的數據表格實際上是帶有逗號分隔值的文本文件。CSV表格允許您以CSV格式導入或者轉儲數據，以便於讀取和寫入相同格式的腳本，與應用程式進行數據交換。由於CSV表是沒有索引的，因此通常應在正常操作期間將數據保存在InnoDB表中，並且只能在導入或導出階段使用CSV表。 |
| Archive | 這些緊湊、無索引的引擎表旨在存儲和檢索大量參考的歷史、歸檔或安全審核信息。 |
| Blackhole | Blackhole存儲引擎接受但不存儲數據，類似於Unix/dev/null設備。查詢總是會返回一個空集。這些表可用於將DML語句發送到從屬伺服器的複製配置，但是主伺服器不保留其自己的數據副本。 |
| Merge | 使MySQL DBA或開發人員能夠對一系列相同的MyISAM表進行邏輯分組，並將其作為一個對象引用。merge適用於數據倉庫等VLDB環境。 |
| Federated | Federated可通過鏈接單獨的MySQL伺服器以從許多物理伺服器創建一個邏輯資料庫。其非常適合於分散式或數據集環境。 |
| Example | 該引擎作為MySQL源代碼中的一個例子，說明瞭如何開始編寫新的存儲引擎。這主要是開發商感興趣的。存儲引擎是一個什麼都不做的“stub”。您可以使用此引擎創建表，但不能存儲數據或從中檢索數據。 |
4.MySQL常用存儲引擎特性對比
| 特性 | MyISAM | Memory | InnoDB | Archive | NDB |
|---|---|---|---|---|---|
| 存儲限制 | 256TB | RAM | 64TB | NONE | 384EB |
| 事務 | NO | NO | YES | NO | NO |
| 鎖粒度 | TABLE | TABLE | ROW | ROW | ROW |
| B-tree索引 | YES | YES | YES | NO | NO |
| T-tree索引 | NO | NO | NO | NO | YES |
| Hash索引 | NO | YES | NO | NO | YES |
| Full-text search索引 | YES | NO | YES | NO | NO |
| Clustered索引 | NO | NO | YES | NO | NO |
| 數據緩存 | NO | N/A | YES | NO | YES |
| 索引緩存 | YES | N/A | YES | NO | YES |
| 壓縮數據 | YES | NO | YES | YES | NO |
| 加密數據 | YES | YES | YES | YES | YES |
| 集群資料庫支持 | NO | NO | NO | NO | YES |
| 主從複製支持 | YES | YES | YES | YES | YES |
| 外鍵支持 | NO | NO | YES | NO | NO |
5.設置與更改MySQL的引擎
5.1.設置表的引擎
如果建表的時候不指定引擎，那麼表的引擎就會和資料庫的預設配置一致。
指定表的引擎建立表，建立一個學生表：
create tables `student` (
`Sno` int(10) not null comment '學號',
`Sname` varchar(16) not null comment '姓名',
`Ssex` char(2) not null comment '性別',
`Sage` varchar(16) default null,
`Sdept` varchar(16) default null comment '學生所在系別',
key `ind_sage` (`Sage`),
key `ind_sno` (`Sno`)
) engine=myisam default charset=utf8 # 最後一行括弧外，指定引擎。
5.2.更改表的引擎
一般來說，更改MySQL引擎的需求並不多見，但偶爾也會有。更改表的引擎的幾種修改方法。
5.2.1.利用SQL命令語句修改引擎
alter table oldboy engine = innodb;
alter table oldboy engine = myisam;
更改引擎：
show create table test\G
alter table test engine = myisam;
show create table test\G
使用此方法若要批量修改，則需要通過開發腳本實現，與分表分庫腳本差不多。
5.2.2.使用sed對備份的SQL文件進行批量轉換
使用sed對備份內容進行引擎轉換：
nohup sed -e 's/MyISAM/InnoDB/g' oldboy.sql > oldboy_1.sql &
5.2.3.mysql_convert_table_format命令修改
mysql_convert_table_format --user=root --password=oldboy123 --socket=/data/3306/mysql.sock --engine=myisam oldboy test
該命令需要一些依賴包，安裝方法為：
yum -y install perl-DBI perl-DBD-MySQL perl-Time-HiRes
6.MyISAM引擎
6.1.什麼是MyISAM引擎
MyISAM引擎是MySQL關係型資料庫管理系統的預設存儲引擎（MySQL5.5.5以前）。這種MySQL表存儲結構可從舊的ISAM代碼中擴展出許多有用的功能。在新版本的MySQL中，InnoDB引擎由於支持事務、外鍵等，有利於數據的一致性，以及其能支持更高的多用戶併發性等優點，InnoDB已經取代了曾經常用的MyISAM引擎，不過由於資料庫中的MySQL庫的大部分表主要用於讀取，因此，MyISAM引擎依然在使用。
6.2.MyISAM引擎的存儲方式
每一個MyISAM引擎的表都對應於硬碟上的三個文件。這三個文件雖然具有一樣的文件名，但是其不同的擴展名指示了其不同的類型用途：“.frm”文件用於保存表的定義，該文件並不是MyISAM引擎的一部分，而是伺服器的一部分；“.MYD”用於保存表的數據；“.MYI”則是表的索引文件。“.MYD”和.MYI是MyISAM的關鍵點。
MySQL資料庫系統的表大多數都使用MyISAM引擎。
6.3.MyISAM引擎的主要特點
| 特性 | 支持情況 | 說明 |
|---|---|---|
| 存儲限制 | 256TB | |
| 事務支持 | NO | |
| 鎖表粒度 | TABLE | 即數據更新時鎖定整個表：其鎖定機制是表級鎖定，這雖然可以讓鎖定的實現成本很小，但是同時也大大降低了其併發性能 |
| 全文索引 | YES | |
| 數據緩存 | NO | 不會緩存數據 |
| 索引緩存 | YES | MyISAM可以通過key_buffer_size緩存索引，以大大提高訪問性能，減少磁碟IO，但是這個緩存區只會緩存索引，而不會緩存數據 |
| 外鍵支持 | NO | 不支持外鍵 |
| 資源占用 | 少 | 因為功能不多，且管理粒度較粗，因此，MyISAM消耗系統資源比InnoDB少很多 |
| 讀寫是否阻塞 | YES | 不僅會在寫入的時候阻塞讀取，MyISAM還會在讀取的時候阻塞寫入，但讀本身並不會阻塞另外的讀 |
| 是否預設 | NO | MyISAM是MySQL5.5.5之前預設的存儲引擎，因為性能問題，在MySQL後期版本中被取代 |
6.4.MyISAM引擎適用的生產業務場景
MyISAM引擎可以使用的生產業務場景。
1、不需要事務支持並且對數據一致性要求不高的業務。
2、一般適用於讀請求較多的應用，讀寫都頻繁的場景不適合。
3、讀寫併發訪問相對較低的業務。
4、數據修改相對較少的業務（阻塞問題）。
5、硬體資源比較差的伺服器。
6、使用讀寫分離的MySQL從庫可以使用MyISAM。
當下99%的企業業務場景，都不需要使用MyISAM了，而是選擇更有優勢的InnoDB。
7.InnoDB引擎
7.1.什麼是InnoDB引擎
InnoDB引擎是當下MySQL資料庫最重要的存儲引擎，其正在成為目前MySQL AB所發行新版的標準，被包含在所有的安裝包里。與其他的存儲引擎相比，InnoDB引擎的優點是更新數據行級鎖定、支持ACID的事務、支持外鍵，它的設計目標是面向線上事務處理的應用，目前絕大多數互聯網公司都在使用InnoDB引擎，該引擎替代了其他的引擎。MySQL5.6版本的預設引擎已變為InnoDB引擎。
7.2.InnoDB引擎的存儲方式
InnoDB存儲引擎將數據存放在一個像黑盒一樣的邏輯表空間中，這個表空間分為共用表空間和獨立表空間，從MySQL5.6開始，即預設支持將InnoDB引擎的表數據單獨存放到各自獨立的ibd文件中（獨立表空間）。
7.3.InnoDB引擎的主要特點
| 特性 | 支持情況 | 說明 |
|---|---|---|
| 存儲限制 | 64TB | 存儲限制有些小 |
| 事務 | YES | 支持4個事務隔離級別，支持多版本讀 |
| 鎖粒度 | ROW | 更新數據僅鎖定行 |
| B-tree索引 | YES | |
| T-tree索引 | NO | |
| Hash索引 | NO | |
| Full-text search索引 | YES | 從5.5開始支持全文索引 |
| Clustered索引 | YES | 數據和主鍵以Cluster方式進行存儲，組成一顆平衡樹 |
| 數據緩存 | YES | 高效緩存特性：能緩存索引，也能緩存數據 |
| 索引緩存 | YES | 高效緩存特性：能緩存索引，也能緩存數據 |
| 壓縮數據 | YES | 可以壓縮數據 |
| 加密數據 | YES | 可以加密數據 |
| 集群資料庫支持 | NO | 不支持MySQL集群，NDB是集群的引擎 |
| 主從複製支持 | YES | 支持主從複製集群 |
| 資源占用 | 高 | 由於其功能和粒度都更強，因此對硬體的要求很高 |
| 分區支持 | YES | 支持分區，可以提升擴展性和性能 |
| 表空間支持 | YES | 支持共用和獨立表空間，有利於管理和提升性能 |
7.4.InnoDB引擎適用的生產業務場景
1、需要事務支持的業務（具有很好的事務特性）。
2、行級鎖定對高併發有很好的適應能力，但需要確保查詢是通過索引來完成的。
3、數據讀寫及更新都較為頻繁的場景，如BBS、SNS、微博、微信等。
4、數據一致性要求較高的業務，例如：充值轉賬、銀行卡轉賬等。
5、硬體設備資源較好，特別是記憶體要大，可以利用InnoDB較好的緩存能力來提高記憶體利用率，儘可能減少磁碟IO。
7.5.InnoDB引擎相關參數介紹
| InnoDB引擎的重要參數 | 說明 |
|---|---|
| innodb_buffer_pool_size = 2048M | InnoDB使用一個緩衝池來保存索引和原始數據，緩衝池設置的越大，理論上在存取表裡面的數據時所需要的磁碟I/O就越少。官方建議將InnoDB的Buffer Pool值配置為物理記憶體的50%~80% |
| innodb_data_file_path = ibdata1:12M:autoextend | InnoDB數據文件的路徑，預設為12MB大小ibdata1的單獨文件，預設以64MB為單位自增（autoextend） |
| innodb_additional_mem_pool_size = 16M | 該參數用來設置InnoDB存儲的數據目錄信息和其他內部數據結構的記憶體池大小。應用程式里的表越多，就需要在其中分配越多的記憶體。對於一個相對穩定的應用來說，這個參數的大小也是相對穩定的，沒有必要預留非常大的值。如果InnoDB將開始從操作系統分配記憶體，並且向MySQL錯誤日誌中記錄警告信息。預設為1MB，當發現錯誤日誌中已經有相關的警告信息時，就應該適當地增加該參數的大小 |
| innodb_file_io_threads = 4 | InnoDB中的文件I/O線程。通常設置為4，如果是Windows則可以設置更大的值以提高磁碟I/O |
| innodb_thread_concurrency = 8 | 你的伺服器中有幾個CPU就設置為幾，建議使用預設設置，一般設置為8 |
| innodb_flush_log_at_trx_commit = 2 | 若設置為0，就相當於innodb_log_buffer_size隊列滿後再統一存儲，預設值為1，該值也是最安全的設置 |
| innodb_log_buffer_size = 16M | 預設為1MB，通常設置為8~16MB就足夠了 |
| innodb_log_file_size = 128M | 確定日誌文件的大小，更大的設置可以提高性能，但也會增加資料庫恢復的時間 |
| innodb_log_files_in_group = 3 | 為提高性能，MySQL可以以迴圈的方式將日誌文件寫到多個文件。推薦設置為3 |
| innodb_max_dirty_pages_pct = 90 | InnoDB主線程刷新緩存池中的數據 |
| innodb_lock_wait_timeout = 120 | InnoDB事務被回滾之前可以等待一個鎖定的超時秒數。InnoDB在它自己的鎖定表中自動檢測事務死鎖並且回滾事務。預設值為50秒 |
| innodb_file_per_table = 1 | InnoDB為獨立表空間模式，每個資料庫的每個表都會生成一個數據空間。值為0表示關閉，值為1表示開啟 |
| innodb_data_home_dir = /data/xxx | InnoDB數據的存放路徑 |
| innodb_log_group_home_dir = /data/xxx | 日誌分組的目錄路徑 |
7.6.InnoDB引擎調優的基本方法
1、主鍵應儘可能小，以避免對Secondary index帶來過大的空間負擔。
2、建立有效索引避免全表掃描，因為會使用表鎖。
3、儘可能緩存所有的索引和數據，提高響應速度，減少磁碟IO消耗。
4、在進行大批量小插入的時候，應儘量自己控制事務而不要使用autocommit自動提交。若有開關則可以控制提交方式。
5、合理設置innodb_flush_log_at_trx_commit參數值，不要過度追求安全性。
6、應避免主鍵更新，因為這會帶來大量的數據移動。
8.Memory存儲引擎
Memory就是記憶體的意思，因此Memory存儲引擎（又稱為heap引擎）的數據存儲是放在記憶體（註意：由max_heap_table_size參數控制記憶體占用大小，預設為16MB。）中的，因此存取速度特別快，但是如果資料庫宕機或重啟，那麼所有的數據就都會丟失，因此它比較適合用於存放臨時表的數據，例如，discuz論壇資料庫中的統計線上人數的session表採用的就是Memory引擎。Memory存儲引擎預設採用的是Hash索引，而不像其他引擎（MyISAM和InnoDB）預設的是B-tree索引。
Memory存儲引擎在使用上也有一些限制，例如，僅支持表鎖，不支持TEXT和B1OB數據類型，還有當存儲變長欄位（varchar）時按照定長欄位（char）來進行的，這也會浪費一些記憶體空間。Memory存儲引擎在企業工作中應用的不是很多。
9.ARCHIVE存儲引擎
ARCHIVE的中文意思是歸檔，因此ARCHIVE適用於存放大量歸檔歷史數據（可查詢但不能刪除）的保存。
ARCHIVE引擎僅支持select、insert操作；MySQL5.1以後開始支持索引等操作。
ARCHIVE引擎使用zlib無損數據壓縮演算法，壓縮比可達10：1，可大量節省磁碟空間，設計ARCHIVE引擎的目標是提供高速的插入和壓縮等功能。
建立兩個不同存儲引擎的表，測試ARCHIVE存儲引擎的表其占用空間的情況：
首先建立一個MyISAM存儲引擎的表，插入數據：
create table t1 engine=myisam as select * from information_schema.columns;
show table status like 't1'\G
再建立一個ARCHIVE引擎表，插入數據：
create table t2 engine=archive as select * from information_schema.columns;
數據文件形式：
.ARZ是數據壓縮文件，.frm是表結構定義文件
10.NDB存儲引擎
NDB存儲引擎是一個集群存儲引擎，類似於oracle的RAC集群，但它是share nothing的架構，因此NDB能夠提供更高級別的高可用和可擴展性。NDB的特點是數據全部存放在記憶體中，因此，通過主鍵進行查找的速度非常快。
關於NDB，有一個問題需要註意，它的連接（join）操作是在MySQL資料庫層完成的，而不是在存儲引擎層完成的，這就意味著，複雜的Join操作需要巨大的網路開銷，查詢速度會很慢，在中小型企業中，NDB引擎的使用頻率極少。
