Linux MySQL分庫分表之Mycat

介紹

背景

  • 表的個數達到了幾百千萬張表時,眾多的業務模塊都訪問這個數據庫,壓力會比較大,考慮對其進行分庫
  • 表的數據達到幾千萬級別,在做很多操作都比較吃力,考慮對其進行分庫或分表

數據切分(sharding)方案

  數據的切分(Sharding)根據其切分規則的類型,可以分為兩種切分模式:

  • 垂直切分:按照業務模塊進行切分,將不同模塊的表切分到不同的數據庫中
  • 水平切分,將一張大表按照一定的切分規則,按照行切分成不同的表或者切分到不同的庫中

如何理解垂直切分?

  垂直分庫:主要解決的問題是單個數據庫中[數據表]過多問題

  垂直分表:主要解決的問題是單個中[過多問題(將一張大表,拆分不同的關聯表)。

如何理解水平切分?

  水平切分主要解決的問題就是對於[單表數據量過大]的問題(1000W以上數據性能會有所下降)

切分原則

  1. 能不切盡量不要切分
  2. 如果要切分一定要選擇合適的切分規則,提前規劃好
  3. 數據切分盡量通過冗餘或表分組(Table Group)來降低跨庫Join的可能
  4. 由於數據庫中間件對數據Join實現的優劣難以把握,而且實現高性能難度極大,業務讀取盡量少使用多表Join

分庫分表之後帶來問題?

  1. 跨庫Join:訂單表需要關聯會員信息(訂單表和會員表拆分為兩個庫的表)
    1. 應用層由一個查詢拆分為多個
    2. 全局表,每個庫都存儲相同的數據,比如字典表、地址表
    3. 字段冗餘
    4. Mycat技術可以實現跨庫Join,只能實現2張表跨庫Join
  2. 分佈式事務(Mycat沒有很好實現分佈式事務)
    1. 強一致性(互聯網項目不推薦,性能不好)
    2. 最終一致性(異步方式去實現,需要通過日誌信息)
  3. 主鍵問題(保證ID的連續性和唯一性)
    1. UUID(性能不好)
    2. redis incr命令
    3. zookeeper
    4. 雪花算法
  4. 跨庫進行排序問題
    1. 在應用層進行排序

Mycat應用

官網鏈接

點我直達

Mycat核心概念

  • Schema:由它制定邏輯數據庫(相當於MySQL的database數據庫)
  • Table:邏輯表(相當於MySQL的table表)
  • DataNode:真正存儲數據的物理節點
  • DataHost:存儲節點所在的數據庫主機(指定MySQL數據庫的連接信息)
  • User:MyCat的用戶(類似於MySQL的用戶,支持多用戶)

MyCat主要解決的問題

  • 海量數據存儲
  • 查詢優化

Mycat對數據庫的支持

Mycat安裝

安裝要求

  • jdk:要求jdk必須是1.7及以上版本 (我使用的是jdk 1.8

  • Mysql:推薦mysql是5.5以上版本(我使用的是mysql 5.7

安裝jdk

具體教程:點我直達

Mcat下載

下載鏈接:點我直達

百度雲盤地址:https://pan.baidu.com/s/14A3BAwnBRGZppc3AicF5Hw  密碼: gkrp

解壓

修改配置文件

路徑:/cyb/soft/mycat/conf

server.xml

用途:用於配置用戶信息

<?xml version="1.0" encoding="UTF-8"?>
<!-- - - Licensed under the Apache License, Version 2.0 (the "License"); 
    - you may not use this file except in compliance with the License. - You 
    may obtain a copy of the License at - - http://www.apache.org/licenses/LICENSE-2.0 
    - - Unless required by applicable law or agreed to in writing, software - 
    distributed under the License is distributed on an "AS IS" BASIS, - WITHOUT 
    WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - See the 
    License for the specific language governing permissions and - limitations 
    under the License. -->
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">
    <system>
    <property name="useSqlStat">0</property>  <!-- 1為開啟實時統計、0為關閉 -->
    <property name="useGlobleTableCheck">0</property>  <!-- 1為開啟全加班一致性檢測、0為關閉 -->

        <property name="sequnceHandlerType">2</property>
      <!--  <property name="useCompression">1</property>--> <!--1為開啟mysql壓縮協議-->
        <!--  <property name="fakeMySQLVersion">5.6.20</property>--> <!--設置模擬的MySQL版本號-->
    <!-- <property name="processorBufferChunk">40960</property> -->
    <!-- 
    <property name="processors">1</property> 
    <property name="processorExecutor">32</property> 
     -->
        <!--默認為type 0: DirectByteBufferPool | type 1 ByteBufferArena-->
        <property name="processorBufferPoolType">0</property>
        <!--默認是65535 64K 用於sql解析時最大文本長度 -->
        <!--<property name="maxStringLiteralLength">65535</property>-->
        <!--<property name="sequnceHandlerType">0</property>-->
        <!--<property name="backSocketNoDelay">1</property>-->
        <!--<property name="frontSocketNoDelay">1</property>-->
        <!--<property name="processorExecutor">16</property>-->
        <!--
            <property name="serverPort">8066</property> <property name="managerPort">9066</property> 
            <property name="idleTimeout">300000</property> <property name="bindIp">0.0.0.0</property> 
            <property name="frontWriteQueueSize">4096</property> <property name="processors">32</property> -->
        <!--分佈式事務開關,0為不過濾分佈式事務,1為過濾分佈式事務(如果分佈式事務內只涉及全局表,則不過濾),2為不過濾分佈式事務,但是記錄分佈式事務日誌-->
        <property name="handleDistributedTransactions">0</property>
        
            <!--
            off heap for merge/order/group/limit      1開啟   0關閉
        -->
        <property name="useOffHeapForMerge">1</property>

        <!--
            單位為m
        -->
        <property name="memoryPageSize">1m</property>

        <!--
            單位為k
        -->
        <property name="spillsFileBufferSize">1k</property>

        <property name="useStreamOutput">0</property>

        <!--
            單位為m
        -->
        <property name="systemReserveMemorySize">384m</property>


        <!--是否採用zookeeper協調切換  -->
        <property name="useZKSwitch">true</property>


    </system>
    
    <!-- 全局SQL防火牆設置 -->
    <!-- 
    <firewall> 
       <whitehost>
          <host host="127.0.0.1" user="mycat"/>
          <host host="127.0.0.2" user="mycat"/>
       </whitehost>
       <blacklist check="false">
       </blacklist>
    </firewall>
    -->
    
    <user name="root">
        <property name="password">root</property>
        <property name="schemas">TESTDB</property>
        
        <!-- 表級 DML 權限設置 -->
        <!--         
        <privileges check="false">
            <schema name="TESTDB" dml="0110" >
                <table name="tb01" dml="0000"></table>
                <table name="tb02" dml="1111"></table>
            </schema>
        </privileges>        
         -->
    </user>

    <user name="user">
        <property name="password">user</property>
        <property name="schemas">TESTDB</property>
        <property name="readOnly">true</property>
    </user>

</mycat:server>

schema.xml

用途:管理邏輯表

為了演示方便,刪掉一些不必要的標籤,標籤詳細用法:點我直達

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

    <schema name="TESTDB" checkSQLschema="true" sqlMaxLimit="100">
        <!-- auto sharding by id (long) -->
        <table name="cyb_test" dataNode="dn1,dn2,dn3" rule="mod-long" />
    </schema>
    <dataNode name="dn1" dataHost="localhost1" database="db1" />
    <dataNode name="dn2" dataHost="localhost1" database="db2" />
    <dataNode name="dn3" dataHost="localhost1" database="db3" />
    <dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"
              writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <!-- can have multi write hosts -->
        <writeHost host="hostM1" url="192.168.31.200:3306" user="root"
                   password="root">
            <!-- can have multi read hosts -->
            <readHost host="hostS2" url="192.168.31.201:3306" user="root" password="root" />
        </writeHost>
    </dataHost>
</mycat:schema>

rule.xml

用途:定義了我們對錶進行拆分所涉及到的規則定義,視情況修改參數

<?xml version="1.0" encoding="UTF-8"?>
<!-- - - Licensed under the Apache License, Version 2.0 (the "License"); 
    - you may not use this file except in compliance with the License. - You 
    may obtain a copy of the License at - - http://www.apache.org/licenses/LICENSE-2.0 
    - - Unless required by applicable law or agreed to in writing, software - 
    distributed under the License is distributed on an "AS IS" BASIS, - WITHOUT 
    WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - See the 
    License for the specific language governing permissions and - limitations 
    under the License. -->
<!DOCTYPE mycat:rule SYSTEM "rule.dtd">
<mycat:rule xmlns:mycat="http://io.mycat/">
    <tableRule name="rule1">
        <rule>
            <columns>id</columns>
            <algorithm>func1</algorithm>
        </rule>
    </tableRule>

    <tableRule name="rule2">
        <rule>
            <columns>user_id</columns>
            <algorithm>func1</algorithm>
        </rule>
    </tableRule>

    <tableRule name="sharding-by-intfile">
        <rule>
            <columns>sharding_id</columns>
            <algorithm>hash-int</algorithm>
        </rule>
    </tableRule>
    <tableRule name="auto-sharding-long">
        <rule>
            <columns>id</columns>
            <algorithm>rang-long</algorithm>
        </rule>
    </tableRule>
    <tableRule name="mod-long">
        <rule>
            <columns>id</columns>
            <algorithm>mod-long</algorithm>
        </rule>
    </tableRule>
    <tableRule name="sharding-by-murmur">
        <rule>
            <columns>id</columns>
            <algorithm>murmur</algorithm>
        </rule>
    </tableRule>
    <tableRule name="crc32slot">
        <rule>
            <columns>id</columns>
            <algorithm>crc32slot</algorithm>
        </rule>
    </tableRule>
    <tableRule name="sharding-by-month">
        <rule>
            <columns>create_time</columns>
            <algorithm>partbymonth</algorithm>
        </rule>
    </tableRule>
    <tableRule name="latest-month-calldate">
        <rule>
            <columns>calldate</columns>
            <algorithm>latestMonth</algorithm>
        </rule>
    </tableRule>
    
    <tableRule name="auto-sharding-rang-mod">
        <rule>
            <columns>id</columns>
            <algorithm>rang-mod</algorithm>
        </rule>
    </tableRule>
    
    <tableRule name="jch">
        <rule>
            <columns>id</columns>
            <algorithm>jump-consistent-hash</algorithm>
        </rule>
    </tableRule>

    <function name="murmur"
        class="io.mycat.route.function.PartitionByMurmurHash">
        <property name="seed">0</property><!-- 默認是0 -->
        <property name="count">2</property><!-- 要分片的數據庫節點數量,必須指定,否則沒法分片 -->
        <property name="virtualBucketTimes">160</property><!-- 一個實際的數據庫節點被映射為這麼多虛擬節點,默認是160倍,也就是虛擬節點數是物理節點數的160倍 -->
        <!-- <property name="weightMapFile">weightMapFile</property> 節點的權重,沒有指定權重的節點默認是1。以properties文件的格式填寫,以從0開始到count-1的整數值也就是節點索引為key,以節點權重值為值。所有權重值必須是正整數,否則以1代替 -->
        <!-- <property name="bucketMapPath">/etc/mycat/bucketMapPath</property> 
            用於測試時觀察各物理節點與虛擬節點的分佈情況,如果指定了這個屬性,會把虛擬節點的murmur hash值與物理節點的映射按行輸出到這個文件,沒有默認值,如果不指定,就不會輸出任何東西 -->
    </function>

    <function name="crc32slot"
              class="io.mycat.route.function.PartitionByCRC32PreSlot">
        <property name="count">2</property><!-- 要分片的數據庫節點數量,必須指定,否則沒法分片 -->
    </function>
    <function name="hash-int"
        class="io.mycat.route.function.PartitionByFileMap">
        <property name="mapFile">partition-hash-int.txt</property>
    </function>
    <function name="rang-long"
        class="io.mycat.route.function.AutoPartitionByLong">
        <property name="mapFile">autopartition-long.txt</property>
    </function>
    <function name="mod-long" class="io.mycat.route.function.PartitionByMod">
        <!-- how many data nodes -->
        <property name="count">3</property>
    </function>

    <function name="func1" class="io.mycat.route.function.PartitionByLong">
        <property name="partitionCount">8</property>
        <property name="partitionLength">128</property>
    </function>
    <function name="latestMonth"
        class="io.mycat.route.function.LatestMonthPartion">
        <property name="splitOneDay">24</property>
    </function>
    <function name="partbymonth"
        class="io.mycat.route.function.PartitionByMonth">
        <property name="dateFormat">yyyy-MM-dd</property>
        <property name="sBeginDate">2015-01-01</property>
    </function>
    
    <function name="rang-mod" class="io.mycat.route.function.PartitionByRangeMod">
            <property name="mapFile">partition-range-mod.txt</property>
    </function>
    
    <function name="jump-consistent-hash" class="io.mycat.route.function.PartitionByJumpConsistentHash">
        <property name="totalBuckets">3</property>
    </function>
</mycat:rule>

啟動mycat

進入mycat/bin,啟動mycat

啟動命令:./mycat start
停止命令:./mycat stop
重啟命令:./mycat restart
查看狀態命令:./mycat status

注意,可以使用mysql的客戶端直接連接mycat服務,默認端口為8066

錯誤日誌(重要)

  部署過程中,我碰到點小問題,找不到主機名,具體解決方案,請看我另一篇:點我直達 ,如果Mycat服務起不來,記得看錯誤日誌喲!

測試

ip:192.168.31.200(mysql主服務器)

ip:192.168.31.201(mysql從服務器)

ip:192.168.31.209(mycat服務器)

  注:演示過程中,因為mysql搭建了集群,主從複製,可能網絡原因,有些延遲,或者mysql主從複製同步機制問題,導致刷新好幾次,才显示出來,因為圖片較大,被分割幾張gif,內容都是連續的,驗證結果,達到預期,演示成功!

  MySQL集群搭建主從複製:點我直達

本站聲明:網站內容來源於博客園,如有侵權,請聯繫我們,我們將及時處理

【其他文章推薦】

※為什麼 USB CONNECTOR 是電子產業重要的元件?

網頁設計一頭霧水該從何著手呢? 台北網頁設計公司幫您輕鬆架站!

※台北網頁設計公司全省服務真心推薦

※想知道最厲害的網頁設計公司"嚨底家"!

※推薦評價好的iphone維修中心

您可能也會喜歡…