Mysql不像ORACLE,提供各种各样的dump方式 ,可以了解数据库的各种内存结构,数据存储结构。mysql只有少量的一些trace方式,下面向大家介绍如何通过innodb_table_monitor来窥视innodb存储引擎表及其索引的存储方式。这篇文章,也纠正上一篇文章《INNODB与ORACLE单行存储长度对比》存在的问题,上文中将oracle中一个table的大小实际上与mysql(table+primary index)进行了比较,这个比较本身就是不公平的,对大家的误导表示歉意。
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | OFF |
+-----------------------+-------+
1 row in set (0.00 sec)
创建数据库
create database dc_test
character set gbk;
创建测试表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
重复写入数据
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)
创建一张只有主键的测试表
`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
`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文件里
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_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相比,数据计算证明完全相同
*************************** 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)
参考文档:
http://code.google.com/p/innodb-tools/wiki/InnodbRecoveryHowto
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/1384/viewspace-611335/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/1384/viewspace-611335/