MySQL Innodb_table_monitor Parsing

Background:
It is a good idea to use Innodb_table_monitor to view the stored information and index structure inside the table. Before the MySQL string primary key and integer primary key analysis mentioned some of the content, but not in detail, now come to a good analysis.

How to use:
Create a table of Innodb_table_monitor:

CREATE TABLE Innodb_table_monitor (a int) engine=innodb;

Once the table is established, the monitored information is written to the error log at every 1m20s interval. If you stop monitoring, you just need to delete the table . Note: The error log can become very large if it is turned on.

Specific information about the log:

===========================================121103 10:25:57 INNODB TABLE MONITOR output============================= ==============--------------------------------------Table:namesys_foreign, ID 0, flags 0, columns 7, indexes 3, appr.rows 8 COLUMNS:ID:DATA_VARCHAR prtype 524292 len 0; For_name:data_varchar Prtype 524292 Len 0; Ref_name:data_varchar Prtype 524292 Len 0; N_cols:data_int Len 4; Db_row_id:data_sys Prtype, Len 6; Db_trx_id:data_sys Prtype 257 Len 6;  Db_roll_ptr:data_sys Prtype 258 len 7; Index:name Id_ind, ID 0, Fields 1/6, Uniq 1, type 3 root page, Appr.key vals 8, leaf pages 1, size pages 1 FIEL Ds:id db_trx_id db_roll_ptr for_name ref_name n_cols index:name For_ind, ID 0, fields, uniq 2, type 0 root pa GE, Appr.key vals 4, leaf pages 1, size pages 1 fields:for_name ID index:name ref_ind, id 0, fields, Uniq 2, type 0 root page, appr.key vals 3, leaf pages 1, size pages 1 fields:ref_name ID------------------------------ --------Table:nameSys_foreign_cols, ID 0, flags 0, columns 7, indexes 1, appr.rows 8 COLUMNS:ID:DATA_VARCHAR prtype 524292 len 0; Pos:data_int Len 4; For_col_name:data_varchar Prtype 524292 Len 0; Ref_col_name:data_varchar Prtype 524292 Len 0; Db_row_id:data_sys Prtype, Len 6; Db_trx_id:data_sys Prtype 257 Len 6;  Db_roll_ptr:data_sys Prtype 258 len 7; Index:name Id_ind, ID 0, Fields 2/6, Uniq 2, type 3 root page, Appr.key vals 8, leaf pages 1, size pages 1 FIEL Ds:id POS db_trx_id db_roll_ptr for_col_name ref_col_name--------------------------------------

Shown above, where the output structure begins with 2 internal data dictionary tables for maintaining foreign key information, with the table named Sys_foreign, Sys_foreign_cols. Look at the user-created table information:

1, table with primary key (with data):

CREATE TABLE ' test ' (  ' uid ' char ($) NOT null DEFAULT ' ',  ' id ' int (one) ' NOT null auto_increment,  ' name ' varchar (255) default NULL,  ' status ' tinyint (4) default NULL,  PRIMARY key (' uid '),  key ' idx_id ' (' id ')) engine= InnoDB DEFAULT Charset=utf8

  1 table:name test/test, ID 0 718, flags 1, columns 7, Indexes 2, appr.rows 635787 2 COLUMNS:uid:DATA_MYSQL data_not_null Len 108; Id:data_int Data_binary_type data_not_null Len 4; Name:data_varmysql Len 765; Status:data_int Data_binary_type Len 1;  Db_row_id:data_sys Prtype-Len 6; Db_trx_id:data_sys Prtype 257 Len 6; Db_roll_ptr:data_sys Prtype 258 len 7;  3 Index:name  PRIMARY , id 0 1387, Fields 1/6, Uniq 1, type 3 4 root Page 3, Appr.key Vals 635787, L EAF pages 4056, size pages 4078 5 fields:  uid  db_trx_id db_roll_ptr ID name status  * * Primary key contains all columns */  

6 index:name idx_id, id 0 1388, fields, Uniq 2, type 0  
7 Root page 4, appr.key vals 638241, leaf pages 1834, size pages 1896  
/* The two-level index defined in the table contains the primary key UID column */

Part I (1): Table information: Table name (test), table ID (718), Number of columns (7), Number of indexes (2), base <Cardinality> (625787, will change);

Part II (2): Column information: Includes column name, column type (DATA_XXXX), NULL, and length of the column field. There are also three additional columns (primary key ID, transaction ID, rollback child pointer) in total 7 columns;

data_xxx (column type): These symbols indicate the DATA type. There may is multiple data_xxx symbols for a given column. Prtype (character set encoding for column data type, nullability, symbolization, and whether it is a binary string): The column ' s "prec Ise "type". This field includes information such as the column data type, character set code, nullability,signedness, and whether it I s a binary string. This field was described in the innobase/include/data0type.h source File. len (the length of the column field): The column in Bytes. prec (type Exact value): The precision of the type.

Part III: Index Information: index name, index Id,fields m/n (m represents the number of columns in the user-defined index/n represents the total number of indexed columns, which contains the additional internal columns), because there is no defined primary key or non-null unique index displayed. InnoDB will create the table automatically when the name is Gen_clust_index clustered Index. If the definition of a primary key is displayed, this time the value of name in index is:PRIMARY. As well as some page information and cardinality information for the index. The last is the indexed column, which corresponds to N in the fields m/n.

type(Index Type: Clustered index 1, unique index 2, normal index 0, both clustered and unique 3): 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 uni Que values), would have a type value of 3. An index with a type value of 0 is neither clustered nor unique. The flag values is defined in the innobase/include/dict0mem.h source File. root page (index node: root node): The index root page number. appr. Key Vals (cardinality of the index): The approximate index Cardinality. leaf page (page Node Size): Number of leaf pages in the Index. size pages (Total Page Size): The Approximages:the approate total number of pages in the Index. fields (indexed columns, primary key contains all columns and hidden columns, level Two index contains primary key): The names of the fields in the index. For a clustered index is generated automatically, the field list begins with the internal db_row_id (ROW ID) field. DB_TRX_ID and Db_roll_ptr is always added internally to the clustered index, following the fields that comprise the prima Ry Key. For a secondary index, the final fields is those from the primary key, which is not part of the secondary index.

2, the table above has a primary key, see the table without the primary key (no data):

CREATE TABLE ' test1 ' (  ' uid ' char (*) NOT null DEFAULT ' ',  ' id ' int (one) ' NOT null auto_increment,  ' name ' Varcha R (255) default NULL,  ' status ' tinyint (4) default NULL,  KEY ' idx_id ' (' id ')) engine=innodb default Charset=utf8

1 table:name test/test1, ID 0 820, Flags 1, Columns 7, indexes 2, appr.rows COLUMNS:uid:DATA_MYSQL data_not_null   Len 108; Id:data_int Data_binary_type data_not_null Len 4; Name:data_varmysql Len 765; Status:data_int Data_binary_type Len 1; Db_row_id:data_sys Prtype, Len 6; Db_trx_id:data_sys Prtype 257 Len 6; Db_roll_ptr:data_sys prtype 258 len 7;3   /*1 Description Just primary key, not unique */4    root Page 3, appr.key vals 0, leaf pages 1, s ize pages    :  db_row_id db_trx_id db_roll_ptr UID ID name status6   index:name idx_id, ID 0 152 5, fields, Uniq 2, type '    root page 4, appr.key vals 0, leaf pages 1, size pages    :  ID db_row_i D

Similarities and differences between 1 and 2:
The first and second lines of their information are the same.
The third row is inconsistent, because 1 of the table has a primary key, and 2 of the table does not have a primary key, 2 will automatically generate a row_id to replace, and cause the fifth row is not consistent, because 2:1 more than one row_id (6 bytes). So 2 of the tables are indexed in 7 columns, while 1 has 6, 2 does not define an index column, and 1 defines one (fields). Because the level two index contains the primary key column (line 8th), the primary key is different, and the columns of the level two index are different.

3, the test has no primary key table size problem:

CREATE TABLE ' test2 ' (  ' uid ' char (*) NOT null DEFAULT ' ',  ' id ' int (one) ' NOT null auto_increment,  ' name ' Varcha R (255) default NULL,  ' status ' tinyint (4) default NULL,/    * has self-increment primary key */CREATE TABLE ' test3 ' (  ' uid ') char ($) NOT null default ' ',  ' id ' int (one) not null,  ' name ' varchar (255) Default NULL,  ' status ' tinyint (4) D Efault NULL) Engine=innodb; / * No primary key * / CREATE TABLE ' test4 ' (  ' uid ' char ($) NOT null DEFAULT ' ',  ' id ' int (one) ' NOT null,  ' name ' varchar (255) Defau LT null,  ' status ' tinyint (4) DEFAULT null,/  * has string primary key * /

--------------------------------------table:name Test/test2, ID 0 824, Flags 1, Columns 7, indexes 1, appr.rows 625647 C OLUMNS:uid:DATA_MYSQL data_not_null Len 108; Id:data_int Data_binary_type data_not_null Len 4; Name:data_varmysql Len 765; Status:data_int Data_binary_type Len 1; Db_row_id:data_sys Prtype, Len 6; Db_trx_id:data_sys Prtype 257 Len 6;  Db_roll_ptr:data_sys Prtype 258 len 7; Index:namePRIMARY, ID 0 1532, fields 1/6, Uniq 1, type 3 root Page 3, appr.key vals 625647, leaf pages 4056, size pages 4070 fields:i D db_trx_id db_roll_ptr uid name status--------------------------------------table:name test/test3, ID 0 825, flags 1, CO Lumns 7, indexes 1, appr.rows 617020 COLUMNS:uid:DATA_MYSQL data_not_null Len 108; Id:data_int Data_binary_type data_not_null Len 4; Name:data_varmysql Len 765; Status:data_int Data_binary_type Len 1; Db_row_id:data_sys Prtype, Len 6; Db_trx_id:data_sys Prtype 257 Len 6;  Db_roll_ptr:data_sys Prtype 258 len 7; Index:nameGen_clust_index, ID 0 1533, Fields 0/7, Uniq 1, type 1 root Page 3, appr.key vals 617020, leaf pages 4311, size pages 4326 fields:d b_row_id db_trx_id db_roll_ptr UID ID name status--------------------------------------table:name Test/test4, ID 0 826, F Lags 1, Columns 7, indexes 1, appr.rows 655560 COLUMNS:uid:DATA_MYSQL data_not_null Len 108; Id:data_int Data_binary_type data_not_null Len 4; Name:data_varmysql Len 765; Status:data_int Data_binary_type Len 1; Db_row_id:data_sys Prtype, Len 6; Db_trx_id:data_sys Prtype 257 Len 6;  Db_roll_ptr:data_sys Prtype 258 len 7; Index:namePRIMARY, ID 0 1534, fields 1/6, Uniq 1, type 3 root Page 3, appr.key vals 655560, leaf pages 4056, size pages 4078 fields:u ID db_trx_id db_roll_ptr ID name Status--------------------------------------

MySQL Innodb_table_monitor parsing