InnoDB
lastmod 2025-02-11created 2024-11-295 edits
InnoDB is a database engine for MySQL and is built into every binary distributed by MySQL AB. One of its improvements over the other database engines available for MySQL is support for ACID-compliant transactions, similar to PostgreSQL. It also supports foreign keys (FK). (This is called declarative referential integrity.)
After Innobase was acquired by Oracle in October 2005, InnoDB became an Oracle product. The software is dual-licensed: it is distributed under the GNU General Public License, but licenses can be sold to organizations that want to combine InnoDB with proprietary software.
MyISAM vs InnoDB
- MyISAM
- MyISAM manages Tables and Indexes in separate files
- The more Read Only operations a service has, the more efficient the MyISAM engine is
- InnoDB
- InnoDB is more efficient when transaction processing is needed and large volumes of data are handled
- InnoDB uses the Tablespace concept.
- By default it consists of a tablespace called innodb_system.
- An ibdata file exists by default when mysql is installed
- Environment variables: innodb_data_file_path, innodb_data_home_dir
Key features of the InnoDB engine
- InnoDB clusters by PK
- As a result, it tends to search by PK before other secondary Indexes
- Adopts Oracle's architecture
- Concepts mainly used in Oracle, such as MVCC (Multi version Concurrency Control) and tablespaces, are applied
- MVCC: performs work without taking locks, so reads are possible without waiting for locks held by other transactions