mysql innodb monitor_利用innodb_table_monitor来trace innodb内部信息

Mysql不像ORACLE，提供各种各样的dump方式 ，可以了解数据库的各种内存结构，数据存储结构。mysql只有少量的一些trace方式，下面向大家介绍如何通过innodb_table_monitor来窥视innodb存储引擎表及其索引的存储方式。这篇文章，也纠正上一篇文章《INNODB与ORACLE单行存储长度对比》中存在的问题，上文中将oracle中一个table的大小实际上与mysql(table+primary index)进行了比较，这个比较本身就是不公平的，对大家的误导表示歉意。

创建数据库

create database dc_test

character set gbk;

创建测试表test1

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

创建一张没有主键的测试表test3

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

创建innodb_table_monitor表，通知innodb存储引擎将数据输出到/etc/my.cnf中定义的log-error文件里

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) 大;从索引字段上看，second 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)

test1表大小为5991 pages,与show table status like ‘test1′中的Data_length相比，数据计算证明完全相同

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)

觉得文章有用？立即：

和朋友一起 共学习 共进步！

猜想失败，您看看下面的文章有用吗？