(mysql.info) table-and-index

(mysql.info) innodb-multi-versioning

(mysql.info) file-space-management

 
 14.2.13 `InnoDB' Table and Index Structures
 -------------------------------------------

 
* innodb-physical-structure    Physical Structure of an Index
* innodb-insert-buffering      Insert Buffering
* innodb-adaptive-hash         Adaptive Hash Indexes
* innodb-physical-record       Physical Row Structure
 
 MySQL stores its data dictionary information for tables in `.frm' files
 in database directories. This is true for all MySQL storage engines.
 But every `InnoDB' table also has its own entry in the `InnoDB'
 internal data dictionary inside the tablespace. When MySQL drops a
 table or a database, it has to delete both an `.frm' file or files, and
 the corresponding entries inside the `InnoDB' data dictionary. This is
 the reason why you cannot move `InnoDB' tables between databases simply
 by moving the `.frm' files.
 
 Every `InnoDB' table has a special index called the clustered index
 where the data for the rows is stored. If you define a `PRIMARY KEY' on
 your table, the index of the primary key is the clustered index.
 
 If you do not define a `PRIMARY KEY' for your table, MySQL picks the
 first `UNIQUE' index that has only `NOT NULL' columns as the primary key
 and `InnoDB' uses it as the clustered index. If there is no such index
 in the table, `InnoDB' internally generates a clustered index where the
 rows are ordered by the row ID that `InnoDB' assigns to the rows in
 such a table. The row ID is a 6-byte field that increases monotonically
 as new rows are inserted. Thus, the rows ordered by the row ID are
 physically in insertion order.
 
 Accessing a row through the clustered index is fast because the row
 data is on the same page where the index search leads. If a table is
 large, the clustered index architecture often saves a disk I/O when
 compared to the traditional solution. (In many database systems, data
 storage uses a different page from the index record.)
 
 In `InnoDB', the records in non-clustered indexes (also called
 secondary indexes) contain the primary key value for the row. `InnoDB'
 uses this primary key value to search for the row from the clustered
 index. Note that if the primary key is long, the secondary indexes use
 more space.
 
 `InnoDB' compares `CHAR' and `VARCHAR' strings of different lengths
 such that the remaining length in the shorter string is treated as if
 padded with spaces.

Info Catalog

(mysql.info) innodb-multi-versioning

(mysql.info) innodb

(mysql.info) file-space-management

automatically generated byinfo2html

(mysql.info) table-and-index

Menu