MySQL 5.6 Reference Manual Including MySQL NDB Cluster 7.3-7.4 Reference Guide
The InnoDB
Table Monitor is deprecated and
may be removed in a future release. Similar information can be
obtained from InnoDB
INFORMATION_SCHEMA
tables. See
Section 21.30, “INFORMATION_SCHEMA InnoDB Tables”.
The InnoDB
Table Monitor prints the contents of
the InnoDB
internal data dictionary.
The output contains one section per table. The
SYS_FOREIGN
and
SYS_FOREIGN_COLS
sections are for internal data
dictionary tables that maintain information about foreign keys.
There are also sections for the Table Monitor table and each
user-created InnoDB
table. Suppose that the
following two tables have been created in the
test
database:
CREATE TABLE parent ( par_id INT NOT NULL, fname CHAR(20), lname CHAR(20), PRIMARY KEY (par_id), UNIQUE INDEX (lname, fname) ) ENGINE = INNODB; CREATE TABLE child ( par_id INT NOT NULL, child_id INT NOT NULL, name VARCHAR(40), birth DATE, weight DECIMAL(10,2), misc_info VARCHAR(255), last_update TIMESTAMP, PRIMARY KEY (par_id, child_id), INDEX (name), FOREIGN KEY (par_id) REFERENCES parent (par_id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE = INNODB;
Then the Table Monitor output looks something like this (reformatted slightly):
=========================================== 090420 12:09:32 INNODB TABLE MONITOR OUTPUT =========================================== -------------------------------------- TABLE: name SYS_FOREIGN, id 0 11, columns 7, indexes 3, appr.rows 1 COLUMNS: ID: DATA_VARCHAR DATA_ENGLISH len 0; FOR_NAME: DATA_VARCHAR DATA_ENGLISH len 0; REF_NAME: DATA_VARCHAR DATA_ENGLISH len 0; N_COLS: DATA_INT len 4; DB_ROW_ID: DATA_SYS prtype 256 len 6; DB_TRX_ID: DATA_SYS prtype 257 len 6; INDEX: name ID_IND, id 0 11, fields 1/6, uniq 1, type 3 root page 46, appr.key vals 1, leaf pages 1, size pages 1 FIELDS: ID DB_TRX_ID DB_ROLL_PTR FOR_NAME REF_NAME N_COLS INDEX: name FOR_IND, id 0 12, fields 1/2, uniq 2, type 0 root page 47, appr.key vals 1, leaf pages 1, size pages 1 FIELDS: FOR_NAME ID INDEX: name REF_IND, id 0 13, fields 1/2, uniq 2, type 0 root page 48, appr.key vals 1, leaf pages 1, size pages 1 FIELDS: REF_NAME ID -------------------------------------- TABLE: name SYS_FOREIGN_COLS, id 0 12, columns 7, indexes 1, appr.rows 1 COLUMNS: ID: DATA_VARCHAR DATA_ENGLISH len 0; POS: DATA_INT len 4; FOR_COL_NAME: DATA_VARCHAR DATA_ENGLISH len 0; REF_COL_NAME: DATA_VARCHAR DATA_ENGLISH len 0; DB_ROW_ID: DATA_SYS prtype 256 len 6; DB_TRX_ID: DATA_SYS prtype 257 len 6; INDEX: name ID_IND, id 0 14, fields 2/6, uniq 2, type 3 root page 49, appr.key vals 1, leaf pages 1, size pages 1 FIELDS: ID POS DB_TRX_ID DB_ROLL_PTR FOR_COL_NAME REF_COL_NAME -------------------------------------- TABLE: name test/child, id 0 14, columns 10, indexes 2, appr.rows 201 COLUMNS: par_id: DATA_INT DATA_BINARY_TYPE DATA_NOT_NULL len 4; child_id: DATA_INT DATA_BINARY_TYPE DATA_NOT_NULL len 4; name: DATA_VARCHAR prtype 524303 len 40; birth: DATA_INT DATA_BINARY_TYPE len 3; weight: DATA_FIXBINARY DATA_BINARY_TYPE len 5; misc_info: DATA_VARCHAR prtype 524303 len 255; last_update: DATA_INT DATA_UNSIGNED DATA_BINARY_TYPE DATA_NOT_NULL len 4; DB_ROW_ID: DATA_SYS prtype 256 len 6; DB_TRX_ID: DATA_SYS prtype 257 len 6; INDEX: name PRIMARY, id 0 17, fields 2/9, uniq 2, type 3 root page 52, appr.key vals 201, leaf pages 5, size pages 6 FIELDS: par_id child_id DB_TRX_ID DB_ROLL_PTR name birth weight misc_info last_update INDEX: name name, id 0 18, fields 1/3, uniq 3, type 0 root page 53, appr.key vals 210, leaf pages 1, size pages 1 FIELDS: name par_id child_id FOREIGN KEY CONSTRAINT test/child_ibfk_1: test/child ( par_id ) REFERENCES test/parent ( par_id ) -------------------------------------- TABLE: name test/innodb_table_monitor, id 0 15, columns 4, indexes 1, appr.rows 0 COLUMNS: i: DATA_INT DATA_BINARY_TYPE len 4; DB_ROW_ID: DATA_SYS prtype 256 len 6; DB_TRX_ID: DATA_SYS prtype 257 len 6; INDEX: name GEN_CLUST_INDEX, id 0 19, fields 0/4, uniq 1, type 1 root page 193, appr.key vals 0, leaf pages 1, size pages 1 FIELDS: DB_ROW_ID DB_TRX_ID DB_ROLL_PTR i -------------------------------------- TABLE: name test/parent, id 0 13, columns 6, indexes 2, appr.rows 299 COLUMNS: par_id: DATA_INT DATA_BINARY_TYPE DATA_NOT_NULL len 4; fname: DATA_CHAR prtype 524542 len 20; lname: DATA_CHAR prtype 524542 len 20; DB_ROW_ID: DATA_SYS prtype 256 len 6; DB_TRX_ID: DATA_SYS prtype 257 len 6; INDEX: name PRIMARY, id 0 15, fields 1/5, uniq 1, type 3 root page 50, appr.key vals 299, leaf pages 2, size pages 3 FIELDS: par_id DB_TRX_ID DB_ROLL_PTR fname lname INDEX: name lname, id 0 16, fields 2/3, uniq 2, type 2 root page 51, appr.key vals 300, leaf pages 1, size pages 1 FIELDS: lname fname par_id FOREIGN KEY CONSTRAINT test/child_ibfk_1: test/child ( par_id ) REFERENCES test/parent ( par_id ) ----------------------------------- END OF INNODB TABLE MONITOR OUTPUT ==================================
For each table, Table Monitor output contains a section that displays general information about the table and specific information about its columns, indexes, and foreign keys.
The general information for each table includes the table name (in
format except for internal tables), its ID, the number of columns
and indexes, and an approximate row count.
db_name
/tbl_name
The COLUMNS
part of a table section lists each
column in the table. Information for each column indicates its
name and data type characteristics. Some internal columns are
added by InnoDB
, such as
DB_ROW_ID
(row ID),
DB_TRX_ID
(transaction ID), and
DB_ROLL_PTR
(a pointer to the rollback/undo
data).
DATA_
xxx
These symbols indicate the data type. There may be multiple
DATA_
symbols
for a given column.
xxx
prtype
The column's “precise” type. This field includes
information such as the column data type, character set code,
nullability, signedness, and whether it is a binary string.
This field is described in the
innobase/include/data0type.h
source file.
len
The column length in bytes.
Each INDEX
part of the table section provides
the name and characteristics of one table index:
name
The index name. If the name is PRIMARY
, the
index is a primary key. If the name is
GEN_CLUST_INDEX
, the index is the clustered
index that is created automatically if the table definition
doesn't include a primary key or non-NULL
unique index. See Section 14.6.2.1, “Clustered and Secondary Indexes”.
id
The index ID.
fields
The number of fields in the index, as a value in
format:
m
/n
m
is the number of user-defined
columns; that is, the number of columns you would see in
the index definition in a CREATE TABLE
statement.
n
is the total number of index
columns, including those added internally. For the
clustered index, the total includes the other columns in
the table definition, plus any columns added internally.
For a secondary index, the total includes the columns from
the primary key that are not part of the secondary index.
uniq
The number of leading fields that are enough to determine index values uniquely.
type
The index type. This is a bit field. For example, 1 indicates
a clustered index and 2 indicates a unique index, so a
clustered index (which always contains unique values), has the
type
value 3. An index with
type
value 0 is neither clustered nor
unique. The flag values are defined in the
innobase/include/dict0mem.h
source file.
root page
The index root page number.
appr. key vals
The approximate index cardinality.
leaf pages
The approximate number of leaf pages in the index.
size pages
The approximate total number of pages in the index.
FIELDS
The names of the fields in the index. For a clustered index
that was generated automatically, the field list begins with
the internal DB_ROW_ID
(row ID) field.
DB_TRX_ID
and
DB_ROLL_PTR
are always added internally to
the clustered index, following the fields that comprise the
primary key. For a secondary index, the final fields are those
from the primary key that are not part of the secondary index.
The end of the table section lists the FOREIGN
KEY
definitions that apply to the table. This
information appears whether the table is a referencing or
referenced table.