MySQL 5.7 General Tablespace學習(r11筆記第34天)

MySQL裡面的檔案蠻有意思，之前大體有兩個引數來做基本的控制。一個是innodb_data_file_path就是一個共享表空間，資料都往這一個檔案裡放，也就是ibdata1,這個檔案其實角色是有重複的，undo，資料都會放在一起。ibdata1會持續增長，無法收縮。另外一個引數是innodb_file_per_table，這樣一來，就成了獨立表空間，通俗一些就是每一個表都有獨立的檔案.frm和.ibd，而且實際中使用獨立表空間還是比較普遍的，對於delete的操作影響MySQL和Oracle就大大不同。

  後來到了MySQL 5.7.5,新增了線上階段undo log的功能，讓undo從原本的ibdata1剝離出來，而對於通用的獨立表空間的應用場景，MySQL也提供了另外一種管理方式，就是General tablespace。其實這個特性在Oracle中已經非常普遍，換個角度來理解就很容易了，它沒有庫的概念，可以在多個庫裡建屬於同一表空間的表。

為了支援這個特性，主要做了兩部分改動：Innodb層的支援及Server層對MDL子模組的改動。這部分我們後面又時間再說。    

    建立一個表空間的語句很簡單，語法如下：

CREATE TABLESPACE tablespace_name    ADD DATAFILE 'file_name'
    [FILE_BLOCK_SIZE = value]
        [ENGINE [=] engine_name]

大體的格式就是create tablespace xxx add datafile 'xxxx' engine=innodb; 這樣的方式，儲存單位預設是16k。

create tablespace general_ts1  add datafile 'general_ts1_01.dbf'   engine=innodb;
ERROR 3121 (HY000): Incorrect File Name 'general_ts1_01.dbf'.

這裡需要說明的一點是，檔案路徑可以是絕對的，也可以是相對的。但是檔名就得是.ibd的格式。

 create tablespace general_ts1  add datafile 'general_ts1_01.ibd'   engine=innodb;
Query OK, 0 rows affected (0.06 sec)

當然我們可以使用create table xxx 指定tablespace的方式，或者是alter table 指定tablespace的方式。

下面這種方式在GTID下是不支援的，值得說明一下。

create table test_ts tablespace general_ts1 as  select * from test ;
ERROR 1786 (HY000): Statement violates GTID consistency: CREATE TABLE ... SELECT.

我們換一個姿勢，建立一個表指定表空間。

create table test_ts (id int,name varchar(30)) tablespace general_ts1;
Query OK, 0 rows affected (0.04 sec)檢視錶的建表語句就可以看得很清楚了。

> show create table test_ts;
| test_ts | CREATE TABLE `test_ts` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(30) DEFAULT NULL
) /*!50100 TABLESPACE `general_ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8 |

我們來對比測試一下，重新指定一個表users，大概有80多萬的資料量。

> select count(*)from users;
+----------+
| count(*) |
+----------+
|   817975 |
+----------+

可以看到在修改前的表usres是存在兩個獨立的檔案。

-rw-r----- 1 mysql mysql       8606 Dec  4 22:48 users.frm
-rw-r----- 1 mysql mysql   41943040 Dec  4 22:48 users.ibd

使用alter語句來修改，整個過程很快

> alter table users tablespace general_ts1;
Query OK, 0 rows affected (1.87 sec)
Records: 0  Duplicates: 0  Warnings: 0

這個時候目錄下只存在一個定義檔案了，資料都放到新建的表空間了。

-rw-r----- 1 mysql mysql       8606 Jan  4 22:46 users.frm

我們簡單解析一下這個定義檔案，看看內容和原來有什麼差別，可以看到有了新的表空間的標識。

# strings users.frm
PRIMARY
InnoDB
general_ts1
)                                        
user_id
user_name
user_id
user_name

原本空白的資料檔案馬上就有了資料。

-rw-r----- 1 mysql mysql   41943040 Jan  4 22:46 general_ts1_01.ibd

如果我們修改表空間為獨立表空間的方式，也是可以的。

> ALTER TABLE users TABLESPACE=innodb_file_per_table;
Query OK, 0 rows affected (2.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

有一個差別就是建表DDL和原來的格式就不大一樣了。

> show create table users;
| users | CREATE TABLE `users` (
  `user_id` int(11) unsigned NOT NULL,
  `user_name` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`user_id`)
) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8 |

修改完成後.ibd檔案會重新生成。

如果要檢視錶空間的資訊，在使用general tablespace的情況下檢視資料字典就會有一些差別。比如資料庫test下存在一個表users，在檢視INNODB_SYS_TABLESPACES中是隻能看到表空間的基礎定義資訊，general_ts1而找不到users的字樣。

最後簡單說一下，我的公眾號現在開通了搜尋查詢功能，可以使用如下的專屬連結搜尋歷史的文章。

或者在公眾號的選單“號內搜文”來得到連結。