使用欄向儲存

改進 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

儲存方式

兩個表。第一個表是 id 與 species 的清單。01 - panda。02 - zebra。03 - zebra。04 - koala。第二個表是 id 與 age 的清單。01 - 2。02 - 3。03 - 10。04 - 5。

分析導向—高度適配

欄向儲存特性

同一欄位存於相鄰位置
取回所有列很快
欄位計算速度快

分析導向

計數、平均、運算
報表
欄位彙總

儲存方式

三個表。第一個表是 id 與 name 的清單。01 - Bob。02 - Sunny。03 - Beco。04 - Coco。第二個表是 id 與 species 的清單。01 - panda。02 - zebra。03 - zebra。04 - koala。第三個表是 id 與 age 的清單。01 - 2。02 - 3。03 - 10。04 - 5。

交易導向—不適合

保留列關係

取回所有欄位較慢
載入資料較慢

交易導向

快速新增與刪除記錄

儲存方式

資料庫範例


Postgres	Citus Data、Greenplum、Amazon Redshift
MySQL	MariaDB
Oracle	Oracle In-Memory Cloud Store
	Clickhouse、Apache Druid、CrateDB

資訊綱要（information schema）

減少欄位

謹慎使用 SELECT *

SELECT column_name, data_type
FROM information_schema.columns
WHERE table_catalog = 'schema_name'
AND table_name = 'zoo_animals'

column_name	data_type
id	integer
name	text
species	text

資訊綱要（information schema）

減少欄位

謹慎使用 SELECT *
使用資訊綱要

SELECT column_name, data_type
FROM information_schema.columns
WHERE table_catalog = 'schema_name'
AND table_name = 'zoo_animals'

column_name	data_type
id	integer
name	text
species	text

撰寫查詢

各欄用各自的查詢檢視

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

-- Structure for column oriented
SELECT MIN(age), MAX(age)
FROM zoo_animals
WHERE species = 'zebra'

-- Structure for row-oriented
SELECT *
FROM zoo_animals
WHERE species = 'zebra'
ORDER BY age

一起來練習吧！

改進 PostgreSQL 的查詢效能