Linux MySQL分庫分表之Mycat

陳彥斌發表於2020-06-20
LinuxMySql

介紹

背景

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

資料切分(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下載

下載連結：點我直達

解壓

修改配置檔案

路徑：/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叢集搭建主從複製：點我直達

相關文章