關於網路那些事...

網路行銷,SEO,網路趨勢,教學文章,網頁設計,生活時事

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)

如果你喜歡我們的文章內容,請在這裡按個讚



最新文章推薦