1.垂直分表的背景

我們的商城系統數據庫，目前是單點數據庫，隨著業務量越來越大，每日產生的數據量越來越多，單臺數據庫的存儲能力和計算能力是有限的，為了保證用戶的體驗度和滿意度，在數據庫性能到達瓶頸之前，我們先對數據進行性能優化，目前的優化方案是對商城庫進行垂直分表，擴展數據庫節點，將不同業務的表存儲在多個數據庫節點中，提高數據庫的性能。

垂直分庫指的是將一個庫中的多個表，拆分到多個數據庫實例中，也就是拆分到了多臺不同的數據庫服務器上，緩解了單臺數據庫所承擔的壓力。

2.垂直分表案例實戰

2.1.垂直分表規劃

為了保證數據庫的高可用性和讀寫分離，我們在前面準備了2套雙主雙從的集群，我們按照雙主雙從集群進行劃分，每一套雙主雙從充當一個數據節點也就是分片，將商品庫的這些表分別存放在不同的節點分片中，從而達到分表的目的。

如下圖所示，商品庫的所有表的劃分情況如下：

• 將tb_goods_base、tb_goods_brand、tb_goods_cat、tb_goods_desc、tb_goods_item、tb_user、tb_user_address這7張表劃分到分片1這個雙主雙從集群中。
• 將tb_order_item、tb_order_master、tb_order_pay_log、tb_areas_city、tb_areas_region、tb_areas_provinces這6張表劃分到分片2這個雙主雙從集群中。

利用Mycat實現垂直分表的思路：

• 首先聲明一個schema，定義邏輯庫，邏輯庫就是這個商品庫。
• 然后定義邏輯表table，根據不同的表劃分到不同的數據節點dataNode上。
• 然后定義dataNode關聯數據庫節點中真實的數據庫。
• 最后定義dataHost也就是分片，一共定義2組分片，分別指向各自的雙主雙從復制集群。

此架構實現后，我們的商品庫就從單點架構升級達到了雙主雙從高可用+讀寫分離的架構，并且也從邏輯上進行了分庫分表操作，提供數據庫性能。

2.2.配置Mycat實現垂直分表

1）在Schema配置文件中配置垂直分表

關于配置參數有幾點需要說明一下：

• 如果在<schema>標簽中指定了dataNode數據節點，那么該邏輯庫下所有的表都會被存儲到指定數據節點的數據庫實例上。
• 如果單獨在<table>邏輯表標簽中定義了dataNode數據節點，那么該數據節點的優先級將大于schema中的數據節點，會根據邏輯表標簽中的定義的dataNode，將該表存放在指定的數據庫實例中。
• 由于我們的分片一共有兩個，因此我們要定義兩個dataNode數據節點，一個數據節點相當于是一個分片，然后將指定的表、庫按照需求劃分到對應的分片上。
• 定義好分片后，就需要去定義dataHost數據主機了，一個分片對應一個dataHost，因此我們需要定義兩組dataHost，第一套雙主雙從集群分片主機為mysqlcluster-1，第二套雙主雙從集群分片主機為mysqlcluster-2。
• 然后在dataHost中去指定數據存儲的具體數據庫實例，也就是我們的雙主雙從集群，雙主雙從集群也是兩套主從復制集群，我們配置成兩組<writeHost>，雙主雙從集群只有一個主庫承擔寫操作，另一個主庫充當備用主庫，當主庫故障后，備用主庫直接切換成主庫，形成高可用集群，雙從全部承擔讀操作。

Schema配置文件的邏輯調用關系如下：

<schema>中包含<table>，定義要對那些庫和表進行操作，邏輯庫和邏輯表都會關聯數據節點<dataNode>，在數據節點中關聯數據主機<dataHost>和真實數據庫名稱，在<dataHost>中定義數據庫實例信息。

一個邏輯庫或者邏輯表的分庫分表操作，是根據關聯數據節點確定要將庫、表分在哪個數據庫節點上，然后關聯的數據節點找到具體的數據主機，最后庫、表就分在了關聯的數據主機上。

[root@mysql-1 ~]# vim /data/mycat/conf/schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">  
<mycat:schema xmlns:mycat="http://io.mycat/"><!--定義邏輯庫 庫名叫做db_shopping 該邏輯庫關聯dn1這個數據節點--> 	<schema name="db_shopping" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"><!--定義邏輯表 將部分表劃分到dn1這個分片--><table name="tb_goods_base" dataNode="dn1" primaryKey="id"/><table name="tb_goods_brand" dataNode="dn1"/><table name="tb_goods_cat" dataNode="dn1"/><table name="tb_goods_desc" dataNode="dn1"/><table name="tb_goods_item" dataNode="dn1"/><table name="tb_user" dataNode="dn1"/><table name="tb_user_address" dataNode="dn1"/><!--定義邏輯表 將部分表劃分到dn2這個分片--><table name="tb_order_item" dataNode="dn2"/><table name="tb_order_master" dataNode="dn2"/><table name="tb_order_pay_log" dataNode="dn2"/><table name="tb_areas_city" dataNode="dn2"/><table name="tb_areas_region" dataNode="dn2"/><table name="tb_areas_provinces" dataNode="dn2"/></schema>  <!--定義數據節點 也就是分片 一個分片會關聯一個數據主機組 然后對應真實的數據庫名稱--><dataNode name="dn1" dataHost="mysqlcluster-1" database= "db_shopping" />          <dataNode name="dn2" dataHost="mysqlcluster-2" database= "db_shopping" />          <!--定義數據主機 在這個標簽下定義具體的讀寫操作路由的數據庫實例地址 schema、table劃分如何指定的是該數據主機關聯的數據節點 那么對應的庫、表都會被存儲在數據主機定義的數據庫實例中--><dataHost name="mysqlcluster-1" maxCon="1000" minCon="10" balance="1"  writeType="0" dbType="mysql"  dbDriver="native" switchType="1">    <heartbeat>select user()</heartbeat>  <!--定義寫操作路由的數據庫實例--><writeHost host="c1-1-master3306" url="192.168.20.11:3306" user="root" password="123456"><!--定義讀操作路由的數據庫實例--><readHost host="c1-1-slave3308" url="192.168.20.11:3308" user="root" password="123456" /></writeHost> <!--備用的主庫 也是提供寫操作的數據庫，當主庫c1-1-master3306故障后 備用庫開始提供寫操作--><writeHost host="c1-2-master3306" url="192.168.20.12:3306" user="root" password="123456"><!--備用主庫的從庫 從始至終 只要備用主庫不故障 會一直提供讀服務--><readHost host="c1-2-slave3308" url="192.168.20.12:3308" user="root" password="123456" /></writeHost> </dataHost>  <dataHost name="mysqlcluster-2" maxCon="1000" minCon="10" balance="1"  writeType="0" dbType="mysql"  dbDriver="native" switchType="1">    <heartbeat>select user()</heartbeat>  <writeHost host="c2-1-master3307" url="192.168.20.11:3307" user="root" password="123456"><readHost host="c2-1-slave3309" url="192.168.20.11:3309" user="root" password="123456" /></writeHost> <!--備用主庫db3 主庫db1故障后 開始提供寫操作--><writeHost host="c2-2-master3307" url="192.168.20.12:3307" user="root" password="123456"><!--備用主庫的從庫 從始至終 只要備用主庫不故障 會一直提供讀服務--><readHost host="c2-2-slave3309" url="192.168.20.12:3309" user="root" password="123456" /></writeHost> </dataHost>  </mycat:schema>

2）在Server配置文件中定義通過Mycat連接后允許訪問的邏輯庫

[root@mysql-1 ~]# vim /data/mycat/conf/server.xml
······<user name="root" defaultAccount="true"><!--登錄用戶的密碼--><property name="password">123456</property><!--該用戶登錄后可以顯示那些Schema--><property name="schemas">TESTDB</property></user>
······

2.3.重啟Mycat

垂直分表策略規則配置完成后，下面就可以重啟Mycat了。

[root@mysql-1 ~]# mycat restart
Stopping Mycat-server...
Mycat-server was not running.
Starting Mycat-server...

2.4.在Mycat命令行中導入數據結構

Mycat已經配置完垂直分表了，當有數據要寫入到我們分的表時，對應的數據庫實例上要保證這個庫、表都存在，否則用戶的數據將寫入失敗，無論是生產環境還是測試環境，使用Mycat分庫分庫后，需要先在分片節點上將數據庫創建出來，然后再通過Mycat將不同的表劃分存儲到不同的分片節點上。

1）在兩個分片節點中分別創建出db_shopping數據庫

分片節點是雙主雙從集群，只需要連接上雙主雙從的任意一個主庫上，創建數據庫即可，會同步到所有的從庫、主庫上。

#分片1的主庫操作
[root@mysql-1 sql]# mysql -uroot -p123456 -P3306 -h 192.168.20.11
mysql> create database db_shopping character set utf8mb4;#分片2的主庫操作
[root@mysql-1 sql]# mysql -uroot -p123456 -P3307 -h 192.168.20.11
mysql> create database db_shopping character set utf8mb4;

2）登陸Mycat導入商品庫的所有表以及數據

數據庫需要在每個數據庫實例上獨立創建出來，數據表我們是通過Mycat進行垂直分表的，因此無需在每個實例上創建，只需要登陸Mycat，在Mycat中執行建表語句和插入數據，這時Mycat就會根據配置的分表策略，將不同的表寫入到不同的數據庫實例上。

當線上生產庫要進行分表時，也是按照這種套路，在Mycat上執行多個表備份的數據，Mycat會自動路由到指定的數據庫實例分片上。

[root@mysql-1 sql]# mysql -uroot -p123456 -P8066 -h 192.168.20.11
mysql> use db_shopping;
mysql> source /root/sql/shopping-table.sql
mysql> source /root/sql/shopping-insert.sql

2.5.查看由Mycat分表后每個分片上存儲的表

我們一共有2個分片，每個分片都是雙主雙從的集群模式，我們只需要看每個分片的任意一個主庫即可，因為從庫會同步主庫的數據，接下來我們去查看每個分片上所存儲的商品庫的表有那些，是否是我們規劃的樣子。

1）分片：mysqlcluster-1

查看該分片節點中的任意一個主庫即可看到全部信息。

分片1：mysqlcluster-1包含了tb_goods_base、tb_goods_brand、tb_goods_cat、tb_goods_desc、tb_goods_item、tb_user、tb_user_address這7張表，垂直分表成功。

2）分片：mysqlcluster-2

查看該分片節點中的任意一個主庫即可看到全部信息。

分片2：mysqlcluster-2包含了tb_order_item、tb_order_master、tb_order_pay_log、tb_areas_city、tb_areas_region、tb_areas_provinces這6張表，垂直分表成功。

2.6.Mycat垂直分表后可能遇到的問題

程序連接數據庫，都是直接配置Mycat的地址，Mycat中的數據庫、表都是邏輯性的，對于程序而言、開發同事而言，他們并不知道Mycat后端對應了那些數據庫實例，并且當Mycat進行垂直分庫、分表后，表與表之間可能都不在一個數據庫實例上。

這時如果我們有多表聯查的操作，可能聯查的表與表并沒有分在同一個數據庫實例里，此時就會報錯了，提示Mycat路由找不到對應的表，如下圖所示：

mysql> select ua.user_id, ua.contact, p.province, c.city, r.area , ua.address from tb_user_address ua ,tb_areas_city c , tb_areas_provinces p ,tb_areas_region r where ua.province_id = p.provinceid and ua.city_id = c.cityid and ua.town_id = r.areaid ;

tb_user_address、tb_areas_city這幾張表都是在數據庫中存在的，但是依舊報錯說找不到該表，回想一下，在我們分庫分表時，并沒有將這兩張表放在一個分片中，此時Mycat就不知道這兩張表究竟位于哪一個分片中，因此就會報錯找不到這張表。

解決辦法就是將要進行聯查的表在Mycat分表時，設置成全局表，全局表會在指定的分片節點上創建，可以指定多個分片節點，并數據也是一樣的，配置如下:

除了tb_user_address這張表以外，聯查的其他表都在一個分片里，因此我們只對tb_user_address這張表設置全局表即可。

            <table name="tb_user_address" dataNode="dn1,dn2" type="global"/>

雖然指定了全局表，即使重啟Mycat也不會生效，因為涉及到修改數據分布了，就需要將數據庫實例上的庫全部刪除，然后重新備份還原，非常麻煩，因此建議一開始分庫分表時，就將有聯查動作的表劃分到一個分片節點中，避免出錯。

配置全局表后，重新刪庫導入表之后，在每個分片節點上都會存在全局表，全局表很雞肋，后期設置需要重新刷Mycat配置，很麻煩，不建議使用，前期盡可能規劃好。

經過一系列刪庫還原，生效全局表后，聯查成功。

2.7.垂直分表完成

此時垂直分表已經完成了，說一些實戰性的經驗。

db_shopping庫的部分表分在了分片節點1上，部分表分在分片節點2上，以后有新表創建時，如果沒有在Mycat中為新表單獨進行配置，默認會被分片到邏輯庫關聯的分片節點上。

如果對于新表就要求說存儲在分片節點2上，那么在創建這張新表時，就在Mycat上配置好，然后重啟Mycat，最后在Mycat上創建這張新表，字段路由到分片節點2上。

一定要最初就規劃好每個分片鎖存儲的表，盡可能不使用全局表。