数据库分表

r囧r小猫 2022-03-19 18:47 220阅读 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,最后的测试结果基本满足需求,定位数据基本上均匀的分布到每一张表中。

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

分表的索引值

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

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

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

源码

查询数据表索引值

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

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

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

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

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

  1. ...
  2. import org.apache.commons.lang.StringUtils;
  3. import org.apache.commons.lang.builder.HashCodeBuilder;
  4. import org.apache.log4j.Logger;
  5. /** * 分表核心工具类 */
  6. public class SubmeterUtil {
  7. private static final Logger logger = Logger.getLogger(SubmeterUtil.class);
  8. /** * 功能描述: 创建空的构造函数 */
  9. public SubmeterUtil() {
  10. }
  11. /** * 功能描述: 获取一个新的静态实例 */
  12. public static SubmeterUtil newSubmeterUtil(){
  13. return new SubmeterUtil();
  14. }
  15. /** * 功能描述: 完成手机号与车牌号的字符串拼接操作 * @param: phone 手机号 * @param: carNo 车牌号 */
  16. private String join(String phone,String carNo){
  17. String union = "";
  18. //如果手机号为空,则将手机号定为“”
  19. if(StringUtils.isEmpty(phone)){
  20. phone = "";
  21. }
  22. //如果车牌号为空,则将车牌号定位“”
  23. if(StringUtils.isEmpty(carNo)){
  24. carNo = "";
  25. }
  26. //由于手机号与车牌号是一种互斥关系,则必然有一个存在,因而当全部不存在时,直接返回null
  27. if("".equals(phone) && "".equals(carNo)){
  28. return null;
  29. }
  30. //返回字符串拼接后的结果
  31. return phone + carNo;
  32. }
  33. /** * 功能描述: 此处执行核心分表算法,获取分表索引值(默认分成64张表,其中每张表3个月内的预估数据上线为3,125,000条) * @param: union 手机号与车牌号合并后的字符串 */
  34. private Integer getSubmeterIndex(String union){
  35. //如果合并后的字符串为空,则直接返回null
  36. if(null == union){
  37. return null;
  38. }
  39. int hashCode;
  40. //将手机号进行MD5运算
  41. String unionMD5 = MD5Util.encrypt(union);
  42. //log.info("计算后的MD5值为:{}",phoneMD5);
  43. //然后将MD5值进行哈希算法
  44. int unionMD5Integer = HashCodeBuilder.reflectionHashCode(unionMD5);
  45. //log.info("转换成int类型的数值为:{}",phoneMD5Integer);
  46. //取模运算
  47. hashCode = unionMD5Integer < 0 ? - unionMD5Integer : unionMD5Integer;
  48. //log.info("hashCode:{}",hashCode);
  49. //取余运算
  50. int mod = hashCode % 64;
  51. logger.info("运算获得的索引值为:" + mod);
  52. return mod;
  53. }
  54. /** * 功能描述: 根据手机号与车牌号生成分表索引值 * @param phone 手机号 * @param carNo 车牌号 */
  55. public Integer generateSubmeterIndex(String phone,String carNo){
  56. //合并手机号与车牌号
  57. String union = join(phone,carNo);
  58. //经过核心算法处理,生成分表索引值
  59. Integer mod = getSubmeterIndex(union);
  60. //如果生成的数字为null,则证明其传入的参数有误,因而也直接返回null
  61. if(null == mod){
  62. return null;
  63. }
  64. return mod;
  65. }
  66. }

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

  1. ...
  2. import org.apache.log4j.Logger;
  3. import java.security.MessageDigest;
  4. /** * 功能描述: MD5工具类 */
  5. public class MD5Util {
  6. private static final Logger logger = Logger.getLogger(MD5Util.class);
  7. public final static String encrypt(String s) {
  8. char hexDigits[] = { '0', '1', '2', '3', '4', '5', '6', '7', '8', '9', 'A', 'B', 'C', 'D', 'E', 'F' };
  9. try {
  10. byte[] btInput = s.getBytes();
  11. // 获得MD5摘要算法的 MessageDigest 对象
  12. MessageDigest mdInst = MessageDigest.getInstance("MD5");
  13. // 使用指定的字节更新摘要
  14. mdInst.update(btInput);
  15. // 获得密文
  16. byte[] md = mdInst.digest();
  17. // 把密文转换成十六进制的字符串形式
  18. int j = md.length;
  19. char str[] = new char[j * 2];
  20. int k = 0;
  21. for (int i = 0; i < j; i++) {
  22. byte byte0 = md[i];
  23. str[k++] = hexDigits[byte0 >>> 4 & 0xf];
  24. str[k++] = hexDigits[byte0 & 0xf];
  25. }
  26. return new String(str);
  27. } catch (Exception e) {
  28. logger.error("generate md5 error:" + s, e);
  29. return null;
  30. }
  31. }
  32. }

在分表中插入数据

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

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

  将Map param 与 LocationStorage 相互转换的工具类:LocationStorageConversionUtil

  1. ...
  2. import com.gexin.fastjson.JSON;
  3. ...
  4. import org.apache.commons.lang.StringUtils;
  5. import org.apache.log4j.Logger;
  6. import java.util.Date;
  7. import java.util.Map;
  8. /** * 功能描述: 将Map<String, String> param 与 LocationStorage 相互转换的工具类 */
  9. public class LocationStorageConversionUtil {
  10. private static final Logger logger = Logger.getLogger(LocationStorageConversionUtil.class);
  11. /** * 功能描述: 创建空的构造函数 */
  12. public LocationStorageConversionUtil() {
  13. }
  14. /** * 功能描述: 实例化一个LocationStorageConversionUtil工具类 */
  15. public static LocationStorageConversionUtil newLocationStorageConversionUtil(){
  16. return new LocationStorageConversionUtil();
  17. }
  18. /** * 功能描述: 将Map<String, String> param数据类型转换成LocationStorage实体类 */
  19. public LocationStorage changeMapToLocationStorageEntity(Map<String, String> param){
  20. //将map转换成实体类
  21. LocationStorage locationStorage = dataConversion(param);
  22. //如果转换失败,则直接返回null
  23. if(null == locationStorage){
  24. return null;
  25. }
  26. //数据类型判断
  27. Integer dataSourceType = judgeDataSource(locationStorage.getMobile(),locationStorage.getCarNo());
  28. //如果数据类型不存在,则直接返回null
  29. if(null == dataSourceType){
  30. return null;
  31. }
  32. //否则的话执行数据的初始化操作
  33. //初始化数据源类型
  34. locationStorage.setDataSource(dataSourceType);
  35. //初始化生成时间
  36. locationStorage.setCreateTime(new Date());
  37. return locationStorage;
  38. }
  39. /** * 功能描述: 数据类型判断 */
  40. public Integer judgeDataSource(String mobile, String carNo) {
  41. //如果手机号为空,则置为“”
  42. if(StringUtils.isEmpty(mobile)){
  43. mobile = "";
  44. } else {
  45. //如果存在,则直接返回
  46. return CommonType.locationStorage.LBS;
  47. }
  48. //如果车牌号为空,则置为“”
  49. if(StringUtils.isEmpty(carNo)){
  50. carNo = "";
  51. } else {
  52. //如果存在,则直接返回
  53. return CommonType.locationStorage.ZJ;
  54. }
  55. //如果两个都不存在,则直接返回null
  56. if("".equals(mobile) && "".equals(carNo)){
  57. return null;
  58. }
  59. return null;
  60. }
  61. /** * 功能描述: 将map转换成实体类 */
  62. private LocationStorage dataConversion(Map<String,String> param) {
  63. //将Map转换成json
  64. String paramStr = JSON.toJSONString(param);
  65. //将json转换成实体类
  66. return JSON.parseObject(paramStr,LocationStorage.class);
  67. }
  68. }

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

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

  保存定位数据

  1. int insertLocationStorage(LocationStorage locationStorage);

  保存的SQL语句:

  1. <insert id="insertLocationStorage" useGeneratedKeys="true" keyProperty="id" keyColumn="id">
  2. insert into t_location_storage_${tableIndex}
  3. <trim prefix="(" suffix=")" suffixOverrides=",">
  4. <if test="id != null">
  5. id,
  6. </if>
  7. <if test="dataSource != null">
  8. data_source,
  9. </if>
  10. <if test="mobile != null">
  11. mobile,
  12. </if>
  13. <if test="carNo != null">
  14. car_no,
  15. </if>
  16. ...
  17. </trim>
  18. <trim prefix="values (" suffix=")" suffixOverrides=",">
  19. <if test="id != null">
  20. #{id,jdbcType=BIGINT},
  21. </if>
  22. <if test="dataSource != null">
  23. #{dataSource,jdbcType=INTEGER},
  24. </if>
  25. <if test="mobile != null">
  26. #{mobile,jdbcType=VARCHAR},
  27. </if>
  28. <if test="carNo != null">
  29. #{carNo,jdbcType=VARCHAR},
  30. </if>
  31. ...
  32. </trim>
  33. </insert>

发表评论

表情:
评论列表 (有 0 条评论,220人围观)

还没有评论,来说两句吧...

相关阅读

    相关 数据库分库

    为什么分? 业务需求的不断增加,数据量越来越大,因此数据的增删改查开销越来越大。再加上物理服务器的资源也是有限的。最终数据库的处理能力,承载的数量都会达到瓶颈。就需要合理的

    相关 数据库分库

    数据库分库分表的应用场景及解决方案   现实业务场景中,为了保障客户体验并满足业务的线性增长。会对数据量巨大,且业务会始终进行的产品进行分表分库策略。但是如何合理的根据业务采

    相关 数据库分库

    一、 基本思想 Sharding的基本思想就要把一个数据库切分成多个部分放到不同的数据库(server)上,从而缓解单一数据库的性能问题。对于海量数据的数据库,