2020重新出發(fā)芳杏，MySql基礎(chǔ)，MySql的存儲引擎

[TOC]

MySQL存儲引擎

數(shù)據(jù)庫存儲引擎是數(shù)據(jù)庫底層軟件組件辟宗，數(shù)據(jù)庫管理系統(tǒng)使用數(shù)據(jù)引擎進(jìn)行創(chuàng)建爵赵、查詢、更新和刪除數(shù)據(jù)操作泊脐。簡而言之空幻，存儲引擎就是指表的類型。數(shù)據(jù)庫的存儲引擎決定了表在計(jì)算機(jī)中的存儲方式容客。不同的存儲引擎提供不同的存儲機(jī)制秕铛、索引技巧、鎖定水平等功能缩挑，使用不同的存儲引擎還可以獲得特定的功能但两。

現(xiàn)在許多數(shù)據(jù)庫管理系統(tǒng)都支持多種不同的存儲引擎。MySQL 的核心就是存儲引擎供置。

MySQL 提供了多個(gè)不同的存儲引擎谨湘，包括處理事務(wù)安全表的引擎和處理非事務(wù)安全表的引擎。在 MySQL 中士袄，不需要在整個(gè)服務(wù)器中使用同一種存儲引擎悲关，針對具體的要求，可以對每一個(gè)表使用不同的存儲引擎娄柳。

MySQL 5.7 支持的存儲引擎有 InnoDB、MyISAM艘绍、Memory赤拒、Merge、Archive、CSV挎挖、BLACKHOLE 等这敬。可以使用SHOW ENGINES;語句查看系統(tǒng)所支持的引擎類型蕉朵，結(jié)果如圖所示崔涂。

MySQL數(shù)據(jù)庫中的存儲引擎

Support 列的值表示某種引擎是否能使用，YES表示可以使用始衅，NO表示不能使用冷蚂，DEFAULT表示該引擎為當(dāng)前默認(rèn)的存儲引擎。

存儲引擎汛闸，主要是 InnoDB 和 MyISAM 進(jìn)行詳細(xì)講解蝙茶。像 NDB 這樣的需要更多擴(kuò)展性的討論。

  • ARCHIVE：用于數(shù)據(jù)存檔的引擎诸老，數(shù)據(jù)被插入后就不能在修改了隆夯，且不支持索引。
  • CSV：在存儲數(shù)據(jù)時(shí)别伏，會以逗號作為數(shù)據(jù)項(xiàng)之間的分隔符蹄衷。
  • BLACKHOLE：會丟棄寫操作，該操作會返回空內(nèi)容厘肮。
  • FEDERATED：將數(shù)據(jù)存儲在遠(yuǎn)程數(shù)據(jù)庫中宦芦，用來訪問遠(yuǎn)程表的存儲引擎。
  • InnoDB：具備外鍵支持功能的事務(wù)處理引擎
  • MEMORY：置于內(nèi)存的表MERGE用來管理由多個(gè)
  • MyISAM ：表構(gòu)成的表集合MyISAM主要的非事務(wù)處理存儲引擎
  • NDBMySQL ：集群專用存儲引擎

MySQL InnoDB存儲引擎

InnoDB 是 MySQL 中第一個(gè)提供外鍵約束的存儲引擎轴脐，而且它對事務(wù)的處理能力是其它存儲引擎無法與之相比的调卑。

MySQL 5.5 版本以后，默認(rèn)存儲引擎由 MyISAM 修改為 InnoDB大咱。InnoDB 是目前最重要恬涧、使用最廣泛的存儲引擎。

InnoDB 一直在持續(xù)改進(jìn)碴巾，隨著處理能力的不斷提高溯捆，其優(yōu)秀的性能和可維護(hù)性使它成為生產(chǎn)中普遍推薦使用的存儲引擎。一般情況下厦瓢，除非有特別的原因需要使用其它存儲引擎提揍，否則應(yīng)該優(yōu)先考慮 InnoDB 引擎。

InnoDB優(yōu)勢

InnoDB 之所以如此受寵煮仇，主要在于其功能方面的較多優(yōu)勢劳跃。

  1. 支持事務(wù)安裝：InnoDB 最重要的一點(diǎn)就是支持事務(wù)，可以說這是 InnoDB 成為 MySQL 中最流行的存儲引擎的一個(gè)非常重要的原因浙垫。InnoDB 還實(shí)現(xiàn)了 SQL92 標(biāo)準(zhǔn)所定義的 4 個(gè)隔離級別（READ UNCOMMITTED刨仑，READ COMMITTED郑诺，REPEATABLE READ 和 SERIALIZABLE）。
  2. 災(zāi)難恢復(fù)性好：InnoDB 通過 commit杉武、rollback辙诞、crash-recovery 來保障數(shù)據(jù)的安全。具體來說轻抱，crash-recovery 就是指如果服務(wù)器因?yàn)橛布蜍浖膯栴}而崩潰飞涂，不管當(dāng)時(shí)數(shù)據(jù)是怎樣的狀態(tài)，在重啟 MySQL 后祈搜，InnoDB 都會自動恢復(fù)到發(fā)生崩潰之前的狀態(tài)较店，并回到用戶離開的地方。
  3. 使用行級鎖：InnoDB 改變了 MyISAM 的鎖機(jī)制夭问，實(shí)現(xiàn)了行鎖泽西。雖然 InnoDB 的行鎖機(jī)制是通過索引來完成的，但畢竟在數(shù)據(jù)庫中 99%的 SQL 語句都要使用索引來檢索數(shù)據(jù)缰趋。行鎖定機(jī)制也為 InnoDB 在承受高并發(fā)壓力的環(huán)境下增強(qiáng)了不小的競爭力捧杉。
    • 在 SQL 查詢中可以自由地將 InnoDB 類型的表與其他類型的表混合起來，甚至在同一個(gè)查詢中也可以混合秘血。
  4. 實(shí)現(xiàn)了緩沖處理：InnoDB 提供了專門的緩存池味抖，實(shí)現(xiàn)了緩沖管理，不僅能緩沖索引也能緩沖數(shù)據(jù)灰粮，常用的數(shù)據(jù)可以直接從內(nèi)存中處理仔涩，比從磁盤獲取數(shù)據(jù)處理速度要快。相比之下粘舟，MyISAM 只是緩存了索引熔脂。
    • InnoDB 的表和索引在一個(gè)邏輯表空間中，表空間可以包含數(shù)個(gè)文件（或原始磁盤分區(qū)）柑肴。這與 MyISAM 表不同霞揉，比如在 MyISAM 表中每個(gè)表被保存在分離的文件中。InnoDB 表可以是任何尺寸晰骑，即使在文件尺寸被限制為 2GB 的操作系統(tǒng)上适秩。
  5. 支持外鍵：InnoDB 支持外鍵約束，檢查外鍵硕舆、插入秽荞、更新和刪除，以確保數(shù)據(jù)的完整性抚官。在存儲表中數(shù)據(jù)時(shí)每張表的存儲都按主鍵順序存放扬跋，如果沒有顯式地在定義表時(shí)指定主鍵，InnoDB 會為每一行生成一個(gè) 6 字節(jié)的 ROWID 耗式，并以此作為主鍵胁住。
    • InnoDB 實(shí)現(xiàn)外鍵引用這一重要特性趁猴，使在數(shù)據(jù)庫端控制部分?jǐn)?shù)據(jù)的完整性成為可能刊咳。雖然很多數(shù)據(jù)庫系統(tǒng)調(diào)優(yōu)專家都建議不要這樣做彪见，但是對于不少用戶來說，大部分情況下娱挨，在數(shù)據(jù)庫端加外鍵控制仍然是成本最低的選擇余指。
  6. 適合需要大型數(shù)據(jù)庫的網(wǎng)站：InnoDB 被用在眾多需要高性能的大型數(shù)據(jù)庫網(wǎng)站上。InnoDB 是為處理巨大數(shù)據(jù)量時(shí)的最大性能設(shè)計(jì)跷坝，它的 CPU 效率可能是任何其他基于磁盤的關(guān)系數(shù)據(jù)庫引擎所不能匹敵的酵镜。

除了以上幾個(gè)亮點(diǎn)之外，InnoDB 常常還有很多其它的功能特色帶給使用者驚喜柴钻。當(dāng)然淮韭，使用 InnoDB 存儲引擎肯定也有缺點(diǎn)。相對于其它存儲引擎來說贴届，使用 InnoDB 存儲引擎的讀寫效率稍差靠粪，且占用的數(shù)據(jù)空間相對較大。

物理存儲

使用 InnoDB 時(shí)毫蚓，MySQL 會在數(shù)據(jù)目錄（Data）下創(chuàng)建一個(gè)名為 ibdata1 的 10MB 大小的自動擴(kuò)展數(shù)據(jù)文件占键，以及兩個(gè)名為 ib_logfile0 和 ib_logfile1 的 5MB 大小的日志文件。

InnoDB 存儲引擎和 MyISAM 不太一樣元潘，雖然也有 .frm 文件來存放表結(jié)構(gòu)定義相關(guān)的元數(shù)據(jù)畔乙，但是表數(shù)據(jù)和索引數(shù)據(jù)是存放在一起的。至于是每個(gè)表單獨(dú)存放還是所有表存放在一起翩概，用戶可以自己設(shè)置（下面會介紹如何設(shè)置）牲距。

InnoDB 的物理存儲結(jié)構(gòu)分為兩大部分：

數(shù)據(jù)文件（表數(shù)據(jù)和索引數(shù)據(jù)）

數(shù)據(jù)文件用來存放數(shù)據(jù)表中的數(shù)據(jù)和所有的索引數(shù)據(jù)，包括主鍵和其他普通索引钥庇。

InnoDB 存儲的數(shù)據(jù)采用表空間（Tablepace）進(jìn)行存放設(shè)計(jì)牍鞠。表空間是用來存放 MySQL 系統(tǒng)相關(guān)信息的一個(gè)特殊共享表空間。

InnoDB 的表空間分為以下兩種形式：

  1. 共享表空間上沐，表數(shù)據(jù)和索引都存放在同一個(gè)表空間皮服。默認(rèn)的表空間文件就是上面所提到的 MySQL 初始化路徑下的 ibdata1 文件。
  2. 獨(dú)立表空間参咙，每個(gè)表的數(shù)據(jù)和索引被存放在一個(gè)單獨(dú)的 .ibd 文件中龄广。

可以通過以下命令查看 MySQL 是否使用獨(dú)立表空間：

mysql> SHOW VARIABLES LIKE 'innodb_file_per_table%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set, 1 warning (0.01 sec)

innodb_file_per_table 值為 ON 時(shí)表示開啟獨(dú)立表文件，InnoDB 表的數(shù)據(jù)和索引都會以單獨(dú)的形式存放蕴侧；值為 OFF 時(shí)择同，InnoDB 表的數(shù)據(jù)和索引都存放在一個(gè)表空間【幌可以通過設(shè)置該參數(shù)的值來決定是否使用獨(dú)立表空間敲才，具體設(shè)置文章后面會講解裹纳。

共享表空間

共享表空間的數(shù)據(jù)文件可以設(shè)置為固定大小和可自動擴(kuò)展大小兩種形式。自動擴(kuò)展形式的文件可以設(shè)置文件的最大大小和每次擴(kuò)展量紧武。在創(chuàng)建自動擴(kuò)展的數(shù)據(jù)文件時(shí)剃氧，建議大家最好加上最大尺寸的屬性，一個(gè)原因是文件系統(tǒng)本身有一定的大小限制阻星，還有一個(gè)原因就是方便自身維護(hù)朋鞍。

當(dāng)表空間快要用完的時(shí)候，我們必須要為其增加數(shù)據(jù)文件妥箕，當(dāng)然滥酥，只有共享表空間有此操作。

共享表空間增加數(shù)據(jù)文件的操作比較簡單畦幢，只需要在 innodb_data_file_path 參數(shù)后面按照標(biāo)準(zhǔn)格式設(shè)置好文件路徑和相關(guān)屬性即可坎吻。

innodb_data_file_path 參數(shù)負(fù)責(zé)定義共享表空間的路徑、初始化大小宇葱、自動擴(kuò)展策略瘦真。可以使用以下命令查看當(dāng)前共享表空間文件的路徑贝搁、大小和自動化策略：

mysql> SHOW VARIABLES LIKE 'innodb_data_file_path%';
+-----------------------+------------------------+
| Variable_name         | Value                  |
+-----------------------+------------------------+
| innodb_data_file_path | ibdata1:12M:autoextend |
+-----------------------+------------------------+
1 row in set, 1 warning (0.01 sec)

用戶可以通過 innodb_data_file_path 參數(shù)來指定表空間文件吗氏，格式如下：

innodb_data_file_path=datafile_spec1[;datafile_spec2]...

其中，datafile_spec1 格式為表空間文件路徑:大小:屬性雷逆，還可以指定多個(gè)文件組成一個(gè)表空間弦讽，同時(shí)指定文件的屬性，例如：

[mysqld]
innodb_data_file_path=/db/ibdata1:2000M;/dr2/db/ibdata2:2000M:autoextend

這里將 /db/ibdata1 和 /dr2/db/ibdata2 兩個(gè)文件用來組成表空間膀哲。若這兩個(gè)文件位于不同的磁盤上往产，磁盤的負(fù)載可能被平均，因此可以提高數(shù)據(jù)庫的整體性能某宪。

指定多個(gè)文件時(shí)仿村，autoextend 屬性只在最后一個(gè)數(shù)據(jù)文件中指定，表示表空間自動擴(kuò)展兴喂。這里表示文件 ibdata1 的大小為 2000MB蜘腌，文件 ibdata2 的大小為 2000MB恶复，如果用完了 2000MB录别，該文件還可以自動增長邮破。

設(shè)置完 innodb_data_file_path 參數(shù)后，所有基于 InnoDB 存儲引擎的表的數(shù)據(jù)都會記錄到該共享表空間中壶谒。

不過這里需要注意的是云矫，InnoDB 在創(chuàng)建新數(shù)據(jù)文件時(shí)不會創(chuàng)建目錄，如果指定目錄不存在汗菜，則會報(bào)錯并無法啟動让禀。另外挑社，InnoDB 給共享表空間增加數(shù)據(jù)文件之后，必須要重啟數(shù)據(jù)庫系統(tǒng)才能生效巡揍。

這也是大多數(shù)人一直不太喜歡使用共享表空間而選用獨(dú)立表空間的原因之一痛阻。

獨(dú)立表空間

通過設(shè)置 innodb_file_per_table 參數(shù)，可以將每個(gè)基于 InnoDB 存儲引擎的表產(chǎn)生一個(gè)獨(dú)立表空間吼肥。

獨(dú)立表空間的命名規(guī)則為表名.ibd录平。通過這樣的方式麻车，用戶不用將所有數(shù)據(jù)都存放于默認(rèn)的表空間中缀皱。

使用 SET 命令打開/關(guān)閉獨(dú)立表空間，命令和運(yùn)行結(jié)果如下：

mysql> SET GLOBAL innodb_file_per_table=1;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW VARIABLES LIKE 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |shell
+-----------------------+-------+
1 row in set, 1 warning (0.03 sec)

mysql> SET GLOBAL innodb_file_per_table=0;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | OFF   |
+-----------------------+-------+
1 row in set, 1 warning (0.00 sec)

需要注意的是动猬，單獨(dú)的表空間文件只存儲該表的數(shù)據(jù)啤斗、索引和緩沖等信息。所以無論是使用共享表空間還是獨(dú)享表空間來存放表赁咙，共享表空間都是必須存在的钮莲。

日志文件

默認(rèn)情況下，InnoDB 存儲引擎的數(shù)據(jù)目錄下會有兩個(gè)名為 ib_logfile0 和 ib_logfile1 的文件彼水。在 MySQL 官方手冊中將其稱為 InnoDB 存儲引擎的重做日志文件（redo log file）崔拥。

重做日志文件對 InnoDB 存儲引擎至關(guān)重要。InnoDB 可以通過重做日志將數(shù)據(jù)庫宕機(jī)時(shí)已經(jīng)完成但還沒有來得及將數(shù)據(jù)寫入磁盤的事務(wù)恢復(fù)凤覆，也能將所有部分完成并已經(jīng)寫入磁盤的未完成事務(wù)回滾链瓦，并且將數(shù)據(jù)還原，以此來保證數(shù)據(jù)的完整性盯桦。

每個(gè) InnoDB 存儲引擎至少有 1 個(gè)重做日志文件組（group）慈俯，每個(gè)文件組下至少有 2 個(gè)重做日志文件，如默認(rèn)的 ib_logfile0 和 ib_logfile1拥峦。

如果你的數(shù)據(jù)庫中有 InnoDB 的表贴膘，那么千萬別全部刪除 InnoDB 的日志文件，這很可能會讓你的數(shù)據(jù)庫 Crash略号，無法啟動刑峡，或者丟失數(shù)據(jù)。

  • 數(shù)據(jù)庫不工作或停止響應(yīng)玄柠、進(jìn)程中斷等情況突梦，在業(yè)界也叫做數(shù)據(jù)庫 Crash。

在 MySQL 啟動參數(shù)文件設(shè)置中随闪，InnoDB 的所有參數(shù)基本上都帶有前綴“innodb_”阳似，不論是 InnoDB 數(shù)據(jù)還是和日志相關(guān)，或者是其他一些性能铐伴，事務(wù)等等相關(guān)的參數(shù)都是一樣撮奏。

下面是影響重做日志文件的參數(shù)：

  • innodb_log_file_size：指定每個(gè)重做日志的大小俏讹。
  • innodb_log_files_in_group：指定日志文件組中重做日志文件的數(shù)量，默認(rèn)為 1畜吊。
  • innodb_mirrored_log_groups：指定日志鏡像文件組的數(shù)量泽疆，默認(rèn)為 1。
  • innodb_log_group_home_dir：指定日志文件組所在路徑玲献，默認(rèn)為./殉疼。

簡而言之，MySQL 中所有和 InnoDB 相關(guān)的系統(tǒng)變量都以“innodb_”做為前綴捌年。

在 MySQL 中瓢娜，可以通過 skip-innodb 參數(shù)來屏蔽 InnoDB 存儲引擎，這樣即使我們在安裝編譯時(shí)礼预，安裝了 InnoDB 存儲引擎眠砾，使用者也無法創(chuàng)建 InnoDB 的表。

MySQL MyISAM存儲引擎

MyISAM 存儲引擎是 MySQL 中常見的存儲引擎托酸，曾（MySQL 5.1及之前版本）是 MySQL 的默認(rèn)存儲引擎褒颈。

MyISAM 是基于 ISAM 存儲引擎發(fā)展起來的。實(shí)際上那會還沒有存儲引擎的概念励堡，ISAM 只是一種算法谷丸，或者說是數(shù)據(jù)的處理方式。如同 SQL Server/Oracle 這類產(chǎn)品一樣应结，MySQL 對表對象的管理方式只有一種刨疼。隨著 MySQL 架構(gòu)的不斷發(fā)展和演進(jìn)，最終才引入插件式存儲引擎的概念摊趾，ISAM 也進(jìn)化為 MyISAM 并一直作為 MySQL 數(shù)據(jù)庫的默認(rèn)存儲引擎币狠，直到 MySQL 5.5 版本才被 InnoDB 引擎取代了默認(rèn)存儲引擎的地位。

優(yōu)缺點(diǎn)

作為 MySQL 最早的存儲引擎之一砾层，MyISAM 有一些已經(jīng)開發(fā)出來很多年的特性漩绵，可以滿足用戶的實(shí)際需求。例如全文索引肛炮、壓縮止吐、空間函數(shù)（GIS）等。但 MySQL 官方的重心早就不在 MyISAM 引擎上了侨糟，所以近些年來碍扔，MyISAM 一直沒有很大的改進(jìn)，也存在著許多的缺陷秕重。

優(yōu)點(diǎn)

  • 占用空間小
  • 訪問速度快不同，對事務(wù)完整性沒有要求或以 SELECT、INSERT 為主的應(yīng)用基本上都可以使用這個(gè)引擎來創(chuàng)建表
  • 可以配合鎖，實(shí)現(xiàn)操作系統(tǒng)下的復(fù)制備份
  • 支持全文檢索（InnoDB 在 MySQL 5.6 版本以后也支持全文檢索）
  • 數(shù)據(jù)緊湊存儲二拐，因此可獲得更小的索引和更快的全表掃描性能服鹅。

加鎖與并發(fā)

MyISAM 對整張表加鎖，而不是針對行百新。讀取時(shí)會對需要讀到的所有表加共享鎖企软，寫入時(shí)對表加排他鎖。但是在表有讀取查詢的同時(shí)饭望，也可以往表中插入新的記錄（這被稱為并發(fā)插入）仗哨。

修復(fù)

對于 MyISAM 表，MySQL 可以手工（執(zhí)行命令 CHECK TABLE tablename）或者自動執(zhí)行檢查和修復(fù)（執(zhí)行命令 REPAIR TABLE tablename）操作铅辞，但這里說的修復(fù)和事務(wù)恢復(fù)以及崩潰修復(fù)是不同的概念厌漂。

另外，如果 MySQL 服務(wù)器已經(jīng)關(guān)閉巷挥，也可以通過 myisamchk 命令行工具進(jìn)行檢查和修復(fù)操作桩卵。

索引特性

MyISAM 支持以下 3 種類型的索引：

  • B-Tree 索引：顧名思義，就是所有的索引節(jié)點(diǎn)都按照 balance tree 的數(shù)據(jù)結(jié)構(gòu)來存儲倍宾，所有的索引數(shù)據(jù)節(jié)點(diǎn)都在葉節(jié)點(diǎn)。
  • R-Tree 索引：R-Tree 索引的存儲方式和 b-tree 索引有一些區(qū)別胜嗓，主要設(shè)計(jì)用于為存儲空間和多維數(shù)據(jù)的字段做索引高职，所以對于目前的 MySQL 版本來說，也僅支持 geometry 類型的字段作索引辞州。
  • Full-text 索引：就是全文索引怔锌，它的存儲結(jié)構(gòu)也是 b-tree。主要是為了解決需要用 like 查詢時(shí)的低效問題变过。

MyISAM 上面三種索引類型中埃元，最經(jīng)常使用的就是 B-Tree 索引了，偶爾會使用到 Full-text媚狰，但是 R-Tree 索引一般系統(tǒng)中都是很少用到的岛杀。另外 MyISAM 的 B-Tree 索引有一個(gè)較大的限制，那就是參與一個(gè)索引的所有字段的長度之和不能超過 1000 字節(jié)崭孤。

缺點(diǎn)

  • 不支持事務(wù)的完整性和并發(fā)性
  • 不支持行級鎖类嗤，使用表級鎖，并發(fā)性差
  • 主機(jī)宕機(jī)后辨宠，MyISAM表易損壞遗锣，災(zāi)難恢復(fù)性不佳
  • 數(shù)據(jù)庫崩潰后無法安全恢復(fù)
  • 只緩存索引，數(shù)據(jù)的緩存是利用操作系統(tǒng)緩沖區(qū)來實(shí)現(xiàn)的嗤形，可能會引發(fā)過多的系統(tǒng)調(diào)用精偿，且效率不佳

物理存儲

MyISAM 存儲引擎的表在數(shù)據(jù)庫中被存儲成 3 個(gè)物理文件，文件名與表名相同。擴(kuò)展名為 frm笔咽、MYD 和 MYI墓阀。其中：

  • frm 為擴(kuò)展名的文件存儲表的結(jié)構(gòu)；
  • MYD 為擴(kuò)展名的文件存儲數(shù)據(jù)拓轻，其是 MYData 的縮寫斯撮；
  • MYI 為擴(kuò)展名的文件存儲索引，其是 MYIndex 的縮寫扶叉。不管表有多少索引勿锅，都是存放在同一個(gè) .MYI 文件中。

MyISAM 類型的數(shù)據(jù)文件和索引文件可以放置在不同的目錄枣氧，平均分布 IO溢十，以此來獲得更快的速度。

要指定索引文件和數(shù)據(jù)文件的路徑达吞，需要在創(chuàng)建表的時(shí)候通過 DATA DIRECTORY 和 INDEX DIRECTORY 語句指定张弛，也就是說不同 MyISAM 表的索引文件和數(shù)據(jù)文件可以放置到不同的路徑下。文件路徑需要是絕對路徑酪劫，并且具有訪問權(quán)限吞鸭。

雖然每一個(gè) MyISAM 的表數(shù)據(jù)都存放在后綴名為 .MYD 的文件中，但是每個(gè)文件的存放格式可能并不完全一樣覆糟。因?yàn)?MyISAM 支持 3 種不同的數(shù)據(jù)存放格式刻剥，即靜態(tài)型、動態(tài)型和壓縮型滩字。

靜態(tài)型

靜態(tài)型為 MyISAM 存儲引擎的默認(rèn)存儲格式造虏，其字段是固定長度，這樣每個(gè)記錄都是固定長度的麦箍，這種存儲方式存儲非常迅速漓藕，容易緩存，出現(xiàn)故障容易恢復(fù)挟裂。缺點(diǎn)是占用的空間比動態(tài)表多享钞。靜態(tài)型的表的數(shù)據(jù)在存儲的時(shí)候會按照列的寬度定義去補(bǔ)足空格，但是在應(yīng)用訪問的時(shí)候并不會得到這些空格话瞧，空格在返回給應(yīng)用之前就被去掉了嫩与。

需要注意的是，如果需要保存的內(nèi)容后面本來就帶有空格交排，那么在返回結(jié)果的時(shí)候也會被去掉划滋。這一點(diǎn)開發(fā)人員在編寫程序的時(shí)候需要特別注意，因?yàn)殪o態(tài)表是默認(rèn)的存儲格式埃篓，開發(fā)人員可能并沒有意識到這一點(diǎn)处坪，從而丟失了尾部的空格。

動態(tài)型

動態(tài)型包含變長字段，記錄的長度不是固定的同窘。這樣存儲的優(yōu)點(diǎn)是占用的空間相對較少玄帕，但是頻繁的更新刪除記錄會產(chǎn)生碎片，需要定期執(zhí)行 OPTIMIZE TABLE 語句或 myisamchk -r 命令來改善性能想邦，并且出現(xiàn)故障的時(shí)候恢復(fù)相對比較困難裤纹。

壓縮型

與上面兩種格式相比，壓縮型的表就顯得特殊一些丧没。壓縮型的表需要使用 myisampack 工具創(chuàng)建鹰椒，解壓縮則用另外的 myisamchk 命令。壓縮表是制度的呕童，不支持添加或修改記錄漆际。

壓縮表是基于靜態(tài)或動態(tài)格式表的，優(yōu)點(diǎn)在于占用的磁盤空間非常小夺饲，可以減少磁盤 I/O奸汇，從而提升查詢性能。因?yàn)槊總€(gè)記錄都是被單獨(dú)壓縮的往声，所以只有非常小的開支擂找。

理論上，MyISAM 存儲引擎的表可以被多個(gè)數(shù)據(jù)庫實(shí)例同時(shí)使用同時(shí)操作烁挟，但是一般不建議這樣做婴洼，關(guān)于這點(diǎn)，MySQL 官方的用戶手冊中也有提到撼嗓，建議盡量不要在多個(gè) mysqld 之間共享 MyISAM 存儲文件。

如果表在創(chuàng)建并導(dǎo)入數(shù)據(jù)以后欢唾，不會再進(jìn)行修改操作且警，這樣的表或許適合采用 MyISAM 壓縮表。

不同存儲引擎的數(shù)據(jù)表在文件系統(tǒng)中的表示

MySQL 支持 InnoDB礁遣、MyISAM斑芜、Memory、Merge祟霍、Archive杏头、CSV、BLACKHOLE 幾種存儲引擎沸呐，不同存儲引擎的數(shù)據(jù)表在文件系統(tǒng)中的表示也各不相同醇王。

MySQL 中的每一個(gè)數(shù)據(jù)表在磁盤上至少被表示為一個(gè)文件，即存放著該數(shù)據(jù)表結(jié)構(gòu)定義的 .frm 文件崭添。不同的存儲引擎還有其它用來存放數(shù)據(jù)和索引信息的文件寓娩。

注意：從 MySQL 8.0 版本開始，frm 表結(jié)構(gòu)定義文件被取消，MySQL 把表結(jié)構(gòu)信息都寫到了系統(tǒng)表空間棘伴。

不同存儲引擎的數(shù)據(jù)表在文件系統(tǒng)中是如何表示的寞埠。

MyISAM

MyISAM 存儲引擎的數(shù)據(jù)表在數(shù)據(jù)庫目錄里使用 3 個(gè)文件來代表，這些文件的基本名與數(shù)據(jù)表的名字相同焊夸，擴(kuò)展名則表明了文件的具體用途仁连。這三個(gè)文件的擴(kuò)展名分別是：

  • .frm：表結(jié)構(gòu)定義文件，存放著該數(shù)據(jù)表的結(jié)構(gòu)定義阱穗。
  • .MYD：MY Data 的縮寫饭冬，數(shù)據(jù)文件，存放著該數(shù)據(jù)表中各個(gè)行的數(shù)據(jù)颇象。
  • .MYI： MY Index 的縮寫伍伤，索引文件，存放著該數(shù)據(jù)表的全部索引信息遣钳。

下面創(chuàng)建存儲引擎為 MyISAM 的 tb_myisam 表扰魂，SQL 語句如下。

mysql> SET default_storage_engine=MyISAM;
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE tb_myisam(
    -> id INT
    -> );
Query OK, 0 rows affected (0.03 sec)

MERGE

MERGE 存儲引擎的數(shù)據(jù)表其實(shí)是一個(gè)邏輯結(jié)構(gòu)蕴茴。它代表著由一組結(jié)構(gòu)完全相同的 MyISAM 數(shù)據(jù)表所構(gòu)成的集合劝评。有關(guān)的查詢命令會把它當(dāng)作一個(gè)大數(shù)據(jù)表來對待。

MERGE 存儲引擎的數(shù)據(jù)表除了擁有存儲表結(jié)構(gòu)定義的 .frm 文件以外倦淀，還有一個(gè)擴(kuò)展名為 .mgr 的文件蒋畜，這個(gè)文件里不保存數(shù)據(jù)，而是數(shù)據(jù)的來源地撞叽。通俗的說姻成，就是一份由多個(gè) MyISAM 數(shù)據(jù)表的名單構(gòu)成的 MERGE 數(shù)據(jù)表。

下面創(chuàng)建存儲引擎為 MERGE 的 tb_merge 表愿棋，SQL 語句如下科展。

mysql> SET default_storage_engine=Merge;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE tb_merge(
    -> id INT
    -> );
Query OK, 0 rows affected (0.02 sec)

InnoDB

對于 InnoDB 存儲引擎的數(shù)據(jù)表，一個(gè)表對應(yīng)兩個(gè)文件糠雨，一個(gè)是 *.frm才睹，存儲表結(jié)構(gòu)信息；一個(gè)是 *.ibd甘邀，存儲表中數(shù)據(jù)琅攘。

下面創(chuàng)建存儲引擎為 InnoDB 的 tb_innodb 表，SQL 語句如下松邪。

mysql> SET default_storage_engine=InnoDB;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE tb_innodb(
    -> id INT
    -> );
Query OK, 0 rows affected (0.10 sec)

Memory

Memory 存儲引擎的數(shù)據(jù)表是創(chuàng)建在內(nèi)存中的數(shù)據(jù)表坞琴。因?yàn)?MySQL 服務(wù)器把 Memory 數(shù)據(jù)表的數(shù)據(jù)和索引都存放在了內(nèi)存中而不是硬盤上，所以除了相應(yīng)的 .frm 文件外测摔，Memory 引擎表在文件系統(tǒng)里沒有其它相應(yīng)的代表文件置济。

下面創(chuàng)建存儲引擎為 Memory 的 tb_memory 表解恰，SQL 語句如下。

mysql> SET default_storage_engine=Memory;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE tb_memory(
    -> id INT
    -> );
Query OK, 0 rows affected (0.03 sec)

Archive

Archive 存儲引擎的數(shù)據(jù)表除了擁有 .frm 表結(jié)構(gòu)定義文件外浙于，還有一個(gè)擴(kuò)展名為 .arz 的數(shù)據(jù)文件护盈，用來存儲歷史歸檔數(shù)據(jù)。執(zhí)行優(yōu)化操作時(shí)可能還會出現(xiàn)一個(gè)擴(kuò)展名為 .arn 的文件羞酗。

下面創(chuàng)建存儲引擎為 Archive 的 tb_archive 表腐宋，SQL 語句如下。

mysql> SET default_storage_engine=Archive;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE tb_archive(
    -> id INT
    -> );
Query OK, 0 rows affected (0.04 sec)

CSV

與其它類型的存儲引擎相同檀轨，CSV 引擎表也會包含一個(gè) .frm 表結(jié)構(gòu)定義文件胸竞，此外還會創(chuàng)建一個(gè)擴(kuò)展名為 .CSV 的數(shù)據(jù)文件。這個(gè)文件是 CSV 格式的文本文件参萄，用來保存表中的實(shí)際數(shù)據(jù)卫枝。

.CSV 文件可以直接在 Excel 中打開，或者是使用其它文件編輯工具查看讹挎。另外校赤，還有一個(gè)同名的元信息文件，文件擴(kuò)展名為 .CSM筒溃，用來保存表的狀態(tài)及表中保存的數(shù)據(jù)量马篮。

注意：由于 CSV 文件可被直接編輯，如果操作得當(dāng)怜奖，可以不通過 SQL 語句直接修改 CSV 文件中的內(nèi)容浑测。

CSV 存儲引擎基于 CSV 格式文件存儲數(shù)據(jù)，由于自身文件格式的原因歪玲，所有列必須強(qiáng)制指定 NOT NULL迁央。

下面創(chuàng)建存儲引擎為 CSV 的 tb_csv 表，SQL 語句如下滥崩。

mysql> SET default_storage_engine=csv;
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE tb_csv(
    -> id INT NOT NULL,
    -> name CHAR(10) NOT NULL
    -> );
Query OK, 0 rows affected (0.04 sec)

BLACKHOLE

由于在 BLACKHOLE 存儲引擎的數(shù)據(jù)表中寫入任何數(shù)據(jù)都會消失漱贱，所以除了 .frm 文件，BLACKHOLE 引擎表沒有其他相應(yīng)的代表文件夭委。

下面創(chuàng)建存儲引擎為 BLACKHOLE 的 tb_blackhole 表，SQL 語句如下募强。

mysql> SET default_storage_engine=BLACKHOLE;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE tb_blackhole(
    -> id INT
    -> );
Query OK, 0 rows affected (0.03 sec)

不同存儲引擎的數(shù)據(jù)表在文件系統(tǒng)中的表示株灸。

img

MySQL查看和修改默認(rèn)存儲引擎

如果需要操作默認(rèn)存儲引擎，首先需要查看默認(rèn)存儲引擎擎值』派眨可以通過執(zhí)行下面的語句來查看默認(rèn)的存儲引擎，具體 SQL 語句如下：

SHOW VARIABLES LIKE 'default_storage_engine%';

執(zhí)行上面的 SQL 語句鸠儿，其結(jié)果如圖所示屹蚊。

查看數(shù)據(jù)庫默認(rèn)存儲引擎

執(zhí)行結(jié)果顯示厕氨，InnoDB 存儲引擎為默認(rèn)存儲引擎。

使用下面的語句可以修改數(shù)據(jù)庫臨時(shí)的默認(rèn)存儲引擎：

SET default_storage_engine=< 存儲引擎名 >

例如汹粤，將 MySQL 數(shù)據(jù)庫的臨時(shí)默認(rèn)存儲引擎修改為 MyISAM命斧，輸入的 SQL 語句和運(yùn)行結(jié)果如圖所示。

修改MySQL數(shù)據(jù)庫中的默認(rèn)存儲引擎

此時(shí)嘱兼，可以發(fā)現(xiàn) MySQL 的默認(rèn)存儲引擎已經(jīng)變成了 MyISAM国葬。但是當(dāng)再次重啟客戶端時(shí)，默認(rèn)存儲引擎仍然是 InnoDB芹壕。

如何選擇MySQL存儲引擎汇四？

在使用 MySQL 數(shù)據(jù)庫管理系統(tǒng)時(shí)，選擇一個(gè)合適的存儲引擎是一個(gè)非常復(fù)雜的問題踢涌。不同的存儲引擎都有各自的特性通孽、優(yōu)勢和使用的場合，正確的選擇存儲引擎可以提高應(yīng)用的使用效率睁壁。

為了能夠正確地選擇存儲引擎背苦，必須掌握各種存儲引擎的特性。下面重點(diǎn)介紹幾種常用的存儲引擎堡僻，它們對各種特性的支持如下表所示糠惫。

特性 MyISAM InnoDB MEMORY
存儲限制 有 支持 有
事務(wù)安全 不支持 支持 不支持
鎖機(jī)制 表鎖 行鎖 表鎖
B樹索引 支持 支持 支持
哈希索引 不支持 不支持 支持
全文索引 支持 不支持 不支持
集群索引 不支持 支持 不支持
數(shù)據(jù)緩存 支持 支持
索引緩存 支持 支持 支持
數(shù)據(jù)可壓縮 支持 不支持 不支持
空間使用 低 高 N/A
內(nèi)存使用 低 高 中等
批量插入速度 高 低 高
支持外鍵 不支持 支持 不支持

表中主要介紹了 MyISAM、InnoDB 和 MEMORY 三種存儲引擎特性的對比钉疫。下面詳細(xì)介紹這 3 個(gè)存儲引擎的應(yīng)用場合并給出相應(yīng)的建議硼讽。

MyISAM

在 MySQL 5.1 版本及之前的版本，MyISAM 是默認(rèn)的存儲引擎牲阁。

MyISAM 存儲引擎不支持事務(wù)和外鍵固阁，所以訪問速度比較快。如果應(yīng)用主要以讀取和寫入為主城菊，只有少量的更新和刪除操作备燃，并且對事務(wù)的完整性、并發(fā)性要求不是很高凌唬，那么選擇 MyISAM 存儲引擎是非常適合的并齐。

MyISAM 是在 Web 數(shù)據(jù)倉儲和其他應(yīng)用環(huán)境下最常使用的存儲引擎之一。

InnoDB

MySQL 5.5 版本之后默認(rèn)的事務(wù)型引擎修改為 InnoDB客税。

InnoDB 存儲引擎在事務(wù)上具有優(yōu)勢况褪，即支持具有提交、回滾和崩潰恢復(fù)能力的事務(wù)安裝更耻，所以比 MyISAM 存儲引擎占用更多的磁盤空間测垛。

如果應(yīng)用對事務(wù)的完整性有比較高的要求，在并發(fā)條件下要求數(shù)據(jù)的一致性秧均，數(shù)據(jù)操作除了插入和查詢以外食侮，還包括很多的更新号涯、刪除操作，那么 InnoDB 存儲引擎是比較合適的選擇锯七。

InnoDB 存儲引擎除了可以有效地降低由于刪除和更新導(dǎo)致的鎖定链快，還可以確保事務(wù)的完整提交（Commit）和回滾（Rollback），對于類似計(jì)費(fèi)系統(tǒng)或者財(cái)務(wù)系統(tǒng)等對數(shù)據(jù)準(zhǔn)確性要求比較高的系統(tǒng)起胰，InnoDB 都是合適的選擇久又。

MEMORY

MEMORY 存儲引擎將所有數(shù)據(jù)保存在 RAM 中，所以該存儲引擎的數(shù)據(jù)訪問速度快效五，但是安全上沒有保障地消。

MEMORY 對表的大小有限制，太大的表無法緩存在內(nèi)存中畏妖。由于使用 MEMORY 存儲引擎沒有安全保障脉执，所以要確保數(shù)據(jù)庫異常終止后表中的數(shù)據(jù)可以恢復(fù)。

如果應(yīng)用中涉及數(shù)據(jù)比較少戒劫，且需要進(jìn)行快速訪問半夷，則適合使用 MEMORY 存儲引擎。

總結(jié)

不同應(yīng)用的特點(diǎn)是千差萬別的迅细，選擇適應(yīng)存儲引擎才是最佳方案也不是絕對的巫橄，這需要根據(jù)實(shí)際應(yīng)用進(jìn)行測試，從而得到最適合的結(jié)果茵典。

MySQL修改數(shù)據(jù)表的存儲引擎

MySQL 的核心就是存儲引擎湘换。MySQL 存儲引擎主要有 InnoDB、MyISAM统阿、Memory彩倚、BDB、Merge扶平、Archive帆离、Federated、CSV结澄、BLACKHOLE 等哥谷。

MySQL 中修改數(shù)據(jù)表的存儲引擎的語法格式如下：

ALTER TABLE <表名> ENGINE=<存儲引擎名>;

ENGINE 關(guān)鍵字用來指明新的存儲引擎。

以上這種方法適用于修改單個(gè)表的存儲引擎麻献，如果希望修改默認(rèn)的存儲引擎呼巷，就需要修改 my.cnf 配置文件。在 my.cnf 配置文件的 [mysqld] 后面加入以下語句：

default-storage-engine=存儲引擎名稱

然后保存就可以了赎瑰。

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
  • 人面猴
    序言：七十年代末，一起剝皮案震驚了整個(gè)濱河市破镰，隨后出現(xiàn)的幾起案子餐曼，更是在濱河造成了極大的恐慌压储，老刑警劉巖，帶你破解...
    沈念sama閱讀 212,816評論 6贊 492
  • 死咒
    序言：濱河連續(xù)發(fā)生了三起死亡事件源譬，死亡現(xiàn)場離奇詭異集惋，居然都是意外死亡，警方通過查閱死者的電腦和手機(jī)踩娘，發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 90,729評論 3贊 385
  • 救了他兩次的神仙讓他今天三更去死
    文/潘曉璐 我一進(jìn)店門刮刑，熙熙樓的掌柜王于貴愁眉苦臉地迎上來，“玉大人养渴，你說我怎么就攤上這事雷绢。” “怎么了理卑？”我有些...
    開封第一講書人閱讀 158,300評論 0贊 348
  • 道士緝兇錄：失蹤的賣姜人
    文/不壞的土叔 我叫張陵翘紊，是天一觀的道長。 經(jīng)常有香客問我藐唠，道長帆疟，這世上最難降的妖魔是什么？ 我笑而不...
    開封第一講書人閱讀 56,780評論 1贊 285
  • ?港島之戀（遺憾婚禮）
    正文 為了忘掉前任宇立，我火速辦了婚禮踪宠，結(jié)果婚禮上，老公的妹妹穿的比我還像新娘妈嘹。我一直安慰自己柳琢，他們只是感情好，可當(dāng)我...
    茶點(diǎn)故事閱讀 65,890評論 6贊 385
  • 惡毒庶女頂嫁案：這布局不是一般人想出來的
    文/花漫 我一把揭開白布蟋滴。 她就那樣靜靜地躺著染厅，像睡著了一般。 火紅的嫁衣襯著肌膚如雪津函。 梳的紋絲不亂的頭發(fā)上肖粮，一...
    開封第一講書人閱讀 50,084評論 1贊 291
  • 城市分裂傳說
    那天，我揣著相機(jī)與錄音尔苦，去河邊找鬼涩馆。 笑死，一個(gè)胖子當(dāng)著我的面吹牛允坚，可吹牛的內(nèi)容都是我干的魂那。 我是一名探鬼主播，決...
    沈念sama閱讀 39,151評論 3贊 410
  • 雙鴛鴦連環(huán)套：你想象不到人心有多黑
    文/蒼蘭香墨 我猛地睜開眼稠项，長吁一口氣：“原來是場噩夢啊……” “哼涯雅！你這毒婦竟也來了？” 一聲冷哼從身側(cè)響起展运，我...
    開封第一講書人閱讀 37,912評論 0贊 268
  • 萬榮殺人案實(shí)錄
    序言：老撾萬榮一對情侶失蹤活逆，失蹤者是張志新（化名）和其女友劉穎精刷，沒想到半個(gè)月后，有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體蔗候，經(jīng)...
    沈念sama閱讀 44,355評論 1贊 303
  • ?護(hù)林員之死
    正文 獨(dú)居荒郊野嶺守林人離奇死亡怒允，尸身上長有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點(diǎn)故事閱讀 36,666評論 2贊 327
  • ?白月光啟示錄
    正文 我和宋清朗相戀三年，在試婚紗的時(shí)候發(fā)現(xiàn)自己被綠了锈遥。 大學(xué)時(shí)的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片纫事。...
    茶點(diǎn)故事閱讀 38,809評論 1贊 341
  • 活死人
    序言：一個(gè)原本活蹦亂跳的男人離奇死亡，死狀恐怖所灸，靈堂內(nèi)的尸體忽然破棺而出丽惶，到底是詐尸還是另有隱情，我是刑警寧澤庆寺，帶...
    沈念sama閱讀 34,504評論 4贊 334
  • ?日本核電站爆炸內(nèi)幕
    正文 年R本政府宣布蚊夫，位于F島的核電站，受9級特大地震影響懦尝，放射性物質(zhì)發(fā)生泄漏知纷。R本人自食惡果不足惜，卻給世界環(huán)境...
    茶點(diǎn)故事閱讀 40,150評論 3贊 317
  • 男人毒藥：我在死后第九天來索命
    文/蒙蒙 一陵霉、第九天 我趴在偏房一處隱蔽的房頂上張望琅轧。 院中可真熱鬧，春花似錦踊挠、人聲如沸乍桂。這莊子的主人今日做“春日...
    開封第一講書人閱讀 30,882評論 0贊 21
  • 一樁弒父案效床，背后竟有這般陰謀
    文/蒼蘭香墨 我抬頭看了看天上的太陽睹酌。三九已至，卻和暖如春剩檀，著一層夾襖步出監(jiān)牢的瞬間憋沿，已是汗流浹背。 一陣腳步聲響...
    開封第一講書人閱讀 32,121評論 1贊 267
  • 情欲美人皮
    我被黑心中介騙來泰國打工沪猴， 沒想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留辐啄，地道東北人。 一個(gè)月前我還...
    沈念sama閱讀 46,628評論 2贊 362
  • 代替公主和親
    正文 我出身青樓运嗜，卻偏偏與公主長得像壶辜，于是被迫代替她去往敵國和親。 傳聞我的和親對象是個(gè)殘疾皇子担租，可洞房花燭夜當(dāng)晚...
    茶點(diǎn)故事閱讀 43,724評論 2贊 351