回覆列表
  • 1 # 此生唯一

    公司做了自己的分庫分表元件,下面就自己的經驗來看下分庫分表的優點和碰到的問題!

    何為分庫分表?採取一定的策略將大量的表資料分佈在不同的資料庫,表中實現資料的均衡儲存!

    分庫分表的背景:隨著資訊資料的急劇增長,單點資料庫會有宕機,或者單庫單表效能低下,查詢和儲存效率低的問題,使用分庫分表實現資料的分佈儲存,效能更好,適合現在資料量多,使用者需求高的特點!

    分庫分表的優點:資料分佈在不同的資料庫中,單表資料量低,查詢速度快!可以在每個節點搭建叢集防止資料丟失!

    分庫分表遇到的問題:

    1,多庫多表需要不重複的ID生成策略,但是資料重複!

    解決方案:UUID,全域性序列號等等!

    2,如果是按照hash等方式實現的分庫分表,可能難以擴充套件

    解決方案:1,使用時間段或者ID等進行劃分,可持續擴充套件(會帶來別的問題),2,資料重新遷移!

    3,連線查詢,統計等出現困難:

    解決方案:1,按照某個指定的分庫分表字段(ID)(分佈在同一個庫中)進行連線查詢!2,將主要欄位進行冗餘,方便統計和連線查詢!

    分庫分表的元件有很多,mycat是最流行的一個!

    怎麼使用mycat進行分庫分表?

    1,下載安裝mycat,配置環境變數!

    2,配置檔案(啟動記憶體等),使用命令列啟動和停止!

    3,建立多庫多表!

    4,選擇分庫分表策略(水平和垂直),演算法等!

    5,配置server.xml,schema.xml,rule.xml用於配置對映,規則等!

    6,連線測試!

    具體的mycat應用不是幾句話可以說的清的,最好是自己搭建環境,自己寫Demo測試,方能掌握!

    我的Demo還在寫,到時候分享,需要的朋友,敬請關注。。。

  • 2 # Java架構達人

    MySQL的使用場景中,讀寫分離只是方案中的一部分,想要擴充套件,勢必會用到分庫分表,可喜的是Mycat裡已經做到了,今天花時間測試了一下,感覺還不錯。

    關於分庫分表

    當然自己也理了一下,分庫分表的這些內容,如果分成幾個策略或者階段,大概有下面的幾種。

    最上面的第一種是直接拆表,比如資料庫db1下面有test1,test2,test3三個表,透過中介軟體看到的還是表test,裡面的資料做了這樣的拆分,能夠在一定程度上分解壓力,如果細細品來,和分割槽表的套路有些像。

    接下來的幾類也是不斷完善,把表test拆解到多個庫中,多個伺服器中,如果做了讀寫分離,全套的方案這樣的拆解改進還是很大的。如此來看,資料庫中介軟體做了很多應用和資料庫之間的很多事情,能夠流行起來除了技術原因還是有很多其他的因素。

    分庫分表的測試環境模擬

    如果要在一臺伺服器上測試分庫分表,而且要求架構方案要全面,作為技術可行性的一個判定參考,是否可以實現呢。

    如果模擬一主兩從的架構,模擬服務分佈在3臺伺服器上,這樣的方案需要建立9個例項,每個例項上有3個db需要分別拆分。

    大體的配置如下:

    master1: 埠33091

    (m1)slave1: 埠33092

    (m1)slave2: 埠33093

    master2: 埠33071

    (m2)slave1: 埠33072

    (m2)slave2: 埠33073

    master3: 埠33061

    (m3)slave1: 埠33062

    (m3)slave2: 埠33063

    畫個圖來說明一下,其中db1,db2,db3下面有若干表,需要做sharding

    所以我們需要模擬的就是這個事情。

    使用Mycat碰到的幾個小問題解惑

    使用Mycat的時候碰到了幾個小問題,感覺比較有代表性,記錄了一下。

    問題1:

    首先是使用Mycat連線到資料庫之後,如果不切換到具體的資料庫下,使用[資料庫名].[表名]的方式會丟擲下面的錯誤,可見整個過程中,Mycat攔截了SQL資訊做了過濾,在轉換的時候找不到目標路由。當然實際使用中,規範使用肯定不會有這個問題。

    mysql> select * from db1.shard_auto;

    ERROR 1064 (HY000): find no Route:select * from db1.shard_auto

    問題2:

    在配置了sharding策略之後,insert語句丟擲了下面的錯誤,這個是對語法的一個基本的要求。

    mysql> insert into shard_mod_long values(1,"aa",date);

    ERROR 1064 (HY000): partition table, insert must provide ColumnList

    問題3:

    如果sharding策略配置有誤,很可能出現表訪問正常,但是DML會有問題,提示資料衝突了。至於如何配置sharding,下面會講。

    mysql> select * from shard_mod_long;

    Empty set (0.00 sec)

    mysql> insert into shard_mod_long(ID,name,shard_date) values(1,"aa",current_date);

    ERROR 1105 (HY000): Duplicate entry "1" for key "PRIMARY"

    問題4:

    如果sharding的配置有誤,很可能出現多份冗餘資料。

    檢視執行計劃就一目瞭然,透過data_node可以看到資料指向了多個目標庫。

    mysql> explain insert into shard_auto(ID,name,shard_date) values(1,"aa",current_date);

    +-----------+------------------------------------------------+

    | DATA_NODE | SQL |

    +-----------+------------------------------------------------+

    | pxcNode11 | insert into shard_auto(ID,name,shard_date) values(1,"aa",current_date) |

    | pxcNode21 | insert into shard_auto(ID,name,shard_date) values(1,"aa",current_date) |

    | pxcNode31 | insert into shard_auto(ID,name,shard_date) values(1,"aa",current_date) |

    +-----------+------------------------------------------------+

    這種情況如果有一定的需求還是蠻不錯的,做sharding可惜了。問題就在於下面的這個table配置。

    <table name="shard_auto" primaryKey="ID" type="global" dataNode="pxcNode11,pxcNode21,pxcNode31" rule="auto-sharding-long" />

    需要去掉 type="global"的屬性,讓它sharding。

    Mycat裡面的sharding策略

    Mycat的分片策略很豐富,這個是超出自己的預期的,也是Mycat的一大亮點。

    大體分片規則如下,另外還有一些其他分片方式這裡不全部列舉:

    (1)分片列舉:sharding-by-intfile

    (2)主鍵範圍:auto-sharding-long

    (3)一致性hash:sharding-by-murmur

    (4)字串hash解析:sharding-by-stringhash

    (5)按日期(天)分片:sharding-by-date

    (6)按單月小時拆分:sharding-by-hour

    (7)自然月分片:sharding-by-month

    在開始之前,我們要建立下面的表來模擬幾個sharding的場景,表名根據需求可以改變。

    create table shard_test(ID int primary key, name varchar(20),shard_date date);

    主鍵範圍分片

    主鍵範圍分片是參考了主鍵值,按照主鍵值的分佈來分佈資料庫在不同的庫中,我們先在對應的sharding節點上建立同樣的表結構。

    關於sharding的策略,需要修改rule.xml檔案。

    常用的sharding策略已經在Mycat裡面實現了,如果要自行實現也可以定製。比如下面的規則,是基於主鍵欄位ID來做sharding,分佈的演算法是rang-long,引用了function rang-long,這個function是在對應的一個Java類中實現的。

    <tableRule name="auto-sharding-long">

    <rule>

    <columns>ID</columns>

    <algorithm>rang-long</algorithm>

    </rule>

    <function name="rang-long"

    class="io.mycat.route.function.AutoPartitionByLong">

    <property name="mapFile">autopartition-long.txt</property>

    當然主鍵的範圍是不固定的,可以根據需求來定製,比如按照一百萬為單位,或者1000位單位,檔案是 autopartition-long.txt 檔案的內容預設如下,模板裡是分為了3個分片,如果要定製更多的就需要繼續配置了,目前來看這個配置只能夠承載15億的資料量,可以根據需求繼續擴充套件定製。

    # range start-end ,data node index

    # K=1000,M=10000.

    0-500M=0

    500M-1000M=1

    1000M-1500M=2

    插入一些資料來驗證一下,我們可以檢視執行計劃來做基本的驗證,配置無誤,資料就根據規則流向了指定的資料庫下的表裡。

    mysql> explain insert into shard_auto(ID,name,shard_date) values(1,"aa",current_date);

    +-----------+------------------------------------------------+

    | DATA_NODE | SQL |

    +-----------+------------------------------------------------+

    | pxcNode11 | insert into shard_auto(ID,name,shard_date) values(1,"aa",current_date) |

    +-----------+------------------------------------------------+

    還有一個檢視sharding效果的小方法,比如我插入一個極大的值,保證和其他資料不在一個分片上,我們執行查詢語句兩次,結果會有點變化。

    sharing的效果

    mysql> select * from shard_auto;

    +---------+------+------------+

    | ID | name | shard_date |

    +---------+------+------------+

    | 1 | aa | 2017-09-06 |

    | 2 | bb | 2017-09-06 |

    | 5000001 | aa | 2017-09-06 |

    +---------+------+------------+

    3 rows in set (0.00 sec)

    稍作停頓,繼續執行。

    mysql> select * from shard_auto;

    +---------+------+------------+

    | ID | name | shard_date |

    +---------+------+------------+

    | 5000001 | aa | 2017-09-06 |

    | 1 | aa | 2017-09-06 |

    | 2 | bb | 2017-09-06 |

    +---------+------+------------+

    3 rows in set (0.01 sec)

    Hash分片

    Hash分片其實企業級應用尤其廣泛,我覺得一個原因是透過這種資料路由的方式,得到的資料情況是基本可控的,和業務的關聯起來比較直接。很多拆分方法都是根據mod方法來平均分佈資料。

    sharding的策略在rule.xml裡面配置,還是預設的mod-long規則,引用了演算法mod-long,這裡是根據sharding的節點數來做的,預設是3個。

    <tableRule name="mod-long">

    <rule>

    <columns>id</columns>

    <algorithm>mod-long</algorithm>

    </rule>

    </tableRule>

    <function name="mod-long">

    <property name="count">3</property>

    </function>

    比如檢視兩次insert的結果情況。

    mysql> explain insert into shard_mod_long(ID,name,shard_date) values(4,"dd",current_date);

    +-----------+------------------------------------------------+

    | DATA_NODE | SQL |

    +-----------+------------------------------------------------+

    | pxcNode22 | insert into shard_mod_long(ID,name,shard_date) values(4,"dd",current_date) |

    +-----------+------------------------------------------------+

    mysql> explain insert into shard_mod_long(ID,name,shard_date) values(5,"ee",current_date);

    +-----------+------------------------------------------------+

    | DATA_NODE | SQL |

    +-----------+------------------------------------------------+

    | pxcNode23 | insert into shard_mod_long(ID,name,shard_date) values(5,"ee",current_date) |

    +-----------+------------------------------------------------+

    可以看到資料還是遵循了節點的規律,平均分佈。

    至於schema.xml的配置,是整個分庫的核心,我索性也給出一個配置來,供參考。

    <?xml version="1.0"?>

    <!DOCTYPE mycat:schema SYSTEM "schema.dtd">

    <mycat:schema xmlns:mycat="http://io.mycat/">

    <schema name="db1" checkSQLschema="false" sqlMaxLimit="100" >

    <table name="shard_mod_long" primaryKey="ID" type="global" dataNode="pxcNode11,pxcNode21,pxcNode31" rule="mod-long" />

    <table name="shard_auto" primaryKey="ID" type="global" dataNode="pxcNode11,pxcNode21,pxcNode31" rule="auto-sharding-long" />

    </schema>

    <dataNode name="pxcNode11" dataHost="dtHost" database="db1" />

    <dataNode name="pxcNode21" dataHost="dtHost2" database="db1" />

    <dataNode name="pxcNode31" dataHost="dtHost3" database="db1" />

    <dataHost name="dtHost" maxCon="500" minCon="20" balance="1"

    writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">

    <heartbeat>show slave status</heartbeat>

    <writeHost host="hostMaster" url="192.168.163.128:33091" user="mycat_user" password="mycat" />

    </dataHost>

    <dataHost name="dtHost2" maxCon="500" minCon="20" balance="1"

    writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">

    <heartbeat>show slave status</heartbeat>

    <writeHost host="hostMaster" url="192.168.163.128:33071" user="mycat_user" password="mycat" />

    </dataHost>

    <dataHost name="dtHost3" maxCon="500" minCon="20" balance="1"

    writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">

    <heartbeat>show slave status</heartbeat>

    <writeHost host="hostMaster" url="192.168.163.128:33061" user="mycat_user" password="mycat" />

    </dataHost>

    </mycat:schema>

    =================================================================================================

    用Mycat,學會資料庫讀寫分離、分表分庫

    php疑難雜症鋪 2017-09-13 14:31

    用Mycat,學會資料庫讀寫分離、分表分庫

    系統開發中,資料庫是非常重要的一個點。除了程式的本身的最佳化,如:SQL語句最佳化、程式碼最佳化,資料庫的處理本身最佳化也是非常重要的。主從、熱備、分表分庫等都是系統發展遲早會遇到的技術問題問題。Mycat是一個廣受好評的資料庫中介軟體,已經在很多產品上進行使用了。希望透過這篇文章的介紹,能學會Mycat的使用。

    安裝

    Mycat官網:http://www.mycat.io/

    可以瞭解下Mycat的背景和應用情況,這樣使用起來比較有信心。

    Mycat下載地址:http://dl.mycat.io/

    官網有個文件,屬於詳細的介紹,初次入門,看起來比較花時間。

    下載:

    建議大家選擇 1.6-RELEASE 版本,畢竟是比較穩定的版本。

    安裝:

    根據不同的系統選擇不同的版本。包括linux、windows、mac,作者考慮還是非常周全的,當然,也有原始碼版的。(ps:原始碼版的下載後,只要配置正確,就可以正常執行除錯,這個贊一下。)

    Mycat的安裝其實只要解壓下載的目錄就可以了,非常簡單。

    安裝完成後,目錄如下:

    目錄說明binmycat命令,啟動、重啟、停止等catletcatlet為Mycat的一個擴充套件功能confMycat 配置資訊,重點關注libMycat引用的jar包,Mycat是java開發的logs日誌檔案,包括Mycat啟動的日誌和執行的日誌。

    配置

    Mycat的配置檔案都在conf目錄裡面,這裡介紹幾個常用的檔案:

    檔案說明server.xmlMycat的配置檔案,設定賬號、引數等schema.xmlMycat對應的物理資料庫和資料庫表的配置rule.xmlMycat分片(分庫分表)規則

    Mycat的架構其實很好理解,Mycat是代理,Mycat後面就是物理資料庫。和Web伺服器的Nginx類似。對於使用者來說,訪問的都是Mycat,不會接觸到後端的資料庫。

    我們現在做一個主從、讀寫分離,簡單分表的示例。結構如下圖:

    伺服器IP說明Mycat192.168.0.2mycat伺服器,連線資料庫時,連線此伺服器database1192.168.0.3物理資料庫1,真正儲存資料的資料庫database2192.168.0.4物理資料庫2,真正儲存資料的資料庫

    Mycat作為主資料庫中介軟體,肯定是與程式碼弱關聯的,所以程式碼是不用修改的,使用Mycat後,連線資料庫是不變的,預設埠是8066。連線方式和普通資料庫一樣,如:jdbc:mysql://192.168.0.2:8066/

    server.xml

    示例

    重點關注下面這段,其他預設即可。

    引數說明user使用者配置節點--name登入的使用者名稱,也就是連線Mycat的使用者名稱--password登入的密碼,也就是連線Mycat的密碼--schemas資料庫名,這裡會和schema.xml中的配置關聯,多個用逗號分開,例如需要這個使用者需要管理兩個資料庫db1,db2,則配置db1,dbs--privileges配置使用者針對表的增刪改查的許可權,具體見文件吧

    我這裡配置了一個賬號test 密碼也是test,針對資料庫lunch,讀寫許可權都有,沒有針對表做任何特殊的許可權。

    schema.xml

    schema.xml是最主要的配置項,首先看我的配置檔案。

    引數說明schema資料庫設定,此資料庫為邏輯資料庫,name與server.xml中schema對應dataNode分片資訊,也就是分庫相關配置dataHost物理資料庫,真正儲存資料的資料庫

    每個節點的屬性逐一說明:

    schema:

    屬性說明name邏輯資料庫名,與server.xml中的schema對應checkSQLschema資料庫字首相關設定,建議看文件,這裡暫時設為folsesqlMaxLimitselect 時預設的limit,避免查詢全表

    table:

    屬性說明name表名,物理資料庫中表名dataNode表儲存到哪些節點,多個節點用逗號分隔。節點為下文dataNode設定的nameprimaryKey主鍵欄位名,自動生成主鍵時需要設定autoIncrement是否自增rule分片規則名,具體規則下文rule詳細介紹

    dataNode

    屬性說明name節點名,與table中dataNode對應datahost物理資料庫名,與datahost中name對應database物理資料庫中資料庫名

    dataHost

    屬性說明name物理資料庫名,與dataNode中dataHost對應balance均衡負載的方式writeType寫入方式dbType資料庫型別heartbeat心跳檢測語句,注意語句結尾的分號要加。

    應用場景

    資料庫分表分庫

    配置如下:

    我在192.168.0.2、192.168.0.3均有資料庫lunch。

    lunchmenu、restaurant、userlunch、users這些表都只寫入節點dn1,也就是192.168.0.2這個服務,而dictionary寫入了dn1、dn2兩個節點,也就是192.168.0.2、192.168.0.3這兩臺伺服器。分片的規則為:mod-long。

    table中的rule屬性對應的就是rule.xml檔案中tableRule的name,具體有哪些分表和分庫的實現,建議還是看下文件。我這裡選擇的mod-long就是將資料平均拆分。因為我後端是兩臺物理庫,所以rule.xml中mod-long對應的function count為2,見下面部分程式碼:

    資料庫讀寫分離

    配置如下:

    這樣的配置與前一個示例配置改動如下:

    datahost也只有一臺,但是writehost總添加了readhost,balance改為1,表示讀寫分離。

    以上配置達到的效果就是102.168.0.2為主庫,192.168.0.3為從庫。

    注意:Mycat主從分離只是在讀的時候做了處理,寫入資料的時候,只會寫入到writehost,需要透過mycat的主從複製將資料複製到readhost,這個問題當時候我糾結了好久,資料寫入writehost後,readhost一直沒有資料,以為是自己配置的問題,後面才發現Mycat就沒有實現主從複製的功能,畢竟資料庫本身自帶的這個功能才是最高效穩定的。

    至於其他的場景,如同時主從和分表分庫也是支援的了,只要瞭解這個實現以後再去修改配置,都是可以實現的。而熱備及故障專業官方推薦使用haproxy配合一起使用,大家可以試試。

    使用

    Mycat的啟動也很簡單,啟動命令在Bin目錄:

    如果在啟動時發現異常,在logs目錄中檢視日誌。

    wrapper.log 為程式啟動的日誌,啟動時的問題看這個

    mycat.log 為指令碼執行時的日誌,SQL指令碼執行報錯後的具體錯誤內容,檢視這個檔案。mycat.log是最新的錯誤日誌,歷史日誌會根據時間生成目錄儲存。

    mycat啟動後,執行命令不成功,可能實際上配置有錯誤,導致後面的命令沒有很好的執行。

    Mycat帶來的最大好處就是使用是完全不用修改原有程式碼的,在mycat透過命令啟動後,你只需要將資料庫連線切換到Mycat的地址就可以了。如下面就可以進行連線了:

    連線成功後可以執行sql指令碼了。

    所以,可以直接透過sql管理工具(如:navicat、datagrip)連線,執行指令碼。我一直用datagrip來進行日常簡單的管理,這個很方便。

    Mycat還有一個管理的連線,埠號是9906.

    連線後可以根據管理命令檢視Mycat的執行情況,當然,喜歡UI管理方式的人,可以安裝一個Mycat-Web來進行管理,有興趣自行搜尋。

    簡而言之,開發中使用Mycat和直接使用Mysql機會沒有差別。

    常見問題

    Mycat是不是配置以後,就能完全解決分表分庫和讀寫分離問題?

    Mycat配合資料庫本身的複製功能,可以解決讀寫分離的問題,但是針對分表分庫的問題,不是完美的解決。或者說,至今為止,業界沒有完美的解決方案。

    分表分庫寫入能完美解決,但是,不能完美解決主要是聯表查詢的問題,Mycat支援兩個表聯表的查詢,多餘兩個表的查詢不支援。 其實,很多資料庫中介軟體關於分表分庫後查詢的問題,都是需要自己實現的,而且節本都不支援聯表查詢,Mycat已經算做地非常先進了。

    分表分庫的後聯表查詢問題,大家透過合理資料庫設計來避免。

    Mycat支援哪些資料庫,其他平臺如 .net、PHP能用嗎?

    官方說了,支援的資料庫包括MySQL、SQL Server、Oracle、DB2、PostgreSQL 等主流資料庫,很贊。

    儘量用Mysql,我試過SQL Server,會有些小問題,因為部分語法有點差異。

    Mycat 非JAVA平臺如 .net、PHP能用嗎?

    可以用。這一點MyCat做的也很棒。

  • 3 # 碼農小胖哥

    分庫/分表

    顧名思義,分庫分表就是按照一定的規則,對原有的資料庫和表進行拆分,把一個數據庫分成多個庫,把一張表拆分成為多張表。

    為什麼要分庫/分表

    隨著時間和業務的發展,資料庫和表中的資料量會越來越多,對硬體特別是磁碟消耗是越來越大,磁碟定址、IO操作、系統記憶體都有很大的開銷,為了提升資料庫操作的效率,因此以空間換時間。打個比方,你把五顏六色的玻璃球放到一個籃子裡,如果去找各種顏色球的數量是不是很麻煩。如果按照顏色分開放,找綠色的球直接去綠色區,不用顧及其他顏色,是不是效率就高了很多。就是這個原理

    垂直分庫/分表

    垂直劃分資料庫是根據業務進行劃分,將一張表的幾個欄位分成幾張表,把大表變成小表,把一個大庫按照業務 拆分幾個專門庫,比如商品,按照商品類別,商品詳情,訂單詳情,訂單 ,劃分到 商品庫,訂單庫中 。透過降低單庫(表)的大小來提高效能 放入不同的庫中。

    水平分庫/分表

    水平劃分是根據一定規則,例如時間或hash值等進行資料的拆分。這種拆分都是行級別拆分,也就是所有拆分的都是完整的一條資料。比如把使用者表按照年份拆分2018年註冊的使用者,2019年的使用者,2018之前註冊的使用者。

    MyCat

    Mycat是一款開源的資料庫中介軟體,主要是用作資料庫代理,也就是消費資料庫資料的消費方不直接接觸資料庫,透過mycat來代理訪問。就像明星一樣,你有商演想找明星,不會直接找到明星,會找到經紀人,經紀人來談具體事宜,經紀人再告訴明星。

    Mycat的原理中最重要是在sql執行前可以攔截處理一些邏輯,它攔截了使用者傳送過來的SQL語句,首先對SQL語句做了一些特定的分析:如分片分析、路由分析、讀寫分離分析、快取分析等,然後將此SQL發往後端的真實資料庫,並將返回的結果做適當的處理,最終再返回給使用者。因此對於分庫分表來說把分庫分表的規則寫入攔截器,攔截器透過分析來指向真實的地址獲取資料,還可以將資料進行邏輯處理後返回消費方。這樣就完成了分庫分表的操作。這樣就達成了對開發無感知的目的,避免分庫分表造成了路由邏輯的編寫。

    以下就是網上找來的mycat分庫分表的示意圖,相信很容易看懂。

  • 4 # 此生唯一

    公司做了自己的分庫分表元件,下面就自己的經驗來看下分庫分表的優點和碰到的問題!

    何為分庫分表?採取一定的策略將大量的表資料分佈在不同的資料庫,表中實現資料的均衡儲存!

    分庫分表的背景:隨著資訊資料的急劇增長,單點資料庫會有宕機,或者單庫單表效能低下,查詢和儲存效率低的問題,使用分庫分表實現資料的分佈儲存,效能更好,適合現在資料量多,使用者需求高的特點!

    分庫分表的優點:資料分佈在不同的資料庫中,單表資料量低,查詢速度快!可以在每個節點搭建叢集防止資料丟失!

    分庫分表遇到的問題:

    1,多庫多表需要不重複的ID生成策略,但是資料重複!

    解決方案:UUID,全域性序列號等等!

    2,如果是按照hash等方式實現的分庫分表,可能難以擴充套件

    解決方案:1,使用時間段或者ID等進行劃分,可持續擴充套件(會帶來別的問題),2,資料重新遷移!

    3,連線查詢,統計等出現困難:

    解決方案:1,按照某個指定的分庫分表字段(ID)(分佈在同一個庫中)進行連線查詢!2,將主要欄位進行冗餘,方便統計和連線查詢!

    分庫分表的元件有很多,mycat是最流行的一個!

    怎麼使用mycat進行分庫分表?

    1,下載安裝mycat,配置環境變數!

    2,配置檔案(啟動記憶體等),使用命令列啟動和停止!

    3,建立多庫多表!

    4,選擇分庫分表策略(水平和垂直),演算法等!

    5,配置server.xml,schema.xml,rule.xml用於配置對映,規則等!

    6,連線測試!

    具體的mycat應用不是幾句話可以說的清的,最好是自己搭建環境,自己寫Demo測試,方能掌握!

    我的Demo還在寫,到時候分享,需要的朋友,敬請關注。。。

  • 5 # Java架構達人

    MySQL的使用場景中,讀寫分離只是方案中的一部分,想要擴充套件,勢必會用到分庫分表,可喜的是Mycat裡已經做到了,今天花時間測試了一下,感覺還不錯。

    關於分庫分表

    當然自己也理了一下,分庫分表的這些內容,如果分成幾個策略或者階段,大概有下面的幾種。

    最上面的第一種是直接拆表,比如資料庫db1下面有test1,test2,test3三個表,透過中介軟體看到的還是表test,裡面的資料做了這樣的拆分,能夠在一定程度上分解壓力,如果細細品來,和分割槽表的套路有些像。

    接下來的幾類也是不斷完善,把表test拆解到多個庫中,多個伺服器中,如果做了讀寫分離,全套的方案這樣的拆解改進還是很大的。如此來看,資料庫中介軟體做了很多應用和資料庫之間的很多事情,能夠流行起來除了技術原因還是有很多其他的因素。

    分庫分表的測試環境模擬

    如果要在一臺伺服器上測試分庫分表,而且要求架構方案要全面,作為技術可行性的一個判定參考,是否可以實現呢。

    如果模擬一主兩從的架構,模擬服務分佈在3臺伺服器上,這樣的方案需要建立9個例項,每個例項上有3個db需要分別拆分。

    大體的配置如下:

    master1: 埠33091

    (m1)slave1: 埠33092

    (m1)slave2: 埠33093

    master2: 埠33071

    (m2)slave1: 埠33072

    (m2)slave2: 埠33073

    master3: 埠33061

    (m3)slave1: 埠33062

    (m3)slave2: 埠33063

    畫個圖來說明一下,其中db1,db2,db3下面有若干表,需要做sharding

    所以我們需要模擬的就是這個事情。

    使用Mycat碰到的幾個小問題解惑

    使用Mycat的時候碰到了幾個小問題,感覺比較有代表性,記錄了一下。

    問題1:

    首先是使用Mycat連線到資料庫之後,如果不切換到具體的資料庫下,使用[資料庫名].[表名]的方式會丟擲下面的錯誤,可見整個過程中,Mycat攔截了SQL資訊做了過濾,在轉換的時候找不到目標路由。當然實際使用中,規範使用肯定不會有這個問題。

    mysql> select * from db1.shard_auto;

    ERROR 1064 (HY000): find no Route:select * from db1.shard_auto

    問題2:

    在配置了sharding策略之後,insert語句丟擲了下面的錯誤,這個是對語法的一個基本的要求。

    mysql> insert into shard_mod_long values(1,"aa",date);

    ERROR 1064 (HY000): partition table, insert must provide ColumnList

    問題3:

    如果sharding策略配置有誤,很可能出現表訪問正常,但是DML會有問題,提示資料衝突了。至於如何配置sharding,下面會講。

    mysql> select * from shard_mod_long;

    Empty set (0.00 sec)

    mysql> insert into shard_mod_long(ID,name,shard_date) values(1,"aa",current_date);

    ERROR 1105 (HY000): Duplicate entry "1" for key "PRIMARY"

    問題4:

    如果sharding的配置有誤,很可能出現多份冗餘資料。

    檢視執行計劃就一目瞭然,透過data_node可以看到資料指向了多個目標庫。

    mysql> explain insert into shard_auto(ID,name,shard_date) values(1,"aa",current_date);

    +-----------+------------------------------------------------+

    | DATA_NODE | SQL |

    +-----------+------------------------------------------------+

    | pxcNode11 | insert into shard_auto(ID,name,shard_date) values(1,"aa",current_date) |

    | pxcNode21 | insert into shard_auto(ID,name,shard_date) values(1,"aa",current_date) |

    | pxcNode31 | insert into shard_auto(ID,name,shard_date) values(1,"aa",current_date) |

    +-----------+------------------------------------------------+

    這種情況如果有一定的需求還是蠻不錯的,做sharding可惜了。問題就在於下面的這個table配置。

    <table name="shard_auto" primaryKey="ID" type="global" dataNode="pxcNode11,pxcNode21,pxcNode31" rule="auto-sharding-long" />

    需要去掉 type="global"的屬性,讓它sharding。

    Mycat裡面的sharding策略

    Mycat的分片策略很豐富,這個是超出自己的預期的,也是Mycat的一大亮點。

    大體分片規則如下,另外還有一些其他分片方式這裡不全部列舉:

    (1)分片列舉:sharding-by-intfile

    (2)主鍵範圍:auto-sharding-long

    (3)一致性hash:sharding-by-murmur

    (4)字串hash解析:sharding-by-stringhash

    (5)按日期(天)分片:sharding-by-date

    (6)按單月小時拆分:sharding-by-hour

    (7)自然月分片:sharding-by-month

    在開始之前,我們要建立下面的表來模擬幾個sharding的場景,表名根據需求可以改變。

    create table shard_test(ID int primary key, name varchar(20),shard_date date);

    主鍵範圍分片

    主鍵範圍分片是參考了主鍵值,按照主鍵值的分佈來分佈資料庫在不同的庫中,我們先在對應的sharding節點上建立同樣的表結構。

    關於sharding的策略,需要修改rule.xml檔案。

    常用的sharding策略已經在Mycat裡面實現了,如果要自行實現也可以定製。比如下面的規則,是基於主鍵欄位ID來做sharding,分佈的演算法是rang-long,引用了function rang-long,這個function是在對應的一個Java類中實現的。

    <tableRule name="auto-sharding-long">

    <rule>

    <columns>ID</columns>

    <algorithm>rang-long</algorithm>

    </rule>

    <function name="rang-long"

    class="io.mycat.route.function.AutoPartitionByLong">

    <property name="mapFile">autopartition-long.txt</property>

    當然主鍵的範圍是不固定的,可以根據需求來定製,比如按照一百萬為單位,或者1000位單位,檔案是 autopartition-long.txt 檔案的內容預設如下,模板裡是分為了3個分片,如果要定製更多的就需要繼續配置了,目前來看這個配置只能夠承載15億的資料量,可以根據需求繼續擴充套件定製。

    # range start-end ,data node index

    # K=1000,M=10000.

    0-500M=0

    500M-1000M=1

    1000M-1500M=2

    插入一些資料來驗證一下,我們可以檢視執行計劃來做基本的驗證,配置無誤,資料就根據規則流向了指定的資料庫下的表裡。

    mysql> explain insert into shard_auto(ID,name,shard_date) values(1,"aa",current_date);

    +-----------+------------------------------------------------+

    | DATA_NODE | SQL |

    +-----------+------------------------------------------------+

    | pxcNode11 | insert into shard_auto(ID,name,shard_date) values(1,"aa",current_date) |

    +-----------+------------------------------------------------+

    還有一個檢視sharding效果的小方法,比如我插入一個極大的值,保證和其他資料不在一個分片上,我們執行查詢語句兩次,結果會有點變化。

    sharing的效果

    mysql> select * from shard_auto;

    +---------+------+------------+

    | ID | name | shard_date |

    +---------+------+------------+

    | 1 | aa | 2017-09-06 |

    | 2 | bb | 2017-09-06 |

    | 5000001 | aa | 2017-09-06 |

    +---------+------+------------+

    3 rows in set (0.00 sec)

    稍作停頓,繼續執行。

    mysql> select * from shard_auto;

    +---------+------+------------+

    | ID | name | shard_date |

    +---------+------+------------+

    | 5000001 | aa | 2017-09-06 |

    | 1 | aa | 2017-09-06 |

    | 2 | bb | 2017-09-06 |

    +---------+------+------------+

    3 rows in set (0.01 sec)

    Hash分片

    Hash分片其實企業級應用尤其廣泛,我覺得一個原因是透過這種資料路由的方式,得到的資料情況是基本可控的,和業務的關聯起來比較直接。很多拆分方法都是根據mod方法來平均分佈資料。

    sharding的策略在rule.xml裡面配置,還是預設的mod-long規則,引用了演算法mod-long,這裡是根據sharding的節點數來做的,預設是3個。

    <tableRule name="mod-long">

    <rule>

    <columns>id</columns>

    <algorithm>mod-long</algorithm>

    </rule>

    </tableRule>

    <function name="mod-long">

    <property name="count">3</property>

    </function>

    比如檢視兩次insert的結果情況。

    mysql> explain insert into shard_mod_long(ID,name,shard_date) values(4,"dd",current_date);

    +-----------+------------------------------------------------+

    | DATA_NODE | SQL |

    +-----------+------------------------------------------------+

    | pxcNode22 | insert into shard_mod_long(ID,name,shard_date) values(4,"dd",current_date) |

    +-----------+------------------------------------------------+

    mysql> explain insert into shard_mod_long(ID,name,shard_date) values(5,"ee",current_date);

    +-----------+------------------------------------------------+

    | DATA_NODE | SQL |

    +-----------+------------------------------------------------+

    | pxcNode23 | insert into shard_mod_long(ID,name,shard_date) values(5,"ee",current_date) |

    +-----------+------------------------------------------------+

    可以看到資料還是遵循了節點的規律,平均分佈。

    至於schema.xml的配置,是整個分庫的核心,我索性也給出一個配置來,供參考。

    <?xml version="1.0"?>

    <!DOCTYPE mycat:schema SYSTEM "schema.dtd">

    <mycat:schema xmlns:mycat="http://io.mycat/">

    <schema name="db1" checkSQLschema="false" sqlMaxLimit="100" >

    <table name="shard_mod_long" primaryKey="ID" type="global" dataNode="pxcNode11,pxcNode21,pxcNode31" rule="mod-long" />

    <table name="shard_auto" primaryKey="ID" type="global" dataNode="pxcNode11,pxcNode21,pxcNode31" rule="auto-sharding-long" />

    </schema>

    <dataNode name="pxcNode11" dataHost="dtHost" database="db1" />

    <dataNode name="pxcNode21" dataHost="dtHost2" database="db1" />

    <dataNode name="pxcNode31" dataHost="dtHost3" database="db1" />

    <dataHost name="dtHost" maxCon="500" minCon="20" balance="1"

    writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">

    <heartbeat>show slave status</heartbeat>

    <writeHost host="hostMaster" url="192.168.163.128:33091" user="mycat_user" password="mycat" />

    </dataHost>

    <dataHost name="dtHost2" maxCon="500" minCon="20" balance="1"

    writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">

    <heartbeat>show slave status</heartbeat>

    <writeHost host="hostMaster" url="192.168.163.128:33071" user="mycat_user" password="mycat" />

    </dataHost>

    <dataHost name="dtHost3" maxCon="500" minCon="20" balance="1"

    writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">

    <heartbeat>show slave status</heartbeat>

    <writeHost host="hostMaster" url="192.168.163.128:33061" user="mycat_user" password="mycat" />

    </dataHost>

    </mycat:schema>

    =================================================================================================

    用Mycat,學會資料庫讀寫分離、分表分庫

    php疑難雜症鋪 2017-09-13 14:31

    用Mycat,學會資料庫讀寫分離、分表分庫

    系統開發中,資料庫是非常重要的一個點。除了程式的本身的最佳化,如:SQL語句最佳化、程式碼最佳化,資料庫的處理本身最佳化也是非常重要的。主從、熱備、分表分庫等都是系統發展遲早會遇到的技術問題問題。Mycat是一個廣受好評的資料庫中介軟體,已經在很多產品上進行使用了。希望透過這篇文章的介紹,能學會Mycat的使用。

    安裝

    Mycat官網:http://www.mycat.io/

    可以瞭解下Mycat的背景和應用情況,這樣使用起來比較有信心。

    Mycat下載地址:http://dl.mycat.io/

    官網有個文件,屬於詳細的介紹,初次入門,看起來比較花時間。

    下載:

    建議大家選擇 1.6-RELEASE 版本,畢竟是比較穩定的版本。

    安裝:

    根據不同的系統選擇不同的版本。包括linux、windows、mac,作者考慮還是非常周全的,當然,也有原始碼版的。(ps:原始碼版的下載後,只要配置正確,就可以正常執行除錯,這個贊一下。)

    Mycat的安裝其實只要解壓下載的目錄就可以了,非常簡單。

    安裝完成後,目錄如下:

    目錄說明binmycat命令,啟動、重啟、停止等catletcatlet為Mycat的一個擴充套件功能confMycat 配置資訊,重點關注libMycat引用的jar包,Mycat是java開發的logs日誌檔案,包括Mycat啟動的日誌和執行的日誌。

    配置

    Mycat的配置檔案都在conf目錄裡面,這裡介紹幾個常用的檔案:

    檔案說明server.xmlMycat的配置檔案,設定賬號、引數等schema.xmlMycat對應的物理資料庫和資料庫表的配置rule.xmlMycat分片(分庫分表)規則

    Mycat的架構其實很好理解,Mycat是代理,Mycat後面就是物理資料庫。和Web伺服器的Nginx類似。對於使用者來說,訪問的都是Mycat,不會接觸到後端的資料庫。

    我們現在做一個主從、讀寫分離,簡單分表的示例。結構如下圖:

    伺服器IP說明Mycat192.168.0.2mycat伺服器,連線資料庫時,連線此伺服器database1192.168.0.3物理資料庫1,真正儲存資料的資料庫database2192.168.0.4物理資料庫2,真正儲存資料的資料庫

    Mycat作為主資料庫中介軟體,肯定是與程式碼弱關聯的,所以程式碼是不用修改的,使用Mycat後,連線資料庫是不變的,預設埠是8066。連線方式和普通資料庫一樣,如:jdbc:mysql://192.168.0.2:8066/

    server.xml

    示例

    重點關注下面這段,其他預設即可。

    引數說明user使用者配置節點--name登入的使用者名稱,也就是連線Mycat的使用者名稱--password登入的密碼,也就是連線Mycat的密碼--schemas資料庫名,這裡會和schema.xml中的配置關聯,多個用逗號分開,例如需要這個使用者需要管理兩個資料庫db1,db2,則配置db1,dbs--privileges配置使用者針對表的增刪改查的許可權,具體見文件吧

    我這裡配置了一個賬號test 密碼也是test,針對資料庫lunch,讀寫許可權都有,沒有針對表做任何特殊的許可權。

    schema.xml

    schema.xml是最主要的配置項,首先看我的配置檔案。

    引數說明schema資料庫設定,此資料庫為邏輯資料庫,name與server.xml中schema對應dataNode分片資訊,也就是分庫相關配置dataHost物理資料庫,真正儲存資料的資料庫

    每個節點的屬性逐一說明:

    schema:

    屬性說明name邏輯資料庫名,與server.xml中的schema對應checkSQLschema資料庫字首相關設定,建議看文件,這裡暫時設為folsesqlMaxLimitselect 時預設的limit,避免查詢全表

    table:

    屬性說明name表名,物理資料庫中表名dataNode表儲存到哪些節點,多個節點用逗號分隔。節點為下文dataNode設定的nameprimaryKey主鍵欄位名,自動生成主鍵時需要設定autoIncrement是否自增rule分片規則名,具體規則下文rule詳細介紹

    dataNode

    屬性說明name節點名,與table中dataNode對應datahost物理資料庫名,與datahost中name對應database物理資料庫中資料庫名

    dataHost

    屬性說明name物理資料庫名,與dataNode中dataHost對應balance均衡負載的方式writeType寫入方式dbType資料庫型別heartbeat心跳檢測語句,注意語句結尾的分號要加。

    應用場景

    資料庫分表分庫

    配置如下:

    我在192.168.0.2、192.168.0.3均有資料庫lunch。

    lunchmenu、restaurant、userlunch、users這些表都只寫入節點dn1,也就是192.168.0.2這個服務,而dictionary寫入了dn1、dn2兩個節點,也就是192.168.0.2、192.168.0.3這兩臺伺服器。分片的規則為:mod-long。

    table中的rule屬性對應的就是rule.xml檔案中tableRule的name,具體有哪些分表和分庫的實現,建議還是看下文件。我這裡選擇的mod-long就是將資料平均拆分。因為我後端是兩臺物理庫,所以rule.xml中mod-long對應的function count為2,見下面部分程式碼:

    資料庫讀寫分離

    配置如下:

    這樣的配置與前一個示例配置改動如下:

    datahost也只有一臺,但是writehost總添加了readhost,balance改為1,表示讀寫分離。

    以上配置達到的效果就是102.168.0.2為主庫,192.168.0.3為從庫。

    注意:Mycat主從分離只是在讀的時候做了處理,寫入資料的時候,只會寫入到writehost,需要透過mycat的主從複製將資料複製到readhost,這個問題當時候我糾結了好久,資料寫入writehost後,readhost一直沒有資料,以為是自己配置的問題,後面才發現Mycat就沒有實現主從複製的功能,畢竟資料庫本身自帶的這個功能才是最高效穩定的。

    至於其他的場景,如同時主從和分表分庫也是支援的了,只要瞭解這個實現以後再去修改配置,都是可以實現的。而熱備及故障專業官方推薦使用haproxy配合一起使用,大家可以試試。

    使用

    Mycat的啟動也很簡單,啟動命令在Bin目錄:

    如果在啟動時發現異常,在logs目錄中檢視日誌。

    wrapper.log 為程式啟動的日誌,啟動時的問題看這個

    mycat.log 為指令碼執行時的日誌,SQL指令碼執行報錯後的具體錯誤內容,檢視這個檔案。mycat.log是最新的錯誤日誌,歷史日誌會根據時間生成目錄儲存。

    mycat啟動後,執行命令不成功,可能實際上配置有錯誤,導致後面的命令沒有很好的執行。

    Mycat帶來的最大好處就是使用是完全不用修改原有程式碼的,在mycat透過命令啟動後,你只需要將資料庫連線切換到Mycat的地址就可以了。如下面就可以進行連線了:

    連線成功後可以執行sql指令碼了。

    所以,可以直接透過sql管理工具(如:navicat、datagrip)連線,執行指令碼。我一直用datagrip來進行日常簡單的管理,這個很方便。

    Mycat還有一個管理的連線,埠號是9906.

    連線後可以根據管理命令檢視Mycat的執行情況,當然,喜歡UI管理方式的人,可以安裝一個Mycat-Web來進行管理,有興趣自行搜尋。

    簡而言之,開發中使用Mycat和直接使用Mysql機會沒有差別。

    常見問題

    Mycat是不是配置以後,就能完全解決分表分庫和讀寫分離問題?

    Mycat配合資料庫本身的複製功能,可以解決讀寫分離的問題,但是針對分表分庫的問題,不是完美的解決。或者說,至今為止,業界沒有完美的解決方案。

    分表分庫寫入能完美解決,但是,不能完美解決主要是聯表查詢的問題,Mycat支援兩個表聯表的查詢,多餘兩個表的查詢不支援。 其實,很多資料庫中介軟體關於分表分庫後查詢的問題,都是需要自己實現的,而且節本都不支援聯表查詢,Mycat已經算做地非常先進了。

    分表分庫的後聯表查詢問題,大家透過合理資料庫設計來避免。

    Mycat支援哪些資料庫,其他平臺如 .net、PHP能用嗎?

    官方說了,支援的資料庫包括MySQL、SQL Server、Oracle、DB2、PostgreSQL 等主流資料庫,很贊。

    儘量用Mysql,我試過SQL Server,會有些小問題,因為部分語法有點差異。

    Mycat 非JAVA平臺如 .net、PHP能用嗎?

    可以用。這一點MyCat做的也很棒。

  • 6 # 碼農小胖哥

    分庫/分表

    顧名思義,分庫分表就是按照一定的規則,對原有的資料庫和表進行拆分,把一個數據庫分成多個庫,把一張表拆分成為多張表。

    為什麼要分庫/分表

    隨著時間和業務的發展,資料庫和表中的資料量會越來越多,對硬體特別是磁碟消耗是越來越大,磁碟定址、IO操作、系統記憶體都有很大的開銷,為了提升資料庫操作的效率,因此以空間換時間。打個比方,你把五顏六色的玻璃球放到一個籃子裡,如果去找各種顏色球的數量是不是很麻煩。如果按照顏色分開放,找綠色的球直接去綠色區,不用顧及其他顏色,是不是效率就高了很多。就是這個原理

    垂直分庫/分表

    垂直劃分資料庫是根據業務進行劃分,將一張表的幾個欄位分成幾張表,把大表變成小表,把一個大庫按照業務 拆分幾個專門庫,比如商品,按照商品類別,商品詳情,訂單詳情,訂單 ,劃分到 商品庫,訂單庫中 。透過降低單庫(表)的大小來提高效能 放入不同的庫中。

    水平分庫/分表

    水平劃分是根據一定規則,例如時間或hash值等進行資料的拆分。這種拆分都是行級別拆分,也就是所有拆分的都是完整的一條資料。比如把使用者表按照年份拆分2018年註冊的使用者,2019年的使用者,2018之前註冊的使用者。

    MyCat

    Mycat是一款開源的資料庫中介軟體,主要是用作資料庫代理,也就是消費資料庫資料的消費方不直接接觸資料庫,透過mycat來代理訪問。就像明星一樣,你有商演想找明星,不會直接找到明星,會找到經紀人,經紀人來談具體事宜,經紀人再告訴明星。

    Mycat的原理中最重要是在sql執行前可以攔截處理一些邏輯,它攔截了使用者傳送過來的SQL語句,首先對SQL語句做了一些特定的分析:如分片分析、路由分析、讀寫分離分析、快取分析等,然後將此SQL發往後端的真實資料庫,並將返回的結果做適當的處理,最終再返回給使用者。因此對於分庫分表來說把分庫分表的規則寫入攔截器,攔截器透過分析來指向真實的地址獲取資料,還可以將資料進行邏輯處理後返回消費方。這樣就完成了分庫分表的操作。這樣就達成了對開發無感知的目的,避免分庫分表造成了路由邏輯的編寫。

    以下就是網上找來的mycat分庫分表的示意圖,相信很容易看懂。

  • 中秋節和大豐收的關聯?
  • 請問,當今社會一個月要賺多少錢才能有幸福感?