SQL數(shù)據(jù)庫分庫分表設(shè)計(jì)及常見問題
當(dāng)前位置：點(diǎn)晴教程→知識管理交流
→『 技術(shù)文檔交流 』
文章導(dǎo)讀 背景介紹 隨著互聯(lián)網(wǎng)技術(shù)的發(fā)展，數(shù)據(jù)量呈爆炸性增長。大數(shù)據(jù)量的業(yè)務(wù)場景中，數(shù)據(jù)庫成為系統(tǒng)性能瓶頸的一個(gè)主要因素。當(dāng)單個(gè)數(shù)據(jù)庫包含了太多數(shù)據(jù)或過高的訪問量時(shí)，會出現(xiàn)查詢緩慢、響應(yīng)時(shí)間長等問題，嚴(yán)重影響用戶體驗(yàn)。為了解決這一問題， 場景分析例如：在交易系統(tǒng)核心數(shù)據(jù)庫設(shè)計(jì)大致包括： 產(chǎn)品數(shù)據(jù)庫（Product/Asset Database）：存儲系統(tǒng)可交易的產(chǎn)品或資產(chǎn)的詳細(xì)信息，比如在股票交易系統(tǒng)中，這里會包含股票代碼、股票名稱、當(dāng)前價(jià)格等信息。 訂單數(shù)據(jù)庫（Order Database）：存儲用戶提交的訂單信息，包括訂單ID、訂單狀態(tài)（如待處理、完成、取消）、訂單創(chuàng)建時(shí)間等。 用戶數(shù)據(jù)庫（User Database）：存儲用戶的基本信息，如用戶ID、用戶名、密碼（通常進(jìn)行加密存儲）、聯(lián)系信息等，以及用戶的權(quán)限和角色定義。 交易數(shù)據(jù)庫（Transaction Database）：記錄所有交易的詳細(xì)信息，如交易ID、交易類型（買入、賣出等）、交易金額、交易時(shí)間、交易雙方等。這個(gè)數(shù)據(jù)庫是交易系統(tǒng)的核心，需要高效且可靠。 配置數(shù)據(jù)庫（Configuration Database）：存儲系統(tǒng)配置信息，如交易規(guī)則、費(fèi)用設(shè)置、系統(tǒng)參數(shù)等。 歷史數(shù)據(jù)庫（Historical Data Database）：保存交易、訂單和價(jià)格的歷史記錄。這對于數(shù)據(jù)分析、報(bào)告生成及監(jiān)控非常重要。 賬戶數(shù)據(jù)庫（Account Database）：存儲用戶的賬戶信息，包括賬戶余額、賬戶類型、賬戶狀態(tài)等。在交易系統(tǒng)中，賬戶信息是核心數(shù)據(jù)之一。 安全和審計(jì)數(shù)據(jù)庫（Security and Audit Database）：用于記錄安全相關(guān)的事件，如登錄嘗試、權(quán)限變更等，以及審計(jì)記錄，確保系統(tǒng)的安全性和可追蹤性。 ...... 從上邊的分析看，對應(yīng)數(shù)據(jù)庫表大致歸納為以下幾種類型：
...... 思考一般哪些表可能存在數(shù)據(jù)激增、性能問題？日志表、流水表、用戶表等都可能。而系統(tǒng)配置則可能相對較少。 分庫分表
分庫分表是一種數(shù)據(jù)庫架構(gòu)優(yōu)化技術(shù)，說白了就是一種 以訂單庫 db_order 和 訂單表 tb_order 為例（db為庫，tb為表）： 水平分庫：根據(jù)某些規(guī)則（例如訂單ID的范圍）將db_order數(shù)據(jù)庫分成多個(gè)數(shù)據(jù)庫（分片），如db_order_1, db_order_2, db_order_3等。每個(gè)數(shù)據(jù)庫的表結(jié)構(gòu)相同，但存儲的訂單數(shù)據(jù)不同。  水平分表：根據(jù)訂單的創(chuàng)建時(shí)間，將tb_order分成tb_order_2022, tb_order_2023, tb_order_2024等多個(gè)表，每個(gè)表存儲各自時(shí)間段的訂單數(shù)據(jù)。表結(jié)構(gòu)保持一致，但每個(gè)表只存儲一部分?jǐn)?shù)據(jù)。  垂直分庫：根據(jù)業(yè)務(wù)功能將數(shù)據(jù)垂直分割到不同的數(shù)據(jù)庫中。例如，將訂單相關(guān)的表保留在db_order中，將用戶相關(guān)的表遷移到新的數(shù)據(jù)庫db_user中，商品相關(guān)的表遷移到db_product中。 垂直分表：若tb_order表中的字段非常多，包含了訂單的基本信息、訂單屬性信息、訂單資費(fèi)信息等多個(gè)方面。此時(shí)，可以將tb_order表垂直拆分為多個(gè)表，如tb_order_base存儲訂單的基本信息，tb_order_chars存儲訂單屬性信息、tb_order_charges存儲訂單資費(fèi)信息。 小結(jié) 有了以上這些了解，基本對分庫分表概念有了大致了解。對于分庫一般按照 分庫分表常見問題
參考規(guī)則根據(jù)《阿里巴巴Java開發(fā)手冊》，給出如下建議：  工程經(jīng)驗(yàn)事實(shí)上，通常在實(shí)戰(zhàn)中，一般按經(jīng)驗(yàn)數(shù)據(jù)達(dá)到千萬級，就需要分庫分表。原因如下： 我們知道：InnoDB管理磁盤的最小單元：頁，頁大小16KB. 在日常開發(fā)中，對于數(shù)據(jù)庫性能優(yōu)化，我們首先想到的是  圖片來源于網(wǎng)絡(luò)，侵權(quán)刪 樹高為3的B+樹數(shù)據(jù)存儲計(jì)算規(guī)則： 根節(jié)點(diǎn)計(jì)算： 假設(shè)數(shù)據(jù)類型是bigint，大小為8b。數(shù)據(jù)本身也需要一小塊空間，用來存儲下一層索引數(shù)據(jù)頁的地址，大小為6b， 那么根節(jié)點(diǎn)是可以存儲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è)元素都會延伸出來一個(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可以存儲的總數(shù)據(jù)為 1170 * 1170 * 16 = 21902400 (千萬級) 其實(shí)計(jì)算結(jié)果與我們平時(shí)的工作經(jīng)驗(yàn)也是相符的，一般mysql一張表的數(shù)據(jù)超過了千萬也是得進(jìn)行分表操作了。 參考文章： MySQL一張表到底能存多少數(shù)據(jù)？[1]
例如，本節(jié)我們以訂單表的分表為例，一般訂單表中含有訂單編號：order_id, 用戶編號：user_id, 訂單創(chuàng)建時(shí)間：order_date等。 對于訂單表，通常我們可以考慮以下分片鍵選項(xiàng)： 訂單編號 優(yōu)點(diǎn)：訂單編號通常是唯一的，可以確保每個(gè)訂單都分散到不同的分片上。這對于保證數(shù)據(jù)均勻分布和避免熱點(diǎn)數(shù)據(jù)非常有幫助。 用戶編號 優(yōu)點(diǎn)：用戶編號通常也是唯一的，并且如果用戶的訂單量分布均勻，那么使用用戶編號作為分片鍵可以確保每個(gè)用戶的訂單都在同一個(gè)分片上，這對于查詢某個(gè)用戶的所有訂單非常高效。 缺點(diǎn)：如果用戶的訂單量差異很大，那么某些分片可能會存儲大量的訂單數(shù)據(jù)，而其他分片可能只有少量的數(shù)據(jù)。這會導(dǎo)致數(shù)據(jù)分布不均勻，進(jìn)而影響查詢性能。 訂單創(chuàng)建時(shí)間 優(yōu)點(diǎn)：適用于：按時(shí)間范圍查詢訂單的場景。 缺點(diǎn)：可能出現(xiàn)熱點(diǎn)數(shù)據(jù)傾斜問題（即在某個(gè)時(shí)段產(chǎn)生訂單峰值）
在選擇主鍵策略時(shí)，需要注意以下幾點(diǎn)：
在 MySQL 中進(jìn)行分庫分表時(shí)，自增主鍵策略確實(shí)需要特別處理，因?yàn)閭鹘y(tǒng)的自增主鍵策略在分布式環(huán)境下會導(dǎo)致主鍵沖突。每個(gè)數(shù)據(jù)庫實(shí)例或分片都會從相同的起始點(diǎn)開始自增，這會導(dǎo)致在不同的分片上生成相同的 ID，進(jìn)而引發(fā)數(shù)據(jù)沖突。 幾種常見的主鍵策略方案：
UUID 是一個(gè) 128 位的值，具有全局唯一性，可以很好地解決分布式環(huán)境下的主鍵沖突問題。但是，UUID 字符串較長，存儲和索引效率較低，而且是無序的，可能會影響查詢性能。
通過這種結(jié)構(gòu)，雪花算法可以保證生成的ID按時(shí)間遞增，并且整個(gè)分布式系統(tǒng)中不會有重復(fù)的ID。
總之，在分庫分表時(shí)，自增主鍵策略需要進(jìn)行特殊處理，以確保全局唯一性，并根據(jù)實(shí)際情況選擇合適的方案。
選擇分庫分表的策略時(shí)，確實(shí)需要根據(jù)具體的業(yè)務(wù)場景和數(shù)據(jù)特性來決定。例如訂單表，以訂單ID ( 基于范圍的策略適用場景：當(dāng)訂單ID有明確的增長趨勢，例如連續(xù)的自增ID，并且你知道未來可能的訂單數(shù)量時(shí)，范圍分表是一個(gè)好選擇。 策略實(shí)現(xiàn)：可以將訂單ID按照范圍劃分到不同的表中。例如，訂單ID【1-1000萬】 在表  優(yōu)點(diǎn)：
缺點(diǎn)：
基于哈希的策略適用場景：當(dāng)訂單ID沒有明確的增長趨勢，哈希分表是一個(gè)好選擇。 策略實(shí)現(xiàn)：使用哈希函數(shù)對訂單ID進(jìn)行哈希運(yùn)算，然后根據(jù)哈希值的結(jié)果決定存儲在哪個(gè)表中。 優(yōu)點(diǎn)：
缺點(diǎn)：
映射表策略適用場景：當(dāng)訂單ID的分布不均，或者需要靈活控制數(shù)據(jù)分布時(shí)，映射表分表可能是一個(gè)好選擇。 策略實(shí)現(xiàn)：使用一個(gè)映射表來記錄每個(gè)訂單ID應(yīng)該存儲在哪個(gè)表中。這個(gè)映射表可以是內(nèi)存中的數(shù)據(jù)結(jié)構(gòu)，也可以是數(shù)據(jù)庫中的一個(gè)表。 優(yōu)點(diǎn)：
缺點(diǎn)：
一致性哈希策略適用場景：當(dāng)系統(tǒng)需要高可用性，并且希望在添加或刪除節(jié)點(diǎn)時(shí)盡量減少數(shù)據(jù)遷移時(shí)，一致性哈?？赡苁且粋€(gè)好選擇。 策略實(shí)現(xiàn)：使用一致性哈希算法將訂單ID映射到哈希環(huán)上，然后根據(jù)哈希環(huán)上的節(jié)點(diǎn)（或表）來存儲數(shù)據(jù)。 一致性哈希算法的核心思想是將哈希值空間表示為一個(gè)閉合的圓環(huán)（哈希環(huán)），每個(gè)節(jié)點(diǎn)負(fù)責(zé)維護(hù)圓環(huán)上一段連續(xù)的哈希值范圍。  在分庫分表的場景中，可以將每個(gè)數(shù)據(jù)庫或表看作是一個(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ù)庫或表），將數(shù)據(jù)插入或查詢該節(jié)點(diǎn)。 優(yōu)點(diǎn)：
缺點(diǎn)：
曾幾何時(shí)，面試過程中遇到過這樣一個(gè)問題：假設(shè)有一個(gè)用戶表，你用ID做的分片鍵，那么有一個(gè)類似于name這樣的字段如何查詢？ 這里提供幾種常見的思路： 1.全局索引全局索引是一個(gè)跨所有分片的索引，它包含了非分片鍵字段和對應(yīng)的分片鍵信息。查詢時(shí)，先通過全局索引找到相關(guān)的分片鍵，然后在相應(yīng)的分片中查詢詳細(xì)數(shù)據(jù)。 適用場景：適用于查詢頻率高、數(shù)據(jù)量大的非分片鍵字段。 優(yōu)點(diǎn)：查詢效率高，可以快速定位到數(shù)據(jù)所在的分片。 缺點(diǎn)：全局索引維護(hù)成本較高，需要定期更新以保持與分片數(shù)據(jù)的一致性。 2. 數(shù)據(jù)冗余在每個(gè)分片中存儲部分非分片鍵字段的數(shù)據(jù)。這樣，即使不直接查詢分片鍵，也可以在分片內(nèi)快速找到相關(guān)數(shù)據(jù)。 適用場景：適用于查詢性能要求極高，且可以接受一定數(shù)據(jù)冗余的場景。 優(yōu)點(diǎn)：查詢性能高，無需跨分片查詢。 缺點(diǎn)：數(shù)據(jù)冗余增加了存儲成本和維護(hù)復(fù)雜性。 3. 應(yīng)用層處理在應(yīng)用層實(shí)現(xiàn)復(fù)雜的查詢邏輯，將多個(gè)分片中的查詢結(jié)果匯總后進(jìn)行處理。 適用場景：適用于查詢頻率不高，或者可以接受一定延遲的場景。 優(yōu)點(diǎn)：靈活性高，可以根據(jù)業(yè)務(wù)需求定制查詢邏輯。 缺點(diǎn)：查詢性能可能受到網(wǎng)絡(luò)延遲和分片數(shù)量的影響。 4. 使用Elasticsearch（ES）將非分片鍵字段的數(shù)據(jù)同步到Elasticsearch中，利用Elasticsearch強(qiáng)大的搜索和查詢能力進(jìn)行查詢。 適用場景：適用于非結(jié)構(gòu)化數(shù)據(jù)、全文搜索、復(fù)雜查詢等場景。 優(yōu)點(diǎn)：支持復(fù)雜的查詢操作，如全文搜索、模糊匹配等；查詢性能高，支持分布式部署。 缺點(diǎn)：需要維護(hù)Elasticsearch集群，增加了系統(tǒng)的復(fù)雜性；數(shù)據(jù)同步可能引入一定的延遲。 5. 數(shù)據(jù)庫中間件使用數(shù)據(jù)庫中間件（如ShardingSphere、MyCAT等）來管理分庫分表，中間件可以自動處理非分片鍵字段的查詢，將請求路由到正確的分片。 適用場景：適用于希望減少應(yīng)用層復(fù)雜性的場景。 優(yōu)點(diǎn)：簡化了應(yīng)用層的查詢邏輯，減少了開發(fā)和維護(hù)的工作量。 缺點(diǎn)：需要配置和維護(hù)數(shù)據(jù)庫中間件。 總結(jié)在實(shí)際應(yīng)用中，可能需要根據(jù)實(shí)際情況結(jié)合多種策略來滿足不同的查詢需求。同時(shí)，隨著業(yè)務(wù)的發(fā)展和數(shù)據(jù)量的增長，可能需要不斷調(diào)整和優(yōu)化分庫分表策略。
熱點(diǎn)數(shù)據(jù)傾斜通常發(fā)生在某些特定的數(shù)據(jù)項(xiàng)（例如，用戶激增、促銷訂單峰值等）等，導(dǎo)致這些數(shù)據(jù)的查詢和更新操作集中在些某特定的數(shù)據(jù)庫或表上，從而造成性能瓶頸。 解決方案：采用 
分庫分表后，數(shù)據(jù)被分散到了不同的數(shù)據(jù)庫或表中?？鐜礻P(guān)聯(lián)查詢成為新的問題。為了解決這個(gè)問題，可以采取以下幾種策略：
需要注意的是，雖然上述方法可以解決跨庫關(guān)聯(lián)查詢的問題，但它們也會帶來一些額外的復(fù)雜性。在設(shè)計(jì)分庫分表方案時(shí)，需要綜合考慮業(yè)務(wù)需求、數(shù)據(jù)量、查詢頻率等因素，選擇合適的策略來平衡性能和可維護(hù)性。同時(shí)，隨著業(yè)務(wù)的發(fā)展和數(shù)據(jù)量的增長，可能需要對分庫分表方案進(jìn)行調(diào)整和優(yōu)化。
分庫分表后，排序和分頁問題變得相對復(fù)雜，因?yàn)閿?shù)據(jù)不再集中在一個(gè)單一的數(shù)據(jù)庫或表中。解決這些問題需要綜合考慮多種因素，包括數(shù)據(jù)量、查詢頻率、業(yè)務(wù)需求等。以下是一些解決分庫分表后排序和分頁問題的策略： 排序問題
分頁問題
當(dāng)數(shù)據(jù)量逐漸增加，需要進(jìn)行分庫分表的擴(kuò)容時(shí)，可以從以下幾個(gè)方面來考慮和制定策略： 1. 數(shù)據(jù)增長評估首先，要對數(shù)據(jù)的增長趨勢進(jìn)行準(zhǔn)確的評估。通過分析歷史數(shù)據(jù)、業(yè)務(wù)發(fā)展趨勢以及用戶增長情況，可以預(yù)測未來的數(shù)據(jù)量增長情況。一般預(yù)估未來3~5年的數(shù)據(jù)增長。 2. 選擇合適的分片鍵選擇一個(gè)合適的分片鍵是分庫分表的關(guān)鍵。分片鍵應(yīng)該能夠均勻分布數(shù)據(jù)，避免某些數(shù)據(jù)庫或表過載。同時(shí)，分片鍵的選擇也要考慮到查詢性能和數(shù)據(jù)一致性等因素。 3. 實(shí)施擴(kuò)容基于數(shù)據(jù)增長趨勢和分片鍵的選擇，制定詳細(xì)的擴(kuò)容計(jì)劃。這包括確定擴(kuò)容的時(shí)間點(diǎn)、擴(kuò)容的目標(biāo)規(guī)模、數(shù)據(jù)遷移和重新分配的策略等。確保擴(kuò)容過程能夠順利進(jìn)行，盡可能減少對業(yè)務(wù)的影響。 4. 數(shù)據(jù)遷移與重新分配在擴(kuò)容過程中，需要進(jìn)行數(shù)據(jù)遷移和重新分配。這通常涉及到將現(xiàn)有數(shù)據(jù)從舊的數(shù)據(jù)庫或表遷移到新的數(shù)據(jù)庫或表中?？梢允褂脭?shù)據(jù)遷移工具或自動化腳本來完成這個(gè)過程，確保數(shù)據(jù)的完整性和一致性。 5. 負(fù)載均衡在擴(kuò)容后，需要確保數(shù)據(jù)在新舊數(shù)據(jù)庫或表之間均勻分布，以實(shí)現(xiàn)負(fù)載均衡?？梢允褂秘?fù)載均衡器或支持分庫分表的中間件來動態(tài)分配請求，確保系統(tǒng)的性能和穩(wěn)定性。 6. 監(jiān)控與調(diào)優(yōu)在擴(kuò)容過程中和擴(kuò)容后，需要對系統(tǒng)進(jìn)行持續(xù)的監(jiān)控和調(diào)優(yōu)。通過監(jiān)控?cái)?shù)據(jù)庫或表的負(fù)載情況、查詢性能等指標(biāo)，及時(shí)發(fā)現(xiàn)并解決性能瓶頸和故障。同時(shí)，根據(jù)實(shí)際需求進(jìn)行調(diào)優(yōu)，如調(diào)整索引、優(yōu)化查詢語句等，以提升系統(tǒng)的整體性能。
業(yè)界常用的分庫分表中間有：Sharding和MyCat
ShardingSphere是一套開源的分布式數(shù)據(jù)庫中間件解決方案組成的生態(tài)圈，它由Sharding-JDBC、Sharding-Proxy和Sharding-Sidecar（計(jì)劃中）這3款相互獨(dú)立的產(chǎn)品組成。ShardingSphere提供 優(yōu)點(diǎn)：
缺點(diǎn)：
Mycat是一個(gè)開源的、跨平臺的、基于MySQL協(xié)議的數(shù)據(jù)庫中間件，支持SQL分析、SQL解析、SQL路由、SQL改寫、SQL執(zhí)行和結(jié)果歸并等功能。Mycat可以實(shí)現(xiàn)透明的 優(yōu)點(diǎn)：
缺點(diǎn)：
分庫分表后，因?yàn)閿?shù)據(jù)分布在不同的數(shù)據(jù)庫和表中，需要確保不同數(shù)據(jù)庫實(shí)例間的事務(wù)一致性。解決這類分布式事務(wù)問題，可以參考個(gè)人的其它歷史文章： 總結(jié)分庫分表技術(shù)總結(jié) 一、分庫分表策略 分庫分表（Sharding）是一種將單一數(shù)據(jù)庫拆分為多個(gè)數(shù)據(jù)庫實(shí)例，以及將單一大表拆分為多個(gè)小表的技術(shù)策略。其目的是解決單一數(shù)據(jù)庫在數(shù)據(jù)量、并發(fā)訪問、性能等方面的瓶頸，提升系統(tǒng)的整體性能和可靠性。 常見的分庫分表策略包括：
二、分庫分表常見問題
此外，對于某些不適用分庫分表的場景，或者希望簡化分布式數(shù)據(jù)庫管理的復(fù)雜性，可以考慮使用TiDB。 參考文章鏈接： https://mp.weixin.qq.com/s/SuJ-XCaVegVunOIf69-9AQ 結(jié)尾共享即共贏。如有幫助，幫忙點(diǎn)贊和在看。關(guān)注公眾號【碼易有道】，定期更新一些工程實(shí)踐的總結(jié)和個(gè)人心得。歡迎你的加入，一起學(xué)習(xí)、交流、做長期且正確的事情！??！ MySQL一張表到底能存多少數(shù)據(jù)？: https://www.php.cn/faq/500130.html 該文章在 2024/3/30 16:46:28 編輯過 |
關(guān)鍵字查詢
相關(guān)文章
正在查詢... 
|
400 186 1886
