今天將探討Engine Section,MySQL可以使用多種儲存引擎,這特色使得MySQL與其他資料庫產品不同.表中所列出來的都是使用Binary安裝時會安裝進來的,其中PERFORMANCE_SCHEMA 是與效能有關的資訊.另外還有一個FEDERATED,這是類似DBLink可以與其他MySQL連線,讀取遠端Server的資料.
今天將先介紹MyISAM儲存引擎.

1. MyISAM 部份
   MyISAM是MySQL常用的儲存引擎,操作快速,沒有支持外鍵限制, 沒有Transaction的功能,
   似乎與現在資料庫強調的一些功能不符合,但是實務運用上,也有許多情況不需要那樣嚴謹,
   不用去處理這些功能,讓速度變快,加上MyISAM支持 FULLTEXT index;
   使得MyISAM很適合用來儲存網站使用的資料,無怪乎MySQL是架設網站的常見選擇.

以下用MySQL官方的範例資料庫sakila裡面的film_text當例子.
film_text建立時的DDL:

CREATE TABLE `film_text` (
  `film_id` smallint(6) NOT NULL,
  `title` varchar(255) NOT NULL,
  `description` text,
  PRIMARY KEY (`film_id`),
  FULLTEXT KEY `idx_title_description` (`title`,`description`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

將title 與 description 建立了 FULLTEXT KEY.

SELECT COUNT(1)
FROM film_text
WHERE MATCH(title, description)
AGAINST('monkey' IN NATURAL LANGUAGE MODE);
+----------+
| COUNT(1) |
+----------+
|       87 |
+----------+
1 row in set (0.00 sec)

一下就找出來有87筆符合的資料.這比一般SQL命令的LIKE方便.

1.1 與 MyISAM 效能有關的參數
(a) key_buffer_size
(b) table_open_cache

1.2 MyISAM 使用的檔案
MyISAM每建立一個Table就會使用三個檔案,分別是
(a) table.frm 定義檔
(b) table.MYD 資料檔
(c) table.MYI 索引檔

1.3 MyISAM 空間使用
使用MyISAM引擎時,建立Table時,裡面欄位有VARCHAR, VARBINARY, BLOB, TEXT,
資料型態時會使用動態長度.
若無使用上述資料型態使用固定長度,會速度較快,但若僅使用上述中的VARCHAR時,也可以使用ROW_FORMAT=FIXED,來使用固定長度,不過這樣空間使用較浪費了.

以下將使用系統字典檔來當測試資料, 登入時加上 --local_infile

$ mysql -S /var/run/mysqld/mysqld.sock --local_infile -u root -p myperf

CREATE TABLE myisam_fixed(
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
data CHAR(100) NOT NULL) ENGINE=MyISAM;

CREATE TABLE myisam_dynamic(
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
data VARCHAR(100) NOT NULL) ENGINE=MyISAM;

CREATE TABLE myisam_fixed2(
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
data VARCHAR(100) NOT NULL) ENGINE=MyISAM ROW_FORMAT=FIXED;

CREATE TABLE myisam_dynamic2(
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
data TEXT NOT NULL) ENGINE=MyISAM ROW_FORMAT=FIXED;

SELECT table_name, row_format
FROM INFORMATION_SCHEMA.TABLES
WHERE table_name LIKE 'myisam_%';

+-----------------+------------+
| table_name      | row_format |
+-----------------+------------+
| myisam_dynamic  | Dynamic    |
| myisam_dynamic2 | Dynamic    |
| myisam_fixed    | Fixed      |
| myisam_fixed2   | Fixed      |
+-----------------+------------+

可以看到 myisam_fixed,是使用固定長度,myisam_dynamic因為有 VARCHAR(100), 所以是
動態長度,而myisam_fixed2 雖然是有 VARCHAR(100),但是因為使用了 ROW_FORMAT=FIXED,
所以變成了固定長度,而myisam_dynamic2 雖然是使用 ROW_FORMAT=FIXED,但是裡面有 TEXT,
這是還是會使用動態長度.

--載入資料.
LOAD DATA INFILE '/usr/share/dict/words' INTO TABLE myisam_fixed(data);

LOAD DATA INFILE '/usr/share/dict/words' INTO TABLE myisam_dynamic(data);

LOAD DATA INFILE '/usr/share/dict/words' INTO TABLE myisam_fixed2(data);
--察看使用空間
SELECT table_name, table_rows, row_format,
        data_length / 1024 AS 'data(K)',
        index_length / 1024 AS 'index(K)'
FROM INFORMATION_SCHEMA.TABLES
WHERE table_name IN ('myisam_fixed', 'myisam_fixed2', 'myisam_dynamic');

+----------------+------------+------------+------------+----------+
| table_name     | table_rows | row_format | data(K)    | index(K) |
+----------------+------------+------------+------------+----------+
| myisam_dynamic |      99171 | Dynamic    |  1986.5234 | 997.0000 |
| myisam_fixed   |      99171 | Fixed      | 29538.2373 | 996.0000 |
| myisam_fixed2  |      99171 | Fixed      | 29731.9307 | 996.0000 |
+----------------+------------+------------+------------+----------+

可以看到使用動態長度的節省空間,固定長度的使用空間多出許多;而使用VARCHAR又使用ROW_FORMAT=FIXED
的myisam_fixed2使用空間最大,因為VARCHAR本身存放時會有一個變動長度指標,較 CHAR 佔了幾個bytes(實際
上要看長度而變動),此時又因為ROW_FORMAT=FIXED,又都固定長度,所以反而使用了最多的空間.指標都差不多.
接著來看看實際檔案佔的空間.

-rw-rw---- 1 mysql mysql  2034200 10月  6 19:49 myisam_dynamic.MYD
-rw-rw---- 1 mysql mysql  1020928 10月  6 19:49 myisam_dynamic.MYI
-rw-rw---- 1 mysql mysql     8586 10月  6 19:30 myisam_fixed2.frm
-rw-rw---- 1 mysql mysql 30445497 10月  6 19:50 myisam_fixed2.MYD
-rw-rw---- 1 mysql mysql  1019904 10月  6 19:50 myisam_fixed2.MYI
-rw-rw---- 1 mysql mysql     8586 10月  6 19:29 myisam_fixed.frm
-rw-rw---- 1 mysql mysql 30247155 10月  6 19:49 myisam_fixed.MYD
-rw-rw---- 1 mysql mysql  1019904 10月  6 19:49 myisam_fixed.MYI

資料檔與索引檔與使用以下指令回報完全相同,另外還有定義檔,實務上我們不需要去擔心定義檔佔用的空間.

SELECT table_name, table_rows, row_format,
        data_length, index_length
FROM INFORMATION_SCHEMA.TABLES
WHERE table_name IN ('myisam_fixed', 'myisam_fixed2', 'myisam_dynamic');
+----------------+------------+------------+-------------+--------------+
| table_name     | table_rows | row_format | data_length | index_length |
+----------------+------------+------------+-------------+--------------+
| myisam_dynamic |      99171 | Dynamic    |     2034200 |      1020928 |
| myisam_fixed   |      99171 | Fixed      |    30247155 |      1019904 |
| myisam_fixed2  |      99171 | Fixed      |    30445497 |      1019904 |
+----------------+------------+------------+-------------+--------------+

1.4 當刪除資料以後的情況
我們將myisam_fixed2的資料刪除.

TRUNCATE TABLE myisam_fixed2;

察看MySQL回報的空間情況.
SELECT table_name, table_rows, row_format,
        data_length, index_length
FROM INFORMATION_SCHEMA.TABLES
WHERE table_name = 'myisam_fixed2';
+---------------+------------+------------+-------------+--------------+
| table_name    | table_rows | row_format | data_length | index_length |
+---------------+------------+------------+-------------+--------------+
| myisam_fixed2 |          0 | Fixed      |           0 |         1024 |
+---------------+------------+------------+-------------+--------------+

都不佔空間了,接著來看看檔案系統裡面的資料檔與索引檔,空間使用情況.
-rw-rw---- 1 mysql mysql     8586 10月  6 19:40 myisam_dynamic2.frm
-rw-rw---- 1 mysql mysql        0 10月  6 19:40 myisam_dynamic2.MYD
-rw-rw---- 1 mysql mysql     1024 10月  6 19:40 myisam_dynamic2.MYI

也都相符.接著把 myisam_fixed裡的資料用DELETE刪掉.
DELETE FROM myisam_fixed WHERE id <= 99171;
SELECT count(1) FROM myisam_fixed;
+----------+
| count(1) |
+----------+
|        0 |
+----------+
一筆都不剩,接著看MySQL回報的空間使用情況.

SELECT table_name, table_rows, row_format,
        data_length, index_length
FROM INFORMATION_SCHEMA.TABLES
WHERE table_name = 'myisam_fixed';
+--------------+------------+------------+-------------+--------------+
| table_name   | table_rows | row_format | data_length | index_length |
+--------------+------------+------------+-------------+--------------+
| myisam_fixed |          0 | Fixed      |    30247155 |      1019904 |
+--------------+------------+------------+-------------+--------------+

察看檔案系統裡面的資料檔與索引檔,空間使用情況.
-rw-rw---- 1 mysql mysql     8586 10月  6 19:29 myisam_fixed.frm
-rw-rw---- 1 mysql mysql 30247155 10月  6 20:10 myisam_fixed.MYD
-rw-rw---- 1 mysql mysql  1019904 10月  6 20:10 myisam_fixed.MYI

雖然已經將資料使用DELETE刪除,但是還是佔用了同樣的空間.此時輸入資料,會使用原本已經配置的空間,直到不足才會向
作業系統要更大的空間.

若我們要把這空間釋放出來,可以使用OPTIMIZE 指令.

OPTIMIZE NO_WRITE_TO_BINLOG TABLE myisam_fixed;
+---------------------+----------+----------+----------+
| Table               | Op       | Msg_type | Msg_text |
+---------------------+----------+----------+----------+
| myperf.myisam_fixed | optimize | status   | OK       |
+---------------------+----------+----------+----------+
1 row in set (0.02 sec)

順利完成,接著一樣察看空間.
SELECT table_name, table_rows, row_format,
        data_length, index_length
FROM INFORMATION_SCHEMA.TABLES
WHERE table_name = 'myisam_fixed';
+--------------+------------+------------+-------------+--------------+
| table_name   | table_rows | row_format | data_length | index_length |
+--------------+------------+------------+-------------+--------------+
| myisam_fixed |          0 | Fixed      |           0 |         1024 |
+--------------+------------+------------+-------------+--------------+

檔案系統空間使用情況

-rw-rw---- 1 mysql mysql    8586 10月  6 19:29 myisam_fixed.frm
-rw-rw---- 1 mysql mysql       0 10月  6 20:18 myisam_fixed.MYD
-rw-rw---- 1 mysql mysql    1024 10月  6 20:18 myisam_fixed.MYI

1.4 ROW_FORMAT的第三種:壓縮格式
	MyISAM還有第三種row format,可以用來存放壓縮的資料,以節省空間.實務上有些資料是列為備查,
不需要再變動,這時候就可以將其壓縮.
	要壓縮時,只能透過 myisampack 工具程式來完成,注意此時MySQL應該關閉.
	以 myisam_dynamic 為對象操作.

# myisampack myisam_dynamic.MYI
Compressing myisam_dynamic.MYD: (99171 records)
- Calculating statistics
- Compressing file
54.54%     
Remember to run myisamchk -rq on compressed tables

# myisamchk -rq myisam_dynamic
- check record delete-chain
- recovering (with sort) MyISAM-table 'myisam_dynamic'
Data records: 99171
- Fixing index 1

察看檔案系統上的空間
-rw-rw---- 1 mysql mysql  924837 10月  6 19:49 myisam_dynamic.MYD
-rw-rw---- 1 mysql mysql 1011712 10月  6 20:45 myisam_dynamic.MYI

與前面相比,已經縮小.

重新啟動MySQL,察看情形.
SELECT table_name, table_rows, row_format,
        data_length, index_length
FROM INFORMATION_SCHEMA.TABLES
WHERE table_name = 'myisam_dynamic';

+----------------+------------+------------+-------------+--------------+
| table_name     | table_rows | row_format | data_length | index_length |
+----------------+------------+------------+-------------+--------------+
| myisam_dynamic |      99171 | Compressed |      924830 |      1011712 |
+----------------+------------+------------+-------------+--------------+

row_format已經變為 Compressed, 924830 為之前 2034200 約為 45.46%, 
myisampack 回報之 54.54%,為壓縮之資料為全體之 54.54% 的意思.

試試看是否能 INSERT / UPDATE 資料到myisam_dynamic.

INSERT INTO myisam_dynamic(data) VALUES
('I want to Insert!!');

ERROR 1036 (HY000): Table 'myisam_dynamic' is read only

UPDATE myisam_dynamic SET data = 'I will change it' 
WHERE id = 1;

ERROR 1036 (HY000): Table 'myisam_dynamic' is read only

會回報錯誤,該Table已經為唯讀了.這樣不僅壓縮了資料,也保護了資料.