MySQL 分庫分表的專案實踐

2022-04-11 13:00:39

一、為什麼要分庫分表

資料庫架構演變

剛開始多數專案用單機資料庫就夠了，隨著伺服器流量越來越大，面對的請求也越來越多，我們做了資料庫讀寫分離，使用多個從庫副本（Slave）負責讀，使用主庫（Master）負責寫，master和slave通過主從複製實現資料同步更新，保持資料一致。slave 從庫可以水平擴充套件，所以更多的讀請求不成問題

但是當用戶量級上升，寫請求越來越多，怎麼保證資料庫的負載足夠？增加一個Master是不能解決問題的，因為資料要儲存一致性，寫操作需要2個master之間同步，相當於是重複了，而且架構設計更加複雜

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

二、庫表太大產生的問題

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

拆分策略

單個庫太大，先考慮是表多還是資料多：

如果因為表多而造成資料過多，則使用垂直拆分，即根據業務拆分成不同的庫
如果因為單張表的資料量太大，則使用水平拆分，即把表的資料按照某種規則（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/">
	<!-- 邏輯資料庫 -->
	<schema name="USERDB1" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1" /> <!-- 兩個邏輯庫對應兩個不同的資料節點 -->
	<schema name="USERDB2" checkSQLschema="false" sqlMaxLimit="100"dataNode="dn2" />
	<!-- 儲存節點 -->
	<dataNode name="dn1" dataHost="node1" database="mytest1" />  <!-- 兩個資料節點對應兩個不同的物理機器 -->
	<dataNode name="dn2" dataHost="node2" database="mytest2" />  <!-- USERDB1對應mytest1，USERDB2對應mytest2 -->
	<!-- 資料庫主機 -->
	<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>

兩個邏輯庫對應兩個不同的資料節點，兩個資料節點對應兩個不同的物理機器

mytest1和mytest2分成了不同機器上的不同的庫，各包含一部分表，它們原來是合在一塊的，在一臺機器上，現在做了垂直的拆分。
使用者端就需要去連線不同的邏輯庫了，根據業務操作不同的邏輯庫

然後設定了兩個寫庫，兩臺機器把庫平分了，分擔了原來單機的壓力。分庫伴隨著分表，從業務上對錶拆分

2. 垂直分表

垂直分表，基於列欄位進行。一般是針對幾百列的這種大表，也避免查詢時，資料量太大造成的“跨頁”問題。

一般是表中的欄位較多，將不常用的，資料較大，長度較長（比如text型別欄位）的拆分到擴充套件表。存取頻率較高的欄位單獨放在一張表

四、水平分庫分表

針對資料量巨大的單張表（比如訂單表），按照某種規則（RANGE、HASH取模等），切分到多張表裡面去。但是這些表還是在同一個庫中，所以庫級別的資料庫操作還是有IO瓶頸，不建議採用

將單張表的資料切分到多個伺服器上去，每個伺服器具有一部分庫與表，只是表中資料集合不同。水平分庫分表能夠有效的緩解單機和單庫的效能瓶頸和壓力，突破IO、連線數、硬體資源等的瓶頸

分庫分表可以和主從複製同時進行，但不基於主從複製；讀寫分離才基於主從複製

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/">
	<!-- 邏輯資料庫 -->
	<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>
	<!-- 儲存節點 -->
	<dataNode name="dn1" dataHost="node1" database="mytest1" />
	<dataNode name="dn2" dataHost="node2" database="mytest2" />
	<!-- 資料庫主機 -->
	<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>