面試官：什么情況下選擇分庫？什么情況下選擇分表？

分庫和分表是數據庫層面的兩種常見解決方案，它們分別解決不同的問題。

1. 分庫解決的問題

分庫主要是為了解決高并發的問題。在數據庫系統中，當對數據庫的訪問量（QPS，Queries Per Second）非常高時，會對數據庫實例造成很大的壓力，降低數據查詢效率，甚至導致連接失敗。這是因為每個數據庫實例都有連接數的限制，當訪問連接數過多時，就會超出這個限制。

此時，將一個數據庫實例的操作拆分成對多個數據庫實例的操作，即進行分庫，可以有效地降低單個數據庫實例的壓力，提高系統的并發處理能力。此外，分庫還可以實現業務數據的隔離，便于管理和維護。

2. 分表解決的問題

（水平）分表主要是為了解決數據量大的問題。當一張表中的數據量過大時，對該表的讀寫操作都會變得效率低下。這是因為數據庫需要遍歷的記錄過多導致可能得IO次數增多，如果表中的記錄數目過多，這個過程將會非常耗時。

通過分表，可以將數據分散到多個表中，每個表的數據量都相對較小，這樣在執行查詢操作時，數據庫只需要在相對較小的表中查找數據，從而大大提高查詢效率。此外，分表還可以避免單表數據過大引發的一系列問題，如備份和恢復的速度慢等。

現實情況中能不水平分表就盡量不要水平分表，而是在經過增加索引，讀寫分離后仍無法解決查詢慢的情況下才進行分表。原因主要基于以下幾點考慮：

• 復雜性增加：水平分表將原始表的數據按某種策略（如哈希、范圍等）拆分成多個子表，每個子表包含原始表的一部分數據。這增加了系統的復雜性，因為需要管理多個表，并且在查詢、更新和刪除數據時需要考慮數據分布和路由。
• 數據一致性難以保證：在水平分表的情況下，某些操作（如跨分表事務）可能難以保證數據的一致性。分布式事務問題是一個挑戰，因為當使用分片技術進行水平分表時，可能會涉及到多個數據庫節點的事務處理。這增加了系統的復雜性和開發成本，同時也可能影響數據的可靠性和完整性。
• 維護和遷移成本：水平分表后，數據的維護和遷移成本也會增加。例如，當需要添加新的字段或修改現有字段時，需要在所有相關的子表上執行相應的操作。此外，如果需要進行數據遷移或備份，也需要考慮如何高效地處理多個分片的數據。

面試官：水平分表該如何選擇sharding key，應該遵循哪些原則？

Sharding Key（分片鍵）是用于將數據進行分片的屬性或字段。選擇Sharding Key 最重要的原則是：我們的業務最頻繁的使用哪個字段訪問數據的。

以電商系統的訂單表分片為例。假設把 OrderlD 作為 Sharding Key行不行?

在電商系統中，買家和商家都有查看訂單列表的需求，但最能為電商帶來利益的是買家用戶，而買家用戶對于訂單最高頻的訪問是 app 中"我的訂單"頁面，此時的查詢條件是用戶ID。但是如果我們的分片依據是 OrderID，強行查詢的只能查詢所有的分片，并合并查詢結果，效率很低，且沒法分頁。

而如果把 UserID 作為Sharding Key，此時用戶在app中"我的訂單"頁面正好可以使用到分片鍵，一個用戶對應的訂單信息都在一個分片中，因為分片是使用的UserID，此時效率最高。直接去對應的分片去查詢就可以了。

問題來了，有使用OrderlD進行查詢的場景怎么辦，怎么通過OrderID找到對應的數據分片?

管理員或者商家可能不使用UserID而只使用OrderID作為查詢條件查看單條訂單。為了保證用戶在主場景下的查詢效率，還是以UserID作為分片鍵。但是在生成訂單ID的時候，可以將用戶ID的后幾位作為訂單ID的一部分。比如18位的訂單號，它的第15-18位是用戶ID的后四位。此時按照訂單ID查詢的時候，可以根據訂單ID中的用戶ID找到分片。

假設我們已經確定了把 UserID 作為Sharding Key ，那么商家想查詢自己的訂單列表怎么辦?

一般的方案是把訂單數據同步到其他的存儲中間件中，用其他存儲中間件解決復雜查詢的問題。例如可以構建一套以商家ID作為Sharding Key的只讀訂單庫，專門供商家使用。或者可以將數據同步到HDFS中，用一些大數據技術生成對應的訂單報表。

一個選擇分片鍵的一些通用依據如下所示：

(1) 數據分布均勻性：

• 分片鍵的數據基數要足夠大，也就是分片鍵的value盡可能不同，以確保數據能夠均勻分布到各個分片節點上，避免數據傾斜。
• 增長趨勢可預測，便于進行容量規劃和分片管理。
• 避免選擇可能導致熱點數據的字段，如使用時間戳作為分片鍵時，需要注意追加寫入可能導致特定分片成為熱點。

(2) 查詢模式適配性：

• 與業務最頻繁的查詢模式匹配，以便在查詢時能夠高效地定位到數據所在的分片節點。
• 支持就近路由，即查詢時能夠直接定位到包含所需數據的分片節點，提升查詢效率。

(3) 字段的更新頻率：

    選擇低更新頻率的字段作為Sharding Key，以減少數據遷移和重新分片的頻率。

面試官：Mysql的水平分表和分區有什么區別，什么時候用水平分表什么時候用分區?介紹一下Mysql中常見的分區算法？

1. 水平分表（Sharding）

原理：水平分表是將一個大型表的數據按某種規則拆分到多個獨立的表中。這些表通常具有相同的結構，但存儲不同的數據。

應用場景：水平分表適用于數據量特別大、需要分布式存儲和高并發訪問的場景，如大型電商平臺、社交網絡等。同時分表可以分散到不同的數據庫實例，當單個數據庫實例無法承載所有數據或處理所有請求時，水平分表成為了一種有效的解決方案。

優點：

• 可以突破單節點數據庫服務器的I/O能力限制，提高系統的可擴展性。
• 可以將數據分散到多個存儲單元中，以減輕單表的數據量和訪問壓力，從而提高數據庫的性能。

缺點：

• 實現和維護相對復雜，需要手動管理各個分表，包括表的創建、數據遷移和備份恢復等操作。
• 跨表查詢需要應用程序處理或使用中間件支持，增加了開發難度和成本。

2. 分區（Partitioning）

原理：分區是將一個表的數據按某種規則劃從邏輯上分成多個分區，每個分區存儲一部分數據。但這些分區仍然屬于同一個表和同一個數據庫實例。

應用場景：分區適用于中等規模的數據優化，通過分區，可以優化查詢性能和管理效率。

優點：

• 數據庫系統自動管理分區，支持自動分區裁剪和優化，提高了查詢性能。
• 管理和維護相對簡單，減少了開發和運維成本。

缺點：擴展性相對較弱，受限于單個數據庫實例的資源。

當需要增加新的分區或調整分區范圍時，可能需要重新定義分區規則并遷移數據，增加了系統停機時間和數據不一致性風險。

3. 分區和分表對比

以下是MySQL中常見的分區算法及其使用場景：

(1) Range分區（范圍分區）

定義：基于屬于一個給定連續區間的列值，把多行分配給分區。

使用場景：適用于那些可以基于某個范圍進行劃分的數據。例如，可以按年份、月份或日期范圍對表進行分區，以便查詢特定時間段內的數據時，能夠只掃描包含所需數據的分區，從而提高查詢效率。

示例：

CREATE TABLE orders (
    id INT NOT NULL AUTO_INCREMENT,
    order_date DATE,
    customer_id INT,
    amount DECIMAL(10,2),
    PRIMARY KEY (id, order_date)
)
PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p0 VALUES LESS THAN (2010),
    PARTITION p1 VALUES LESS THAN (2015),
    PARTITION p2 VALUES LESS THAN (2020),
    PARTITION p3 VALUES LESS THAN (2025)
);

(2) List分區（列表分區）

定義：基于預定義的值列表進行劃分。

使用場景：適用于那些值具有離散特性的列。例如，當某個字段的值只有有限的幾個選項時，可以使用List分區。

示例：

CREATE TABLE employees (
    id INT NOT NULL,
    department_id INT NOT NULL,
    name VARCHAR(50)
)
PARTITION BY LIST (department_id) (
    PARTITION p_hr VALUES IN (1, 2),
    PARTITION p_finance VALUES IN (3, 4),
    PARTITION p_it VALUES IN (5, 6)
);

(3) Hash分區（哈希分區）

定義：使用哈希函數對列值進行計算，然后根據結果分配到不同的分區。

使用場景：適用于數據分布較為均勻的場景。通過哈希分區，可以將數據均勻地分布到不同的分區中，從而提高查詢性能。

示例：

CREATE TABLE users (
    id INT NOT NULL,
    email VARCHAR(100) NOT NULL,
    signup_date DATE NOT NULL
)
PARTITION BY HASH(id)
PARTITIONS 4;

4. 選擇分區算法的建議

• Range分區：當需要基于某個范圍進行劃分時，如按年份、月份等，可以選擇Range分區。
• List分區：當某個字段的值只有有限的幾個選項時，可以選擇List分區。
• Hash分區：當數據分布較為均勻，且希望將數據均勻地分布到不同的分區中時，可以選擇Hash分區。

面試官：能不能說說看什么是垂直分表以及垂直分表的場景？從Innodb底層存儲說明為什么需要做垂直分表？

1. 定義

垂直分表是將一個寬表（即包含多個字段的表）按照字段進行拆分，形成多個子表，每個子表僅包含原表中的部分字段。這些子表通過主鍵或唯一索引進行關聯，以保持數據的完整性。

2. 原理及作用

優化查詢性能：通過拆分表中的字段，可以減少單個表的寬度，從而提高查詢效率。尤其是當查詢只涉及部分字段時，可以避免加載不必要的字段，減少I/O開銷。

分離冷熱數據：將使用頻率較高的熱數據與使用頻率較低的冷數據分離到不同的表中，可以優化數據庫性能，減少對冷數據的訪問開銷。

提高緩存命中率：表寬度減小后，緩存的效果會更好，因為緩存通常只能存儲有限數量的數據行或數據頁。當表寬度較小時，更多的數據行或數據頁可以被緩存，從而提高緩存命中率。

3. 適用場景

垂直分表通常適用于以下幾種場景：

• 寬表拆分：當一個表中包含了大量的字段，且部分字段的使用頻率較低時，可以通過垂直分表將常用字段和不常用字段分開。
• 冷熱數據分離：在大數據應用中，部分數據的訪問頻率很高（熱數據），而部分數據的訪問頻率很低（冷數據）。通過垂直分表可以將冷熱數據分開，以優化數據庫性能。
• 功能模塊獨立：如果某些功能模塊只需要訪問表中的部分字段，那么可以將這些字段獨立出來，以減少查詢時的I/O負擔。
• 大字段處理：表中包含大字段（如BLOB、TEXT等）時，這些字段會占用大量存儲空間和I/O資源。通過垂直分表，可以將這些大字段獨立存儲，以減少對其他查詢的影響。

從InnoDB存儲結構的角度來看，實現垂直分表的原因主要基于以下幾點：

4. InnoDB存儲結構概述

InnoDB是MySQL的默認存儲引擎，它管理數據的方式是通過行（Row）組成頁（Page），頁再組成區（Extent），區再組成段（Segment），最后段組成表空間（Tablespace）。其中，頁是InnoDB存儲的基本單位，其大小通常為16KB。

5. 垂直分表與InnoDB存儲結構的關聯

減少跨頁檢索：

• 在InnoDB存儲結構中，數據是按頁存儲的。如果表中的字段過多，每行數據占用的空間就會增大，這可能導致一行數據跨越多個頁存儲。當進行查詢時，如果需要跨頁檢索數據，就會增加I/O操作的次數，降低查詢效率。
• 通過垂直分表，將不常用或大字段拆分到單獨的表中，可以減少主表的寬度，使每行數據占用的空間減小，從而減少跨頁檢索的可能性，提高查詢效率。

優化頁內數據行存儲：

• InnoDB存儲引擎在壓縮和解壓縮時會花費額外的時間。如果表中的數據行跨頁存儲，那么在解壓縮時需要處理更多的數據，這會增加I/O負擔。
• 通過垂直分表，可以優化頁內數據行的存儲，使更多的數據行能夠在同一個頁內存儲，從而減少跨頁檢索和磁盤掃描的范圍，達到提高查詢效率的目的。

提升緩存命中率：

• 數據庫緩存通常只能存儲有限的數據量。當表寬度較大時，緩存中可能只能存儲少量的數據行，這降低了緩存的命中率。
• 通過垂直分表，將表拆分成更小的表，可以使更多的數據行能夠被緩存，從而提高緩存命中率，減少磁盤I/O操作，提高數據庫性能。