[譯] 全面感受學習資料庫系統的魅力。Anti-pattern RDB 和理想的資料庫設計是什麼呢?

「從失敗中學習 RDB 的正確方法」的作者來告訴你

古哥
古哥
Mar 29 · 13 min read

本文譯自:「データベースを学ぶ魅力を徹底解説、RDBアンチパターンや最強DB設計とは?〜「失敗から学ぶ RDBの正しい歩き方」著者が語る〜」— flexy(フレキシー)

Image for post
Image for post

曽根壮大(@soudai1025),日本 PostgreSQL 使用者社群(日本 PostgreSQL ユーザ会,特定非営利活動法人)的理事,同時也是 Have Fun Tech 公司的創辦人。先前為「オミカレOmikare」公司的副社長兼技術長。最近發表了新書「從失敗中學習 RDB 的正確方法」(失敗から学ぶ RDBの正しい歩き方)。

這次帶來的主題是資料庫系統,包含了說明 MySQL 和 PostgreSQL 之間的差異特性、Anti-pattern 的基本認識、以及學習方法的分享。

一、從工程師角度發現學習資料庫系統的魅力

  1. 知識的成本效益高
    從工程師學習資料庫的角度來看,我認為資料庫的學習優勢是其知識運用的壽命長久。
    如果是 Web 應用程式的話,每年都可能大幅升級,甚至連流行的程式語言都不一樣;但是,資料庫系統卻能以在 10 年或 20 年之中學習到的知識進行實務運用。知識的成本效益相當高。
    例如,如果你使用部落格系統和資料庫建立了電子商務的網站,然後專心營運這一個電子商務網站。部落格的應用程式就算消失了,資料庫也會保留著。將來即使更新或升級了網站,基本上也不需要捨棄資料庫系統。
  2. 資料庫設計很重要
    另一方面,由於資料庫是應用程式的基礎建設,所以如果初始架構設計有缺陷的話,很容易使開發建置的時程延長。尤其是,新創公司時常傾向於延後資料庫設計的工作,但是基本上都很難再之後進行改善。
    你應該在一開始就進行資料庫設計,因為它會影響很長的時間,而且也希望讓一家新創公司的年輕工程師早日開始學習資料庫。PostgreSQL 有免費公開的 Web 文件,例如官方使用者手冊[日本語][正體中文]和 PostgreSQL Internals
    由於其應用壽命長,因此在資料庫領域,迄今為止,前輩們不斷研究的數十年知識已經累積在各種書籍和網頁之中。如果你持續系統性地學習,就可以成為專業人士。這樣的特性真的很吸引人!
  3. 可長期經營,薪資水準較高
    資料工程師是一個能長期經營的職業。即使你在結婚或懷孕後離開工作場所一年,資料庫在此期間也不太可能發生巨大的變化。海外的許多女工程師也很容易重返工作崗位,以作為 DBA(資料庫管理員)的身份,持續活躍在職場上。
    另外,實際上現場會產生問題的,都是以大約 5 或 10 年為單位長期營運的資料庫系統。
    在這裡要請大家注意的是,基本上一家長期營運資料庫的公司都有足夠營業利潤的。因此,工作本身的單價通常很高,解決系統問題也能視為是有利潤的營業項目。
    像這樣優質的工作情況也算是資料庫的另一項優點吧。
  4. 給初學者參考書,就先開始動手製作一個應用程式吧
    對於初學者,這裡有一些推薦的書籍,可以幫助你學習資料庫系統:
    『達人に学ぶDB設計 徹底指南書』(翔泳社)ミック
    『達人に学ぶSQL徹底指南書 第2版 初級者で終わりたくないあなたへ』(翔泳社)ミック
    『SQLアンチパターン』(オライリージャパン)Bill Karwin

    市面上有很多不錯的書籍可以學習資料庫系統的基本知識,但是如果有任何讀者在第一次學習資料庫和程式設計,請首先嘗試一下有關「作ってみよう!(試著做看看吧)」系列的書籍會更好。程式語言可能是 PHP 或 Ruby,總之第一步就是開始建立一個應用程式吧。
    在與日本 PostgreSQL 使用者社群的理事們聚會時,看到某個文組學生留言之後,我寫了一篇主題為「即使是文組出身,也可以成為程式設計師」的部落格文章。因此,建議以開始建立一個應用程式作為學習的方法。

二、資料庫系統的類型

什麼是 RDB 呢?談談 MySQL 和 PostgreSQL

「RDB」是「Relational Database」的縮寫,在日本語中翻譯為「関係データベース」。(正體中文譯為「關聯式資料庫」)
RDB 將資料以多個資料表進行管理,定義資料表之間的關聯性以處理複雜的資料關係。

MySQL 和 PostgreSQL 之間的差別

MySQL 和 PostgreSQL 是資料庫軟體中很棒的兩個產品。我想根據我最近在台灣 COSCUP 上所使用的投影片來說明其差異。

MySQL 和 PostgreSQL 之間有三個主要的差異點:

  • Architecutre(系統架構)
  • License(授權方式)
  • Development style(發展風格)

其中,系統架構有明顯上差異。

MySQL 使用多執行緒(Multi-threading),PostgreSQL 則採用多工處理(Multi-processing)。你可能會認為多執行緒速度更快,但事實並非如此。緩慢的查詢效能問題比 Fork 的成本更重要;再來,與長查詢交易事務鎖定相比,連線成本實際上可以忽略不計。另外,使用 Connection Pool 也可以降低連線成本。

Image for post
Image for post
圖:MySQL architecture (引用發表於台灣 COSCUP 的投影片)
Image for post
Image for post
圖:PostgreSQL architecture (引用發表於台灣 COSCUP 的投影片)

MySQL 和 PostgreSQL 在儲存資料方面存在著不同的設計思維。

MySQL 透過其可自訂式儲存引擎可以更自由地儲存資料。例如,您可以在 MySQL 中執行 INSERT 語句並將其保存到 memcached。這種近乎無限自由度的功能可以極大延展 MySQL 使用範圍。

另一方面,PostgreSQL 嚴格地將資料儲存在 RDBMS 之中。儘管從PostgreSQL 12 開始引入了外掛式儲存引擎的功能,但它還像是一個實驗階段的功能,所以仍然由 PostgreSQL 本身嚴謹地儲存資料。當然,如果將 MySQL 作為 RDBMS 使用,也可以正確而嚴格儲存資料;然而,PostgreSQL 的特色則會以其複寫機制突顯出來。

相對於 MySQL (Cluster)可以在 Slave 伺服器的更新資料,而 PostgreSQL 則無法於 Slave 使用 UPDATE 指令。

現在的情況是,MySQL 和 PostgreSQL 在發展的同時,發揮了獨特的色彩,並且持續相互影響中。

三、什麼是「設計不良的資料庫」?

如何發現 Anti-pattern RDB

在本文的開頭,我提到了資料庫初始化和優化的重要性。因為資料庫是基礎設施,所以是一個神秘的設計。我稱它為神奇的設計,但是如果您將其設計視為技術債,就很難更改規格。

如果地基就已經是三角形的屋頂,無法在上面建置任何東西。

要重構這樣的資料庫,您需要能夠識別 Anti-pattern RDB。

我經常引用「Refactoring Databases」(Addison-Wesley Professional,2006,日本語版已絕版)中有一些重構資料庫必須要注意的項目。它被稱為「資料庫的不祥之兆」。

需要重構的項目

  • 用於多種目的的欄位
    - 值會因為資料的屬性而改變含義的欄位。
    - 例如日期欄位:如果是會員,是加入的日子;如果是員工,則是加入公司的日子。
  • 用於多種目的的資料表
    - 與前項的欄位一樣,資料表具有多種含義
    - 例如:會員、管理者、公司等等,在「Users」資料表中混在一起
  • 冗餘資料過多
    - 資料未正規化
    - 例如:同時有生日和年齡欄位,則年齡欄位將在一年後偏離事實,資料會不一致。
  • 資料表的欄位過多
    - memo1、memo2、memo3 … memo99 等等。
    - 多個欄位可能可以整合在一起。
  • 資料表中的資料列太多
    - 從不刪除資料的資料表。
    - 由於很害怕刪除操作的結果,因此都透過邏輯上刪除等方式設計。
    - 如果資料列的數量確實很多,則考慮使用分割資料表(Partition Table)。
  • 「需要智慧」的欄位
    - 超過資料本身意涵的欄位,資料中未表現業務邏輯。
    - 例如:以 9 開頭的 ID 是管理者,以 1 開頭的 ID 是一般使用者。
  • 很害怕執行 UPDATE
    - 變更資料庫系統或改變資料內容是否會弄壞我的應用程行?因為害怕這些事而不敢碰觸它。

讓我們看一個「資料庫的不祥之兆」的例子。以下值可能會有資料破口。

Image for post
Image for post

如果你發現這樣的「資料庫的不祥之兆」,請首先考慮進行重構是否有意義,對你的工時有多大影響,以及現在是否應該這樣做。你需要排定優先處理的次序。

實際上,該資料破口的值在「失敗から学ぶ RDBの正しい歩き方」中被當作名為「データベースの迷宮」(資料庫迷宮)的 Anti-pattern。

四、那麼,理想的資料庫設計是什麼呢?

什麼是最理想的資料庫設計? 像是下面的例子,以下說明:

Image for post
Image for post

根據要求,我們假設了儲存使用者資訊時所需要的資料表設計:

  • 資料表:users
    所有使用者所屬的父資料表。基本上,只有 INSERT,不考慮 UPDATE 和 DELETE。
  • 資料表:user_detail
    附加到使用者的詳細資料記錄在這裡。如果要對所有的使用者增加欄位,請在這裡新增。為什麼不在 users 增加欄位?由於 users 是父資料表,因此更新父資料表存在著 deadlock 的風險。
  • 資料表:properties
    properties 主要儲存使用者的地址、電話號碼和其他必要的註冊資訊。我們採用 JSONB 型別。由於 PostgreSQL 具有將表示式作為 INDEX 的功能,因此即使使用 JSONB 類型,參照的效率也沒有問題。這樣可以輕鬆實現無 schema 的設計。主要缺點是因為它們易於部分更新,而且限制內容功能比較差。
  • 資料表:option
    option,是選項的字眼,假想儲存不一定需要的欄位。我們採用 JSONB 型別。
  • 資料表:user_token、user_auth_log、user_active、user_leave
    這 4 個資料表是很常見的資料表設計。
    這些資料表的問題在於,如果移除 users 裡的記錄,也無法移除這些資料的使用者記錄,因為這些資料表是作為外部鍵關連著的。
    但不幸的是,你可能已經移除了外部鍵關連或為 users 增加了一個名為 delete_flag 的欄位,而防止出現這種情況的機制是 user_active 與 user_leave。
    您可以根據使用者的狀態建立資料表,如果狀態變更了,請遷移資料表的資料並且做出回應。系統以 SQL 查詢有效的帳戶時,只需要查看 user_active 即可。
    可以透過 JOIN user_active 和 user_detail 來獲得大多數必要的資訊。許多情況是一對一的,而 JOIN 的成本是彼此的主鍵,是此例的精髓。

在以上的設計中,可以為每個狀態都建立一個資料表,但是基本上不會減少父資料表的 users。結果就是,users 的資料會越來越多。而且,當你要移除 users 時,有許多相關連的子資料表要同步處理,因此移除過程趨於複雜,而且很少有為 users 設定 delete_flag 欄位以表現為移除的情況。

此例的對策是使用外部鍵關連的 CASCADE。但是在許多情況下,移除 log 類型資料表會花費一些時間,並且會對系統造成負載壓力。

解決方案中的其中一個步驟是僅一次對一個資料表進行 CASCADE,將 log 類型的資料表以 RESTRICT 操作,首先針對這些資料庫進行移除,或者以 SET NULL 方式當作移除資料。

但是,如果要單獨保留 log,則需要建立具有相同資料表結構的舊 schema 等,並將其複製到相對的資料表。

許多移除使用者的請求具有更複雜的操作,如果在考慮到分散式儲存的時候,問題會更多。

這種情況下的答案只能依個案討論。因此在類似的問題或系統中,如果父資料表可能變得越來越大,我認為最好都先好好考慮一下。

什麼是 Schema?
在電腦的程式設計技術中定義邏輯和實體結構的規格。資料庫系統中資料庫物件(資料表、類別、檢視表、同義詞、使用者權限)的集合。Schema 將其集合成為一個實例。用資料庫術語來說,它意味著描述和定義資料庫規格。 以 COBOL 來說的話,是屬於 Data Division 的部份。

我的文章「僕の考えた最強のDB設計」(我眼中的最理想資料庫設計)中,對此進行了更詳細的說明。

五、如何學習資料庫系統? 「輸出導向」學習法的重要性

利用「輸出導向」學習法克服成長障礙

在如何學習資料庫之外,我想談談自我成長的策略。

Image for post
Image for post

如上所述,學習總是存在一些阻礙,而你必須逐步克服它們。

重要的是循序漸進,而在這裡我想在學習策略的概念中推薦「輸出導向」學習法。透過輸出自身的知識和經驗來學習,我認為是一種非常有效的成長方式。

例如,如果你透過讀書會來進行「輸出導向」學習法的話,那麼情況將如下所示:

<積極學習(知識之牆)>去讀書會學習你所不知道的事,向前輩討教
<具體實作(執行之牆)>實際操作你在學習中所學到的東西
<教學相長(了解之牆)>學習在讀書會上分享
<技術獲得(技術之牆)>申請成為研討會的講者
<持續成長(習慣之牆)>周而復始地成長

這是我在 25 歲時決定認真學習技術的方法。我想學習與專業無關的知識,但是我卻沒有時間和金錢。那時,我決定以「在讀書會上台分享」作為交付目標,創造出了一種必須努力的情況,而我就必須為此進行學習。

結語

參與各地的社群活動

東京的讀書會和社群的好處是,有很多機會與知名工程師交流,但你也應該更積極嘗試到市郊地區參與社群活動。有一些都市中所沒有的優勢,例如能夠與參與者進行更緊密交流、多樣化的社群組織形態、社群同好之間的連結也更加緊密。

我目前是日本 PostgreSQL 使用者社群的理事,負責在各地舉行學習交流會(活動列表)。我們社群從初學者到進階使用者都提供了各種課程。由於在全國各地都有分部,所以各地都有學習交流會的舉辦,有時我也會擔任活動的講師。如果可以的話,請一定要來參加。

Image for post
Image for post
曽根壮大さん
日本PostgreSQLユーザ会の理事
合同会社Have Fun Tech 代表

pgsql-tw

這裡是 PostgreSQL 台灣使用者社群,歡迎任何人投稿!

古哥

Written by

古哥

解決不了問題,就解決提出問題的人

pgsql-tw

pgsql-tw

你懂資料庫,資料庫就會幫你🐘

古哥

Written by

古哥

解決不了問題,就解決提出問題的人

pgsql-tw

pgsql-tw

你懂資料庫,資料庫就會幫你🐘

Medium is an open platform where 170 million readers come to find insightful and dynamic thinking. Here, expert and undiscovered voices alike dive into the heart of any topic and bring new ideas to the surface. Learn more

Follow the writers, publications, and topics that matter to you, and you’ll see them on your homepage and in your inbox. Explore

If you have a story to tell, knowledge to share, or a perspective to offer — welcome home. It’s easy and free to post your thinking on any topic. Write on Medium

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store