技術文章 » MySQL你必須要了解儲存引擎

MySQL你必須要了解儲存引擎

2022-03-03 19:00:27
本篇文章給大家帶來了關於的相關知識，其中主要介紹了儲存引擎的相關問題，還包括了索引設計與使用、資料型別選擇以及字元集設定等問題，希望對大家有幫助。

推薦學習：

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

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

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

今天發現了一個神奇的引數：-site:xxxx.net

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

1、儲存引擎的介紹

與到多數關係型資料庫的區別在於MySQL有一個儲存引擎的概念，針對不同的儲存需求可以選擇最合適的儲存引擎。MySQL中的外掛式的儲存引擎是其一大特色，使用者可以根據應用的需求選擇如何儲存、是否索引，是否使用事務。嘿嘿，你也可以根據業務環境去適配最適合自己業務的儲存引擎。

Oracle從中嗅到了商機，收購了MySQL，從此有了企業版（商業支援）。社群版依舊可以免費下載。另一大魅力也是因為開源，社群高度活躍，人人都可貢獻。接下來介紹幾種使用比較多的儲存引擎，儲存引擎並無優劣之分，有的只是誰更適合對應的生產業務環境。

MySQL5.0中支援的儲存引擎有FEDERATED、MRG_MYISAM、MyISAM、BLACKHOLE、CSV、MEMORY、ARCHIVE、NDB Cluster、BDB、EXAMPLE、InnoDB（MySQL5.5以及MariaDB10.2之後的預設儲存引擎）、PERFORMANCE_SCHEMA（非常規儲存資料引擎）。下面給出MySQL與MariaDB支援的記憶體引擎的對比，可以看出MariaDB新增了Aria引擎：

檢視儲存引擎

通過MySQL登入自帶的字元介面輸入show engines\G;或者使用支援MySQL查詢的工具SQLyog、phpMyAdmin、MySQL workbench等查詢支援的引擎，這裡只展示部分喲：

[test@cnwangk ~]$ mysql -uroot -p
Enter password: 
mysql> show engines\G;*************************** 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：當前版本資料庫是否支援該儲存引擎，YES：支援、NO：不支援；Supports transactions, row-level locking, and foreign keys，個人字面上翻譯這段話：支援事務、行級別鎖和外來鍵；
  • Comment：對該儲存引擎的詳情描述，比如描述該引擎否支援事務和外來鍵；
  • Transactions：對該儲存引擎是否支援事務的描述，YES：支援、NO：不支援；
  • XA：是否滿足XA規範。XA規範是開放群組關於分散式事務處理(DTP)的規範。YES：支援、NO：不支援；
  • Savepoints：字面意思是儲存點，對事物控制是否支援，YES：支援、NO：不支援。

小聲嗶嗶，如果你能閱讀明白官方的一些英文檔案，這將有助於你對MySQL儲存引擎的進一步理解，養成閱讀原始碼或者檔案的能力。

順帶的提一下MySQL的妹妹MariaDB。在MySQL的復刻版本MariaDB中10.2之前使用的自帶的新引擎Aria，在MariaDB10.2之後使用的預設儲存引擎也是InnoDB，足以看出InnoDB儲存引擎的優秀之處。MariaDB的API和協定相容MySQL，另外又新增了一些功能，以支援原生的非阻塞操作和進度報告。這意味著，所有使用MySQL的聯結器、程式庫和應用程式也將可以在MariaDB下工作。在此基礎上，由於擔心甲骨文MySQL的一個更加封閉的軟體專案，Fedora等Linux發行版已經在最新版本中以MariaDB取代MySQL，維基媒體基金會的伺服器同樣也使用MariaDB取代了MySQL。

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

  • MyISAM
  • InnoDB
  • MEMORY
  • MERGE

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

2、部分儲存引擎的特性

儲存引擎/支援特性儲存限制事務安全鎖機制B樹索引雜湊索引全文索引叢集索引資料快取索引快取資料可壓縮空間使用記憶體使用批次插入速度外來鍵支援
MyISAM有
表鎖支援
支援

支援支援低低高
InnoDB64TB支援行鎖支援
支援（5.6）支援支援支援
高高低支援
MEMORY有
表鎖支援支援

支援支援
N/A中等高
MERGE沒有
表鎖支援



支援
低低高
NDB有
行鎖支援


支援支援
低高高

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

2.1、MyISAM儲存引擎

MyISAM是MySQL5.5之前預設的儲存引擎。MyISAM不支援事務、不支援外來鍵。優勢在於存取速度快，對事務完整性沒有特殊要求或者以select和insert為主的應用基本上可以使用MyISAM作為儲存引擎建立表。我們先弄個例子出來演示，事先準備了一張資料千萬級別的表，看看這個儲存引擎的特性：

我已經建立好了資料庫為test，在test中分別建立了兩張表test和tolove。test表在建立的時候指定預設儲存引擎為MyISAM，tolove表指定儲存引擎為InnoDB。
使用MyISAM儲存引擎建立的表tolove，查詢儲存有1kw資料的表tolove。

tips：你可以使用use test，切換到test資料庫，就不用像我這樣查詢tolove表去指定test資料庫了喲！

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

再看演示使用InnoDB儲存引擎建立的表test，同樣為了演示，事先隨機生成了1kw條資料。

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

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

MyISAM在磁碟中儲存的檔案：

每個MyISAM在磁碟上儲存成3個檔案，其檔名和表名都相同，擴充套件名分別是：

  • .frm：儲存表定義；
  • .MYD：MYData，儲存資料；
  • .MYI：MYindex，儲存索引。

資料檔案和索引檔案可以存放在不同的目錄，平均分佈IO，獲得更快的速度，提升效能。需要指定索引檔案和資料檔案儲存的路徑，建立表時通過DATA DIRECTORY和INDEX DIRECTORY引數指定，表明不同MyISAM表的索引檔案和資料檔案可以存放在不同的路徑下。當然，需要給予該路徑的存取許可權。

MyISAM損壞處理方式 ：

MyISAM型別的表可能會損壞，原因多種多樣。損壞後的表有可能不能被存取，會提示需要修復或者存取後提示返回錯誤結果。MyISAM型別的表，可以通過提供的修復工具執行CHECK TABLE語句檢查MyISAM表的健康程度，使用REPAIR TABLE語句修復一個損壞的表。表損壞可能會導致資料庫異常重新啟動，需要儘快修復並確定原因好做應對策略。

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

  • 靜態表，固定長度；
  • 動態表
  • 壓縮表

靜態表是MyISAM儲存引擎的預設儲存格式，欄位長度是定長，記錄都是固定長度。優勢在於儲存迅速、容易快取、出現故障易恢復；缺點是相對耗儲存空間。需要注意的是：如需儲存內容後面的空格，預設返回結果會去掉後面的空格。

動態表包含變長欄位，記錄不是固定長度，儲存優勢：佔用空間相對較小、但頻繁刪除和更新記錄會產生碎片。這時，需要定期執行optimize table語句或者myisamchk -r命令來改善效能，出現故障恢復相對較難。

壓縮表由mysiampack工具建立，佔用磁碟空間很小。因為每個記錄是被單獨壓縮，所以存取開始非常小。

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

順帶安利一波，前段時間發現WPS也能夠製作精美的思維導圖，並且支援一鍵匯入到doc檔案中。普通使用者最多可儲存150個檔案。之前也用過XMind、processon、gitmind等等，現在使用WPS更方便了。

2.2、InnoDB儲存引擎

優點與缺點：InnoDB儲存引擎提供了具有提交（commit）、回滾（rollback）和崩潰恢復能力的事務安全。但對比MyISAM儲存引擎，InnoDB寫的處理效率相對差一些，並且會佔用更多的磁碟空間保留資料和索引。下圖是我儲存了1kw條資料的表，並且使用的是InnoDB儲存引擎。student01表同樣使用了InnoDB儲存引擎，儲存資料為100w條。從下圖可以看出儲存資料索引在.ibd檔案中、表結構則存在.frm檔案中。

2.2.1、自動增長列

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

本來想繼續使用bols那張表作為演示的，思來想去還是正經一點。為了演示，我又新增了一張表為autoincre_test，表示id設定為主鍵且自增長，儲存引擎選擇InnoDB。然後插入了3條資料進行演示。查詢當前執行緒最後插入資料的記錄使用值：

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;語句強制設定自動增長列的初始值，預設從1開始，但該強制的預設值是保留在記憶體中的，如果使用該值之前資料庫重新啟動，強制預設值則會丟失，就需要重新設定，畢竟使用記憶體沒有載入到磁碟中。

通過上面的演示，你會發現插入記錄是0或者空時，實際插入的將是自動增長後的值。通過last_insert_id()函數可以查詢當前執行緒最後插入資料的記錄使用值。如果一次插入多條記錄，則返回的是第一條記錄使用的自動增長值，這裡就不演示插入多條資料了。記住一點，可以使用last_insert_id()去查詢id記錄值。

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

2.2.2、外來鍵約束

在MySQL中，目前支援外來鍵約束的儲存引擎只有InnoDB。建立外來鍵的時候，要求父表必須有對應的索引。子表建立外來鍵的時候，也會自動建立對應的索引。下面將通過範例進行講解。可以從MySQL官網下載範例資料庫world和sakila進行參考。

  • city表，FOREIGN KEY (CountryCode) REFERENCES country (Code)
  • country表
  • countrylanguage表，FOREIGN KEY (CountryCode) REFERENCES country (Code)

通過MySQL workbench或者Navicat逆向生成物理模型進行參考，更加直觀。插一句，在MySQL的官網同樣有一個sakila資料庫是關於演員電影的，也提供了sakila的ERR物理模型圖，這句話做了超連結，可以直接存取。給出我之前逆向生成的world資料庫的物理模型：

在建立索引時，可以指定在刪除、更新父表時，對子表進行的相應操作包含：

  • restrict
  • cascade
  • set null和no action

其中restrict和no action相同，restrict限制在子表有關聯記錄的情況下父表無法更新；cascade表示在父表更新或刪除的時候，級聯更新或者刪除子表對應記錄；set null表示在父表更新或刪除的時候，子表的對應欄位被set null。選擇cascade以及set null時需要謹慎操作，有可能導致資料丟失。

在匯入多個表的資料時，如果忽略表之前的匯入順序，可以暫時關閉外來鍵檢查；同樣執行load data和alter table時也可以暫時關閉外來鍵檢查加快處理的速度，提升效率。關閉外來鍵檢查的命令為：

set foreign_key_checks=0;

執行完匯入資料或者修改表的操作後，通過開啟外來鍵檢查命令改回來：

set foreign_key_checks=1;

對於InnoDB型別的表，外來鍵資訊可以通過show create table或者show table status檢視。比如查詢world資料庫中的city表：

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

關於外來鍵約束就提這麼多，沒有演示建立以及刪除，因為貼太多的SQL語句太佔篇幅了。可以到MySQL官網下載world和sakila資料庫進行測試。

2.2.3、儲存方式

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

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

使用共用表空間儲存，這種方式建立的表的表結構儲存在.frm檔案中，資料和索引儲存在innodb_data_home_dir和innodb_data_file_path定義的表空間中，可以是多個檔案。在開頭介紹InnoDB儲存引擎時也提到過檔案儲存位置。

使用多表空間儲存，這種方式建立的表的表結構仍然儲存在.frm檔案中，但每個表的資料和索引單獨儲存在.ibd檔案中。如果是個分割區表，則每個分割區對應單獨的.ibd檔案，檔名為表名+分割區名。可以在建立分割區的時候指定每個分割區的資料檔案位置，以此來平均分佈磁碟的IO，達到緩解磁碟壓力的目的。如下是在Windows下使用InnoDB儲存了海量資料的檔案：

使用多表空間儲存需要設定引數innodb_file_per_table，重新啟動資料庫伺服器才能生效喲。多表空間的引數生效後，只對新建的表生效。多表空間的資料檔案無大小限制，無需設定初始大小，也不需設定檔案的最大限制與擴充套件大小等引數。使用多表空間儲存優勢在於方便單表備份和恢復操作。雖然不能直接複製.frm和.ibd檔案達到目的，但可以使用如下命令操作：

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

將備份恢復到資料庫中，單表備份，只能恢復到原來所在的資料庫中，無法恢復到其它資料庫中。如過需要將單表恢復至其它目標資料庫中，則需要通過mysqldump和mysqlimport來實現。

注意：即便多表儲存更有優勢，但是共用表儲存空間依舊是必須的，InnoDB將內部資料字典和線上重做紀錄檔存在這個檔案中。

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

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

2.3、MEMORY儲存引擎

MEMORY儲存引擎使用存在與記憶體中的內容來建立表。每個MEMORY表只對應一個磁碟檔案，格式是.frm。MEMORY型別的表存取速度極快，存在記憶體中當然快。這就是Redis為什麼這麼快？不僅小？還能持久？咱回到正題，MEMORY存在記憶體中並預設使用hash索引，一旦服務關閉，表中資料會丟失。建立一張名為GIRLS的表指定儲存引擎為MEMORY，注意了在UNIX和Linux作業系統下，是對欄位和表名大小是寫敏感的，關鍵字不影響。

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索引，但是你可以手動指定索引型別。例如預設手動指定使用關鍵字USING HASH：

-- 建立索引指定索引型別為hash。create index mem_hash USING HASH on GIRLS(ID);-- 查詢索引型別，簡化了一下，只展示了部分引數。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容易丟失資料，但是在啟動MySQL服務的時候，我們可以使用**–init-file選項，將insert into … select或者load data infile**這樣的語句存放在這個指定的檔案中，就可以在服務啟動時從持久穩固的資料來源裝載表。

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

每個MEMORY表中存放的資料量大小，受max_heap_table_size系統變數約束，初始值為16MB，可以根據需求調整。通過max_rows可以指定表的最大行數。

MEMORY儲存引擎最大特色是快，主要用於內容變化不頻繁的程式碼錶，或者是為了做統計提供的中間表，效率更高。使用MEMORY時需謹慎，萬一忘了這廝重新啟動資料就沒了就尷尬了。所以在使用時，考慮好重新啟動伺服器後如何取得資料。

關於MEMORY儲存引擎就介紹到這裡，大部分都是些理論知識，更多的需要自己去實踐測試。

2.4、MERGE儲存引擎

MERGE儲存引擎是一組MyISAM表的組合，這些MyISAM表必須結果完全相同，MERGE表本身沒有資料，對MERGE型別的表可以進行查詢、更新、刪除操作，實際上是對內部的MyISAM表進行操作的。對於MERGE型別表的插入操作，通過insert_method子句定義插入的，可以有3個不同的值，使用first或last插入操作對應開始與最後一個表上。如果不定義這個子句，或者定義為NO，表示不能對MERGE表進行操作。

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

  • .frm檔案儲存表定義；
  • .mrg檔案包含組合表的資訊，包含表組成、插入資料依據。

可以通過修改.mrg檔案來修改表，但修改後需要使用flush tables重新整理。測試可以先建立兩張儲存引擎為MyISAM的表，再建一張儲存引擎為MERGE儲存引擎的表。如下所示建立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

通過插入資料驗證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)

插入完資料，分別檢視demo01和demo02各只有一條資料，總表可以看到倆分表的全部資料。關鍵是指定了insert_method=last。MERGE表和分割區表的區別，MERGE並不能智慧地將記錄插入到對應表中，而分割區表可以做到。通常我們使用MERGE表來透明的對多個表進行查詢和更新操作。可以自己在下面測試總表插入資料，看分表的情況，我這裡就不貼程式碼了。

關於MySQL自帶的幾款常用儲存引擎就介紹到此，感興趣的可以私下測試驗證，更多參考請到官網獲取API或者DOC檔案。

除了MySQL自帶的一些儲存引擎之外，常見優秀的第三方儲存引擎有TokuDB，一款開源的高效能儲存引擎，適用於MySQL和MariaDB。更多詳情可以去TokuDB官網瞭解喲。

2.5、修改表的儲存引擎

建立新表時，如果不指定儲存引擎，系統會使用預設儲存引擎。在MySQL5.5之前預設的儲存引擎為MyISAM，在MySQL5.5之後預設的儲存引擎為InnoDB。如果想修改預設儲存引擎，可以通過組態檔指定default-table-type的引數。關於儲存引擎的檢視，在上面介紹儲存引擎的時候已經有說明了。

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

在建立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

測試生成的資料量比較大，隨機生成了1千萬條資料。查詢（select）業務相對較多，在建表的時候就指定預設儲存引擎MyISAM，統計（count）的效率很高。以我的渣渣電腦，使用INNODB儲存引擎，統計一次需要2~3秒左右。在上面講到MYISAM的時候，已經將查詢時間進行過對比。

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

修改建立的tolove表為MYISAM引擎進行測試。

-- 修改建立的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等查詢，以下是我查詢出自己建立表時設定的儲存引擎為InnoDB：

-- 顯示出我建立的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
-- 顯示出我建立的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條資料喲！有一部分解釋說明我寫在了程式碼塊中，看起來更加舒服。

3、儲存引擎的選擇

在選擇合適的儲存引擎時，應根據應用特點選擇合適的儲存引擎。對於複雜的應用系統，你可以選擇多種儲存引擎滿足不同的應用場景需求。如何選擇合適的儲存引擎呢？儲存引擎的選擇真的很重要嗎？

確實應該好好思考，在並不複雜的應用場景下，可能MyISAM儲存引擎就能滿足日常開銷。或許在另外一種場景之下InnoDB才是最佳選擇，綜合效能更好，滿足更多需求。

MyISAM是MySQL的預設的外掛式儲存引擎，是MySQL在5.5之前的預設儲存引擎。如果應用以讀和插入操作居多，只有很少的更新和刪除操作，對事務完整性、並行性沒有很高的需求，此時首選是MyISAM儲存引擎。在web和資料倉儲最常用的儲存引擎之一。

InnoDB用於事務處理應用程式，並且支援外來鍵。是MySQL在5.5之後的預設儲存引擎，同樣也是MariaDB在10.2之後的預設儲存引擎，足見InnoDB的優秀之處。如果應用對事務完整性有較高的要求，在並行情況下要求資料高度一致性。資料除了插入和查詢以外，還包括很多的更新和刪除操作，那麼InnoDB應該是比較合適的儲存引擎選擇。InnoDB除了有效地降低由於刪除和更新導致的鎖定，還可以確保事務的完整提交（commit）、回滾（rollback）。對類似計費系統或者財務系統等對資料準確性要求比較高的系統，InnoDB也是合適的選擇。插點題外話，本人在工作中使用Oracle資料庫也有一段時間，Oracle的事務確實很強大，處理巨量資料壓力很強。

MEMORY儲存引擎將所有的資料存在RAM中，在需要快速定位記錄和其它類似資料的環境下，可提供極快的存取。MEMORY的缺陷在於對錶的大小有限制，太大的表無法快取在記憶體中，其次是要確保表的資料可以恢復，資料庫異常重新啟動後表的資料是可恢復的。MEMORY表通常用於更新不太頻繁的小表，快速定位存取結果。

MERGE用於將一組等同的MyISAM儲存引擎的表以邏輯方式組合在一起，並作為一個物件應用它們。MERGE表的優點在於可以突破對單個MyISAM表大小的限制，並通過將不同的表分佈在多個磁碟上，改善MERGE表的存取效率。對資料局倉庫等VLDB環境很適合。

最後，關於儲存引擎的選擇都是根據別人實際經驗去總結的。並不是一定契合你的應用場景，最終需要使用者對各自應用進行測試，通過測試來獲取最合適的結果。就像我開始列舉的範例，資料量很龐大，對查詢和插入業務比較頻繁，我就開始對MyISAM儲存引擎進行測試，確實比較符合我的應用場景。

關於儲存引擎的選擇，總結簡化如下圖1-3：

4、表的優化（碎片整理）

在開始介紹存MyISAM和InnoDB儲引擎的時候，我也展示過儲存大量資料所佔的磁碟空間。使用OPTIMIZE TABLE來優化test資料庫下的test表，優化之前，這張表佔據磁碟空間大概在824M；通過優化之後，有明顯的改善，系統回收了沒有利用的空間，test表所耗磁碟空間明顯下降，優化之後只有456M。這裡就不貼磁碟所佔空間的截圖了。

OPTIMIZE TABLE test.`test`;

優化之後，統計（count）資料效率也有所提升，大概在2.5sec左右：

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

優化之前，統計資料大概在3.080 sec。經過對比，效率提升是可觀的。

你也可以使用explain執行計劃對查詢語句進行優化。關於MySQL優化方面的知識，並不是本文的重點，就不做過多描述。

二、索引設計與使用

1、索引簡介

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

MySQL所有列型別都可以被索引，對相關列合理的使用索引是提高查詢（select）操作效能的最佳方法。根據引擎可以定義每張表的最大索引數和最大索引長度，MySQL的每種儲存引擎（MyISAM、InnoDB等等）對每張表至少支援16個索引，總索引長度至少為256位元組。大多數儲存引擎有更高的限制。

MyISAM和InnoDB儲存引擎預設建立的表都是BTREE索引。在MySQL8.0之前是不只支援函數索引的，MySQL5.7推出了虛擬列功能，在MySQL8.0開始支援函數索引，也是8.0版本的新特性之一。

MySQL支援字首索引，對索引欄位的前N個字元建立索引，字首索引長度和儲存引擎有關。有很多人經常會問到，MySQL支援全文索引嗎？我的回答是：支援。MySQL5.6之前MyISAM儲存引擎支援全文索引（FULLTEXT），5.6之後InnoDB開始支援全文索引。

為test表建立10個位元組的字首索引，建立索引的語法如下：

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執行計劃去判斷。關於索引的簡述就介紹這麼多，更多基礎知識可以參考官方檔案或者權威書籍。

2、設計索引原則

索引的設計可以遵循一些已有的原則，建立索引的時候請儘量考慮符合這些原則。有助於提升索引的使用效率。

搜尋的索引列，不一定是所要選擇的列。最合適的索引列，往往是出現在where子句中的列，或者是連線子句中指定的列，而不是出現在select後選擇列表中的列。

使用唯一索引。考慮某列中值的分佈，索引列的基數越大，索引效果越好。

使用短索引。如果對字串列進行索引，應指定一個字首長度。比如char(100)，思考一下，重複度的問題。是全部索引來的快，還是對部分字元進行索引更優？

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

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

InnoDB儲存引擎的表。對於使用InnoDB儲存引擎的表，記錄預設按一定的順序儲存。有如下幾種情況：

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

以上就是對索引設計原則的簡單介紹。

3、B-TREE與HASH索引

使用這些索引時，應該考慮索引是否當前使用條件下生效！在使用MEMORY儲存引擎的表中可以選擇使用HASH索引或者B-TREE索引，兩種不同的索引有其各自適用的範圍。

HASH索引。只用於這類關係操作符：=、<=>的操作比較，優化器不能使用HASH索引來加速order by操作。MySQL不能確定在兩個值之間大約有多少行。

B-TREE索引。對於B-TREE索引，使用>、<、>=、<=、BETWEEN、!=或者<>、亦或是使用like ‘condition’。其中’condition’不以萬用字元開始的操作符時，都可以使用相關列上的索引。

關於索引就介紹到這裡。合理的使用索引將有助於提升效率，但並不是使用的索引越多越好。

三、資料型別選擇

  • 字串型別char與varchar
  • 浮點數和定點數
  • 日期型別

工作中，個人使用經驗。Oracle裡面使用BLOB儲存大欄位比較頻繁，TEXT相對少見，使用VARCHAR2型別比較多。但在MySQL中是不支援VARCHAR2型別的。

1、CHAR與VARCHAR

char和varchar型別類似，用於儲存字串，但它們儲存和檢索的方式不同。char型別屬於固定長度（定長）型別的字串，varchar屬於可變長度的字串型別。在MySQL的嚴格模式中，使用的char和varchar，超過列長度的值不會被儲存，並且出現錯誤提示。

char優缺點。char是固定長度，處理速度比varchar要快，但缺點是浪費儲存空間，沒有varchar那麼靈活。varchar。隨著MySQL的不斷升級，varchar型別也在不斷優化，效能也在提升，被用於更多的應用中。

MyISAM儲存引擎：建議使用固定長度的資料列代替可變長度的資料列。

InnoDB儲存引擎：建議使用VARCHAR型別。

MEMORY儲存引擎：使用固定長度資料型別儲存。

2、TEXT與BLOB

一般情況，儲存少量的字串時，會選擇char和varchar型別。而在儲存較大文字時，通常選擇TEXT或者BLOB大欄位，二者之間的區別在於BLOB能存二進位制資料，比如：照片，TEXT型別只能存字元資料。這也是為什麼我在開始的時候提及到個人工作中見到BLOB型別相對較多。TEXT和BLOB還包括不同型別：

  • TEXT、LONGTEXT、MEDIUMINT、MEDIUMTEXT、TINYTEXT；
  • BLOB、LONGBLOB、MEDIUMBLOB、TINYBLOB。

區別在於儲存文字長度和位元組不同。

需要注意的點：

  • BLOB和TEXT值會引起一些效能問題，尤其是執行大量刪除操作時；
  • 可以使用合成索引提高大欄位的查詢效能；
  • 在不必要的時候避免檢索大欄位；
  • 將BLOB和TEXT分離到不同的表中。

3、浮點數與定點數

浮點型別一般用於表示含有小數部分的值。列舉一些範例：

  • double型別：用於浮點數（雙精度）；
  • decimal型別：MySQL中表示定點數；
  • float型別：用於浮點數（單精度）。

學過Java語言的同學，對這些浮點型別並不陌生吧。

注意點：浮點數存在誤差問題，對精度比較敏感的資料，避免對浮點型別做比較。

4、日期型別

談到日期型別，又讓我想起了7年前學Java語言的時候，會寫一個工具類（Utils.java），將常用的處理日期的方法寫進去然後呼叫。經常用到的一個方法（SimpleDateFormat），對時間戳進行轉換格式化。

MySQL中常用的日期型別有：

  • DATE
  • DATETIME
  • TIME
  • TIMESTAMP

如果需要記錄年月日時分秒，並且記錄的年份比較久遠，最好用DATETIME，而不要使用TIMESTAMP時間戳。TIMESTAMP表示的範圍比DATETIME短得多。

四、字元集（字元編碼）設定

從本質上來說，計算機只能是被二進位制程式碼（010101）。因此，不論是計算機程式還是處理的資料，最終都會轉換成二進位制程式碼，計算機才能識別。為了讓計算機不僅能做科學計算，也能處理文字資訊，於是計算機字元集誕生了。

字元編碼（英語：Character encoding）、字集碼是把字元集中的字元編碼為指定集合中某一物件)（例如：位元模式、自然數序列、8位元組或者電脈衝），以便文字在計算機中儲存和通過通訊網路的傳遞。常見的例子包括將拉丁字母表編碼成摩斯電碼和ASCII。其中，ASCII將字母、數位和其它符號編號，並用7位元的二進位制來表示這個整數。通常會額外使用一個擴充的位元，以便於以1個位元組的方式儲存。

在計算機技術發展的早期，如ASCII（1963年）和EBCDIC（1964年）這樣的字元集逐漸成為標準。但這些字元集的侷限很快就變得明顯，於是人們開發了許多方法來擴充套件它們。對於支援包括東亞CJK字元家族在內的寫作系統的要求能支援更大量的字元，並且需要一種系統而不是臨時的方法實現這些字元的編碼。

參照自維基百科對字元編碼的介紹。

1、Unicode

Unicode是什麼？是統一編碼，是電腦科學領域的業界標準。從最初的的1.0.0到目前最新的14.0版本，對應ISO/IEC 10646-N:xxxx。說一下UTF-8、UTF-16、UTF-16LE、UTF-32BE、UTF-32LE等等大家應該很熟悉了。

2、常見字元集

常見的字元集：

  • UTF-8：泛用性最廣泛；
  • GBK：對中文支援非常友好，在GB2312基礎上進行了擴充；
  • GB2312：對中文字元集支援，；
  • GB18030：支援中文字元集，解決GBK強制力不夠的問題。

3、MySQL支援的字元集

通過show character set;命令可以檢視MySQL支援的字元集。我只展示部分：

mysql [test]> show character set;
| gbk      | GBK Simplified Chinese      | gbk_chinese_ci      |      2 |
| gb2312   | GB2312 Simplified Chinese   | gb2312_chinese_ci   |      2 |
| utf8     | UTF-8 Unicode               | utf8_general_ci     |      3 |
| utf8mb4  | UTF-8 Unicode               | utf8mb4_general_ci  |      4 |
| utf16    | UTF-16 Unicode              | utf16_general_ci    |      4 |
| utf32    | UTF-32 Unicode              | utf32_general_ci    |      4 |

或者你還可以使用DESC information_schema.CHARACTER_SETS檢視所有字元集和字元集預設的校對規則。

檢視相關字元集校對規則，可以使用SHOW COLLATION配合 LIKE模糊搜尋gbk字元集。

SHOW COLLATION LIKE 'gbk%';

MySQL字元集設定：預設可以過組態檔設定character-set-server引數。

  • Linux發行版中安裝一般在my.cnf中設定；
  • Windows下在my.ini檔案中設定
[mysqld]character-set-server=utf-8
character-set-server=gbk

額外再提一點，判斷字元集所佔位元組，可以使用函數LENGTH()：

SELECT LENGTH('中');

如果使用的是UTF-8編碼，預設漢字是佔用3個位元組，使用GBK則佔用2個位元組。字元編碼就介紹到這裡。

五、MySQL範例資料庫sakila

檢視、儲存過程、函數、觸發器。這裡給出我自己隨機生成海量資料用到的函數和儲存過程。

1、函數

建立函數，使用DELIMITER宣告，使用CREATE FUNCTION建立函數，tolove表的建立在介紹儲存引擎過程中已經有展示過。

/** 建立函數 生成學號 **/DELIMITER $CREATE FUNCTION rand_number() RETURNS INTBEGIN
	DECLARE i INT DEFAULT 0;
	SET i= FLOOR(1+RAND()*100);
	RETURN i;END $DELIMITER $

建立函數：用於生成姓名隨機字串

/** 建立函數 生成姓名隨機字串 **/DELIMITER $CREATE FUNCTION rand_name(n INT) RETURNS VARCHAR(255)BEGIN
	DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
	DECLARE return_str VARCHAR(255) DEFAULT '';
	DECLARE i INT DEFAULT 0;
	WHILE i < n DO
	SET return_str = CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
	SET i = i+1;
	END WHILE;
	RETURN return_str;	END $DELIMITER $

2、儲存過程

建立儲存過程，使用CREATE PROCEDURE建立：

/** 建立儲存過程 **/DELIMITER $CREATE PROCEDURE insert_tolove(IN max_num INT(10))BEGIN
	DECLARE i INT DEFAULT 0;
	DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
	START TRANSACTION;
	WHILE i< max_num DO
	INSERT INTO test.`tolove`(ID,GIRL_NAME,GIRL_AGE,CUP_SIZE) VALUES(NULL,rand_name(5),rand_number(),NULL);
	SET i = i + 1;
	END WHILE;COMMIT;END $DELIMITER $

使用CALL呼叫儲存過程，隨機生成百萬資料：

/** 呼叫儲存過程 **/CALL insert_tolove(100*10000);

刪除函數或者儲存過程，使用DROP關鍵字

-- 刪除函數rand_nameDROP FUNCTION rand_name;
-- 刪除儲存過程insert_toloveDROP PROCEDURE insert_tolove;

3、觸發器

建立觸發器使用CREATE TRIGGER，這裡就參照sakila資料庫範例。如果存在，使用了判斷語句 IF EXISTS，然後刪除DROP TRIGGER已經存在的觸發器。

DELIMITER $$USE `sakila`$$DROP TRIGGER /*!50032 IF EXISTS */ `customer_create_date`$$CREATE
    /*!50017 DEFINER = 'root'@'%' */
    TRIGGER `customer_create_date` BEFORE INSERT ON `customer` 
    FOR EACH ROW SET NEW.create_date = NOW();$$DELIMITER ;

4、sakila資料庫

在文中我反覆提到了MySQL的範例資料庫sakila，是一個完整的學習MySQL的好例子。包含了檢視、儲存過程、函數和觸發器。可以去MySQL的官網獲取SQL指令碼。

文末留一個神祕的引數，通過此種方式可以過濾你不想看到的內容喲！無論在手機端或者PC端都可生效，親測可用。

xxxx(檢索的內容) -site:xxxn.net
-- 或者
xxxx(檢索的內容) -site:xxshu.com

推薦學習：

以上就是MySQL你必須要了解儲存引擎的詳細內容，更多請關注TW511.COM其它相關文章！