快捷導(dǎo)航

Mysql分庫(kù)分表的項(xiàng)目實(shí)踐

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

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

??前言

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

??分庫(kù)分表簡(jiǎn)介

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

??垂直拆分

垂直拆分又分為兩種情況：垂直分庫(kù)和垂直分表。

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

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

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

??水平拆分

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

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

??分庫(kù)分表主鍵問(wèn)題

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

?? COMB
COMB是UUID的一種改進(jìn)方案，因?yàn)閁UID沒(méi)有規(guī)律而且沒(méi)有順序?qū)е滤饕龢?shù)變動(dòng)頻繁，那么有沒(méi)有一種有序的UUID？所以大佬們就開(kāi)發(fā)出了COMB。COMB是UUID和時(shí)間信息的結(jié)合體，這樣生成的ID就有順序了，索引樹(shù)的變動(dòng)就可控了。因?yàn)橛袝r(shí)間信息所以要保證多個(gè)服務(wù)器時(shí)間同步。

?? 雪花算法
雪花算法是一種Twitter開(kāi)源的分布式ID生成算法，是long類型的ID，占用8字節(jié)的空間，由時(shí)間信息+機(jī)器信息+流水號(hào)組成。因?yàn)榍懊媸菚r(shí)間信息所以保證了ID的有序性，同時(shí)占用空間要比前面兩種小。因?yàn)橛袝r(shí)間信息所以要保證多個(gè)服務(wù)器時(shí)間同步。

在這里插入圖片描述

?? 第三方表生成ID
因?yàn)槭嵌鄠€(gè)表存放數(shù)據(jù)采用主鍵自增肯定會(huì)導(dǎo)致表中的ID出現(xiàn)重復(fù)的情況，那么可不可以使用另一個(gè)表專門(mén)生成ID呢？每次執(zhí)行都向表中插入數(shù)據(jù)獲取新的ID，這樣就保證了多個(gè)表的ID自增。這種方案理論上是可行的，但是在性能和可靠性上存在缺陷，因?yàn)槊看味家M(jìn)行一次數(shù)據(jù)庫(kù)網(wǎng)絡(luò)請(qǐng)求獲取ID，效率較低，而且第三方表的依賴性太強(qiáng)。

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

關(guān)于這么多ID生成策略，個(gè)人比較喜歡雪花算法，因?yàn)槠湎噍^于UUID來(lái)說(shuō)性能比較高，而對(duì)比第三方ID來(lái)說(shuō)沒(méi)有這么強(qiáng)的依賴性，只要代碼能跑就能生成ID。

??分庫(kù)分表擴(kuò)容

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

停機(jī)擴(kuò)容實(shí)現(xiàn)起來(lái)比較簡(jiǎn)單，但是需要定期進(jìn)行處理，而且處理完之后需要嚴(yán)格的測(cè)試。同時(shí)停機(jī)擴(kuò)容的高可用咱就不說(shuō)了，預(yù)計(jì)雙九都不到。

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

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

在這里插入圖片描述

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

在這里插入圖片描述

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

在這里插入圖片描述

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

在這里插入圖片描述

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

??Sharding-JDBC

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

??Sharding-JDBC簡(jiǎn)介

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

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

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

?? Sharding-JDBC的分片策略

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

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

??使用Sharding-JDBC分庫(kù)

?? 引入Sharding-JDBC的Jar包

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

?? properties配置

#配置兩個(gè)數(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=******
#分庫(kù)策略，通過(guò)ID取模進(jìn)行分庫(kù)
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

?? 測(cè)試代碼
以前怎么寫(xiě)的業(yè)務(wù)代碼還是怎么寫(xiě)，sharding會(huì)自動(dòng)的給分配到對(duì)應(yīng)的數(shù)據(jù)庫(kù)。

@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);
        }
    }
}

?? 打印日志
可以看到在寫(xiě)入的時(shí)候會(huì)根據(jù)ID取模自動(dòng)匹配對(duì)應(yīng)的數(shù)據(jù)庫(kù)中進(jìn)行保存，一個(gè)數(shù)據(jù)庫(kù)中只保存了奇數(shù)ID另一個(gè)數(shù)據(jù)庫(kù)值保存了偶數(shù)ID。

在這里插入圖片描述

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

??使用Sharding-JDBC分表

?? properties配置
咱就不同時(shí)分庫(kù)又分表了，看一下一個(gè)庫(kù)中分表，

#分庫(kù)策略，通過(guò)ID取模進(jìn)行分庫(kù)
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é)點(diǎn)配置
spring.shardingsphere.sharding.tables.user.actual-data-nodes=ds0.user_$->{0..1}
#分表策略,通過(guò)ID取模進(jìn)行分表
spring.shardingsphere.sharding.tables.user.table-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.user.table-strategy.inline.algorithm-expression=user_$->{id % 2}

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

?? 打印日志

在這里插入圖片描述

??使用Sharding-JDBC讀寫(xiě)分離

?? properties配置

#主數(shù)據(jù)庫(kù)配置
spring.shardingsphere.masterslave.name=datasource
spring.shardingsphere.masterslave.master-data-source-name=ds0
#從數(shù)據(jù)庫(kù)配置，可以是多個(gè)采用負(fù)載均衡策略
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。

在這里插入圖片描述