儲存引擎

什麼是儲存引擎

MySQL 可以分成兩層來看。上層負責解析 SQL、檢查權限、決定查詢怎麼執行； 下層的儲存引擎（storage engine）負責把資料真正寫進硬碟（或記憶體）、建立索引、處理鎖定。 同一個 SQL 敘述交給不同的儲存引擎執行，資料存放的方式、能不能復原、多人同時寫入時的表現都不一樣。

MySQL 的特色是每張表格可以各自選擇儲存引擎，同一個資料庫裡可以同時有 InnoDB 和 MyISAM 的表格。建立表格時用 ENGINE= 指定：

CREATE TABLE asset (
  assetID INT NOT NULL,
  description VARCHAR(255)
) ENGINE=MyISAM;

沒有寫 ENGINE= 時，使用系統變數 default_storage_engine 的值。MySQL 5.5 起預設引擎是 InnoDB，MySQL 8.0 也是。

舊教材的寫法 TYPE=MyISAM 在 MySQL 5.5 已經移除，8.0 執行會出現語法錯誤（ERROR 1064），一律改寫成 ENGINE=MyISAM。

SHOW ENGINES 的欄位與 ALTER TABLE … ENGINE=

列出的引擎依安裝的版本與套件而略有不同；在 MySQL 8.0 只有 InnoDB 的 Transactions 是 YES。

查詢表格的引擎有兩種方式：

SHOW TABLE STATUS LIKE 'asset';          -- Engine 欄位
SELECT TABLE_NAME, ENGINE
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'shop';             -- 一次列出整個資料庫

information_schema 是 MySQL 內建、唯讀的資料庫，裡面的表格描述了「伺服器上有哪些資料庫、表格、欄位」。 本課的直譯器只模擬了它常用的幾張表格；SHOW TABLE STATUS 裡的列數、檔案大小等數字也是簡化的示意值。

ALTER TABLE … ENGINE= 會重建整張表格：MySQL 建立一張新引擎的空表格，把資料逐列複製過去，再取代原本的表格。 表格很大時要花很久，而且新引擎不支援的定義（例如 MEMORY 不能有 TEXT 欄位）會讓轉換失敗。

InnoDB 與 MyISAM

課程原本以 MyISAM 為主（它在 MySQL 5.5 以前是預設引擎），現在幾乎所有新表格都使用 InnoDB：

依主鍵排列的意思是：InnoDB 的資料本身就存放在主鍵索引裡，不管寫入的順序如何，資料列都依主鍵由小到大排好。 沒有定義主鍵時，InnoDB 會用第一個 UNIQUE NOT NULL 的索引，再沒有就自己產生一個隱藏的列編號。

舊投影片提到 MyISAM 的 .frm 檔：MySQL 8.0 把表格定義改存在 InnoDB 裡的資料字典，.frm 檔已經不再使用，MyISAM 改用 .sdi 檔保存表格定義。 mysql 系統資料庫的表格在 8.0 也大多改成了 InnoDB（記錄查詢日誌的 general_log、slow_log 是 CSV 表格）。

MyISAM 目前的用途很少：舊系統留下的表格、只讀不寫的資料。需要交易、外鍵或多人同時寫入時一律使用 InnoDB。

其他儲存引擎

已經不存在的引擎：原課程提到的幾個名稱在 MySQL 8.0 都不能用了。

• ISAM：MyISAM 的前身，MySQL 5.0 移除
• BDB（Berkeley DB）：早期支援交易的引擎，MySQL 5.1 移除，交易功能由 InnoDB 取代
• HEAP：就是 MEMORY 的舊名稱。8.0 仍接受 ENGINE=HEAP 這個寫法，但建立出來的表格顯示為 MEMORY，新程式請直接寫 MEMORY

指定不存在的引擎（例如 ENGINE=BDB）會出現 ERROR 1286 (42000): Unknown storage engine 'BDB'。

MERGE 表格：把每個月的日誌合起來查詢

課程的情境：系統每個月建立一張日誌表格（log2003Jan、log2003Feb、log2003Mar）， 單一表格不會太大，舊月份也容易備份或刪除；但查詢「這一季全部的日誌」時，希望像查一張表格一樣方便。MERGE 表格就是做這件事：

CREATE TABLE logs (
  logid INT AUTO_INCREMENT PRIMARY KEY,
  logts DATETIME,
  entry CHAR(255)
) ENGINE=MERGE UNION=(log2003Jan, log2003Feb, log2003Mar) INSERT_METHOD=LAST;

• UNION=(…) 列出組成的表格。這些表格必須都是 MyISAM，而且欄位與索引的定義完全相同，否則查詢時會出現 ERROR 1168
• INSERT_METHOD 決定寫入 MERGE 表格的資料放到哪裡：FIRST（第一張）、LAST（最後一張）、NO（不能寫入，這是預設值）
• MERGE 表格的主鍵不會跨表格檢查：三張表格各自的 logid 都從 1 開始，合在一起就有三個 1
• 引擎名稱寫 MERGE 或 MRG_MyISAM 都可以，查詢時顯示為 MRG_MyISAM

表格名稱的大小寫：MySQL 把每張表格存成檔案，在 Linux（lower_case_table_names=0，Ubuntu 的預設）上， log2003Jan 和 log2003jan 是兩個不同的檔案名稱，寫錯大小寫就會找不到表格；Windows 預設不分大小寫（值為 1）。 表格名稱一律照建立時的寫法，不要依賴不分大小寫。

MERGE 只能搭配 MyISAM。現在同樣的需求多半改用 InnoDB 的分割表格（PARTITION BY RANGE），一張表格依日期分成多個分割區，同時保有交易與資料列鎖定。

全文檢索：在文章裡找詞

要找內文提到 merger 的文章，第一個想到的寫法是 WHERE body LIKE '%merger%'。 這個寫法可以用，但有兩個問題：開頭是 % 時用不到索引，每次都要讀完整張表格；而且只能回答「有沒有」，不能依相關程度排序。

全文檢索（full-text search）先把文字切成一個一個的詞，建立「哪個詞出現在哪些資料列」的索引，查詢時再算出每一列的相關度。 課程的 article 表格在 title 與 body 兩個欄位上建立一個 FULLTEXT 索引：

CREATE TABLE article (
  articleID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255),
  body TEXT,
  FULLTEXT (title, body)
) ENGINE=MyISAM;

SELECT title
FROM article
WHERE MATCH (title, body) AGAINST ('merger');

• MATCH (…) 裡的欄位必須和某一個 FULLTEXT 索引的欄位完全相同。只寫 MATCH (title) 會出現 ERROR 1191 (HY000): Can't find FULLTEXT index matching the column list
• 舊投影片說「只有 MyISAM 支援全文檢索」：這是 MySQL 5.5 以前的情況，InnoDB 從 MySQL 5.6 起也支援 FULLTEXT 索引
• 中文沒有空白分隔詞，預設的切詞方式不適用。中文內容要在建立索引時加上 WITH PARSER ngram，依固定長度（預設 2 個字）切詞

自然語言模式（natural language mode，預設的模式）的規則，以 MyISAM 為準：

50% 門檻常讓初學者困惑：在只有兩三筆資料的測試表格裡，幾乎每個詞都超過一半，什麼都查不到。

布林模式與 InnoDB 的全文檢索

在 AGAINST 的字串後面加上 IN BOOLEAN MODE，就可以用運算子控制條件。布林模式沒有 50% 門檻：

課程的範例：

SELECT title FROM article
WHERE MATCH (title, body)
      AGAINST ('+linux +"Open Source" -desktop Java ~Oracle' IN BOOLEAN MODE);

意思是：一定要有 linux、一定要有片語 Open Source、不能有 desktop；有 Java 的相關度提高，有 Oracle 的相關度降低。 布林模式不會自動依相關度排序，需要固定順序時自己加 ORDER BY。本課的直譯器在布林模式下的分數是簡化的計算，題目只看查到哪些列。

InnoDB 的全文檢索規則有幾點不同：

最短詞長是伺服器啟動時讀取的設定，修改後要重新啟動 MySQL，並重建 FULLTEXT 索引才會生效。

重點整理

• 指定引擎用 ENGINE=（TYPE= 已移除）；轉換用 ALTER TABLE 表格 ENGINE=引擎，會重建整張表格
• 查詢引擎：SHOW ENGINES、information_schema.TABLES 的 ENGINE 欄位
• ISAM、BDB 已移除；HEAP 是 MEMORY 的舊名稱
• 全文檢索：FULLTEXT (欄位) 建立索引，MATCH (欄位) AGAINST ('詞') 查詢，MATCH 的欄位要與索引完全相同
• 自然語言模式（MyISAM）有 50% 門檻、最短詞長 4；布林模式用 +、-、~、"片語" 控制條件；InnoDB 沒有 50% 門檻、最短詞長 3

下一課介紹 InnoDB 最重要的功能：交易。