Materialized View

Harry Yuan

目錄

  • View 是啥？
  • View 痛點
  • Materialized View
  • 效能優化
  • Demo

View 是啥？

又稱檢視表、虛擬資料表。

由一份指令定義的可查詢物件

 

View 是啥？

由一份指令定義的可查詢物件

又稱檢視表、虛擬資料表

 

  • 一種關連式資料庫的物件

  • 主要用來儲存一組查詢指令

  • 它代表你的指令，方便撈取資料

View 是啥？

select count(id)

from  users

where created_at > now() - interval '1 day'

老闆：今天台灣DAU多少？by device？

select count(u.id) as total_dau,

          sum(case when d.os='ios' then 1 else  0 end) as ios_dau,

          sum(case when d.os='aos' then 1 else  0 end) as aos_dau

from users u

inner join device_info d on u.id=d.user_id

where u.last_sign_in_at::date = now()::date and

            u.country_code = 'TW' 

老闆：今天韓國DAU多少？by device？

select count(u.id) as total_dau,

          sum(case when d.os='ios' then 1 else  0 end) as ios_dau,

          sum(case when d.os='aos' then 1 else  0 end) as aos_dau

from users u

inner join device_info d on u.id=d.user_id

where u.last_sign_in_at::date = now()::date  and

            u.country_code = 'KR' 

老闆：今天立本DAU多少？by device？

select count(u.id) as total_dau,

          sum(case when d.os='ios' then 1 else  0 end) as ios_dau,

          sum(case when d.os='aos' then 1 else  0 end) as aos_dau

from users u

inner join device_info d on u.id=d.user_id

where u.last_sign_in_at::date = now()::date and

            u.country_code = 'JP' 

保留經常重用的code

select count(u.id) as total_dau,

          sum(case when d.os='ios' then 1 else  0 end) as ios_dau,

          sum(case when d.os='aos' then 1 else  0 end) as aos_dau

from users u

inner join device_info d on u.id=d.user_id

where u.last_sign_in_at::date = now()::date and

            u.country_code = 'JP' 

拔掉會變動的部分

select count(u.id) as total_dau,

          sum(case when d.os='ios' then 1 else  0 end) as ios_dau,

          sum(case when d.os='aos' then 1 else  0 end) as aos_dau

from users u

inner join device_info d on u.id=d.user_id

where u.last_sign_in_at::date = now()::date  and

            u.country_code = 'JP' 

老闆：今天__DAU多少？by device？

select count(u.id) as total_dau,

          sum(case when d.os='ios' then 1 else  0 end) as ios_dau,

          sum(case when d.os='aos' then 1 else  0 end) as aos_dau

from users u

inner join device_info d on u.id=d.user_id

where u.last_sign_in_at::date = now()::date

 

老闆：今天__DAU多少？by device？

select count(u.id) as total_dau,

          sum(case when d.os='ios' then 1 else  0 end) as ios_dau,

          sum(case when d.os='aos' then 1 else  0 end) as aos_dau

from users u

inner join device_info d on u.id=d.user_id

where u.last_sign_in_at::date = now()::date

老闆：今天__DAU多少？by device？

select count(u.id) as total_dau,

          sum(case when d.os='ios' then 1 else  0 end) as ios_dau,

          sum(case when d.os='aos' then 1 else  0 end) as aos_dau

from users u

inner join device_info d on u.id=d.user_id

where u.last_sign_in_at::date = now()::date

老闆：今天__DAU多少？by device？

create view dau_report as

老闆：今天__DAU多少？by device？

create view dau_report as

老闆：今天__DAU多少？by device？

create view dau_report as

select count(u.id) as total_dau,

          sum(case when d.os='ios' then 1 else  0 end) as ios_dau,

          sum(case when d.os='aos' then 1 else  0 end) as aos_dau

from users u

inner join device_info d on u.id=d.user_id

where u.last_sign_in_at::date = now()::date

老闆：今天__DAU多少？by device？

select count(u.id) as total_dau,

          sum(case when d.os='ios' then 1 else  0 end) as ios_dau,

          sum(case when d.os='aos' then 1 else  0 end) as aos_dau

from users u

inner join device_info d on u.id=d.user_id

where u.last_sign_in_at::date = now()::date

老闆：今天__DAU多少？by device？

select count(u.id) as total_dau,

          sum(case when d.os='ios' then 1 else  0 end) as ios_dau,

          sum(case when d.os='aos' then 1 else  0 end) as aos_dau

from users u

inner join device_info d on u.id=d.user_id

where u.last_sign_in_at::date = now()::date

dau_report

老闆：今天__DAU多少？by device？

select count(u.id) as total_dau,

          sum(case when d.os='ios' then 1 else  0 end) as ios_dau,

          sum(case when d.os='aos' then 1 else  0 end) as aos_dau

from users u

inner join device_info d on u.id=d.user_id

where u.last_sign_in_at > now() - interval '1 day' 

select * from dau_report

老闆：今天__DAU多少？by device？

select * from dau_report

老闆：今天台灣DAU多少？by device？

select * from dau_report

where country_code='TW'

老闆：今天韓國DAU多少？by device？

select * from dau_report

where country_code='KR'

老闆：今天立本DAU多少？by device？

select * from dau_report

where country_code='JP'

View 優點

select * from dau_report

方便使用

  • 大幅減少指令長度，可快速查詢
  • 重用性高（可 join 其他資料表、sub query）
  • 權限控管
  • 在資料庫中幾乎不佔空間

View 缺點？

  1. 並沒有儲存任何結果，僅儲存「指令」和「規格」
  2. 每次都會執行查詢，使用硬體資源

僅儲存「指令」和「規格」

每次都會真的執行查詢

select count(u.id) as total_dau,

          sum(case when d.os='ios' then 1 else  0 end) as ios_dau,

          sum(case when d.os='aos' then 1 else  0 end) as aos_dau

from users u

inner join device_info d on u.id=d.user_id

where u.last_sign_in_at > now() - interval '1 day' 

select * from dau_report

每次都會真的執行查詢

explain analyze select * from dau_report

每次都會真的執行查詢

explain analyze select * from dau_report

 Aggregate  (cost=31.82..31.83 rows=1 width=24) 
   ->  Hash Join  (cost=13.15..31.24 rows=47 width=28)
         ...
         ->  Seq Scan on users u  (cost=0.00..17.70 rows=147 width=4) (actual time=0.012..0.020 rows=19 loops=1)
               Filter: (last_sign_in_at > (now() - '1 day'::interval))
                 ...
               ->  Seq Scan on device_info d  (cost=0.00..11.40 rows=140 width=28) ...

建議

  1. 盡量單獨使用，用作純搜尋捷徑
  2. 權限控管：不想讓其他人可以知道 Table 其他細節時可以包成 View

Materialized View

又稱實體化檢視表。

 

  1. 由一份指令定義的可查詢物件
  2. 具有儲存結果的功能
  3. 使用時只會從自身結果搜尋，不會執行指令
  4. 特定時候才會執行指令

MView - 儲存功能

在建立時會執行一次指令，並且將結果儲存起來。

 

下 REFRESH 時，會再次執行指令並刷新資料。

 

MView - 儲存功能

在建立時會執行一次指令，並且將結果儲存起來。

 

下 REFRESH 時，會再次執行指令並刷新資料。

 

MView - 儲存功能

在建立時會執行一次指令，並且將結果儲存起來。

 

下 REFRESH 時，會再次執行指令並刷新資料。

 

線上看 API: 影片觀看時數

線上看 API: 影片觀看時數

線上看 API: 影片觀看時數

線上看 API: 影片觀看時數

線上看 API: 影片觀看時數

線上看 API: 影片觀看時數

select vwh.user_id,

           vwh.video_id,

           v.name as video_name,

           sum(vwh.end_time - vwh.start_time) as time_used

from video_watching_histories vwh

inner join videos v on v.id = vwh.video_id

where vwh.user_id = 1

group by vwh.user_id, v.id, v.name

order by sum(vwh.end_time - vwh.start_time) desc;

線上看 API: 影片觀看時數

線上看 API: 影片觀看時數

如果資料表非常大？

select count(id) from video_watching_histories

select count(id) from video_watching_histories

 count
----------------------------
  9029840

如果資料表非常大？

如果資料表非常大？

Server

如果資料表非常大？

select vwh.user_id,

           vwh.video_id,

           v.name as video_name,

           sum(vwh.end_time - vwh.start_time) as time_used

from video_watching_histories vwh

inner join videos v on v.id = vwh.video_id

where vwh.user_id = 1

group by vwh.user_id, v.id, v.name

order by sum(vwh.end_time - vwh.start_time) desc;

Server

如果資料表非常大？

select vwh.user_id,

           vwh.video_id,

           v.name as video_name,

           sum(vwh.end_time - vwh.start_time) as time_used

from video_watching_histories vwh

inner join videos v on v.id = vwh.video_id

where vwh.user_id = 1

group by vwh.user_id, v.id, v.name

order by sum(vwh.end_time - vwh.start_time) desc;

select vwh.user_id,

           vwh.video_id,

           v.name as video_name,

           sum(vwh.end_time - vwh.start_time) as time_used

from video_watching_histories vwh

inner join videos v on v.id = vwh.video_id

where vwh.user_id = 2

group by vwh.user_id, v.id, v.name

order by sum(vwh.end_time - vwh.start_time) desc;

Server

如果資料表非常大？

select vwh.user_id,

           vwh.video_id,

           v.name as video_name,

           sum(vwh.end_time - vwh.start_time) as time_used

from video_watching_histories vwh

inner join videos v on v.id = vwh.video_id

where vwh.user_id = 1

group by vwh.user_id, v.id, v.name

order by sum(vwh.end_time - vwh.start_time) desc;

select vwh.user_id,

           vwh.video_id,

           v.name as video_name,

           sum(vwh.end_time - vwh.start_time) as time_used

from video_watching_histories vwh

inner join videos v on v.id = vwh.video_id

where vwh.user_id = 2

group by vwh.user_id, v.id, v.name

order by sum(vwh.end_time - vwh.start_time) desc;

select vwh.user_id,

           vwh.video_id,

           v.name as video_name,

           sum(vwh.end_time - vwh.start_time) as time_used

from video_watching_histories vwh

inner join videos v on v.id = vwh.video_id

where vwh.user_id = 9

group by vwh.user_id, v.id, v.name

order by sum(vwh.end_time - vwh.start_time) desc;

Server

如果資料表非常大？

select vwh.user_id,

           vwh.video_id,

           v.name as video_name,

           sum(vwh.end_time - vwh.start_time) as time_used

from video_watching_histories vwh

inner join videos v on v.id = vwh.video_id

where vwh.user_id = 1

group by vwh.user_id, v.id, v.name

order by sum(vwh.end_time - vwh.start_time) desc;

select vwh.user_id,

           vwh.video_id,

           v.name as video_name,

           sum(vwh.end_time - vwh.start_time) as time_used

from video_watching_histories vwh

inner join videos v on v.id = vwh.video_id

where vwh.user_id = 2

group by vwh.user_id, v.id, v.name

order by sum(vwh.end_time - vwh.start_time) desc;

select vwh.user_id,

           vwh.video_id,

           v.name as video_name,

           sum(vwh.end_time - vwh.start_time) as time_used

from video_watching_histories vwh

inner join videos v on v.id = vwh.video_id

where vwh.user_id = 4

group by vwh.user_id, v.id, v.name

order by sum(vwh.end_time - vwh.start_time) desc;

select vwh.user_id,

           vwh.video_id,

           v.name as video_name,

           sum(vwh.end_time - vwh.start_time) as time_used

from video_watching_histories vwh

inner join videos v on v.id = vwh.video_id

where vwh.user_id = 9

group by vwh.user_id, v.id, v.name

order by sum(vwh.end_time - vwh.start_time) desc;

Server

如果資料表非常大？

select vwh.user_id,

           vwh.video_id,

           v.name as video_name,

           sum(vwh.end_time - vwh.start_time) as time_used

from video_watching_histories vwh

inner join videos v on v.id = vwh.video_id

where vwh.user_id = 1

group by vwh.user_id, v.id, v.name

order by sum(vwh.end_time - vwh.start_time) desc;

select vwh.user_id,

           vwh.video_id,

           v.name as video_name,

           sum(vwh.end_time - vwh.start_time) as time_used

from video_watching_histories vwh

inner join videos v on v.id = vwh.video_id

where vwh.user_id = 2

group by vwh.user_id, v.id, v.name

order by sum(vwh.end_time - vwh.start_time) desc;

select vwh.user_id,

           vwh.video_id,

           v.name as video_name,

           sum(vwh.end_time - vwh.start_time) as time_used

from video_watching_histories vwh

inner join videos v on v.id = vwh.video_id

where vwh.user_id = 4

group by vwh.user_id, v.id, v.name

order by sum(vwh.end_time - vwh.start_time) desc;

select vwh.user_id,

           vwh.video_id,

           v.name as video_name,

           sum(vwh.end_time - vwh.start_time) as time_used

from video_watching_histories vwh

inner join videos v on v.id = vwh.video_id

where vwh.user_id = 9

group by vwh.user_id, v.id, v.name

order by sum(vwh.end_time - vwh.start_time) desc;

Server

如果資料表非常大？

  1. 做 caching

不適合 Caching 的時機

select vwh.user_id,

           vwh.video_id,

           v.name as video_name,

           sum(vwh.end_time - vwh.start_time) as time_used

from video_watching_histories vwh

inner join videos v on v.id = vwh.video_id

where vwh.user_id = 1

group by vwh.user_id, v.id, v.name

order by sum(vwh.end_time - vwh.start_time) desc;

select vwh.user_id,

           vwh.video_id,

           v.name as video_name,

           sum(vwh.end_time - vwh.start_time) as time_used

from video_watching_histories vwh

inner join videos v on v.id = vwh.video_id

where vwh.user_id = 2

group by vwh.user_id, v.id, v.name

order by sum(vwh.end_time - vwh.start_time) desc;

select vwh.user_id,

           vwh.video_id,

           v.name as video_name,

           sum(vwh.end_time - vwh.start_time) as time_used

from video_watching_histories vwh

inner join videos v on v.id = vwh.video_id

where vwh.user_id = 4

group by vwh.user_id, v.id, v.name

order by sum(vwh.end_time - vwh.start_time) desc;

select vwh.user_id,

           vwh.video_id,

           v.name as video_name,

           sum(vwh.end_time - vwh.start_time) as time_used

from video_watching_histories vwh

inner join videos v on v.id = vwh.video_id

where vwh.user_id = 9

group by vwh.user_id, v.id, v.name

order by sum(vwh.end_time - vwh.start_time) desc;

Server

cache: user_1

cache: user_4

cache: user_2

cache: user_9

不適合 Caching 的時機

select vwh.user_id,

           vwh.video_id,

           v.name as video_name,

           sum(vwh.end_time - vwh.start_time) as time_used

from video_watching_histories vwh

inner join videos v on v.id = vwh.video_id

where vwh.user_id = 1

group by vwh.user_id, v.id, v.name

order by sum(vwh.end_time - vwh.start_time) desc;

select vwh.user_id,

           vwh.video_id,

           v.name as video_name,

           sum(vwh.end_time - vwh.start_time) as time_used

from video_watching_histories vwh

inner join videos v on v.id = vwh.video_id

where vwh.user_id = 2

group by vwh.user_id, v.id, v.name

order by sum(vwh.end_time - vwh.start_time) desc;

select vwh.user_id,

           vwh.video_id,

           v.name as video_name,

           sum(vwh.end_time - vwh.start_time) as time_used

from video_watching_histories vwh

inner join videos v on v.id = vwh.video_id

where vwh.user_id = 4

group by vwh.user_id, v.id, v.name

order by sum(vwh.end_time - vwh.start_time) desc;

select vwh.user_id,

           vwh.video_id,

           v.name as video_name,

           sum(vwh.end_time - vwh.start_time) as time_used

from video_watching_histories vwh

inner join videos v on v.id = vwh.video_id

where vwh.user_id = 9

group by vwh.user_id, v.id, v.name

order by sum(vwh.end_time - vwh.start_time) desc;

Server

cache: user_1

expired: user_4

expired: user_2

expired: user_9

不適合 Caching 的時機

select vwh.user_id,

           vwh.video_id,

           v.name as video_name,

           sum(vwh.end_time - vwh.start_time) as time_used

from video_watching_histories vwh

inner join videos v on v.id = vwh.video_id

where vwh.user_id = 1

group by vwh.user_id, v.id, v.name

order by sum(vwh.end_time - vwh.start_time) desc;

select vwh.user_id,

           vwh.video_id,

           v.name as video_name,

           sum(vwh.end_time - vwh.start_time) as time_used

from video_watching_histories vwh

inner join videos v on v.id = vwh.video_id

where vwh.user_id = 2

group by vwh.user_id, v.id, v.name

order by sum(vwh.end_time - vwh.start_time) desc;

select vwh.user_id,

           vwh.video_id,

           v.name as video_name,

           sum(vwh.end_time - vwh.start_time) as time_used

from video_watching_histories vwh

inner join videos v on v.id = vwh.video_id

where vwh.user_id = 4

group by vwh.user_id, v.id, v.name

order by sum(vwh.end_time - vwh.start_time) desc;

select vwh.user_id,

           vwh.video_id,

           v.name as video_name,

           sum(vwh.end_time - vwh.start_time) as time_used

from video_watching_histories vwh

inner join videos v on v.id = vwh.video_id

where vwh.user_id = 9

group by vwh.user_id, v.id, v.name

order by sum(vwh.end_time - vwh.start_time) desc;

Server

cache: user_1

expired: user_4

expired: user_2

expired: user_9

如果資料表非常大？

1. 做 caching

2. 幫大家統一先算好一次

使用 Materialized View

select vwh.user_id,

           vwh.video_id,

           v.name as video_name,

           sum(vwh.end_time - vwh.start_time) as time_used

from video_watching_histories vwh

inner join videos v on v.id = vwh.video_id

where vwh.user_id = 1

group by vwh.user_id, v.id, v.name

order by sum(vwh.end_time - vwh.start_time) desc;

select vwh.user_id,

           vwh.video_id,

           v.name as video_name,

           sum(vwh.end_time - vwh.start_time) as time_used

from video_watching_histories vwh

inner join videos v on v.id = vwh.video_id

where vwh.user_id = 2

group by vwh.user_id, v.id, v.name

order by sum(vwh.end_time - vwh.start_time) desc;

select vwh.user_id,

           vwh.video_id,

           v.name as video_name,

           sum(vwh.end_time - vwh.start_time) as time_used

from video_watching_histories vwh

inner join videos v on v.id = vwh.video_id

where vwh.user_id = 4

group by vwh.user_id, v.id, v.name

order by sum(vwh.end_time - vwh.start_time) desc;

select vwh.user_id,

           vwh.video_id,

           v.name as video_name,

           sum(vwh.end_time - vwh.start_time) as time_used

from video_watching_histories vwh

inner join videos v on v.id = vwh.video_id

where vwh.user_id = 9

group by vwh.user_id, v.id, v.name

order by sum(vwh.end_time - vwh.start_time) desc;

Server

使用 Materialized View

select vwh.user_id,

           vwh.video_id,

           v.name as video_name,

           sum(vwh.end_time - vwh.start_time) as time_used

from video_watching_histories vwh

inner join videos v on v.id = vwh.video_id

where vwh.user_id = 1

group by vwh.user_id, v.id, v.name

order by sum(vwh.end_time - vwh.start_time) desc;

使用 Materialized View

select vwh.user_id,

           vwh.video_id,

           v.name as video_name,

           sum(vwh.end_time - vwh.start_time) as time_used

from video_watching_histories vwh

inner join videos v on v.id = vwh.video_id

where vwh.user_id = 1

group by vwh.user_id, v.id, v.name

order by sum(vwh.end_time - vwh.start_time) desc;

使用 Materialized View

select vwh.user_id,

           vwh.video_id,

           v.name as video_name,

           sum(vwh.end_time - vwh.start_time) as time_used

from video_watching_histories vwh

inner join videos v on v.id = vwh.video_id

group by vwh.user_id, v.id, v.name

order by sum(vwh.end_time - vwh.start_time) desc;

使用 Materialized View

create materialized view user_video_report as

select vwh.user_id,

           vwh.video_id,

           v.name as video_name,

           sum(vwh.end_time - vwh.start_time) as time_used

from video_watching_histories vwh

inner join videos v on v.id = vwh.video_id

group by vwh.user_id, v.id, v.name

order by sum(vwh.end_time - vwh.start_time) desc;

使用 Materialized View

create materialized view user_video_report as

select vwh.user_id,

           vwh.video_id,

           v.name as video_name,

           sum(vwh.end_time - vwh.start_time) as time_used

from video_watching_histories vwh

inner join videos v on v.id = vwh.video_id

group by vwh.user_id, v.id, v.name

order by sum(vwh.end_time - vwh.start_time) desc;

使用 Materialized View

create materialized view user_video_report as

select vwh.user_id,

           vwh.video_id,

           v.name as video_name,

           sum(vwh.end_time - vwh.start_time) as time_used

from video_watching_histories vwh

inner join videos v on v.id = vwh.video_id

group by vwh.user_id, v.id, v.name

order by sum(vwh.end_time - vwh.start_time) desc;

建立時會執行一次指令，並儲存結果。

使用 Materialized View

select * from user_video_report

使用 Materialized View

select * from user_video_report

where user_id=1

使用 Materialized View

select * from user_video_report

where user_id=1

1. 真的從 user_video_report 去 select

使用 Materialized View

select * from user_video_report

where user_id=1

1. 真的從 user_video_report 去 select

2. select 不會執行裡面的指令

只搜尋自己的資料

explain analyze select * from user_video_report

where user_id=1

只搜尋自己的資料

explain analyze select * from user_video_report

where user_id=1

 Seq Scan on user_video_report (cost=0.00..1.88 rows=5 width=48)
     Filter: (user_id = 1)
     Rows Removed by Filter: 65
 Planning Time: 15.753 ms
 Execution Time: 13.564 ms

刷新 Materialized View 資料

1. 真的從 user_video_report 去 select

2. select 不會執行裡面的指令

3. 只有 REFRESH 時，才執行指令並更新資料

refresh user_video_report​

select * from user_video_report

where user_id=1

select vwh.user_id,

           vwh.video_id,

           v.name as video_name,

           sum(vwh.end_time - vwh.start_time) as time_used

from video_watching_histories vwh

inner join videos v on v.id = vwh.video_id

where vwh.user_id = 2

group by vwh.user_id, v.id, v.name

order by sum(vwh.end_time - vwh.start_time) desc;

select vwh.user_id,

           vwh.video_id,

           v.name as video_name,

           sum(vwh.end_time - vwh.start_time) as time_used

from video_watching_histories vwh

inner join videos v on v.id = vwh.video_id

where vwh.user_id = 4

group by vwh.user_id, v.id, v.name

order by sum(vwh.end_time - vwh.start_time) desc;

select vwh.user_id,

           vwh.video_id,

           v.name as video_name,

           sum(vwh.end_time - vwh.start_time) as time_used

from video_watching_histories vwh

inner join videos v on v.id = vwh.video_id

where vwh.user_id = 9

group by vwh.user_id, v.id, v.name

order by sum(vwh.end_time - vwh.start_time) desc;

Server

使用 Materialized View

select vwh.user_id,

           vwh.video_id,

           v.name as video_name,

           sum(vwh.end_time - vwh.start_time) as time_used

from video_watching_histories vwh

inner join videos v on v.id = vwh.video_id

where vwh.user_id = 1

group by vwh.user_id, v.id, v.name

order by sum(vwh.end_time - vwh.start_time) desc;

select *

from user_video_report

where vwh.user_id = 1

select *

from user_video_report

where vwh.user_id = 2

select *

from user_video_report

where vwh.user_id = 4

select *

from user_video_report

where vwh.user_id = 9

Server

使用 Materialized View

select *

from user_video_report

where vwh.user_id = 1

select *

from user_video_report

where vwh.user_id = 2

select *

from user_video_report

where vwh.user_id = 4

select *

from user_video_report

where vwh.user_id = 9

Server

使用 Materialized View

Response Time 大幅減少

RDS CPU % 減少

select *

from user_video_report

where vwh.user_id = 1

select *

from user_video_report

where vwh.user_id = 2

select *

from user_video_report

where vwh.user_id = 4

select *

from user_video_report

where vwh.user_id = 9

Server

使用 Materialized View

Worker

每15分鐘

refresh user_video_report​

select *

from user_video_report

where vwh.user_id = 1

select *

from user_video_report

where vwh.user_id = 2

select *

from user_video_report

where vwh.user_id = 4

select *

from user_video_report

where vwh.user_id = 9

Server

使用 Materialized View

Worker

每15分鐘

refresh user_video_report​

自行決定何時執行 CPU Intense 的指令

Recap

種類 執行指令 儲存功能 主要用途？
View 每次 🚫 簡化方便查詢
資訊穩定
內建安全性
Materialized View 建立時
Refresh時		 ✅ 暫存大量運算結果
本身為table，查詢快速

Question

Updatable View?

  • 若可以從 view 對應回原資料表欄位就可以。
  • 一般都不太會直接從 View 去 Update 資料，也不建議這樣做。

Question

影片觀看記錄其他解法？

  • 建立新 table user_video_summary 
  • 在原資料表 video_watching_records 上建立 trigger
  • 當產生一筆 video_watching_records 時觸發 trigger，去更新 user_video_summary
  • API 只對 user_video_summary 做搜尋

Demo

Made with Slides.com