网站首页mysql

mysql简单水平分表方案

发布时间:2021-06-15 11:26:25编辑:slayer.hover阅读(780)

    一.假设当前有1000w条数据, 要将这些数据平均的分布到10张表中


    1. 创建A-J10张表, 包含主键hash_id及其它数据字段,  所有表的字段相同.

    A表    hash_id,name,****, created_at
    B表    hash_id,name,****, created_at
    C表    hash_id,name,****, created_at
    ...
    J表    hash_id,name,****, created_at


    2. 创建总表union_table, 仅包含字段: id(自增主键), hash_id(索引), created_at.

    union_table表    id,  hash_id, created_at

    总表中将包含有所有的hash_id.


    3. 插入数据.

        a> 先在union_table插入一条自增数据, 获取到自增id.

        b> 将id进行hash运算, 得到hash_id串, 作为子表的主键, 并更新到union_table的hash_id字段中.

             参考: https://www.top51.cn/index/view/id/3.html 最后一段.

        c> 将id取余id%10, 用结果决定将这条数据插入哪个子表.


    4. 获取按created_at排序的分页列表数据.

        a> 在总表中取到所有的hash_id   

    result_hashids =[select hash_id from union_table order by created_at desc limit 500000, 10];

        b> 在子表中联合查询

            

    select * from A where hash_id (in result_hashids)
    union all
    select * from B where hash_id (in result_hashids)
    union all
    select * from C where hash_id (in result_hashids)
    ...
    union all
    select * from J where hash_id (in result_hashids)

            

    5. 按hash_id取数据.

        a> 将hash_id解码后取余, 确定要从哪张子表里读数据

    id = decode("vla4YyflvmMnIs1cgoV0Q");
    table_index = id % 10;

        b> 在子表中根据hash_id直接查询数据

    select * from X where hash_id='vla4YyflvmMnIs1cgoV0Q'



    二.预期数据量未知, 按单表数据限额(如: 50w条), 动态增加分表

    1. 创建个配置表setting_table, 包含字段: db(值为当前工作子表: A).

    setting_table表    id,  db


    2. 创建总表union_table, 仅包含字段: id(自增主键), hash_id(索引), created_at.

    union_table表    id,  hash_id, created_at

    总表中将包含有所有的hash_id.


    3. 创建第一张子表, 包含主键hash_id及其它数据字段, 所有子表字段相同.

    A表    hash_id,name,****, created_at


    4. 插入数据.

        a> 根据union_table的数据量, 判断是否需要添加新子表, 若添加新子表,则更新setting_table中的当前工作子表字段值

        a> 先在union_table插入一条自增数据, 获取到自增id.

        b> 生成子表主键hash_id, 此处的hashid与上面的有所不同, 需要将自增id与当前工作表, 合并进行hash运算.

              如: 当前的工作子表为A, 在union_table中的自增ID为1, 则将("A-1")整体进行hash计算.

    hash_id = id_encode("A-1");

              将得到的hash串, 作为子表主键, 并更新到union_table的hash_id字段中.         
              生成hashid可参考: https://www.top51.cn/index/view/id/3.html 倒数第二段.

        c> 插入当前工作子表数据.


    5. 按hash_id取数据.

        a> 将hash_id解码后, 可确定从哪张子表里读数据

    id = id_decode("jfeww5evay47z6b4aluiarjxwi");
    table = explode('-', id)[0];

        b> 在子表中根据hash_id直接查询数据

    select * from table where hash_id='jfeww5evay47z6b4aluiarjxwi'






评论