精华内容
参与话题
问答
  • 简谈数据库分库分表

    万次阅读 2020-04-29 14:17:47
    为何分表 数据库架构 1、垂直分区 2、水平分区(Sharding) 单单表 单多表 一主多备 为何分表 当一张表的数据达到千万级时甚至亿级时,查询一次所花的时间会变多,如果有联合查询的话可能会死在那儿了。...

    为何分表

    数据库架构

    1、垂直分区

    2、水平分区(Sharding)

    单库单表

    单库多表

    一主多备


    为何分表

    当一张表的数据达到千万级时甚至亿级时,查询一次所花的时间会变多,如果有联合查询的话可能会死在那儿了。分表的目的就在于此,减小数据库的负担,缩短查询时间。

    • 大数据量并且访问频繁的表,将其分为若干个表:

           比如对于某些网站平台的数据库表,数据量很大,这种能预估出来的大数据量表,我们就事先分出个N个表,这个N是多少,根据实际情况而定。

      某网站现在的数据量假设是5000万条,可以设计每张表容纳的数据量是500万条,也就是拆分成10张表。

           那么如何判断某张表的数据是否容量已满呢?可以在程序段对于要新增数据的表,在插入前先做统计表记录数量的操作,当<500万条数据,就直接插入,当已经到达阀值,可以在程序段新创建数据库表(自动扩容),再执行插入操作。

    数据库架构

    1.简单的数据库(PG/MySQL)主从复制:
    数据库的主从复制解决了数据库的读写分离,(一主多备)能很好的提升读的性能,其图如下:

    在这里插入图片描述

    但是,主从复制也带来其他一系列性能瓶颈问题:
    :---------:1.写入无法扩展
    :---------:2.写入无法缓存
    :---------:3.复制延时
    :---------:4.锁表率上升
    :---------:5.表变大,缓存率下降

    那问题产生总得解决的,这就产生下面的优化方案,一起来看看。

    1、垂直分区

    如果把业务切割得足够独立,那把不同业务的数据放到不同的数据库服务器将是一个不错的方案,而且万一其中一个业务崩溃了也不会影响其他业务的正常进行,并且也起到了负载分流的作用,大大提升了数据库的吞吐能力。经过垂直分区后的数据库架构图如下:

    在这里插入图片描述

    已经足够独立了,但是有些业务之间或多或少总会有点联系,如用户,基本上都会和每个业务存在关联,况且这种分区方式,也不能解决单张表数据量暴涨的问题。

    2、水平分区(Sharding)

    将用户按一定规则(按id哈希)分组,并把该组用户的数据存储到一个数据库分片中,即一个sharding,这样随着用户数量的增加,只要简单地配置一台服务器即可,原理图如下:

    在这里插入图片描述

      如何来确定某个用户所在的shard呢,这个时候我们可以建一张用户和shard对应的数据表,每次请求先从这张表找用户的shard id,再从对应shard中查询相关数据,如下图所示:

    在这里插入图片描述

    • 单库单表

    单库单表是最常见的数据库设计,例如,有一张用户(user)表放在数据库db中,所有的用户都可以在db库中的user表中查到

    • 单库多表

      随着用户数量的增加,user表的数据量会越来越大,当数据量达到一定程度的时候对user表的查询会渐渐的变慢,从而影响整个DB的性能。还有一个更严重的问题是,事务提交会锁表,期间所有的读写操作只能等待。

    • 一主多备

      在实际的应用中,大部分情况都是读远大于写。DB提供了读写分离的机制,所有的写操作都必须对应到Master,读操作可以在 Master和Slave机器上进行,Slave与Master的结构完全一样,一个Master可以有多个Slave,甚至Slave下还可以挂 Slave,通过此方式可以有效的提高DB集群的 QPS.

      所有的写操作都是先在Master上操作,然后同步更新到Slave上,所以从Master同步到Slave机器有一定的延迟,当系统很繁忙的时候,延迟问题会更加严重,Slave机器数量的增加也会使这个问题更加严重。

    此外,可以看出Master是集群的瓶颈,当写操作过多,会严重影响到Master的稳定性,如果Master挂掉,整个集群都将不能正常工作。

     

    展开全文
  • 分库分表算法: 1.算法(零散均匀分库): 1.1)分库分表规则 中间变量=USER_ID%(分库数量*每个库的表数量) 库=中间变量/每个库的表数量 表=中间变量%每个库的表数量 1.2)查看用户所在的库、表 SET @userId = 65; SET @...

    说明:

    timestamp时间即将耗尽,使用datetime 替换 timestamp:
    1)区别:时区影响:对于TIMESTAMP,它把客户端插入的时间从当前时区转化为UTC(世界标准时间)进行存储。查询时,将其又转化为客户端当前时区进行返回;而对于DATETIME,不做任何改变,基本上是原样输入和输出。
            存储空间:timestamp占用4个字节,datetime存储占用8个字节
            时间范围:
                  timestamp可表示范围:1970-01-01 00:00:00~2038-01-09 03:14:07,
              datetime支持的范围更宽1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
    2)替换原因:timestamp会在2038年耗尽,timestamp的DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP属性,
       datetime完美兼容多时区业务,不必考虑时区影响, datetime类型列的默认值也可以在业务代码中指定,或建表直接指定例如:
    CREATE TABLE table_name(
        id bigint(22) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
        inserttime datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '插入时间',
        updatetime datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
        isactive tinyint(1) NOT NULL DEFAULT '1' COMMENT '逻辑删除(1:保留,0:删除)'
        PRIMARY KEY (id),
        KEY idx_inserttime (inserttime),
        KEY idx_updatetime (updatetime)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='table_name_describle';

    一.分库分表算法:
    1.算法(零散均匀分库):
    1.1)分库分表规则
    中间变量=USER_ID%(分库数量*每个库的表数量)
    库=中间变量/每个库的表数量
    表=中间变量%每个库的表数量

    1.2)查看用户所在的库、表
    SET @userId = 65;
    SET @dbCount = 8;
    SET @tablePerDB = 256;
    SET @centerNum = @userId % (@tablePerDB * @dbCount);
    SET @dbNum = convert(@centerNum / @tablePerDB,signed) ;
    SET @tableNum = (@centerNum % @tablePerDB);
    SELECT @dbNum,@tableNum;

    2.算法(同尾号均匀分配同库):
    2.1)分库分表规则
    中间变量=USER_ID%(分库数量*每个库的表数量)
    库=中间变量%分库数量
    表=中间变量/分库数量

    3.2)查看用户所在的库、表
    SET @userId = 65;
    SET @dbCount = 8;
    SET @tablePerDB = 256;
    SET @centerNum = @userId % (@tablePerDB * @dbCount);
    SET @dbNum = (@centerNum % @dbCount);
    SET @tableNum = convert(@centerNum / @dbCount,signed) ;
    SELECT @dbNum,@tableNum;
    注意:该方式dbCount=10时,能根据userId尾号,能肉眼区分在那个库。


    二.相关存储过程

    1.分库分表:根据指定业务列创建,id,inserttime,updatetime,isactive字段为规范字段,库与表序号从0开始.
    1.1)创建分库分表存储过程
    use mysql;
    drop procedure if EXISTS split_db_split_table1;
    create procedure split_db_split_table1(in logicDb varchar(100),in logicTable varchar(100),in dbCount int(11),in tableCountPerDb int(11),in tableFieldSql text)
    begin
    DECLARE ddlSql text;
    DECLARE dbName varchar(100);
    DECLARE tableName varchar(100);
    DECLARE dbStartSeq int(11);
    DECLARE dbEndSeq int(11);
    DECLARE tableStartSeq int(11);
    DECLARE tableEndSeq int(11);

     #设置库开始序号
    set dbStartSeq = 0;
     #设置库结束序号
    set dbEndSeq = dbCount-1;

     #循环建库,dbCount=1表示不分库
    while dbStartSeq <= dbEndSeq do
     #检查数据库是否存在,不存在创建,dbCount=1表示不分库
     IF dbCount=1 THEN 
      set dbName = logicDb;
     ELSE
      set dbName = CONCAT(logicDb,"_",LPAD(dbStartSeq,2,'0'));
     END IF;

     set ddlSql = CONCAT(" CREATE DATABASE IF NOT EXISTS ",dbName);
     set @dynamicSql = ddlSql;
     prepare stmt from @dynamicSql;
     execute stmt;     


     #设置表开始序号
     set tableStartSeq = 0;
     #设置表结束序号
     set tableEndSeq = tableCountPerDb-1;
     #循环创建表
     while tableStartSeq <= tableEndSeq do
       set tableName = CONCAT(logicTable,"_",LPAD(tableStartSeq,4,'0'));
      set tableName = CONCAT(" ",dbName,".",tableName);
       # 删除表
       set ddlSql = CONCAT(" DROP TABLE IF EXISTS ",tableName);
       set @dynamicSql = ddlSql;
       prepare stmt from @dynamicSql;
       execute stmt;       

       # 创建表    
       SET ddlSql = CONCAT(" CREATE TABLE IF NOT EXISTS ",tableName," (
                   id bigint(20) AUTO_INCREMENT PRIMARY KEY NOT NULL comment '唯一Id',
                   user_id bigint(20) NOT NULL COMMENT '用户ID' ,"
                   ,IFNULL(tableFieldSql,""),
                   "
                   inserttime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '插入时间',
                   updatetime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
                   isactive tinyint(1) NOT NULL DEFAULT '1' COMMENT '是否有效'
              ); "
         );
       set @dynamicSql = ddlSql;
       prepare stmt from @dynamicSql;
       execute stmt;              
          
       # 创建idx_user_id索引  
       set ddlSql = CONCAT(" create index idx_user_id on ",tableName," (user_id); ");
       set @dynamicSql = ddlSql;
       prepare stmt from @dynamicSql;
       execute stmt;

       # 创建idx_inserttime索引  
       set ddlSql = CONCAT(" create index idx_inserttime on ",tableName," (inserttime); ");
       set @dynamicSql = ddlSql;
       prepare stmt from @dynamicSql;
       execute stmt;

       # 创建idx_updatetime索引  
       set ddlSql = CONCAT(" create index idx_updatetime on ",tableName," (updatetime); ");
      set @dynamicSql = ddlSql;
       prepare stmt from @dynamicSql;
       execute stmt;

       set tableStartSeq = tableStartSeq+1;
     end while;
     set dbStartSeq = dbStartSeq+1;
    end while;
    end;

    1.2)调用分库分表储存过程
    use sys;
    SET @tableFieldSql = "
    age int(4) NOT NULL COMMENT '年龄' ,
    ";
    call split_db_split_table1('pms_manager','tb_user',8,256,@tableFieldSql);


    2.分库分表:根据表全部字段语句创建,注意:库与表序号从0开始
    2.1)创建分库分表存储过程
    use mysql;
    drop procedure if EXISTS split_db_split_table2;
    create procedure split_db_split_table2(in logicDb varchar(100),in logicTable varchar(100),in dbCount int(11),in tableCountPerDb int(11),in tableFieldSql text)
    begin
    DECLARE ddlSql text;
    DECLARE dbName varchar(100);
    DECLARE tableName varchar(100);
    DECLARE dbStartSeq int(11);
    DECLARE dbEndSeq int(11);
    DECLARE tableStartSeq int(11);
    DECLARE tableEndSeq int(11);

     #设置库开始序号
    set dbStartSeq = 0;
     #设置库结束序号
    set dbEndSeq = dbCount-1;

     #循环建库,dbCount=1表示不分库
    while dbStartSeq <= dbEndSeq do
     #检查数据库是否存在,不存在创建,dbCount=1表示不分库
     IF dbCount=1 THEN 
      set dbName = logicDb;
     ELSE
      set dbName = CONCAT(logicDb,"_",LPAD(dbStartSeq,2,'0'));
     END IF;

     set ddlSql = CONCAT(" CREATE DATABASE IF NOT EXISTS ",dbName);
     set @dynamicSql = ddlSql;
     prepare stmt from @dynamicSql;
     execute stmt;     


     #设置表开始序号
     set tableStartSeq = 0;
     #设置表结束序号
     set tableEndSeq = tableCountPerDb-1;
     #循环创建表
     while tableStartSeq <= tableEndSeq do
       set tableName = CONCAT(logicTable,"_",LPAD(tableStartSeq,4,'0'));
      set tableName = CONCAT(" ",dbName,".",tableName);
       # 删除表
       set ddlSql = CONCAT(" DROP TABLE IF EXISTS ",tableName);
       set @dynamicSql = ddlSql;
       prepare stmt from @dynamicSql;
       execute stmt;       

       # 创建表    
       SET ddlSql = CONCAT(" CREATE TABLE IF NOT EXISTS ",tableName,IFNULL(tableFieldSql,""));
       set @dynamicSql = ddlSql;
       prepare stmt from @dynamicSql;
       execute stmt;     
      
       set tableStartSeq = tableStartSeq+1;
     end while;
     set dbStartSeq = dbStartSeq+1;
    end while;
    end;

    2.2)调用分库分表储存过程
    use sys;
    SET @tableFieldSql = "

    (
    id bigint(20) AUTO_INCREMENT PRIMARY KEY NOT NULL comment '唯一Id',
    user_id bigint(20) NOT NULL COMMENT '用户ID' ,
    inserttime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '插入时间',
    updatetime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    isactive tinyint(1) NOT NULL DEFAULT '1' COMMENT '是否有效'
    )
    ENGINE=InnoDB
    DEFAULT CHARACTER SET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    COMMENT='用户表'
    AUTO_INCREMENT=1
    ROW_FORMAT=DYNAMIC;

    ";
    call split_db_split_table2('pms_manager','tb_user',8,256,@tableFieldSql);


    3.删除分库分表:根据参数删库删表,库与表序号从0开始
    3.1)创建分库分表存储过程
    use mysql;
    drop procedure if EXISTS destory_db_split_table;
    create procedure destory_db_split_table(in logicDb varchar(100),in logicTable varchar(100),in dbCount int(11),in tableCountPerDb int(11),in isDeleteDb tinyint(1))
    begin
    DECLARE ddlSql text;
    DECLARE dbName varchar(100);
    DECLARE tableName varchar(100);
    DECLARE dbStartSeq int(11);
    DECLARE dbEndSeq int(11);
    DECLARE tableStartSeq int(11);
    DECLARE tableEndSeq int(11);

     #设置库开始序号
    set dbStartSeq = 0;
     #设置库结束序号
    set dbEndSeq = dbCount-1;

     #循环建库,dbCount=1表示不分库
    while dbStartSeq <= dbEndSeq do
     #检查数据库是否存在,不存在创建,dbCount=1表示不分库
     IF dbCount=1 THEN 
      set dbName = logicDb;
     ELSE
      set dbName = CONCAT(logicDb,"_",LPAD(dbStartSeq,2,'0'));
     END IF;

     #设置表开始序号
     set tableStartSeq = 0;
     #设置表结束序号
     set tableEndSeq = tableCountPerDb-1;
     #循环创建表
     while tableStartSeq <= tableEndSeq do
       set tableName = CONCAT(logicTable,"_",LPAD(tableStartSeq,4,'0'));
      set tableName = CONCAT(" ",dbName,".",tableName);
       # 删除表
       set ddlSql = CONCAT(" DROP TABLE IF EXISTS ",tableName," ; ");
       set @dynamicSql = ddlSql;
       prepare stmt from @dynamicSql;
       execute stmt;       
      
       set tableStartSeq = tableStartSeq+1;
     end while;

     If isDeleteDb THEN
      set ddlSql = CONCAT(" DROP DATABASE IF EXISTS ",dbName," ; ");
      set @dynamicSql = ddlSql;
      prepare stmt from @dynamicSql;
      execute stmt;     
     END IF;
     set dbStartSeq = dbStartSeq+1;
    end while;
    end;

    3.2)调用删库删表储存过程
    use sys;
    call destory_db_split_table('pms_manager','tb_user',8,256,false);


    三.用户id批量排序,高命中率分库分表算法
    /**
         * 根据用户Id取2位进行反转再排序,已提升同库分表命中概率(兼容按尾号2位、尾号1位分表)
         * @param userList
         */
        private void sortByUserIdEndHitRate(List<UserInfo> userList){
            Collections.sort(userList, new Comparator<UserInfo>() {
                @Override
                public int compare(UserInfo o1, UserInfo o2) {
                    Long end1 = Long.valueOf(reverse(o1.getUserId()));
                    Long end2 = Long.valueOf(reverse(o2.getUserId()));
                    return end1.compareTo(end2);
                }
            });
        }

        /**
         * 根据用户Id字符串反转
         * @param userId
         * @return
         */
        private String reverse(String userId) {
            char[] s = userId.trim().toCharArray();
            char[] ns = new char[2];
            int n = s.length - 1;
            if(s.length == 0){
                ns[0] = '0';
                ns[1] = '0';
            }else if(s.length == 1){
                ns[0] = s[n];
                ns[1] = '0';
            }else {
                ns[0] = s[n];
                ns[1] = s[n - 1];
            }
            return new String(ns);
        }

    展开全文
  • 这篇博客通过ShardingSphere实现分库分表 一、项目概述 1、技术架构 项目总体技术选型 SpringBoot2.0.6 + shardingsphere4.0.0-RC1 + Maven3.5.4 + MySQL + lombok(插件) 2、项目说明 场景:在实际开发中,如果表的...

    这篇博客通过ShardingSphere实现分库分表

    一、项目概述

    1、技术架构

    项目总体技术选型

    SpringBoot2.0.6 + shardingsphere4.0.0-RC1 + Maven3.5.4 + MySQL + lombok(插件)

    2、项目说明

    场景:在实际开发中,如果表的数据过大我们需要把一张表拆分成多张表,也可以垂直切分把一个库拆分成多个库,这里就是通过ShardingSphere实现分库分表功能。

    3、数据库设计

    分库 ds一个库分为 ds0库 和 ds1库。

    分表 tab_user一张表分为tab_user0表 和 tab_user1表。

    如图

    ds0库

    在这里插入图片描述
    ds1库
    在这里插入图片描述

    二、核心代码

    1、application.properties

    server.port=8088
    
    #========这里换种方式,采用java配置实现分库分表==================
    
    #指定mybatis信息
    mybatis.config-location=classpath:mybatis-config.xml
    #打印sql
    spring.shardingsphere.props.sql.show=true
    
    spring.shardingsphere.datasource.names=ds0,ds1
    
    spring.shardingsphere.datasource.ds0.type=com.alibaba.druid.pool.DruidDataSource
    spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.jdbc.Driver
    spring.shardingsphere.datasource.ds0.url=jdbc:mysql://localhost:3306/ds0?characterEncoding=utf-8
    spring.shardingsphere.datasource.ds0.username=root
    spring.shardingsphere.datasource.ds0.password=root
    
    spring.shardingsphere.datasource.ds1.type=com.alibaba.druid.pool.DruidDataSource
    spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.jdbc.Driver
    spring.shardingsphere.datasource.ds1.url=jdbc:mysql://localhost:3306/ds1?characterEncoding=utf-8
    spring.shardingsphere.datasource.ds1.username=root
    spring.shardingsphere.datasource.ds1.password=root
    
    #根据年龄分库
    spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=age
    spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=ds$->{age % 2}
    #根据id分表
    spring.shardingsphere.sharding.tables.tab_user.actual-data-nodes=ds$->{0..1}.tab_user$->{0..1}
    spring.shardingsphere.sharding.tables.tab_user.table-strategy.inline.sharding-column=id
    spring.shardingsphere.sharding.tables.tab_user.table-strategy.inline.algorithm-expression=tab_user$->{id % 2}
    

    Sharding-JDBC可以通过Java,YAML,Spring命名空间和Spring Boot Starter四种方式配置

    2、UserController

    package com.oujiong.controller;
    
    
    import com.google.common.collect.Lists;
    import com.oujiong.entity.User;
    import com.oujiong.service.UserService;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.web.bind.annotation.GetMapping;
    import org.springframework.web.bind.annotation.PostMapping;
    import org.springframework.web.bind.annotation.RestController;
    
    import javax.annotation.PostConstruct;
    import java.util.List;
    
    
    /**
     * @Description: 接口测试
     *
     * @author xub
     * @date 2019/8/24 下午6:31
     */
    @RestController
    public class UserController {
    
        @Autowired
        private UserService userService;
    
        /**
         * 模拟插入数据
         */
        List<User> userList = Lists.newArrayList();
        /**
         * 初始化插入数据
         */
        @PostConstruct
        private void getData() {
            userList.add(new User(1L,"小小", "女", 3));
            userList.add(new User(2L,"爸爸", "男", 30));
            userList.add(new User(3L,"妈妈", "女", 28));
            userList.add(new User(4L,"爷爷", "男", 64));
            userList.add(new User(5L,"奶奶", "女", 62));
        }
        /**
         * @Description: 批量保存用户
         */
        @PostMapping("save-user")
        public Object saveUser() {
            return userService.insertForeach(userList);
        }
        /**
         * @Description: 获取用户列表
         */
        @GetMapping("list-user")
        public Object listUser() {
            return userService.list();
        }
    
    
    }
    
    

    三、测试验证

    1、批量插入数据

    localhost:8084/save-user

    我们可以从商品接口代码中可以看出,它会批量插入5条数据。我们先看控制台输出SQL语句

    在这里插入图片描述
    我们可以从SQL语句可以看出 ds0 和 ds1 库中都插入了数据。

    我们再来看数据库

    ds0.tab_user0
    在这里插入图片描述
    ds0.tab_user1
    在这里插入图片描述
    ds1.tab_user0
    在这里插入图片描述
    ds1.tab_user1
    在这里插入图片描述
    完成分库分表插入数据。

    2、获取数据

    请求接口结果

    在这里插入图片描述
    我们可以看出虽然已经分库分表,但依然可以将多表数据聚合在一起并可以支持按age排序。

    展开全文
  • 【分库、分表】MySQL分库分表方案

    千次阅读 2019-04-19 22:59:11
    一、Mysql分库分表方案 1.为什么要分表: 当一张表的数据达到几千万时,你查询一次所花的时间会变多,如果有联合查询的话,我想有可能会死在那儿了。分表的目的就在于此,减小数据库的负担,缩短查询时间。 mysql...

    一、Mysql分库分表方案

    1.为什么要分表:

    当一张表的数据达到几千万时,你查询一次所花的时间会变多,如果有联合查询的话,我想有可能会死在那儿了。分表的目的就在于此,减小数据库的负担,缩短查询时间。

    mysql中有一种机制是表锁定和行锁定,是为了保证数据的完整性。表锁定表示你们都不能对这张表进行操作,必须等我对表操作完才行。行锁定也一样,别的sql必须等我对这条数据操作完了,才能对这条数据进行操作。

    2. mysql proxy:amoeba

    做mysql集群,利用amoeba。

    从上层的java程序来讲,不需要知道主服务器和从服务器的来源,即主从数据库服务器对于上层来讲是透明的。可以通过amoeba来配置。

    3.大数据量并且访问频繁的表,将其分为若干个表

    比如对于某网站平台的数据库表-公司表,数据量很大,这种能预估出来的大数据量表,我们就事先分出个N个表,这个N是多少,根据实际情况而定。

         某网站现在的数据量至多是5000万条,可以设计每张表容纳的数据量是500万条,也就是拆分成10张表,

    那么如何判断某张表的数据是否容量已满呢?可以在程序段对于要新增数据的表,在插入前先做统计表记录数量的操作,当<500万条数据,就直接插入,当已经到达阀值,可以在程序段新创建数据库表(或者已经事先创建好),再执行插入操作。

    4. 利用merge存储引擎来实现分表

    如果要把已有的大数据量表分开比较痛苦,最痛苦的事就是改代码,因为程序里面的sql语句已经写好了。用merge存储引擎来实现分表, 这种方法比较适合.

    举例子:

     


    二、数据库架构

    1、简单的MySQL主从复制:

    MySQL的主从复制解决了数据库的读写分离,并很好的提升了读的性能,其图如下:

     

    其主从复制的过程如下图所示:

     

    但是,主从复制也带来其他一系列性能瓶颈问题:

    1. 写入无法扩展
    2. 写入无法缓存
    3. 复制延时
    4. 锁表率上升
    5. 表变大,缓存率下降

    那问题产生总得解决的,这就产生下面的优化方案,一起来看看。

    2、MySQL垂直分区

       如果把业务切割得足够独立,那把不同业务的数据放到不同的数据库服务器将是一个不错的方案,而且万一其中一个业务崩溃了也不会影响其他业务的正常进行,并且也起到了负载分流的作用,大大提升了数据库的吞吐能力。经过垂直分区后的数据库架构图如下:

     

    然而,尽管业务之间已经足够独立了,但是有些业务之间或多或少总会有点联系,如用户,基本上都会和每个业务相关联,况且这种分区方式,也不能解决单张表数据量暴涨的问题,因此为何不试试水平分割呢?

    3、MySQL水平分片(Sharding)

    这是一个非常好的思路,将用户按一定规则(按id哈希)分组,并把该组用户的数据存储到一个数据库分片中,即一个sharding,这样随着用户数量的增加,只要简单地配置一台服务器即可,原理图如下:

     

    如何来确定某个用户所在的shard呢,可以建一张用户和shard对应的数据表,每次请求先从这张表找用户的shard id,再从对应shard中查询相关数据,如下图所示:

     

    ①单库单表 

    单库单表是最常见的数据库设计,例如,有一张用户(user)表放在数据库db中,所有的用户都可以在db库中的user表中查到。 


    ②单库多表 

    随着用户数量的增加,user表的数据量会越来越大,当数据量达到一定程度的时候对user表的查询会渐渐的变慢,从而影响整个DB的性能。如果使用mysql, 还有一个更严重的问题是,当需要添加一列的时候,mysql会锁表,期间所有的读写操作只能等待。 

    可以通过某种方式将user进行水平的切分,产生两个表结构完全一样的user_0000,user_0001等表,user_0000 + user_0001 + …的数据刚好是一份完整的数据。 

    ③多库多表

    随着数据量增加也许单台DB的存储空间不够,随着查询量的增加单台数据库服务器已经没办法支撑。这个时候可以再对数据库进行水平区分。 


     

    四、分库分表规则 

    设计表的时候需要确定此表按照什么样的规则进行分库分表。例如,当有新用户时,程序得确定将此用户信息添加到哪个表中;同理,当登录的时候我们得通过用户的账号找到数据库中对应的记录,所有的这些都需要按照某一规则进行。 
    路由 

    通过分库分表规则查找到对应的表和库的过程。如分库分表的规则是user_id mod 4的方式,当用户新注册了一个账号,账号id的123,我们可以通过id mod 4的方式确定此账号应该保存到User_0003表中。当用户123登录的时候,我们通过123 mod 4后确定记录在User_0003中。 
    分库分表产生的问题,及注意事项 

    1.   分库分表维度的问题 

    假如用户购买了商品,需要将交易记录保存取来,如果按照用户的纬度分表,则每个用户的交易记录都保存在同一表中,所以很快很方便的查找到某用户的 购买情况,但是某商品被购买的情况则很有可能分布在多张表中,查找起来比较麻烦。反之,按照商品维度分表,可以很方便的查找到此商品的购买情况,但要查找 到买人的交易记录比较麻烦。 

    所以常见的解决方式有: 

    • 通过扫表的方式解决,此方法基本不可能,效率太低了。
    • 记录两份数据,一份按照用户纬度分表,一份按照商品维度分表。
    • 通过搜索引擎解决,但如果实时性要求很高,又得关系到实时搜索。 

    2.   联合查询的问题 

    联合查询基本不可能,因为关联的表有可能不在同一数据库中。 

    3.   避免跨库事务

    避免在一个事务中修改db0中的表的时候同时修改db1中的表,一个是操作起来更复杂,效率也会有一定影响。 

    4.   尽量把同一组数据放到同一DB服务器上

    例如将卖家a的商品和交易信息都放到db0中,当db1挂了的时候,卖家a相关的东西可以正常使用。也就是说避免数据库中的数据依赖另一数据库中的数据。 

    5.一主多备

    在实际的应用中,绝大部分情况都是读远大于写。Mysql提供了读写分离的机制,所有的写操作都必须对应到Master,读操作可以在 Master和Slave机器上进行,Slave与Master的结构完全一样,一个Master可以有多个Slave,甚至Slave下还可以挂 Slave,通过此方式可以有效的提高DB集群的 QPS.                                                       

    所有的写操作都是先在Master上操作,然后同步更新到Slave上,所以从Master同步到Slave机器有一定的延迟,当系统很繁忙的时候,延迟问题会更加严重,Slave机器数量的增加也会使这个问题更加严重。 
    此外,可以看出Master是集群的瓶颈,当写操作过多,会严重影响到Master的稳定性,如果Master挂掉,整个集群都将不能正常工作。 
    所以

    • 当读压力很大的时候,可以考虑添加Slave机器的分式解决,但是当Slave机器达到一定的数量就得考虑分库了。
    • 当写压力很大的时候,就必须得进行分库操作。 

     


    五、MySQL使用为什么要分库分表

    可以用说用到MySQL的地方,只要数据量一大, 马上就会遇到一个问题,要分库分表. 这里引用一个问题为什么要分库分表呢?MySQL处理不了大的表吗? 其实是可以处理的大表的.我所经历的项目中单表物理上文件大小在80G多,单表记录数在5亿以上,而且这个表 属于一个非常核用的表:朋友关系表. 

    但这种方式可以说不是一个最佳方式. 因为面临文件系统如Ext3文件系统对大于大文件处理上也有许多问题. 这个层面可以用xfs文件系统进行替换.但MySQL单表太大后有一个问题是不好解决: 表结构调整相关的操作基 
    本不在可能.所以大项在使用中都会面监着分库分表的应用. 

    从Innodb本身来讲数据文件的Btree上只有两个锁, 叶子节点锁和子节点锁,可以想而知道,当发生页拆分或是添加新叶时都会造成表里不能写入数据.所以分库分表还就是一个比较好的选择了. 

    那么分库分表多少合适呢? 
    经测试在单表1000万条记录一下,写入读取性能是比较好的. 这样在留点buffer,那么单表全是数据字型的保持在800万条记录以下, 有字符型的单表保持在500万以下. 

    如果按 100库100表来规划,如用户业务: 500万*100*100 = 50000000万 = 5000亿记录. 

    心里有一个数了,按业务做规划还是比较容易的.

    分布式数据库架构--排序、分页、分组、实现


     

    六、最近研究分布式数据库架构,发现排序、分组及分页让着实人有点头疼。现把问题及解决思路整理如下。

    1.多分片(水平切分)返回结果合并(排序)

    ①Select + None Aggregate Function的有序记录合并排序 

    解决思路:对各分片返回的有序记录,进行排序去重合并。此处主要是编写排序去重合并算法。

    ②Select + None Aggregate Function的无序记录合并

    解决思路:对各分片返回的无序记录,进行去重合并。

    • 优点:实现比较简单。
    •  缺点:数据量越大,字段越多,去重处理就会越耗时。

    ③Select + Aggregate Function的记录合并(排序)Oracle常用聚合函数:Count、Max、Min、Avg、Sum。

    • AF:Max、Min
      • 思路:通过算法对各分片返回结果再求max、min值。
    • AF:Avg、Sum、Count
      • 思路:分片间无重复记录或字段时,通过算法对各分片返回结果再求avg、sum、count值。分片间有重复记录或字段时,先对各分片记录去重合并,再通过算法求avg、sum、count值。

    比如:

    select count(*) from user
    
    select count(deptno) from user;
    
    select count(distinct deptno) from user;

    2.多分片(水平切分)返回结果分页

    解决思路:合并各分片返回结果,逻辑分页。

            优点:  实现简单。

            缺点:  数据量越大,缓存压力就越大。

                         分片数据量越大,查询也会越慢。

    3.多分片(水平切分)查询有分组语法的合并

    ①Group By Having + None Aggregate Function时

    • Select + None Aggregate Function
      • 比如:select job user group by job;
      • 思路:直接去重(排序)合并。
    • Select + Aggregate Function
      • 比如:select max(sal),job user group by job;
      • 思路:同Select + Aggregate Function的记录合并(排序)。

    ②Group By Having + Aggregate Function时

    解决思路:去掉having AF条件查询各分片,然后把数据放到一张表里。再用group by having 聚合函数查询。

    4.分布式数据库架构--排序分组分页参考解决方案

    • 解决方案1:Hadoop + Hive。
      • 思路:使用Hadoop HDFS来存储数据,通过Hdoop MapReduce完成数据计算,通过Hive HQL语言使用部分与RDBBS一样的表格查询特性和分布式存储计算特性。
      • 优点:
      1. 可以解决问题
      2. 具有并发处理能力
      3. 可以离线处理
      • 缺点: 
      1. 实时性不能保证
      2. 网络延迟会增加
      3. 异常捕获难度增加
      4. Web应用起来比较复杂
    • 解决方案2:总库集中查询。
      • 优点:
      1. 可以解决问题
      2. 实现简单
      • 缺点:
      1. 总库数据不能太大
      2. 并发压力大

    5.小结

    对 于分布式数据库架构来说,排序、分页、分组一直就是一个比较复杂的问题。避免此问题需要好好地设计分库、分表策略。同时根据特定的场景来解决问题。也可以 充分利用海量数据存储(Hadoop-HDFS|Hive|HBse)、搜索引擎(Lucene|Solr)及分布式计算(MapReduce)等技术来 解决问题。
    别外,也可以用NoSQL技术替代关系性数据库来解决问题,比如MogonDB\redis。

    即使爬到最高的山上,一次也只能脚踏实地地迈一步。

     

    转自:https://www.cnblogs.com/sunny3096/p/8595058.html

    展开全文
  • 分库分表适用场景分库分表用于应对当前互联网常见的两个场景——大数据量和高并发。通常分为垂直拆分和水平拆分两种。垂直拆分是根据业务将一个库(表)拆分为多个库(表)。如:将经常和不常访问的字段拆分至不同的...
  • 需要安装如下包 EFCore.Sharding public void ConfigureServices(IServiceCollection services) { services.UseEFCoreSharding(config => { //获取配置文件的连接字符串 string conName = Configuration[...
  • MySQL分库分表技术

    2013-12-11 19:52:40
    这个PPT由浅入深,从很少的用户到千万级别的用户,告诉你为什么要使用分库分表,包括垂直和水平切分,偏入门的理论,代码基本无
  • MySQL分库分表方案

    千次阅读 2019-01-26 23:29:16
    一、Mysql分库分表方案 1.为什么要分表: 2. mysql proxy:amoeba 3.大数据量并且访问频繁的表,将其分为若干个表 4. 利用merge存储引擎来实现分表 二、数据库架构 1、简单的MySQL主从复制: 2、MySQL垂直...
  • 本文着重介绍sharding切分策略,如果你对数据库sharding缺少基本的了解,请参考我另一篇从基础理论全面介绍sharding的文章:数据库Sharding的基本思想和切分策略 第一部分:...数据库分库分表(sharding)实施策略图解
  • 精品专栏死磕 Java 并发死磕 Sharding-jdbc死磕 Spring 之 IOC再次抛出笔者的观点,在能满足业务场景的情况下,单表>分区>单库分表...
  • 参考博主的另一篇文章进行《基于MySQL Yum存储在Linux-7.2上安装MySQL-5.7.21数据库服务》 2、下载Linux MyCat CSDN 下载:【Linux MyCat+文档下载】 官网下载:【MyCat官网下载】 ...
  • 由于业务需要,需要对Mysql数据库进行分库分表,故而最近一直在整理分库分表的相关知识,现手上的工作也告一段落了,抽空将自己最近的学习结果转化为博文,分享给大家,本博文打算做成一个系列的,首先是分库分表的...
  • MySQL分库分表相关面试知识点

    万次阅读 2020-04-23 09:46:05
    基础概念 分表 能够解决单表数据量过大带来的查询效率下降的...分库分表技术优化了数据存储方式,有效减小数据库服务器的负担、缩短查询响应时间。 数据分库分表存储场景条件 关系型数据库 主从架构(mast...
  • mysql分库分表,springboot程序集成mycat实现分库分表 具体代码访问链接下载 从项目到部署服务器的过程,毕竟些写项目是为了发布部署服务器。 本篇主要介绍**mysql分库分表,springboot程序集成mycat实现分库分表** ...
  • 数据量达到一定程度的话可能就会进行分库分表,所以最近研究了一个分库分表的插件ShardingJDBC,因为myCat或者其他的开源插件都已经不维护了或者停止更新,目前ShardingJDBC比较活跃,而且不用进行单独部署工作,这...
  • Mysql分库分表

    千次阅读 2020-02-01 17:32:32
    Mysql分库分表垂直分表水平分表垂直或水平分库 对于大型互联网应用的数据库架构上, 首先做好主从分离提升读写性能, 还有通常查询操作远多于增删改, 因此从库充分的做好水平扩展来进一步提升读性能. 然后按单库或单...
  • MySQL分库分表

    千次阅读 2020-07-14 13:31:46
    MySQL分库分表 互联网系统需要处理大量用户的请求。比如微信日活用户破10亿,海量的用户每天产生海量的数量;美团外卖,每天都是几千万的订单,那这些系统的用户表、订单表、交易流水表等是如何处理呢? 数据量只增...
  • 浅谈分库分表

    千次阅读 2017-12-24 16:46:10
    本文将为什么要分库分表、怎么分以及分库分表后带来的问题,来谈一谈分库分表的相关知识。
  • MySQL-分库分表初探

    2020-02-03 19:57:56
    文章目录官方文档主从复制 解决不了 主节点DB写的的压力常见的分库分表的方式分库分表DB分片前的准备如何选择分区键 ,尽量避免跨分片查询如何存储无需分片的表如何在节点上部署分片 官方文档 ...
  • 今天小编就为大家分享一篇关于MySQL分库分表总结讲解,小编觉得内容挺不错的,现在分享给大家,具有很好的参考价值,需要的朋友一起跟随小编来看看吧
  • MySQL 分库分表

    2020-01-07 17:17:26
    MySQL 分库分表: https://cloud.tencent.com/developer/article/1539420 众所周知,数据库很容易成为应用系统的瓶颈。单机数据库的资源和处理能力有限,在高并发的分布式系统中,可采用分库分表突破单机局限。...
  • mysql分库分表

    2017-12-08 18:07:07
    分库分表中维度的选取是极为重要一个步骤,这个主要与所设计分表业务密切相关的。 数据量是基于目前数据的业务属性在未来的时间内递量,依次来评估数据所占的是硬盘大小。举个例子:股票期货交易的ticker数据,每天...
  • 本课程主要是以实战为主,全程使用YAML配置讲解Sharing-JDBC分表分库分库分表分表分库、读写分离(结合分表分库分库分表分表分库)、强制路由、数据脱敏、自定义分片规则。MySQL主从复制搭建,使用...
  • mysql 分库分表

    2019-01-18 15:42:21
    mysql 分库分表 分表是分散数据库压力的好方法。 分表,最直白的意思,就是将一个表结构分为多个表,然后,可以再同一个库里,也可以放到不同的库。 当然,首先要知道什么情况下,才需要分表。个人觉得单表记录条数...

空空如也

1 2 3 4 5 ... 20
收藏数 14,254
精华内容 5,701
关键字:

分库分表