[ 丹臣]利用innodb_table_monitor來trace innodb內部資訊

mysql> show variables like '%per_table%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | OFF   |
+-----------------------+-------+
1 row in set (0.00 sec)

CREATE TABLE `test1` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(50) NOT NULL,
  `user_id` int(11) NOT NULL,
  `gmt_create` datetime NOT NULL,
  `gmt_modified` datetime NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `idx_test1_userid` (`user_id`,`gmt_create`)
) ENGINE=InnoDB  DEFAULT CHARSET=gbk

mysql> insert into test1(title,user_id,gmt_create,gmt_modified) values('this is a test',1001,now(),now());
Query OK, 1 row affected (0.00 sec)

mysql> insert into test1(title,user_id,gmt_create,gmt_modified) select title,user_id,gmt_create,gmt_modified from test1;
Query OK, 786432 rows affected (13.65 sec)
Records: 786432  Duplicates: 0  Warnings: 0

mysql> select count(*) from test1;
+----------+
| count(*) |
+----------+
|  1572864 |
+----------+
1 row in set (0.59 sec)

CREATE TABLE `test2` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(50) NOT NULL,
  `user_id` int(11) NOT NULL,
  `gmt_create` datetime NOT NULL,
  `gmt_modified` datetime NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk

CREATE TABLE `test3` (
  `id` int(11) NOT NULL,
  `title` varchar(50) NOT NULL,
  `user_id` int(11) NOT NULL,
  `gmt_create` datetime NOT NULL,
  `gmt_modified` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk

mysql> create table innodb_table_monitor(a int) engine=innodb;
Query OK, 0 rows affected (0.05 sec)

===========================================
090623  8:15:25 INNODB TABLE MONITOR OUTPUT
===========================================
--------------------------------------
TABLE: name dc_test/innodb_table_monitor, id 0 24, columns 5, indexes 1, appr.rows 0
  COLUMNS: a: DATA_INT len 4 prec 0; DB_ROW_ID: DATA_SYS prtype 256 len 6 prec 0; DB_TRX_ID: DATA_SYS prtype 257 len 6 prec 0; DB_ROLL_PTR: DATA_SYS prtype 258 len 7 prec 0;
  INDEX: name GEN_CLUST_INDEX, id 0 28, fields 0/4, type 1
   root page 50, appr.key vals 0, leaf pages 1, size pages 1
   FIELDS:  DB_ROW_ID DB_TRX_ID DB_ROLL_PTR a
   --
  
--------------------------------------
TABLE: name dc_test/test1, id 0 22, columns 9, indexes 2, appr.rows 1573185
  COLUMNS: id: DATA_INT len 4 prec 0; title: type 12 len 100 prec 0; user_id: DATA_INT len 4 prec 0; gmt_create: DATA_INT len 8 prec 0; gmt_modified: DATA_INT len 8 prec 0; DB_ROW_ID: DATA_SYS prtype 256 len 6 prec 0; DB_TRX_ID: DATA_SYS prtype 257 len 6 prec 0; DB_ROLL_PTR: DATA_SYS prtype 258 len 7 prec 0;
  INDEX: name PRIMARY, id 0 25, fields 1/7, type 3
   root page 57, appr.key vals 1573185, leaf pages 5959, size pages 5991  --注意這個值
   FIELDS:  id DB_TRX_ID DB_ROLL_PTR title user_id gmt_create gmt_modified
  INDEX: name idx_test1_userid, id 0 26, fields 2/3, type 0
   root page 62, appr.key vals 9, leaf pages 2093, size pages 2279
   FIELDS:  user_id gmt_create id   
   --主鍵與表的資料是存放在一起的,所以primary index(size pages 5991) 比 second index(size pages 2279) 大;從索引欄位上看，secondary index(idx_test1_userid)包含primary indexed columns(id)
--------------------------------------
TABLE: name dc_test/test2, id 0 14, columns 9, indexes 1, appr.rows 0
  COLUMNS: id: DATA_INT len 4 prec 0; title: type 12 len 100 prec 0; user_id: DATA_INT len 4 prec 0; gmt_create: DATA_INT len 8 prec 0; gmt_modified: DATA_INT len 8 prec 0; DB_ROW_ID: DATA_SYS prtype 256 len 6 prec 0; DB_TRX_ID: DATA_SYS prtype 257 len 6 prec 0; DB_ROLL_PTR: DATA_SYS prtype 258 len 7 prec 0;
  INDEX: name PRIMARY, id 0 16, fields 1/7, type 3
   root page 63, appr.key vals 0, leaf pages 1, size pages 1
   FIELDS:  id DB_TRX_ID DB_ROLL_PTR title user_id gmt_create gmt_modified
   --主鍵與表的資料是存放在一起的
  
--------------------------------------
TABLE: name dc_test/test3, id 0 17, columns 9, indexes 1, appr.rows 0
  COLUMNS: id: DATA_INT len 4 prec 0; title: type 12 len 100 prec 0; user_id: DATA_INT len 4 prec 0; gmt_create: DATA_INT len 8 prec 0; gmt_modified: DATA_INT len 8 prec 0; DB_ROW_ID: DATA_SYS prtype 256 len 6 prec 0; DB_TRX_ID: DATA_SYS prtype 257 len 6 prec 0; DB_ROLL_PTR: DATA_SYS prtype 258 len 7 prec 0;
  INDEX: name GEN_CLUST_INDEX, id 0 19, fields 0/8, type 1
   root page 213, appr.key vals 0, leaf pages 1, size pages 1
   FIELDS:  DB_ROW_ID DB_TRX_ID DB_ROLL_PTR id title user_id gmt_create gmt_modified
   --在建立表時，沒有定義主鍵，innodb會在內部建立一個主鍵索引
----------------------------------
END OF INNODB TABLE MONITOR OUTPUT
==================================

DB_TRX_ID - this field is managed by InnoDB internally and contains a ID of transaction which changed a record last time
DB_ROLL_PTR - one more internal InnoDB field (TODO: find out what is it used for).
DB_ROW_ID - this internally used field should be the first field in tables without primary keys (it is an auto-increment field used by InnoDB to identify rows in such tables)

mysql> show table status like 'test1'\G
*************************** 1. row ***************************
           Name: test1
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 1573185  --這個是抽樣值，不是精確值
 Avg_row_length: 62
    Data_length: 98156544
Max_data_length: 0
   Index_length: 37339136
      Data_free: 0
 Auto_increment: 1572865
    Create_time: 2009-06-22 12:37:17
    Update_time: NULL
     Check_time: NULL
      Collation: gbk_chinese_ci
       Checksum: NULL
 Create_options:
        Comment: InnoDB free: 871424 kB
1 row in set (0.00 sec)

mysql> select 5991*16*1024;
+--------------+
| 5991*16*1024 |
+--------------+
|     98156544 |
+--------------+
1 row in set (0.00 sec)