一、為什么要分庫分表

數(shù)據(jù)庫架構(gòu)演變

剛開始多數(shù)項(xiàng)目用單機(jī)數(shù)據(jù)庫就夠了，隨著服務(wù)器流量越來越大，面對的請求也越來越多，我們做了數(shù)據(jù)庫讀寫分離， 使用多個(gè)從庫副本（Slave）負(fù)責(zé)讀，使用主庫（Master）負(fù)責(zé)寫，master和slave通過主從復(fù)制實(shí)現(xiàn)數(shù)據(jù)同步更新，保持?jǐn)?shù)據(jù)一致。slave 從庫可以水平擴(kuò)展，所以更多的讀請求不成問題

但是當(dāng)用戶量級(jí)上升，寫請求越來越多，怎么保證數(shù)據(jù)庫的負(fù)載足夠？增加一個(gè)Master是不能解決問題的， 因?yàn)閿?shù)據(jù)要保存一致性，寫操作需要2個(gè)master之間同步，相當(dāng)于是重復(fù)了，而且架構(gòu)設(shè)計(jì)更加復(fù)雜

這時(shí)需要用到分庫分表（sharding），把庫和表存放在不同的MySQL Server上，每臺(tái)服務(wù)器可以均衡寫請求的次數(shù)

二、庫表太大產(chǎn)生的問題

• 單庫太大：單庫處理能力有限、所在服務(wù)器上的磁盤空間不足、遇到IO瓶頸，需要把單庫切分成更多更小的庫
• 單表太大：CURD效率都很低、數(shù)據(jù)量太大導(dǎo)致索引文件過大，磁盤IO加載索引花費(fèi)時(shí)間，導(dǎo)致查詢超時(shí)。所以只用索引還是不行的，需要把單表切分成多個(gè)數(shù)據(jù)集更小的表。MyCat提供的分表算法都在rule.xml，可以根據(jù)不同的分表算法進(jìn)行拆分，比如根據(jù)時(shí)間拆分、一致性哈希、直接用主鍵對分表的個(gè)數(shù)取模等

拆分策略

單個(gè)庫太大，先考慮是表多還是數(shù)據(jù)多：

• 如果因?yàn)楸矶喽斐蓴?shù)據(jù)過多，則使用垂直拆分，即根據(jù)業(yè)務(wù)拆分成不同的庫
• 如果因?yàn)閱螐埍淼臄?shù)據(jù)量太大，則使用水平拆分，即把表的數(shù)據(jù)按照某種規(guī)則（rule.xml定義的分表算法）拆分成多張表

分庫分表的原則應(yīng)該是先考慮垂直拆分，再考慮水平拆分

三、垂直拆分

分庫分表和讀寫分離可以共同進(jìn)行

1. 垂直分庫

server.xml

<user name="root">
<property name="password">123456</property>
<property name="schemas">USERDB1,USERDB2</property>
</user>

配置了USERDB1、USERDB2這兩個(gè)邏輯庫

schema.xml

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
	<!-- 邏輯數(shù)據(jù)庫 -->
	<schema name="USERDB1" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1" /> <!-- 兩個(gè)邏輯庫對應(yīng)兩個(gè)不同的數(shù)據(jù)節(jié)點(diǎn) -->
	<schema name="USERDB2" checkSQLschema="false" sqlMaxLimit="100"dataNode="dn2" />
	<!-- 存儲(chǔ)節(jié)點(diǎn) -->
	<dataNode name="dn1" dataHost="node1" database="mytest1" />  <!-- 兩個(gè)數(shù)據(jù)節(jié)點(diǎn)對應(yīng)兩個(gè)不同的物理機(jī)器 -->
	<dataNode name="dn2" dataHost="node2" database="mytest2" />  <!-- USERDB1對應(yīng)mytest1，USERDB2對應(yīng)mytest2 -->
	<!-- 數(shù)據(jù)庫主機(jī) -->
	<dataHost name="node1" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native">
		<heartbeat>select user()</heartbeat>
		<writeHost host="192.168.131.129" url="192.168.131.129:3306" user="root" password="123456" />
	</dataHost>
	
	<dataHost name="node2" maxCon="1000" minCon="10" balance="0"writeType="0" dbType="mysql" dbDriver="native">
		<heartbeat>select user()</heartbeat>
		<writeHost host="192.168.0.6" url="192.168.0.6:3306" user="root" password="123456" />
	</dataHost>
</mycat:schema>

兩個(gè)邏輯庫對應(yīng)兩個(gè)不同的數(shù)據(jù)節(jié)點(diǎn)，兩個(gè)數(shù)據(jù)節(jié)點(diǎn)對應(yīng)兩個(gè)不同的物理機(jī)器

mytest1和mytest2分成了不同機(jī)器上的不同的庫，各包含一部分表，它們原來是合在一塊的，在一臺(tái)機(jī)器上，現(xiàn)在做了垂直的拆分。
客戶端就需要去連接不同的邏輯庫了，根據(jù)業(yè)務(wù)操作不同的邏輯庫

然后配置了兩個(gè)寫庫，兩臺(tái)機(jī)器把庫平分了，分擔(dān)了原來單機(jī)的壓力。分庫伴隨著分表，從業(yè)務(wù)上對表拆分

2. 垂直分表

垂直分表，基于列字段進(jìn)行。一般是針對幾百列的這種大表，也避免查詢時(shí)，數(shù)據(jù)量太大造成的“跨頁”問題。

一般是表中的字段較多，將不常用的， 數(shù)據(jù)較大，長度較長（比如text類型字段）的拆分到擴(kuò)展表。訪問頻率較高的字段單獨(dú)放在一張表

四、水平分庫分表

針對數(shù)據(jù)量巨大的單張表（比如訂單表），按照某種規(guī)則（RANGE、HASH取模等），切分到多張表里面去。 但是這些表還是在同一個(gè)庫中，所以庫級(jí)別的數(shù)據(jù)庫操作還是有IO瓶頸，不建議采用

將單張表的數(shù)據(jù)切分到多個(gè)服務(wù)器上去，每個(gè)服務(wù)器具有一部分庫與表，只是表中數(shù)據(jù)集合不同。 水平分庫分表能夠有效的緩解單機(jī)和單庫的性能瓶頸和壓力，突破IO、連接數(shù)、硬件資源等的瓶頸

分庫分表可以和主從復(fù)制同時(shí)進(jìn)行，但不基于主從復(fù)制；讀寫分離才基于主從復(fù)制

server.xml

<user name="root">
	<property name="password">123456</property>
	<property name="schemas">USERDB</property>
</user>

schema.xml

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
	<!-- 邏輯數(shù)據(jù)庫 -->
	<schema name="USERDB" checkSQLschema="false" sqlMaxLimit="100">
		<table name="user" dataNode="dn1" /> <!-- 這里的user和student都是實(shí)際存在的物理表名 -->
		<table name="student" primaryKey="id" autoIncrement="true" dataNode="dn1,dn2" rule="mod-long"/>
	</schema>
	<!-- 存儲(chǔ)節(jié)點(diǎn) -->
	<dataNode name="dn1" dataHost="node1" database="mytest1" />
	<dataNode name="dn2" dataHost="node2" database="mytest2" />
	<!-- 數(shù)據(jù)庫主機(jī) -->
	<dataHost name="node1" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native">
		<heartbeat>select user()</heartbeat>
		<writeHost host="192.168.131.129" url="192.168.131.129:3306" user="root" password="123456" />
	</dataHost>
	<dataHost name="node2" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native">
		<heartbeat>select user()</heartbeat>
		<writeHost host="192.168.0.6" url="192.168.0.6:3306" user="root" password="123456" />
	</dataHost>
</mycat:schema>

user表示一個(gè)普通的表，直接放在數(shù)據(jù)節(jié)點(diǎn)dn1上，放在一臺(tái)機(jī)器上，這張表不用進(jìn)行拆分

student表的primaryKey是id，根據(jù)id拆分，放在dn1和dn2上，最終這個(gè)表要分在兩臺(tái)機(jī)器上，在物理上分開了，但是在邏輯上還是一個(gè)，往哪張表里增加，在2臺(tái)機(jī)器上查詢?nèi)缓笕绾魏喜⑦@些操作都是由mycat完成的

拆分的規(guī)則是取模（mod - long)，每次插入用id模上存在的機(jī)器數(shù)（2）

此外還需要在rule.xml中配置以下拆分算法

找到算法mod-long，因?yàn)槲覀儗⑦壿嫳韘tudent分開映射到兩臺(tái)主機(jī)上，所以修改數(shù)據(jù)節(jié)點(diǎn)的數(shù)量為2

2. 測試水平分表

Linux主機(jī)

Windows主機(jī)

登錄到mycat的8066端口

使用MyCat給user表插入兩條數(shù)據(jù)

由于schema.xml配置文件中，邏輯表user只在Linux主機(jī)的mytest1庫中存在，mycat操作的邏輯表user會(huì)影響Linux主機(jī)上的物理表，而不會(huì)影響Windows主機(jī)上的表。我們分別查看一下Linux和Windows主機(jī)的user表：

我們再通過MyCat給student表插入兩條數(shù)據(jù)

我們知道schema.xml配置文件中，邏輯表student對應(yīng)兩臺(tái)主機(jī)上的兩個(gè)庫mytest1、mytest2中的兩張表，所以對邏輯表插入的兩條數(shù)據(jù)，會(huì)實(shí)際影響到兩張物理表（用id%機(jī)器數(shù)，決定插入到哪張物理表）。我們分別查看一下Linux和Windows主機(jī)的student表：

再通過MyCat插入id=3和id=4的數(shù)據(jù)，應(yīng)該插入不同主機(jī)上的不同物理表

這就相當(dāng)于把student表進(jìn)行水平拆分了

通過MyCat查詢的時(shí)候只需要正常輸入就行，我們配置的是表拆分后放在這2個(gè)數(shù)據(jù)節(jié)點(diǎn)上，MyCat會(huì)根據(jù)配置在兩個(gè)庫上查詢并進(jìn)行數(shù)據(jù)合并

 到此這篇關(guān)于MySQL 分庫分表的項(xiàng)目實(shí)踐的文章就介紹到這了,更多相關(guān)MySQL 分庫分表內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論