MySQL開發篇，儲存引擎的選擇真的很重要嗎？

龍騰萬里sky發表於2022-01-30
MySql儲存引擎

前言

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

開發人員或者是DBA都應該關注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 tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ***************************
      Engine: MyISAM
     Support: YES
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 6. row ***************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 8. row ***************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
9 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 有 表鎖 支援 支援 支援 支援 低 低 高
InnoDB 64TB 支援 行鎖 支援 支援（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: Fixed
1 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 Table
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

測試生成的資料量比較大，隨機生成了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 INT
BEGIN
	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_name
DROP FUNCTION rand_name;
-- 刪除儲存過程insert_tolove
DROP 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

持續更新優化中...

總結

以上就是此次文章的所有內容的，希望能對你的工作有所幫助。感覺寫的好，就拿出你的一鍵三連。在公眾號上更新的可能要快一點，目前還在完善中。能看到這裡的，都是帥哥靚妹。如果感覺總結的不到位，也希望能留下您寶貴的意見，我會在文章中進行調整優化。

原創不易，轉載也請標明出處和作者，尊重原創。不定期上傳到github或者gitee。認準龍騰萬里sky，如果看見其它平臺不是這個ID發出我的文章，就是轉載的。linux系列文章：《初學者如何入門linux，原來linux還可以這樣學》已經上傳至github和gitee。個人github倉庫地址，一般會先更新PDF檔案，然後再上傳markdown檔案。如果訪問github太慢，可以使用gitee進行克隆。

tips：使用hexo搭建的靜態部落格也會定期更新維護。

https://github.com/cnwangk/SQL-study

作者：龍騰萬里sky

相關文章