面向行的存储与分区

提升 PostgreSQL 查询性能

Amy McCarty

Instructor

数据库存储类型

面向行存储

  • 保留列之间的关系

面向列存储

  • 保留行之间的关系
id name species age habitat received
01 Bob panda 2 Asia 2018
02 Sunny zebra 3 Africa 2018
03 Beco zebra 10 Africa 2017
04 Coco koala 5 Australia 2016
提升 PostgreSQL 查询性能

面向行

面向行存储

  • 保留列之间的关系

 

id name species age habitat received
01 Bob panda 2 Asia 2018
提升 PostgreSQL 查询性能

面向列

面向列存储

  • 保留行之间的关系  

两个表。第一个表是 id 与 name 列表：01 - Bob；02 - Sunny；03 - Beco；04 - Coco。第二个表是相同 id 与 species 列表：01 - panda；02 - zebra；03 - zebra；04 - koala。

提升 PostgreSQL 查询性能

面向行存储

 

  • 同一行存放在相邻位置
  • 追加或删除整行很快
  • 返回所有列很快
    • 返回所有行较慢
提升 PostgreSQL 查询性能

减少行

减少行数

  • WHERE 过滤
  • INNER JOIN
  • DISTINCT
  • LIMIT
提升 PostgreSQL 查询性能

面向行数据库的方法

分区

  • 将一张（父）表拆成多张更小的（子）表

索引

  • 为列创建有序键以加速查找

 

使用分区与索引

  • 需要设置与维护
  • 其存在由数据库管理员或文档告知
提升 PostgreSQL 查询性能

分区结构

一个动物园动物的父表，含四条记录：id、name、species、habitat、received。01 - Bob - Panda - Asia - 2018。02 - Sunny - Zebra - Africa - 2018。03 - Beco - Zebra - Africa - 2017。04 - Coco - Macaw - South America - 2016。按栖息地列对应有3个子表。Asia 表有 Bob 一条；Africa 表有 Sunny 与 Beco 两条；South America 表有 Coco 一条。

  • 父表
    • 在数据库前端可见
    • 在其上编写查询
  • 子表
    • 在数据库前端不可见
    • 查询会检索它们
提升 PostgreSQL 查询性能

分区结构

一个动物园动物的父表，含四条记录：id、name、species、habitat、received。01 - Bob - Panda - Asia - 2018。02 - Sunny - Zebra - Africa - 2018。03 - Beco - Zebra - Africa - 2017。04 - Coco - Macaw - South America - 2016。按栖息地列对应有3个子表。Asia 表有 Bob 一条；Africa 表有 Sunny 与 Beco 两条；South America 表有 Coco 一条。Africa 表以红色高亮。

SELECT species
FROM zoo_animals
WHERE habitat = 'Africa'
提升 PostgreSQL 查询性能

分区概览

是什么

  • 将一张表拆成多张更小的表

为什么

  • 存储更灵活
  • 查询更快

用于何处

  • 常用作过滤的列
    • 如日期、地点
提升 PostgreSQL 查询性能

分区查询评估

查询规划器

多位厨师围着一口大锅

EXPLAIN
SELECT species
FROM zoo_animals
WHERE habitat = 'Africa'

查询计划

Seq Scan on zoo_animals  (cost=0.00..
17.70 rows=2 width=182)
  Filter: (state_code = 15)
  • 成本（时间）估计
提升 PostgreSQL 查询性能

让我们来练习！

提升 PostgreSQL 查询性能

Preparing Video For Download...