快捷導(dǎo)航

MySQL 分庫(kù)分表實(shí)踐

更新時(shí)間：2023年10月18日 08:59:12 作者：bugcoder-9905

本文主要介紹了MySQL 分庫(kù)分表實(shí)踐，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧

一、為什么要分庫(kù)分表

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

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

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

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

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

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

拆分策略

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

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

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

三、垂直拆分

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

1. 垂直分庫(kù)

server.xml

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

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

schema.xml

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
	<!-- 邏輯數(shù)據(jù)庫(kù) -->
	<schema name="USERDB1" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1" /> <!-- 兩個(gè)邏輯庫(kù)對(duì)應(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)對(duì)應(yīng)兩個(gè)不同的物理機(jī)器 -->
	<dataNode name="dn2" dataHost="node2" database="mytest2" />  <!-- USERDB1對(duì)應(yīng)mytest1，USERDB2對(duì)應(yīng)mytest2 -->
	<!-- 數(shù)據(jù)庫(kù)主機(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è)邏輯庫(kù)對(duì)應(yīng)兩個(gè)不同的數(shù)據(jù)節(jié)點(diǎn)，兩個(gè)數(shù)據(jù)節(jié)點(diǎn)對(duì)應(yīng)兩個(gè)不同的物理機(jī)器

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

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

2. 垂直分表

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

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

四、水平分表

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

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

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

1. 配置水平分表

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ù)庫(kù) -->
	<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"/> <!-- student表按照id取模拆分 -->
	</schema>
	<!-- 存儲(chǔ)節(jié)點(diǎn) -->
	<dataNode name="dn1" dataHost="node1" database="mytest1" />
	<dataNode name="dn2" dataHost="node2" database="mytest2" />
	<!-- 數(shù)據(jù)庫(kù)主機(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ī)器上，在物理上分開(kāi)了，但是在邏輯上還是一個(gè)，往哪張表里增加，在2臺(tái)機(jī)器上查詢?nèi)缓笕绾魏喜⑦@些操作都是由mycat完成的

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

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

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

2. 測(cè)試水平分表

Linux主機(jī)

Windows主機(jī)

登錄到mycat的8066端口

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

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

我們?cè)偻ㄟ^(guò)MyCat給student表插入兩條數(shù)據(jù)

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