將所有數(shù)據(jù)都遷移到mycat中，一共有4個數(shù)據(jù)庫，blog01，blog02，blog_article01，blog_article02。
創(chuàng)新互聯(lián)堅持“要么做到，要么別承諾”的工作理念，服務(wù)領(lǐng)域包括：成都網(wǎng)站制作、網(wǎng)站設(shè)計、外貿(mào)網(wǎng)站建設(shè)、企業(yè)官網(wǎng)、英文網(wǎng)站、手機端網(wǎng)站、網(wǎng)站推廣等服務(wù)，滿足客戶于互聯(lián)網(wǎng)時代的鹽山網(wǎng)站設(shè)計、移動媒體設(shè)計的需求，幫助企業(yè)找到有效的互聯(lián)網(wǎng)解決方案。努力成為您成熟可靠的網(wǎng)絡(luò)建設(shè)合作伙伴！
article，article_tags分別在blog_article01，blog_article02，按照uid進(jìn)行水平拆分。
user_info表在blog01，link，category，tag在blog02數(shù)據(jù)庫中。
1.按時間分表
這種分表方式有一定的局限性，當(dāng)數(shù)據(jù)有較強的實效性，如微博發(fā)送記錄、微信消息記錄等，這種數(shù)據(jù)很少有用戶會查詢幾個月前的數(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分表
通過一個原始目標(biāo)的ID或者名稱通過一定的hash算法計算出數(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
7.1 范圍分片(range)
比如說t3表
(1)行數(shù)非常多，2000w（1-1000w:sh1 1000w01-2000w:sh2）
(2)訪問非常頻繁，用戶順序訪問較多
cp schema.xml schema.xml.1
vim schema.xml
添加：
table name="t3" dataNode="sh1,sh2" rule="auto-sharding-long" /
vim rule.xml
tableRule name="auto-sharding-long"
rule
columnsid/columns
algorithmrang-long/algorithm
/rule
function name="rang-long"
class="io.mycat.route.function.AutoPartitionByLong"
property name="mapFile"autopartition-long.txt/property
/function
vim autopartition-long.txt
0-10=0
10-20=1
[root@db01 conf]# mysql -S /data/3308/mysql.sock -e "select * from taobao.t3"
[root@db01 conf]# mysql -S /data/3307/mysql.sock -e "select * from taobao.t3"
7.2 取模分片
1%3 1
2%3 2
3%3 0
4%3 1
5%3 2
任何正整數(shù)數(shù)字和N（正整數(shù)）取模，得的值永遠(yuǎn)都是 0~N-1
id % 分片數(shù)量取模
N % 5 = 0-4 idx
取余分片方式：分片鍵（一個列）與節(jié)點數(shù)量進(jìn)行取余，得到余數(shù)，將數(shù)據(jù)寫入對應(yīng)節(jié)點
vim schema.xml
table name="t4" dataNode="sh1,sh2" rule="mod-long" /
vim rule.xml
property name="count"2/property
創(chuàng)建測試表：
mysql -S /data/3307/mysql.sock -e "use taobao;create table t4 (id int not null primary key auto_increment,name varchar(20) not null);"
mysql -S /data/3308/mysql.sock -e "use taobao;create table t4 (id int not null primary key auto_increment,name varchar(20) not null);"
重啟mycat
mycat restart
[root@db01 ~]# mysql -uroot -p123456 -h 10.0.0.52 -P8066 --default-auth=mysql_native_password
use TESTDB
insert into t4(id,name) values(1,'a');
insert into t4(id,name) values(2,'b');
insert into t4(id,name) values(3,'c');
insert into t4(id,name) values(4,'d');
insert into t4(id,name) values(6,'x'),(8,'y'),(10,'z');
分別登錄后端節(jié)點查詢數(shù)據(jù)
mysql -S /data/3308/mysql.sock -e "select * from taobao.t4"
mysql -S /data/3307/mysql.sock -e "select * from taobao.t4"
7.3 枚舉分片 (區(qū)域、zone)
t5 表
id name telnum
1 bj 1212
2 sh 22222
3 bj 3333
4 sh 44444
5 bj 5555
sharding-by-intfile
vim schema.xml
table name="t5" dataNode="sh1,sh2" rule="sharding-by-intfile" /
vim rule.xml
function name="hash-int" class="org.opencloudb.route.function.PartitionByFileMap"
property name="mapFile"partition-hash-int.txt/property
property name="type"1/property
/function
vim partition-hash-int.txt 配置：
bj=0
sh=1
DEFAULT_NODE=1
mysql -S /data/3307/mysql.sock -e "use taobao;create table t5 (id int not null primary key auto_increment,name varchar(20) not null);"
mysql -S /data/3308/mysql.sock -e "use taobao;create table t5 (id int not null primary key auto_increment,name varchar(20) not null);"
重啟mycat
mycat restart
mysql -uroot -p123456 -h10.0.0.52 -P8066 --default-auth=mysql_native_password
use TESTDB
insert into t5(id,name) values(1,'bj');
insert into t5(id,name) values(2,'sh');
insert into t5(id,name) values(3,'bj');
insert into t5(id,name) values(4,'sh');
insert into t5(id,name) values(5,'tj');
mysql -S /data/3308/mysql.sock -e "select * from taobao.t5"
mysql -S /data/3307/mysql.sock -e "select * from taobao.t5"
7.4 Mycat全局表
a b c d .....
join
t
a
id name age
1 zs 18 sh1
id addr aid
1001 bj 1
1002 sh 2
2 ls 19 sh2
id addr aid
1001 bj 1
1002 sh 2
t
id addr aid
1001 bj 1
1002 sh 2
使用場景：
如果你的業(yè)務(wù)中有些數(shù)據(jù)類似于數(shù)據(jù)字典，比如配置文件的配置，
常用業(yè)務(wù)的配置或者數(shù)據(jù)量不大很少變動的表，這些表往往不是特別大，
而且大部分的業(yè)務(wù)場景都會用到，那么這種表適合于Mycat全局表，無須對數(shù)據(jù)進(jìn)行切分，
要在所有的分片上保存一份數(shù)據(jù)即可，Mycat 在Join操作中，業(yè)務(wù)表與全局表進(jìn)行Join聚合會優(yōu)先選擇相同分片內(nèi)的全局表join，
避免跨庫Join，在進(jìn)行數(shù)據(jù)插入操作時，mycat將把數(shù)據(jù)分發(fā)到全局表對應(yīng)的所有分片執(zhí)行，在進(jìn)行數(shù)據(jù)讀取時候?qū)S機獲取一個節(jié)點讀取數(shù)據(jù)。
vim schema.xml
table name="t_area" primaryKey="id" type="global" dataNode="sh1,sh2" /
mysql -S /data/3307/mysql.sock -e "use taobao;create table t_area (id int not null primary key auto_increment,name varchar(20) not null);"
mysql -S /data/3308/mysql.sock -e "use taobao;create table t_area (id int not null primary key auto_increment,name varchar(20) not null);"
重啟mycat
mycat restart
mysql -uroot -p123456 -h10.0.0.52 -P8066 --default-auth=mysql_native_password
mysql -S /data/3308/mysql.sock -e "select * from taobao.t_area"
mysql -S /data/3307/mysql.sock -e "select * from taobao.t_area"
7.5 E-R分片
a
join
b
on a.xx =b.yy
a
id name
1 a sh1
3 c
2 b sh2
4 d
b
id addr aid
1001 bj 1 sh1
1002 sh 2
1003 tj 3 sh2
1004 wh 4
為了防止跨分片join，可以使用E-R模式
table name="a" dataNode="sh1,sh2" rule="mod-long"
childTable name="b" joinKey="aid" parentKey="id" /
/table
select * from a join b on a.id = b.aid
例子：
mysql -S /data/3308/mysql.sock -e "use taobao;create table a (id int not null primary key auto_increment,name varchar(20) not null);"
mysql -S /data/3308/mysql.sock -e "use taobao;create table b (id int not null primary key auto_increment,addr varchar(20) not null ,aid int );"
insert into b(id,addr,aid) values(1001,'bj',1);
insert into b(id,addr,aid) values(1002,'sj',3);
insert into b(id,addr,aid) values(1003,'sd',4);
insert into b(id,addr,aid) values(1004,'we',2);
insert into b(id,addr,aid) values(1005,'er',5);
========
后端數(shù)據(jù)節(jié)點數(shù)據(jù)分布：
mysql -S /data/3307/mysql.sock -e "select * from taobao.a"
mysql -S /data/3307/mysql.sock -e "select * from taobao.b"
mysql -S /data/3308/mysql.sock -e "select * from taobao.a"
mysql -S /data/3308/mysql.sock -e "select * from taobao.b"
一般面對幾千萬級的數(shù)據(jù)，剛開始可能都是把數(shù)據(jù)放在MySQL的一個單庫單表里，但往往這么大量級的數(shù)據(jù)到后期，數(shù)據(jù)庫查詢速度就很慢，因為數(shù)據(jù)量級太大，導(dǎo)致表的索引很大，樹的層級很高，進(jìn)而導(dǎo)致搜索性能下降，而且能放內(nèi)存緩存的數(shù)據(jù)頁是比較少。
所以推薦MySQL單表數(shù)據(jù)量不超過1000w，最好是在500w內(nèi)，若能控制在100萬以內(nèi)，那是 最佳選擇，基本單表100萬以內(nèi)的數(shù)據(jù)，性能上不會有太大的問題，前提是，只要你建好索引就行，其實保證MySQL高性能通常沒什么特別高深的技巧，就是控制數(shù)據(jù)量不要太大，另外就是保證你的查詢用上了索引就行。
針對該問題，就能分庫分表，可選擇將該用戶大表拆分為比如100張表，那么此時幾千萬 數(shù)據(jù)瞬間分散到100個表里去，類似
這樣的100個表，每個表也就幾十萬數(shù)據(jù)而已。
其次，可將這100個表分散到多臺數(shù)據(jù)庫服務(wù)器上去，此時要分散到幾臺服務(wù)器呢?你要考慮兩個點，一個是數(shù)據(jù)量有多少個GB/TB，一個是針對用戶中心的并發(fā)壓力有多高。
互聯(lián)網(wǎng)公司對用戶中心的壓力不會高到離譜，因為一般不會有很多人同時注冊/登錄或同時修改自己的個人信息，所以并發(fā)不是大問題。
數(shù)據(jù)量層面，一般1億行數(shù)據(jù)，大致在1~幾GB之間的范圍，和具體你一行數(shù)據(jù)有多少個字段也有關(guān)，所以說你幾千萬的用戶數(shù)據(jù)，往多了說也就幾個GB。這點數(shù)據(jù)量，對服務(wù)器存儲空間沒壓力。
綜上，此時你完全能給他分配兩臺數(shù)據(jù)庫服務(wù)器，放兩個庫，然后100張表均勻分散在2臺服務(wù)器。分的時候，注意指定一個字段來分，一般就指定userid, 根據(jù)用戶id進(jìn)行hash后，對表進(jìn)行取模，路由到一個表里去，這就能讓數(shù)據(jù)均勻分散。
至此搞定用戶表的分庫分表，只要給系統(tǒng)加上數(shù)據(jù)庫中間件技術(shù)，設(shè)置好路由規(guī)則，即可輕松對2個分庫上的100張表crud。平時針對某個用戶增刪改查,直接對他的userid進(jìn)行hash,然后對表取模，做個路由，就知道到哪個表里去找這個用戶的數(shù)據(jù)了。
但是這里可能會出現(xiàn)一些問題：用戶在登錄時，可能不是根據(jù)userid登錄，而是根據(jù)username、手機號，此時你就沒有userid了, 怎么知道去哪個表里找這個用戶的數(shù)據(jù)判斷是否能登錄? 對此。常規(guī)方案是建立一個索引映射表，搞個表結(jié)構(gòu)為 (username, userid)的索引映射 表，把username和userid一一映射， 然后針對username再做一次分庫分表，把這個索引映射表可拆分為比如100個表分散在兩臺服務(wù)器。然后用戶登錄時，就能根據(jù)username先去索引映射表里查找對應(yīng)userid，比如對username進(jìn)行hash，然后取模路由到一個表里去，找到username對應(yīng)的userid，接著根據(jù)userid進(jìn)行hash再取模，路由到按照userid分庫分表的一個表里，找到用戶的完整數(shù)據(jù)。但這種方式會把一次查詢轉(zhuǎn)化為兩個表的兩次查詢，先查索引映射表，再根據(jù)userid去查具體的數(shù)據(jù)，性能有損耗，不過有時為解決分庫分表問題，也只能用這種方案。
另外就是若公司運營團(tuán)隊里，有用戶管理模塊，需要對公司的用戶按照手機號、住址、年齡、性別、職業(yè)等各種條件搜索，這咋辦?
沒啥好辦法，基本上就是要對你的用戶數(shù)據(jù)表進(jìn)行binlog監(jiān)聽，把你要搜索的所有字段同步到ES，建立好搜索的索引。然后你的運營系統(tǒng)就可以通過ES進(jìn)行復(fù)雜的多條件搜索，ES是適合干這個事兒的，然后定位到一批userid，通過userid回到分庫分表環(huán)境里去找出具體的用戶數(shù)據(jù)，在頁面上展示出來即可。
這就是一套比較常規(guī)和完整的分庫分表的方案。
一條語句沒法實現(xiàn)的... create table table2 select * from table1 order by id limit 1,100 create table table3 select * from table1 order by id limit 100,100 create table table4 select * from table1 order by id limit 200,100
mysql分庫分表一般有如下場景
其中1，2相對較容易實現(xiàn),本文重點講講水平拆表和水平拆庫,以及基于mybatis插件方式實現(xiàn)水平拆分方案落地。
在 《聊一聊擴展字段設(shè)計》 一文中有講解到基于KV水平存儲擴展字段方案,這就是非常典型的可以水平分表的場景。主表和kv表是一對N關(guān)系,隨著主表數(shù)據(jù)量增長,KV表最大N倍線性增長。
這里我們以分KV表水平拆分為場景
對于kv擴展字段查詢,只會根據(jù)id + key 或者 id 為條件的方式查詢,所以這里我們可以按照id 分片即可
分512張表(實際場景具體分多少表還得根據(jù)字段增加的頻次而定)
分表后表名為kv_000 ~ kv_511
id % 512 = 1 .... 分到 kv_001,
id % 512 = 2 .... 分到 kv_002
依次類推!
水平分表相對比較容易,后面會講到基于mybatis插件實現(xiàn)方案
場景:以下我們基于博客文章表分庫場景來分析
目標(biāo):
表結(jié)構(gòu)如下(節(jié)選部分字段):
按照user_id sharding
假如分1024個庫,按照user_id % 1024 hash
user_id % 1024 = 1 分到db_001庫
user_id % 1024 = 2 分到db_002庫
依次類推
目前是2個節(jié)點,假如后期達(dá)到瓶頸,我們可以增加至4個節(jié)點
最多可以增加只1024個節(jié)點,性能線性增長
對于水平分表/分庫后,非shardingKey查詢首先得考慮到
基于mybatis分庫分表,一般常用的一種是基于spring AOP方式, 另外一種基于mybatis插件。其實兩種方式思路差不多。
為了比較直觀解決這個問題,我分別在Executor 和StatementHandler階段2個攔截器
實現(xiàn)動態(tài)數(shù)據(jù)源獲取接口
測試結(jié)果如下
由此可知,我們需要在Executor階段 切換數(shù)據(jù)源
對于分庫:
原始sql:
目標(biāo)sql:
其中定義了三個注解
@useMaster 是否強制讀主
@shardingBy 分片標(biāo)識
@DB 定義邏輯表名 庫名以及分片策略
1)編寫entity
Insert
select
以上順利實現(xiàn)mysql分庫,同樣的道理實現(xiàn)同時分庫分表也很容易實現(xiàn)。
此插件具體實現(xiàn)方案已開源:
目錄如下:
mysql分庫分表,首先得找到瓶頸在哪里(IO or CPU),是分庫還是分表,分多少？不能為了分庫分表而拆分。
原則上是盡量先垂直拆分 后 水平拆分。
以上基于mybatis插件分庫分表是一種實現(xiàn)思路,還有很多不完善的地方,
例如:
重慶分公司
重慶分公司
