一條語(yǔ)句沒(méi)法實(shí)現(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
目前創(chuàng)新互聯(lián)建站已為上1000家的企業(yè)提供了網(wǎng)站建設(shè)、域名、雅安服務(wù)器托管、網(wǎng)站托管維護(hù)、企業(yè)網(wǎng)站設(shè)計(jì)、白朗網(wǎng)站維護(hù)等服務(wù)，公司將堅(jiān)持客戶導(dǎo)向、應(yīng)用為本的策略，正道將秉承"和諧、參與、激情"的文化，與客戶和合作伙伴齊心協(xié)力一起成長(zhǎng)，共同發(fā)展。
一般面對(duì)幾千萬(wàn)級(jí)的數(shù)據(jù)，剛開(kāi)始可能都是把數(shù)據(jù)放在MySQL的一個(gè)單庫(kù)單表里，但往往這么大量級(jí)的數(shù)據(jù)到后期，數(shù)據(jù)庫(kù)查詢速度就很慢，因?yàn)閿?shù)據(jù)量級(jí)太大，導(dǎo)致表的索引很大，樹(shù)的層級(jí)很高，進(jìn)而導(dǎo)致搜索性能下降，而且能放內(nèi)存緩存的數(shù)據(jù)頁(yè)是比較少。
所以推薦MySQL單表數(shù)據(jù)量不超過(guò)1000w，最好是在500w內(nèi)，若能控制在100萬(wàn)以內(nèi)，那是 最佳選擇，基本單表100萬(wàn)以內(nèi)的數(shù)據(jù)，性能上不會(huì)有太大的問(wèn)題，前提是，只要你建好索引就行，其實(shí)保證MySQL高性能通常沒(méi)什么特別高深的技巧，就是控制數(shù)據(jù)量不要太大，另外就是保證你的查詢用上了索引就行。
針對(duì)該問(wèn)題，就能分庫(kù)分表，可選擇將該用戶大表拆分為比如100張表，那么此時(shí)幾千萬(wàn) 數(shù)據(jù)瞬間分散到100個(gè)表里去，類似
這樣的100個(gè)表，每個(gè)表也就幾十萬(wàn)數(shù)據(jù)而已。
其次，可將這100個(gè)表分散到多臺(tái)數(shù)據(jù)庫(kù)服務(wù)器上去，此時(shí)要分散到幾臺(tái)服務(wù)器呢?你要考慮兩個(gè)點(diǎn)，一個(gè)是數(shù)據(jù)量有多少個(gè)GB/TB，一個(gè)是針對(duì)用戶中心的并發(fā)壓力有多高。
互聯(lián)網(wǎng)公司對(duì)用戶中心的壓力不會(huì)高到離譜，因?yàn)橐话悴粫?huì)有很多人同時(shí)注冊(cè)/登錄或同時(shí)修改自己的個(gè)人信息，所以并發(fā)不是大問(wèn)題。
數(shù)據(jù)量層面，一般1億行數(shù)據(jù)，大致在1~幾GB之間的范圍，和具體你一行數(shù)據(jù)有多少個(gè)字段也有關(guān)，所以說(shuō)你幾千萬(wàn)的用戶數(shù)據(jù)，往多了說(shuō)也就幾個(gè)GB。這點(diǎn)數(shù)據(jù)量，對(duì)服務(wù)器存儲(chǔ)空間沒(méi)壓力。
綜上，此時(shí)你完全能給他分配兩臺(tái)數(shù)據(jù)庫(kù)服務(wù)器，放兩個(gè)庫(kù)，然后100張表均勻分散在2臺(tái)服務(wù)器。分的時(shí)候，注意指定一個(gè)字段來(lái)分，一般就指定userid, 根據(jù)用戶id進(jìn)行hash后，對(duì)表進(jìn)行取模，路由到一個(gè)表里去，這就能讓數(shù)據(jù)均勻分散。
至此搞定用戶表的分庫(kù)分表，只要給系統(tǒng)加上數(shù)據(jù)庫(kù)中間件技術(shù)，設(shè)置好路由規(guī)則，即可輕松對(duì)2個(gè)分庫(kù)上的100張表crud。平時(shí)針對(duì)某個(gè)用戶增刪改查,直接對(duì)他的userid進(jìn)行hash,然后對(duì)表取模，做個(gè)路由，就知道到哪個(gè)表里去找這個(gè)用戶的數(shù)據(jù)了。
但是這里可能會(huì)出現(xiàn)一些問(wèn)題：用戶在登錄時(shí)，可能不是根據(jù)userid登錄，而是根據(jù)username、手機(jī)號(hào)，此時(shí)你就沒(méi)有userid了, 怎么知道去哪個(gè)表里找這個(gè)用戶的數(shù)據(jù)判斷是否能登錄? 對(duì)此。常規(guī)方案是建立一個(gè)索引映射表，搞個(gè)表結(jié)構(gòu)為 (username, userid)的索引映射 表，把username和userid一一映射， 然后針對(duì)username再做一次分庫(kù)分表，把這個(gè)索引映射表可拆分為比如100個(gè)表分散在兩臺(tái)服務(wù)器。然后用戶登錄時(shí)，就能根據(jù)username先去索引映射表里查找對(duì)應(yīng)userid，比如對(duì)username進(jìn)行hash，然后取模路由到一個(gè)表里去，找到username對(duì)應(yīng)的userid，接著根據(jù)userid進(jìn)行hash再取模，路由到按照userid分庫(kù)分表的一個(gè)表里，找到用戶的完整數(shù)據(jù)。但這種方式會(huì)把一次查詢轉(zhuǎn)化為兩個(gè)表的兩次查詢，先查索引映射表，再根據(jù)userid去查具體的數(shù)據(jù)，性能有損耗，不過(guò)有時(shí)為解決分庫(kù)分表問(wèn)題，也只能用這種方案。
另外就是若公司運(yùn)營(yíng)團(tuán)隊(duì)里，有用戶管理模塊，需要對(duì)公司的用戶按照手機(jī)號(hào)、住址、年齡、性別、職業(yè)等各種條件搜索，這咋辦?
沒(méi)啥好辦法，基本上就是要對(duì)你的用戶數(shù)據(jù)表進(jìn)行binlog監(jiān)聽(tīng)，把你要搜索的所有字段同步到ES，建立好搜索的索引。然后你的運(yùn)營(yíng)系統(tǒng)就可以通過(guò)ES進(jìn)行復(fù)雜的多條件搜索，ES是適合干這個(gè)事兒的，然后定位到一批userid，通過(guò)userid回到分庫(kù)分表環(huán)境里去找出具體的用戶數(shù)據(jù)，在頁(yè)面上展示出來(lái)即可。
這就是一套比較常規(guī)和完整的分庫(kù)分表的方案。
mysql數(shù)據(jù)庫(kù)對(duì)1億條數(shù)據(jù)的分表方法設(shè)計(jì)：
目前針對(duì)海量數(shù)據(jù)的優(yōu)化有兩種方法：
（1）垂直分割
優(yōu)勢(shì)：降低高并發(fā)情況下，對(duì)于表的鎖定。
不足：對(duì)于單表來(lái)說(shuō)，隨著數(shù)據(jù)庫(kù)的記錄增多，讀寫(xiě)壓力將進(jìn)一步增大。
（2）水平分割
如果單表的IO壓力大，可以考慮用水平分割，其原理就是通過(guò)hash算法，將一張表分為N多頁(yè)，并通過(guò)一個(gè)新的表（總表），記錄著每個(gè)頁(yè)的的位置。
假如一個(gè)門(mén)戶網(wǎng)站，它的數(shù)據(jù)庫(kù)表已經(jīng)達(dá)到了1億條記錄，那么此時(shí)如果通過(guò)select去查詢，必定會(huì)效率低下（不做索引的前提下）。為了降低單表的讀寫(xiě)IO壓力，通過(guò)水平分割，將這個(gè)表分成10個(gè)頁(yè)，同時(shí)生成一個(gè)總表，記錄各個(gè)頁(yè)的信息，那么假如我查詢一條id=100的記錄，它不再需要全表掃描，而是通過(guò)總表找到該記錄在哪個(gè)對(duì)應(yīng)的頁(yè)上，然后再去相應(yīng)的頁(yè)做檢索，這樣就降低了IO壓力。
7.1 范圍分片(range)
比如說(shuō)t3表
(1)行數(shù)非常多，2000w（1-1000w:sh1 1000w01-2000w:sh2）
(2)訪問(wèn)非常頻繁，用戶順序訪問(wèn)較多
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
取余分片方式：分片鍵（一個(gè)列）與節(jié)點(diǎn)數(shù)量進(jìn)行取余，得到余數(shù)，將數(shù)據(jù)寫(xiě)入對(duì)應(yīng)節(jié)點(diǎn)
vim schema.xml
table name="t4" dataNode="sh1,sh2" rule="mod-long" /
vim rule.xml
property name="count"2/property
創(chuàng)建測(cè)試表：
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é)點(diǎn)查詢數(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
使用場(chǎng)景：
如果你的業(yè)務(wù)中有些數(shù)據(jù)類似于數(shù)據(jù)字典，比如配置文件的配置，
常用業(yè)務(wù)的配置或者數(shù)據(jù)量不大很少變動(dòng)的表，這些表往往不是特別大，
而且大部分的業(yè)務(wù)場(chǎng)景都會(huì)用到，那么這種表適合于Mycat全局表，無(wú)須對(duì)數(shù)據(jù)進(jìn)行切分，
要在所有的分片上保存一份數(shù)據(jù)即可，Mycat 在Join操作中，業(yè)務(wù)表與全局表進(jìn)行Join聚合會(huì)優(yōu)先選擇相同分片內(nèi)的全局表join，
避免跨庫(kù)Join，在進(jìn)行數(shù)據(jù)插入操作時(shí)，mycat將把數(shù)據(jù)分發(fā)到全局表對(duì)應(yīng)的所有分片執(zhí)行，在進(jìn)行數(shù)據(jù)讀取時(shí)候?qū)?huì)隨機(jī)獲取一個(gè)節(jié)點(diǎn)讀取數(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é)點(diǎn)數(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"
一，什么是mysql分表，分區(qū)
什么是分表，從表面意思上看呢，就是把一張表分成N多個(gè)小表，具體請(qǐng)看mysql分表的3種方法
什么是分區(qū)，分區(qū)呢就是把一張表的數(shù)據(jù)分成N多個(gè)區(qū)塊，這些區(qū)塊可以在同一個(gè)磁盤(pán)上，也可以在不同的磁盤(pán)上，具體請(qǐng)參考mysql分區(qū)功能詳細(xì)介紹，以及實(shí)例
二，mysql分表和分區(qū)有什么區(qū)別呢
1，實(shí)現(xiàn)方式上
a），mysql的分表是真正的分表，一張表分成很多表后，每一個(gè)小表都是完正的一張表，都對(duì)應(yīng)三個(gè)文件，一個(gè).MYD數(shù)據(jù)文件，.MYI索引文件，.frm表結(jié)構(gòu)文件。
Sql代碼
[root@BlackGhost test]# ls |grep user
alluser.MRG
alluser.frm
user1.MYD
user1.MYI
user1.frm
user2.MYD
user2.MYI
user2.frm
簡(jiǎn)單說(shuō)明一下，上面的分表呢是利用了merge存儲(chǔ)引擎（分表的一種），alluser是總表，下面有二個(gè)分表，user1，user2。他們二個(gè)都是獨(dú)立的表，取數(shù)據(jù)的時(shí)候，我們可以通過(guò)總表來(lái)取。這里總表是沒(méi)有.MYD,.MYI這二個(gè)文件的，也就是說(shuō)，總表他不是一張表，沒(méi)有數(shù)據(jù)，數(shù)據(jù)都放在分表里面。我們來(lái)看看.MRG到底是什么東西
Sql代碼
[root@BlackGhost test]# cat alluser.MRG |more
user1
user2
#INSERT_METHOD=LAST
從上面我們可以看出，alluser.MRG里面就存了一些分表的關(guān)系，以及插入數(shù)據(jù)的方式?？梢园芽偙砝斫獬梢粋€(gè)外殼，或者是聯(lián)接池。
b），分區(qū)不一樣，一張大表進(jìn)行分區(qū)后，他還是一張表，不會(huì)變成二張表，但是他存放數(shù)據(jù)的區(qū)塊變多了。
Sql代碼
[root@BlackGhost test]# ls |grep aa
aa#P#p1.MYD
aa#P#p1.MYI
aa#P#p3.MYD
aa#P#p3.MYI
aa.frm
aa.par
從上面我們可以看出，aa這張表，分為二個(gè)區(qū)，p1和p3，本來(lái)是三個(gè)區(qū)，被我刪了一個(gè)區(qū)。我們都知道一張表對(duì)應(yīng)三個(gè)文件.MYD,.MYI,.frm。分區(qū)呢根據(jù)一定的規(guī)則把數(shù)據(jù)文件和索引文件進(jìn)行了分割，還多出了一個(gè).par文件，打開(kāi).par文件后你可以看出他記錄了，這張表的分區(qū)信息，根分表中的.MRG有點(diǎn)像。分區(qū)后，還是一張，而不是多張表。
2，數(shù)據(jù)處理上
a），分表后，數(shù)據(jù)都是存放在分表里，總表只是一個(gè)外殼，存取數(shù)據(jù)發(fā)生在一個(gè)一個(gè)的分表里面?？聪旅娴睦樱?/p>
select * from alluser where id='12'表面上看，是對(duì)表alluser進(jìn)行操作的，其實(shí)不是的。是對(duì)alluser里面的分表進(jìn)行了操作。
b），分區(qū)呢，不存在分表的概念，分區(qū)只不過(guò)把存放數(shù)據(jù)的文件分成了許多小塊，分區(qū)后的表呢，還是一張表。數(shù)據(jù)處理還是由自己來(lái)完成。
3，提高性能上
a），分表后，單表的并發(fā)能力提高了，磁盤(pán)I/O性能也提高了。并發(fā)能力為什么提高了呢，因?yàn)椴閷ひ淮嗡ǖ臅r(shí)間變短了，如果出現(xiàn)高并發(fā)的話，總表可以根據(jù)不同的查詢，將并發(fā)壓力分到不同的小表里面。磁盤(pán)I/O性能怎么搞高了呢，本來(lái)一個(gè)非常大的.MYD文件現(xiàn)在也分?jǐn)偟礁鱾€(gè)小表的.MYD中去了。
b），mysql提出了分區(qū)的概念，我覺(jué)得就想突破磁盤(pán)I/O瓶頸，想提高磁盤(pán)的讀寫(xiě)能力，來(lái)增加mysql性能。
在這一點(diǎn)上，分區(qū)和分表的測(cè)重點(diǎn)不同，分表重點(diǎn)是存取數(shù)據(jù)時(shí)，如何提高mysql并發(fā)能力上；而分區(qū)呢，如何突破磁盤(pán)的讀寫(xiě)能力，從而達(dá)到提高mysql性能的目的。
4），實(shí)現(xiàn)的難易度上
a），分表的方法有很多，用merge來(lái)分表，是最簡(jiǎn)單的一種方式。這種方式根分區(qū)難易度差不多，并且對(duì)程序代碼來(lái)說(shuō)可以做到透明的。如果是用其他分表方式就比分區(qū)麻煩了。
b），分區(qū)實(shí)現(xiàn)是比較簡(jiǎn)單的，建立分區(qū)表，根建平常的表沒(méi)什么區(qū)別，并且對(duì)開(kāi)代碼端來(lái)說(shuō)是透明的。
三，mysql分表和分區(qū)有什么聯(lián)系呢
1，都能提高mysql的性高，在高并發(fā)狀態(tài)下都有一個(gè)良好的表面。
2，分表和分區(qū)不矛盾，可以相互配合的，對(duì)于那些大訪問(wèn)量，并且表數(shù)據(jù)比較多的表，我們可以采取分表和分區(qū)結(jié)合的方式（如果merge這種分表方式，不能和分區(qū)配合的話，可以用其他的分表試），訪問(wèn)量不大，但是表數(shù)據(jù)很多的表，我們可以采取分區(qū)的方式等。
分區(qū)
分區(qū)就是把一個(gè)數(shù)據(jù)表的文件和索引分散存儲(chǔ)在不同的物理文件中。
mysql支持的分區(qū)類型包括Range、List、Hash、Key，其中Range比較常用：
RANGE分區(qū)：基于屬于一個(gè)給定連續(xù)區(qū)間的列值，把多行分配給分區(qū)。
LIST分區(qū)：類似于按RANGE分區(qū)，區(qū)別在于LIST分區(qū)是基于列值匹配一個(gè)離散值集合中的某個(gè)值來(lái)進(jìn)行選擇。
HASH分區(qū)：基于用戶定義的表達(dá)式的返回值來(lái)進(jìn)行選擇的分區(qū)，該表達(dá)式使用將要插入到表中的這些行的列值進(jìn)行計(jì)算。這個(gè)函數(shù)可以包含MySQL 中有效的、產(chǎn)生非負(fù)整數(shù)值的任何表達(dá)式。
KEY分區(qū)：類似于按HASH分區(qū)，區(qū)別在于KEY分區(qū)只支持計(jì)算一列或多列，且MySQL服務(wù)器提供其自身的哈希函數(shù)。必須有一列或多列包含整數(shù)值。
分表
分表和分區(qū)類似，區(qū)別是，分區(qū)是把一個(gè)邏輯表文件分成幾個(gè)物理文件后進(jìn)行存儲(chǔ)，而分表則是把原先的一個(gè)表分成幾個(gè)表。進(jìn)行分表查詢時(shí)可以通過(guò)union或者視圖。
分表又分垂直分割和水平分割，其中水平分分割最為常用。水平分割通常是指切分到另外一個(gè)數(shù)據(jù)庫(kù)或表中 。
重慶分公司
重慶分公司
