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.4, “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 isGEN_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 aCREATE 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 withtype
value 0 is neither clustered nor unique. The flag values are defined in theinnobase/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
andDB_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.