Mysql系列六：（Mycat分片路由原理、Mycat常用分片规则及对应源

发布时间：2020-12-11 23:57:40 所属栏目：MySql教程来源：网络整理

导读：?一、Mycat分片路由原理我们先来看下面的一个SQL在Mycat里面是如何执行的： travelrecord id ( , ); 有3个分片dn1,dn2,dn3,id=5000001 查询时可能有出现的问题： dn1 ?dn2 ?dn3 2）只扫描某个片。漏掉数据的情况。那么Mycat是如何解决上面的问题的呢？ Myc

?一、Mycat分片路由原理

我们先来看下面的一个SQL在Mycat里面是如何执行的：

travelrecord id (,);

有3个分片dn1,dn2,dn3,id=5000001

查询时可能有出现的问题：

dn1 ?dn2 ?dn3

2）只扫描某个片。漏掉数据的情况。

那么Mycat是如何解决上面的问题的呢？

Mycat解析的过程主要有Visitor和Statement两个阶段

1）Visitor过程，解析出如下属性：

　　　　　　　　　　　　　　哪一张表

　　　　　　　　　　　　　　字段列表

　　　　　　　　　　　　　　条件信息

　　　　　　　　　　　　　　什么样的SQL

通过查询条件可以知道要查询的数据都在哪些分片上

travelrecord id travelrecord id ；（dn3执行）

4）分别在分片dn2，dn3上执行第 3）步改写的SQL，然后把从dn2，dn3上得到的结果进行拼装就是最终的结果了

visitor

确定分片的过程：首先看where

二、Mycat常用分片规则

1. 时间类：按天分片、自然月分片、单月小时分片

2. 哈希类：Hash固定分片、日期范围Hash分片、截取数字Hash求模范围分片、截取数字Hash分片、一致性Hash分片

3. 取模类：取模分片、取模范围分片、范围求模分片

4. 其他类：枚举分片、范围约定分片、应用指定分片、冷热数据分片

下面基于源码来介绍Mycat的常用分片规则，

三、Mycat常用分片规则介绍

分片规则都定义在rule.xml文件里面

id func1 1,1,2,3,1 128,128,128

1. 自动范围分片

在rule.xml里面的配置：

id rang-long

<function name="rang-long"
class="io.mycat.route.function.AutoPartitionByLong">
<property name="mapFile">autopartition-long.txt</property>
</function>

说明：

有3个分片，第1个分片存储的是1-500000的数据，第2个分片存储的是500001-1000000的数据，第3个分片存储的是1000001-1500000的数据

insert into employee(id,name) value(1,Tom);在第1个分片

insert into employee(id,name) value(500002,Jack);在第2个分片

insert into employee(id,name) value(1000002,Lucy);在第3个分片

对应代码：

import io.mycat.config.model.rule.RuleAlgorithm;

/**

auto partition by Long,can be used in auto increment primary key partition
@author wuzhi
*/
public class AutoPartitionByLong extends AbstractPartitionAlgorithm implements RuleAlgorithm{

private String mapFile;
private LongRange[] longRongs;

private int defaultNode = -1;
@Override
public void init() {
```
 initialize();
```
}

public void setMapFile(String mapFile) {
this.mapFile = mapFile;
}

@Override
public Integer calculate(String columnValue) {
// columnValue = NumberParseUtil.eliminateQoute(columnValue);
try {
long value = Long.parseLong(columnValue);
Integer rst = null;
for (LongRange longRang : this.longRongs) {
if (value <= longRang.valueEnd && value >= longRang.valueStart) {
return longRang.nodeIndx;
}
}
//数据超过范围，暂时使用配置的默认节点
if (rst == null && defaultNode >= 0) {
return defaultNode;
}
return rst;
} catch (NumberFormatException e){
throw new IllegalArgumentException(new StringBuilder().append("columnValue:").append(columnValue).append(" Please eliminate any quote and non number within it.").toString(),e);
}
}

@Override
public Integer[] calculateRange(String beginValue,String endValue) {
return AbstractPartitionAlgorithm.calculateSequenceRange(this,beginValue,endValue);
}

@Override
public int getPartitionNum() {
// int nPartition = longRongs.length;
```
 <Integer> distNodeIdxSet = HashSet<Integer> nPartition =private void initialize() {
BufferedReader in = null;
try {
// FileInputStream fin = new FileInputStream(new File(fileMapPath));
InputStream fin = this.getClass().getClassLoader()
.getResourceAsStream(mapFile);
if (fin == null) {
throw new RuntimeException("can't find class resource file "
+ mapFile);
}
in = new BufferedReader(new InputStreamReader(fin));
LinkedList longRangeList = new LinkedList();
```
 (String line = ; (line = in.readLine()) != = (line.startsWith("#") || line.startsWith("//" ind = line.indexOf('=' (ind < 0" warn: bad line int " + mapFile + " :"
 += line.substring(0,ind).trim().split("-" longStart = NumberParseUtil.parseLong(pairs[0 longEnd = NumberParseUtil.parseLong(pairs[1 nodeId = Integer.parseInt(line.substring(ind + 1= longRangeList.toArray( (e public int getDefaultNode() {
return defaultNode;
}

public void setDefaultNode(int defaultNode) {
this.defaultNode = defaultNode;
}

static class LongRange {
public final int nodeIndx;
public final long valueStart;
public final long valueEnd;
```
 LongRange( nodeIndx, valueStart,.nodeIndx =.valueStart =.valueEnd =<span style="color: #000000"&gt; valueEnd;
 }
```
}
}

2. 枚举分片

把数据分类存储

在rule.xml里面的配置：
sharding_id hash-int partition-hash-int.txt 0
说明：0

对应代码：

import io.mycat.config.model.rule.RuleAlgorithm;

/**

@author mycat
*/
public class PartitionByFileMap extends AbstractPartitionAlgorithm implements RuleAlgorithm {

private String mapFile;
private Map<Object,Integer> app2Partition;
/**
- Map<Object,Integer> app2Partition中key值的类型：默认值为0，0表示Integer，非零表示String
 */
 private int type;
/**
- 默认节点在map中的key
 */
 private static final String DEFAULT_NODE = "DEFAULT_NODE";
/**
- 默认节点:小于0表示不设置默认节点，大于等于0表示设置默认节点
- 默认节点的作用：枚举分片时，如果碰到不识别的枚举值，就让它路由到默认节点
- 如果不配置默认节点（defaultNode值小于0表示不配置默认节点），碰到
- 不识别的枚举值就会报错，
- like this：can't find datanode for sharding column:column_name val:ffffffff
 */
 private int defaultNode = -1;
@Override
public void init() {
```
 initialize();
```
}

public void setMapFile(String mapFile) {
this.mapFile = mapFile;
}

public void setType(int type) {
this.type = type;
}

public void setDefaultNode(int defaultNode) {
this.defaultNode = defaultNode;
}

@Override
public Integer calculate(String columnValue) {
try {
Object value = columnValue;
if (type == 0) {
value = Integer.valueOf(columnValue);
}
Integer rst = null;
Integer pid = app2Partition.get(value);
if (pid != null) {
rst = pid;
} else {
rst = app2Partition.get(DEFAULT_NODE);
}
return rst;
} catch (NumberFormatException e){
throw new IllegalArgumentException(new StringBuilder().append("columnValue:").append(columnValue).append(" Please check if the format satisfied.").toString(),e);
}
}

@Override
public int getPartitionNum() {
Set set = new HashSet(app2Partition.values());
int count = set.size();
return count;
}

private void initialize() {
BufferedReader in = null;
try {
// FileInputStream fin = new FileInputStream(new File(fileMapPath));
InputStream fin = this.getClass().getClassLoader()
.getResourceAsStream(mapFile);
if (fin == null) {
throw new RuntimeException("can't find class resource file "
+ mapFile);
}
in = new BufferedReader(new InputStreamReader(fin));
```
 app2Partition = HashMap<Object,Integer> (String line = ; (line = in.readLine()) != = (line.startsWith("#") || line.startsWith("//" ind = line.indexOf('=' (ind < 0= line.substring(0 pid = Integer.parseInt(line.substring(ind + 1(type == 0
 (defaultNode >= 0 (e <span style="color: #000000"&gt; (Exception e2) {
 }
 }
```
}
}

3. Hash固定分片（固定分片Hash算法）

Hash

在rule.xml里面的配置：
id func1 1,128
说明：

1) partitionCount.length

2) sum((partitionCount[i]*partitionLength[j])) === 1024——>partitionCount[0]*partitionLength[0]+partitionCount[1]*partitionLength[1] === 1024

1*128+1*128+2*128+3*128+1*128 === 1024

eg：

8

1

2

...................

8

id

id%1024 = 128

import io.mycat.config.model.rule.RuleAlgorithm;
import io.mycat.route.util.PartitionUtil;

public final class PartitionByLong extends AbstractPartitionAlgorithm implements RuleAlgorithm {
protected int[] count;
protected int[] length;
protected PartitionUtil partitionUtil;

</span><span style="color: #0000ff"&gt;private</span> <span style="color: #0000ff"&gt;static</span> <span style="color: #0000ff"&gt;int</span><span style="color: #000000"&gt;[] toIntArray(String string) {
    String[] strs </span>= io.mycat.util.SplitUtil.split(string,',<span style="color: #0000ff"&gt;true</span><span style="color: #000000"&gt;);
    </span><span style="color: #0000ff"&gt;int</span>[] ints = <span style="color: #0000ff"&gt;new</span> <span style="color: #0000ff"&gt;int</span><span style="color: #000000"&gt;[strs.length];
    </span><span style="color: #0000ff"&gt;for</span> (<span style="color: #0000ff"&gt;int</span> i = 0; i < strs.length; ++<span style="color: #000000"&gt;i) {
        ints[i] </span>=<span style="color: #000000"&gt; Integer.parseInt(strs[i]);
    }
    </span><span style="color: #0000ff"&gt;return</span><span style="color: #000000"&gt; ints;
}

</span><span style="color: #0000ff"&gt;public</span> <span style="color: #0000ff"&gt;void</span><span style="color: #000000"&gt; setPartitionCount(String partitionCount) {
    </span><span style="color: #0000ff"&gt;this</span>.count =<span style="color: #000000"&gt; toIntArray(partitionCount);
}

</span><span style="color: #0000ff"&gt;public</span> <span style="color: #0000ff"&gt;void</span><span style="color: #000000"&gt; setPartitionLength(String partitionLength) {
    </span><span style="color: #0000ff"&gt;this</span>.length =<span style="color: #000000"&gt; toIntArray(partitionLength);
}

@Override
</span><span style="color: #0000ff"&gt;public</span> <span style="color: #0000ff"&gt;void</span><span style="color: #000000"&gt; init() {
    partitionUtil </span>= <span style="color: #0000ff"&gt;new</span><span style="color: #000000"&gt; PartitionUtil(count,length);

}

@Override
</span><span style="color: #0000ff"&gt;public</span><span style="color: #000000"&gt; Integer calculate(String columnValue)  {

// columnValue = NumberParseUtil.eliminateQoute(columnValue);
try {
long key = Long.parseLong(columnValue);
return partitionUtil.partition(key);
} catch (NumberFormatException e){
throw new IllegalArgumentException(new StringBuilder().append("columnValue:").append(columnValue).append(" Please eliminate any quote and non number within it.").toString(),endValue);
}

// @Override
// public int getPartitionCount() {
// int nPartition = 0;
// for(int i = 0; i < count.length; i++) {
// nPartition += count[i];
// }
// return nPartition;
// }

}

4.?求模分片

id%

在rule.xml里面的配置：

id mod-long 3

对应代码：

import java.math.BigInteger;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import io.mycat.config.model.rule.RuleAlgorithm;

/**

number column partion by Mod operator
if count is 10 then 0 to 0,21 to 1 (21 % 10 =1)
@author wuzhih

*/
public class PartitionByMod extends AbstractPartitionAlgorithm implements RuleAlgorithm {

private int count;
@Override
public void init() {

}

public void setCount(int count) {
this.count = count;
}

@Override
public Integer calculate(String columnValue) {
// columnValue = NumberParseUtil.eliminateQoute(columnValue);
try {
BigInteger bigNum = new BigInteger(columnValue).abs();
return (bigNum.mod(BigInteger.valueOf(count))).intValue();
} catch (NumberFormatException e){
throw new IllegalArgumentException(new StringBuilder().append("columnValue:").append(columnValue).append(" Please eliminate any quote and non number within it.").toString(),e);
}

}

@Override
public int getPartitionNum() {
int nPartition = this.count;
return nPartition;
}

private static void hashTest() {
PartitionByMod hash=new PartitionByMod();
hash.setCount(11);
hash.init();

 </span><span style="color: #0000ff"&gt;int</span>[] bucket=<span style="color: #0000ff"&gt;new</span> <span style="color: #0000ff"&gt;int</span><span style="color: #000000"&gt;[hash.count];

 Map</span><Integer,List<Integer>> hashed=<span style="color: #0000ff"&gt;new</span> HashMap<><span style="color: #000000"&gt;();

 </span><span style="color: #0000ff"&gt;int</span> total=1000_0000;<span style="color: #008000"&gt;//</span><span style="color: #008000"&gt;数据量</span>
 <span style="color: #0000ff"&gt;int</span> c=0<span style="color: #000000"&gt;;
 </span><span style="color: #0000ff"&gt;for</span>(<span style="color: #0000ff"&gt;int</span> i=100_0000;i<total+100_0000;i++){<span style="color: #008000"&gt;//</span><span style="color: #008000"&gt;假设分片键从100万开始</span>
     c++<span style="color: #000000"&gt;;
     </span><span style="color: #0000ff"&gt;int</span> h=<span style="color: #000000"&gt;hash.calculate(Integer.toString(i));
     bucket[h]</span>++<span style="color: #000000"&gt;;
     List</span><Integer> list=<span style="color: #000000"&gt;hashed.get(h);
     </span><span style="color: #0000ff"&gt;if</span>(list==<span style="color: #0000ff"&gt;null</span><span style="color: #000000"&gt;){
         list</span>=<span style="color: #0000ff"&gt;new</span> ArrayList<><span style="color: #000000"&gt;();
         hashed.put(h,list);
     }
     list.add(i);
 }
 System.out.println(c</span>+"   "+<span style="color: #000000"&gt;total);
 </span><span style="color: #0000ff"&gt;double</span> d=0<span style="color: #000000"&gt;;
 c</span>=0<span style="color: #000000"&gt;;
 </span><span style="color: #0000ff"&gt;int</span> idx=0<span style="color: #000000"&gt;;
 System.out.println(</span>"index    bucket   ratio"<span style="color: #000000"&gt;);
 </span><span style="color: #0000ff"&gt;for</span>(<span style="color: #0000ff"&gt;int</span><span style="color: #000000"&gt; i:bucket){
     d</span>+=i/(<span style="color: #0000ff"&gt;double</span><span style="color: #000000"&gt;)total;
     c</span>+=<span style="color: #000000"&gt;i;
     System.out.println(idx</span>+++"  "+i+"   "+(i/(<span style="color: #0000ff"&gt;double</span><span style="color: #000000"&gt;)total));
 }
 System.out.println(d</span>+"  "+<span style="color: #000000"&gt;c);

 System.out.println(</span>"****************************************************"<span style="color: #000000"&gt;);
 rehashTest(hashed.get(</span>0<span style="color: #000000"&gt;));

}
private static void rehashTest(List partition) {
PartitionByMod hash=new PartitionByMod();
hash.count=110;//分片数
 hash.init();

 </span><span style="color: #0000ff"&gt;int</span>[] bucket=<span style="color: #0000ff"&gt;new</span> <span style="color: #0000ff"&gt;int</span><span style="color: #000000"&gt;[hash.count];

 </span><span style="color: #0000ff"&gt;int</span> total=partition.size();<span style="color: #008000"&gt;//</span><span style="color: #008000"&gt;数据量</span>
 <span style="color: #0000ff"&gt;int</span> c=0<span style="color: #000000"&gt;;
 </span><span style="color: #0000ff"&gt;for</span>(<span style="color: #0000ff"&gt;int</span> i:partition){<span style="color: #008000"&gt;//</span><span style="color: #008000"&gt;假设分片键从100万开始</span>
     c++<span style="color: #000000"&gt;;
     </span><span style="color: #0000ff"&gt;int</span> h=<span style="color: #000000"&gt;hash.calculate(Integer.toString(i));
     bucket[h]</span>++<span style="color: #000000"&gt;;
 }
 System.out.println(c</span>+"   "+<span style="color: #000000"&gt;total);
 c</span>=0<span style="color: #000000"&gt;;
 </span><span style="color: #0000ff"&gt;int</span> idx=0<span style="color: #000000"&gt;;
 System.out.println(</span>"index    bucket   ratio"<span style="color: #000000"&gt;);
 </span><span style="color: #0000ff"&gt;for</span>(<span style="color: #0000ff"&gt;int</span><span style="color: #000000"&gt; i:bucket){
     c</span>+=<span style="color: #000000"&gt;i;
     System.out.println(idx</span>+++"  "+i+"   "+(i/(<span style="color: #0000ff"&gt;double</span><span style="color: #000000"&gt;)total));
 }

}
public static void main(String[] args) {
// hashTest();
PartitionByMod partitionByMod = new PartitionByMod();
partitionByMod.count=8;
partitionByMod.calculate(""6"");
partitionByMod.calculate("'6'");
}
}

5.?自然月分片

按照自然月的方式进行分片

在rule.xml里面的配置：

create_time partbymonth yyyy-MM-dd 2015-01-01

说明：

sEndDated

分片

对应代码：

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collections;
import java.util.List;

import io.mycat.config.model.rule.RuleAlgorithm;
import org.apache.log4j.Logger;

/**

例子按月份列分区，每个自然月一个分片，格式 between操作解析的范例
@author wzh
*/
public class PartitionByMonth extends AbstractPartitionAlgorithm implements
RuleAlgorithm {
private static final Logger LOGGER = Logger.getLogger(PartitionByDate.class);
private String sBeginDate;
private String dateFormat;
private String sEndDate;
private Calendar beginDate;
private Calendar endDate;
private int nPartition;

private ThreadLocal formatter;

@Override
public void init() {
try {
beginDate = Calendar.getInstance();
beginDate.setTime(new SimpleDateFormat(dateFormat)
.parse(sBeginDate));
formatter = new ThreadLocal() {
@Override
protected SimpleDateFormat initialValue() {
return new SimpleDateFormat(dateFormat);
}
};
if(sEndDate!=null&&!sEndDate.equals("")) {
endDate = Calendar.getInstance();
endDate.setTime(new SimpleDateFormat(dateFormat).parse(sEndDate));
nPartition = ((endDate.get(Calendar.YEAR) - beginDate.get(Calendar.YEAR)) * 12
- endDate.get(Calendar.MONTH) - beginDate.get(Calendar.MONTH)) + 1;
```
 (nPartition <= 0 java.lang.IllegalArgumentException("Incorrect time range for month partitioning!"= -1 /**
- For circulatory partition,calculated value of target partition needs to be
- rotated to fit the partition range
 */
 private int reCalculatePartition(int targetPartition) {
 /**
 - If target date is previous of start time of partition setting,shift
 - the delta range between target and start date to be positive value
 */
 if (targetPartition < 0) {
 targetPartition = nPartition - (-targetPartition) % nPartition;
 }
 if (targetPartition >= nPartition) {
 targetPartition = targetPartition % nPartition;
 }
 
 return targetPartition;
 }
@Override
public Integer calculate(String columnValue) {
try {
int targetPartition;
Calendar curTime = Calendar.getInstance();
curTime.setTime(formatter.get().parse(columnValue));
targetPartition = ((curTime.get(Calendar.YEAR) - beginDate.get(Calendar.YEAR))
* 12 + curTime.get(Calendar.MONTH)
- beginDate.get(Calendar.MONTH));
```
 
 (nPartition > 0= IllegalArgumentException( StringBuilder().append("columnValue:").append(columnValue).append(" Please check if the format satisfied."public Integer[] calculateRange(String beginValue,String endValue) {
try {
int startPartition,endPartition;
Calendar partitionTime = Calendar.getInstance();
SimpleDateFormat format = new SimpleDateFormat(dateFormat);
partitionTime.setTime(format.parse(beginValue));
startPartition = ((partitionTime.get(Calendar.YEAR) - beginDate.get(Calendar.YEAR))
12 + partitionTime.get(Calendar.MONTH)
- beginDate.get(Calendar.MONTH));
partitionTime.setTime(format.parse(endValue));
endPartition = ((partitionTime.get(Calendar.YEAR) - beginDate.get(Calendar.YEAR))
12 + partitionTime.get(Calendar.MONTH)
- beginDate.get(Calendar.MONTH));
```
 List<Integer> list = ArrayList<> (startPartition <== (Collections.frequency(list,nodeValue) < 1++ size = (list.toArray("error" Integer[0public int getPartitionNum() {
int nPartition = this.nPartition;
return nPartition;
}

public void setsBeginDate(String sBeginDate) {
this.sBeginDate = sBeginDate;
}

public void setDateFormat(String dateFormat) {
this.dateFormat = dateFormat;
}

public void setsEndDate(String sEndDate) {
this.sEndDate = sEndDate;
}

}

6.?匹配求模分片

prefixLengthcharAt(i)

在rule.xml里面的配置：

id partitionbyprefixpattern 3 6

有下面这种类型的数据

201801 01 ??10001

columns6

对应代码：

/**

partition by Prefix length,can be used in String partition
@author hexiaobin
*/
public class PartitionByPrefixPattern extends AbstractPartitionAlgorithm implements RuleAlgorithm {
private static final int PARTITION_LENGTH = 1024;
private int patternValue = PARTITION_LENGTH;// 分区长度，取模数值(默认为1024)
private int prefixLength;// 字符前几位进行ASCII码取和
private String mapFile;
private LongRange[] longRongs;

@Override
public void init() {
```
 initialize();
```
}

public void setMapFile(String mapFile) {
this.mapFile = mapFile;
}

public void setPatternValue(int patternValue) {
this.patternValue = patternValue;
}

public void setPrefixLength(int prefixLength) {
this.prefixLength = prefixLength;
}

@Override
public Integer calculate(String columnValue) {
try {
int Length = Integer.valueOf(prefixLength);
```
 Length = columnValue.length() < Length ? sum = 0 ( i = 0; i < Length; i++= sum += (LongRange longRang : hash = sum % (hash <= longRang.valueEnd &amp;&amp; hash >= IllegalArgumentException( StringBuilder().append("columnValue:").append(columnValue).append(" Please eliminate any quote and non number within it."public int getPartitionNum() {
// int nPartition = this.longRongs.length;
/*
- fix #1284 这里的统计应该统计Range的nodeIndex的distinct总数
 */
 Set distNodeIdxSet = new HashSet();
 for(LongRange range : longRongs) {
 distNodeIdxSet.add(range.nodeIndx);
 }
 int nPartition = distNodeIdxSet.size();
 return nPartition;
 }
private void initialize() {
BufferedReader in = null;
try {
// FileInputStream fin = new FileInputStream(new File(fileMapPath));
InputStream fin = this.getClass().getClassLoader()
.getResourceAsStream(mapFile);
if (fin == null) {
throw new RuntimeException("can't find class resource file "
+ mapFile);
}
in = new BufferedReader(new InputStreamReader(fin));
LinkedList longRangeList = new LinkedList();
```
 (String line = ; (line = in.readLine()) != = (line.startsWith("#") || line.startsWith("//" ind = line.indexOf('=' (ind < 0" warn: bad line int " + mapFile + " :"
 += line.substring(0,longEnd));

 }
 longRongs = longRangeList.toArray( (e static class LongRange {
public final int nodeIndx;
public final long valueStart;
public final long valueEnd;
```
 LongRange( nodeIndx,.nodeIndx =.valueStart =.valueEnd =<function name="sharding-by-hotdate" class="org.opencloudb.route.function.PartitionByHotDate">
<property name="dateFormat">yyyy-MM-dd</property> 
<property name="sLastDay">30</property> 
<property name="sPartionDay">30</property> 
</function>

对应代码：

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import io.mycat.config.model.rule.RuleAlgorithm;

/**

根据日期查询日志数据冷热数据分布，最近n个月的到实时交易库查询，超过n个月的按照m天分片
@author sw

create_time sharding-by-hotdate PartitionByHotDate AbstractPartitionAlgorithm Logger LOGGER = LoggerFactory.getLogger(PartitionByHotDate.private String dateFormat;
private String sLastDay;
private String sPartionDay;

private long sLastTime;
private long partionTime;
private ThreadLocal formatter;

private long beginDate;

private static final long oneDay = 86400000;

@Override
public void init() {
try {
formatter = new ThreadLocal() {
@Override
protected SimpleDateFormat initialValue() {
return new SimpleDateFormat(dateFormat);
}
};
sLastTime = Integer.valueOf(sLastDay);
partionTime = Integer.parseInt(sPartionDay) * oneDay;
} catch (Exception e) {
throw new java.lang.IllegalArgumentException(e);
}
}

@Override
public Integer calculate(String columnValue) {
Integer targetPartition = -1;
try {
long targetTime = formatter.get().parse(columnValue).getTime();
Calendar now = Calendar.getInstance();
long nowTime = now.getTimeInMillis();

     beginDate </span>= nowTime - sLastTime *<span style="color: #000000"&gt; oneDay;

     </span><span style="color: #0000ff"&gt;long</span> diffDays = (nowTime - targetTime) / (1000 * 60 * 60 * 24) + 1<span style="color: #000000"&gt;;
     </span><span style="color: #0000ff"&gt;if</span>(diffDays-sLastTime <= 0 || diffDays<0<span style="color: #000000"&gt; ){
         targetPartition </span>= 0<span style="color: #000000"&gt;;
     }</span><span style="color: #0000ff"&gt;else</span><span style="color: #000000"&gt;{
         targetPartition </span>= (<span style="color: #0000ff"&gt;int</span>) ((beginDate - targetTime) / partionTime) + 1<span style="color: #000000"&gt;;
     }

     LOGGER.debug(</span>"PartitionByHotDate calculate for " + columnValue + " return " +<span style="color: #000000"&gt; targetPartition);
     </span><span style="color: #0000ff"&gt;return</span><span style="color: #000000"&gt; targetPartition;
 } </span><span style="color: #0000ff"&gt;catch</span><span style="color: #000000"&gt; (ParseException e) {
     </span><span style="color: #0000ff"&gt;throw</span> <span style="color: #0000ff"&gt;new</span> IllegalArgumentException(<span style="color: #0000ff"&gt;new</span> StringBuilder().append("columnValue:").append(columnValue).append(" Please check if the format satisfied."<span style="color: #000000"&gt;).toString(),String endValue)  {
 Integer[] targetPartition </span>= <span style="color: #0000ff"&gt;null</span><span style="color: #000000"&gt;;
 </span><span style="color: #0000ff"&gt;try</span><span style="color: #000000"&gt; {
     </span><span style="color: #0000ff"&gt;long</span> startTime =<span style="color: #000000"&gt; formatter.get().parse(beginValue).getTime();
     </span><span style="color: #0000ff"&gt;long</span> endTime =<span style="color: #000000"&gt; formatter.get().parse(endValue).getTime();
     Calendar now </span>=<span style="color: #000000"&gt; Calendar.getInstance();
     </span><span style="color: #0000ff"&gt;long</span> nowTime =<span style="color: #000000"&gt; now.getTimeInMillis();

     </span><span style="color: #0000ff"&gt;long</span> limitDate = nowTime - sLastTime *<span style="color: #000000"&gt; oneDay;
     </span><span style="color: #0000ff"&gt;long</span> diffDays = (nowTime - startTime) / (1000 * 60 * 60 * 24) + 1<span style="color: #000000"&gt;;
     </span><span style="color: #0000ff"&gt;if</span>(diffDays-sLastTime <= 0 || diffDays<0<span style="color: #000000"&gt; ){
         Integer [] re </span>= <span style="color: #0000ff"&gt;new</span> Integer[1<span style="color: #000000"&gt;];
         re[</span>0] = 0<span style="color: #000000"&gt;;
         targetPartition </span>=<span style="color: #000000"&gt; re ;
     }</span><span style="color: #0000ff"&gt;else</span><span style="color: #000000"&gt;{
         Integer [] re </span>= <span style="color: #0000ff"&gt;null</span><span style="color: #000000"&gt;;
         Integer begin </span>= 0,end = 0<span style="color: #000000"&gt;;
         end </span>= <span style="color: #0000ff"&gt;this</span><span style="color: #000000"&gt;.calculate(beginValue);
         </span><span style="color: #0000ff"&gt;boolean</span> hasLimit = <span style="color: #0000ff"&gt;false</span><span style="color: #000000"&gt;;
         </span><span style="color: #0000ff"&gt;if</span>(endTime-limitDate > 0<span style="color: #000000"&gt;){
             endTime </span>=<span style="color: #000000"&gt; limitDate;
             hasLimit </span>= <span style="color: #0000ff"&gt;true</span><span style="color: #000000"&gt;;
         }
         begin </span>= <span style="color: #0000ff"&gt;this</span><span style="color: #000000"&gt;.calculate(formatter.get().format(endTime));
         </span><span style="color: #0000ff"&gt;if</span>(begin == <span style="color: #0000ff"&gt;null</span> || end == <span style="color: #0000ff"&gt;null</span><span style="color: #000000"&gt;){
             </span><span style="color: #0000ff"&gt;return</span><span style="color: #000000"&gt; re;
         }
         </span><span style="color: #0000ff"&gt;if</span> (end >=<span style="color: #000000"&gt; begin) {
             </span><span style="color: #0000ff"&gt;int</span> len = end-begin+1<span style="color: #000000"&gt;;
             </span><span style="color: #0000ff"&gt;if</span><span style="color: #000000"&gt;(hasLimit){
                 re </span>= <span style="color: #0000ff"&gt;new</span> Integer[len+1<span style="color: #000000"&gt;];
                 re[</span>0] = 0<span style="color: #000000"&gt;;
                 </span><span style="color: #0000ff"&gt;for</span>(<span style="color: #0000ff"&gt;int</span> i =0;i<len;i++<span style="color: #000000"&gt;){
                     re[i</span>+1]=begin+<span style="color: #000000"&gt;i;
                 }
             }</span><span style="color: #0000ff"&gt;else</span><span style="color: #000000"&gt;{
                 re </span>= <span style="color: #0000ff"&gt;new</span><span style="color: #000000"&gt; Integer[len];
                 </span><span style="color: #0000ff"&gt;for</span>(<span style="color: #0000ff"&gt;int</span> i=0;i<len;i++<span style="color: #000000"&gt;){
                     re[i]</span>=begin+<span style="color: #000000"&gt;i;
                 }
             }
             </span><span style="color: #0000ff"&gt;return</span><span style="color: #000000"&gt; re;
         }</span><span style="color: #0000ff"&gt;else</span><span style="color: #000000"&gt;{
             </span><span style="color: #0000ff"&gt;return</span><span style="color: #000000"&gt; re;
         }
     }
 } </span><span style="color: #0000ff"&gt;catch</span><span style="color: #000000"&gt; (ParseException e) {
     </span><span style="color: #0000ff"&gt;throw</span> <span style="color: #0000ff"&gt;new</span> IllegalArgumentException(<span style="color: #0000ff"&gt;new</span> StringBuilder().append("endValue:").append(endValue).append(" Please check if the format satisfied."<span style="color: #000000"&gt;).toString(),e);
 }
 </span><span style="color: #0000ff"&gt;return</span><span style="color: #000000"&gt; targetPartition;

}

public void setsPartionDay(String sPartionDay) {
this.sPartionDay = sPartionDay;
}
public void setDateFormat(String dateFormat) {
this.dateFormat = dateFormat;
}
public String getsLastDay() {
return sLastDay;
}
public void setsLastDay(String sLastDay) {
this.sLastDay = sLastDay;
}
}

8.?一致性哈希分片

1）首先求出mysql服务器（节点）的哈希值，并将其配置到0～2^32的圆（continuum）上。

2）为每台mysql服务器物理节点虚拟出多个虚拟节点，并计算hash值映射到相同的圆上。

3）然后从数据映射到的mysql服务器虚拟节点的位置开始顺时针查找，将数据保存到找到的第一个mysql服务器上。如果超过2³²仍然找不到服务器，就会保存到第一台mysql服务器上。

特点：解决数据均匀分布

在rule.xml里面的配置：

id murmur 0 2 160 节点的权重，没有指定权重的节点默认是1。以properties文件的格式填写，以从0开始到count-1的整数值也就是节点索引为key，以节点权重值为值。所有权重值必须是正整数，否则以1代替用于测试时观察各物理节点与虚拟节点的分布情况，如果指定了这个属性，会把虚拟节点的murmur hash值与物理节点的映射按行输出到这个文件，没有默认值，如果不指定，就不会输出任何东西

对应代码：

import java.io.BufferedReader;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.OutputStream;
import java.nio.charset.Charset;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.SortedMap;
import java.util.TreeMap;

import com.google.common.hash.HashFunction;
import com.google.common.hash.Hashing;

/**

consistancy hash,murmur hash
implemented by Guava
@author wuzhih
*/
public class PartitionByMurmurHash extends AbstractPartitionAlgorithm implements RuleAlgorithm {
private static final int DEFAULT_VIRTUAL_BUCKET_TIMES=160;
private static final int DEFAULT_WEIGHT=1;
private static final Charset DEFAULT_CHARSET=Charset.forName("UTF-8");

private int seed;
private int count;
private int virtualBucketTimes=DEFAULT_VIRTUAL_BUCKET_TIMES;
private Map<Integer,Integer> weightMap=new HashMap<>();
// private String bucketMapPath;

private HashFunction hash;

private SortedMap<Integer,Integer> bucketMap;
@Override
public void init() {
try{
bucketMap=new TreeMap<>();
// boolean serializableBucketMap=bucketMapPath!=null && bucketMapPath.length()>0;
// if(serializableBucketMap){
// File bucketMapFile=new File(bucketMapPath);
// if(bucketMapFile.exists() && bucketMapFile.length()>0){
// loadBucketMapFile();
// return;
// }
// }
 generateBucketMap();
// if(serializableBucketMap){
// storeBucketMap();
// }
}catch(Exception e){
throw new MurmurHashException(e);
}
}

private void generateBucketMap(){
hash=Hashing.murmur3_32(seed);//计算一致性哈希的对象
for(int i=0;i<count;i++){//构造一致性哈希环，用TreeMap表示
StringBuilder hashName=new StringBuilder("SHARD-").append(i);
for(int n=0,shard=virtualBucketTimes*getWeight(i);n<shard;n++){
bucketMap.put(hash.hashUnencodedChars(hashName.append("-NODE-").append(n)).asInt(),i);
}
}
weightMap=null;
}
// private void storeBucketMap() throws IOException{
// try(OutputStream store=new FileOutputStream(bucketMapPath)){
// Properties props=new Properties();
// for(Map.Entry entry:bucketMap.entrySet()){
// props.setProperty(entry.getKey().toString(),entry.getValue().toString());
// }
// props.store(store,null);
// }
// }
// private void loadBucketMapFile() throws FileNotFoundException,IOException{
// try(InputStream in=new FileInputStream(bucketMapPath)){
// Properties props=new Properties();
// props.load(in);
// for(Map.Entry entry:props.entrySet()){
// bucketMap.put(Integer.parseInt(entry.getKey().toString()),Integer.parseInt(entry.getValue().toString()));
// }
// }
// }
/**
- 得到桶的权重，桶就是实际存储数据的DB实例
- 从0开始的桶编号为key，权重为值，权重默认为1。
- 键值必须都是整数
- @param bucket
- @return
 */
 private int getWeight(int bucket){
 Integer w=weightMap.get(bucket);
 if(w==null){
 w=DEFAULT_WEIGHT;
 }
 return w;
 }
 /**
- 创建murmur_hash对象的种子，默认0
- @param seed
 */
 public void setSeed(int seed){
 this.seed=seed;
 }
 /**
- 节点的数量
- @param count
 */
 public void setCount(int count) {
 this.count = count;
 }
 /**
- 虚拟节点倍数，virtualBucketTimes*count就是虚拟结点数量
- @param virtualBucketTimes
 */
 public void setVirtualBucketTimes(int virtualBucketTimes){
 this.virtualBucketTimes=virtualBucketTimes;
 }
 /**
- 节点的权重，没有指定权重的节点默认是1。以properties文件的格式填写，以从0开始到count-1的整数值也就是节点索引为key，以节点权重值为值。
- 所有权重值必须是正整数，否则以1代替
- @param weightMapPath
- @throws IOException
- @throws
 */
 public void setWeightMapFile(String weightMapPath) throws IOException{
 Properties props=new Properties();
 try(BufferedReader reader=new BufferedReader(new InputStreamReader(this.getClass().getClassLoader().getResourceAsStream(weightMapPath),DEFAULT_CHARSET))){
 props.load(reader);
 for(Map.Entry entry:props.entrySet()){
 int weight=Integer.parseInt(entry.getValue().toString());
 weightMap.put(Integer.parseInt(entry.getKey().toString()),weight>0?weight:1);
 }
 }
 }
 // /*
 // 保存一致性hash的虚拟节点文件路径。
 // 如果这个文件不存在或是空文件就按照指定的count,weightMapFile等构造新的MurmurHash数据结构并保存到这个路径的文件里。
 // 如果这个文件已存在且不是空文件就加载这个文件里的内容作为MurmurHash数据结构，此时其它参数都忽略。
 // 除第一次以外在之后增加节点时可以直接修改这个文件，不过不推荐这么做。如果节点数量变化了，推荐删除这个文件。
 // 可以不指定这个路径，不指定路径时不会保存murmur hash
 // @param bucketMapPath
 // /
 // public void setBucketMapPath(String bucketMapPath){
 // this.bucketMapPath=bucketMapPath;
 // }
 @Override
 public Integer calculate(String columnValue) {
 SortedMap<Integer,Integer> tail = bucketMap.tailMap(hash.hashUnencodedChars(columnValue).asInt());
 if (tail.isEmpty()) {
 return bucketMap.get(bucketMap.firstKey());
 }
 return tail.get(tail.firstKey());
 }
@Override
public int getPartitionNum() {
int nPartition = this.count;
return nPartition;
}

private static void hashTest() throws IOException{
PartitionByMurmurHash hash=new PartitionByMurmurHash();
hash.count=10;//分片数
 hash.init();
```
 [] bucket= <Integer,List<Integer>> hashed= HashMap<> total=1000_0000;
 c=0( i=100_0000;i<total+100_0000;i++){
 c++ h=++<Integer> list=(list=== ArrayList<>+" "+ d=0=0 idx=0"index bucket ratio"(+=i/(+=+++" "+i+" "+(i/(+" "+=="****************************************************"// rehashTest(hashed.get(0));
 }
private static void rehashTest(List partition){
PartitionByMurmurHash hash=new PartitionByMurmurHash();
hash.count=12;//分片数
 hash.init();
```
 [] bucket= total=partition.size();
 c=0( i:partition){
 c++ h=+++" "+=0 idx=0"index bucket ratio"(+=+++" "+i+" "+(i/(public static void main(String[] args) throws IOException {
hashTest();
}
}

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!

Mysql系列六：（Mycat分片路由原理、Mycat常用分片规则及对应源

?一、Mycat分片路由原理

二、Mycat常用分片规则

三、Mycat常用分片规则介绍

1. 自动范围分片

2. 枚举分片

3. Hash固定分片（固定分片Hash算法）

4.?求模分片

5.?自然月分片

6.?匹配求模分片

7.?冷热数据分片

8.?一致性哈希分片