MySQL存儲引擎如何理解

2024-07-24 12:33:20

字體：大中小

供稿：網(wǎng)友

　　這篇文章主要講解了“MySQL存儲引擎怎么理解”，文中的講解內(nèi)容簡單清晰，易于學(xué)習(xí)與理解，下面請大家跟著小編的思路慢慢深入，一起來研究和學(xué)習(xí)“MySQL存儲引擎怎么理解”吧！

　　今天發(fā)現(xiàn)了一個(gè)神奇的參數(shù)：-site:xxxx.net

　　一、存儲引擎的選擇（表類型）

　　1、存儲引擎的介紹

　　與到多數(shù)關(guān)系型數(shù)據(jù)庫的區(qū)別在于MySQL有一個(gè)存儲引擎的概念，針對不同的存儲需求可以選擇最合適的存儲引擎。MySQL中的插件式的存儲引擎是其一大特色，用戶可以根據(jù)應(yīng)用的需求選擇如何存儲、是否索引，是否使用事務(wù)。嘿嘿，你也可以根據(jù)業(yè)務(wù)環(huán)境去適配最適合自己業(yè)務(wù)的存儲引擎。

　　Oracle從中嗅到了商機(jī)，收購了MySQL，從此有了企業(yè)版（商業(yè)支持）。社區(qū)版依舊可以免費(fèi)下載。另一大魅力也是因?yàn)殚_源，社區(qū)高度活躍，人人都可貢獻(xiàn)。接下來介紹幾種使用比較多的存儲引擎，存儲引擎并無優(yōu)劣之分，有的只是誰更適合對應(yīng)的生產(chǎn)業(yè)務(wù)環(huán)境。

　　MySQL5.0中支持的存儲引擎有FEDERATED、MRG_MYISAM、MyISAM、BLACKHOLE、CSV、MEMORY、ARCHIVE、NDB Cluster、BDB、EXAMPLE、InnoDB（MySQL5.5以及MariaDB10.2之后的默認(rèn)存儲引擎）、PERFORMANCE_SCHEMA（非常規(guī)存儲數(shù)據(jù)引擎）。下面給出MySQL與MariaDB支持的存儲器引擎的對比，可以看出MariaDB新增了Aria引擎：

　　作用描述：

　　Engine：引擎名稱（描述）；

　　Support：當(dāng)前版本數(shù)據(jù)庫是否支持該存儲引擎，YES：支持、NO：不支持；Supports transactions, row-level locking, and foreign keys，個(gè)人字面上翻譯這段話：支持事務(wù)、行級別鎖和外鍵；

　　Comment：對該存儲引擎的詳情描述，比如描述該引擎否支持事務(wù)和外鍵；

　　Transactions：對該存儲引擎是否支持事務(wù)的描述，YES：支持、NO：不支持；

　　XA：是否滿足XA規(guī)范。XA規(guī)范是開放群組關(guān)于分布式事務(wù)處理(DTP)的規(guī)范。YES：支持、NO：不支持；

　　Savepoints：字面意思是保存點(diǎn)，對事物控制是否支持，YES：支持、NO：不支持。

　　小聲嗶嗶，如果你能閱讀明白官方的一些英文文檔，這將有助于你對MySQL存儲引擎的進(jìn)一步理解，養(yǎng)成閱讀源碼或者文檔的能力。

　　順帶的提一下MySQL的妹妹MariaDB。在MySQL的復(fù)刻版本MariaDB中10.2之前使用的自帶的新引擎Aria，在MariaDB10.2之后使用的默認(rèn)存儲引擎也是InnoDB，足以看出InnoDB存儲引擎的優(yōu)秀之處。MariaDB的API和協(xié)議兼容MySQL，另外又添加了一些功能，以支持本地的非阻塞操作和進(jìn)度報(bào)告。這意味著，所有使用MySQL的連接器、程序庫和應(yīng)用程序也將可以在MariaDB下工作。在此基礎(chǔ)上，由于擔(dān)心甲骨文MySQL的一個(gè)更加封閉的軟件項(xiàng)目，F(xiàn)edora等Linux發(fā)行版已經(jīng)在最新版本中以MariaDB取代MySQL，維基媒體基金會的服務(wù)器同樣也使用MariaDB取代了MySQL。

　　主要需要了解的幾種存儲引擎：

　　MyISAM

　　InnoDB

　　MEMORY

　　MERGE

　　下面將著重介紹我最近看書認(rèn)識的幾種常用的存儲引擎，對比各個(gè)存儲引擎之間的區(qū)別，幫助我們理解不同存儲引擎的使用方式。更多詳情可以參考MySQL的官方文檔。

　　2、部分存儲引擎的特性

　　存儲引擎/支持特性存儲限制事務(wù)安全鎖機(jī)制 B樹索引哈希索引全文索引集群索引數(shù)據(jù)緩存索引緩存數(shù)據(jù)可壓縮空間使用內(nèi)存使用批量插入速度外鍵支持

　　InnoDB存儲引擎在MySQL5.6版本開始支持全文索引。在MySQL5.7推出了虛擬列，MySQL8.0新特性加入了函數(shù)索引支持。

　　2.1、MyISAM存儲引擎

　　MyISAM是MySQL5.5之前默認(rèn)的存儲引擎。MyISAM不支持事務(wù)、不支持外鍵。優(yōu)勢在于訪問速度快，對事務(wù)完整性沒有特殊要求或者以select和insert為主的應(yīng)用基本上可以使用MyISAM作為存儲引擎創(chuàng)建表。我們先弄個(gè)例子出來演示，事先準(zhǔn)備了一張數(shù)據(jù)千萬級別的表，看看這個(gè)存儲引擎的特性：

　　我已經(jīng)創(chuàng)建好了數(shù)據(jù)庫為test，在test中分別創(chuàng)建了兩張表test和tolove。test表在創(chuàng)建的時(shí)候指定默認(rèn)存儲引擎為MyISAM，tolove表指定存儲引擎為InnoDB。

　　使用MyISAM存儲引擎創(chuàng)建的表tolove，查詢存儲有1kw數(shù)據(jù)的表tolove。

　　tips：你可以使用use test，切換到test數(shù)據(jù)庫，就不用像我這樣查詢tolove表去指定test數(shù)據(jù)庫了喲！

　　MySQL [(none)]> select count(*) from test.tolove;

　　+----------+

　　| count(*) |

　　+----------+

　　| 10000000 |

　　+----------+

　　1 row in set (0.000 sec)

　　再看演示使用InnoDB存儲引擎創(chuàng)建的表test，同樣為了演示，事先隨機(jī)生成了1kw條數(shù)據(jù)。

　　MySQL [(none)]> select count(*) from test.test;

　　+----------+

　　| count(*) |

　　+----------+

　　| 10000000 |

　　+----------+

　　1 row in set (3.080 sec)

　　進(jìn)行對比同樣存儲1kw條數(shù)據(jù)的表，使用MyISAM作為存儲引擎查詢速度堪稱光速1 row in set (0.000 sec)，使用InnoDB存儲引擎查詢速度稍遜一籌1 row in set (3.080 sec)。

　　MyISAM在磁盤中存儲的文件：

　　每個(gè)MyISAM在磁盤上存儲成3個(gè)文件，其文件名和表名都相同，擴(kuò)展名分別是：

　　.frm：存儲表定義；

　　.MYD：MYData，存儲數(shù)據(jù)；

　　.MYI：MYindex，存儲索引。

　　MySQL存儲引擎怎么理解

　　數(shù)據(jù)文件和索引文件可以存放在不同的目錄，平均分布IO，獲得更快的速度，提升性能。需要指定索引文件和數(shù)據(jù)文件存儲的路徑，創(chuàng)建表時(shí)通過DATA DIRECTORY和INDEX DIRECTORY參數(shù)指定，表明不同MyISAM表的索引文件和數(shù)據(jù)文件可以存放在不同的路徑下。當(dāng)然，需要給予該路徑的訪問權(quán)限。

　　MyISAM損壞處理方式：

　　MyISAM類型的表可能會損壞，原因多種多樣。損壞后的表有可能不能被訪問，會提示需要修復(fù)或者訪問后提示返回錯(cuò)誤結(jié)果。MyISAM類型的表，可以通過提供的修復(fù)工具執(zhí)行CHECK TABLE語句檢查MyISAM表的健康程度，使用REPAIR TABLE語句修復(fù)一個(gè)損壞的表。表損壞可能會導(dǎo)致數(shù)據(jù)庫異常重新啟動，需要盡快修復(fù)并確定原因好做應(yīng)對策略。

　　MySQL存儲引擎怎么理解

　　使用MyISAM存儲引擎的表支持3種不同的存儲格式，如下：

　　靜態(tài)表，固定長度；

　　動態(tài)表

　　壓縮表

　　靜態(tài)表是MyISAM存儲引擎的默認(rèn)存儲格式，字段長度是定長，記錄都是固定長度。優(yōu)勢在于存儲迅速、容易緩存、出現(xiàn)故障易恢復(fù)；缺點(diǎn)是相對耗存儲空間。需要注意的是：如需保存內(nèi)容后面的空格，默認(rèn)返回結(jié)果會去掉后面的空格。

　　動態(tài)表包含變長字段，記錄不是固定長度，存儲優(yōu)勢：占用空間相對較小、但頻繁刪除和更新記錄會產(chǎn)生碎片。這時(shí)，需要定期執(zhí)行optimize table語句或者myisamchk -r命令來改善性能，出現(xiàn)故障恢復(fù)相對較難。

　　壓縮表由mysiampack工具創(chuàng)建，占用磁盤空間很小。因?yàn)槊總€(gè)記錄是被單獨(dú)壓縮，所以訪問開始非常小。

　　梳理一下MyISAM存儲引擎的要點(diǎn)，如下圖1-2-2-1所示：

　　MySQL存儲引擎怎么理解

　　順帶安利一波，前段時(shí)間發(fā)現(xiàn)WPS也能夠制作精美的思維導(dǎo)圖，并且支持一鍵導(dǎo)入到doc文件中。普通用戶最多可存儲150個(gè)文件。之前也用過XMind、processon、gitmind等等，現(xiàn)在使用WPS更方便了。

　　2.2、InnoDB存儲引擎

　　優(yōu)點(diǎn)與缺點(diǎn)：InnoDB存儲引擎提供了具有提交（commit）、回滾（rollback）和崩潰恢復(fù)能力的事務(wù)安全。但對比MyISAM存儲引擎，InnoDB寫的處理效率相對差一些，并且會占用更多的磁盤空間保留數(shù)據(jù)和索引。下圖是我存儲了1kw條數(shù)據(jù)的表，并且使用的是InnoDB存儲引擎。student01表同樣使用了InnoDB存儲引擎，存儲數(shù)據(jù)為100w條。從下圖可以看出存儲數(shù)據(jù)索引在.ibd文件中、表結(jié)構(gòu)則存在.frm文件中。

　　MySQL存儲引擎怎么理解

　　2.2.1、自動增長列

　　InnoDB表的自動增長列可以手工插入，但插入的值為空或者0，則實(shí)際插入的將是自動自動增長后的值。

　　本來想繼續(xù)使用bols那張表作為演示的，思來想去還是正經(jīng)一點(diǎn)。為了演示，我又新增了一張表為autoincre_test，表示id設(shè)置為主鍵且自增長，存儲引擎選擇InnoDB。然后插入了3條數(shù)據(jù)進(jìn)行演示。查詢當(dāng)前線程最后插入數(shù)據(jù)的記錄使用值：

　　MySQL [test]> create table autoincre_test(id int not null auto_increment,name varchar(16),primary key(id))engine=innodb;

　　Query OK, 0 rows affected (0.018 sec)

　　MySQL [test]> insert into autoincre_test values(1,'1'),(0,'2'),(null,'3');

　　Query OK, 3 rows affected (0.007 sec)

　　Records: 3 Duplicates: 0 Warnings: 0

　　MySQL [test]> select * from autoincre_test;

　　+----+------+

　　| id | name |

　　+----+------+

　　| 1 | 1    |

　　| 2 | 2    |

　　| 3 | 3    |

　　+----+------+

　　3 rows in set (0.000 sec)

　　select last_insert_id();

　　MySQL [test]> select last_insert_id();

　　+------------------+

　　| last_insert_id() |

　　+------------------+

　　|                2 |

　　+------------------+

　　1 row in set (0.000 sec)

　　tips：可以通過alter table table_name=n;語句強(qiáng)制設(shè)置自動增長列的初始值，默認(rèn)從1開始，但該強(qiáng)制的默認(rèn)值是保留在內(nèi)存中的，如果使用該值之前數(shù)據(jù)庫重新啟動，強(qiáng)制默認(rèn)值則會丟失，就需要重新設(shè)置，畢竟使用內(nèi)存沒有加載到磁盤中。

　　通過上面的演示，你會發(fā)現(xiàn)插入記錄是0或者空時(shí)，實(shí)際插入的將是自動增長后的值。通過last_insert_id()函數(shù)可以查詢當(dāng)前線程最后插入數(shù)據(jù)的記錄使用值。如果一次插入多條記錄，則返回的是第一條記錄使用的自動增長值，這里就不演示插入多條數(shù)據(jù)了。記住一點(diǎn)，可以使用last_insert_id()去查詢id記錄值。

　　對于InnoDB表，自動增長列必須是索引。如果是組合索引，也必須是組合索引的第一列。但對于MyISAM表，自動增長列可以是組合索引的其它列。這樣插入記錄后，自動增長列是按照組合索引的前面幾列排序后遞增的。你可以創(chuàng)建一張表指定MyISAM存儲引擎，然后將兩列字段組合索引進(jìn)行測試驗(yàn)證。

　　2.2.2、外鍵約束

　　在MySQL中，目前支持外鍵約束的存儲引擎只有InnoDB。創(chuàng)建外鍵的時(shí)候，要求父表必須有對應(yīng)的索引。子表創(chuàng)建外鍵的時(shí)候，也會自動創(chuàng)建對應(yīng)的索引。下面將通過實(shí)例進(jìn)行講解。可以從MySQL官網(wǎng)下載示例數(shù)據(jù)庫world和sakila進(jìn)行參考。

　　city表，F(xiàn)OREIGN KEY (CountryCode) REFERENCES country (Code)

　　country表

　　countrylanguage表，F(xiàn)OREIGN KEY (CountryCode) REFERENCES country (Code)

　　通過MySQL workbench或者Navicat逆向生成物理模型進(jìn)行參考，更加直觀。插一句，在MySQL的官網(wǎng)同樣有一個(gè)sakila數(shù)據(jù)庫是關(guān)于演員電影的，也提供了sakila的ERR物理模型圖，這句話做了超鏈接，可以直接訪問。給出我之前逆向生成的world數(shù)據(jù)庫的物理模型：

　　MySQL存儲引擎怎么理解

　　在創(chuàng)建索引時(shí)，可以指定在刪除、更新父表時(shí)，對子表進(jìn)行的相應(yīng)操作包含：

　　restrict

　　cascade

　　set null和no action

　　其中restrict和no action相同，restrict限制在子表有關(guān)聯(lián)記錄的情況下父表無法更新；cascade表示在父表更新或刪除的時(shí)候，級聯(lián)更新或者刪除子表對應(yīng)記錄；set null表示在父表更新或刪除的時(shí)候，子表的對應(yīng)字段被set null。選擇cascade以及set null時(shí)需要謹(jǐn)慎操作，有可能導(dǎo)致數(shù)據(jù)丟失。

　　在導(dǎo)入多個(gè)表的數(shù)據(jù)時(shí)，如果忽略表之前的導(dǎo)入順序，可以暫時(shí)關(guān)閉外鍵檢查；同樣執(zhí)行l(wèi)oad data和alter table時(shí)也可以暫時(shí)關(guān)閉外鍵檢查加快處理的速度，提升效率。關(guān)閉外鍵檢查的命令為：

　　set foreign_key_checks=0;

　　執(zhí)行完導(dǎo)入數(shù)據(jù)或者修改表的操作后，通過開啟外鍵檢查命令改回來：

　　set foreign_key_checks=1;

　　對于InnoDB類型的表，外鍵信息可以通過show create table或者show table status查看。比如查找world數(shù)據(jù)庫中的city表：

　　MySQL [sakila]> show table status like 'city'/G

　　關(guān)于外鍵約束就提這么多，沒有演示創(chuàng)建以及刪除，因?yàn)橘N太多的SQL語句太占篇幅了。可以到MySQL官網(wǎng)下載world和sakila數(shù)據(jù)庫進(jìn)行測試。

　　2.2.3、存儲方式

　　InnoDB存儲表和索引有兩種方式：

　　共享表空間存儲

　　多表空間存儲

　　使用共享表空間存儲，這種方式創(chuàng)建的表的表結(jié)構(gòu)保存在.frm文件中，數(shù)據(jù)和索引保存在innodb_data_home_dir和innodb_data_file_path定義的表空間中，可以是多個(gè)文件。在開頭介紹InnoDB存儲引擎時(shí)也提到過文件存儲位置。

　　使用多表空間存儲，這種方式創(chuàng)建的表的表結(jié)構(gòu)仍然保存在.frm文件中，但每個(gè)表的數(shù)據(jù)和索引單獨(dú)保存在.ibd文件中。如果是個(gè)分區(qū)表，則每個(gè)分區(qū)對應(yīng)單獨(dú)的.ibd文件，文件名為表名+分區(qū)名。可以在創(chuàng)建分區(qū)的時(shí)候指定每個(gè)分區(qū)的數(shù)據(jù)文件位置，以此來平均分布磁盤的IO，達(dá)到緩解磁盤壓力的目的。如下是在Windows下使用InnoDB存儲了海量數(shù)據(jù)的文件：

　　MySQL存儲引擎怎么理解

　　使用多表空間存儲需要設(shè)置參數(shù)innodb_file_per_table，重啟數(shù)據(jù)庫服務(wù)器才能生效喲。多表空間的參數(shù)生效后，只對新建的表生效。多表空間的數(shù)據(jù)文件無大小限制，無需設(shè)置初始大小，也不需設(shè)置文件的最大限制與擴(kuò)展大小等參數(shù)。使用多表空間存儲優(yōu)勢在于方便單表備份和恢復(fù)操作。雖然不能直接復(fù)制.frm和.ibd文件達(dá)到目的，但可以使用如下命令操作：

　　alter table table_name discard tablespace;alter table table_name import tablespace;

　　將備份恢復(fù)到數(shù)據(jù)庫中，單表備份，只能恢復(fù)到原來所在的數(shù)據(jù)庫中，無法恢復(fù)到其它數(shù)據(jù)庫中。如過需要將單表恢復(fù)至其它目標(biāo)數(shù)據(jù)庫中，則需要通過mysqldump和mysqlimport來實(shí)現(xiàn)。

　　注意：即便多表存儲更有優(yōu)勢，但是共享表存儲空間依舊是必須的，InnoDB將內(nèi)部數(shù)據(jù)字典和在線重做日志存在這個(gè)文件中。

　　梳理一下InnoDB存儲引擎的要點(diǎn)，

　　MySQL存儲引擎怎么理解

　　關(guān)于InnoDB存儲引擎就介紹到此處了，更多詳情可以參考MySQL的官方文檔。是不是發(fā)現(xiàn)了我只在MyISAM和InnoDB存儲引擎做了總結(jié)的思維導(dǎo)圖。沒錯(cuò)，只做了這兩個(gè)，因?yàn)檫@倆最常用。至于為啥是粉色背景，因?yàn)槔戏蛴幸活w少女心！

　　2.3、MEMORY存儲引擎

　　MEMORY存儲引擎使用存在與內(nèi)存中的內(nèi)容來創(chuàng)建表。每個(gè)MEMORY表只對應(yīng)一個(gè)磁盤文件，格式是.frm。MEMORY類型的表訪問速度極快，存在內(nèi)存中當(dāng)然快。這就是Redis為什么這么快？不僅小？還能持久？咱回到正題，MEMORY存在內(nèi)存中并默認(rèn)使用hash索引，一旦服務(wù)關(guān)閉，表中數(shù)據(jù)會丟失。創(chuàng)建一張名為GIRLS的表指定存儲引擎為MEMORY，注意了在UNIX和Linux操作系統(tǒng)下，是對字段和表名大小是寫敏感的，關(guān)鍵字不影響。

　　CREATE TABLE GIRLS (

　　 ID int NOT NULL,GIRE_NAME varchar(64) NOT NULL,GIRL_AGE varchar(10) NOT NULL,

　　 CUP_SIZE varchar(2) NOT NULL,PRIMARY KEY (ID)) ENGINE=MEMORY DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

　　還記得在介紹存儲引擎做的那會張表格嗎，有介紹到MEMORY支持B TREE索引。雖然MEMORY默認(rèn)使用的索引是hash索引，但是你可以手動指定索引類型。例如默認(rèn)手動指定使用關(guān)鍵字USING HASH：

　　-- 創(chuàng)建索引指定索引類型為hash。create index mem_hash USING HASH on GIRLS(ID);-- 查詢索引類型，簡化了一下，只展示了部分參數(shù)。mysql> SHOW TABLE STATUS LIKE 'GIRLS'/G*************************** 1. row ***************************

　　           Name: GIRLS         Engine: MEMORY

　　        Version: 10

　　     Row_format: Fixed1 row in set (0.00 sec)

　　雖然MEMORY容易丟失數(shù)據(jù)，但是在啟動MySQL服務(wù)的時(shí)候，我們可以使用**–init-file選項(xiàng)，將insert into … select或者load data infile**這樣的語句存放在這個(gè)指定的文件中，就可以在服務(wù)啟動時(shí)從持久穩(wěn)固的數(shù)據(jù)源裝載表。

　　服務(wù)器需要提供足夠的內(nèi)存來維持所有在同一時(shí)間使用的MEMORY表，當(dāng)不在需要MEMORY表內(nèi)容之時(shí)，釋放被MEMORY表使用的內(nèi)存。仔細(xì)思考一下，如果內(nèi)存用了不釋放那將有多可怕。此時(shí)可以執(zhí)行delete form 或truncate table亦或完整地刪除整個(gè)表，使用drop table。這里提一點(diǎn)，在Oracle中也同樣支持truncate，使用truncate的好處在于不用再去考慮回滾（rollback），效率更高。使用truncate需要在命令模式下使用，其它客戶端工具可能不支持。

　　每個(gè)MEMORY表中存放的數(shù)據(jù)量大小，受max_heap_table_size系統(tǒng)變量約束，初始值為16MB，可以根據(jù)需求調(diào)整。通過max_rows可以指定表的最大行數(shù)。

　　MEMORY存儲引擎最大特色是快，主要用于內(nèi)容變化不頻繁的代碼表，或者是為了做統(tǒng)計(jì)提供的中間表，效率更高。使用MEMORY時(shí)需謹(jǐn)慎，萬一忘了這廝重啟數(shù)據(jù)就沒了就尷尬了。所以在使用時(shí)，考慮好重啟服務(wù)器后如何取得數(shù)據(jù)。

　　關(guān)于MEMORY存儲引擎就介紹到這里，大部分都是些理論知識，更多的需要自己去實(shí)踐測試。

　　2.4、MERGE存儲引擎

　　MERGE存儲引擎是一組MyISAM表的組合，這些MyISAM表必須結(jié)果完全相同，MERGE表本身沒有數(shù)據(jù)，對MERGE類型的表可以進(jìn)行查詢、更新、刪除操作，實(shí)際上是對內(nèi)部的MyISAM表進(jìn)行操作的。對于MERGE類型表的插入操作，通過insert_method子句定義插入的，可以有3個(gè)不同的值，使用first或last插入操作對應(yīng)開始與最后一個(gè)表上。如果不定義這個(gè)子句，或者定義為NO，表示不能對MERGE表進(jìn)行操作。

　　對MERGE表進(jìn)行DROP操作，只是對MERGE的定義進(jìn)行刪除，對內(nèi)部表沒有任何影響。MERGE表上保留兩個(gè)文件，文件名以表的名字開始，分別為：

　　.frm文件存儲表定義；

　　.mrg文件包含組合表的信息，包含表組成、插入數(shù)據(jù)依據(jù)。

　　可以通過修改.mrg文件來修改表，但修改后需要使用flush tables刷新。測試可以先創(chuàng)建兩張存儲引擎為MyISAM的表，再建一張存儲引擎為MERGE存儲引擎的表。

（編輯：武林網(wǎng)）

上一篇：MySQL表結(jié)構(gòu)實(shí)例解析

下一篇：MySQL數(shù)據(jù)庫基礎(chǔ)知識點(diǎn)有哪些