腳本之家服務器常用軟件

快捷導航

Mysql分庫分表的項目實踐

更新時間：2025年06月25日 09:59:38 作者：程序猿轉(zhuǎn)行擺攤

本文介紹了垂直拆分和水平拆分策略及這兩種方式的優(yōu)缺點,實現(xiàn)Sharding-JDBC這一輕量級分庫分表中間件的使用,具有一定的參考價值,感興趣的可以了解一下

??前言

當前互聯(lián)網(wǎng)發(fā)展速度越來越快，很多應用的用戶量也越來越多，很多大的互聯(lián)網(wǎng)項目的用戶量甚至破億，日活躍用戶也在幾千萬，用戶的活動信息一般都記錄到了數(shù)據(jù)庫中，那么Mysql怎么存放這些數(shù)據(jù)才能有更好的性能呢？本文簡單講解了一下Mysql中的分庫分表方案。

??分庫分表簡介

??分庫分表是兩個方面的優(yōu)化：分庫，當表的數(shù)量很多導致數(shù)據(jù)系統(tǒng)的單個數(shù)據(jù)庫很大，這時候需要根據(jù)不同業(yè)務將表拆分到多個數(shù)據(jù)庫中；分表，當表中的數(shù)據(jù)太多的時候?qū)е聠蝹€表的太大，這時候需要將表中的數(shù)據(jù)拆分到多個表中。
所以分庫分表在實際操作中可以分為三種實現(xiàn)：分庫、分表、分庫分表。
??分庫分表主要實現(xiàn)在垂直拆分和水平拆分兩個方向。

??垂直拆分

垂直拆分又分為兩種情況：垂直分庫和垂直分表。

?? 垂直分庫
垂直分庫指的是內(nèi)部的表數(shù)量太多導致的單個庫的體積太過龐大，例如常見的電商系統(tǒng)中有用戶信息相關(guān)的表、商品信息相關(guān)的表、訂單信息相關(guān)的表等，這些表都存放在一個數(shù)據(jù)庫里整個數(shù)據(jù)庫太過臃腫龐大，所以要將表根據(jù)業(yè)務的分類拆分到多個數(shù)據(jù)庫中，同時項目也拆分成了分布式的項目。

?? 垂直分表
垂直分表指的是表中的字段過多導致的單個表的體積太過臃腫，常見的拆分方案：根據(jù)表中字段的訪問情況進行拆分，例如一張表30個字段，其中18個字段是核心字段，另外12個字段是非核心字段，這就可以將該表拆分成一個主表和一個擴展表；在前面講解innoDB引擎時也提到了一個特殊情況，表中含有較長的可變長度字段，這就需要將表中長可變長度字段拆分出一個表來提高主表的查詢效率。

??垂直拆分后系統(tǒng)數(shù)據(jù)庫業(yè)務分明比較清晰，單個Page可以存放更多的Row，查詢效率更快，但是表拆分之后單表操作變成了多表操作導致JOIN連表，同時會增加事務復雜度。

??水平拆分

水平拆分指的是單個表中數(shù)據(jù)量太多，導致查詢的時候速度變慢。例如電商平臺的日PV應該可以達到上億，這些數(shù)據(jù)如果記錄在單個表中那查詢效率可想而知了，這時候就可以根據(jù)情況拆分多個表。
水平拆分的方案需要根據(jù)具體的業(yè)務數(shù)據(jù)來進行處理，例如表中有不同分類的數(shù)據(jù)，可以將根據(jù)分類拆分多個表，或者根據(jù)時間信息拆分，咱的項目日志不就一般根據(jù)時間拆分的嗎？如果表中沒有可以拆分的依據(jù)可以通過ID取模來進行分表。

??水平拆分后單表的數(shù)據(jù)量變小了，查詢效率變快了，但是對于通過ID取模拆分的表來說擴容就變成了一個難點，需要把所有數(shù)據(jù)重新劃分多個表中；如果拆分規(guī)則不好的話可能需要跨表查詢甚至跨庫查詢導致查詢難度增加。

??分庫分表主鍵問題

以前數(shù)據(jù)庫中的ID可能是讓數(shù)據(jù)庫自增生成的，但是分庫分表后就不太適合了，多個表可能會出現(xiàn)主鍵重復的情況，下面介紹幾種主鍵生成策略。
?? UUID
對于UUID大家應該不陌生了，UUID隨機生成重復的概率極低，相信很多人都使用過UUID來作為表中的主鍵，但是UUID是沒有規(guī)律的，所以索引的B+樹的變動比較頻繁，而且主鍵長在輔助索引的樹中占用的空間就比較多。

?? COMB
COMB是UUID的一種改進方案，因為UUID沒有規(guī)律而且沒有順序?qū)е滤饕龢渥儎宇l繁，那么有沒有一種有序的UUID？所以大佬們就開發(fā)出了COMB。COMB是UUID和時間信息的結(jié)合體，這樣生成的ID就有順序了，索引樹的變動就可控了。因為有時間信息所以要保證多個服務器時間同步。

?? 雪花算法
雪花算法是一種Twitter開源的分布式ID生成算法，是long類型的ID，占用8字節(jié)的空間，由時間信息+機器信息+流水號組成。因為前面是時間信息所以保證了ID的有序性，同時占用空間要比前面兩種小。因為有時間信息所以要保證多個服務器時間同步。

在這里插入圖片描述

?? 第三方表生成ID
因為是多個表存放數(shù)據(jù)采用主鍵自增肯定會導致表中的ID出現(xiàn)重復的情況，那么可不可以使用另一個表專門生成ID呢？每次執(zhí)行都向表中插入數(shù)據(jù)獲取新的ID，這樣就保證了多個表的ID自增。這種方案理論上是可行的，但是在性能和可靠性上存在缺陷，因為每次都要進行一次數(shù)據(jù)庫網(wǎng)絡請求獲取ID，效率較低，而且第三方表的依賴性太強。

?? Redis生成ID
使用SQL數(shù)據(jù)庫生產(chǎn)自增ID的效率太低，那么咱是不是可以使用Redis自增來生成ID呢？可以讓Redis進行原子自增操作來獲取ID，這種方法性能較高，對Redis依賴性也比較強，但是咱項目使用Redis不可能讓Redis掛掉吧。Redis搭建集群后集群的步長需要調(diào)整，擴展性不太好。

關(guān)于這么多ID生成策略，個人比較喜歡雪花算法，因為其相較于UUID來說性能比較高，而對比第三方ID來說沒有這么強的依賴性，只要代碼能跑就能生成ID。

??分庫分表擴容

很多系統(tǒng)業(yè)務數(shù)據(jù)時時刻刻都在產(chǎn)生，所以數(shù)據(jù)庫的容量上限經(jīng)常會達到極限，這時候就需要對數(shù)據(jù)庫進行擴容操作。
?? 停機擴容
停機擴容是指將項目停掉不對外服務了，這期間內(nèi)進行數(shù)據(jù)庫擴容操作，重新對數(shù)據(jù)進行分配，結(jié)束后再啟動服務。停機擴容一般常見于金融項目，例如我目前的金融賬戶使用的華寶證券的，它們每周末非交易日的時候就會發(fā)布停機公告，對于部分業(yè)務停止服務，當然有可能是數(shù)據(jù)庫擴容也可能是其他信息維護。

停機擴容實現(xiàn)起來比較簡單，但是需要定期進行處理，而且處理完之后需要嚴格的測試。同時停機擴容的高可用咱就不說了，預計雙九都不到。

?? 平滑擴容
對于互聯(lián)網(wǎng)電商肯定不能使用停機擴容，停機之后相關(guān)業(yè)務停止得丟失多少用戶，所以他們一般采用平滑擴容方案。平滑擴容一般采用的是二倍擴容。
平滑擴容的過程大致是這樣的：

1??目前的服務器數(shù)據(jù)庫是有User1和User2兩個數(shù)據(jù)庫的，User1中為id取模0的數(shù)據(jù)，User2中是ID取模1的數(shù)據(jù)。

在這里插入圖片描述

2??增加一倍數(shù)據(jù)庫，設(shè)置雙主單寫模式，將User1的數(shù)據(jù)復制一份給User3，將User2的數(shù)據(jù)復制一份給User4。

在這里插入圖片描述

3??數(shù)據(jù)同步完成，此時的數(shù)據(jù)情況是這樣的User1和User3存放的都是ID取模0的，User2和User4都是取模為1的數(shù)據(jù)，這時候?qū)㈦p主單寫修改為雙主雙寫，保證數(shù)據(jù)的準確性。

在這里插入圖片描述

4??四臺數(shù)據(jù)庫的需要對4取模，將User1中取模2的刪除，將User2中取模3的刪除，將User3中取模0的刪除，將User4中取模1的刪除。然后關(guān)掉雙主模式，修改數(shù)據(jù)庫配置重啟數(shù)據(jù)庫。

在這里插入圖片描述

平滑擴容停機時間非常短，僅僅是數(shù)據(jù)庫重啟的時間，保證了高可用，但是實現(xiàn)起來比較費勁，而且越到后期的擴容越費勁。

??Sharding-JDBC

分庫分表的中間件有很多，根據(jù)其使用位置分為代理層的Mycat、Sharding-Proxy和MySQL Proxy，代理層分庫分表是通過中間件來配置數(shù)據(jù)庫，代碼不用改動，訪問的數(shù)據(jù)庫的時候代理已經(jīng)給處理好了；還有應用層的中間件Sharding-JDBC，個人用Sharding-JDBC比較多，所以就用Sharding-JDBC來演示一下分庫分表操作了。
本次演示使用的是SpringbootTest+mybatis-plus+sharding-JDBC。
使用Sharding-JDBC后我們的業(yè)務代碼該怎么寫還是怎么寫，只需要在yml配置文件中增加一個Sharding配置文件，配置好那些表進行分庫分表以及分庫分表的策略就行了。

??Sharding-JDBC簡介

Sharding-JDBC是一個輕量級的數(shù)據(jù)操作中間件，使用Java語言編寫的，可以理解為Sharding-JDBC在JDBC操作的上面又加了一層服務，根據(jù)其配置修改JDBC的執(zhí)行達到數(shù)據(jù)控制效果。

?? Sharding-JDBC中的一些相關(guān)的概念

真實表： 真實表就是在數(shù)據(jù)庫中真實存在的表，例如user1,user2,user3。
邏輯表： 邏輯表就像是真是表構(gòu)成的假表，例如user1,user2,user3，我們在使用的時候用的是user邏輯表。
數(shù)據(jù)節(jié)點： 數(shù)據(jù)節(jié)點是數(shù)據(jù)庫和表構(gòu)成的組合，例如database0.user1。
綁定表： 綁定表指的是兩個有關(guān)聯(lián)的表，例如user和user_extend表，查詢的時候SELECT * FROM user a LEFT JOIN user_extend b on (a.user_id = b.user_id)。用戶表和它的擴展表就是一個綁定表組合，合起來才是一個完整用戶數(shù)據(jù)。
廣播表： 廣播表指的是一些字典表，為了查詢這些表每個數(shù)據(jù)庫都存在而且數(shù)據(jù)也是相同的。

?? Sharding-JDBC的分片策略

標準分片策略StandardShardingStrategy：只支持單分片鍵，也就是只能通過一個字段來分片?？梢酝ㄟ^SQL語句中的=、>、< 、in等操作來進行分片。支持精準分片算法和范圍分片算法。
復合分片策略ComplexShardingStrategy：支持多分片鍵，也就是可以通過多個字段來進行分片，這個需要自己實現(xiàn)。
行表達式分片策略InlineShardingStrategy：只支持但分片鍵，通過Groovy表達式來分片，例如：user_${id%2}這個語句跟前端拼接字符串很像，意思是id對2取模然后拼接前面的字符串user_來識別區(qū)分不同的分片。
Hint分片策略HintShardingStrategy： Hint分片是通過表中字段之外的值進行分片。

?? Sharding-JDBC注意事項
Sharding-JDBC不支持持CASE WHEN、HAVING、UNION操作，如果在使用的時候SQL中寫了這些操作可能會不識別導致出錯。
Sharding-JDBC的嵌套子查詢只支持一層，如果有子查詢中嵌套子查詢的可能會報錯。
使用Sharding-JDBC的時候就不要再在SQL中指定數(shù)據(jù)了(例如select * from db1.user)，否則可能會報錯。
使用Sharding-JDBC的時候不要對分片鍵做處理(例如使用type字段做分片禁止對type做計算操作)，否則可能會出錯。

??使用Sharding-JDBC分庫

?? 引入Sharding-JDBC的Jar包

<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>4.1.0</version>
</dependency>

?? properties配置

#配置兩個數(shù)據(jù)源
spring.shardingsphere.datasource.names=ds0,ds1
#數(shù)據(jù)源1
spring.shardingsphere.datasource.ds1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds1.jdbc-url=jdbc:mysql://82.157.173.148:3306/test002?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8&useSSL=false
spring.shardingsphere.datasource.ds1.username=root
spring.shardingsphere.datasource.ds1.password=******
#數(shù)據(jù)源2
spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds0.jdbc-url=jdbc:mysql://82.156.90.65:3306/test001?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8&useSSL=false
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=******
#分庫策略，通過ID取模進行分庫
spring.shardingsphere.sharding.tables.user.database-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.user.database-strategy.inline.algorithm-expression=ds$->{id % 2}
#SQL結(jié)果打印
spring.shardingsphere.props.sql.show=true
spring.main.allow-bean-definition-overriding=true

?? 測試代碼
以前怎么寫的業(yè)務代碼還是怎么寫，sharding會自動的給分配到對應的數(shù)據(jù)庫。

@RunWith(SpringRunner.class)
@SpringBootTest(classes = ShardingJdbcDemoApplication.class)
public class Test001 {
    @Resource
    private UserMapper userMapper;

    @Test
    public void test() {
        for (int i = 0; i < 10; i++) {
            User user = new User();
            user.setId(i);
            user.setName("張三");
            user.setAge(18);
            userMapper.insert(user);
        }
    }
}

?? 打印日志
可以看到在寫入的時候會根據(jù)ID取模自動匹配對應的數(shù)據(jù)庫中進行保存，一個數(shù)據(jù)庫中只保存了奇數(shù)ID另一個數(shù)據(jù)庫值保存了偶數(shù)ID。

在這里插入圖片描述

?? 讀取數(shù)據(jù)操作的原理是在兩個庫中都執(zhí)行SQL語句，將最終的結(jié)果加在一起。

??使用Sharding-JDBC分表

?? properties配置
咱就不同時分庫又分表了，看一下一個庫中分表，

#分庫策略，通過ID取模進行分庫
spring.shardingsphere.sharding.tables.user.database-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.user.database-strategy.inline.algorithm-expression=ds0
#數(shù)據(jù)節(jié)點配置
spring.shardingsphere.sharding.tables.user.actual-data-nodes=ds0.user_$->{0..1}
#分表策略,通過ID取模進行分表
spring.shardingsphere.sharding.tables.user.table-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.user.table-strategy.inline.algorithm-expression=user_$->{id % 2}

測試代碼還是那個插入操作，直接看執(zhí)行結(jié)果吧，會根據(jù)ID取模自動匹配對應的表。

?? 打印日志

在這里插入圖片描述

??使用Sharding-JDBC讀寫分離

?? properties配置

#主數(shù)據(jù)庫配置
spring.shardingsphere.masterslave.name=datasource
spring.shardingsphere.masterslave.master-data-source-name=ds0
#從數(shù)據(jù)庫配置，可以是多個采用負載均衡策略
spring.shardingsphere.masterslave.slave-data-source-names=ds1
spring.shardingsphere.masterslave.load-balance-algorithm-type=ROUND_ROBIN
#使用雪花算法生成ID
spring.shardingsphere.sharding.tables.user.key-generator.column=id
spring.shardingsphere.sharding.tables.user.key-generator.type=SNOWFLAKE

?? 打印日志
可以看到執(zhí)行插入操作使用的是database0，執(zhí)行查詢操作使用的是database1。

在這里插入圖片描述