MySQL5.6ではinnodb_file_per_tableがDefaultでテーブル毎にテーブルスペース(ファイル)が作成されますが、MySQL5.7.6 DMRからは、CREATE TABLESPACEステートメントによって、
複数テーブルで共有出来るテーブルスペースが作成出来るようになりました。また、Defaultデータディレクトリーとは別のパスにテーブルスペースを作成出来るので、
負荷が高いテーブルなどをSSDなどに配置するなど柔軟に対応することが可能になります。Oracle(テーブルスペース)やMS SQL(ファイルグループ)に関しては、
以前から同様に指定出来ますが、オープンソースデータベースのMySQLは5.7になり、更にそれらの商用データベースと同様の機能も利用出来る汎用性を備えたデータベースになってきました。
MySQL5.7はまだ5.7.7RCですが、更に新たな機能が追加されているので適宜可能な範囲で、こちらにてご紹介したいとお思います。

参照: 13.1.15 CREATE TABLESPACE Syntax
CREATE TABLESPACE is supported with InnoDB as of MySQL 5.7.6.
A general tablespace is a shared tablespace, similar to the system tablespace.
It can hold multiple tables, and supports all table row formats.
General tablespaces can also be created in a location relative to or independent of the MySQL data directory.

Tablespaceの作成


root@localhost [USER01]> CREATE TABLESPACE U_TABLESPACE01 ADD DATAFILE '/home/mysql/user_tablespace01.ibd' Engine=InnoDB;
Query OK, 0 rows affected (0.01 sec)

root@localhost [USER01]> CREATE TABLESPACE U_TABLESPACE02_8K ADD DATAFILE '/home/mysql/user_tablespace02_8k.ibd' FILE_BLOCK_S8192 Engine=InnoDB;
Query OK, 0 rows affected (0.01 sec)

テーブルスペースを指定してテーブルを作成

root@localhost [USER01]> CREATE TABLE `T_USER01` (
    -> `id` int(11) NOT NULL AUTO_INCREMENT,
    -> `text` varchar(100) DEFAULT NULL,
    ->  PRIMARY KEY (`id`)
    ->  ) TABLESPACE = U_TABLESPACE01 ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.01 sec)

root@localhost [USER01]> CREATE TABLE `T_USER02_8K` (
    -> `id` int(11) NOT NULL AUTO_INCREMENT,
    -> `text` varchar(100) DEFAULT NULL,
    -> PRIMARY KEY (`id`)
    -> ) TABLESPACE = U_TABLESPACE02_8K ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPRESSED KEY_BLOC=8;
Query OK, 0 rows affected (0.00 sec)

root@localhost [USER01]> SELECT * FROM information_schema.INNODB_SYS_TABLESPACES where NAME like 'U_%';
+-------+-------------------+------+-------------+------------+-----------+---------------+------------+
| SPACE | NAME              | FLAG | FILE_FORMAT | ROW_FORMAT | PAGE_SIZE | ZIP_PAGE_SIZE | SPACE_TYPE |
+-------+-------------------+------+-------------+------------+-----------+---------------+------------+
|   165 | U_TABLESPACE01    | 2048 | Any         | Any        |     16384 |             0 | General    |
|   166 | U_TABLESPACE02_8K | 2089 | Barracuda   | Compressed |     16384 |          8192 | General    |
+-------+-------------------+------+-------------+------------+-----------+---------------+------------+
2 rows in set (0.00 sec)

tablespace2

Defaultデータディレクトリーとは別にテーブルスペースが作成されている事を確認


root@localhost [USER01]> show variables like 'datadir';
+---------------+------------------------+
| Variable_name | Value                  |
+---------------+------------------------+
| datadir       | /usr/local/mysql/data/ |
+---------------+------------------------+
1 row in set (0.00 sec)

root@localhost [USER01]> system ls /home/mysql/
user_tablespace01.ibd  user_tablespace02_8k.ibd
root@localhost [USER01]> 

先程、T_USER01を作成してテーブルスペースに追加でテーブルを作成


root@localhost [USER01]> CREATE TABLE `T_USER02` (
    -> `id` int(11) NOT NULL AUTO_INCREMENT,
    -> `text` varchar(100) DEFAULT NULL,
    -> PRIMARY KEY (`id`)
    -> ) TABLESPACE = U_TABLESPACE01 ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.00 sec)

root@localhost [USER01]> show tables;
+------------------+
| Tables_in_USER01 |
+------------------+
| T_USER01         |
| T_USER02         |
| T_USER02_8K      |
+------------------+
3 rows in set (0.00 sec)

root@localhost [USER01]> SELECT * FROM information_schema.INNODB_SYS_TABLES where NAME LIKE 'USER%';
+----------+--------------------+------+--------+-------+-------------+------------+---------------+------------+
| TABLE_ID | NAME               | FLAG | N_COLS | SPACE | FILE_FORMAT | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE |
+----------+--------------------+------+--------+-------+-------------+------------+---------------+------------+
|      171 | USER01/T_USER01    |  129 |      5 |   165 | Antelope    | Compact    |             0 | General    |
|      173 | USER01/T_USER02    |  129 |      5 |   165 | Antelope    | Compact    |             0 | General    |
|      172 | USER01/T_USER02_8K |  169 |      5 |   166 | Barracuda   | Compressed |          8192 | General    |
+----------+--------------------+------+--------+-------+-------------+------------+---------------+------------+
3 rows in set (0.00 sec)

root@localhost [USER01]> 

tablespace_innodb

既存のテーブルを共通テーブルスペースへ移動する場合はAlter Tableコマンドにて対応

root@localhost [USER01]> CREATE TABLE `T_USER03` (
    -> `id` int(11) NOT NULL AUTO_INCREMENT,
    -> `text` varchar(100) DEFAULT NULL,
    -> PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.03 sec)

root@localhost [USER01]> SELECT * FROM information_schema.INNODB_SYS_TABLES where NAME LIKE 'USER%';
+----------+--------------------+------+--------+-------+-------------+------------+---------------+------------+
| TABLE_ID | NAME               | FLAG | N_COLS | SPACE | FILE_FORMAT | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE |
+----------+--------------------+------+--------+-------+-------------+------------+---------------+------------+
|      171 | USER01/T_USER01    |  129 |      5 |   165 | Antelope    | Compact    |             0 | General    |
|      173 | USER01/T_USER02    |  129 |      5 |   165 | Antelope    | Compact    |             0 | General    |
|      172 | USER01/T_USER02_8K |  169 |      5 |   166 | Barracuda   | Compressed |          8192 | General    |
|      174 | USER01/T_USER03    |    1 |      5 |   167 | Antelope    | Compact    |             0 | Single     |
+----------+--------------------+------+--------+-------+-------------+------------+---------------+------------+
4 rows in set (0.00 sec)

root@localhost [USER01]> ALTER TABLE T_USER03 TABLESPACE U_TABLESPACE01;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@localhost [USER01]> SELECT * FROM information_schema.INNODB_SYS_TABLES where NAME LIKE 'USER%';
+----------+--------------------+------+--------+-------+-------------+------------+---------------+------------+
| TABLE_ID | NAME               | FLAG | N_COLS | SPACE | FILE_FORMAT | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE |
+----------+--------------------+------+--------+-------+-------------+------------+---------------+------------+
|      171 | USER01/T_USER01    |  129 |      5 |   165 | Antelope    | Compact    |             0 | General    |
|      173 | USER01/T_USER02    |  129 |      5 |   165 | Antelope    | Compact    |             0 | General    |
|      172 | USER01/T_USER02_8K |  169 |      5 |   166 | Barracuda   | Compressed |          8192 | General    |
|      175 | USER01/T_USER03    |  129 |      5 |   165 | Antelope    | Compact    |             0 | General    |
+----------+--------------------+------+--------+-------+-------------+------------+---------------+------------+
4 rows in set (0.00 sec)

root@localhost [USER01]> 

tablespace_alter

メモ:
5.7でサポートされる以下のページサイズも対応していますが、圧縮機能はサポートされていません。
64K 64K (65536) Compression is not supported
32K 32K (32768) Compression is not supported

Tablespace Row Formatのサポートについて
General tablespaces support all table row formats (REDUNDANT, COMPACT, DYNAMIC, COMPRESSED)
with the caveat that compressed and uncompressed tables cannot exist in the same general tablespace due to different physical page sizes.


Transportable Tablespace on MySQLの動作確認
MyISAMではなく、InnoDBがこれからのMySQLのコアなストレージエンジンなので、
MyISAMの時のようにファイルコピーして、データベースを他のサーバーでも
利用する機会があるかと思い、一応InnoDBで実装されたTransportable Tablespaceを確認。

Sakilaデータベースのlanguage tableを他のDBインスタンスに移動してみる。

root@localhost [(none)]>select @@hostname;
+------------+
| @@hostname |
+------------+
| CentOS01   |
+------------+
1 row in set (0.00 sec)

root@localhost [(none)]>show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| audit_test         |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| test               |
+--------------------+
7 rows in set (0.00 sec)

root@localhost [(none)]>

root@localhost [(none)]>use sakila
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
root@localhost [sakila]>show tables;
+----------------------------+
| Tables_in_sakila           |
+----------------------------+
| actor                      |
| actor_info                 |
| address                    |
| category                   |
| city                       |
| country                    |
| customer                   |
| customer_list              |
| film                       |
| film_actor                 |
| film_category              |
| film_list                  |
| film_text                  |
| inventory                  |
| language                   |
| nicer_but_slower_film_list |
| payment                    |
| rental                     |
| rental2                    |
| sales_by_film_category     |
| sales_by_store             |
| staff                      |
| staff_list                 |
| store                      |
+----------------------------+
24 rows in set (0.00 sec)


root@localhost [sakila]>show create database sakila;
+----------+-----------------------------------------------------------------+
| Database | Create Database                                                 |
+----------+-----------------------------------------------------------------+
| sakila   | CREATE DATABASE `sakila` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-----------------------------------------------------------------+
1 row in set (0.00 sec)

root@localhost [sakila]>


root@localhost [sakila]>show create table language\G
*************************** 1. row ***************************
       Table: language
Create Table: CREATE TABLE `language` (
  `language_id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  `name` char(20) NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`language_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

root@localhost [sakila]>

root@localhost [sakila]>FLUSH TABLES language FOR EXPORT;
Query OK, 0 rows affected (0.01 sec)

root@localhost [sakila]>

FOR EXPORTで対象にしたテーブル用にcfgファイルが出来ているので、
ibdとcfgファイルをコピー先にコピーする。

[root@CentOS01 sakila]# pwd
/usr/local/mysql-advanced-5.6.21-linux-glibc2.5-x86_64/data/sakila
[root@CentOS01 sakila]# ls -l language.*
-rw-rw----. 1 mysql mysql   477 11月  3 15:03 language.cfg
-rw-rw----. 1 mysql mysql  8648 10月 30 12:08 language.frm
-rw-rw----. 1 mysql mysql 98304 10月 30 12:08 language.ibd
[root@CentOS01 sakila]# 

[root@CentOS01 sakila]# scp language.{ibd,cfg} 192.168.56.102:/usr/local/mysql/data/sakila
root@192.168.56.102's password: 
language.ibd                                                                                                                                100%   96KB  96.0KB/s   00:00    
language.cfg                                                                                                                                100%  477     0.5KB/s   00:00    
[root@CentOS01 sakila]# 

コピーが終わったので、テーブルをUNLOCKして利用可能にする。

root@localhost [sakila]>UNLOCK TABLES;
Query OK, 0 rows affected (0.01 sec)

root@localhost [sakila]>

root@localhost [sakila]>select * from language;
+-------------+----------+---------------------+
| language_id | name     | last_update         |
+-------------+----------+---------------------+
|           1 | English  | 2006-02-15 05:02:19 |
|           2 | Italian  | 2006-02-15 05:02:19 |
|           3 | Japanese | 2006-02-15 05:02:19 |
|           4 | Mandarin | 2006-02-15 05:02:19 |
|           5 | French   | 2006-02-15 05:02:19 |
|           6 | German   | 2006-02-15 05:02:19 |
+-------------+----------+---------------------+
6 rows in set (0.00 sec)

root@localhost [sakila]>

UNLOCKするまで、対象テーブルはSELECT出来ますが、
INSERT,UPDATE,DELETEなど変更を伴うDMLは待たされます。

lock

コピー先のインスタンスに予め空のDBとテーブルを作成しておく。
※Scriptはコピー元でSHOW CREATEコマンドで確認したSCRIPTを実行してあります。

root@localhost [(none)]> select @@hostname;
+------------+
| @@hostname |
+------------+
| CentOS02   |
+------------+
1 row in set (0.00 sec)

root@localhost [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

root@localhost [(none)]> 


root@localhost [(none)]> CREATE DATABASE `sakila` /*!40100 DEFAULT CHARACTER SET utf8 */;
Query OK, 1 row affected (0.00 sec)

root@localhost [(none)]> use sakila;
Database changed
root@localhost [sakila]> CREATE TABLE `language` (
    ->   `language_id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
    ->   `name` char(20) NOT NULL,
    ->   `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    ->   PRIMARY KEY (`language_id`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.10 sec)

root@localhost [sakila]> alter table language DISCARD TABLESPACE;                                                                                                             Query OK, 0 rows affected (0.08 sec)

root@localhost [sakila]> 

上記で空テーブルを無効にしてあるので、コピー元のインスタンスからファイルコピーし終わったら、
以下のコマンドでテーブルスペースをIMPORTする。

root@localhost [sakila]> alter table language Import TABLESPACE;
Query OK, 0 rows affected (0.13 sec)

root@localhost [sakila]> 

root@localhost [sakila]> show tables;
+------------------+
| Tables_in_sakila |
+------------------+
| language         |
+------------------+
1 row in set (0.00 sec)

root@localhost [sakila]> select * from language;
+-------------+----------+---------------------+
| language_id | name     | last_update         |
+-------------+----------+---------------------+
|           1 | English  | 2006-02-15 05:02:19 |
|           2 | Italian  | 2006-02-15 05:02:19 |
|           3 | Japanese | 2006-02-15 05:02:19 |
|           4 | Mandarin | 2006-02-15 05:02:19 |
|           5 | French   | 2006-02-15 05:02:19 |
|           6 | German   | 2006-02-15 05:02:19 |
+-------------+----------+---------------------+
6 rows in set (0.00 sec)

root@localhost [sakila]> 

これで、同じ内容のテーブルが2台のサーバーで利用可能になりました。
コピーしたcfgファイルはバイナリ―ファイルになります。

参考
http://dev.mysql.com/doc/refman/5.6/en/tablespace-copying.html


INNODBのTABLESPACEにどれだけFREE SPACEがあるか確認。

mysql> SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, ENGINE, DATA_FREE,TABLE_COMMENT FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA=’test’ AND TABLE_NAME like ‘T%’;

+————–+————+————+——–+———–+—————+
| TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | ENGINE | DATA_FREE | TABLE_COMMENT |
+————–+————+————+——–+———–+—————+
| test | T1 | BASE TABLE | InnoDB | 4194304 | |
| test | T2 | BASE TABLE | InnoDB | 4194304 | |
| test | t1 | BASE TABLE | InnoDB | 4194304 | My Table |
+————–+————+————+——–+———–+—————+
3 rows in set (0.02 sec)
mysql>

mysql> show table status like ‘T%’;
|-+————-+———+————+——+—————-+————-+—————–+————–+———–+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free |
+——+——–+———+————+——+—————-+————-+—+————-+————–+————
| T1 | InnoDB | 10 | Compact | 9 | 1820 | 16384 | 0 | 0 | 4194304 |
| T2 | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 4194304 |
|+——+——–+———+———–+——+—————-+————-+—————–+————–+———–+
2 rows in set (0.55 sec)
mysql>

innodb_freespace