MySQL你必須要了解存儲引擎

本篇文章給大家?guī)砹岁P(guān)于mysql的相關(guān)知識，其中主要介紹了存儲引擎的相關(guān)問題，還包括了索引設(shè)計與使用、數(shù)據(jù)類型選擇以及字符集設(shè)置等問題，希望對大家有幫助。

推薦學習：mysql

誰說mysql查詢千萬級別的數(shù)據(jù)很拉跨？我今天就要好好的和你拉拉家常，暢談到深夜，一起過除夕！這篇文章也是年前的最后一篇，希望能帶給大家些許收獲，不知不覺查找文檔和參考實體書籍就寫了這么多，自己都感覺到意外。不禁感慨到，知道的越多，才知道不知道的更多。

開發(fā)人員應(yīng)該會關(guān)注MySQL使用的存儲引擎，選擇合適存儲引擎對你的應(yīng)用性能提升是明顯的。在閱讀到本文的時候，肯定是有一定的MySQL或者其它數(shù)據(jù)庫基礎(chǔ)的，不然有些地方看著會很費勁。重點地方，我都進行了加粗處理，這樣更容易獲取關(guān)鍵知識點。

關(guān)于存儲引擎，一篇文章也不可能面面俱到，對個人覺得比較重要、于工作有益的方面進行闡述。如果真的去深挖，估計得一本書的篇幅。順帶還介紹一些數(shù)據(jù)類型選擇、字符集設(shè)置、索引的使用；視圖、存儲過程、函數(shù)以及觸發(fā)器啊等等會在下一篇博文進行詳細的描述。但本文不會做太詳細的敘述。本篇文章以存儲引擎的選擇為核心，如果有出現(xiàn)瑕疵的地方，希望您能留下寶貴的建議。

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

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

1、存儲引擎的介紹

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

Oracle從中嗅到了商機，收購了MySQL，從此有了企業(yè)版（商業(yè)支持）。社區(qū)版依舊可以免費下載。另一大魅力也是因為開源，社區(qū)高度活躍，人人都可貢獻。接下來介紹幾種使用比較多的存儲引擎，存儲引擎并無優(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之后的默認存儲引擎）、PERFORMANCE_SCHEMA（非常規(guī)存儲數(shù)據(jù)引擎）。下面給出MySQL與MariaDB支持的存儲器引擎的對比，可以看出MariaDB新增了Aria引擎：

查看存儲引擎

通過MySQL登錄自帶的字符界面輸入show enginesG;或者使用支持MySQL查詢的工具SQLyog、phpMyAdmin、MySQL workbench等查詢支持的引擎，這里只展示部分喲：

[test@cnwangk ~]$ mysql -uroot -p Enter password:  mysql> show enginesG;*************************** 2. row ***************************       Engine: MRG_MYISAM      Support: YES     Comment: Collection of identical MyISAM tablesTransactions: NO           XA: NO   Savepoints: NO*************************** 3. row ***************************       Engine: MyISAM      Support: YES     Comment: MyISAM storage engineTransactions: NO           XA: NO   Savepoints: NO*************************** 6. row ***************************       Engine: MEMORY      Support: YES     Comment: Hash based, stored in memory, useful for temporary tablesTransactions: NO           XA: NO   Savepoints: NO*************************** 8. row ***************************       Engine: InnoDB      Support: DEFAULT      Comment: Supports transactions, row-level locking, and foreign keysTransactions: YES           XA: YES   Savepoints: YES9 rows in set (0.00 sec)

作用描述：

• Engine：引擎名稱（描述）；
• Support：當前版本數(shù)據(jù)庫是否支持該存儲引擎，YES：支持、NO：不支持；Supports transactions, row-level locking, and foreign keys，個人字面上翻譯這段話：支持事務(wù)、行級別鎖和外鍵；
• Comment：對該存儲引擎的詳情描述，比如描述該引擎否支持事務(wù)和外鍵；
• Transactions：對該存儲引擎是否支持事務(wù)的描述，YES：支持、NO：不支持；
• XA：是否滿足XA規(guī)范。XA規(guī)范是開放群組關(guān)于分布式事務(wù)處理(DTP)的規(guī)范。YES：支持、NO：不支持；
• Savepoints：字面意思是保存點，對事物控制是否支持，YES：支持、NO：不支持。

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

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

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

• MyISAM
• InnoDB
• MEMORY
• MERGE

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

2、部分存儲引擎的特性

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

2.1、MyISAM存儲引擎

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

我已經(jīng)創(chuàng)建好了數(shù)據(jù)庫為test，在test中分別創(chuàng)建了兩張表test和tolove。test表在創(chuàng)建的時候指定默認存儲引擎為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，同樣為了演示，事先隨機生成了1kw條數(shù)據(jù)。

MySQL [(none)]> select count(*) from test.test; +----------+ | count(*) | +----------+ | 10000000 | +----------+ 1 row in set (3.080 sec)

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

MyISAM在磁盤中存儲的文件：

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

• .frm：存儲表定義；
• .MYD：MYData，存儲數(shù)據(jù)；
• .MYI：MYindex，存儲索引。

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

MyISAM損壞處理方式 ：

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

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

• 靜態(tài)表，固定長度；
• 動態(tài)表
• 壓縮表

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

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

壓縮表由mysiampack工具創(chuàng)建，占用磁盤空間很小。因為每個記錄是被單獨壓縮，所以訪問開始非常小。

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

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

2.2、InnoDB存儲引擎

優(yōu)點與缺點：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文件中。

2.2.1、自動增長列

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

本來想繼續(xù)使用bols那張表作為演示的，思來想去還是正經(jīng)一點。為了演示，我又新增了一張表為autoincre_test，表示id設(shè)置為主鍵且自增長，存儲引擎選擇InnoDB。然后插入了3條數(shù)據(jù)進行演示。查詢當前線程最后插入數(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;語句強制設(shè)置自動增長列的初始值，默認從1開始，但該強制的默認值是保留在內(nèi)存中的，如果使用該值之前數(shù)據(jù)庫重新啟動，強制默認值則會丟失，就需要重新設(shè)置，畢竟使用內(nèi)存沒有加載到磁盤中。

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

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

2.2.2、外鍵約束

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

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

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

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

• restrict
• cascade
• set null和no action

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

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

set foreign_key_checks=0;

執(zhí)行完導入數(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)建以及刪除，因為貼太多的SQL語句太占篇幅了。可以到MySQL官網(wǎng)下載world和sakila數(shù)據(jù)庫進行測試。

2.2.3、存儲方式

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

• 共享表空間存儲
• 多表空間存儲

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

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

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

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

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

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

梳理一下InnoDB存儲引擎的要點，如下圖1-2-2-2所示：

關(guān)于InnoDB存儲引擎就介紹到此處了，更多詳情可以參考MySQL的官方文檔。是不是發(fā)現(xiàn)了我只在MyISAM和InnoDB存儲引擎做了總結(jié)的思維導圖。沒錯，只做了這兩個，因為這倆最常用。至于為啥是粉色背景，因為老夫有一顆少女心！

2.3、MEMORY存儲引擎

MEMORY存儲引擎使用存在與內(nèi)存中的內(nèi)容來創(chuàng)建表。每個MEMORY表只對應(yīng)一個磁盤文件，格式是.frm。MEMORY類型的表訪問速度極快，存在內(nèi)存中當然快。這就是Redis為什么這么快？不僅小？還能持久？咱回到正題，MEMORY存在內(nèi)存中并默認使用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默認使用的索引是hash索引，但是你可以手動指定索引類型。例如默認手動指定使用關(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ù)的時候，我們可以使用**–init-file選項，將insert into … select或者load data infile**這樣的語句存放在這個指定的文件中，就可以在服務(wù)啟動時從持久穩(wěn)固的數(shù)據(jù)源裝載表。

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

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

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

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

2.4、MERGE存儲引擎

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

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

• .frm文件存儲表定義；
• .mrg文件包含組合表的信息，包含表組成、插入數(shù)據(jù)依據(jù)。

可以通過修改.mrg文件來修改表，但修改后需要使用flush tables刷新。測試可以先創(chuàng)建兩張存儲引擎為MyISAM的表，再建一張存儲引擎為MERGE存儲引擎的表。如下所示創(chuàng)建demo為總表指定引擎為MERGE，demo01和demo02為分表：

CREATE TABLE `merge_demo` (   `ID` INT(11) NOT NULL AUTO_INCREMENT,`NAME` VARCHAR(16) COLLATE utf8_bin NOT NULL,   PRIMARY KEY (`ID`)) ENGINE=MERGE UNION=(merge_demo01,merge_demo02)  INSERT_METHOD=LAST DEFAULT CHARSET=utf8 COLLATE=utf8_bin   CREATE TABLE `merge_demo01` (   `ID` INT(11) NOT NULL AUTO_INCREMENT,`NAME` VARCHAR(16) COLLATE utf8_bin NOT NULL,   PRIMARY KEY (`ID`)) ENGINE=MYISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin   CREATE TABLE `merge_demo02` (   `ID` INT(11) NOT NULL AUTO_INCREMENT,`NAME` VARCHAR(16) COLLATE utf8_bin NOT NULL,   PRIMARY KEY (`ID`)) ENGINE=MYISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin

通過插入數(shù)據(jù)驗證MERGE確實是一個MyISAM的組合，就是這么神奇。如下所示，只對demo01和demo02進行插入：

INSERT INTO study.`merge_demo01` VALUES(1,'demo01'); INSERT INTO study.`merge_demo02` VALUES(1,'demo02'); mysql [study]> select * from merge_demo; +----+--------+ | ID | NAME   | +----+--------+ |  1 | demo01 | |  1 | demo02 | +----+--------+ 2 rows in set (0.000 sec)

插入完數(shù)據(jù)，分別查看demo01和demo02各只有一條數(shù)據(jù)，總表可以看到倆分表的全部數(shù)據(jù)。關(guān)鍵是指定了insert_method=last。MERGE表和分區(qū)表的區(qū)別，MERGE并不能智能地將記錄插入到對應(yīng)表中，而分區(qū)表可以做到。通常我們使用MERGE表來透明的對多個表進行查詢和更新操作。可以自己在下面測試總表插入數(shù)據(jù)，看分表的情況，我這里就不貼代碼了。

關(guān)于MySQL自帶的幾款常用存儲引擎就介紹到此，感興趣的可以私下測試驗證，更多參考請到官網(wǎng)獲取API或者DOC文檔。

除了MySQL自帶的一些存儲引擎之外，常見優(yōu)秀的第三方存儲引擎有TokuDB，一款開源的高性能存儲引擎，適用于MySQL和MariaDB。更多詳情可以去TokuDB官網(wǎng)了解喲。

2.5、修改表的存儲引擎

創(chuàng)建新表時，如果不指定存儲引擎，系統(tǒng)會使用默認存儲引擎。在MySQL5.5之前默認的存儲引擎為MyISAM，在MySQL5.5之后默認的存儲引擎為InnoDB。如果想修改默認存儲引擎，可以通過配置文件指定default-table-type的參數(shù)。關(guān)于存儲引擎的查看，在上面介紹存儲引擎的時候已經(jīng)有說明了。

方法一：建表即指定當前表的存儲引擎

在創(chuàng)建tolove表的時候就指定存儲引擎，例如指定存儲引擎為MyISAM，默認編碼為utf8：

-- Create TableCREATE TABLE `tolove` (   `ID` int(11) NOT NULL AUTO_INCREMENT,`GIRL_NAME` varchar(64) COLLATE utf8_bin DEFAULT NULL,   `GIRL_AGE` varchar(64) COLLATE utf8_bin DEFAULT NULL,`CUP_SIZE` varchar(10) COLLATE utf8_bin DEFAULT NULL,   PRIMARY KEY (`ID`)) ENGINE=MyISAM AUTO_INCREMENT=20000001 DEFAULT CHARSET=utf8 COLLATE=utf8_bin

測試生成的數(shù)據(jù)量比較大，隨機生成了1千萬條數(shù)據(jù)。查詢（select）業(yè)務(wù)相對較多，在建表的時候就指定默認存儲引擎MyISAM，統(tǒng)計（count）的效率很高。以我的渣渣電腦，使用INNODB存儲引擎，統(tǒng)計一次需要2~3秒左右。在上面講到MYISAM的時候，已經(jīng)將查詢時間進行過對比。

方法二：使用alter table修改當前表的存儲引擎

修改創(chuàng)建的tolove表為MYISAM引擎進行測試。

-- 修改創(chuàng)建的tolove表為MYISAM引擎進行測試ALTER TABLE test.`tolove` ENGINE=MYISAM;

修改test表的存儲引擎為INNODB進行測試。

-- 修改表的存儲引擎為INNODB進行測試ALTER TABLE test.`test` ENGINE=INNODB;

SHOW CREATE TABLE查詢表的存儲引擎，分別查詢test表和tolove表，在講存儲引擎為MyISAM的時候，有演示過喲！

SHOW CREATE TABLE test.`test`;SHOW CREATE TABLE test.`tolove`;

如果在工具中無法看全，可以導出成xml、csv、html等查詢，以下是我查詢出自己創(chuàng)建表時設(shè)置的存儲引擎為InnoDB：

-- 顯示出我創(chuàng)建的test表的SQL語句存儲引擎為InnoDB CREATE TABLE `test` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `STU_NAME` varchar(50) NOT NULL, `SCORE` int(11) NOT NULL, `CREATETIME` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY (`ID`) ) ENGINE=InnoDB AUTO_INCREMENT=20000001 DEFAULT CHARSET=utf8 -- 顯示出我創(chuàng)建的tolove表的SQL語句，存儲引擎為MyISAM CREATE TABLE `tolove` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `GIRL_NAME` varchar(64) COLLATE utf8_bin DEFAULT NULL, `GIRL_AGE` varchar(64) COLLATE utf8_bin DEFAULT NULL, `CUP_SIZE` varchar(10) COLLATE utf8_bin DEFAULT NULL, PRIMARY KEY (`ID`) ) ENGINE=MyISAM AUTO_INCREMENT=20000001 DEFAULT CHARSET=utf8 COLLATE=utf8_bin

存儲引擎的修改就介紹這么多，看到我的自增長列（AUTO_INCREMENT）ID到了20000001，之前隨機生成過一次1kw條數(shù)據(jù)喲！有一部分解釋說明我寫在了代碼塊中，看起來更加舒服。

3、存儲引擎的選擇

在選擇合適的存儲引擎時，應(yīng)根據(jù)應(yīng)用特點選擇合適的存儲引擎。對于復(fù)雜的應(yīng)用系統(tǒng)，你可以選擇多種存儲引擎滿足不同的應(yīng)用場景需求。如何選擇合適的存儲引擎呢？存儲引擎的選擇真的很重要嗎？

確實應(yīng)該好好思考，在并不復(fù)雜的應(yīng)用場景下，可能MyISAM存儲引擎就能滿足日常開銷。或許在另外一種場景之下InnoDB才是最佳選擇，綜合性能更好，滿足更多需求。

MyISAM是MySQL的默認的插件式存儲引擎，是MySQL在5.5之前的默認存儲引擎。如果應(yīng)用以讀和插入操作居多，只有很少的更新和刪除操作，對事務(wù)完整性、并發(fā)性沒有很高的需求，此時首選是MyISAM存儲引擎。在web和數(shù)據(jù)倉庫最常用的存儲引擎之一。

InnoDB用于事務(wù)處理應(yīng)用程序，并且支持外鍵。是MySQL在5.5之后的默認存儲引擎，同樣也是MariaDB在10.2之后的默認存儲引擎，足見InnoDB的優(yōu)秀之處。如果應(yīng)用對事務(wù)完整性有較高的要求，在并發(fā)情況下要求數(shù)據(jù)高度一致性。數(shù)據(jù)除了插入和查詢以外，還包括很多的更新和刪除操作，那么InnoDB應(yīng)該是比較合適的存儲引擎選擇。InnoDB除了有效地降低由于刪除和更新導致的鎖定，還可以確保事務(wù)的完整提交（commit）、回滾（rollback）。對類似計費系統(tǒng)或者財務(wù)系統(tǒng)等對數(shù)據(jù)準確性要求比較高的系統(tǒng)，InnoDB也是合適的選擇。插點題外話，本人在工作中使用Oracle數(shù)據(jù)庫也有一段時間，Oracle的事務(wù)確實很強大，處理大數(shù)據(jù)壓力很強。

MEMORY存儲引擎將所有的數(shù)據(jù)存在RAM中，在需要快速定位記錄和其它類似數(shù)據(jù)的環(huán)境下，可提供極快的訪問。MEMORY的缺陷在于對表的大小有限制，太大的表無法緩存在內(nèi)存中，其次是要確保表的數(shù)據(jù)可以恢復(fù)，數(shù)據(jù)庫異常重啟后表的數(shù)據(jù)是可恢復(fù)的。MEMORY表通常用于更新不太頻繁的小表，快速定位訪問結(jié)果。

MERGE用于將一組等同的MyISAM存儲引擎的表以邏輯方式組合在一起，并作為一個對象應(yīng)用它們。MERGE表的優(yōu)點在于可以突破對單個MyISAM表大小的限制，并通過將不同的表分布在多個磁盤上，改善MERGE表的訪問效率。對數(shù)據(jù)局倉庫等VLDB環(huán)境很適合。

最后，關(guān)于存儲引擎的選擇都是根據(jù)別人實際經(jīng)驗去總結(jié)的。并不是一定契合你的應(yīng)用場景，最終需要用戶對各自應(yīng)用進行測試，通過測試來獲取最合適的結(jié)果。就像我開始列舉的示例，數(shù)據(jù)量很龐大，對查詢和插入業(yè)務(wù)比較頻繁，我就開始對MyISAM存儲引擎進行測試，確實比較符合我的應(yīng)用場景。

關(guān)于存儲引擎的選擇，總結(jié)簡化如下圖1-3：

4、表的優(yōu)化（碎片整理）

在開始介紹存MyISAM和InnoDB儲引擎的時候，我也展示過存儲大量數(shù)據(jù)所占的磁盤空間。使用OPTIMIZE TABLE來優(yōu)化test數(shù)據(jù)庫下的test表，優(yōu)化之前，這張表占據(jù)磁盤空間大概在824M；通過優(yōu)化之后，有明顯的改善，系統(tǒng)回收了沒有利用的空間，test表所耗磁盤空間明顯下降，優(yōu)化之后只有456M。這里就不貼磁盤所占空間的截圖了。

OPTIMIZE TABLE test.`test`;

優(yōu)化之后，統(tǒng)計（count）數(shù)據(jù)效率也有所提升，大概在2.5sec左右：

mysql [test]> select count(*) from test; -- 使用的是innodb存儲引擎測試 +----------+ | count(*) | +----------+ | 10000000 | +----------+ 1 row in set (2.468 sec)

優(yōu)化之前，統(tǒng)計數(shù)據(jù)大概在3.080 sec。經(jīng)過對比，效率提升是可觀的。

你也可以使用explain執(zhí)行計劃對查詢語句進行優(yōu)化。關(guān)于MySQL優(yōu)化方面的知識，并不是本文的重點，就不做過多描述。

二、索引設(shè)計與使用

1、索引簡介

在涉及到MySQL的面試當中，會提到最左前綴索引，都被玩成梗了。

MySQL所有列類型都可以被索引，對相關(guān)列合理的使用索引是提高查詢（select）操作性能的最佳方法。根據(jù)引擎可以定義每張表的最大索引數(shù)和最大索引長度，MySQL的每種存儲引擎（MyISAM、InnoDB等等）對每張表至少支持16個索引，總索引長度至少為256字節(jié)。大多數(shù)存儲引擎有更高的限制。

MyISAM和InnoDB存儲引擎默認創(chuàng)建的表都是BTREE索引。在MySQL8.0之前是不只支持函數(shù)索引的，MySQL5.7推出了虛擬列功能，在MySQL8.0開始支持函數(shù)索引，也是8.0版本的新特性之一。

MySQL支持前綴索引，對索引字段的前N個字符創(chuàng)建索引，前綴索引長度和存儲引擎有關(guān)。有很多人經(jīng)常會問到，MySQL支持全文索引嗎？我的回答是：支持。MySQL5.6之前MyISAM存儲引擎支持全文索引（FULLTEXT），5.6之后InnoDB開始支持全文索引。

為test表創(chuàng)建10個字節(jié)的前綴索引，創(chuàng)建索引的語法如下：

CREATE INDEX girl_name ON table_name(test(10));

同樣可以使用alter table語句去新增索引，給girl表的字段girl_name新增一個索引：

ALTER TABLE test.`girl` ADD INDEX idx_girlname(girl_name);

對于使用索引的驗證可以使用explain執(zhí)行計劃去判斷。關(guān)于索引的簡述就介紹這么多，更多基礎(chǔ)知識可以參考官方文檔或者權(quán)威書籍。

2、設(shè)計索引原則

索引的設(shè)計可以遵循一些已有的原則，創(chuàng)建索引的時候請盡量考慮符合這些原則。有助于提升索引的使用效率。

搜索的索引列，不一定是所要選擇的列。最合適的索引列，往往是出現(xiàn)在where子句中的列，或者是連接子句中指定的列，而不是出現(xiàn)在select后選擇列表中的列。

使用唯一索引。考慮某列中值的分布，索引列的基數(shù)越大，索引效果越好。

使用短索引。如果對字符串列進行索引，應(yīng)指定一個前綴長度。比如char(100)，思考一下，重復(fù)度的問題。是全部索引來的快，還是對部分字符進行索引更優(yōu)？

利用最左前綴。在創(chuàng)建一個N列的索引時，實際上是創(chuàng)建了MySQL可利用的N個索引。多列索引可以起幾個索引的作用，利用索引中最左邊的列表來匹配行。這樣的列集稱為最左前綴。都快被涉及到MySQL的面試玩成梗了，哈哈。

注意不要過度使用索引。不要以為使用索引好處多多，就在所有的列上全部使用索引，過度使用索引反而會適得其反。每個額外的索引會占用磁盤空間，對磁盤寫操作性能造成損耗。在重構(gòu)的時候，索引也得更新，造成不必要的時間浪費。

InnoDB存儲引擎的表。對于使用InnoDB存儲引擎的表，記錄默認按一定的順序保存。有如下幾種情況：

• 如果有明確定義的主鍵，則遵循主鍵順序保存；
• 在沒有主鍵，但有唯一索引的情況下，會遵循唯一索引順序保存；
• 既沒有主鍵又沒有唯一索引，表中會自動生成一個內(nèi)部列，并遵循這個列的順序保存。