mysql innodb monitor_MySQL innodb_table_monitor 解析

背景:

用innodb_table_monitor来查看表内部的存储信息和索引结构是一个好的办法。再之前的MySQL 字符串主键和整型主键分析中提到了一些内容,但没有细讲,现在来好好的分析下。

使用方法:建立一张 innodb_table_monitor的表:

create table innodb_table_monitor(a int)engine=innodb;

表建立之后,会每隔1m20s间隔把监控到的信息写到error日志中。要是停止监控的话,只需要删除表就可以了。注意:要是一直开启的话,错误日志会变的非常大。

日志的具体信息:

48304ba5e6f9fe08f3fa1abda7d326ab.png

===========================================

121103 10:25:57 INNODB TABLE MONITOR OUTPUT

===========================================

--------------------------------------

TABLE: name SYS_FOREIGN, id 0 11, 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 256 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 11, fields 1/6, uniq 1, type 3

root page 46, appr.key vals 8, 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 4, 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 3, leaf pages 1, size pages 1

FIELDS: REF_NAME ID

--------------------------------------

TABLE: name SYS_FOREIGN_COLS, id 0 12, 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 256 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 14, fields 2/6, uniq 2, type 3

root page 49, appr.key vals 8, leaf pages 1, size pages 1

FIELDS: ID POS DB_TRX_ID DB_ROLL_PTR FOR_COL_NAME REF_COL_NAME

--------------------------------------

48304ba5e6f9fe08f3fa1abda7d326ab.png

上面显示,其中输出结构的开始部分会包含2张内部数据字典表用于维护外键的信息,表名为SYS_FOREIGN、SYS_FOREIGN_COLS。看看用户创建表的信息:

1,有主键的表(有数据):

48304ba5e6f9fe08f3fa1abda7d326ab.png

CREATE TABLE `test` (

`uid` char(36) NOT NULL DEFAULT '',

`id` int(11) 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

48304ba5e6f9fe08f3fa1abda7d326ab.png

48304ba5e6f9fe08f3fa1abda7d326ab.png

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 256 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, leaf pages 4056, size pages 4078

5 FIELDS: uid DB_TRX_ID DB_ROLL_PTR id name status /*主键包含所有列*/

6 INDEX: name idx_id, id 0 1388, fields 1/2, uniq 2, type 0

7 root page 4, appr.key vals 638241, leaf pages 1834, size pages 1896

8 FIELDS: id uid /*表中定义的二级索引包含主键uid列*/

48304ba5e6f9fe08f3fa1abda7d326ab.png

第一部分(1):表信息:表名(test),表ID(718),列数(7),索引数(2),索性的基数(625787,会变动);

第二部分(2):列信息:包括列名、列类型(DATA_XXXX)、是否NULL、以及列字段的长度。后面还有额外三列(主键ID,事务ID,回滚子指针) 总共7列;

• DATA_xxx(列类型): These symbols indicate the data type. There may be multiple DATA_xxx symbols for a given column.

• 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.

• prec(类型的精确值):The precision of the type.

第三部分(3~5):索引信息:索引名、索引ID,fields m/n (m代表用户定义索引中的列数/n代表总的索引列数,其中包含附加的internal columns),由于没有显示的定义主键或者非空的唯一索引,InnoDB会建表的时候自动的创建名字为GEN_CLUST_INDEX的Clustered Index。如果显示的定义一个主键的话,这个时候INDEX中name的值是:PRIMARY。以及 一些page信息和索引的基数信息。最后则是被索引的列,和 fields m/n 里的n对应。

48304ba5e6f9fe08f3fa1abda7d326ab.png

• type(索引类型:聚集索引1,唯一索引2,普通索引0,既聚集又唯一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 unique values), will have a type value of 3. An index with a type value of 0 is neither clustered nor unique. The flag values are defined in the innobase/include/dict0mem.h source file.

• root page(索引节点:根节点): The index root page number.

• appr. key vals(索引的基数): The approximate index cardinality.

• leaf page(页节点大小): number of leaf pages in the index.

• size pages(总页大小): The approximages: The approate 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 and DB_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.

48304ba5e6f9fe08f3fa1abda7d326ab.png

2,上面的表是有主键的,看看无主键的表(没有数据):

48304ba5e6f9fe08f3fa1abda7d326ab.png

CREATE TABLE `test1` (

`uid` char(36) NOT NULL DEFAULT '',

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(255) DEFAULT NULL,

`status` tinyint(4) DEFAULT NULL,

KEY `idx_id` (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

48304ba5e6f9fe08f3fa1abda7d326ab.png

48304ba5e6f9fe08f3fa1abda7d326ab.png

1 TABLE: name test/test1, id 0 820, flags 1, columns 7, indexes 2, appr.rows 0

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 256 len 6; DB_TRX_ID: DATA_SYS prtype 257 len 6; DB_ROLL_PTR: DATA_SYS prtype 258 len 7;

3 INDEX: name GEN_CLUST_INDEX, id 0 1524, fields 0/7, uniq 1, type 1 /*1 说明只是主键,不是唯一*/

4 root page 3, appr.key vals 0, leaf pages 1, size pages 1

5 FIELDS: DB_ROW_ID DB_TRX_ID DB_ROLL_PTR uid id name status

6 INDEX: name idx_id, id 0 1525, fields 1/2, uniq 2, type 0

7 root page 4, appr.key vals 0, leaf pages 1, size pages 1

8 FIELDS: id DB_ROW_ID

48304ba5e6f9fe08f3fa1abda7d326ab.png

1和2对比异同:

第一行和第二行他们的信息是一样的。

第三行出现不一致,因为1的表是有主键的,而2的表没有主键,2会自动生成一个Row_ID来代替,而导致第五行也不一致,因为2比1多了一个Row_ID(6个字节)。所以2的表中被索引的列有7个,而1只有6个,2没有定义索引列,而1定义了一个(fields)。因为二级索引包含主键列(第8行),主键不一样了,导致二级索引的列也会不一样。

3,测试有没有主键表大小问题:

48304ba5e6f9fe08f3fa1abda7d326ab.png

CREATE TABLE `test2` (

`uid` char(36) NOT NULL DEFAULT '',

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(255) DEFAULT NULL,

`status` tinyint(4) DEFAULT NULL,

PRIMARY KEY `idx_id` (`id`)

) ENGINE=InnoDB; /*有自增主键*/

CREATE TABLE `test3` (

`uid` char(36) NOT NULL DEFAULT '',

`id` int(11) NOT NULL,

`name` varchar(255) DEFAULT NULL,

`status` tinyint(4) DEFAULT NULL

) ENGINE=InnoDB;/*无主键*/

CREATE TABLE `test4` (

`uid` char(36) NOT NULL DEFAULT '',

`id` int(11) NOT NULL,

`name` varchar(255) DEFAULT NULL,

`status` tinyint(4) DEFAULT NULL,

PRIMARY KEY (`uid`)

) ENGINE=InnoDB; /*有字符串主键*/

48304ba5e6f9fe08f3fa1abda7d326ab.png

48304ba5e6f9fe08f3fa1abda7d326ab.png

--------------------------------------

TABLE: name test/test2, id 0 824, flags 1, columns 7, indexes 1, appr.rows 625647

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 256 len 6; DB_TRX_ID: DATA_SYS prtype 257 len 6; DB_ROLL_PTR: DATA_SYS prtype 258 len 7;

INDEX: name PRIMARY, id 0 1532, fields 1/6, uniq 1, type 3

root page 3, appr.key vals 625647, leaf pages 4056, size pages 4070

FIELDS: id DB_TRX_ID DB_ROLL_PTR uid name status

--------------------------------------

TABLE: name test/test3, id 0 825, flags 1, columns 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 256 len 6; DB_TRX_ID: DATA_SYS prtype 257 len 6; DB_ROLL_PTR: DATA_SYS prtype 258 len 7;

INDEX: name GEN_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: DB_ROW_ID DB_TRX_ID DB_ROLL_PTR uid id name status

--------------------------------------

TABLE: name test/test4, id 0 826, flags 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 256 len 6; DB_TRX_ID: DATA_SYS prtype 257 len 6; DB_ROLL_PTR: DATA_SYS prtype 258 len 7;

INDEX: name PRIMARY, id 0 1534, fields 1/6, uniq 1, type 3

root page 3, appr.key vals 655560, leaf pages 4056, size pages 4078

FIELDS: uid DB_TRX_ID DB_ROLL_PTR id name status

--------------------------------------

48304ba5e6f9fe08f3fa1abda7d326ab.png

结果是:test2和test4大小一样,因为主键包含所有列。test3最大,因为每行都多了一个额外 row_id 列(6字节)。上面是针对表中只有主键进行测试的(比较特殊),要是有多个二级索引或则组合索引(常见),情况可能发生变化,特别是有随机字符串主键,原因请看这里

总结:

所以,从上面看出 innodb 都最好必须设置主键,而且是整型自增的。为什么不是字符串,请看这里。不设置主键没有任何好处:即使不设置主键也不会让表空间变小(除非主键需要新增列),反而使得插入更随机无序,可能导致IO更高。具体原因可以都通过innodb_table_monitor看出。

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值