MySQL - Text 和 Blob 特性探討

簡介

MySQL 儲存格式中，常用TEXT或BLOB來儲存長字串

兩者之間的差異主要為: TEXT 只能儲存字元資料，BLOB 可以保存二進位資料(圖片..)

通常，根據內文預期的長度來挑選適當的類型

  Type |         Bytes | English words | Multi-byte words

———–+—————+—————+—————– TINYTEXT or TINYBLOB | 255 | ±44 | ±23 TEXT or BLOB | 65,535 | ±11,000 | ±5,900 MEDIUMTEXT or MEDIUMBLOB | 16,777,215 | ±2,800,000 | ±1,500,000 LONGTEXT or LONGBLOB | 4,294,967,295 | ±740,000,000 | ±380,000,000

缺點

兩者在刪除之後，仍會占用空間，長期或大量執行刪除動作時，會產生大量空洞空間，引起性能問題。

定期最佳化表格

透過 OPTIMIZE TABLE 將資料表進行磁碟重組，將空洞空間釋放出來，改善效能。

>OPTIMIZE TABLE 資料表名稱

搜尋

對於 TEXT 與 BLOB 內容進行搜尋，效能較差

因此，有幾個方式可以用來改善查詢表單的性能:

合成索引

合成索引是將TEXT 或 BLOB 的內容，同時存成一個 MD5、SHA1…等散列值。

查詢時，則是將查詢的字串同樣轉成散列值，再來查詢

但是這樣的方式只適合精確匹配的字串，雖然能有效提升效能，卻無法廣泛實用

這裡做一個合成索引範例說明:

我們先建立表單，其中 content 是長內文，hash則是會用來儲存散列值

> CREATE TABLE text_table (id int(10) NOT NULL AUTO_INCREMENT, content text, hash varchar(40), PRIMARY KEY(id));

Query OK, 0 rows affected (0.06 sec)

插入值時，這裡是將 md5(content) 散列值儲存在 hash 欄位

MariaDB [example]> INSERT INTO text_table(`content`,`hash`) VALUES(REPEAT('hi',3), md5(content));
Query OK, 1 row affected (0.00 sec)
MariaDB [example]> INSERT INTO text_table(`content`,`hash`) VALUES(REPEAT('hello',3), md5(content));
Query OK, 1 row affected (0.00 sec)


MariaDB [example]> SELECT * FROM text_table;
+----+-----------------+----------------------------------+
| id | content         | hash                             |
+----+-----------------+----------------------------------+
|  1 | hihi            | e9f5713dec55d727bb35392cec6190ce |
|  2 | hellohellohello | 99fb31087791f6317ad7c6da1433f172 |
+----+-----------------+----------------------------------+
2 rows in set (0.00 sec)

查詢時，則會將搜尋的字串轉乘散列值，再來進行比對

MariaDB [example]> SELECT * FROM text_table WHERE hash=md5('hihi');
+----+---------+----------------------------------+
| id | content | hash                             |
+----+---------+----------------------------------+
|  1 | hihi    | e9f5713dec55d727bb35392cec6190ce |
+----+---------+----------------------------------+
1 row in set (0.00 sec)

首碼索引

MySQL 提供了首碼索引的功能，可以為欄位的前 n 個字元建立索引，來改善查詢的性能

MariaDB [example]> CREATE INDEX IDX_BLOB ON text_table(content(100));
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

使用 DESC 查看是否已成功使用了索引這裡執行結果，可以看到已經成功使用前綴 IDX_BLOB


MariaDB [example]> DESC SELECT * FROM text_table WHERE content like 'hi%' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: text_table
         type: range
possible_keys: IDX_BLOB
          key: IDX_BLOB
      key_len: 103
          ref: NULL
         rows: 1
        Extra: Using where
1 row in set (0.00 sec)