• Mycat之常用分片规则


    1 取模

    2 枚举

    <table name="orders_ware_info" dataNode="dn1,dn2" rule="sharding_by_intfile">
        
    </table>
    
    <tableRule name="sharding_by_intfile">
        <rule>
            <columns>areacode</columns>
            <algorithm>hash-int</algorithm>
        </rule>
    </tableRule>
    
    <function name="hash-int" class="io.mycat.route.function.PartitionByFileMap">
            <!--其中分片函数配置中,mapFile标识配置文件名称,type默认值为0,0表示Integer,非零表示String-->
            <property name="mapFile">partition-hash-int.txt</property>
            <!--
            所有的节点配置都是从0开始,及0代表节点1
            defaultNode 默认节点:小于0表示不设置默认节点,大于等于0表示设置默认节点,结点为指定的值
            默认节点的作用:枚举分片时,如果碰到不识别的枚举值,就让它路由到默认节点
            如果不配置默认节点(defaultNode值小于0表示不配置默认节点),碰到
            不识别的枚举值就会报错,
            like this:can't find datanode for sharding column:column_name val:ffffffff    
            -->
            <property name="type">1</property>
            <property name="defaultNode">1</property>
    </function>
    
    文件里这么写
    110=0 0 就对应dn1
    120=1 1 就对应dn2

    注意,文件partition-hash-int.txt 和 schema.xml放在相同的路径下,也就是mycat的conf下

    3 范围约定 

    <table name="orders_ware_info" dataNode="dn1,dn2" rule="auto_sharding_long">
        
    </table>
    
    <tableRule name="auto_sharding_long">
        <rule>
            <columns>order_id</columns>
            <algorithm>rang-long</algorithm>
        </rule>
    </tableRule>
    
    <function name="rang-long" class="io.mycat.route.function.AutoPartitionByLong">
            <!--其中分片函数配置中,mapFile标识配置文件名称,type默认值为0,0表示Integer,非零表示String-->
            <property name="mapFile">autopartition-long.txt</property>
           
           
            <property name="defaultNode">1</property>
    </function>
    
    autopartition-long.txt文件里这么写
    0-102=0
    103-200=1

     4 按照日期分片

      按照月份

    <tableRule name="sharding-by-date">
      <rule>
        <columns>date_str</columns>
        <algorithm>partbymonth</algorithm>
      </rule>
    </tableRule>
    
     
    
    <!-- 分片规则 partbymonth 的配置  从 2015 -01 -01 开始分片 -->
    
    <function name="partbymonth" class="org.opencloudb.route.function.PartitionByMonth">
    
      <property name="dateFormat">yyyy-MM-dd</property>
      <property name="sBeginDate">2015-01-01</property>
      <property name="sEndDate">2015-12-31</property> </function>

      

    由于只建立了8个分片,超出部分就直接抛数组越界异常了。
    
     mysql> insert into t_range_date (id,date_str,context) values(1,'2015-01-01','month-1-str');
    
    insert into t_range_date (id,date_str,context) values(2,'2015-02-01','month-2-str');
    
    insert into t_range_date (id,date_str,context) values(3,'2015-03-01','month-3-str');
    
    insert into t_range_date (id,date_str,context) values(4,'2015-04-01','month-4-str');
    
    insert into t_range_date (id,date_str,context) values(5,'2015-05-01','month-5-str');
    
    insert into t_range_date (id,date_str,context) values(6,'2015-06-01','month-6-str');
    
    insert into t_range_date (id,date_str,context) values(7,'2015-07-01','month-7-str');
    
    insert into t_range_date (id,date_str,context) values(8,'2015-08-01','month-8-str');
    
    insert into t_range_date (id,date_str,context) values(9,'2015-09-01','month-9-str');
    
    insert into t_range_date (id,date_str,context) values(10,'2015-10-01','month-10-str');
    
    insert into t_range_date (id,date_str,context) values(11,'2015-11-01','month-11-str');
    
    Query OK, 1 row affected (0.01 sec)
    
    Query OK, 1 row affected (0.01 sec)
    
    Query OK, 1 row affected (0.00 sec)
    
    Query OK, 1 row affected (0.00 sec)
    
    Query OK, 1 row affected (0.01 sec)
    
    Query OK, 1 row affected (0.00 sec)
    
    Query OK, 1 row affected (0.01 sec)
    
    Query OK, 1 row affected (0.00 sec)
    
    ERROR 1064 (HY000): Index: 8, Size: 8
    ERROR 1064 (HY000): Index: 9, Size: 8
    ERROR 1064 (HY000): Index: 10, Size: 8

    按照 day分表

    <tableRule name="sharding-by-date">
    
    <rule>
    
    <columns>create_time</columns>
    
    <algorithm>sharding-by-date</algorithm>
    
    </rule>
    
    </tableRule>
    
    <function name="sharding-by-date" class="io.mycat.route.function.PartitionByDate">
    
    <property name="dateFormat">yyyy-MM-dd</property>
    
    <property name="sBeginDate">2018-01-31</property>
    
    <property name="sEndDate">2018-12-31</property>
    
    <property name="sPartionDay">2</property>
    
    </function>
    
    相关属性:
    dateFormat:日期格式
    sBeginDate:开始日期
    sEndDate:结束日期,如果配置了结束日期,则代码数据到达了这个日期的分片后,会重复从开始分片插入
    sPartionDay:分区天数,默认值 10 ,从开始日期算起,每个10天一个分区

    配置了sEndDate后如果超过了日期,数据都会落到第一个分片数据库里

  • 相关阅读:
    SpringMvc 的小dome
    解决Eclipse加载Tomcat报错Could not load the Tomcat server configuration.The configuration may be corrupt or incomplete.
    【转载】理解java三层架构:持久层、业务层、表现层
    Eclipse 报错 org.springframework.context.support.AbstractApplicationContext refresh处理
    Eclipes 报错The ... package is not accessible解决
    Tomcat文件目录及用途
    MyBatis配置文件mybatis-config.xml中的mappers中引入映射文件的几种方式总结
    Mybatis中自动生成主键在mysql和Oracle当中的情况
    mybatis中int类型的判断问题分析
    Cause: org.apache.ibatis.builder.BuilderException: Error creating document instance. Cause: org.xml.sax.SAXParseException; lineNumber: 56; columnNumber: 17
  • 原文地址:https://www.cnblogs.com/juniorMa/p/14443786.html
Copyright © 2020-2023  润新知