神刀安全网

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;

CREATETABLESPACEnew ADDDATAFILE 'newdata.ibd' BLOCK_SIZE=16k; 

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 .idb 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 put after ADD DATAFILE .  It also enforces the convention that all InnoDB datafiles other than the system tablespace will end in .ibd which helps them to be recognized.

Notice also that there is no path on the file.  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:

CREATEDATABASEnew; SETSESSIONinnodb_file_per_table=ON; CREATETABLEnew.new (a INT) engine=InnoDB; CREATETABLESPACEnew ADDDATAFILE 'new.ibd' engine=InnoDB; 

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 shutting down the engine and copying the FRM file and the IBD file.  This copies its data and its MySQL metadata. But there is also metadata in the InnoDB system tables which must have already been established.

Or you could use the discard command like this:

ALTERTABLEt1DISCARDTABLESPACE; 

After copying the datafile, it can be imported or activated again like this:

ALTERTABLEt1IMPORTTABLESPACE; 

This has 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.

You can move a file between systems when the engines are down, but both of those systems need to have the exact same metadata describing the same tablespace and all its tables.

Choosing a tablespace for your table

A table can be created or alter 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;

SETSESSIONinnodb_file_per_table=OFF; CREATETABLEt1 (a INT) TABLESPACE=innodb_file_per_tableengine=InnoDB; thetablewouldbecreatedin itsownfile-per-tabletablespace. 

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

SETSESSIONinnodb_file_per_table=ON; CREATETABLEt1 (a INT) TABLESPACE=innodb_systemengine=InnoDB; 

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;

CREATETABLESPACEnew ADDDATAFILE 'new1.ibd'; CREATETABLESPACENew ADDDATAFILE 'new1.ibd'; CREATETABLESPACENEW ADDDATAFILE 'new1.ibd'; 

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;

CREATETABLESPACE `InnoDB_System` ADDDATAFILE 'innodb_system.ibd'; 

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.

转载本站任何文章请注明:转载至神刀安全网,谢谢神刀安全网 » General Tablespaces in MySQL 5.7 – Details and Tips

分享到:更多 ()

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址
分享按钮