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

一、分庫分表的必要性

建網(wǎng)站原本是網(wǎng)站策劃師、網(wǎng)絡(luò)程序員、網(wǎng)頁設(shè)計(jì)師等，應(yīng)用各種網(wǎng)絡(luò)程序開發(fā)技術(shù)和網(wǎng)頁設(shè)計(jì)技術(shù)配合操作的協(xié)同工作。創(chuàng)新互聯(lián)建站專業(yè)提供成都網(wǎng)站設(shè)計(jì)、網(wǎng)站建設(shè)、外貿(mào)網(wǎng)站建設(shè),網(wǎng)頁設(shè)計(jì),網(wǎng)站制作(企業(yè)站、響應(yīng)式網(wǎng)站建設(shè)、電商門戶網(wǎng)站)等服務(wù),從網(wǎng)站深度策劃、搜索引擎友好度優(yōu)化到用戶體驗(yàn)的提升,我們力求做到極致!

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

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

提示：如場景無必要，千萬不要使用分庫分表。

二、分庫分表的思路

1、垂直區(qū)分

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

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

2、水平區(qū)分

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

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

以上一般來說，垂直分庫和水平分表用的會多些。

三、分庫分表的原理分析

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

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

1、Hash取模方案

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

2、Range范圍方案

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

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

1、技術(shù)選型

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

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

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

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

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

2、中間件

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

（1）Proxy模式

（2）Client模式

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

（3）中間件的比較

由于Client模式少了一層，運(yùn)維方便，相對來說容易些。

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

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

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

1、引入maven依賴

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

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

3、創(chuàng)建DataSource

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

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

mysql分庫分表一般有如下場景

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

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

這里我們以分KV表水平拆分為場景

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

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

分表后表名為kv_000 ~ kv_511

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

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

依次類推!

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

場景:以下我們基于博客文章表分庫場景來分析

目標(biāo):

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

按照user_id sharding

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

user_id % 1024 = 1 分到db_001庫

user_id % 1024 = 2 分到db_002庫

依次類推

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

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

對于水平分表/分庫后,非shardingKey查詢首先得考慮到

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

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

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

測試結(jié)果如下

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

對于分庫:

原始sql:

目標(biāo)sql:

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

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

@shardingBy 分片標(biāo)識

@DB 定義邏輯表名 庫名以及分片策略

1)編寫entity

Insert

select

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

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

目錄如下:

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

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

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

例如:

mysql 分表分頁查詢解決思路

當(dāng)業(yè)務(wù)上按照月份分表，但是前端h5需要分頁展示，小伙伴們不知有沒有遇到這個(gè)這個(gè)需求最后怎么完成的。

我這里想了一個(gè)解決思路，可能還不完善，希望能拋轉(zhuǎn)引玉。

1、入?yún)ageNo 為頁號碼，如果為1那么就是第一頁。pageSize 可以是入?yún)⒁部啥ㄋ?，這里定死10條。Limit 是數(shù)據(jù)偏移標(biāo)記，根據(jù)入?yún)ageNo 計(jì)算出來的，Limit=(pageNo-1)*pageSize。假設(shè)A表有41條數(shù)據(jù)符合，B表有51條數(shù)據(jù)符合，如下圖。

有幾種種情況? ?1.如果limitA表41條 那么需要從A表中取數(shù)據(jù)，（1）如果Limit+pageSizeCount直接獲取數(shù)據(jù)即可（2）如果Limit+pageSizeCount,那么需要從A 表取部分?jǐn)?shù)據(jù)從B表取一部分?jǐn)?shù)據(jù)。

1.如果limitA表41條? 那么需要從B表取數(shù)據(jù)，如果B數(shù)據(jù)依然不足，那么重復(fù)以上的步驟。如下圖

mysql水平分表的幾種方法

1.按時(shí)間分表

這種分表方式有一定的局限性，當(dāng)數(shù)據(jù)有較強(qiáng)的實(shí)效性，如微博發(fā)送記錄、微信消息記錄等，這種數(shù)據(jù)很少有用戶會查詢幾個(gè)月前的數(shù)據(jù)，如就可以按月分表。

2.按區(qū)間范圍分表

一般在有嚴(yán)格的自增id需求上，如按照user_id水平分表：

table_1 ?user_id從1~100w?

table_2 ?user_id從101~200w?

table_3 ?user_id從201~300w?

...?

3.hash分表

通過一個(gè)原始目標(biāo)的ID或者名稱通過一定的hash算法計(jì)算出數(shù)據(jù)存儲表的表名，然后訪問相應(yīng)的表。

按如下分10張表：

function?get_hash_table($table,?$userid)

{

$str?=?crc32($userid);

if?($str??0)?{

$hash?=?"0"?.?substr(abs($str),?0,?1);

}?else?{

$hash?=?substr($str,?0,?2);

}

return?$table?.?"_"?.?$hash;

}

echo get_hash_table('message',?'user18991');?//結(jié)果為message_10

echo get_hash_table('message',?'user34523');?//結(jié)果為message_13

分享文章：mysql分表怎么使用,mysql分表語句
網(wǎng)頁路徑：http://jinyejixie.com/article38/hsiipp.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站制作、外貿(mào)網(wǎng)站建設(shè)、域名注冊、做網(wǎng)站、搜索引擎優(yōu)化、商城網(wǎng)站

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源： 創(chuàng)新互聯(lián)