数据库分表

r囧r小猫 2022-03-19 18:47 215阅读 0赞

### 文章目录 ###

*  分表思路
 *   *  分表的背景
     *  分表的计算
     *  分表的存储规则
     *  分表的索引值
 *  源码
 *   *  查询数据表索引值
     *  在分表中插入数据

# 分表思路 #

## 分表的背景 ##

在我们保存数据时，有时候会遇到保存大数据量的情况。而我就遇到过这种情况，比如说存储卫星定位数据。初步估计，其在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>