数据库分表

分表思路

分表的背景

  在我们保存数据时，有时候会遇到保存大数据量的情况。而我就遇到过这种情况，比如说存储卫星定位数据。初步估计，其在3个月内的数据量大致为2亿条。

  至于为什么是3个月，这是由于该需求正处于探索阶段，先整3个月的试用期，待试用期过后再进行相应评估，是继续做，还是停掉，因而暂时也只需要存储3个月的数据量。

  而如果采用传统的单表操作，那么越往后期，该表中的数据存储量将会越来越庞大，那么其不可避免的会导致后续的数据读写效率的低下性，为了解决该问题，我采用的就是下面的分表策略。

分表的计算

   在确定了选用分表策略后，接下来就是需要分多少张表了，经过计算，平均每张表存储300-400万条数据量的情况下是最合理的，而如果分表过少，则会导致单表的数据存储量上升，进而会导致后续定位数据读写性能的下降。而如果平均每张表存储比较少的数据量，则需要创建更多的分表，那么随之而来的问题则是如何维护数量庞大的分表。

   而另外一个需要注意的事情是，如果采用分表策略，当分表数为2的n次方的分表策略是合理的，这和手机存储为什么都是2的n次方的原理一样。

   在考虑到上述条件之后，我们初步计算公式如下：

分表数*每张表数据量=2亿。
上述公式需满足如下条件：
分表数为2的n次方。
每张表数据量范围300万至400万

   上述公式的计算结果为：

分表数：64
每张表的数据量为：3125000条

分表的存储规则

   在存储数据时，我们需要将数据尽可能均匀的落到每张分表中，尽量做到每张分表的数据存储量是大致相似的，因而我很快的想到了“散列”，也就是人们常说的“哈希（Hash）”。说完就干，于是我很快的就写好测试代码，然后测试。

   经过测试，我发现很多重复数据，于是采用Set集合进行去重，然后再测，发现数据分散大致比较均匀，但是还是有部分表分到了大量的数据，可能是这些数据之间的差别比较小所致。于是我又想到了MD5，由于MD5是初始数据的微小改动，就会导致后续数据出现较大的不同，也就是说MD5其实是有将微小差别放大的功能，于是我先进行MD5计算，然后再执行Hash，最后的测试结果基本满足需求，定位数据基本上均匀的分布到每一张表中。

   由于测试用例并没有保存下来，所以说这里就不用再论述了。毕竟下面的代码中有测试用例的最终可执行版本。

分表的索引值

   我们在分表的运用中，切记，不要涉及到跨表数据查询，因为所要查询的数据一旦跨表，则会导致数据的全表扫描查询，这和不分表就没有区别了，因而在分表前，一定要考虑到，不要涉及到数据的跨表查询操作。

   我们的项目在分表时，表索引的创建比较麻烦。因为我们的数据根据业务来说，主要是有两类，其一是根据司机的手机号进行的定位，其查询必然需要依赖手机号创建定位表索引；与此同时我们还存在根据司机车牌号进行的定位，因而其查询也必然会出现需要依赖车牌号创建定位表索引。而我们的分表索引只有一个，这意味着，如果要是依赖手机号作为索引，则会导致以车牌号存储的定位数据在查询时出现跨表操作，反之亦然。这样一来，我们就很难在此处的业务逻辑中采用分表策略了，貌似现在分表策略已经进入了死胡同。

  在后续的与业务人员的沟通中，我们了解到手机号与车牌号定位数据虽然都存储在一张表中（即表中同时存在手机号与车牌号字段），但是在一条定位数据中，其并不会同时出现手机号与车牌号，也就是说手机号与车牌号是一种互斥关系，因而我将手机号与车牌号拼接起来，共同组成了一个字符串，然后再对其执行分表的索引值计算，经过实测，这样做是可以的，下面是其详细的实现代码。

源码

查询数据表索引值

  根据手机号或车牌号查询数据表索引值，并返回相应的查询结果

public ResultData locationStorageIndex(Map<String, String> param) {
    //根据手机号或车牌号查看定位数据存储表索引id
    Integer index = locationStorageIndex(param.get("mobile"),param.get("carNo"));
    if(null == index){
        return new ResultData("查询失败！",ResultData.RESULT_FAILURE);
    }
    return null == index ? new ResultData("查询失败！",ResultData.RESULT_FAILURE) : new ResultData("查询成功！",ResultData.RESULT_SUCCESS,index);
}

  调用SubmeterUtil工具类，根据手机号或车牌号生成数据表索引值

public Integer locationStorageIndex(String mobile,String carNo){
    //生成数据表索引值
    return SubmeterUtil.newSubmeterUtil().generateSubmeterIndex(mobile,carNo);
}

  根据手机号或车牌号生成分表索引核心工具类：SubmeterUtil

...
import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang.builder.HashCodeBuilder;
import org.apache.log4j.Logger;

/**
 * 分表核心工具类
 */
public class SubmeterUtil {

    private static final Logger logger = Logger.getLogger(SubmeterUtil.class);

    /**
     * 功能描述: 创建空的构造函数
     */
    public SubmeterUtil() {
    }

    /**
     * 功能描述: 获取一个新的静态实例
     */
    public static SubmeterUtil newSubmeterUtil(){
        return new SubmeterUtil();
    }

    /**
     * 功能描述: 完成手机号与车牌号的字符串拼接操作
     * @param: phone 手机号
     * @param: carNo 车牌号
     */
    private String join(String phone,String carNo){
        String union = "";
        //如果手机号为空，则将手机号定为“”
        if(StringUtils.isEmpty(phone)){
            phone = "";
        }
        //如果车牌号为空，则将车牌号定位“”
        if(StringUtils.isEmpty(carNo)){
            carNo = "";
        }
        //由于手机号与车牌号是一种互斥关系，则必然有一个存在，因而当全部不存在时，直接返回null
        if("".equals(phone) && "".equals(carNo)){
            return null;
        }
        //返回字符串拼接后的结果
        return phone + carNo;
    }

    /**
     * 功能描述: 此处执行核心分表算法，获取分表索引值（默认分成64张表，其中每张表3个月内的预估数据上线为3，125，000条）
     * @param: union 手机号与车牌号合并后的字符串
     */
    private Integer getSubmeterIndex(String union){
        //如果合并后的字符串为空，则直接返回null
        if(null == union){
            return null;
        }
        int hashCode;
        //将手机号进行MD5运算
        String unionMD5 = MD5Util.encrypt(union);
        //log.info("计算后的MD5值为：{}",phoneMD5);
        //然后将MD5值进行哈希算法
        int unionMD5Integer = HashCodeBuilder.reflectionHashCode(unionMD5);
        //log.info("转换成int类型的数值为：{}",phoneMD5Integer);
        //取模运算
        hashCode = unionMD5Integer < 0 ? - unionMD5Integer : unionMD5Integer;
        //log.info("hashCode：{}",hashCode);
        //取余运算
        int mod = hashCode % 64;
        logger.info("运算获得的索引值为：" + mod);
        return mod;
    }

    /**
     * 功能描述: 根据手机号与车牌号生成分表索引值
     * @param phone 手机号
     * @param carNo 车牌号
     */
    public Integer generateSubmeterIndex(String phone,String carNo){
        //合并手机号与车牌号
        String union = join(phone,carNo);
        //经过核心算法处理，生成分表索引值
        Integer mod = getSubmeterIndex(union);
        //如果生成的数字为null，则证明其传入的参数有误，因而也直接返回null
        if(null == mod){
            return null;
        }
        return mod;
    }

}

  MD5核心算法工具类：MD5Util工具类

...
import org.apache.log4j.Logger;

import java.security.MessageDigest;

/**
 * 功能描述: MD5工具类
 */
public class MD5Util {

    private static final Logger logger = Logger.getLogger(MD5Util.class);

    public final static String encrypt(String s) {
        char hexDigits[] = { '0', '1', '2', '3', '4', '5', '6', '7', '8', '9', 'A', 'B', 'C', 'D', 'E', 'F' };
        try {
            byte[] btInput = s.getBytes();
            // 获得MD5摘要算法的 MessageDigest 对象
            MessageDigest mdInst = MessageDigest.getInstance("MD5");
            // 使用指定的字节更新摘要
            mdInst.update(btInput);
            // 获得密文
            byte[] md = mdInst.digest();
            // 把密文转换成十六进制的字符串形式
            int j = md.length;
            char str[] = new char[j * 2];
            int k = 0;
            for (int i = 0; i < j; i++) {
                byte byte0 = md[i];
                str[k++] = hexDigits[byte0 >>> 4 & 0xf];
                str[k++] = hexDigits[byte0 & 0xf];
            }
            return new String(str);
        } catch (Exception e) {
            logger.error("generate md5 error：" + s, e);
            return null;
        }
    }
}

在分表中插入数据

  在分表中插入数据，并返回相应的请求结果

public ResultData insertLocationStorage(Map<String, String> param) {
    LocationStorage locationStorage = LocationStorageConversionUtil.newLocationStorageConversionUtil().changeMapToLocationStorageEntity(param);
    //如果为null,则说明数据转换失败，那么直接返回。
    if(null == locationStorage){
        logger.error("insertLocationStorage -> 定位数据插入失败！");
        return new ResultData("定位数据插入失败！",ResultData.RESULT_FAILURE);
    }
    //执行数据的插入操作
    LocationStorage locationStorageResult = insertLocationStorageEntity(locationStorage);
    //如果插入成功，则返回的结果中含有主键id的数值
    return null == locationStorageResult ? new ResultData("定位数据插入失败！",ResultData.RESULT_FAILURE) : new ResultData("定位数据插入成功！",ResultData.RESULT_SUCCESS,locationStorageResult);
}

  将Map<String, String> param 与 LocationStorage 相互转换的工具类：LocationStorageConversionUtil

...

import com.gexin.fastjson.JSON;
...
import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;

import java.util.Date;
import java.util.Map;

/**
 * 功能描述： 将Map<String, String> param 与 LocationStorage 相互转换的工具类
 */
public class LocationStorageConversionUtil {

    private static final Logger logger = Logger.getLogger(LocationStorageConversionUtil.class);

    /**
     * 功能描述: 创建空的构造函数
     */
    public LocationStorageConversionUtil() {
    }

    /**
     * 功能描述: 实例化一个LocationStorageConversionUtil工具类
     */
    public static LocationStorageConversionUtil newLocationStorageConversionUtil(){
        return new LocationStorageConversionUtil();
    }

    /**
     * 功能描述: 将Map<String, String> param数据类型转换成LocationStorage实体类
     */
    public LocationStorage changeMapToLocationStorageEntity(Map<String, String> param){
        //将map转换成实体类
        LocationStorage locationStorage = dataConversion(param);
        //如果转换失败，则直接返回null
        if(null == locationStorage){
            return null;
        }
        //数据类型判断
        Integer dataSourceType = judgeDataSource(locationStorage.getMobile(),locationStorage.getCarNo());
        //如果数据类型不存在，则直接返回null
        if(null == dataSourceType){
            return null;
        }
        //否则的话执行数据的初始化操作
        //初始化数据源类型
        locationStorage.setDataSource(dataSourceType);
        //初始化生成时间
        locationStorage.setCreateTime(new Date());
        return locationStorage;
    }

    /**
     * 功能描述: 数据类型判断
     */
    public Integer judgeDataSource(String mobile, String carNo) {
        //如果手机号为空，则置为“”
        if(StringUtils.isEmpty(mobile)){
            mobile = "";
        } else {
            //如果存在，则直接返回
            return CommonType.locationStorage.LBS;
        }

        //如果车牌号为空，则置为“”
        if(StringUtils.isEmpty(carNo)){
            carNo = "";
        } else {
            //如果存在，则直接返回
            return CommonType.locationStorage.ZJ;
        }
        //如果两个都不存在，则直接返回null
        if("".equals(mobile) && "".equals(carNo)){
            return null;
        }
        return null;
    }

    /**
     * 功能描述: 将map转换成实体类
     */
    private LocationStorage dataConversion(Map<String,String> param) {
        //将Map转换成json
        String paramStr = JSON.toJSONString(param);
        //将json转换成实体类
        return JSON.parseObject(paramStr,LocationStorage.class);
    }

}

  将实体类中的数据插入到分表中，并返回相应的结果

public LocationStorage insertLocationStorageEntity(LocationStorage locationStorage){
    //生成数据表索引值
    Integer tableIndex = SubmeterUtil.newSubmeterUtil().generateSubmeterIndex(locationStorage.getMobile(),locationStorage.getCarNo());
    //如果生成失败，则不执行任何操作，直接返回null
    if(null == tableIndex){
        return null;
    }
    //设置定位数据存储表的索引值
    locationStorage.setTableIndex(tableIndex);
    logger.info("当前操作的定位数据存储表t_location_storage的索引值为：" + tableIndex);
    //如果生成成功，则执行数据的插入操作
    int flag = locationStorageMapper.insertLocationStorage(locationStorage);
    //如果数据新增成功，将新增的数据返回，如果数据新增失败，则直接返回null
    return flag > 0 ? locationStorage : null;
}

  保存定位数据

int insertLocationStorage(LocationStorage locationStorage);

  保存的SQL语句：

<insert id="insertLocationStorage" useGeneratedKeys="true" keyProperty="id" keyColumn="id">
    insert into t_location_storage_${tableIndex}
    <trim prefix="(" suffix=")" suffixOverrides=",">
      <if test="id != null">
        id,
      </if>
      <if test="dataSource != null">
        data_source,
      </if>
      <if test="mobile != null">
        mobile,
      </if>
      <if test="carNo != null">
        car_no,
      </if>
     ...
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides=",">
      <if test="id != null">
        #{id,jdbcType=BIGINT},
      </if>
      <if test="dataSource != null">
        #{dataSource,jdbcType=INTEGER},
      </if>
      <if test="mobile != null">
        #{mobile,jdbcType=VARCHAR},
      </if>
      <if test="carNo != null">
        #{carNo,jdbcType=VARCHAR},
      </if>
      ...
    </trim>
  </insert>