InnoDB

from Wikipedia, the free encyclopedia
InnoDB
Basic data

developer Heikki Tuuri, Innobase Oy Inc. ( Oracle Corporation)
operating system Unix , Linux , Windows , OS X , i5 / OS , OpenVMS
programming language C.
category Storage subsystem
License Dual licensing system ( Commercial and GNU General Public License )
German speaking No
innodb.com

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

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

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

properties

With MySQL Version 5.0, InnoDB is installed as the standard storage subsystem when installed on Windows . When installing under Unix , MyISAM is installed as the standard storage subsystem. Since MySQL 5.5, InnoDB has been the standard storage subsystem among all systems.

InnoDB supports transactions, that is:

  • The write operations carried out in the transaction can be revoked ( rollback ) by aborting the transaction .
  • Repeated read accesses within a transaction are not influenced by write accesses from other transactions ( isolation from ACID ).
  • Write accesses in a transaction result in a write lock on the affected data records for all other transactions ( row level locking ).

The SQL option ENGINEcan be used to explicitly create a table with InnoDB as the storage subsystem : 

CREATE TABLE t (i INT) ENGINE = InnoDB;

(Note: Older versions of MySQL use the keyword TYPEinstead of ENGINE(Example:) TYPE = INNODB. MySQL 5.0 and 5.1 support this syntax backwards compatible, but the use of is ENGINErecommended because since MySQL 5.5 only the keyword is ENGINEsupported.)

Table space

InnoDB saves the table structure in frm files, user data and indices in a table space. The tablespace is set before you start working with the database server and can span one or more files. The files in the table space can be distributed to different directories. Similar to a hard disk partition , the configuration of the table space cannot be adjusted subsequently without risking data loss. If the configuration of the table space is changed, 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, i. H. MySQL saves user data and indices in the t.ibd file together with the t.frm structure file in the database directory. Table data can be subsequently extracted from the table space using the null operation: 

ALTER TABLE t ENGINE=InnoDB;

Memory release

After deleting InnoDB tables, the memory within the table space is released. However, this share is not passed on to the operating system, so the size of the tablespace files is not reduced. The laborious release of hard disk space is done by deleting the entire table space and restoring the database from a backup copy.

An alternative is the per-table table space, in which a separate table space file is created for each table and deleted again after the table has been deleted. The disadvantage of this mode is that part of the data management from MySQL is shifted to the operating system that this may have. Sometimes done less efficiently. Before creating and filling a large table, the per-table table space can be activated and deactivated again at runtime in order to later free up the hard disk space for an individual table. 

SHOW VARIABLES LIKE 'innodb_file_per_table'; -- aktuelle Einstellung anzeigen
SET @@global.innodb_file_per_table = TRUE; -- Per-Table-Tablespace aktivieren
CREATE TABLE a (...) ENGINE=InnoDB; -- Eine Tabelle im Per-Table-Tablespace erstellen
ALTER TABLE t ENGINE=InnoDB; -- Eine Tabelle nachträglich in den Per-Table-Tablespace verschieben
SET @@global.innodb_file_per_table = FALSE; -- Per-Table-Tablespace deaktivieren
CREATE TABLE b (...) ENGINE=InnoDB; -- Eine Tabelle im Tablespace erstellen

DROP TABLE a; -- Tabelle löschen und Festplattenspeicher freigeben
DROP TABLE b; -- Tabelle löschen, Festplattenspeicher wird nicht freigegeben

literature

Web links

Individual evidence

  1. ↑ Oracle announces acquisition of Innobase, a manufacturer of open source software . Oracle . Retrieved January 16, 2015.
  2. ↑ Licensing of MySQL and InnoDB . InnoDB.com. Retrieved July 9, 2010.
  3. ↑ http://www.oracle.com/us/corporate/press/195726
  4. ↑ see e.g. B. Pachev: Understanding MySQL Internals, Chapter 10: Storage Engines
  5. ↑ Archive link ( Memento of the original from January 29, 2012 in the Internet Archive ) Info: The archive link was inserted automatically and has not yet been checked. Please check the original and archive link according to the instructions and then remove this notice.  @1@ 2Template: Webachiv / IABot / dev.mysql.com
  6. ↑ http://dev.mysql.com/doc/refman/5.5/en/create-table.html
  7. ↑ Release InnoDB memory automatically . May 15, 2013. Retrieved June 10, 2013.