innodb ROW_FORMAT

1) The data in each InnoDB table is divided into pages.

2) The pages that make up each table are arranged in a tree data structure called a B-tree index.

3) Table data and secondary indexes both use this type of structure.

4) The B-tree index that represents an entire table is known as the clustered index,
   which is organized according to the primary key columns.

5) "BLOB and VARCHAR" stored on separately allocated disk pages called overflow pages
   We call such columns off-page columns.

Usage:

CREATE TABLE t1 (f1 int unsigned) ROW_FORMAT=DYNAMIC ENGINE=INNODB;

ROW_FORMAT=COMPACT

* Default

* InnoDB stores up to the first 768 bytes of variable-length columns(BLOB and VARCHAR) in the index record

* cause B-tree nodes to fill with data rather than key values

ROW_FORMAT=DYNAMIC;

Barracuda format:

supports compression for table data. This file format was first introduced in the InnoDB Plugin.

clustered index record contains only a 20-byte pointer to the overflow page

* InnoDB chooses the longest columns for off-page storage until the clustered index record fits on the B-tree page.

The DYNAMIC format is predicated on the idea that if a portion of a long data value is stored off-page, it is usually most efficient to store all of the value off-page.

Setting

innodb_file_per_table=#

# the default enabled in 5.6.6 and higher
# InnoDB stores the data and indexes for each newly created table in a separate .ibd file
# (rather than in the system tablespace.)

* Be aware that enabling innodb_file_per_table also means that an "ALTER TABLE" operation
  will move InnoDB table from the system tablespace to an individual .ibd file

innodb_file_format=#

# antelope and barracuda
# This applies only for tables that have their own tablespace

File

.ibd file

The data file for "file-per-table" tablespaces and general tablespaces.
.idb files contain a single table and associated index data.

* The .ibd file extension does not apply to the "system tablespace"(ibdata)

ibdata file

A set of files with names such as ibdata1, ibdata2, and so on, that make up the InnoDB system tablespace.

These files contain
* metadata about InnoDB tables
* one or more undo logs
* the change buffer
* the doublewrite buffer
* some or all of the table data

** The growth of the ibdata files is influenced by the innodb_autoextend_increment configuration option.