淺談mysql數(shù)據(jù)庫(kù)分庫(kù)分表那些事-億級(jí)數(shù)據(jù)存儲(chǔ)方案

mysql分庫(kù)分表一般有如下場(chǎng)景

創(chuàng)新互聯(lián)公司專業(yè)為企業(yè)提供山丹網(wǎng)站建設(shè)、山丹做網(wǎng)站、山丹網(wǎng)站設(shè)計(jì)、山丹網(wǎng)站制作等企業(yè)網(wǎng)站建設(shè)、網(wǎng)頁(yè)設(shè)計(jì)與制作、山丹企業(yè)網(wǎng)站模板建站服務(wù)，十余年山丹做網(wǎng)站經(jīng)驗(yàn)，不只是建網(wǎng)站，更提供有價(jià)值的思路和整體網(wǎng)絡(luò)服務(wù)。

其中1，2相對(duì)較容易實(shí)現(xiàn),本文重點(diǎn)講講水平拆表和水平拆庫(kù),以及基于mybatis插件方式實(shí)現(xiàn)水平拆分方案落地。

在 《聊一聊擴(kuò)展字段設(shè)計(jì)》 一文中有講解到基于KV水平存儲(chǔ)擴(kuò)展字段方案,這就是非常典型的可以水平分表的場(chǎng)景。主表和kv表是一對(duì)N關(guān)系,隨著主表數(shù)據(jù)量增長(zhǎng),KV表最大N倍線性增長(zhǎng)。

這里我們以分KV表水平拆分為場(chǎng)景

對(duì)于kv擴(kuò)展字段查詢,只會(huì)根據(jù)id + key 或者 id 為條件的方式查詢,所以這里我們可以按照id 分片即可

分512張表(實(shí)際場(chǎng)景具體分多少表還得根據(jù)字段增加的頻次而定)

分表后表名為kv_000 ~ kv_511

id % 512 = 1 .... 分到 kv_001,

id % 512 = 2 .... 分到 kv_002

依次類推!

水平分表相對(duì)比較容易,后面會(huì)講到基于mybatis插件實(shí)現(xiàn)方案

場(chǎng)景:以下我們基于博客文章表分庫(kù)場(chǎng)景來(lái)分析

目標(biāo):

表結(jié)構(gòu)如下(節(jié)選部分字段):

按照user_id sharding

假如分1024個(gè)庫(kù),按照user_id % 1024 hash

user_id % 1024 = 1 分到db_001庫(kù)

user_id % 1024 = 2 分到db_002庫(kù)

依次類推

目前是2個(gè)節(jié)點(diǎn),假如后期達(dá)到瓶頸,我們可以增加至4個(gè)節(jié)點(diǎn)

最多可以增加只1024個(gè)節(jié)點(diǎn),性能線性增長(zhǎng)

對(duì)于水平分表/分庫(kù)后,非shardingKey查詢首先得考慮到

基于mybatis分庫(kù)分表,一般常用的一種是基于spring AOP方式, 另外一種基于mybatis插件。其實(shí)兩種方式思路差不多。

為了比較直觀解決這個(gè)問(wèn)題,我分別在Executor 和StatementHandler階段2個(gè)攔截器

實(shí)現(xiàn)動(dòng)態(tài)數(shù)據(jù)源獲取接口

測(cè)試結(jié)果如下

由此可知,我們需要在Executor階段 切換數(shù)據(jù)源

對(duì)于分庫(kù):

原始sql:

目標(biāo)sql:

其中定義了三個(gè)注解

@useMaster 是否強(qiáng)制讀主

@shardingBy 分片標(biāo)識(shí)

@DB 定義邏輯表名 庫(kù)名以及分片策略

1)編寫(xiě)entity

Insert

select

以上順利實(shí)現(xiàn)mysql分庫(kù),同樣的道理實(shí)現(xiàn)同時(shí)分庫(kù)分表也很容易實(shí)現(xiàn)。

此插件具體實(shí)現(xiàn)方案已開(kāi)源:

目錄如下:

mysql分庫(kù)分表,首先得找到瓶頸在哪里(IO or CPU),是分庫(kù)還是分表,分多少？不能為了分庫(kù)分表而拆分。

原則上是盡量先垂直拆分 后 水平拆分。

以上基于mybatis插件分庫(kù)分表是一種實(shí)現(xiàn)思路,還有很多不完善的地方,

例如:

哪項(xiàng)服務(wù)實(shí)現(xiàn)了mysql分庫(kù)分表及讀寫(xiě)分離的業(yè)務(wù)功能

MyCat。MyCat是服務(wù)端的代理，使用MyCat實(shí)現(xiàn)了，整個(gè)分庫(kù)分表和讀寫(xiě)分離過(guò)程對(duì)，Java程序來(lái)說(shuō)是完全透明的。MySQL是一個(gè)關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng)，由瑞典MySQL AB公司開(kāi)發(fā)，屬于Oracle旗下產(chǎn)品。

mysql怎樣分庫(kù)？

1

基本思想之什么是分庫(kù)分表？

從字面上簡(jiǎn)單理解，就是把原本存儲(chǔ)于一個(gè)庫(kù)的數(shù)據(jù)分塊存儲(chǔ)到多個(gè)庫(kù)上，把原本存儲(chǔ)于一個(gè)表的數(shù)據(jù)分塊存儲(chǔ)到多個(gè)表上。

2

基本思想之為什么要分庫(kù)分表？

數(shù)據(jù)庫(kù)中的數(shù)據(jù)量不一定是可控的，在未進(jìn)行分庫(kù)分表的情況下，隨著時(shí)間和業(yè)務(wù)的發(fā)展，庫(kù)中的表會(huì)越來(lái)越多，表中的數(shù)據(jù)量也會(huì)越來(lái)越大，相應(yīng)地，數(shù)據(jù)操作，增刪改查的開(kāi)銷也會(huì)越來(lái)越大；另外，由于無(wú)法進(jìn)行分布式式部署，而一臺(tái)服務(wù)器的資源（cpu、磁盤(pán)、內(nèi)存、io等）是有限的，最終數(shù)據(jù)庫(kù)所能承載的數(shù)據(jù)量、數(shù)據(jù)處理能力都將遭遇瓶頸。

3

分庫(kù)分表的實(shí)施策略。

分庫(kù)分表有垂直切分和水平切分兩種。

3.1

何謂垂直切分，即將表按照功能模塊、關(guān)系密切程度劃分出來(lái)，部署到不同的庫(kù)上。例如，我們會(huì)建立定義數(shù)據(jù)庫(kù)workdb、商品數(shù)據(jù)庫(kù)paydb、用戶數(shù)據(jù)庫(kù)userdb、日志數(shù)據(jù)庫(kù)logdb等，分別用于存儲(chǔ)項(xiàng)目數(shù)據(jù)定義表、商品定義表、用戶數(shù)據(jù)表、日志數(shù)據(jù)表等。

3.2

何謂水平切分，當(dāng)一個(gè)表中的數(shù)據(jù)量過(guò)大時(shí)，我們可以把該表的數(shù)據(jù)按照某種規(guī)則，例如userid散列，進(jìn)行劃分，然后存儲(chǔ)到多個(gè)結(jié)構(gòu)相同的表，和不同的庫(kù)上。例如，我們的userdb中的用戶數(shù)據(jù)表中，每一個(gè)表的數(shù)據(jù)量都很大，就可以把userdb切分為結(jié)構(gòu)相同的多個(gè)userdb：part0db、part1db等，再將userdb上的用戶數(shù)據(jù)表usertable，切分為很多usertable：usertable0、usertable1等，然后將這些表按照一定的規(guī)則存儲(chǔ)到多個(gè)userdb上。

3.3

應(yīng)該使用哪一種方式來(lái)實(shí)施數(shù)據(jù)庫(kù)分庫(kù)分表，這要看數(shù)據(jù)庫(kù)中數(shù)據(jù)量的瓶頸所在，并綜合項(xiàng)目的業(yè)務(wù)類型進(jìn)行考慮。

如果數(shù)據(jù)庫(kù)是因?yàn)楸硖喽斐珊Ａ繑?shù)據(jù)，并且項(xiàng)目的各項(xiàng)業(yè)務(wù)邏輯劃分清晰、低耦合，那么規(guī)則簡(jiǎn)單明了、容易實(shí)施的垂直切分必是首選。

而如果數(shù)據(jù)庫(kù)中的表并不多，但單表的數(shù)據(jù)量很大、或數(shù)據(jù)熱度很高，這種情況之下就應(yīng)該選擇水平切分，水平切分比垂直切分要復(fù)雜一些，它將原本邏輯上屬于一體的數(shù)據(jù)進(jìn)行了物理分割，除了在分割時(shí)要對(duì)分割的粒度做好評(píng)估，考慮數(shù)據(jù)平均和負(fù)載平均，后期也將對(duì)項(xiàng)目人員及應(yīng)用程序產(chǎn)生額外的數(shù)據(jù)管理負(fù)擔(dān)。

在現(xiàn)實(shí)項(xiàng)目中，往往是這兩種情況兼而有之，這就需要做出權(quán)衡，甚至既需要垂直切分，又需要水平切分。我們的游戲項(xiàng)目便綜合使用了垂直與水平切分，我們首先對(duì)數(shù)據(jù)庫(kù)進(jìn)行垂直切分，然后，再針對(duì)一部分表，通常是用戶數(shù)據(jù)表，進(jìn)行水平切分。

4

分庫(kù)分表存在的問(wèn)題。

4.1

事務(wù)問(wèn)題。

在執(zhí)行分庫(kù)分表之后，由于數(shù)據(jù)存儲(chǔ)到了不同的庫(kù)上，數(shù)據(jù)庫(kù)事務(wù)管理出現(xiàn)了困難。如果依賴數(shù)據(jù)庫(kù)本身的分布式事務(wù)管理功能去執(zhí)行事務(wù)，將付出高昂的性能代價(jià)；如果由應(yīng)用程序去協(xié)助控制，形成程序邏輯上的事務(wù)，又會(huì)造成編程方面的負(fù)擔(dān)。

4.2

跨庫(kù)跨表的join問(wèn)題。

在執(zhí)行了分庫(kù)分表之后，難以避免會(huì)將原本邏輯關(guān)聯(lián)性很強(qiáng)的數(shù)據(jù)劃分到不同的表、不同的庫(kù)上，這時(shí)，表的關(guān)聯(lián)操作將受到限制，我們無(wú)法join位于不同分庫(kù)的表，也無(wú)法join分表粒度不同的表，結(jié)果原本一次查詢能夠完成的業(yè)務(wù)，可能需要多次查詢才能完成。

4.3

額外的數(shù)據(jù)管理負(fù)擔(dān)和數(shù)據(jù)運(yùn)算壓力。

額外的數(shù)據(jù)管理負(fù)擔(dān)，最顯而易見(jiàn)的就是數(shù)據(jù)的定位問(wèn)題和數(shù)據(jù)的增刪改查的重復(fù)執(zhí)行問(wèn)題，這些都可以通過(guò)應(yīng)用程序解決，但必然引起額外的邏輯運(yùn)算，例如，對(duì)于一個(gè)記錄用戶成績(jī)的用戶數(shù)據(jù)表usertable，業(yè)務(wù)要求查出成績(jī)最好的100位，在進(jìn)行分表之前，只需一個(gè)order

by語(yǔ)句就可以搞定，但是在進(jìn)行分表之后，將需要n個(gè)order

by語(yǔ)句，分別查出每一個(gè)分表的前100名用戶數(shù)據(jù)，然后再對(duì)這些數(shù)據(jù)進(jìn)行合并計(jì)算，才能得出結(jié)果。

上述整理于互聯(lián)網(wǎng)

分庫(kù)分表技術(shù)及技術(shù)方案

一、分庫(kù)分表的必要性

分庫(kù)分表技術(shù)的使用，主要是數(shù)據(jù)庫(kù)產(chǎn)生了瓶頸，如單庫(kù)的并發(fā)訪問(wèn)或單表的查詢都超出了閾值。對(duì)系統(tǒng)使用造成一定的影響，不得已而產(chǎn)生的技術(shù)。

通過(guò)分庫(kù)分表技術(shù)來(lái)解決此類問(wèn)題，但正因?yàn)槭褂么思夹g(shù)，會(huì)產(chǎn)生ACID一系列的問(wèn)題，各類中間件解決此類問(wèn)題各有各的優(yōu)勢(shì)。

提示：如場(chǎng)景無(wú)必要，千萬(wàn)不要使用分庫(kù)分表。

二、分庫(kù)分表的思路

1、垂直區(qū)分

垂直分庫(kù)：從業(yè)務(wù)角度，一個(gè)庫(kù)分成多個(gè)庫(kù)，如把訂單和用戶信息分成兩個(gè)庫(kù)來(lái)存儲(chǔ)。這樣的好處就是可以微服務(wù)了。每塊的業(yè)務(wù)單獨(dú)部署，互不影響，通過(guò)接口去調(diào)用。

垂直分表：把大表分成多個(gè)小表，如熱點(diǎn)數(shù)據(jù)和非熱點(diǎn)數(shù)據(jù)分開(kāi)，提高查詢速度。

2、水平區(qū)分

水平分表：同一業(yè)務(wù)如數(shù)據(jù)量大了以后，根據(jù)一定的規(guī)則分為不同的表進(jìn)行存儲(chǔ)。

水平分庫(kù)：如訂單分成多個(gè)庫(kù)存儲(chǔ)，分解服務(wù)器壓力。

以上一般來(lái)說(shuō)，垂直分庫(kù)和水平分表用的會(huì)多些。

三、分庫(kù)分表的原理分析

分庫(kù)分表常用的方案：Hash取模方案和range范圍方案；

路由算法為最主要的算法，指得是把路由的Key按照指定的算法進(jìn)行存放；

1、Hash取模方案

根據(jù)取余分配到不同的表里。要根據(jù)實(shí)際情況確認(rèn)模的大小。此方案由于平均分配，不存在熱點(diǎn)問(wèn)題，但數(shù)據(jù)遷移很復(fù)雜。

2、Range范圍方案

range根據(jù)范圍進(jìn)行劃分，如日期，大小。此方案不存在數(shù)據(jù)遷移，但存在熱點(diǎn)問(wèn)題。

四、分庫(kù)分表的技術(shù)選型

1、技術(shù)選型

解決方案主要分為4種：MySQL的分區(qū)技術(shù)、NoSql、NewSQL、MySQL的分庫(kù)分表。

（1）mysql分區(qū)技術(shù)：把一張表存放在不同存儲(chǔ)文件。由于無(wú)法負(fù)載，使用較少。

（2）NoSQL（如MongoDB）：如是訂單等比較重要數(shù)據(jù)，強(qiáng)關(guān)聯(lián)關(guān)系，需約束一致性，不太適應(yīng)。

（3）NewSql(具有NoSQL對(duì)海量數(shù)據(jù)的存儲(chǔ)管理能力，還保持了傳統(tǒng)數(shù)據(jù)庫(kù)支持ACID和SQL等特性):如TiDB可滿足需求。

（4）MySQL的分庫(kù)分表：如使用mysql,此種方案為主流方式。

2、中間件

解決此類問(wèn)題的中間件主要為：Proxy模式、Client模式。

（1）Proxy模式

（2）Client模式

把分庫(kù)分表相關(guān)邏輯存放在客戶端，一版客戶端的應(yīng)用會(huì)引用一個(gè)jar，然后再jar中處理SQL組合、數(shù)據(jù)庫(kù)路由、執(zhí)行結(jié)果合并等相關(guān)功能。

（3）中間件的比較

由于Client模式少了一層，運(yùn)維方便，相對(duì)來(lái)說(shuō)容易些。

五、分庫(kù)分表的實(shí)踐

根據(jù)容量（當(dāng)前容量和增長(zhǎng)量）評(píng)估分庫(kù)或分表個(gè)數(shù) - 選key（均勻）- 分表規(guī)則（hash或range等）- 執(zhí)行（一般雙寫(xiě)）- 擴(kuò)容問(wèn)題（盡量減少數(shù)據(jù)的移動(dòng)）。

在這里我們選用中間件share-jdbc。

1、引入maven依賴

2、spring boot規(guī)則配置

行表達(dá)式標(biāo)識(shí)符可以使用${...}或$-{...}，但前者與Spring本身的屬性文件占位符沖突，因此在Spring環(huán)境中使用行表達(dá)式標(biāo)識(shí)符建議使用$-{...}。

3、創(chuàng)建DataSource

通過(guò)ShardingDataSourceFactory工廠和規(guī)則配置對(duì)象獲取ShardingDataSource，ShardingDataSource實(shí)現(xiàn)自JDBC的標(biāo)準(zhǔn)接口DataSource。然后即可通過(guò)DataSource選擇使用原生JDBC開(kāi)發(fā)，或者使用JPA, MyBatis等ORM工具。