跳至主要内容

[DB] 資料庫基礎 Database Basic

[TOC]

資料庫是什麼​

資料庫的使用主要包含了三個部分「資料庫本身」、「DBMS」和「程式部分」：

  • DBMS：全名是 database management system，它是用來操作資料庫的軟體，也就是實際將資料庫中的資料進行新增、刪除、修改、更新的部分。另外，一般在說使用的是資料庫是 MySQL，或使用的資料庫是 PostgreSQL 時，實際上也是只 DBMS 的部分，而多數的 DBMS 都可以理解 SQL 的指令。
  • 程式：程式部分指的是使用者輸入資料，以及檢視資料的地方，以 Web 來說，通常包含整個前後端，使用者於前端輸入資料，前端將資料傳送到後端後，後端程式再透過 DBMS 的指令將資料保存在資料庫中，而前後端就是這裡所指的程式部分。

資料庫的分類​

RDBMS vs NoSQL​

資料庫主要可以分成兩類，關聯式資料庫（relational database）和非關聯式資料庫（NoSQL）：

  • 關聯式資料庫：常見開源免費的包含 MySQL 和 PostgreSQL；而付費的像是 Microsoft SQL Server 和 Oracle Database。
  • 非關聯式資料庫：常見以 Key-Value Store（KVS）方式儲存的是 Redis 或 Amazon DynamoDB；文件方式儲存的是 MongoDB 和 CouchDB；另外還有像是多節點連結的 Neo4j。

OLTP vs. OLAP​

也有以 Transaction 或 Analytic 來分類：

  • OLTP (Online Transaction Processing)
    • 適合用在有快速處理「交易（transaction）」需求的情境，需要確保資料寫入的正確性。常用於電商和銀行
    • 例如，MySQL、PostgreSQL、MongoDB
  • OLAP (Online Analytical Processing)
    • 適合用在有「複雜分析、查詢」資料需求的情境。常用於 BI 分析、產出財務報告、資料倉儲（data warehouse）
    • 專注於 read-heavy，可以直接透過負責的 query 來組出所需要的資料
    • 例如，Google BigQuery、Amazon Redshift、ClickHouse

Row-oriented vs. Columnar​

  • Row-oriented
    • 將資料以 row 的方式保存，適合用在 transaction processing。舉例來說，資料會這樣被保存
      • Row 1: [1, "Alice", 50]
      • Row 2: [2, "Bob", 60]
      • 例如，MySQL、PostgreSQL、MongoDB
  • Columnar
    • 將資料以 column 的方式保存在不同的檔案或區塊，適合用在 analytic processing。舉例來說，資料會這樣被保存
      • Column 1：[1, 2]
      • Column 2：["Alice", "Bob"]
      • Column 3：[50, 60]
    • 例如，Google BigQuery、Amazon Redshift、ClickHouse

伺服器型資料庫 vs. 檔案型資料庫​

資料庫也可以另外分成「伺服器型資料庫」和「檔案型資料庫」：

  • 伺服器型資料庫：幾乎所有 RDBMS 都是伺服器型資料庫，這種類型的資料庫需要先將資料庫安裝於電腦主機上才能使用。
  • 檔案型資料庫：一個檔案本身就是一個資料庫，像是 SQLite 或 Microsoft Access 都是這種類型的資料庫，適合用在資料量較少，且不會有多人同時存取的情況。

使用資料庫的好處​

使用資料庫有一個很重要的好處是讓「程式」和「資料庫」解耦，也就是說，未來如果想要更換不同的資料庫，可以讓程式不用大幅調整的情況下，進行資料庫的更換。

資料表的設計​

30-14 之資料庫層的優化 - 表的設計 @ mark-lin

字串​

  • 欄位中的字串長度會變動的話： varchar
  • 欄位中的字串長度固定且可能經常更新：char

數值​

  • 不需要負數時，使用 unsigned 可以有兩倍大的儲存空間
  • 選擇合適的儲存大小，能用 int 就不需要使用 bigint
  • 金額使用 decimal

主鍵（Primary Key）​

  • 不要使用太長的值（例如，uuid）當作 PK，會影響到性能。

其他​

  • 儘量不要允許 null，效能會更好，且在 count 時不會有不同的結果
  • 建議在表中加入註解

Common Concurrency Problems​

In database management systems, a phantom read occurs when one user is repeating a read operation on the same records but has new records in the result set. When this isolation level is used, a transaction can read uncommitted data that later may be rolled back1.

Phantom reads occur when a transaction reads a variable once but when it tries to read that same variable again, an error occurs saying that the variable does not exist1.

四種常見的 currency problem 分別是：

  • Dirty Read：指的是讀取 uncommitted data。這會發生在當一個 App 在修改資料但還沒 committed 前，另一個 App 又在讀取這筆資料。這會導致讀取的資料不正確或不一致
  • non-repeatable read
  • phantom read：當使用者重複讀取相同資料時，卻得到了不同的結果

為了避免 dirty reads，SQL 提供了不同的 transaction isolation levels，由低到高依序是：

  • Read Uncommitted：這個 level 允許 transaction 可以讀取 uncommitted data，這會導致 dirty read
  • Read Committed：這個 level 的 transaction 只能讀取 committed data，可以避免 dirty read
  • Repeatable Read：這個 level 除了可以避免 dirty read，同時還能確保相同 query 的 transaction 一定能得到相同的結果，即便其他 transaction 同時在修改資料
  • Serializable：這個 level 提供了最高層次的 isolation levels，確保每一個 transaction 都是依序執行，可以避免 dirty read 和其他異常產生

兩種 locks：

  • S Lock（shared lock）：如果 transaction A 鎖住了資料，transaction B 只能讀取資料，不能修改它
  • X Lock（exclusive lock）：如果資料被執行了 X Lock，任何的 transaction 都不能讀取或修改這個資料

資料表正規化​

30-17 之 MongoDB 的設計---正規與反正規化的戰爭 @ mark-lin

  • 第一正規形式：欄位中不要有重複群組（像是陣列的東西）
  • 第二正規形式：去除相依性（拆成多個關聯的表）
  • 第三正規形式：刪除不依賴索引鍵的欄位（可以透過其他欄位計算得出的值不儲存）

交易（transaction）的特性：ACID​

  • Atomicity（原子性）：只會是「全部執行」或「全部尚未執行」的其中一種狀態
  • Consistency（ㄧ致性）：交易前後資料的完整性受到保持，例如透過定義 Model Schema
  • Isolation（隔離性）：交易執行時，其他使用者看不到正在處理的資料，也不會受到影響
  • Durability（持久性）：交易完成後將永久被儲存

鎖定與死結（lock &deadlock）​

  • 共享鎖（讀鎖）：自己正在讀取，限制他人無法寫入
  • 排他鎖（寫鎖）：自己正在寫入，限制其他人無法讀取（因為值可能還會改變）和寫入

資訊安全的三原則：CIA​

  • Confidentiality（機密性）：只有擁有正當權利的人才能存取
  • Integrity（完整性）：對資料的操作與修改都是經過授權而不會被竄改
  • Availability（可用性）：有權限的使用者可以即時且不中斷地存取資料

參考資料​