InnoDB

InnoDB is a free memory subsystem for the database management system MySQL. Its main advantage over other storage subsystems for MySQL is that transaction security and referential integrity are guaranteed by a foreign key.

InnoDB Oy, the manufacturer of InnoDB, was acquired in October 2005 by Oracle system.

The software is licensed double: For open source applications, it is under the GPL; for non-open source applications is next to a proprietary license.

Properties

In MySQL version 5.0, InnoDB is installed as a standard storage subsystem when installed under Windows. When installing on Unix MyISAM is installed as a standard storage subsystem. As of MySQL 5.5, InnoDB is the default storage subsystem under all systems.

InnoDB supports transactions, ie:

• The made ​​in the transaction write operations may by a transaction abort ( " abort" ) be revoked ( rollback ).
• Repeated read accesses within a transaction are of write accesses by other transactions not affected (isolation from ACID).
• Write accesses in a transaction cause a write lock the affected rows for all other transactions (Row level locking ).

To explicitly create a table with InnoDB as the storage subsystem, the SQL option ENGINE can be used:

CREATE TABLE t (i INT) ENGINE = InnoDB; (Note: Older versions of MySQL use the keyword TYPE instead of ENGINE (example: TYPE = INNODB ) MySQL 5.0 and 5.1 support this syntax backwards compatible, but the use of ENGINE is recommended because since MySQL 5.5, the keyword ENGINE will only be supported. . )

Table space

InnoDB stores the table structure in frm files, user data and indexes in a table space. The table space is set before starting the work with the database server and can extend over one or more files. The files of the table space can be distributed to different directories. Similar to a hard disk partition, the configuration of the table space can not be adjusted subsequently, without risking data loss. After changing the configuration of the table space, the entire database is restored from a backup copy.

With the option innodb_file_per_table a separate table space is created for each table, ie MySQL stores user data and indexes in the file t.ibd together with the structure t.frm file in the database directory. Table data can be extracted subsequently with the zero - operation from the table space:

ALTER TABLE t ENGINE = InnoDB; memory deallocation

After deletion of InnoDB tables, the memory is released within the table space. This release will not be passed to the operating system, so that the size of the table space files is not reduced. The elaborate release the disk space is done by deleting the entire table space and restore the database from a backup copy.

An alternative is the per -table table space for each table created a separate table space file in which and will be deleted after deleting the table. The disadvantage of this mode is that you moved some part of the data management of MySQL on the operating system that does this may be less efficient. Before creating and filling a large table of the per -table table space can be activated and deactivated at runtime to later release the disk space for a single table specifically.

SHOW VARIABLES LIKE ' innodb_file_per_table '; - Show the current setting SET @ @ global.innodb_file_per_table = TRUE; - Enable Per -table tablespace CREATE TABLE a ( ... ) ENGINE = InnoDB; - Create a table in the per- table tablespace ALTER TABLE t ENGINE = InnoDB; - Move a table later in the per -table tablespace SET @ @ global.innodb_file_per_table = FALSE; - Disable Per -table tablespace CREATE TABLE b ( ... ) ENGINE = InnoDB; - Create a table in the tablespace   DROP TABLE a; - Delete table and free up disk space DROP TABLE b; - Delete table, disk space is not freed literature

• Sasha Pachev: Understanding MySQL Internals, O'Reilly, 2007, ISBN 978-0-596-00957-1