InnoDB 분석

  • InnoDB - MySQL의 기본 스토리지 엔진

  • InnoDB tables arrange your data on disk to optimize queries based on primary keys. Each InnoDB table has a primary key index called the clustered index that organizes the data to minimize I/O for primary key lookups

  • Inserts, updates, and deletes are optimized by an automatic mechanism called change buffering. InnoDB not only allows concurrent read and write access to the same table, it caches changed data to streamline disk I/O.

  • file-per-table A general name for the setting controlled by the innodb_file_per_table option, which is an important configuration option that affects aspects of InnoDB file storage, availability of features, and I/O characteristics. As of MySQL 5.6.7, innodb_file_per_table is enabled by default.

With the innodb_file_per_table option enabled, you can create a table in its own .ibd file rather than in the shared ibdata files of the system tablespace. When table data is stored in an individual .ibd file, you have more flexibility to choose row formats required for features such as data compression. The TRUNCATE TABLE operation is also faster, and reclaimed space can be used by the operating system rather than remaining reserved for InnoDB.

  • You can monitor the internal workings of the storage engine by querying INFORMATION_SCHEMA tables.

  • You can monitor the performance details of the storage engine by querying Performance Schema tables.

  • system tablespace One or more data files (ibdata files) containing metadata for InnoDB-related objects (the InnoDB data dictionary), and the storage areas for the change buffer, the doublewrite buffer, and possibly undo logs. It may also contain table and index data for InnoDB tables if tables were created in the system tablespace instead of file-per-table or general tablespaces. The data and metadata in the system tablespace apply to all databases in a MySQL instance.

In MySQL 5.7, the default is file-per-table mode, where each table and its associated indexes are stored in a separate .ibd file. This default makes it easier to use InnoDB features that rely on the Barracuda file format, such as table compression, efficient storage of off-page columns, and large index key prefixes (innodb_large_prefix).