行格式为Compact是如何存储大数据的:
目前创新互联已为近千家的企业提供了网站建设、域名、网页空间、网站改版维护、企业网站设计、永定网站维护等服务,公司将坚持客户导向、应用为本的策略,正道将秉承"和谐、参与、激情"的文化,与客户和合作伙伴齐心协力一起成长,共同发展。
[vb] view plain copy
mysql select version();
+-----------+
| version() |
+-----------+
| 5.1.73 |
+-----------+
1 row in set (0.01 sec)
mysql show table status like 'row'\G;
*************************** 1. row ***************************
Name: row
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 1
Avg_row_length: 81920
Data_length: 81920
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: 2017-01-04 21:46:02
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
我们建立一张测试表,插入数据:
[html] view plain copy
CREATE TABLE `row` (
`content` varchar(65532) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=latin1
mysql insert into row(content) select repeat('a',65532);
Query OK, 1 row affected (0.03 sec)
Records: 1 Duplicates: 0 Warnings: 0
我们使用 py_innodb_page_info.py 工具来查看表中的页分布:
[vb] view plain copy
[root@localhost mysql]# python py_innodb_page_info.py -v com/row.ibd
page offset 00000000, page type File Space Header
page offset 00000001, page type Insert Buffer Bitmap
page offset 00000002, page type File Segment inode
page offset 00000003, page type B-tree Node, page level 0000
page offset 00000004, page type Uncompressed BLOB Page
page offset 00000005, page type Uncompressed BLOB Page
page offset 00000006, page type Uncompressed BLOB Page
page offset 00000007, page type Uncompressed BLOB Page
Total number of page: 8:
Insert Buffer Bitmap: 1
Uncompressed BLOB Page: 4
File Space Header: 1
B-tree Node: 1
File Segment inode: 1
可以看出,第4页的 B-tree Node, page level 0000 格式为数据页,存放着MySQL的行数据。 Uncompressed BLOB Page 可以理解为MySQL存放大数据的地方,暂且叫作外部存储页。Compact格式没有将大数据全部放在数据页中,而是将一部分数据放在了外部存储页中。那么,是全部数据在外部存储页中,还是一部分数据。假如是一部分数据,这一部分是多少呢?
我们使用 hexdump -Cv row.ibd 查看一下数据页 B-tree Node, page level 0000 ,也就是第4页:
[vb] view plain copy
3073 0000c000 8c 25 17 57 00 00 00 03 ff ff ff ff ff ff ff ff |.%.W....????????|
3074 0000c010 00 00 00 00 00 07 3a b8 45 bf 00 00 00 00 00 00 |......:?E?......|
3075 0000c020 00 00 00 00 00 02 00 02 03 a6 80 03 00 00 00 00 |.........?......|
3076 0000c030 00 7f 00 05 00 00 00 01 00 00 00 00 00 00 00 00 |................|
3077 0000c040 00 00 00 00 00 00 00 00 00 13 00 00 00 02 00 00 |................|
3078 0000c050 00 02 00 f2 00 00 00 02 00 00 00 02 00 32 01 00 |...?.........2..|
3079 0000c060 02 00 1c 69 6e 66 69 6d 75 6d 00 02 00 0b 00 00 |...infimum......|
3080 0000c070 73 75 70 72 65 6d 75 6d 14 c3 00 00 10 ff f1 00 |supremum.?...??.|
3081 0000c080 00 00 00 04 03 00 00 00 00 13 12 80 00 00 00 2d |...............-|
3082 0000c090 01 10 61 61 61 61 61 61 61 61 61 61 61 61 61 61 |..aaaaaaaaaaaaaa|
3083 0000c0a0 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 |aaaaaaaaaaaaaaaa|
3084 0000c0b0 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 |aaaaaaaaaaaaaaaa|
3085 0000c0c0 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 |aaaaaaaaaaaaaaaa|
....
....
3128 0000c370 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 |aaaaaaaaaaaaaaaa|
3129 0000c380 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 |aaaaaaaaaaaaaaaa|
3130 0000c390 61 61 00 00 00 02 00 00 00 04 00 00 00 26 00 00 |aa.............|
3131 0000c3a0 00 00 00 00 fc fc 00 00 00 00 00 00 00 00 00 00 |....??..........|
3132 0000c3b0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|
3133 0000c3c0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|
3134 0000c3d0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|
...
...
4093 0000ffc0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|
4094 0000ffd0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|
4095 0000ffe0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|
4096 0000fff0 00 00 00 00 00 70 00 63 01 a1 6c 2b 00 07 3a b8 |.....p.c.?l+..:?|
1,每个数据库对应一个文件夹,文件夹名和库名相同;
2,(单独的表空间中) 每张表对应几个文件,文件名和表名相同,innodb引擎中对应两个文件,后缀名为:frm、ibd;
frm文件:存储的是表结构信息。
ibd文件:存储的是表里的数据、索引等。
1、MySQL如果使用MyISAM存储引擎,数据库文件类型就包括.frm、.MYD、.MYI,默认存放位置是C:\Documents and Settings\All Users\Application Data\MySQL\MySQL Server 5.1\data
2、MySQL如果使用InnoDB存储引擎,MySQL数据库文件类型就包括.frm、ibdata1、.ibd,存放位置有两个,
.frm文件默认存放位置是C:\Documents and Settings\All Users\Application Data\MySQL\MySQL Server 5.1\data, ibdata1、.ibd文件默认存放位置是MySQL安装目录下的data文件夹