分库分表的计算

mameng1998

于 2023-03-30 19:58:24 发布

阅读量74

点赞数

分类专栏： java mysql 文章标签： java servlet 开发语言

本文链接：https://blog.csdn.net/mameng1988/article/details/129865075

版权

java 同时被 2 个专栏收录

45 篇文章 3 订阅

订阅专栏

mysql

30 篇文章 1 订阅

订阅专栏

通过hash计算，代码如下：

1 定义基本sql、常量

/**
 * shard 4 databases. cir_0, cir_1, cir_2, cir_3
*/
private static final int CIRCLE_SHARD_DB_COUNT = 4;
/**
 * shard 64 tables. cir_base_0~15
*/
private static final int CIRCLE_SHARD_TB_COUNT = 16;
private static final int CIRCLE_SHARD_COUNT = CIRCLE_SHARD_DB_COUNT * CIRCLE_SHARD_TB_COUNT;


private static final String COLUMN_CID = "column_cid";
private static final String COLUMN_UID = "column_uid";

private static final String CIRCLE_DB_HASH = "$dbhash";
private static final String CIRCLE_TB_HASH = "$tbhash";
private static final String CIRCLE_SCHEMA = "db_cir_" + CIRCLE_DB_HASH + "tb.cir_base_" + CIRCLE_TB_HASH;



private static final String QUERY_LATEST_CIRCLE_IDS_BY_UID
            = "SELECT " + COLUMN_CID + " FROM " + CIRCLE_SCHEMA + " WHERE " + COLUMN_UID + " = ? AND " +
            COLUMN_PRIVACY + " = 1" +
            " ORDER BY " + COLUMN_CID + " DESC LIMIT ?";

2 根据基本sql，获取分库分表后的sql

public String getSqlString(String baseSql, long ownerid){
        int hash = HashUtil.hash(ownerid, CIRCLE_SHARD_COUNT);
        int dbhash = hash / CIRCLE_SHARD_TB_COUNT;
        int tbhash = hash % CIRCLE_SHARD_TB_COUNT;
        String sql = baseSql.replace(CIRCLE_DB_HASH, String.valueOf(dbhash));
        sql = sql.replace(CIRCLE_TB_HASH, String.valueOf(tbhash));
        LOGGER.debug("sql={}", sql);
        return sql;
}

3 工具类

package com.mico.util.misc;

import com.mico.util.codec.Encoder;

import java.util.zip.CRC32;

/**
 * 
 */
public final class HashUtil {
    private static final int DEFAULT_NUM = 8;

    private static final ThreadLocal<CRC32> CRC32_HOLDER = new ThreadLocal<CRC32>() {
        @Override
        protected CRC32 initialValue() {
            return new CRC32();
        }
    };

    public static final long crc32(String id) {
        CRC32 crc = CRC32_HOLDER.get();
        crc.reset();
        crc.update(Encoder.encodeString(id));
        return crc.getValue();
    }

    public static final int hash(long id) {
        return hash(String.valueOf(id));
    }

    public static final int hash(long id, int num) {
        return hash(String.valueOf(id), num);
    }

    public static final int hash(String id) {
        return hash(id, DEFAULT_NUM);
    }

    public static final int hash(String id, int num) {
        long h = crc32(id);
        h = Math.abs(h);
        int hash = (int) (h / num % num);
        return hash;
    }

}

4 main方法

public static void main(String[] args) {
        int hash = HashUtil.hash("1539425379524502548", CIRCLE_SHARD_COUNT);
        int dbhash = hash / CIRCLE_SHARD_TB_COUNT;
        int tbhash = hash % CIRCLE_SHARD_TB_COUNT;

        System.out.println(dbhash);
        System.out.println(tbhash);
}