General Tablespaces in MySQL 5.7 – Details and Tips

InnoDB in MySQL 5.7 introduced for the first time the ability to create a general tablespace and assign multiple tables to it.  These tablespaces can be assigned anywhere on the system.  They can even be assigned a smaller block size so that they can contain compressed tables that use that size as their key_block_size.

You can create a new tablespace with a command like this;

If the current innodb-page-size is 16KB then the BLOCK_SIZE phrase is optional.

A few comments about datafile names
Notice that the extension .ibd is added to the file name.  This is required. InnoDB will only accept a file name that ends with .ibd.  This helps to ensure that the filename is the one you want since not just anything can be put after ADD DATAFILE.  It also enforces the convention that all InnoDB datafiles other than the system tablespace will end in .ibdwhich helps them to be recognized.

Notice also that there is no path on the datafile above.  Relative paths like this will be relative to the datadir which is found in your configuration file. This is the same location as the system tablespace and log files.

You can also use an absolute path to create the file anywhere else on your system.  There are two restrictions concerning where a general tablespace can be located:

1. It cannot be on the root directory.  Our design engineers thought it would be wise to prevent this. It comes mainly from the unix perspective but it is generally a good idea on Windows also.
2. A general tablespace datafile cannot be located in a directory under the datadir.  This is where datafiles for file-per-table tablespaces are located.  In MySQL, traditionally, directories under the datadir are there to contain files related to a database or schema.  These datafiles and directories are created automatically when you create a table while innodb-file-per-table is ON.  The file name is the same as the tablename with an .ibd extension added.

It is possible to create a tablespace with the same datafile name as a file-per-table datafile.  For example:

The result will be two files named new.ibd.  The general tablespace datafile will be located in the datadir and the file-per-table datafile will be located in a directory called ‘new’ under the datadir.

A word of advice though… Try to give unique names to all your database objects.  A future version of InnoDB may prevent similar datafile names like the two above.  Or it might allow you to start associating a general tablespace with a database which could cause a conflict somehow.  It is much wiser to name different objects differently to avoid any possible conflicts.

General Tablespace Portability
You can move a file-per-table tablespace from one system to another by  following the directions here:  http://dev.mysql.com/doc/refman/5.7/en/tablespace-copying.html

This method uses the following commands:

Discard and Import have never been supported for tables in the system tablespace.  And it is not supported in 5.7 for general tablespaces either.  Since a general tablespace can share multiple tables just like the system tablespace, it is not as easy to transport a datafile from one system to another.

Choosing a tablespace for your table
A table can be created or altered into a general tablespace, a file-per-table tablespace or even the system tablespace by using the TABLESPACE phrase on any CREATE TABLE or ALTER TABLE statement.

This gives you the ability to explicitly choose the tablespace you want for your table and even the ability to move your table around.  You can move any table from any tablespace into any other tablespace with the TABLESPACE phase on the ALTER TABLE statement.  This means that for the first time, you can move a table into the system tablespace.  Also, you can chose to use file-per-table independent of the innodb-file-per-table setting.

So if you were to do this;

the table would be created in its own file-per-table tablespace.

Likewise, this would create the table in the system tablespace;

The tablespace name is a SQL identifier
Notice that there are no quote marks around the tablespace name in these examples.  That is because the tablespace name is a SQL identifier.  The implications are that you can also use the backtick quote marks to enclose this name and that it is always evaluated in a CASE SENSITIVE way.

This means that you can create multiple tablespaces with the same name, but in different cases, like this;

Once again, a word to the wise, try not to name your tablespaces the same with only differences in case.

Reserved Tablespace Names
There are three ‘reserved’ tablespace names that have special meaning, two of which were mentioned earlier:

1. innodb_file-per-table
2. innodb_system
3. innodb_temporary

In 5.7, you can use the first two as I have already shown.  The third one is not available to use.  You do not need to use TABLESPACE=innodb_temporary to put a table into the temporary tablespace.  Just use CREATE TEMPORARY TABLE ...;.

These reserved tablespace names are case sensitive so it is possible to do this;

But once again, please don’t!  You are better off with unique tablespace names.

Conclusion
I hope this discussion has been useful for you to understand General Tablespaces in MySQL 5.7.  There are more tablespace features to come in future releases.

[Source:- Mysqlserverteam]