# Draft: 關聯式資料庫需要知道的幾件事

### 1. 關聯式資料庫特性

#### a. ACID

主要適用於描述 對於 Transaction 的操作，需具備的特性 Atomicity(A), Consistency(C), Isolation(I), Duration(D)。

RDBMS 中的 `Transaction` 是一系列的讀寫(Read/Write)操作直到 Commit 成功，而這一組操作會被當成一條 SQL 進行執行的。這一段我們稱為 `Transaction` ***而單一個 Select, Insert, Update, Delete 是不被稱為 Transaction \[1]***。

`Integrity Constraints` 指的是預先設定的 DB Constraints。ex: Foriegn Key, Primary Key....

* A:  針對 `Transaction` 只許成功，否則要回復上一動。也就是說 `Transaction` 包含一系列操作，如果中途失敗的時候，不會有做到一半失敗而造成資料不一致的問題，失敗就會回去未施作的狀態。
* C: 當 `Transaction` 執行中的 `Before`, `After` 。DB 的 Integrity Constraints 不應該被改變。   
* I: Transaction 之間保證不會相互影響
* D: 只要 Transaction Committed 資料就是永久改變，而不會有因為 DB Crash 而無效

#### b. CAP  Theory\[3]

CAP 理論是分布式系統設計中的思考框架，主要講系統對 data model, operating 處理上的三個特性： **Data model -> Consistency; Operating -> Availability, Partition-Tolerance 而**經過證明三個特型是無法同時兼容的，只能同時兼容 2 者。 

<figure><img src="https://853423727-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-MZBn9owI5fBe0HrECdk%2Fuploads%2FygMlOxPrKU3MdaRGB0Ll%2Fimage.png?alt=media&#x26;token=1c9fd327-9392-42d4-8267-490b6c5dcb3e" alt=""><figcaption></figcaption></figure>

* **Consistency: 指的是data model 可以保證其一制性，且就像 multi-thread 共用記憶體一樣，每一次都只有一個 thread 可以影響。**
* **Availability*****:*****&#x20;指的是當node 在運行的時候，每一個query 都一定會收到response**
* **Partition-Tolerance:** **允許 node 之間可以接受掉資料，且不影響運行**

而從上述的三個特性，實際上不能玩全兼容，舉個例子\[4]，在一個 async 的網路中，同時具備 CAP 三的特性的系統正在運行，當有兩個 request 發生的同時，對兩個不同的 node 進行 read/write ，因為P的特性，可接受掉資訊，因此同時就違反 C 的原則，無法達到 data model 的 consistence 的特性。&#x20;

因此可歸納出可行的特性如下：

* AP: 不管是在 single-node 或者是 cluster，只有要 node 可用就能持續運作
* CA: 在沒有任何的例外下，都是可用並保證資料的一致性。
* CP: 在 node 之前不穩定的時候不接受 request。

### 2. How Does Database Store Data?

* Data structure

Relation Database 的資料結構稱之page，key 指的是一個 row 的 unique key，而 Row 是指記憶體位置。Pages 是以 B-Tree 儲存，

1. &#x20;Non-Leaf 的 Page 會是指的是 index 而這樣的 row 會指設到下一個 Non-Leaf 的 Page 或者 Leaf-Page
2. &#x20;Leaf Page 指的是實際的 table row

<figure><img src="https://853423727-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-MZBn9owI5fBe0HrECdk%2Fuploads%2FDxDWHxGjNMbKYUQ9fN1W%2Fimage.png?alt=media&#x26;token=c1d77ec8-0c3b-48ba-96be-b9fcc8d94ea9" alt=""><figcaption><p>ref:  <a href="https://www.pragimtech.com/blog/sql-optimization/how-do-sql-indexes-work/">https://www.pragimtech.com/blog/sql-optimization/how-do-sql-indexes-work/</a></p></figcaption></figure>

* How Do Database Retrieve data?

在資料庫在讀取資料的時候，要找所對應的page 且輸出給 user。而實際的 activities 如下圖\[6]\[7]：

1. Read Cache:  從cache 取得 page，時間約 0ms
2. Read Buffer: 從 buffer queue 取得時間約 3 ms
3. Read Disk: 從 disk 取得所要的pages
   1. Random Read: about 4 or 8k/s. usually use in specific search ex: where a = :a
   2. Sequential Read: about 40 MB /s. usually use in range search ex: where a > :a
4. Rotate Buffer: 對buffer 進行更新
5. Store cache: 更新cache

> 因此如果需要從 disk 找到資料約10ms的時間&#x20;

{% @mermaid/diagram content="stateDiagram-v2
state is\_end <<choice>>
is\_end --> \[\*]
state is\_read\_cache <<choice>>
is\_read\_cache --> is\_end: found in cache (0ms)
state is\_read\_buffer <<choice>>
is\_read\_buffer --> store\_into\_cache: found in buffer
store\_into\_cache --> is\_end

```
[*] --> Read_Cache
Read_Cache --> is_read_cache
is_read_cache --> Read_Buffer: not found in cache
Read_Buffer --> is_read_buffer
is_read_buffer --> Read_disk: (4ms)
Read_disk --> Rotate_buffer: (random or sequential read)
Rotate_buffer --> is_read_buffer: (1ms)" fullWidth="false" %}
```

* What is the Pre-Fetch in RDBMS?

Pre-Fetch 的意思是指在搜集需要的資料的時候，當找到全部 hit 到 index之後再進行 Retrive data，可參考下圖; 反之沒有Pre-Fetch 就是當Scan 一個 index 之後直接去 retrieve 資料，變成是 sequence 的方式。

<figure><img src="https://853423727-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-MZBn9owI5fBe0HrECdk%2Fuploads%2F1x9QZIu6NBAy8m0W5okm%2Fimage.png?alt=media&#x26;token=f4055001-33c2-4267-b005-d222bb242890" alt=""><figcaption><p>ref:[7] figuer 2.7</p></figcaption></figure>

### 2. Entity Relation Diagram

### 3. RDBMS Index Category

#### How Does DB store data?

4\. RDBMS Lock

### 5. Performace Tunning&#x20;

### Reference

\[1] <https://www.geeksforgeeks.org/sql-transactions/>\
\[2] <https://www.geeksforgeeks.org/acid-properties-in-dbms/>\
\[3] Davoudian, Ali, Liu Chen, and Mengchi Liu. "A survey on NoSQL stores." *ACM Computing Surveys (CSUR)* 51.2 (2018): 1-43.\
\[4] Rao, Vikram, and Steven Guan. "Brewer’s conjecture and the feasibility of consistent, available, partition-tolerant web services."\
\[5] <https://www.pragimtech.com/blog/sql-optimization/how-do-sql-indexes-work/>\
\[6] [ https://dev.mysql.com/doc/refman/8.4/en/innodb-buffer-pool.html](https://dev.mysql.com/doc/refman/8.4/en/innodb-buffer-pool.html)\
\[7] Lahdenmaki, Tapio, and Mike Leach. *Relational Database Index Design and the Optimizers: DB2, Oracle, SQL Server, et al*. John Wiley & Sons, 2005.<br>