• 資料庫效能完整指南：ACID、索引優化、分區、分片、鎖與 SQL/NoSQL 架構

ACID (Atomicity, Consistency, Isolation, Durability) 特性

在資料庫管理系統（DBMS）中，資料庫交易（Database Transaction）是指一組資料庫操作，被視為單個工作單元，要麼完全執行，要麼完全不執行的過程。資料庫交易必須遵循ACID屬性，才可以被提交

PS 使用 COMMIT 命令，才會確保這些更改永久保存在資料庫中

SQL 關聯性資料庫

  • Atomicity (原子性) : 資料操作不可分割。一次 transaction 只能成功或失敗

  • Consistency (一致性)：transaction 完成前後，資料都必須永遠符合 schema 的規則，如果錯誤，可通過調整 schema 來維護

  • Isolation (隔離性)：資料庫允許多個 transactions 同時對資料交叉執行，但執行不應互相干擾、不會導致數據的不一致

    數據腐敗

      # 在創建索引的同時，有一個新的用戶被添加到數據庫中，但此修改尚未納入索引。這將導致索引中缺少新用戶的數據，即數據腐敗
  # 創建用戶表
  CREATE TABLE users (user_id INT PRIMARY KEY, username VARCHAR(255));

  # 添加一個用戶
  INSERT INTO users (user_id, username) VALUES (1, 'John');

  # 創建用戶ID索引 
  CREATE INDEX idx_user_id ON users (user_id);

  # 同時添加另一個用戶，但在創建索引之前
  INSERT INTO users (user_id, username) VALUES (2, 'Jane');

    有許多隔離級別，可以防止數據腐敗

    — Serializable（序列化) : 最高的隔離級別，可以確保事務的【完全隔離】，對所有讀取的資料加鎖，並確保其他交易無法對這些資料進行讀取或寫入操作，但也可能導致系統性能下降，因為它阻止了同時執行多個事務　(PS SELECT FOR UPDATE 鎖定特定行; Serializable 是一個整體的數據庫隔離機制，確保整個事務的一致性和隔離性)

    — Repeatable Read（可重複讀取) : 即使有其他事務對資料進行了更新，也不會讀取到不一致的結果，但仍然可能會出現幻讀的情況

    — Snapshot (快照) : 事務可以查看從開始到結束的數據快照，即使其他事務正在對數據進行更改，當前事務也可以看到開始時的數據狀態。如果多個事務正在對同一行進行更改，可能會發生衝突。在這種情況下，通常會使用鎖定機制或版本控制 (PS MYSQL 沒有，POSTGRE SQL 可以使用 SYSTEM_TIME AS OF TIMESTAMP ‘2023-10-10 12:00:00’)

    — Read Committed（已提交讀取) : 讀取操作【僅返回】【已提交】的數據，並且不會讀取到未提交的數據，避免髒讀和不可重複讀的問題，但仍然可能會出現幻讀的情況

    –Read Uncommitted（未提交讀取) : 允許事務讀取未提交的變更，可能導致髒讀、不可重複讀和幻讀

      # 設置隔離級別，一定要先 BEGIN TRANSACTION
  BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;

      # 如果要查詢事務時間
  SELECT * FROM table_name AS OF TIMESTAMP 'yyyy-mm-dd hh:mm:ss';

  SELECT * FROM table_name VERSIONS BETWEEN TIMESTAMP 'yyyy-mm-dd hh:mm:ss' AND TIMESTAMP 'yyyy-mm-dd hh:mm:ss';

    PS PostgreSQL 在 REPEATABLE READ，可以防止幻讀。但在一些其他的關係型數據庫系統（如 SQL Server、MySQL 和 Oracle）中，只使用 REPEATABLE READ，可能無法完全防止幻讀的發生

      # 範例 
  # read committed，transaction 1 ，在未提交的情況下，一個事務將不會看到另一個事務所進行的任何更改，只能讀取到自己的，會讀出 2-4
  # repeatable read transaction 2 會讀出 2-4，但在其他數據庫平台中可能讀出 2-4-5    # repeatable read 只能保證讀取的值保持不變，但不適用於幻讀，因此 t2 可能會讀取到事務 t1 提交的值 5

  create table test (id integer)
  insert into test (2);

  begin transaction 1
  t1 - set isolation level read committed

  begin transaction 2
  t2 - set isolation level repeatable read

  t1 - select * from test;
  t2 - insert into test (4);
  t1 - select * from test;
  t1 - insert into test (5);
  t2 - select * from test;
  t1 - commit;
  t2 - select * from test;
  t2 - commit;

    螢幕擷取畫面 2023-11-06 135245.png

    不同的isolation下，可能會出現不同的讀取現象

    — Dirty reads（髒讀）：當一個事務讀取了另一個事務更新但尚未提交的資料時發生。第二個事務在第一個事務更新但尚未提交的情況下讀取了資料，導致第二個事務能會讀取到未確認的、無效的資料

      CREATE TABLE accounts (account_id INT PRIMARY KEY, balance DECIMAL(10, 2));
  INSERT INTO accounts (account_id, balance)VALUES (1, 1000.00);

  執行兩個連續的事務（Transaction）

  -- 事務 1 
  START TRANSACTION;
  UPDATE accounts
  SET balance = 500.00
  WHERE account_id = 1;

  -- 事務 2
  START TRANSACTION;
  SELECT * FROM accounts
  WHERE account_id = 1; 
  COMMIT;

  -- 回到事務 1，這時事務 2 可能讀取到事務1尚未提交的更新結果，導致事務2看到的資料是無效的或不一致的
  COMMIT;

    — Non-repeatable reads（不可重複讀）：當一個事務在相同的查詢中多次讀取同一行資料時，由於第二個事務已經對資料進行了更改或刪除，兩次讀取的結果會不一致

      CREATE TABLE accounts ( account_id INT PRIMARY KEY, balance DECIMAL(10, 2));
  INSERT INTO accounts (account_id, balance) VALUES (1, 1000.00);

  執行兩個連續的事務（Transaction）

  -- 事務 1
  START TRANSACTION;
  SELECT * FROM accounts
  WHERE account_id = 1;
  COMMIT;

  -- 事務 2
  START TRANSACTION;
  UPDATE accounts
  SET balance = 500.00
  WHERE account_id = 1;
  COMMIT;

  -- 回到事務 1，可能導致在事務1內兩次讀取，出現了不一致的結果
  SELECT * FROM accounts
  WHERE account_id = 1; 
  COMMIT;

    — Phantom reads（幻讀）：當一個事務在相同的查詢中多次讀取同一行資料時，由於第二個事務已經對資料插入了新的資料行，兩次讀取的結果會不一致

      CREATE TABLE products(product_id INT PRIMARY KEY, product_name VARCHAR(255)); 
  INSERT INTO products (product_id, product_name) VALUES (1, 'Product A'),(2, 'Product B');

  執行兩個連續的事務（Transaction）

  -- 事務 1
  START TRANSACTION;
  SELECT * FROM
  products;
  COMMIT;

  -- 事務 2
  START TRANSACTION;
  INSERT INTO products
  (product_id, product_name) VALUES (3, Product C'); 
  COMMIT; 

  -- 回到事務 1 
  SELECT * FROM products; 
  COMMIT;

    — Lost updates（丟失更新）：兩個或多個事務同時操作相同資料時，最後只有一個事務的更改被保留，而其他事務的更改被覆蓋並丟失

      CREATE TABLE accounts( account_id INT PRIMARY KEY, balance DECIMAL(10,2));
  INSERT INTO accounts (account_id, balance) VALUES (1, 1000.00);

  執行兩個連續的事務（Transaction）

  -- 事務 1
  START TRANSACTION;
  UPDATE accounts
  SET balance = balance - 100
  WHERE account_id = 1;
  COMMIT;

  -- 事務 2
  START TRANSACTION;
  UPDATE accounts
  SET balance = balance - 200
  WHERE account_id = 1;
  COMMIT;

      PS 可以使用適當的【鎖定機制】或其他並發控制機制來協調事務之間對相同資料的操作

  -- 事務 1
  START TRANSACTION;
  SELECT balance INTO @balance
  FROM accounts
  WHERE account_id = 1
  FOR UPDATE;

  UPDATE accounts
  SET balance = @balance - 100
  WHERE account_id = 1;
  COMMIT;

  -- 事務 2
  START TRANSACTION;
  SELECT balance INTO @balance
  FROM accounts
  WHERE account_id = 1
  FOR UPDATE;

  UPDATE accounts SET balance = @balance - 200
  WHERE account_id = 1;
  COMMIT;

      PS 多版本並發控制（MVCC），是一種更高級的並發控制機制。通過為每個事務創建數據行的多個版本，從而使多個事務能夠同時讀取和修改數據，MVCC會根據每個事務的時間戳或版本信息來處理這些操作，不會產生阻塞或衝突

  CREATE TABLE accounts( account_id INT PRIMARY KEY, balance DECIMAL(10, 2)) 
  ENGINE = InnoDB; -- 使用 InnoDB 引擎以支持 MVCC

  INSERT INTO accounts (account_id, balance) VALUES (1, 1000.00);

  -- 事務 1 
  START TRANSACTION;
  SELECT balance FROM accounts WHERE account_id = 1;

  -- 事務 2
  START TRANSACTION;
  UPDATE accounts
  SET balance = balance - 100
  WHERE account_id = 1;
  COMMIT;

  -- 回到事務 1
  SELECT balance FROM accounts WHERE account_id = 1;
  COMMIT;

      PS 樂觀鎖定（Optimistic Locking） ，基於版本標記來檢測並處理衝突

  CREATE TABLE accounts( account_id INT PRIMARY KEY, balance DECIMAL(10,2), version INT ) ENGINE = InnoDB; -- 使用 InnoDB 引擎以支持 MVCC 

  INSERT INTO accounts (account_id, balance, version) VALUES (1, 1000.00, 1);

  -- 事務 1
  START TRANSACTION;
  SELECT balance INTO @balance, version INTO @version FROM accounts WHERE account_id = 1;

  -- 事務 2
  START TRANSACTION;
  SELECT balance INTO @balance, version INTO @version FROM accounts WHERE account_id = 1;

  UPDATE accounts
  SET balance = @balance - 100, version = @version + 1 WHERE account_id = 1 AND version = @version; 
  COMMIT;

  -- 回到事務 1 
  UPDATE accounts
  SET balance = @balance - 200, version = @version + 1 WHERE account_id = 1 AND version = @version;
  COMMIT;

      悲觀鎖定（Pessimistic Locking），會直接鎖定資料行，阻止其他事務對資料進行讀取或修改。通常不直接使用版本號，想顯示版本號，可以修改 SELECT 語句

  CREATE TABLE accounts( account_id INT PRIMARY KEY, balance DECIMAL(10,2), version INT ) ENGINE = InnoDB; -- 使用 InnoDB 引擎以支持 MVCC 

  INSERT INTO accounts (account_id, balance, version) VALUES (1, 1000.00, 1);

  -- 事務 1
  START TRANSACTION;
  SELECT balance, version INTO @balance, @version FROM accounts WHERE account_id = 1 FOR UPDATE; 

  -- 事務 2
  START TRANSACTION;
  SELECT balance, version INTO @balance, @version FROM accounts WHERE account_id = 1 FOR UPDATE;

  UPDATE accounts
  SET balance = balance - 100 WHERE account_id = 1;
  COMMIT;

  -- 回到事務 1 
  UPDATE accounts
  SET balance = balance - 200
  WHERE account_id = 1;
  COMMIT;

    PS 樂觀鎖定（Optimistic Locking） 、悲觀鎖定（Pessimistic Locking）和設置隔離性（Isolation）差異

    樂觀鎖定和悲觀鎖定是處理【並發控制】的不同策略，而隔離性則確保事務的執行【不會相互干擾】

    樂觀鎖定 : 假設並發衝突不太發生，直到實際進行更新操作時才檢查。在進行更新之前，系統假設沒有其他事務會修改相同的數據。常見的方式是在數據中引入版本號或時間戳，當更新時檢查版本號，如果版本號不匹配，表示有其他事務已經修改了數據

    悲觀鎖定 : 假設在進行任何操作之前，數據可能會被其他事務修改，因此在事務開始時就鎖定數據。通常使用各種類型的鎖（例如行鎖或表鎖）來實現。這可能導致性能問題，因為它阻止了其他事務對同一數據進行修改

    隔離性 : 指事務之間的相互影響程度。在並發環境中，多個事務可能同時訪問和修改相同的數據，而隔離性確保每個事務看到的數據是一致的

  • Durability (持久性)：transaction 送出成功後，對資料的操作就是永久的，不可回朔上一步驟，即使系統發生故障也應該保持

Nosql 非關聯性資料庫

  • Atomicity (原子性) : 資料操作不可分割。一次 transaction 只能成功或失敗
  • Consistency (一致性)：最靈活的部分，資料不需要保持一致性，取決於具體的系統需求
  • Isolation (隔離性)：Nosql比較注重效能和擴展性，可能導致一些特定情況下的數據不一致
    在一些 NoSQL 數據庫中，如分佈式數據庫，通常對隔離性的要求會比傳統的關聯式數據庫要求低，是為了保證更高的效能和擴展性，例如高度分佈的環境中，多個用戶或訪問數據庫時，可能導致一些特定情況下的數據不一致
  • Durability (持久性)：transaction 送出成功後，對資料的操作就是永久的，不可回朔上一步驟，即使系統發生故障也應該保持

Commit (提交)

  • “commit” 是指確認一個事務的結束，並將所做的變更永久地應用在資料庫，無法回復

START TRANSACTION;、ROLLBACK;

可以使用 START TRANSACTION 或 BEGIN 命令開始，做一些變更後用ROLLBACK 命令，更改並回到事務開始的狀態

CREATE DATABASE IF NOT EXISTS try;

USE try;

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(255)
);

START TRANSACTION;

INSERT INTO customers (customer_id, customer_name)
VALUES
    (1, 'Alice'),
    (2, 'Bob'),
    (3, 'Charlie'),
    (4, 'David');

SELECT * FROM customers;

ROLLBACK;

INDEX

索引（Index）用於快速查找數據的數據結構

  • 優點: 加速查找、強調唯一性、可以進行排序和分組操作

INDEX 標準索引

CREATE TABLE orders (order_id INT PRIMARY KEY, customer_id INT, order_amount DECIMAL(10,2));

# 創建索引
CREATE INDEX idx_customer_id ON orders (customer_id);

# 搜尋
SELECT customer_id, SUM(order_amount) as total_amount FROM orders GROUP BY customer_id;

聯合索引(Composite Index)、包含索引（Covering Index），使查詢更加高效

CREATE TABLE orders (order_id INT PRIMARY KEY, customer_id INT, order_amount DECIMAL(10,2));

# 創建Composite Index)
CREATE INDEX idx_customer_id ON orders (customer_id, order_id);

# 搜尋並使用Covering Index
SELECT customer_id, SUM(order_amount) as total_amount FROM orders GROUP BY customer_id ORDER BY order_id;

PS 如果同上方設置 Composite Index
“`=
EXPLAIN ANALYZE SELECT id FROM orders WHERE customer_id = 10;

第二個索引可能不會被默認使用
EXPLAIN ANALYZE SELECT id FROM orders WHERE order_id = 10;



>PS postgres sql, 包含索引寫法不同
```=
CREATE INDEX idx_customer_id ON orders (customer_id) INCLUDE (order_id);

B+Tree，查詢上的使用方式基本上相同，通常在範圍查詢方面更加有效，更適合用於【範圍查詢和排序操作】，因為節點中只存儲鍵值，而所有數據都存儲在葉節點中

CREATE INDEX bplus_index ON table_name (column_name);

B-Tree，查詢上的使用方式基本上相同，每個節點儲存鍵值和指向子節點的指針

CREATE INDEX b_index ON table_name (column_name);

缺點: 佔用儲存空間、原始數據更改時，INDEX也要更改、過多的索引存在時，可能會導致數據庫的性能下降

CREATE TABLE customers (customer_id INT PRIMARY KEY, customer_name VARCHAR(255));

CREATE INDEX idx_customer_name ON customers (customer_name);

# 更新 table
UPDATE customers SET customer_name = 'New Customer Name' WHERE customer_id = 123;

# 重新構建索引
ALTER TABLE customers DROP INDEX idx_customer_name, ADD INDEX idx_customer_name ON customers (customer_name);

PS 我們說的資料庫通常是 B-Tree (Balanced Tree) ，是一種自平衡的樹狀結構，它允許在 O(log n) 的時間內進行插入、查詢和刪除操作
LSM Tree 是一種日誌結構合併樹，它將數據分為多個層級進行儲存，數據首先被添加到記憶體中的日誌，然後定期合併到磁盤上的較長數據文件中，例如 Elastisearch

PS PostgreSQL、MySQL 在 B-Tree 差異

PostgreSQL : 主索引（Clustered Index）是整個表，在查找主索引中的鍵時，可以立即獲取該鍵所在的完整行，無需進行其他I/O操作、次要索引（Secondary Index）的葉子節點通常包含主鍵的值，索引在查找鍵時，需要進行額外的I/O操作來檢索完整的行數據

MySQL　： 主索引（Clustered Index）值是完整的行對象，但與PostgreSQL的情況類似，主索引實際上也是整個表、次要索引（Secondary Index）值是指向主索引中行數據實際位置的指針，意思是次要索引的葉子節點，包含指向主索引中對應行的指針

複製 TABLE、INDEX

CREATE TABLE new_table_name LIKE existing_table_name;

# 查看現有表的創建語句，包括索引
SHOW CREATE TABLE existing_table_name;

# 手動添加索引到新表
CREATE INDEX index_name1 ON new_table_name (column1); CREATE INDEX index_name2 ON new_table_name (column2);

PS postgres sql 可以一起添加

CREATE TABLE new_table_name LIKE existing_table_name INCLUDING INDEXES;

Scan types、EXPLAIN ANALYZE TABLE

  • Full Table Scan”（全表掃描）: 通常在小型表或沒有適合的索引時發生，但對於大型表或需要過濾大量數據時，它可能會導致性能問題
SELECT * FROM table_name;
  • Index Scan（索引掃描）: 可以快速定位符合查詢條件的行，但如果需要檢索大量行，則可能需要進行額外的 I/O 操作
SELECT * FROM table_name WHERE indexed_column = 'value';

  • Bitmap Index Scan（位圖索引掃描）: 可以有效地避免不必要的 I/O 操作和資源浪費

    SELECT * FROM table_name WHERE column1 = 'value1' AND column2 = 'value2';

  • EXPLAIN Analyze table 優化查詢性能 : 用於分析和更新表的統計信息，幫助 MySQL 優化器生成更有效的查詢計劃 (PS DESCRIBE/EXPLAIN 用於瞭解表的結構和設計)

    EXPLAIN ANALYZE TABLE table_name;

PS 很常搭配的 AND VS OR
OR操作需要對兩個條件進行分別的搜索，再合併結果，可能導需要更多時間來處理數據，相比之下，AND操作只需要一次搜索，通常比OR操作更有效率

哈希函數

  • MD5（Message Digest Algorithm 5） : 32位元（128位元）的哈希函數，較短的位元長度和已知的安全漏洞，已經不再被廣泛使用

  • SHA1（Secure Hash Algorithm 1） : 160位元的哈希函數，SHA1比MD5更安全，但仍存在某些碰撞攻擊風險

  • SHA2 : SHA2是SHA家族中的一個新版本，包括多個變體（例如SHA-224、SHA-256、SHA-384、SHA-512等）

使用哈希表來快取資料庫查詢的結果，來提高效能

# mysql
CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100)
);

INSERT INTO users (id, username, email) VALUES
(1, 'user1', 'user1@example.com'),
(2, 'user2', 'user2@example.com'),
(3, 'user3', 'user3@example.com');

import pymysql
from collections import defaultdict

class UserCache:
    def __init__(self):
        self.cache = defaultdict(dict)
        self.db = pymysql.connect(host="your_host", user="your_user", password="your_password", db="your_db")

    def get_user(self, user_id):
        # 從快取中尋找用戶資訊
        if user_id in self.cache['users']:
            print("Cache hit!")
            return self.cache['users'][user_id]

        # 如果快取中沒有，從資料庫查詢
        cursor = self.db.cursor()
        query = "SELECT * FROM users WHERE id = %s"
        cursor.execute(query, (user_id,))
        user_data = cursor.fetchone()

        if user_data:
            # 將查詢結果放入快取
            self.cache['users'][user_id] = user_data
            return user_data
        else:
            return None

user_cache = UserCache()

# 第一次查詢
user_data_1 = user_cache.get_user(1)
print(user_data_1)

# 第二次查詢 
user_data_1_cached = user_cache.get_user(1)
print(user_data_1_cached)

# 新的查詢
user_data_2 = user_cache.get_user(2)
print(user_data_2)

使用SHA2哈希函數對密碼進行加密，並將加密後的密碼存儲到數據庫中

UPDATE users SET password = SHA2('user_password', 256) WHERE username = 'desired_username';

數據校驗，生成與數據相關的哈希值。如果數據被修改，哈希值會發生變化，提醒您數據已被修改

CHECKSUM TABLE table_name;

數據識別和查詢，識別數據的唯一性，方便查詢或識別中使用

SELECT MD5(column_name) AS hash_value FROM table_name;

數據查找和驗證

SELECT SHA1('your_value');


SELECT * FROM table_name
WHERE hash_column = SHA1('your_value');

分區 partitioning

使用分區 partitioning，將數據分成多個獨立數據庫(子集)

  • 優點: 查詢效率增加、更容易維護、通過分散數據和查詢負載，可以提高性能
  • 缺點: 複雜性增加、需要更多存儲空間、過多的分區可能會導致查詢性能下降

BY RANGE

CREATE TABLE food (id INT PRIMARY KEY, name VARCHAR(255), category VARCHAR(255))

PARTITION BY RANGE (id) ( PARTITION p1 VALUES LESS THAN (3001), PARTITION p2 VALUES LESS THAN MAXVALUE);

通過指定分區來執行搜尋，提高搜索效率

SELECT * FROM food PARTITION (p1) WHERE id BETWEEN 1 AND 100;

練習
“`=
ALTER TABLE new_food
PARTITION BY RANGE (id) (
PARTITION p1 VALUES LESS THAN (51),
PARTITION p2 VALUES LESS THAN MAXVALUE
);

SELECT * FROM new_food PARTITION (p1) WHERE id BETWEEN 1 AND 20;

BY LIST



```=
CREATE TABLE food_list_partition (id INT PRIMARY KEY, name VARCHAR(255), category VARCHAR(255))

PARTITION BY LIST (category) (PARTITION p_meat VALUES IN ('beef', 'chicken'), PARTITION p_vegetable VALUES IN ('carrot', 'broccoli'), PARTITION p_fruit VALUES IN ('apple', 'banana'), PARTITION p_other VALUES IN (DEFAULT));

SELECT * FROM food_list_partition PARTITION (p_meat);

# 等同於
SELECT * FROM food_list_partition WHERE category IN ('beef', 'chicken');

BY HASH

CREATE TABLE food_hash_partition (id INT PRIMARY KEY, name VARCHAR(255), category VARCHAR(255))

PARTITION BY HASH (id) PARTITIONS 4;

SELECT * FROM food_hash_partition 
WHERE id = 1;

查看分區

SELECT TABLE_NAME, PARTITION_NAME, PARTITION_ORDINAL_POSITION, PARTITION_METHOD, PARTITION_DESCRIPTION 
FROM information_schema.PARTITIONS 
WHERE TABLE_SCHEMA = 'database_name' AND TABLE_NAME = 'table_name' 
ORDER BY PARTITION_ORDINAL_POSITION;

# PARTITION_ORDINAL_POSITION：分區的順序位置
# PARTITION_METHOD：使用的分區方法

查看是否啟用分區修剪

SHOW VARIABLES LIKE 'optimizer_switch';

<> 關閉 
SET GLOBAL optimizer_switch ='index_condition_pushdown=off';

垂直分割 Vertical Partitioning、水平分割 Horizontal partitioning

垂直分割 Vertical Partitioning
一些欄位經常被查詢，而其他欄位很少被查詢、保護敏感數據，可以使用垂直分割

  • 優點: 查詢效率增加，因為列變少了、可以通過限制數據訪問來提高安全性

  • 缺點: 複雜性增加，因為涉及多個表的查詢、可能需要更多的 join 操作、可能需要處理數據一致性和完整性的問題

CREATE TABLE customer_basic_info SELECT id, name, email, address FROM customers;

CREATE TABLE customer_sensitive_info SELECT id, phone_number FROM customers;

水平分割 Horizontal partitioning
需要降低單個表的大小、提高性能可以考慮水平分割

  • 優點: 查詢效率增加、更容易地擴展和分佈數據庫

  • 缺點: 複雜性增加，因為過多的分割表、需要更多的存儲空間和硬件資源

CREATE TABLE orders_2022 SELECT * FROM orders WHERE order_date >= '2022-01-01' AND order_date < '2023-01-01';

CREATE TABLE orders_2023 SELECT * FROM orders WHERE order_date >= '2023-01-01';

分片 Sharding

主要用於橫向擴展和分散數據，當單個數據庫實例無法應對高流量或大型數據量時，分片可以幫助平衡負載，提高性能並提供更好的擴展性

  • 優點: 可擴展性、單一故障點的影響範圍更小，可用性提高、通過平行處理和分散查詢負載，可以提高數據庫的查詢性能

  • 缺點: 複雜性增加、數據一致性難以維護、數據遷移成本高

-- Shard 1 
CREATE TABLE shard1.customers (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50), email VARCHAR(50));

-- Shard 2
CREATE TABLE shard2.customers (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50), email VARCHAR(50));

INSERT INTO shard1.customers SELECT * FROM customers WHERE date_column < '2023-01-01';

INSERT INTO shard2.customers SELECT * FROM customers WHERE date_column >= '2023-01-01';

練習
假設要把 DATABASE travel TABLE food 的資料表分割到兩個資料庫

CREATE DATABASE travel_1;
create database travel_2;

CREATE TABLE travel_1.food_1
SELECT * FROM travel.food WHERE id >= 1 AND id <= 1000;

use travel_1;
select * from food_1;

CREATE TABLE travel_2.food_2
SELECT * FROM travel.food WHERE id >1000;

use travel_2;
select * from food_2;

獨佔鎖 Exclusive Lock、共享鎖　Shared Lock

START TRANSACTION;

-- 獨佔鎖，其他事務無法訪問或修改該數據，直到持有鎖的事務釋放鎖
SELECT * FROM new_food WHERE id = 1 FOR UPDATE;

-- 共享鎖，其他事務也可以訪問這個數據，但是它們不能對數據進行修改
SELECT * FROM new_food WHERE id = 2 LOCK IN SHARE MODE;

COMMIT;

PS 死鎖 Deadlock : 指兩個或多個並發進程在執行過程中，競爭資源或由於彼此通信而造成的僵局，都無法推進下去

# Session 1
BEGIN;
SELECT * FROM table1 WHERE key1 = 1 FOR UPDATE;

# Session 2
BEGIN;
SELECT * FROM table2 WHERE key2 = 2 FOR UPDATE;

# Session 1 is blocked waiting for the lock held by session 2 here
SELECT * FROM table2 WHERE key2 = 2 FOR UPDATE;

解除

SHOW ENGINE INNODB STATUS;
KILL session_id;

同步複製 Synchronous Replication、非同步複製Asynchronous Replication

同步複製 Synchronous Replication

# 在主數據庫上創建一個新用戶
CREATE USER 'replication_user'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'%';

# 配置主數據庫my.cnf文件
[mysqld]
log-bin=mysql-bin
server-id=1

# 配置從數據庫my.cnf文件
[mysqld]
server-id=2

# 開始複製
CHANGE MASTER TO MASTER_HOST='master_host_name',
MASTER_USER='replication_user',
MASTER_PASSWORD='password',
MASTER_LOG_FILE='recorded_log_file_name',
MASTER_LOG_POS=recorded_log_position;
START SLAVE;

非同步複製Asynchronous Replication

# 在主數據庫上創建一個新用戶
CREATE USER 'replication_user'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'%';

# 配置主數據庫my.cnf文件
[mysqld]
log-bin=mysql-bin
server-id=1

＃配置從數據庫my.cnf文件
[mysqld]
server-id=2

#　開始複製
CHANGE MASTER TO MASTER_HOST='master_host_name',
MASTER_USER='replication_user',
MASTER_PASSWORD='password',
MASTER_LOG_FILE='recorded_log_file_name',
MASTER_LOG_POS=recorded_log_position;
START SLAVE;

InnoDB、MYISAM

InnoDB

B+tree 引指向主鍵，主鍵指向行
支持 ACID 符合的事務
使用行級鎖定 (允許多個事務同時對表中的不同行進行讀取和修改操作，同時保持數據的一致性和完整性)
支持外鍵
屬於 Oracle

MYISAM

B-tree 索引直接指向行
不支持ACID
使用表級鎖定 (當一個用戶對表進行讀或寫操作時，其他用戶無法對該表進行任何操作)
插入快速，但更新和刪除可能會產生問題
數據庫崩潰會損壞表，需要手動修復
曾經是 MySQL 的默認引擎
大多數情況下，MySQL 默認使用InnoDB

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
) ENGINE=MYISAM; 
# 可以改成 ENGINE=InnoDB;

PS>根據InnoDB存儲引擎的默認行為，PRIMARY KEY 和所有的索引數據都是一起存儲，有利於提高數據的查詢速度

Nosql (MongoDB)

文檔導向： MongoDB 是一種文檔導向的數據庫，以 JSON 格式（BSON）存儲數據

動態模式 : MongoDB 使用動態模式，這意味著同一集合中的文檔可以具有不同的字段

、高性能 : MongoDB 具有良好的擴展性，可以輕鬆地橫向擴展到多台服務器

擴展性 : MongoDB 提供高性能的查詢和索引功能，並支持複雜的查詢操作

高可用性 : MongoDB 支持數據的冗餘備份和自動失敗恢復

豐富的查詢語言 : MongoDB 支持查詢嵌套文檔和動態查詢

var field = "size";
var query = {};
query[field] = { $gte: 15 };
db.inventory.find(query);
  • 地理空間索引 : MongoDB 支持地理空間索引
db.places.createIndex( { location : "2dsphere" } )

db.places.find(
   {
     location:
       { $near :
          {
            $geometry: { type: "Point",  coordinates: [ -73.9667, 40.78 ] },
            $maxDistance: 1000
          }
       }
   }
)

Catalina
Catalina

Hi, I’m Catalina！
原本在西語市場做開發業務，2023 年正式轉職資料領域。
目前努力補齊計算機組織、微積分、線性代數與機率論，忙碌中做點筆記提醒自己 🤲

文章: 43

相關文章

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *

目前趨勢

•【Kaggle 資料分析】eCommerce Events History in Cosmetics Shop Dataset 分析使用者行為與購買機率：用 Python、Xgboost 打造機器學習專案
• Colab 讀取資料 : Github、Google Drive、本地端
•【Hung-yi Lee 機器學習 – L1 : COVID-19 Cases Prediction (Regression)】
• Linux 一條龍架站 ft.常見指令