InnoDB 的 BLOB field 存放的方式

這邊的 BLOB field 指的包括 VARCHAR、VARBINARY、BLOB、TEXT 這些常常被拿來放大物件的類型:「Externally Stored Fields in InnoDB」。

這跟 InnoDB 存放的格式 (ROW_FORMAT) 也有關,對於不同的格式都需要分開討論。

看之前需要帶一些背景知識,像是 Database index 裡面講到 index 種類時所提到的 Clustered。

看完後對 MySQL InnoDB 的運作方式會更了解一些,對於規劃 schema 也加減有些幫助。

MySQL 裡搜尋 CHAR/VARCHAR (String) 欄位時要注意的事情

MySQL 表格欄位是 CHAR 或 VARCHAR 時,寫搜尋條件要記得使用 string 格式,而非數字。意思是,要避免這種 SQL query:

SELECT * FROM foo WHERE `column_string` = 123456;

原因是即使 column_string 加上了 B-tree index,也無法利用這個 index 加速查詢。

原因是,除了最明確的 '123456' 會符合外,還有很多種 case 符合:

mysql> SELECT 123456 = '0123456';
+--------------------+
| 123456 = '0123456' |
+--------------------+
|                  1 |
+--------------------+
1 row in set (0.00 sec)

mysql> SELECT 123456 = ' 123456';
+--------------------+
| 123456 = ' 123456' |
+--------------------+
|                  1 |
+--------------------+
1 row in set (0.00 sec)

這使得 index 無用武之地。

但如果欄位本身是數字 (INT/BIGINT),搜尋時用字串反而沒關係:MySQL 會先把字串轉型為數字再比較,所以會用到 index。

總而言之:

  • 可以用 INT/BIGINT 時,不要用 CHAR/VARCHAR 儲存。
  • 使用 CHAR/VARCHAR 的欄位當搜尋條件時,要用字串形式當作搜尋條件。(除非你很清楚你在做什麼)

今天上場當救援投手時解掉的問題...

將 latin1 的表格轉換成 UTF-8 表格...

Percona 的人寫了一篇「utf8 data on latin1 tables: converting to utf8 without downtime or double encoding」,告訴你怎麼將 latin1 的 TEXT 欄位轉成 UTF-8,文章內有提到利用 BLOB 轉。

不確定同樣方式能不能做在 VARCHAR 上面 (用 BINARY 轉?),但不知道會不會有 UNIQUE + prefix support 的問題?有遇到再來測試看看...

MySQL 對 VARCHAR 的 Index 空間佔用的問題...

之前不知道從哪邊學到錯的東西... 實驗後發現搞錯了。對 MySQL 的 VARCHAR 欄位下 index 所實際佔用的空間仍是實際大小,而非最大長度。

測試方法是建立表格,schema 是 CREATE TABLE test (id INT UNSIGNED PRIMARY AUTO_INCREMENT, data VARCHAR(255)) ENGINE=InnoDB;,在 inndo_per_file 打開的情況下測試。

這是 1M rows,其中 data 都是 "a",這是 OPTIMIZE TABLE 後的結果:(以下每個都有 OPTIMIZE TABLE)

-rw-rw---- 1 mysql mysql     8586 Jul 30 22:42 test.frm
-rw-rw---- 1 mysql mysql 37748736 Jul 30 22:42 test.ibd

這是 ADD INDEX (data) 後的結果:

-rw-rw---- 1 mysql mysql     8586 Jul 30 22:46 test.frm
-rw-rw---- 1 mysql mysql 50331648 Jul 30 22:46 test.ibd

一樣是 1M rows,但 data 都是 "a" * 100 (一百個 a) 的結果:

-rw-rw---- 1 mysql mysql      8586 Jul 30 23:14 test.frm
-rw-rw---- 1 mysql mysql 146800640 Jul 30 23:15 test.ibd

這是 ADD INDEX (data) 後的結果:

-rw-rw---- 1 mysql mysql      8586 Jul 30 23:21 test.frm
-rw-rw---- 1 mysql mysql 260046848 Jul 30 23:23 test.ibd

實驗可以看出來 MySQL 的確是依照內容的實際長度索引,而非用欄位的最大長度做。

對 MySQL 的 VARCHAR 欄位使用 INDEX 時可以增加效率的方法...

MySQL 中,如果你有 VARCHAR(255) 這種欄位,不要對直接對這個欄位下 INDEX。因為 key 會以最大長度 255 chars 為固定大小,而非動態決定 (latin1 的時候 1 char 是 1 byte,utf8 是 3 bytes,utf8mb4 是 4 bytes),當資料有 1M row data 就直接吃掉 1MB/3MB/4MB 的空間。

解決方法是利用「index 可以指定只取前面 n chars」這個功能來做,至於 n 要取多少就是要估算了... 在「Optimal index size for variable text in MySQL」這篇把要怎麼做的過程寫得還蠻完整的。

同樣的道理也可以用在固定寬度的 BINARY(16) 系列上。