一、為什么要分庫分表

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

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

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

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

二、庫表太大產生的問題

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

拆分策略

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

• 如果因為表多而造成數(shù)據(jù)過多，則使用垂直拆分，即根據(jù)業(yè)務拆分成不同的庫
• 如果因為單張表的數(shù)據(jù)量太大，則使用水平拆分，即把表的數(shù)據(jù)按照某種規(guī)則（rule.xml定義的分表算法）拆分成多張表

分庫分表的原則應該是先考慮垂直拆分，再考慮水平拆分

三、垂直拆分

分庫分表和讀寫分離可以共同進行

1. 垂直分庫

server.xml

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

配置了USERDB1、USERDB2這兩個邏輯庫

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" /> <!-- 兩個邏輯庫對應兩個不同的數(shù)據(jù)節(jié)點 -->
	<schema name="USERDB2" checkSQLschema="false" sqlMaxLimit="100"dataNode="dn2" />
	<!-- 存儲節(jié)點 -->
	<dataNode name="dn1" dataHost="node1" database="mytest1" />  <!-- 兩個數(shù)據(jù)節(jié)點對應兩個不同的物理機器 -->
	<dataNode name="dn2" dataHost="node2" database="mytest2" />  <!-- USERDB1對應mytest1，USERDB2對應mytest2 -->
	<!-- 數(shù)據(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>

兩個邏輯庫對應兩個不同的數(shù)據(jù)節(jié)點，兩個數(shù)據(jù)節(jié)點對應兩個不同的物理機器

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

然后配置了兩個寫庫，兩臺機器把庫平分了，分擔了原來單機的壓力。分庫伴隨著分表，從業(yè)務上對表拆分

2. 垂直分表

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

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

四、水平分庫分表

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

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

分庫分表可以和主從復制同時進行，但不基于主從復制；讀寫分離才基于主從復制

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都是實際存在的物理表名 -->
		<table name="student" primaryKey="id" autoIncrement="true" dataNode="dn1,dn2" rule="mod-long"/>
	</schema>
	<!-- 存儲節(jié)點 -->
	<dataNode name="dn1" dataHost="node1" database="mytest1" />
	<dataNode name="dn2" dataHost="node2" database="mytest2" />
	<!-- 數(shù)據(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表示一個普通的表，直接放在數(shù)據(jù)節(jié)點dn1上，放在一臺機器上，這張表不用進行拆分

student表的primaryKey是id，根據(jù)id拆分，放在dn1和dn2上，最終這個表要分在兩臺機器上，在物理上分開了，但是在邏輯上還是一個，往哪張表里增加，在2臺機器上查詢然后如何合并這些操作都是由mycat完成的

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

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

找到算法mod-long，因為我們將邏輯表student分開映射到兩臺主機上，所以修改數(shù)據(jù)節(jié)點的數(shù)量為2

2. 測試水平分表

Linux主機

Windows主機

登錄到mycat的8066端口

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

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

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

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

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

這就相當于把student表進行水平拆分了

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

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

最新評論