浅析分析mysql表空间

4etfeww

1.1.   分析表空间

[iyunv@gflinuxpy_innodb_page_info]# ./py_innodb_page_info.py -v  

/opt/mysql/mysql/test/mytest.ibd

pageoffset 00000000, page type <File Space Header>

pageoffset 00000001, page type <Insert Buffer Bitmap>

pageoffset 00000002, page type <File Segment inode>

pageoffset 00000003, page type <B-tree Node>, page level <0000>

pageoffset 00000000, page type <Freshly Allocated Page>

pageoffset 00000000, page type <Freshly Allocated Page>

Totalnumber of page: 6:

FreshlyAllocated Page: 2

InsertBuffer Bitmap: 1

FileSpace Header: 1

B-treeNode: 1

FileSegment inode: 1

[iyunv@gflinuxpy_innodb_page_info]#

mysql>show table status like 'mytest'G;

***************************1. row ***************************

           Name: mytest

         Engine: InnoDB

        Version: 10

     Row_format: Compact

           Rows: 3

Avg_row_length: 5461

    Data_length: 16384

Max_data_length:0

   Index_length: 0

      Data_free: 0

Auto_increment: NULL

    Create_time: 2014-08-05 16:31:06

    Update_time: NULL

     Check_time: NULL

      Collation: latin1_swedish_ci

       Checksum: NULL

Create_options: row_format=COMPACT

        Comment:

1 row inset (0.00 sec)

1.1.1.      compact行记录格式

变长字段长度列表	NULL标志位	记录头信息	列1	列2	...	事务id	回滚指针	Rowid
列的顺序逆序占1字节，最大2字节（>255）	1表示行有null；0表示无	占用5字节，40位				6字节	7字节	6字节

         null   除了占用null标识位，不占用任何空间。

         每行数据除了用户定义的列外，还有两个隐藏列，事务id列和回滚指针列；如果表没有定义主键，每行还会增加一个6字节的rowid列。

mysql>desc mytest;

+-------+-------------+------+-----+---------+-------+

| Field |Type        | Null | Key | Default |Extra |

+-------+-------------+------+-----+---------+-------+

| t1    | varchar(10) | YES  |     |NULL    |       |

| t2    | varchar(10) | YES  |     |NULL    |       |

| t3    | char(10)    | YES |     | NULL    |      |

| t4    | varchar(10) | YES  |     |NULL    |       |

+-------+-------------+------+-----+---------+-------+

4 rows inset (0.01 sec)

mysql>select * from mytest;

+------+------+------+------+

| t1   | t2  | t3   | t4   |

+------+------+------+------+

| a    | bb  | bb   | ccc  |

| d    | ee  | ee   | fff  |

| d    | NULL | NULL | fff  |

+------+------+------+------+

3 rows inset (0.00 sec)

         打开表空间文件方式：

[iyunv@gflinuxpy_innodb_page_info]# ./py_innodb_page_info.py -C -v  

/opt/mysql/mysql/test/mytest.ibd> mytest.txt

0000c070  73 75 70 72 65 6d 75 6d  03 02 01 00 00 00 10 00  |supremum........|

0000c080  2c 00 00 00 3e 75 00 00 00 00 83 ba 6b e3 00 00 |,...>u......k...|

0000c090  03 ca 01 10 61 62 62 62  62 20 20 20 20 2020 20  |....abbbb       |

0000c0a0  20 63 63 6303 02 01 00  00 00 18 00 2b 00 00 00  | ccc........+...|

0000c0b0  3e 75 01 00 00 00 83 ba  6c e4 00 00 03 44 01 10  |>u......l....D..|

0000c0c0  64 65 65 65 65 20 20 20  20 20 20 20 20 66 66 66  |deeee       fff|

0000c0d0  03 01 06 00 00 20 ff 98  00 00 00 3e 75 02 00 00  |..... .....>u...|

0000c0e0  00 83 ba 6d e5 00 00 04  24 01 10 64 66 66 66 00  |...m....$..dfff.|

03 02 01/*变长字段长度列表，逆序*/

00/*null标识位，第一行没有null值*/

00 00 10 00 2c/*固定五字节长度*/

00 00 00 3e 75 00/*rowid自动创建，6字节*/

00  00 00 83 ba 6b/*事务id，6字节*/

e3 00 00 03 ca 01 10/*回滚指针，7字节*/

61/*列1数据a*/

62 62/*列2数据bb*/

62 62 20 20 20 20 20 20 20 20/*列3数据bb*/

63 63 63/*列4数据ccc*/

1.1.2.      redundant行记录格式

         redundant是mysql5.0之前innodb行记录存储方式。

变长字段偏移列表	NULL标志位	记录头信息	列1	列2	...	事务id	回滚指针	Rowid
列的顺序逆序占1字节，最大2字节（>255）	1表示行有null；0表示无	占用6字节，48位				6字节	7字节	6字节

mysql>create table mytest2 engine=innodb row_format=redundant as select * frommytest;

Query OK,3 rows affected (0.04 sec)

Records:3  Duplicates: 0  Warnings: 0

mysql>show table status like 'mytest2'G;

***************************1. row ***************************

           Name: mytest2

         Engine: InnoDB

        Version: 10

     Row_format: Redundant

           Rows: 3

Avg_row_length: 5461

    Data_length: 16384

Max_data_length:0

   Index_length: 0

      Data_free: 0

Auto_increment: NULL

    Create_time: 2014-08-06 10:24:20

    Update_time: NULL

     Check_time: NULL

      Collation: latin1_swedish_ci

       Checksum: NULL

Create_options: row_format=REDUNDANT

        Comment:

1 row inset (0.00 sec)

mysql>

mysql>select * from mytest2;

+------+------+------+------+

| t1   | t2  | t3   | t4   |

+------+------+------+------+

| a    | bb  | bb   | ccc  |

| d    | ee  | ee   | fff  |

| d    | NULL | NULL | fff  |

+------+------+------+------+

3 rows inset (0.00 sec)

[iyunv@gflinux test]# hexdump -C -vmytest2.ibd > mytest2.txt

0000c070  08 03 00 00 73 75 70 72  65 6d 75 6d 00 23 20 16  |....supremum.# .|

0000c080  14 13 0c 06 00 00 10 0f  00 ba00 00 00 3e 75 03  |.............>u.|

0000c090  00 0000 83 ba 74 e800  00 03 9f 01 10 61 6262  |.....t.......abb|

0000c0a0  62 62 20 20 20 20 20 20 20 20 63 63 63 23 20 16 |bb        ccc# .|

0000c0b0  14 13 0c 06 00 00 18 0f  00 ea 00 00 00 3e 75 04  |.............>u.|

0000c0c0  00 00 00 83 ba 74 e8 00  00 03 9f 01 1f 64 65 65  |.....t.......dee|

0000c0d0  65 65 20 20 20 20 20 20  20 20 66 66 66 21 9e 94  |ee       fff!..|

0000c0e0  14 13 0c 06 00 00 20 0f  00 74 00 00 00 3e 75 05  |...... ..t...>u.|

0000c0f0  00 00 00 83 ba 74 e8 00  00 03 9f 01 2e 64 00 00  |.....t.......d..|

0000c100  00 00 00 00 00 00 00 00  66 66 66 00 00 00 00 00  |........fff.....|

23 20 16 1413 0c 06/*长度偏移表，逆序*/

00 00 100f  00 ba /*固定六字节长度*/

00 00 00 3e 75 00/*rowid自动创建，6字节*/

00 00 0083 ba 74/*事务id，6字节*/

e8 00  00 03 9f 01 10/*回滚指针，7字节*/

61/*列1数据a*/

62 62/*列2数据bb*/

62 62 20 20 20 20 20 20 20 20/*列3数据bb*/

63 63 63/*列4数据ccc*/

         对于redundant行记录格式，varchar类型的null值不占用任何空间，char类型的null占用空间。

mysql>show variables like '%sql_mode%';

+---------------+------------------------+

|Variable_name | Value                  |

+---------------+------------------------+

|sql_mode      | NO_ENGINE_SUBSTITUTION |

+---------------+------------------------+

1 row inset (0.00 sec)

#不设置该参数为严格模式，有些表可以建立，但是会抛出warnings，自动将varchar类型转#换为text类型。