一、分庫分表的觸發(fā)條件

在MySQL數(shù)據(jù)庫的使用過程中，當(dāng)數(shù)據(jù)量增長到一定規(guī)模時(shí)，單庫單表的架構(gòu)會(huì)面臨性能瓶頸，此時(shí)就需要考慮分庫分表。以下是常見的觸發(fā)場景：

1.1 數(shù)據(jù)量閾值

• 單表數(shù)據(jù)量達(dá)到1000萬-2000萬行時(shí)，查詢性能會(huì)明顯下降。這是因?yàn)镸ySQL的B+樹索引在數(shù)據(jù)量過大時(shí)，樹的高度增加，會(huì)導(dǎo)致磁盤IO次數(shù)增多，查詢效率降低。
• 例如，一個(gè)電商平臺(tái)的訂單表，隨著業(yè)務(wù)的增長，每月新增訂單量達(dá)到數(shù)百萬，經(jīng)過一年多的積累，數(shù)據(jù)量突破1500萬，此時(shí)簡單的查詢?nèi)?ldquo;查詢用戶近三個(gè)月的訂單”響應(yīng)時(shí)間從原來的幾百毫秒增加到幾秒，嚴(yán)重影響用戶體驗(yàn)。

1.2 并發(fā)壓力

當(dāng)數(shù)據(jù)庫的并發(fā)連接數(shù)過高，超過單庫的處理能力時(shí)，會(huì)出現(xiàn)連接超時(shí)、鎖等待等問題。

• 比如一個(gè)社交應(yīng)用的消息表，在高峰期每秒有數(shù)千次的讀寫操作，單庫無法承受這樣的并發(fā)壓力，導(dǎo)致消息發(fā)送延遲、讀取失敗等情況。

二、分庫分表的核心技術(shù)模塊

2.1 水平分表

水平分表是將一個(gè)表中的數(shù)據(jù)按照某種規(guī)則（如范圍、哈希）拆分成多個(gè)結(jié)構(gòu)相同的子表，每個(gè)子表只包含一部分?jǐn)?shù)據(jù)。

2.1.1 技術(shù)原理

• 范圍分片：按照數(shù)據(jù)的某個(gè)字段（如時(shí)間、ID范圍）進(jìn)行分片。例如，訂單表按照月份分片，每個(gè)月的數(shù)據(jù)存放在一個(gè)子表中。
• 哈希分片：對(duì)數(shù)據(jù)的某個(gè)字段進(jìn)行哈希計(jì)算，根據(jù)哈希結(jié)果將數(shù)據(jù)分配到不同的子表中。比如，根據(jù)用戶ID進(jìn)行哈希，將不同用戶的訂單分配到不同的子表。

2.1.2 案例與代碼實(shí)現(xiàn)

案例：一個(gè)電商平臺(tái)的訂單表orders，包含字段order_id（訂單ID）、user_id（用戶ID）、order_time（下單時(shí)間）等，數(shù)據(jù)量達(dá)到2000萬，需要進(jìn)行水平分表。采用按order_id范圍分片，每500萬訂單ID為一個(gè)區(qū)間，分為4個(gè)子表orders_1、orders_2、orders_3、orders_4。

代碼實(shí)現(xiàn)：

-- 創(chuàng)建分表
CREATE TABLE orders_1 (
  order_id BIGINT NOT NULL PRIMARY KEY,
  user_id BIGINT NOT NULL,
  order_time DATETIME NOT NULL,
  -- 其他字段
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
WHERE order_id BETWEEN 1 AND 5000000;
CREATE TABLE orders_2 (
  order_id BIGINT NOT NULL PRIMARY KEY,
  user_id BIGINT NOT NULL,
  order_time DATETIME NOT NULL,
  -- 其他字段
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
WHERE order_id BETWEEN 5000001 AND 10000000;
CREATE TABLE orders_3 (
  order_id BIGINT NOT NULL PRIMARY KEY,
  user_id BIGINT NOT NULL,
  order_time DATETIME NOT NULL,
  -- 其他字段
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
WHERE order_id BETWEEN 10000001 AND 15000000;
CREATE TABLE orders_4 (
  order_id BIGINT NOT NULL PRIMARY KEY,
  user_id BIGINT NOT NULL,
  order_time DATETIME NOT NULL,
  -- 其他字段
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
WHERE order_id BETWEEN 15000001 AND 20000000;
-- 創(chuàng)建視圖，方便查詢
CREATE VIEW orders AS
SELECT * FROM orders_1
UNION ALL
SELECT * FROM orders_2
UNION ALL
SELECT * FROM orders_3
UNION ALL
SELECT * FROM orders_4;

2.2 垂直分表

垂直分表是將一個(gè)表中字段較多的表，按照字段的熱點(diǎn)程度、訪問頻率等，拆分成多個(gè)包含部分字段的子表。

2.2.1 技術(shù)原理

• 將經(jīng)常被查詢的熱點(diǎn)字段放在一個(gè)子表中，將不常被查詢的冷字段放在另一個(gè)子表中。這樣可以減少每次查詢時(shí)讀取的數(shù)據(jù)量，提高查詢效率。
• 例如，用戶表中，用戶的基本信息（如用戶名、手機(jī)號(hào)）經(jīng)常被查詢，而用戶的詳細(xì)信息（如家庭住址、個(gè)人簡介）不常被查詢，可以將其拆分成兩個(gè)子表。

2.2.2 案例與代碼實(shí)現(xiàn)

案例：用戶表user包含字段user_id、username、phone、address、introduction等，其中username、phone經(jīng)常被查詢，address、introduction不常被查詢，進(jìn)行垂直分表。

代碼實(shí)現(xiàn)：

-- 創(chuàng)建用戶基本信息表（熱點(diǎn)字段）
CREATE TABLE user_base (
  user_id BIGINT NOT NULL PRIMARY KEY,
  username VARCHAR(50) NOT NULL,
  phone VARCHAR(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 創(chuàng)建用戶詳細(xì)信息表（冷字段）
CREATE TABLE user_detail (
  user_id BIGINT NOT NULL PRIMARY KEY,
  address VARCHAR(200),
  introduction TEXT,
  FOREIGN KEY (user_id) REFERENCES user_base(user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2.3 分庫

分庫是將多個(gè)表按照一定的規(guī)則拆分到不同的數(shù)據(jù)庫中，以降低單庫的壓力。

2.3.1 技術(shù)原理

• 可以按照業(yè)務(wù)模塊進(jìn)行分庫，將不同業(yè)務(wù)模塊的表放在不同的數(shù)據(jù)庫中。例如，電商平臺(tái)可以將用戶相關(guān)的表放在用戶庫，訂單相關(guān)的表放在訂單庫。
• 也可以結(jié)合分表進(jìn)行分庫，將分表后的子表分布到不同的數(shù)據(jù)庫中。

2.3.2 案例與代碼實(shí)現(xiàn)

案例：一個(gè)大型電商平臺(tái)，包含用戶模塊、商品模塊、訂單模塊，將這三個(gè)模塊的表分別放在user_db、product_db、order_db三個(gè)數(shù)據(jù)庫中。

代碼實(shí)現(xiàn)：

• 在不同的數(shù)據(jù)庫實(shí)例中分別創(chuàng)建對(duì)應(yīng)的表，這里以用戶庫和訂單庫為例：

-- 在user_db數(shù)據(jù)庫中創(chuàng)建用戶相關(guān)表
USE user_db;
CREATE TABLE user_base (
  user_id BIGINT NOT NULL PRIMARY KEY,
  username VARCHAR(50) NOT NULL,
  phone VARCHAR(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 在order_db數(shù)據(jù)庫中創(chuàng)建訂單相關(guān)表
USE order_db;
CREATE TABLE orders_1 (
  order_id BIGINT NOT NULL PRIMARY KEY,
  user_id BIGINT NOT NULL,
  order_time DATETIME NOT NULL
  -- 其他字段
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

三、分庫分表帶來的新問題

3.1 分布式事務(wù)

分庫分表后，一個(gè)業(yè)務(wù)操作可能涉及多個(gè)數(shù)據(jù)庫或多個(gè)表，此時(shí)保證事務(wù)的一致性變得復(fù)雜。

3.1.1 問題說明

在單庫單表中，MySQL的ACID特性可以保證事務(wù)的一致性。但在分布式環(huán)境下，多個(gè)數(shù)據(jù)庫之間無法直接使用本地事務(wù)，可能出現(xiàn)部分操作成功、部分操作失敗的情況。

3.1.2 案例

用戶下單操作，需要在訂單庫中創(chuàng)建訂單記錄，同時(shí)在庫存庫中減少商品庫存。如果訂單創(chuàng)建成功，但庫存減少失敗，就會(huì)出現(xiàn)數(shù)據(jù)不一致。

3.2 跨庫查詢

分庫分表后，查詢可能需要涉及多個(gè)數(shù)據(jù)庫或多個(gè)表，增加了查詢的復(fù)雜度。

3.2.1 問題說明

例如，查詢某個(gè)用戶在多個(gè)月份的訂單，由于訂單表按月份分表且可能分布在不同的庫中，需要同時(shí)查詢多個(gè)庫和表，然后合并結(jié)果。

3.2.2 案例

查詢用戶user_id=100在2023年1月和2月的訂單，需要分別查詢order_db1中的orders_202301表和order_db2中的orders_202302表，然后將結(jié)果合并。

3.3 數(shù)據(jù)遷移與擴(kuò)容

隨著業(yè)務(wù)的發(fā)展，可能需要對(duì)分庫分表的方案進(jìn)行調(diào)整，如增加分表數(shù)量、調(diào)整分片規(guī)則等，這會(huì)涉及到數(shù)據(jù)的遷移和擴(kuò)容。

3.3.1 問題說明

數(shù)據(jù)遷移過程中需要保證數(shù)據(jù)的一致性和完整性，同時(shí)要盡量減少對(duì)業(yè)務(wù)的影響。擴(kuò)容時(shí)需要考慮新的分片規(guī)則如何與原有規(guī)則兼容。

3.3.2 案例

原來訂單表按照order_id范圍分表，每500萬一個(gè)表，現(xiàn)在由于業(yè)務(wù)增長，需要將每個(gè)分表的范圍調(diào)整為250萬，需要將原有的orders_1表（1-500萬）拆分成orders_1（1-250萬）和orders_5（251-500萬），并遷移數(shù)據(jù)。

四、分庫分表的解決方案

4.1 中間件方案

使用專門的分庫分表中間件，如Sharding-JDBC、MyCat等，這些中間件可以幫助開發(fā)者透明地實(shí)現(xiàn)分庫分表，減少手動(dòng)處理的復(fù)雜度。

4.1.1 Sharding-JDBC

• 原理：Sharding-JDBC作為JDBC的增強(qiáng)版，通過對(duì)JDBC接口的封裝，實(shí)現(xiàn)了分庫分表的功能。它可以解析SQL語句，根據(jù)分片規(guī)則路由到對(duì)應(yīng)的數(shù)據(jù)庫和表，并將結(jié)果合并返回。
• 案例：使用Sharding-JDBC實(shí)現(xiàn)訂單表的水平分表，按照order_id取模分片。

代碼實(shí)現(xiàn)（Spring Boot整合Sharding-JDBC）：

spring:
  shardingsphere:
    datasource:
      names: db0,db1
      db0:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://localhost:3306/order_db0
        username: root
        password: root
      db1:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://localhost:3306/order_db1
        username: root
        password: root
    rules:
      sharding:
        tables:
          orders:
            actual-data-nodes: db${0..1}.orders_${0..1}
            database-strategy:
              standard:
                sharding-column: order_id
                sharding-algorithm-name: order_db_inline
            table-strategy:
              standard:
                sharding-column: order_id
                sharding-algorithm-name: order_table_inline
        sharding-algorithms:
          order_db_inline:
            type: INLINE
            props:
              algorithm-expression: db${order_id % 2}
          order_table_inline:
            type: INLINE
            props:
              algorithm-expression: orders_${order_id % 2}
    props:
      sql-show: true

4.2 分布式事務(wù)解決方案

4.2.1 兩階段提交（2PC）

• 原理：分為準(zhǔn)備階段和提交階段。準(zhǔn)備階段，協(xié)調(diào)者向所有參與者發(fā)送準(zhǔn)備請(qǐng)求，參與者執(zhí)行事務(wù)操作但不提交，并反饋是否可以提交；提交階段，如果所有參與者都反饋可以提交，協(xié)調(diào)者發(fā)送提交請(qǐng)求，否則發(fā)送回滾請(qǐng)求。
• 缺點(diǎn)：性能較差，協(xié)調(diào)者故障可能導(dǎo)致參與者處于阻塞狀態(tài)。

4.2.2 最終一致性方案（如TCC、SAGA）

• TCC（Try-Confirm-Cancel）：將一個(gè)事務(wù)拆分為Try、Confirm、Cancel三個(gè)操作。Try階段嘗試執(zhí)行事務(wù)，預(yù)留資源；Confirm階段確認(rèn)執(zhí)行事務(wù)；Cancel階段取消事務(wù)，釋放資源。
• SAGA：將一個(gè)長事務(wù)拆分為多個(gè)短事務(wù)，每個(gè)短事務(wù)都有對(duì)應(yīng)的補(bǔ)償事務(wù)，當(dāng)某個(gè)短事務(wù)失敗時(shí)，執(zhí)行前面所有成功的短事務(wù)的補(bǔ)償事務(wù)，以保證數(shù)據(jù)的最終一致性。

TCC案例代碼（偽代碼）：

// 訂單服務(wù)
public interface OrderTCCService {
    // Try階段：創(chuàng)建訂單，預(yù)留庫存
    boolean tryCreateOrder(OrderDTO orderDTO);
    // Confirm階段：確認(rèn)創(chuàng)建訂單
    boolean confirmCreateOrder(OrderDTO orderDTO);
    // Cancel階段：取消創(chuàng)建訂單，釋放庫存
    boolean cancelCreateOrder(OrderDTO orderDTO);
}
// 庫存服務(wù)
public interface InventoryTCCService {
    // Try階段：扣減庫存預(yù)留
    boolean tryDeductInventory(InventoryDTO inventoryDTO);
    // Confirm階段：確認(rèn)扣減庫存
    boolean confirmDeductInventory(InventoryDTO inventoryDTO);
    // Cancel階段：取消扣減庫存，恢復(fù)庫存
    boolean cancelDeductInventory(InventoryDTO inventoryDTO);
}

五、分庫分表的最佳實(shí)踐

5.1 合理選擇分片策略

• 根據(jù)業(yè)務(wù)特點(diǎn)選擇合適的分片策略，如訂單表可以按照時(shí)間范圍分片，方便查詢歷史數(shù)據(jù)；用戶表可以按照用戶ID哈希分片，使數(shù)據(jù)分布均勻。
• 避免過度分片，分片數(shù)量過多會(huì)增加管理復(fù)雜度和跨庫查詢的開銷。

5.2 做好數(shù)據(jù)備份與恢復(fù)

分庫分表后的數(shù)據(jù)分布在多個(gè)庫和表中，需要制定完善的數(shù)據(jù)備份策略，定期備份數(shù)據(jù)，并確保備份數(shù)據(jù)可以正常恢復(fù)。

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

• 對(duì)分庫分表后的數(shù)據(jù)庫進(jìn)行實(shí)時(shí)監(jiān)控，包括各庫表的性能指標(biāo)（如查詢響應(yīng)時(shí)間、吞吐量、連接數(shù)等）、數(shù)據(jù)增長情況等。
• 根據(jù)監(jiān)控結(jié)果進(jìn)行調(diào)優(yōu)，如調(diào)整分片規(guī)則、優(yōu)化SQL語句、增加硬件資源等。

5.4 考慮未來擴(kuò)展性

在設(shè)計(jì)分庫分表方案時(shí)，要考慮未來業(yè)務(wù)的增長，預(yù)留一定的擴(kuò)展空間，使方案能夠方便地進(jìn)行擴(kuò)容和調(diào)整。例如，采用可擴(kuò)展的分片規(guī)則，當(dāng)數(shù)據(jù)量增長到一定程度時(shí)，可以方便地增加新的分庫分表。

到此這篇關(guān)于MySQL分庫分表的實(shí)踐與挑戰(zhàn)的文章就介紹到這了,更多相關(guān)mysql分庫分表內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論