MySQL 如何存儲大數(shù)據(jù)

行格式為Compact是如何存儲大數(shù)據(jù)的：

目前創(chuàng)新互聯(lián)已為近千家的企業(yè)提供了網(wǎng)站建設(shè)、域名、網(wǎng)頁空間、網(wǎng)站改版維護、企業(yè)網(wǎng)站設(shè)計、永定網(wǎng)站維護等服務，公司將堅持客戶導向、應用為本的策略，正道將秉承"和諧、參與、激情"的文化，與客戶和合作伙伴齊心協(xié)力一起成長，共同發(fā)展。

[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)

我們建立一張測試表，插入數(shù)據(jù)：

[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 格式為數(shù)據(jù)頁，存放著MySQL的行數(shù)據(jù)。 Uncompressed BLOB Page 可以理解為MySQL存放大數(shù)據(jù)的地方，暫且叫作外部存儲頁。Compact格式?jīng)]有將大數(shù)據(jù)全部放在數(shù)據(jù)頁中，而是將一部分數(shù)據(jù)放在了外部存儲頁中。那么，是全部數(shù)據(jù)在外部存儲頁中，還是一部分數(shù)據(jù)。假如是一部分數(shù)據(jù)，這一部分是多少呢？

我們使用 hexdump -Cv row.ibd 查看一下數(shù)據(jù)頁 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+..:?|

mysql是怎么做數(shù)據(jù)存儲的

1，每個數(shù)據(jù)庫對應一個文件夾，文件夾名和庫名相同；

2，(單獨的表空間中) 每張表對應幾個文件，文件名和表名相同，innodb引擎中對應兩個文件，后綴名為：frm、ibd；

frm文件：存儲的是表結(jié)構(gòu)信息。

ibd文件：存儲的是表里的數(shù)據(jù)、索引等。

mysql的數(shù)據(jù)存儲在哪

1、MySQL如果使用MyISAM存儲引擎，數(shù)據(jù)庫文件類型就包括.frm、.MYD、.MYI，默認存放位置是C:\Documents and Settings\All Users\Application Data\MySQL\MySQL Server 5.1\data

2、MySQL如果使用InnoDB存儲引擎，MySQL數(shù)據(jù)庫文件類型就包括.frm、ibdata1、.ibd，存放位置有兩個，

.frm文件默認存放位置是C:\Documents and Settings\All Users\Application Data\MySQL\MySQL Server 5.1\data， ibdata1、.ibd文件默認存放位置是MySQL安裝目錄下的data文件夾