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