PostgreSQL和MySQL的存储层深度解析

PostgreSQL

和

MySQL存储层深度解析存储与计算

,

引领数据处理的发展PostgReSQL

pk

MySQL

：存储层对比12PostgReSQL

和

MySQL

存储层的优化3存储层，我们看重什么？看的见的文件仅仅是文件吗？看不见的管理者：表空间？2.12.22.331什存储与计算

,

引领数据处理的发展

？

什么引领着

数么在引领着数据处理技术的发展4文件系统

:

只存储无计算数据库

:

既存储又计算两个时代，五个阶段存储的目的是为了计算

分布式数据库

:

分布式实时计算

第一代

Haddop

生态园

:

分布式非实时计算

武新分布式文件系统

:

对大数据存储单机时代多机协作时代阶段1阶段2阶段3阶段4阶段551存储与计算

,

引领数据处理的发展

存储与计算的需求

引领什么在引领着数据处理技术的发展

发展62PostgReSQL

pk

MySQL

：

存储层对比存储层，我们看重什么？看的见的文件仅仅是文件吗？看不见的管理者：表空间？2.12.22.372.1

存储层，我们看重什么？存储层，三大能力1

IO

能力2

稳定性3

存储能力力8比较项PostgreSQLMySQL数据管理单位页（默认8k）区（每区默认1M，一次可分配1--4个）物理读写单位页页使用方式

读数据到缓存区读数据到缓存区

预读(Read-Ahead)

多范围读(Multi-RangeRead)2.1存储层，我们看重什么？存储层，三大能力

IO

能力92.1

存储层，我们看重什么？存储层，三大能力

稳定能力PostgreSQL

与MySQL都能长期稳定运行，包括存储系统长期的稳定运行MySQL:Facebook/Google/

淘宝

...PostgreSQL:

腾讯

/Fujitsu/Cisco/NTT

Data..../about/users/

1064-bitsPostgreSQLMySQL数据库的个数无限制无限制表的个数无限制无限制(InnoDB限制40亿张表)单表的容量无限制理论上无限制(受限于OS)2.1存储层，我们看重什么？存储层，三大能力

存储能力112.1

存储层，我们看重什么？文件大小受操作系统限制12比较项PostgreSQLMySQL每个表的数据文件个数无数个1个单个文件最大尺寸默认1G受OS限制单个文件初始大小自动扩展到单个文件最大自动扩展到最大文件在外存的形式filenode.1,filenode.2,etc表名.ibd2.1存储层，我们看重什么？单表比较132.1存储层，我们看重什么？PostgreSQL

单表容量示例：单表

2GB*(1024*100

个文件

)

=

200TB/

表空间（

64-bits

机器，实际上限为

2

的

64

次方

)

次方）MySQL

单表容量示例：受操作系统影响

,

单表容量就是外存文件大小MySQL

依赖脱机配置参数

配置

系统表空间的容量

,

使用不方便且

IO

低效：innodb_data_file_path

=

/ibdata/ibdata1:988M;/disk2/ibdata2:50M:autoextend

14

实例X

易的相册库：150

亿记录，分布在

8

个实例节点上，单表接近

20

亿的记录，单表

350G

的存储量X

浪

60

亿单表的记录，单表容量超过

1T2.2看的见的文件仅仅是文件吗？cReate

tablespace

ts_a

(...)cReate

table

t_a

(…)

…

tablespace

ts_a...

数据库

用户

SQL

外存数据的存储路径目录

+

文件名PostgReSQL/MySQL/...,

神秘的盒子

?152.2看的见的文件仅仅是文件吗？

--PostgReSQL1

存储结构2

运行信息162.2看的见的文件仅仅是文件吗？

--PostgReSQL

存储结构包括了元

信息和用户的数据

1

存储结构

2

运行信息

运行信息与数据

库的功能相对应172.2看的见的文件仅仅是文件吗？

--PostgReSQL

用户

SQL

系统表cReate

tablespace

ts_a

(…)cReate

table

t_a

(…)

…

tablespace

ts_a...select

…

fRom

t_a...

数据缓存区

外存，文件系统：

pg_tablesapce

pg_class

pg_attRibutes

Reltablespace

+

Relfilenode

（

+

ReltoastRelid

）

存储介质目录

-

表空间

-+

文件名

filenoed1/filenode2

18深度挖掘2.2看的见的文件仅仅是文件吗？

--PostgReSQL

文件中存放系统运行信息、元

数据、用户数据

没有管理

IO

依赖操作系统

表空间级的存储能

力没有限制

单个文件大小有限但表的数据量没限制192.2看的见的文件仅仅是文件吗？

--MySQL202.2看的见的文件仅仅是文件吗？

--MySQL

1

系统数据

2

用户数据212.2看的见的文件仅仅是文件吗？

--MySQL

参数控制表空间外存，文件系统：t_a.fRm

t_a.ibd.

InnoDBIf

(…){...}FoR

(int

i=1,

i<100,i++){…}

存储介质

用户

SQL

innodb_file_peR_tablecReate

table

t_a

(…)

…select

…

fRom

t_a...

数据缓存区22stoRage\innobase\include\fil0fil.h:内部存储时，页的类型，部分定义：系统运行信息与数据存放在一起2.2看的见的文件仅仅是文件吗？

--MySQL#define

FIL_PAGE_INDEX

17855

/*!<

B-tree

node

*/#define

FIL_PAGE_UNDO_LOG

2

/*!<

Undo

log

page

*/#define

FIL_PAGE_INODE

3

/*!<

Index

node

*/#define

FIL_PAGE_IBUF_FREE_LIST

4

/*!<

Insert

buffer

free

list

*/#define

FIL_PAGE_TYPE_ALLOCATED

0

/*!<

Freshly

allocated

page

*/#define

FIL_PAGE_IBUF_BITMAP

5

/*!<

Insert

buffer

bitmap

*/#define

FIL_PAGE_TYPE_SYS

6

/*!<

System

page

*/#define

FIL_PAGE_TYPE_TRX_SYS

7

/*!<

Transaction

system

data

*/#define

FIL_PAGE_TYPE_FSP_HDR

8

/*!<

File

space

header

*/#define

FIL_PAGE_TYPE_XDES

9

/*!<

Extent

descriptor

page

*/#define

FIL_PAGE_TYPE_BLOB

10

/*!<

Uncompressed

BLOB

page

*/#define

FIL_PAGE_TYPE_ZBLOB

11

/*!<

First

compressed

BLOB

page

*/

23深度挖掘2.2看的见的文件仅仅是文件吗？

--MySQL

文件中存放元数据、用户数据

对

IO

进行了一

定程度的管理表空间级的存储能

力受

OS

限制

单个文件大小

依赖

OS24比较项PostgreSQLMySQL系统运行的信息使用独立的外存文件存放没有独立文件存放系统的元数据外存文件（global）information_schema用户的表定义使用系统表,由系统统一存储表定义直接存放到外存用户的表数据使用外存文件文件外存文件使用IO管理单位页(物理IO)区(逻辑IO)2.2看的见的文件仅仅是文件吗？

--PK252.3看不见的管理者：表空间？

表空间

:

是数据库管理数据存储的方式

(

也许称作数据空间更为合适

)

历史

:

生于

Oracle

，开花于各个数据库

品种

:

不尽相同

意义

:

数据只有被管理起来，才能被高效利用262.3看不见的管理者：表空间？用途

:

1

控制用户

/

表占用的储存空间大小，配额式管理数据（按对象存储）

如：表的数据容量可控

2

控制数据库所占用的存储空间大小，配额式管理应用的空间（按需存储）

如：不同应用的数据容量可控（云数据库中限制数据库的容量）

3

分离日志文件与数据文件的存储，提高数据库性能（按位置存储）

如：日志文件和数据文件分别储存于不同的物理位置

4

控制数据的分布，提高数据库的输入输出性能（按数据存储）

如：不同数据文件分别存储于不同的物理位置

5

控制对象管理的粒度（按业务逻辑存储）

如：按表空间进行备份

/

恢复

272.3看不见的管理者：表空间？282.3看不见的管理者：表空间？

--PostgReSQL表空间的创建

:

CREATE

TABLESPACE

dbspace

LOCATION

'/data/dbs';

分析

:

表空间的名称

dbspace

对应

物理存储的目录

/data/dbs表空间的使用

:

CREATE

...

table_name

(

[

{

column_name

data_type

...}...]

)

[

TABLESPACE

dbspace

]

分析

:

表空间的名称

tdbspace

对应

表名

table_name

（也可以是索引名）

292.3看不见的管理者：表空间？

--PostgReSQL表空间的管理

:

1

每个数据文件是一个段“

segment”

，一个表可以有无数个段

2

ALTER

TABLESPACE

name

RENAME

TO

new_name

3

ALTER

TABLESPACE

name

OWNER

TO

new_owner

4

ALTER

TABLESPACE

name

SET

(

tablespace_option

=

value

[,

...

]

)

5

ALTER

TABLESPACE

name

RESET

(

tablespace_option

[,

...

]

)

tablespace_option

：

seq_page_cost/random_page_cost

分析

:

表空间管理方式简单

302.3看不见的管理者：表空间？

--PostgReSQL

逻辑存储结构312.3看不见的管理者：表空间？

--MySQL表空间的创建

:

指定用户的每个表独立使用表空间：

innodb_file_per_table指定系统使用的表空间的位置

/

属性

：

innodb_data_home_dirinnodb_data_file_path分析

:MySQL

实例运行中根据参数自动管理表空间的使用

:

自

MySQL

5.6

及之后的版本，通过调整

nnodb_file_per_table

参数，允许每个表拥有独立的表空间。

分析

:

自动管理

322.3看不见的管理者：表空间？

--MySQL表空间的管理

:

1

通过上述参数调整

2

简单

分析

:

表空间管理方式简单，不方便动态切换存储设备（通过符号链接支持）332.3看不见的管理者：表空间？

--MySQL

逻辑存储结构342.3看不见的管理者：表空间？

--

实践中的问题1

动态增加存储设备或数据文件？

D

盘满了，新加的

E

盘能不能继续存放

D

盘中

A

表的新数据？2

文件的动态扩展：数据量增加导致数据文件变大的过程中

产生

IO

等待，有办法避免吗？

文件扩展时，产生了

IO

等待，导致数据处理速度降低，怎么办？3

…...352.3文件组

1...…...文件组

n看不见的管理者：表空间？

--MS

SQL

SeRveR

数据库文件组

k表

1表

k表

n存储层逻辑层36...2.3看不见的管理者：表空间？

--MS

SQL

SeRveRALTER

DATABASE

database_name

<add_or_modify_files>

//

可以增加

/

修改文件|

<add_or_modify_filegroups>

//

可以增加

/

修改文件组{}[;]<add_or_modify_files>::={

ADD

FILE

<filespec>

[

,...n

]

[

TO

FILEGROUP

{

filegroup_name

}

]

//

可以增加文件到文件组...}372.3看不见的管理者：表空间？

--MS

SQL

SeRveR<filespec>::=(

NAME

=

logical_file_name

[

,

NEWNAME

=

new_logical_name

]

[

,

FILENAME

=

{'os_file_name'

|

'filestream_path'

}

]

//

可以换位置（使用新的存储

/

分布文件）

[

,

SIZE

=

size

[

KB

|

MB

|

GB

|

TB

]

]

//

指定初始大小

文件的大小提前扩展

好，避免文件系统扩展文件时产生的

IO

等待

[

,

MAXSIZE

=

{

max_size

[

KB

|

MB

|

GB

|

TB

]

|

UNLIMITED

}

]

[

,

FILEGROWTH

=

growth_increment

[

KB

|

MB

|

GB

|

TB|

%

]

]

//

可自动扩展文件（存储空间的配额管理）

[

,

OFFLINE

])<add_or_modify_filegroups>::={

|

ADD

FILEGROUP

filegroup_name

//

可以增加

/

修改文件组

[

CONTAINS

FILESTREAM

]...}

382.3看不见的管理者：表空间？

--MS

SQL

SeRveR区，

Extent

：

区是表空间管理的基本单位，一个区包括

8

个物理相连的页面。

页面是

IO

的物理单位。392.3看不见的管理者：表空间？

--ORacleCREATE

TABLESPACE

tbs_02

DATAFILE

'diskb:tbs_f02_1.dbf'

SIZE

500K

REUSE

AUTOEXTEND

ON

NEXT

500K

MAXSIZE

100M;ALTER

TABLESPACE

tbs_02

ADD

DATAFILE

'tbs_f02_2.dbf'

//

为指定表空间增加数据文件，可以换位置（使用新的存储

/

分布文件）

SIZE

100K

//

指定初始大小

文件的大小提前扩展

好，避免文件系统扩展文件时产生的

IO

等待

AUTOEXTEND

ON

//

可自动扩展文件（存储空间的配额管理）

NEXT

10K

MAXSIZE

100K;402.3看不见的管理者：表空间？

--ORacle

extent--

最小空间分配单位

DatabaseSYSTEMTablespacetablespace1tablespace2Segment1Segment3

Extent1

Extent2Segment=FileBlock

Segment2Tablespace41比较项PostgreSQLMySQL动态增加存储文件/改变存储位置不能不能为每个表单独配置不同的表空间能能数据文件的大小可以提前分配不能不能对物理IO进行管理(区)不能能2.3看不见的管理者：表空间？

--

存储能力

PK423PostgReSQL

和

MySQL

存储层的优化43存储功能的改进使用方法的改进有益的实践经验

3.13.23.3比较项PostgreSQLMySQL表空间的管理：改造为完全的DMS(DatabaseManagedSpace)类似Oracle、MSSQLServer