今天討論InnoDB, InnoDB是目前MySQL的主流儲存引擎,具有外鍵等現代資料庫特性.
InnoDB 可以使用TableSpace的方式來存放資料與索引,類似Oracle TableSpace的方式;也可以使用每一Table自己使用一檔案的方式, 在MySQL的設定檔my.cnf裡設定innodb_file_per_table = ON 即可.
4.1 InnDB 空間的使用
使用系統字典檔來當測試資料, 登入時加上 --local_infile

-- 建立Tables
CREATE TABLE innodb1(
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
data CHAR(100) NOT NULL) ENGINE=InnoDB;

CREATE TABLE innodb2(
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
data CHAR(100) NOT NULL) ENGINE=InnoDB ROW_FORMAT=COMPRESSED;

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

-- 載入資料
LOAD DATA INFILE '/usr/share/dict/words' INTO TABLE innodb1(data);
Query OK, 99171 rows affected (5.67 sec)
Records: 99171  Deleted: 0  Skipped: 0  Warnings: 0

LOAD DATA INFILE '/usr/share/dict/words' INTO TABLE innodb2(data);
Query OK, 99171 rows affected (5.44 sec)
Records: 99171  Deleted: 0  Skipped: 0  Warnings: 0

LOAD DATA INFILE '/usr/share/dict/words' INTO TABLE myisam1(data);
Query OK, 99171 rows affected (0.22 sec)
Records: 99171  Deleted: 0  Skipped: 0  Warnings: 0

-- 察看空間
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 ('innodb1', 'innodb2', 'myisam1');

+------------+------------+------------+------------+----------+
| table_name | table_rows | row_format | data(K)    | index(K) |
+------------+------------+------------+------------+----------+
| innodb1    |      99270 | Compact    | 13840.0000 |   0.0000 |
| innodb2    |      99270 | Compressed |  6920.0000 |   0.0000 |
| myisam1    |      99171 | Fixed      | 29538.2373 | 996.0000 |
+------------+------------+------------+------------+----------+

實際輸入的資料是 99171筆,但是在InnoDB的格式不同,標準是使用Compact緊湊模式,會先佔用的row數與實際目前的數量略有出入,因為不像MyISAM那樣是獨立的,而是使用Data Page.
另外注意到 innodb2 是壓縮模式,更節省空間了,而且只要在 CREATE TABLE 指定
ROW_FORMAT = COMPRESSED 即可.
但是這功能要比較新的MySQL才有支援,而且要將 innodb_file_format 設為 Barracuda.

-- 察看檔案系統的空間大小
	InnoDB的 以 idb檔來存放資料與索引.

-rw-rw---- 1 mysql mysql     8586 10月  7 19:28 innodb1.frm
-rw-rw---- 1 mysql mysql 22020096 10月  7 19:32 innodb1.ibd
-rw-rw---- 1 mysql mysql     8586 10月  7 19:28 innodb2.frm
-rw-rw---- 1 mysql mysql 11534336 10月  7 19:32 innodb2.ibd
-rw-rw---- 1 mysql mysql     8586 10月  7 19:28 myisam1.frm
-rw-rw---- 1 mysql mysql 30247155 10月  7 19:32 myisam1.MYD
-rw-rw---- 1 mysql mysql  1019904 10月  7 19:32 myisam1.MYI

可以看到 InnoDB 的空間使用較MyISAM節省很多.

4.2 InnoDB 空間的釋放
刪除資料以後來觀察空間使用情況,以及檔案大小.

DELETE FROM innodb1 WHERE id <= 99171;
Query OK, 99171 rows affected (0.91 sec)

SELECT table_name, table_rows, row_format,
        data_length, index_length
FROM INFORMATION_SCHEMA.TABLES
WHERE table_name = 'innodb1';

+------------+------------+------------+-------------+--------------+
| table_name | table_rows | row_format | data_length | index_length |
+------------+------------+------------+-------------+--------------+
| innodb1    |          0 | Compact    |       16384 |            0 |
+------------+------------+------------+-------------+--------------+

可以看到剩下一個data page, 16K.這是InnoDB 基本最少會有一個data page.
但是觀察檔案系統的 idb 檔,還是與未刪除前一樣大.

-rw-rw---- 1 mysql mysql 22020096 10月  7 19:50 innodb1.ibd

這與昨天介紹的MyISAM情況類似,就是向作業系統要了檔案的空間以後,就不會還.
MyISAM 的Table可以用 OPTIMIZE 指令來把空間釋放,那在 InnoDB要如何釋放空間呢?

使用以下指令即可

ALTER TABLE innodb1 ENGINE=InnoDB;

檔案也變小了.

-rw-rw---- 1 mysql mysql    98304 10月  7 19:59 innodb1.ibd