规范化与反规范化数据库

数据库设计

Lis Sulmont

Curriculum Manager

回到书店示例

反规范化：星型模式

规范化：雪花模式

反规范化查询

目标： 获取 2018 年第 4 季度在温哥华售出的所有 Octavia E. Butler 书籍数量

  SELECT SUM(quantity) FROM fact_booksales
    -- Join to get city
    INNER JOIN dim_store_star on fact_booksales.store_id = dim_store_star.store_id
    -- Join to get author
    INNER JOIN dim_book_star on fact_booksales.book_id = dim_book_star.book_id
    -- Join to get year and quarter
    INNER JOIN dim_time_star on fact_booksales.time_id = dim_time_star.time_id
  WHERE 
    dim_store_star.city = 'Vancouver' AND dim_book_star.author = 'Octavia E. Butler' AND
    dim_time_star.year = 2018 AND dim_time_star.quarter = 4;

共 3 次连接（join）

规范化查询

SELECT
  SUM(fact_booksales.quantity)
FROM
  fact_booksales
  -- Join to get city
  INNER JOIN dim_store_sf ON fact_booksales.store_id = dim_store_sf.store_id
  INNER JOIN dim_city_sf ON dim_store_sf.city_id = dim_city_sf.city_id
  -- Join to get author
  INNER JOIN dim_book_sf ON fact_booksales.book_id = dim_book_sf.book_id
  INNER JOIN dim_author_sf ON dim_book_sf.author_id = dim_author_sf.author_id
  -- Join to get year and quarter
  INNER JOIN dim_time_sf ON fact_booksales.time_id = dim_time_sf.time_id
  INNER JOIN dim_month_sf ON dim_time_sf.month_id = dim_month_sf.month_id
  INNER JOIN dim_quarter_sf ON dim_month_sf.quarter_id =  dim_quarter_sf.quarter_id
  INNER JOIN dim_year_sf ON dim_quarter_sf.year_id = dim_year_sf.year_id

规范化查询（续）

WHERE
  dim_city_sf.city = `Vancouver`
  AND 
  dim_author_sf.author = `Octavia E. Butler`
  AND
  dim_year_sf.year = 2018 AND dim_quarter_sf.quarter = 4;

sum
7600

共 8 次连接（join）

那么，为什么还要规范化数据库呢？

规范化可节省空间

反规范化数据库会产生数据冗余

规范化可节省空间

规范化消除数据冗余

规范化提升数据完整性

1. 强制数据一致性

因参照完整性需遵循命名规范，例如用"California"，而非"CA"或"california"

2. 更安全的更新、删除与插入

更少冗余 = 更少需变更的记录

3. 更易通过扩展重构

小表比大表更易扩展

数据库规范化

优点

规范化消除冗余：节省存储
更好的数据完整性：数据更准确一致

缺点

复杂查询更耗 CPU

还记得 OLTP 与 OLAP 吗？

OLTP

如：业务数据库

通常高度规范化

写入密集
优先更快、更安全地插入数据

OLAP

如：数据仓库

通常较少规范化

读取密集
优先更快的分析查询

开始练习吧！

数据库设计