存儲(chǔ)引擎
0. 前言
1. 存儲(chǔ)引擎查看
2. InnoDB存儲(chǔ)引擎特性存儲(chǔ)InnoDB歷史
3. MyISAM存儲(chǔ)引擎前言特性加鎖與并發(fā)修復(fù)索引特性延遲更新索引鍵存儲(chǔ)壓縮表性能
4. InnoDB和MyISAM對(duì)比
5. MySQL其他存儲(chǔ)引擎MEMORY存儲(chǔ)引擎ARCHIVE存儲(chǔ)引擎CSV存儲(chǔ)引擎如何選擇合適的存儲(chǔ)引擎
0. 前言
? ? ? ?數(shù)據(jù)庫存儲(chǔ)引擎是數(shù)據(jù)庫底層軟件組織，數(shù)據(jù)庫管理系統(tǒng)使用數(shù)據(jù)引擎進(jìn)行創(chuàng)建、查詢、更新和刪除數(shù)據(jù)。不同的存儲(chǔ)引擎提供不同的存儲(chǔ)機(jī)制、索引技巧、鎖定水平等功能，使用不同的存儲(chǔ)引擎，還可以獲得特定的功能?，F(xiàn)在許多不同的數(shù)據(jù)庫管理系統(tǒng)都支持多種不同的數(shù)據(jù)引擎。
1. 存儲(chǔ)引擎查看
使用命令SHOW ENGINES來查看MySQL使用的引擎
查看數(shù)據(jù)庫默認(rèn)引擎 ：
SHOW VARIABLES LIKE 'storage_engine';
2. InnoDB存儲(chǔ)引擎
特性
1、是Mysql默認(rèn)的事務(wù)型引擎，也是最重要、使用最廣泛的存儲(chǔ)引擎。
2、高性能和自動(dòng)崩潰恢復(fù)特性
3、InnoDB是為處理巨大數(shù)據(jù)量的最大性能設(shè)計(jì)。
4、InnoDB存儲(chǔ)引擎完全與MySQL服務(wù)器整合，InnoDB存儲(chǔ)引擎維持它自己的緩沖池。
5、InnoDB支持外鍵完整性約束，存儲(chǔ)表中的數(shù)據(jù)時(shí)，每張表的存儲(chǔ)都按主鍵順序存放。
6、InnoDB被用在眾多需要高性能的大型數(shù)據(jù)庫站點(diǎn)上
7、作為事務(wù)型的存儲(chǔ)引擎，InnoDB通過一些機(jī)制和工具支持真正的熱備份。
8、InnoDB表是基于聚簇索引建立的。
? ? ? ?使用InnoDB時(shí)，MySQL將在MySQL數(shù)據(jù)目錄下創(chuàng)建一個(gè)名為ibdata1的10MB大小的自動(dòng)擴(kuò)展數(shù)據(jù)文件，以及兩個(gè)名為ib_logfile0和ib_logfile1的5MB大小的日志文件用于支持事務(wù)的持久性。
存儲(chǔ)
? ? ? ?存儲(chǔ)文件：表空間文件、重做日志文件。
? ? ? ?表空間文件：InnoDB引擎將存儲(chǔ)的數(shù)據(jù)按照表空間進(jìn)行存放的設(shè)計(jì)。在默認(rèn)配置下有一個(gè)初始大小10M,名為ibdata1的文件。該文件就是默認(rèn)的表空間文件，可以通過innodb_data_file_path參數(shù)配置，格式如下：
innodb_data_file_path = datafile1_spec1
[;datafile2_spec2]...
? ? ? ?用戶通過多個(gè)文件組成表空間，同時(shí)制定文件的屬性，如：innodb_data_file_path = c:/innodb:2000M;d:/innodb2:2000M，innodb_file_per_table參數(shù)，則每個(gè)InnoDB表產(chǎn)生一個(gè)獨(dú)立的表空間。命名規(guī)則 表名.ibd，不再將所有數(shù)據(jù)存放在默認(rèn)的表空間。
? ? ? ?日志文件
? ? ? ?ib_logfile0、ib_logfile1。這兩個(gè)就是重做日志文件。每個(gè)InnoDB引擎至少有一個(gè)重做日志文件組，每個(gè)文件組至少兩個(gè)重做日志文件。影響重做日志屬性的參數(shù)有：
? ? ? ?? ? ? ?1. innodb_log_file_size 每個(gè)重做日志的大小
? ? ? ?? ? ? ?2. innodb_log_files_in_group 重做文件日志組中日志文件的數(shù)量，默認(rèn)為2。
? ? ? ?? ? ? ?3. innodb_mirrored_log_groups 日志鏡像文件組的數(shù)量，默認(rèn)1。表示只有一個(gè)日志文件組，沒有鏡像。
? ? ? ?? ? ? ?4. innodb_log_group_home_dir 日志文件組所在路徑，默認(rèn)./ 表示在MySQL的數(shù)據(jù)目錄下。
InnoDB歷史
? ? ? ?2008年，InnoDB plugin，適用于Mysql 5.1版本。
? ? ? ?Oracle收購sun，Mysql5.5
3. MyISAM存儲(chǔ)引擎
前言
? ? ? ?在Mysql5.1及之前的版本，MyISAM是默認(rèn)的存儲(chǔ)引擎。MyISAM提供了大量的特性，包括全文索引、壓縮、空間函數(shù)（GIS）等，但MyISAM不支持事務(wù)和行級(jí)鎖，一個(gè)事務(wù)插入時(shí)，會(huì)將整個(gè)表鎖住。而且有一個(gè)毫無疑問的缺陷就是崩潰后無法完全恢復(fù)。
? ? ? ?盡管如此，MyISAM絕不是一無是處的，對(duì)于只讀的數(shù)據(jù)，或者表比較小、可以忍受修復(fù)操作，則依然可以繼續(xù)使用MyISAM引擎。
特性
加鎖與并發(fā)
MyISAM對(duì)整張表加鎖，而不是針對(duì)行。
修復(fù)
? ? ? ?對(duì)于MyISAM表，MySQL可以手工或自動(dòng)執(zhí)行檢查和修復(fù)操作，但這里說的修復(fù)和事務(wù)恢復(fù)以及崩潰恢復(fù)是不同的概念。執(zhí)行表的修復(fù)可能導(dǎo)致一些數(shù)據(jù)丟失，而且修復(fù)操作是非常慢的?？梢酝ㄟ^CHECK TABLE mytable檢查表的錯(cuò)誤，如果有錯(cuò)誤可以通過執(zhí)行REPAIR TABLE mystable進(jìn)行修復(fù)。另外，如果MySQL服務(wù)器已經(jīng)關(guān)閉，也可以通過myisamchk命令工具進(jìn)行檢查和修復(fù)操作。
索引特性
? ? ? ?對(duì)于MyISAM表，即使是BLOB和TEXT等字段，也可以基于前500個(gè)字符創(chuàng)建索引。MyISAM也支持全文索引，這是一種基于分詞創(chuàng)建的索引，可以支持復(fù)雜的查詢。
延遲更新索引鍵
? ? ? ?創(chuàng)建MyISAM表的時(shí)候，如果指定了DELAY_KEY_WRITE選項(xiàng)，在每次修改執(zhí)行完成時(shí)，不會(huì)立刻將修改的索引數(shù)據(jù)寫入磁盤 ，而是會(huì)寫到內(nèi)存中的鍵緩沖區(qū)，只有在清理鍵緩沖區(qū)或者關(guān)閉表的時(shí)候才會(huì)將對(duì)應(yīng)的索引快寫入到磁盤。
? ? ? ?使用MyISAM引擎創(chuàng)建數(shù)據(jù)庫，將產(chǎn)生3個(gè)文件。文件的名字以表名字開始，擴(kuò)展名之處文件類型：frm文件存儲(chǔ)表定義、數(shù)據(jù)文件的擴(kuò)展名為.MYD（MYData）、索引文件的擴(kuò)展名時(shí).MYI（MYIndex）
存儲(chǔ)
? ? ? ?數(shù)據(jù)文件和索引文件，分別以.MYD和.MYI為擴(kuò)展名。
壓縮表
? ? ? ?如果表在創(chuàng)建并導(dǎo)入數(shù)據(jù)以后，不會(huì)再進(jìn)行修改操作，那么這樣的表或許適合采用MyISAM壓縮表。
? ? ? ?可以使用myisampack對(duì)MyISAM表進(jìn)行壓縮（也較打包pack）。
性能
? ? ? ?MyISAM有一些服務(wù)器級(jí)別的性能擴(kuò)展限制，比如對(duì)索引鍵緩沖區(qū)（key cache）的Mutex鎖，MariaDB基于段（segment）的索引鍵緩沖區(qū)機(jī)制。但MyISAM最典型的性能問題還是表鎖的問題，如果你發(fā)現(xiàn)所有的查詢都長期處于“Locked”狀態(tài)，那么毫無疑問表鎖就是罪魁禍?zhǔn)住?/p>
4. InnoDB和MyISAM對(duì)比
? ? ? ?1、由于鎖粒度的不同，InnoDB比MyISAM支持更高的并發(fā)；
? ? ? ?2、InnoDB為行級(jí)鎖，MyISAM為表級(jí)鎖。
? ? ? ?3、在備份容災(zāi)上，InnoDB支持在線熱備，有很成熟的在線熱備解決方案；
? ? ? ?4、查詢性能上，MyISAM的查詢效率高于InnoDB。
? ? ? ?5、SELECT COUNT(*) 語句，如果行數(shù)在千萬級(jí)別以上，MyISAM可以快速查出，而InnoDB查詢的特別慢。
? ? ? ?6、MyISAM的表結(jié)構(gòu)文件包括：.frm(表結(jié)構(gòu)定義),.MYI(索引),.MYD(數(shù)據(jù))；
而InnoDB的表數(shù)據(jù)文件為:.ibd和.frm(表結(jié)構(gòu)定義)；
5. MySQL其他存儲(chǔ)引擎
MEMORY存儲(chǔ)引擎
將數(shù)據(jù)存在內(nèi)存中，和市場上的Redis，memcached等思想類似，為了提高數(shù)據(jù)的訪問速度，主要特點(diǎn)：
（1）支持的數(shù)據(jù)類型有限制
（2）支持的鎖粒度為表級(jí)鎖。
（3）由于數(shù)據(jù)是存放在內(nèi)存中，所以在服務(wù)器重啟之后，所有數(shù)據(jù)都會(huì)丟失；
（4）查詢的時(shí)候，如果有用到臨時(shí)表，而且臨時(shí)表中有BLOB，TEXT類型的字段，那么這個(gè)臨時(shí)表就會(huì)轉(zhuǎn)化為MyISAM類型的表，性能會(huì)急劇降低；
ARCHIVE存儲(chǔ)引擎
ARCHIVE存儲(chǔ)引擎適合的場景有限，由于其支持壓縮，故主要是用來做日志，流水等數(shù)據(jù)的歸檔，主要特點(diǎn)：
（1）支持Zlib壓縮，數(shù)據(jù)在插入表之前，會(huì)先被壓縮；
（2）僅支持SELECT和INSERT操作，存入的數(shù)據(jù)就只能查詢，不能做修改和刪除；
（3）只支持自增鍵上的索引，不支持其他索引；
CSV存儲(chǔ)引擎
數(shù)據(jù)中轉(zhuǎn)試用，主要特點(diǎn)：
（1）其數(shù)據(jù)格式為.csv格式的文本，可以直接編輯保存；
（2）導(dǎo)入導(dǎo)出比較方便，可以將某個(gè)表中的數(shù)據(jù)直接導(dǎo)出為csv，試用Excel辦公軟件打開；
如何選擇合適的存儲(chǔ)引擎
1、使用場景是否需要事務(wù)支持；
2、是否需要支持高并發(fā)，InnoDB的并發(fā)度遠(yuǎn)高于MyISAM；
3、是否需要支持外鍵；
4、是否需要支持在線熱備；
5、高效緩沖數(shù)據(jù)，InnoDB對(duì)數(shù)據(jù)和索引都做了緩沖，而MyISAM只緩沖了索引；
6、索引，不同存儲(chǔ)引擎的索引并不太一樣；
