MySQL分庫分錶實例分析

PHPz轉載: 2023-06-03 18:34:301169瀏覽

一、為什麼要分庫分錶

資料庫架構演進

剛開始多數項目用單機資料庫就夠了，隨著伺服器流量越來越大，面對的請求也越來越多，我們做了資料庫讀寫分離，使用多個從函式庫副本（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>

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

MySQL分庫分錶實例分析

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

MySQL分庫分錶實例分析

#然後配置了兩個寫庫，兩台機器把庫平分了，分擔了原來單機的壓力。分庫伴隨著分錶，從業務上對錶拆分

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>

MySQL分庫分錶實例分析