關(guān)鍵詞：分庫(kù)分表、高性能、MySQL數(shù)據(jù)庫(kù)

文章導(dǎo)讀

背景介紹

  隨著互聯(lián)網(wǎng)技術(shù)的發(fā)展，數(shù)據(jù)量呈爆炸性增長(zhǎng)。大數(shù)據(jù)量的業(yè)務(wù)場(chǎng)景中，數(shù)據(jù)庫(kù)成為系統(tǒng)性能瓶頸的一個(gè)主要因素。當(dāng)單個(gè)數(shù)據(jù)庫(kù)包含了太多數(shù)據(jù)或過(guò)高的訪問(wèn)量時(shí)，會(huì)出現(xiàn)查詢緩慢、響應(yīng)時(shí)間長(zhǎng)等問(wèn)題，嚴(yán)重影響用戶體驗(yàn)。為了解決這一問(wèn)題，分庫(kù)分表技術(shù)應(yīng)運(yùn)而生。通過(guò)將數(shù)據(jù)分散到多個(gè)數(shù)據(jù)庫(kù)或表中，從而有效提升系統(tǒng)的處理能力和穩(wěn)定性。

場(chǎng)景分析

例如：在交易系統(tǒng)核心數(shù)據(jù)庫(kù)設(shè)計(jì)大致包括：

產(chǎn)品數(shù)據(jù)庫(kù)（Product/Asset Database）：存儲(chǔ)系統(tǒng)可交易的產(chǎn)品或資產(chǎn)的詳細(xì)信息，比如在股票交易系統(tǒng)中，這里會(huì)包含股票代碼、股票名稱、當(dāng)前價(jià)格等信息。

訂單數(shù)據(jù)庫(kù)（Order Database）：存儲(chǔ)用戶提交的訂單信息，包括訂單ID、訂單狀態(tài)（如待處理、完成、取消）、訂單創(chuàng)建時(shí)間等。

用戶數(shù)據(jù)庫(kù)（User Database）：存儲(chǔ)用戶的基本信息，如用戶ID、用戶名、密碼（通常進(jìn)行加密存儲(chǔ)）、聯(lián)系信息等，以及用戶的權(quán)限和角色定義。

交易數(shù)據(jù)庫(kù)（Transaction Database）：記錄所有交易的詳細(xì)信息，如交易ID、交易類型（買入、賣出等）、交易金額、交易時(shí)間、交易雙方等。這個(gè)數(shù)據(jù)庫(kù)是交易系統(tǒng)的核心，需要高效且可靠。

配置數(shù)據(jù)庫(kù)（Configuration Database）：存儲(chǔ)系統(tǒng)配置信息，如交易規(guī)則、費(fèi)用設(shè)置、系統(tǒng)參數(shù)等。

歷史數(shù)據(jù)庫(kù)（Historical Data Database）：保存交易、訂單和價(jià)格的歷史記錄。這對(duì)于數(shù)據(jù)分析、報(bào)告生成及監(jiān)控非常重要。

賬戶數(shù)據(jù)庫(kù)（Account Database）：存儲(chǔ)用戶的賬戶信息，包括賬戶余額、賬戶類型、賬戶狀態(tài)等。在交易系統(tǒng)中，賬戶信息是核心數(shù)據(jù)之一。

安全和審計(jì)數(shù)據(jù)庫(kù)（Security and Audit Database）：用于記錄安全相關(guān)的事件，如登錄嘗試、權(quán)限變更等，以及審計(jì)記錄，確保系統(tǒng)的安全性和可追蹤性。

......

從上邊的分析看，對(duì)應(yīng)數(shù)據(jù)庫(kù)表大致歸納為以下幾種類型：

1. 配置表：產(chǎn)品規(guī)格、數(shù)據(jù)字典、系統(tǒng)參數(shù)、費(fèi)用項(xiàng)等
2. 流水表：訂單數(shù)據(jù)、交易流水等
3. 日志表：應(yīng)用日志、用戶操作日志、異常日志、訪問(wèn)日志等
4. 用戶表：用戶注冊(cè)、用戶登錄等

......

思考

  一般哪些表可能存在數(shù)據(jù)激增、性能問(wèn)題？日志表、流水表、用戶表等都可能。而系統(tǒng)配置則可能相對(duì)較少。

分庫(kù)分表

什么是分庫(kù)分表？

  分庫(kù)分表是一種數(shù)據(jù)庫(kù)架構(gòu)優(yōu)化技術(shù)，說(shuō)白了就是一種分治思想。通過(guò)分庫(kù)分表將數(shù)據(jù)分散到多個(gè)數(shù)據(jù)庫(kù)或表中，來(lái)提高系統(tǒng)的性能和穩(wěn)定性。分庫(kù)分表可以分為以下幾種策略：水平分庫(kù)、水平分表、垂直分庫(kù)、垂直分表。

以訂單庫(kù) db_order 和 訂單表 tb_order 為例（db為庫(kù)，tb為表）：

水平分庫(kù)：根據(jù)某些規(guī)則（例如訂單ID的范圍）將db_order數(shù)據(jù)庫(kù)分成多個(gè)數(shù)據(jù)庫(kù)（分片），如db_order_1, db_order_2, db_order_3等。每個(gè)數(shù)據(jù)庫(kù)的表結(jié)構(gòu)相同，但存儲(chǔ)的訂單數(shù)據(jù)不同。

水平分表：根據(jù)訂單的創(chuàng)建時(shí)間，將tb_order分成tb_order_2022, tb_order_2023, tb_order_2024等多個(gè)表，每個(gè)表存儲(chǔ)各自時(shí)間段的訂單數(shù)據(jù)。表結(jié)構(gòu)保持一致，但每個(gè)表只存儲(chǔ)一部分?jǐn)?shù)據(jù)。

垂直分庫(kù)：根據(jù)業(yè)務(wù)功能將數(shù)據(jù)垂直分割到不同的數(shù)據(jù)庫(kù)中。例如，將訂單相關(guān)的表保留在db_order中，將用戶相關(guān)的表遷移到新的數(shù)據(jù)庫(kù)db_user中，商品相關(guān)的表遷移到db_product中。

垂直分表：若tb_order表中的字段非常多，包含了訂單的基本信息、訂單屬性信息、訂單資費(fèi)信息等多個(gè)方面。此時(shí)，可以將tb_order表垂直拆分為多個(gè)表，如tb_order_base存儲(chǔ)訂單的基本信息，tb_order_chars存儲(chǔ)訂單屬性信息、tb_order_charges存儲(chǔ)訂單資費(fèi)信息。

小結(jié)

  有了以上這些了解，基本對(duì)分庫(kù)分表概念有了大致了解。對(duì)于分庫(kù)一般按照業(yè)務(wù)功能領(lǐng)域劃分，這里我們主要重點(diǎn)介紹分表。

分庫(kù)分表常見問(wèn)題

什么情況下需要分表？

參考規(guī)則

根據(jù)《阿里巴巴Java開發(fā)手冊(cè)》，給出如下建議：

工程經(jīng)驗(yàn)

  事實(shí)上，通常在實(shí)戰(zhàn)中，一般按經(jīng)驗(yàn)數(shù)據(jù)達(dá)到千萬(wàn)級(jí)，就需要分庫(kù)分表。原因如下：

我們知道：InnoDB管理磁盤的最小單元：頁(yè)，頁(yè)大小16KB.

mysql> show global status like '%Innodb_page_size%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| Innodb_page_size | 16384 |
+------------------+-------+
1 row in set (0.00 sec)

  在日常開發(fā)中，對(duì)于數(shù)據(jù)庫(kù)性能優(yōu)化，我們首先想到的是索引優(yōu)化。索引的底層數(shù)據(jù)結(jié)構(gòu)是B+樹。其組織結(jié)構(gòu)如圖所示：

圖片來(lái)源于網(wǎng)絡(luò)，侵權(quán)刪

樹高為3的B+樹數(shù)據(jù)存儲(chǔ)計(jì)算規(guī)則：

根節(jié)點(diǎn)計(jì)算：

假設(shè)數(shù)據(jù)類型是bigint，大小為8b。數(shù)據(jù)本身也需要一小塊空間，用來(lái)存儲(chǔ)下一層索引數(shù)據(jù)頁(yè)的地址，大小為6b， 那么根節(jié)點(diǎn)是可以存儲(chǔ)16*1024/(8+6) = 1170 個(gè)數(shù)據(jù)。

其它層節(jié)點(diǎn)計(jì)算：

第二層：因?yàn)槊總€(gè)節(jié)點(diǎn)數(shù)據(jù)結(jié)構(gòu)和跟節(jié)點(diǎn)一樣，而且在跟節(jié)點(diǎn)每個(gè)元素都會(huì)延伸出來(lái)一個(gè)節(jié)點(diǎn)，所以第二層的數(shù)據(jù)量是1170*1170=1368900

第三層：因?yàn)閕nnodb的葉子節(jié)點(diǎn)，是直接包含整條mysql數(shù)據(jù)的，假設(shè)每條數(shù)據(jù)以1kb計(jì)算，那么第三層每個(gè)節(jié)點(diǎn)為16kb，那么每個(gè)節(jié)點(diǎn)是可以放16個(gè)數(shù)據(jù)的，所以最終mysql可以存儲(chǔ)的總數(shù)據(jù)為

1170 * 1170 * 16 = 21902400 (千萬(wàn)級(jí))

其實(shí)計(jì)算結(jié)果與我們平時(shí)的工作經(jīng)驗(yàn)也是相符的，一般mysql一張表的數(shù)據(jù)超過(guò)了千萬(wàn)也是得進(jìn)行分表操作了。

參考文章：

MySQL一張表到底能存多少數(shù)據(jù)？^[1]

如何選擇分片鍵？

  例如，本節(jié)我們以訂單表的分表為例，一般訂單表中含有訂單編號(hào)：order_id, 用戶編號(hào)：user_id, 訂單創(chuàng)建時(shí)間：order_date等。

對(duì)于訂單表，通常我們可以考慮以下分片鍵選項(xiàng)：

訂單編號(hào)

優(yōu)點(diǎn)：訂單編號(hào)通常是唯一的，可以確保每個(gè)訂單都分散到不同的分片上。這對(duì)于保證數(shù)據(jù)均勻分布和避免熱點(diǎn)數(shù)據(jù)非常有幫助。

用戶編號(hào)

優(yōu)點(diǎn)：用戶編號(hào)通常也是唯一的，并且如果用戶的訂單量分布均勻，那么使用用戶編號(hào)作為分片鍵可以確保每個(gè)用戶的訂單都在同一個(gè)分片上，這對(duì)于查詢某個(gè)用戶的所有訂單非常高效。

缺點(diǎn)：如果用戶的訂單量差異很大，那么某些分片可能會(huì)存儲(chǔ)大量的訂單數(shù)據(jù)，而其他分片可能只有少量的數(shù)據(jù)。這會(huì)導(dǎo)致數(shù)據(jù)分布不均勻，進(jìn)而影響查詢性能。

訂單創(chuàng)建時(shí)間

優(yōu)點(diǎn)：適用于：按時(shí)間范圍查詢訂單的場(chǎng)景。

缺點(diǎn)：可能出現(xiàn)熱點(diǎn)數(shù)據(jù)傾斜問(wèn)題（即在某個(gè)時(shí)段產(chǎn)生訂單峰值）

如何選擇數(shù)據(jù)庫(kù)主鍵策略？

在選擇主鍵策略時(shí)，需要注意以下幾點(diǎn)：

• 唯一性：確保主鍵在全局唯一，避免數(shù)據(jù)沖突。
• 性能：選擇適合的主鍵類型和生成策略，以提高數(shù)據(jù)插入、查詢和索引的性能。
• 擴(kuò)展性：能夠適應(yīng)數(shù)據(jù)量和并發(fā)量增長(zhǎng)。
• 兼容性：選擇的主鍵策略要與使用的數(shù)據(jù)庫(kù)兼容。

  在 MySQL 中進(jìn)行分庫(kù)分表時(shí)，自增主鍵策略確實(shí)需要特別處理，因?yàn)閭鹘y(tǒng)的自增主鍵策略在分布式環(huán)境下會(huì)導(dǎo)致主鍵沖突。每個(gè)數(shù)據(jù)庫(kù)實(shí)例或分片都會(huì)從相同的起始點(diǎn)開始自增，這會(huì)導(dǎo)致在不同的分片上生成相同的 ID，進(jìn)而引發(fā)數(shù)據(jù)沖突。

幾種常見的主鍵策略方案：

1. UUID：

UUID 是一個(gè) 128 位的值，具有全局唯一性，可以很好地解決分布式環(huán)境下的主鍵沖突問(wèn)題。但是，UUID 字符串較長(zhǎng)，存儲(chǔ)和索引效率較低，而且是無(wú)序的，可能會(huì)影響查詢性能。

2. Snowflake 算法： 雪花算法（SnowFlake）是一種分布式ID生成算法，由Twitter開源。其核心思想是使用64位long型數(shù)字作為全局唯一的ID，通過(guò)時(shí)間戳、工作機(jī)器ID和序列號(hào)等部分來(lái)確保ID的全局唯一性。

1. 結(jié)構(gòu)說(shuō)明：

• 1位：未使用（因?yàn)槎M(jìn)制中最高位是符號(hào)位，正數(shù)是0，負(fù)數(shù)是1，一般生成的ID都是正數(shù)，所以這一位固定為0）。
• 41位：時(shí)間戳（毫秒級(jí)），用來(lái)記錄時(shí)間截的差值（當(dāng)前時(shí)間截 - 開始時(shí)間截）。
• 10位：工作機(jī)器ID，包括5位datacenterId（數(shù)據(jù)中心ID）和5位workerId（工作機(jī)器ID），用來(lái)表示工作機(jī)器的ID。
• 12位：序列號(hào)，用來(lái)記錄同一毫秒內(nèi)產(chǎn)生的不同ID，12位可以表示的最大整數(shù)為4095，用來(lái)表示同一機(jī)器同一時(shí)間截（毫秒）內(nèi)產(chǎn)生的4095個(gè)ID序號(hào)。

  通過(guò)這種結(jié)構(gòu)，雪花算法可以保證生成的ID按時(shí)間遞增，并且整個(gè)分布式系統(tǒng)中不會(huì)有重復(fù)的ID。

3. 分布式自增 ID 生成器： 使用像 Twitter 的 Snowflake、阿里巴巴的 Druid 等分布式 ID 生成器來(lái)生成全局唯一的自增 ID。這些生成器通過(guò)特定的算法和機(jī)制保證在不同實(shí)例間生成的 ID 是全局唯一的。

4. 自增主鍵 + 分片策略： 仍然使用自增主鍵，但是結(jié)合分片策略，確保每個(gè)分片上的主鍵值是唯一的。例如，可以預(yù)先為每個(gè)分片分配一個(gè) ID 范圍，確保在這個(gè)范圍內(nèi)的 ID 是唯一的。這種方法需要維護(hù)分片的 ID 范圍，并在必要時(shí)進(jìn)行調(diào)整。

5. 使用數(shù)據(jù)庫(kù)的自增 ID 特性： 某些數(shù)據(jù)庫(kù)支持在分表時(shí)自動(dòng)處理自增 ID，避免沖突。例如，MySQL 8.0 引入了 AUTO_INCREMENT_INCREMENT 和 AUTO_INCREMENT_OFFSET 這兩個(gè)系統(tǒng)變量，用于在復(fù)制或分片環(huán)境中調(diào)整自增 ID 的步長(zhǎng)和起始值，從而避免沖突。

總之，在分庫(kù)分表時(shí)，自增主鍵策略需要進(jìn)行特殊處理，以確保全局唯一性，并根據(jù)實(shí)際情況選擇合適的方案。

如何選擇分表策略？

選擇分庫(kù)分表的策略時(shí)，確實(shí)需要根據(jù)具體的業(yè)務(wù)場(chǎng)景和數(shù)據(jù)特性來(lái)決定。例如訂單表，以訂單ID (order_id) 作為分表鍵。

基于范圍的策略

適用場(chǎng)景：當(dāng)訂單ID有明確的增長(zhǎng)趨勢(shì)，例如連續(xù)的自增ID，并且你知道未來(lái)可能的訂單數(shù)量時(shí)，范圍分表是一個(gè)好選擇。

策略實(shí)現(xiàn)：可以將訂單ID按照范圍劃分到不同的表中。例如，訂單ID【1-1000萬(wàn)】 在表tb_order_01，【1000萬(wàn)-2000萬(wàn)】在表tb_order_02，以此類推。

優(yōu)點(diǎn)：

• 查詢效率較高，尤其是范圍查詢。
• 數(shù)據(jù)遷移和維護(hù)相對(duì)簡(jiǎn)單。

缺點(diǎn)：

• 訂單ID的分布必須均勻.
• 如果訂單ID不是連續(xù)或可預(yù)測(cè)的，這種策略可能不適用。

基于哈希的策略

適用場(chǎng)景：當(dāng)訂單ID沒(méi)有明確的增長(zhǎng)趨勢(shì)，哈希分表是一個(gè)好選擇。

策略實(shí)現(xiàn)：使用哈希函數(shù)對(duì)訂單ID進(jìn)行哈希運(yùn)算，然后根據(jù)哈希值的結(jié)果決定存儲(chǔ)在哪個(gè)表中。

table_index = hash(order_id) % tables_num

優(yōu)點(diǎn)：

• 負(fù)載均衡，每個(gè)表的數(shù)據(jù)分布相對(duì)均勻。

缺點(diǎn)：

• 不利于二次擴(kuò)容。

映射表策略

適用場(chǎng)景：當(dāng)訂單ID的分布不均，或者需要靈活控制數(shù)據(jù)分布時(shí)，映射表分表可能是一個(gè)好選擇。

策略實(shí)現(xiàn)：使用一個(gè)映射表來(lái)記錄每個(gè)訂單ID應(yīng)該存儲(chǔ)在哪個(gè)表中。這個(gè)映射表可以是內(nèi)存中的數(shù)據(jù)結(jié)構(gòu)，也可以是數(shù)據(jù)庫(kù)中的一個(gè)表。

優(yōu)點(diǎn)：

• 靈活，可以根據(jù)需要調(diào)整數(shù)據(jù)分布。

缺點(diǎn)：

• 查詢時(shí)需要先查詢映射表，可能影響性能。

一致性哈希策略

適用場(chǎng)景：當(dāng)系統(tǒng)需要高可用性，并且希望在添加或刪除節(jié)點(diǎn)時(shí)盡量減少數(shù)據(jù)遷移時(shí)，一致性哈希可能是一個(gè)好選擇。

策略實(shí)現(xiàn)：使用一致性哈希算法將訂單ID映射到哈希環(huán)上，然后根據(jù)哈希環(huán)上的節(jié)點(diǎn)（或表）來(lái)存儲(chǔ)數(shù)據(jù)。

一致性哈希算法的核心思想是將哈希值空間表示為一個(gè)閉合的圓環(huán)（哈希環(huán)），每個(gè)節(jié)點(diǎn)負(fù)責(zé)維護(hù)圓環(huán)上一段連續(xù)的哈希值范圍。

  在分庫(kù)分表的場(chǎng)景中，可以將每個(gè)數(shù)據(jù)庫(kù)或表看作是一個(gè)節(jié)點(diǎn)，將這些節(jié)點(diǎn)均勻地分布在哈希環(huán)上。當(dāng)插入或查詢數(shù)據(jù)時(shí)，根據(jù)數(shù)據(jù)的哈希值將其映射到哈希環(huán)上，然后順時(shí)針查找最近的節(jié)點(diǎn)（即負(fù)責(zé)該哈希值范圍的數(shù)據(jù)庫(kù)或表），將數(shù)據(jù)插入或查詢?cè)摴?jié)點(diǎn)。

優(yōu)點(diǎn)：

• 支持節(jié)點(diǎn)的動(dòng)態(tài)擴(kuò)容。

缺點(diǎn)：

• 當(dāng)節(jié)點(diǎn)數(shù)量變化較大時(shí)，可能需要重新計(jì)算所有數(shù)據(jù)的哈希值并進(jìn)行遷移，增加系統(tǒng)的開銷
• 范圍查詢和順序查詢可能不如范圍分表和哈希分表高效。

非分片鍵字段如何查詢？

  曾幾何時(shí)，面試過(guò)程中遇到過(guò)這樣一個(gè)問(wèn)題：假設(shè)有一個(gè)用戶表，你用ID做的分片鍵，那么有一個(gè)類似于name這樣的字段如何查詢？

這里提供幾種常見的思路：

1.全局索引

  全局索引是一個(gè)跨所有分片的索引，它包含了非分片鍵字段和對(duì)應(yīng)的分片鍵信息。查詢時(shí)，先通過(guò)全局索引找到相關(guān)的分片鍵，然后在相應(yīng)的分片中查詢?cè)敿?xì)數(shù)據(jù)。

適用場(chǎng)景：適用于查詢頻率高、數(shù)據(jù)量大的非分片鍵字段。

優(yōu)點(diǎn)：查詢效率高，可以快速定位到數(shù)據(jù)所在的分片。

缺點(diǎn)：全局索引維護(hù)成本較高，需要定期更新以保持與分片數(shù)據(jù)的一致性。

2. 數(shù)據(jù)冗余

  在每個(gè)分片中存儲(chǔ)部分非分片鍵字段的數(shù)據(jù)。這樣，即使不直接查詢分片鍵，也可以在分片內(nèi)快速找到相關(guān)數(shù)據(jù)。

適用場(chǎng)景：適用于查詢性能要求極高，且可以接受一定數(shù)據(jù)冗余的場(chǎng)景。

優(yōu)點(diǎn)：查詢性能高，無(wú)需跨分片查詢。

缺點(diǎn)：數(shù)據(jù)冗余增加了存儲(chǔ)成本和維護(hù)復(fù)雜性。

3. 應(yīng)用層處理

  在應(yīng)用層實(shí)現(xiàn)復(fù)雜的查詢邏輯，將多個(gè)分片中的查詢結(jié)果匯總后進(jìn)行處理。

適用場(chǎng)景：適用于查詢頻率不高，或者可以接受一定延遲的場(chǎng)景。

優(yōu)點(diǎn)：靈活性高，可以根據(jù)業(yè)務(wù)需求定制查詢邏輯。

缺點(diǎn)：查詢性能可能受到網(wǎng)絡(luò)延遲和分片數(shù)量的影響。

4. 使用Elasticsearch（ES）

  將非分片鍵字段的數(shù)據(jù)同步到Elasticsearch中，利用Elasticsearch強(qiáng)大的搜索和查詢能力進(jìn)行查詢。

適用場(chǎng)景：適用于非結(jié)構(gòu)化數(shù)據(jù)、全文搜索、復(fù)雜查詢等場(chǎng)景。

優(yōu)點(diǎn)：支持復(fù)雜的查詢操作，如全文搜索、模糊匹配等；查詢性能高，支持分布式部署。

缺點(diǎn)：需要維護(hù)Elasticsearch集群，增加了系統(tǒng)的復(fù)雜性；數(shù)據(jù)同步可能引入一定的延遲。

5. 數(shù)據(jù)庫(kù)中間件

使用數(shù)據(jù)庫(kù)中間件（如ShardingSphere、MyCAT等）來(lái)管理分庫(kù)分表，中間件可以自動(dòng)處理非分片鍵字段的查詢，將請(qǐng)求路由到正確的分片。

適用場(chǎng)景：適用于希望減少應(yīng)用層復(fù)雜性的場(chǎng)景。

優(yōu)點(diǎn)：簡(jiǎn)化了應(yīng)用層的查詢邏輯，減少了開發(fā)和維護(hù)的工作量。

缺點(diǎn)：需要配置和維護(hù)數(shù)據(jù)庫(kù)中間件。

總結(jié)

在實(shí)際應(yīng)用中，可能需要根據(jù)實(shí)際情況結(jié)合多種策略來(lái)滿足不同的查詢需求。同時(shí)，隨著業(yè)務(wù)的發(fā)展和數(shù)據(jù)量的增長(zhǎng)，可能需要不斷調(diào)整和優(yōu)化分庫(kù)分表策略。

如何解決熱點(diǎn)數(shù)據(jù)傾斜問(wèn)題？

熱點(diǎn)數(shù)據(jù)傾斜通常發(fā)生在某些特定的數(shù)據(jù)項(xiàng)（例如，用戶激增、促銷訂單峰值等）等，導(dǎo)致這些數(shù)據(jù)的查詢和更新操作集中在些某特定的數(shù)據(jù)庫(kù)或表上，從而造成性能瓶頸。

解決方案：采用Range分庫(kù)+Hash分表

如何解決跨庫(kù)關(guān)聯(lián)查詢？

分庫(kù)分表后，數(shù)據(jù)被分散到了不同的數(shù)據(jù)庫(kù)或表中。跨庫(kù)關(guān)聯(lián)查詢成為新的問(wèn)題。為了解決這個(gè)問(wèn)題，可以采取以下幾種策略：

1. 字段冗余： 對(duì)于經(jīng)常需要進(jìn)行關(guān)聯(lián)查詢的字段，可以考慮將這些字段冗余到每個(gè)相關(guān)的表中。這樣，在進(jìn)行查詢時(shí)就不需要跨庫(kù)關(guān)聯(lián)，可以直接在單個(gè)表內(nèi)完成查詢。例如，如果經(jīng)常需要查詢合同和客戶的關(guān)聯(lián)信息，可以在合同表中冗余一些客戶的基礎(chǔ)字段，這樣查詢時(shí)就不需要跨庫(kù)關(guān)聯(lián)客戶表。

2. 數(shù)據(jù)同步： 如果某個(gè)系統(tǒng)經(jīng)常需要查詢另一個(gè)系統(tǒng)的數(shù)據(jù)，可以在當(dāng)前系統(tǒng)中創(chuàng)建一張對(duì)應(yīng)的表，并通過(guò)ETL或其他數(shù)據(jù)同步工具定時(shí)同步所需的數(shù)據(jù)。這樣，查詢時(shí)就可以直接在本地表中完成，避免了跨庫(kù)關(guān)聯(lián)查詢。

3. 全局表（廣播表）： 對(duì)于某些基礎(chǔ)數(shù)據(jù)，如行名行號(hào)信息等，如果它們被多個(gè)業(yè)務(wù)系統(tǒng)頻繁使用，可以考慮在所有的數(shù)據(jù)庫(kù)中都存儲(chǔ)這些基礎(chǔ)數(shù)據(jù)。這樣，無(wú)論哪個(gè)系統(tǒng)需要這些數(shù)據(jù)，都不需要進(jìn)行跨庫(kù)關(guān)聯(lián)查詢。

4. ER表（綁定表）： 對(duì)于存在邏輯主外鍵關(guān)系的表，如訂單表和訂單明細(xì)表，可以考慮將它們的數(shù)據(jù)物理上存儲(chǔ)在一起，形成一個(gè)綁定表。這樣，查詢時(shí)就可以在一個(gè)表中完成主表和明細(xì)表的關(guān)聯(lián)查詢，避免了跨庫(kù)關(guān)聯(lián)。

5. 使用分布式中間件： 分布式中間件如Sharding-JDBC、MyCAT等，可以將多個(gè)物理數(shù)據(jù)庫(kù)視為一個(gè)邏輯數(shù)據(jù)庫(kù)。這些中間件能夠處理復(fù)雜的聯(lián)合查詢、排序、聚合等SQL操作，并根據(jù)分片規(guī)則指導(dǎo)SQL語(yǔ)句的執(zhí)行。它們能夠解決分庫(kù)分表后的通過(guò)程序聚合匯總結(jié)果，解決跨庫(kù)關(guān)聯(lián)查詢問(wèn)題。

6. 應(yīng)用層數(shù)據(jù)聚合： 在應(yīng)用層，可以編寫邏輯來(lái)聚合來(lái)自不同數(shù)據(jù)庫(kù)或表的數(shù)據(jù)。這通常涉及發(fā)起多個(gè)數(shù)據(jù)庫(kù)查詢，然后在應(yīng)用層將結(jié)果集合并成所需的結(jié)構(gòu)。

需要注意的是，雖然上述方法可以解決跨庫(kù)關(guān)聯(lián)查詢的問(wèn)題，但它們也會(huì)帶來(lái)一些額外的復(fù)雜性。在設(shè)計(jì)分庫(kù)分表方案時(shí)，需要綜合考慮業(yè)務(wù)需求、數(shù)據(jù)量、查詢頻率等因素，選擇合適的策略來(lái)平衡性能和可維護(hù)性。同時(shí)，隨著業(yè)務(wù)的發(fā)展和數(shù)據(jù)量的增長(zhǎng)，可能需要對(duì)分庫(kù)分表方案進(jìn)行調(diào)整和優(yōu)化。

如何解決分庫(kù)分表后排序、分頁(yè)問(wèn)題？

  分庫(kù)分表后，排序和分頁(yè)問(wèn)題變得相對(duì)復(fù)雜，因?yàn)閿?shù)據(jù)不再集中在一個(gè)單一的數(shù)據(jù)庫(kù)或表中。解決這些問(wèn)題需要綜合考慮多種因素，包括數(shù)據(jù)量、查詢頻率、業(yè)務(wù)需求等。以下是一些解決分庫(kù)分表后排序和分頁(yè)問(wèn)題的策略：

排序問(wèn)題

1. 全局排序字段： 在分庫(kù)分表時(shí)，可以引入一個(gè)全局排序字段，所有分片都基于這個(gè)字段進(jìn)行排序。這樣，即使數(shù)據(jù)分布在不同的分片中，也可以保證整體排序的一致性。

2. 數(shù)據(jù)同步與合并： 在查詢時(shí)，從各個(gè)分片中分別獲取數(shù)據(jù)，然后在應(yīng)用層將這些數(shù)據(jù)按照排序規(guī)則進(jìn)行合并。這種方式需要處理大量的數(shù)據(jù)傳輸和合并邏輯，可能對(duì)性能有一定影響。

3. 中間件支持： 使用支持分庫(kù)分表的中間件，如ShardingSphere、MyCAT等。這些中間件通常提供了強(qiáng)大的排序功能，能夠處理分庫(kù)分表后的排序問(wèn)題。

4. 預(yù)算與緩存： 對(duì)于某些固定的排序需求，可以預(yù)先計(jì)算排序結(jié)果并緩存起來(lái)，減少實(shí)時(shí)計(jì)算的壓力。

分頁(yè)問(wèn)題

1. 分頁(yè)參數(shù)調(diào)整： 在分庫(kù)分表的情況下，傳統(tǒng)的LIMIT OFFSET分頁(yè)方式可能不再適用。可以考慮調(diào)整分頁(yè)參數(shù)，比如使用基于游標(biāo)（cursor）的分頁(yè)方式，或者基于時(shí)間戳、ID等排序字段的范圍查詢來(lái)實(shí)現(xiàn)分頁(yè)。

2. 數(shù)據(jù)聚合： 類似于排序問(wèn)題的解決方式，從各個(gè)分片中分別獲取數(shù)據(jù)，然后在應(yīng)用層進(jìn)行數(shù)據(jù)聚合，實(shí)現(xiàn)分頁(yè)功能。

3. 中間件支持： 使用支持分庫(kù)分表的中間件，這些中間件通常也提供了分頁(yè)功能的支持，能夠簡(jiǎn)化分頁(yè)查詢的處理。

4. 限制分頁(yè)： 對(duì)于深度分頁(yè)的需求，可以考慮限制分頁(yè)的深度，避免查詢大量的數(shù)據(jù)。例如，只支持查詢前100頁(yè)的數(shù)據(jù)。

5. 預(yù)加載與緩存： 對(duì)于經(jīng)常訪問(wèn)的分頁(yè)數(shù)據(jù)，可以考慮預(yù)加載并緩存起來(lái)，減少實(shí)時(shí)查詢的開銷。

分庫(kù)分表如何擴(kuò)容？

  當(dāng)數(shù)據(jù)量逐漸增加，需要進(jìn)行分庫(kù)分表的擴(kuò)容時(shí)，可以從以下幾個(gè)方面來(lái)考慮和制定策略：

1. 數(shù)據(jù)增長(zhǎng)評(píng)估

  首先，要對(duì)數(shù)據(jù)的增長(zhǎng)趨勢(shì)進(jìn)行準(zhǔn)確的評(píng)估。通過(guò)分析歷史數(shù)據(jù)、業(yè)務(wù)發(fā)展趨勢(shì)以及用戶增長(zhǎng)情況，可以預(yù)測(cè)未來(lái)的數(shù)據(jù)量增長(zhǎng)情況。一般預(yù)估未來(lái)3~5年的數(shù)據(jù)增長(zhǎng)。

2. 選擇合適的分片鍵

  選擇一個(gè)合適的分片鍵是分庫(kù)分表的關(guān)鍵。分片鍵應(yīng)該能夠均勻分布數(shù)據(jù)，避免某些數(shù)據(jù)庫(kù)或表過(guò)載。同時(shí)，分片鍵的選擇也要考慮到查詢性能和數(shù)據(jù)一致性等因素。

3. 實(shí)施擴(kuò)容

  基于數(shù)據(jù)增長(zhǎng)趨勢(shì)和分片鍵的選擇，制定詳細(xì)的擴(kuò)容計(jì)劃。這包括確定擴(kuò)容的時(shí)間點(diǎn)、擴(kuò)容的目標(biāo)規(guī)模、數(shù)據(jù)遷移和重新分配的策略等。確保擴(kuò)容過(guò)程能夠順利進(jìn)行，盡可能減少對(duì)業(yè)務(wù)的影響。

4. 數(shù)據(jù)遷移與重新分配

  在擴(kuò)容過(guò)程中，需要進(jìn)行數(shù)據(jù)遷移和重新分配。這通常涉及到將現(xiàn)有數(shù)據(jù)從舊的數(shù)據(jù)庫(kù)或表遷移到新的數(shù)據(jù)庫(kù)或表中。可以使用數(shù)據(jù)遷移工具或自動(dòng)化腳本來(lái)完成這個(gè)過(guò)程，確保數(shù)據(jù)的完整性和一致性。

5. 負(fù)載均衡

  在擴(kuò)容后，需要確保數(shù)據(jù)在新舊數(shù)據(jù)庫(kù)或表之間均勻分布，以實(shí)現(xiàn)負(fù)載均衡。可以使用負(fù)載均衡器或支持分庫(kù)分表的中間件來(lái)動(dòng)態(tài)分配請(qǐng)求，確保系統(tǒng)的性能和穩(wěn)定性。

6. 監(jiān)控與調(diào)優(yōu)

  在擴(kuò)容過(guò)程中和擴(kuò)容后，需要對(duì)系統(tǒng)進(jìn)行持續(xù)的監(jiān)控和調(diào)優(yōu)。通過(guò)監(jiān)控?cái)?shù)據(jù)庫(kù)或表的負(fù)載情況、查詢性能等指標(biāo)，及時(shí)發(fā)現(xiàn)并解決性能瓶頸和故障。同時(shí)，根據(jù)實(shí)際需求進(jìn)行調(diào)優(yōu)，如調(diào)整索引、優(yōu)化查詢語(yǔ)句等，以提升系統(tǒng)的整體性能。

分庫(kù)分表中間件技術(shù)對(duì)比

業(yè)界常用的分庫(kù)分表中間有：Sharding和MyCat

1. ShardingSphere

  ShardingSphere是一套開源的分布式數(shù)據(jù)庫(kù)中間件解決方案組成的生態(tài)圈，它由Sharding-JDBC、Sharding-Proxy和Sharding-Sidecar（計(jì)劃中）這3款相互獨(dú)立的產(chǎn)品組成。ShardingSphere提供數(shù)據(jù)分片、讀寫分離、多數(shù)據(jù)源集成等功能。其中，Sharding-JDBC是一個(gè)輕量級(jí)的Java框架，以jar包形式提供服務(wù)，無(wú)需額外部署和依賴，適用于Java應(yīng)用。Sharding-Proxy則是一個(gè)獨(dú)立的數(shù)據(jù)庫(kù)代理，支持MySQL和PostgreSQL協(xié)議，可以實(shí)現(xiàn)透明的數(shù)據(jù)庫(kù)操作。

優(yōu)點(diǎn)：

• 功能豐富，支持?jǐn)?shù)據(jù)分片、讀寫分離、廣播表、分布式事務(wù)等。
• 社區(qū)活躍，文檔完善，有較多的成功案例。
• 支持多種數(shù)據(jù)庫(kù)和SQL方言，具有較好的兼容性。

缺點(diǎn)：

• 對(duì)于非Java應(yīng)用可能需要額外的適配工作。
• 在處理復(fù)雜SQL時(shí)可能存在一定的性能損耗。

2. Mycat

  Mycat是一個(gè)開源的、跨平臺(tái)的、基于MySQL協(xié)議的數(shù)據(jù)庫(kù)中間件，支持SQL分析、SQL解析、SQL路由、SQL改寫、SQL執(zhí)行和結(jié)果歸并等功能。Mycat可以實(shí)現(xiàn)透明的讀寫分離、自動(dòng)故障切換、負(fù)載均衡等特性，同時(shí)支持多租戶模式和全局序列號(hào)等功能。

優(yōu)點(diǎn)：

• 支持MySQL協(xié)議，對(duì)于使用MySQL的應(yīng)用無(wú)需修改代碼即可接入。
• 支持多種路由策略，可以滿足不同的業(yè)務(wù)需求。
• 提供豐富的監(jiān)控和管理功能，方便運(yùn)維和管理。

缺點(diǎn)：

• 主要針對(duì)MySQL數(shù)據(jù)庫(kù)，對(duì)于其他類型的數(shù)據(jù)庫(kù)支持有限。
• 在處理復(fù)雜SQL時(shí)可能存在一定的限制和性能問(wèn)題。

如何解決分庫(kù)分表事務(wù)問(wèn)題？

  分庫(kù)分表后，因?yàn)閿?shù)據(jù)分布在不同的數(shù)據(jù)庫(kù)和表中，需要確保不同數(shù)據(jù)庫(kù)實(shí)例間的事務(wù)一致性。解決這類分布式事務(wù)問(wèn)題，可以參考個(gè)人的其它歷史文章：

分布式事務(wù)基礎(chǔ)理論

如何選擇分布式事務(wù)解決方案？

一站式分布式事務(wù)Seata方案

總結(jié)

分庫(kù)分表技術(shù)總結(jié)

一、分庫(kù)分表策略

分庫(kù)分表（Sharding）是一種將單一數(shù)據(jù)庫(kù)拆分為多個(gè)數(shù)據(jù)庫(kù)實(shí)例，以及將單一大表拆分為多個(gè)小表的技術(shù)策略。其目的是解決單一數(shù)據(jù)庫(kù)在數(shù)據(jù)量、并發(fā)訪問(wèn)、性能等方面的瓶頸，提升系統(tǒng)的整體性能和可靠性。

常見的分庫(kù)分表策略包括：

1. 水平拆分（Sharding by Key）：根據(jù)某個(gè)字段的值將表拆分為多個(gè)子表，每個(gè)子表只包含部分?jǐn)?shù)據(jù)。這種策略適用于數(shù)據(jù)量巨大、訪問(wèn)頻繁的表。
2. 垂直拆分：將表按照列進(jìn)行拆分，將不同列的數(shù)據(jù)分散到不同的表中。這種策略適用于某些列的數(shù)據(jù)量特別大或訪問(wèn)特別頻繁的情況。
3. 讀寫分離：將數(shù)據(jù)庫(kù)的讀操作和寫操作分離到不同的數(shù)據(jù)庫(kù)實(shí)例上，以提高系統(tǒng)的并發(fā)處理能力和性能。

二、分庫(kù)分表常見問(wèn)題

1. 數(shù)據(jù)一致性問(wèn)題：分庫(kù)分表后，數(shù)據(jù)分布在不同的數(shù)據(jù)庫(kù)和表中，需要確保跨庫(kù)跨表操作的數(shù)據(jù)一致性。
2. 跨庫(kù)跨表查詢問(wèn)題：復(fù)雜的跨庫(kù)跨表查詢可能變得困難，需要考慮查詢性能和數(shù)據(jù)整合。
3. 事務(wù)處理問(wèn)題：分布式事務(wù)的處理比單一數(shù)據(jù)庫(kù)更復(fù)雜，需要確保不同數(shù)據(jù)庫(kù)實(shí)例間的事務(wù)一致性。
4. 中間件選擇問(wèn)題：選擇合適的分庫(kù)分表中間件是關(guān)鍵，需要考慮中間件的性能、穩(wěn)定性、兼容性等因素。

此外，對(duì)于某些不適用分庫(kù)分表的場(chǎng)景，或者希望簡(jiǎn)化分布式數(shù)據(jù)庫(kù)管理的復(fù)雜性，可以考慮使用TiDB。

參考文章鏈接： https://mp.weixin.qq.com/s/SuJ-XCaVegVunOIf69-9AQ

結(jié)尾

  共享即共贏。如有幫助，幫忙點(diǎn)贊和在看。關(guān)注公眾號(hào)【碼易有道】，定期更新一些工程實(shí)踐的總結(jié)和個(gè)人心得。歡迎你的加入，一起學(xué)習(xí)、交流、做長(zhǎng)期且正確的事情！！！

參考資料

[1]

MySQL一張表到底能存多少數(shù)據(jù)？: https://www.php.cn/faq/500130.html