网曝吃瓜黑料一区,色一情一乱一伦一区二区女同子,日欧137片内射在线视频播放

一、目標

本文將完成如下目標:

分表數(shù)量: 256 分庫數(shù)量: 4
以用戶ID(user_id) 為數(shù)據(jù)庫分片Key
最后測試訂單創(chuàng)建，更新，刪除, 單訂單號查詢，根據(jù)user_id查詢列表操作。

架構(gòu)圖:

表結(jié)構(gòu)如下:

CREATE TABLE `order_XXX` (
  `order_id` bigint(20) unsigned NOT NULL,
  `user_id` int(11) DEFAULT '0' COMMENT '訂單id',
  `status` int(11) DEFAULT '0' COMMENT '訂單狀態(tài)',
  `booking_date` datetime DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`order_id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_bdate` (`booking_date`),
  KEY `idx_ctime` (`create_time`),
  KEY `idx_utime` (`update_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

注: 000<= XXX <= 255, 本文重點在于分庫分表實踐, 只保留具有代表性字段，其它場景可以在此基礎(chǔ)上做改進。

全局唯一ID設(shè)計

要求:1.全局唯一 2:粗略有序 3:可反解出庫編號

1bit + 39bit時間差 + 8bit機器號 + 8bit用戶編號(庫號) + 8bit自增序列

訂單號組成項	保留字段	毫秒級時間差	機器數(shù)	用戶編號(表編號)	自增序列
所占字節(jié)(單位bit)	1	39	8	8	8

單機最大QPS: 256000 使用壽命: 17年

二、環(huán)境準備

1、基本信息

項	版本	備注
SpringBoot	2.1.10.RELEASE
Mango	1.6.16	wiki地址:https://github.com/jfaster/mango
HikariCP	3.2.0
Mysql	5.7	測試使用docker一鍵搭建

2、數(shù)據(jù)庫環(huán)境準備

進入mysql:

#主庫
 mysql -h 172.30.1.21 -uroot -pbytearch
#從庫
 mysql -h 172.30.1.31 -uroot -pbytearch

進入容器

#主
docker exec -it db_1_master /bin/bash
#從
docker exec -it db_1_slave /bin/bash

查看運行狀態(tài)

#主
docker exec db_1_master sh -c 'mysql -u root -pbytearch -e "SHOW MASTER STATUS \G"'
#從
docker exec db_1_slave sh -c 'mysql -u root -pbytearch -e "SHOW SLAVE STATUS \G"'

3、建庫 & 導(dǎo)入分表

（1）在mysql master實例分別建庫

172.30.1.21( o rder_db_ 1) , 172.30.1.22( order_db_2) ,

172.30.1.23( ord er_db_3) , 172.30.1.24( order_db_4 )

（2）依次導(dǎo)入建表SQL 命令為

mysql -uroot -pbytearch -h172.30.1.21 order_db_1<fast-cloud-mysql-sharding/doc/sql/order_db_1.sql;
mysql -uroot -pbytearch -h172.30.1.22 order_db_2<fast-cloud-mysql-sharding/doc/sql/order_db_2.sql;
mysql -uroot -pbytearch -h172.30.1.23 order_db_3<fast-cloud-mysql-sharding/doc/sql/order_db_3.sql;
mysql -uroot -pbytearch -h172.30.1.24 order_db_4<fast-cloud-mysql-sharding/doc/sql/order_db_4.sql;

三、配置&實踐

1、pom文件

     <!-- mango 分庫分表中間件 --> 
            <dependency>
                <groupId>org.jfaster</groupId>
                <artifactId>mango-spring-boot-starter</artifactId>
                <version>2.0.1</version>
            </dependency>
         
             <!-- 分布式ID生成器 -->
            <dependency>
                <groupId>com.bytearch</groupId>
                <artifactId>fast-cloud-id-generator</artifactId>
                <version>${version}</version>
            </dependency>
            <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <version>6.0.6</version>
            </dependency>

2、常量配置

package com.bytearch.fast.cloud.mysql.sharding.common;
/**
 * 分庫分表策略常用常量
 */
public class ShardingStrategyConstant {
    /**
     * database 邏輯名稱 ，真實庫名為 order_db_XXX
     */
    public static final String LOGIC_ORDER_DATABASE_NAME = "order_db";
    /**
     * 分表數(shù) 256，一旦確定不可更改
     */
    public static final int SHARDING_TABLE_NUM = 256;
    /**
     * 分庫數(shù), 不建議更改， 可以更改，但是需要DBA遷移數(shù)據(jù)
     */
    public static final int SHARDING_DATABASE_NODE_NUM = 4;
}

3、yml 配置

4主4從數(shù)據(jù)庫配置，這里僅測試默認使用root用戶密碼，生產(chǎn)環(huán)境不建議使用root用戶。

mango:
  scan-package: com.bytearch.fast.cloud.mysql.sharding.dao
  datasources:
    - name: order_db_1
      master:
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://172.30.1.21:3306/order_db_1?useUnicode=true&characterEncoding=utf8&autoReconnect=true&rewriteBatchedState&connectTimeout=1000&socketTimeout=5000&useSSL=false
        user-name: root
        password: bytearch
        maximum-pool-size: 10
        connection-timeout: 3000
      slaves:
        - driver-class-name: com.mysql.cj.jdbc.Driver
          jdbc-url: jdbc:mysql://172.30.1.31:3306/order_db_1?useUnicode=true&characterEncoding=utf8&autoReconnect=true&rewriteBatchedState&connectTimeout=1000&socketTimeout=5000&useSSL=false
          user-name: root
          password: bytearch
          maximum-pool-size: 10
          connection-timeout: 3000
    - name: order_db_2
      master:
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://172.30.1.22:3306/order_db_2?useUnicode=true&characterEncoding=utf8&autoReconnect=true&rewriteBatchedState&connectTimeout=1000&socketTimeout=5000&useSSL=false
        user-name: root
        password: bytearch
        maximum-pool-size: 10
        connection-timeout: 3000
      slaves:
        - driver-class-name: com.mysql.cj.jdbc.Driver
          jdbc-url: jdbc:mysql://172.30.1.32:3306/order_db_2?useUnicode=true&characterEncoding=utf8&autoReconnect=true&rewriteBatchedState&connectTimeout=1000&socketTimeout=5000&useSSL=false
          user-name: root
          password: bytearch
          maximum-pool-size: 10
          connection-timeout: 3000
    - name: order_db_3
      master:
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://172.30.1.23:3306/order_db_3?useUnicode=true&characterEncoding=utf8&autoReconnect=true&rewriteBatchedState&connectTimeout=1000&socketTimeout=5000&useSSL=false
        user-name: root
        password: bytearch
        maximum-pool-size: 10
        connection-timeout: 3000
      slaves:
        - driver-class-name: com.mysql.cj.jdbc.Driver
          jdbc-url: jdbc:mysql://172.30.1.33:3306/order_db_3?useUnicode=true&characterEncoding=utf8&autoReconnect=true&rewriteBatchedState&connectTimeout=1000&socketTimeout=5000&useSSL=false
          user-name: root
          password: bytearch
          maximum-pool-size: 10
          connection-timeout: 3000
    - name: order_db_4
      master:
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://172.30.1.24:3306/order_db_4?useUnicode=true&characterEncoding=utf8&autoReconnect=true&rewriteBatchedState&connectTimeout=1000&socketTimeout=5000&useSSL=false
        user-name: root
        password: bytearch
        maximum-pool-size: 10
        connection-timeout: 3000
      slaves:
        - driver-class-name: com.mysql.cj.jdbc.Driver
          jdbc-url: jdbc:mysql://172.30.1.34:3306/order_db_4?useUnicode=true&characterEncoding=utf8&autoReconnect=true&rewriteBatchedState&connectTimeout=1000&socketTimeout=5000&useSSL=false
          user-name: root
          password: bytearch
          maximum-pool-size: 10
          connection-timeout: 300

4、分庫分表策略

1). 根據(jù)order_id為shardKey分庫分表策略

package com.bytearch.fast.cloud.mysql.sharding.strategy;
import com.bytearch.fast.cloud.mysql.sharding.common.ShardingStrategyConstant;
import com.bytearch.id.generator.IdEntity;
import com.bytearch.id.generator.SeqIdUtil;
import org.jfaster.mango.sharding.ShardingStrategy;
/**
 * 訂單號分庫分表策略
 */
public class OrderIdShardingStrategy implements ShardingStrategy<Long, Long> {
    @Override
    public String getDataSourceFactoryName(Long orderId) {
        if (orderId == null || orderId < 0L) {
            throw new IllegalArgumentException("order_id is invalid!");
        }
        IdEntity idEntity = SeqIdUtil.decodeId(orderId);
        if (idEntity.getExtraId() >= ShardingStrategyConstant.SHARDING_TABLE_NUM) {
            throw new IllegalArgumentException("sharding table Num is invalid, tableNum:" + idEntity.getExtraId());
        }
        //1. 計算步長
        int step = ShardingStrategyConstant.SHARDING_TABLE_NUM / ShardingStrategyConstant.SHARDING_DATABASE_NODE_NUM;
        //2. 計算出庫編號
        long dbNo = Math.floorDiv(idEntity.getExtraId(), step) + 1;
        //3. 返回數(shù)據(jù)源名
        return String.format("%s_%s", ShardingStrategyConstant.LOGIC_ORDER_DATABASE_NAME, dbNo);
    }
    @Override
    public String getTargetTable(String logicTableName, Long orderId) {
        if (orderId == null || orderId < 0L) {
            throw new IllegalArgumentException("order_id is invalid!");
        }
        IdEntity idEntity = SeqIdUtil.decodeId(orderId);
        if (idEntity.getExtraId() >= ShardingStrategyConstant.SHARDING_TABLE_NUM) {
            throw new IllegalArgumentException("sharding table Num is invalid, tableNum:" + idEntity.getExtraId());
        }
        // 基于約定，真實表名為 logicTableName_XXX， XXX不足三位補0
        return String.format("%s_%03d", logicTableName, idEntity.getExtraId());
    }
}

2). 根據(jù)user_id 為shardKey分庫分表策略

package com.bytearch.fast.cloud.mysql.sharding.strategy;
import com.bytearch.fast.cloud.mysql.sharding.common.ShardingStrategyConstant;
import org.jfaster.mango.sharding.ShardingStrategy;
/**
 * 指定分片KEY 分庫分表策略
 */
public class UserIdShardingStrategy implements ShardingStrategy<Integer, Integer> {
    @Override
    public String getDataSourceFactoryName(Integer userId) {
        //1. 計算步長 即單庫放得表數(shù)量
        int step = ShardingStrategyConstant.SHARDING_TABLE_NUM / ShardingStrategyConstant.SHARDING_DATABASE_NODE_NUM;
        //2. 計算出庫編號
        long dbNo = Math.floorDiv(userId % ShardingStrategyConstant.SHARDING_TABLE_NUM, step) + 1;
        //3. 返回數(shù)據(jù)源名
        return String.format("%s_%s", ShardingStrategyConstant.LOGIC_ORDER_DATABASE_NAME, dbNo);
    }
    @Override
    public String getTargetTable(String logicTableName, Integer userId) {
        // 基于約定，真實表名為 logicTableName_XXX， XXX不足三位補0
        return String.format("%s_%03d", logicTableName, userId % ShardingStrategyConstant.SHARDING_TABLE_NUM);
    }
}

5、dao層編寫

1). OrderPartitionByIdDao

package com.bytearch.fast.cloud.mysql.sharding.dao;
import com.bytearch.fast.cloud.mysql.sharding.common.ShardingStrategyConstant;
import com.bytearch.fast.cloud.mysql.sharding.pojo.entity.OrderEntity;
import com.bytearch.fast.cloud.mysql.sharding.strategy.OrderIdShardingStrategy;
import org.jfaster.mango.annotation.*;
@DB(name = ShardingStrategyConstant.LOGIC_ORDER_DATABASE_NAME, table = "order")
@Sharding(shardingStrategy = OrderIdShardingStrategy.class)
public interface OrderPartitionByIdDao {
    @SQL("INSERT INTO #table (order_id, user_id, status, booking_date, create_time, update_time) VALUES" +
            "(:orderId,:userId,:status,:bookingDate,:createTime,:updateTime)"
    )
    int insertOrder(@TableShardingBy("orderId") @DatabaseShardingBy("orderId") OrderEntity orderEntity);
    @SQL("UPDATE #table set update_time = now()" +
            "#if(:bookingDate != null),booking_date = :bookingDate #end " +
            "#if (:status != null), status = :status #end" +
            "WHERE order_id = :orderId"
    )
    int updateOrderByOrderId(@TableShardingBy("orderId") @DatabaseShardingBy("orderId") OrderEntity orderEntity);

    @SQL("SELECT * FROM #table WHERE order_id = :1")
    OrderEntity getOrderById(@TableShardingBy @DatabaseShardingBy Long orderId);
    @SQL("SELECT * FROM #table WHERE order_id = :1")
    @UseMaster
    OrderEntity getOrderByIdFromMaster(@TableShardingBy @DatabaseShardingBy Long orderId);

6、單元測試

@SpringBootTest(classes = {Application.class})
@RunWith(SpringJUnit4ClassRunner.class)
public class ShardingTest {
    @Autowired
    OrderPartitionByIdDao orderPartitionByIdDao;
    @Autowired
    OrderPartitionByUserIdDao orderPartitionByUserIdDao;
    @Test
    public void testCreateOrderRandom() {
        for (int i = 0; i < 20; i++) {
            int userId = ThreadLocalRandom.current().nextInt(1000,1000000);
            OrderEntity orderEntity = new OrderEntity();
            orderEntity.setOrderId(SeqIdUtil.nextId(userId % ShardingStrategyConstant.SHARDING_TABLE_NUM));
            orderEntity.setStatus(1);
            orderEntity.setUserId(userId);
            orderEntity.setCreateTime(new Date());
            orderEntity.setUpdateTime(new Date());
            orderEntity.setBookingDate(new Date());
            int ret = orderPartitionByIdDao.insertOrder(orderEntity);
            Assert.assertEquals(1, ret);
        }
    }
    @Test
    public void testOrderAll() {
        //insert
        int userId = ThreadLocalRandom.current().nextInt(1000,1000000);
        OrderEntity orderEntity = new OrderEntity();
        orderEntity.setOrderId(SeqIdUtil.nextId(userId % ShardingStrategyConstant.SHARDING_TABLE_NUM));
        orderEntity.setStatus(1);
        orderEntity.setUserId(userId);
        orderEntity.setCreateTime(new Date());
        orderEntity.setUpdateTime(new Date());
        orderEntity.setBookingDate(new Date());
        int i = orderPartitionByIdDao.insertOrder(orderEntity);
        Assert.assertEquals(1, i);
        //get from master
        OrderEntity orderInfo = orderPartitionByIdDao.getOrderByIdFromMaster(orderEntity.getOrderId());
        Assert.assertNotNull(orderInfo);
        Assert.assertEquals(orderInfo.getOrderId(), orderEntity.getOrderId());
        //get from slave
        OrderEntity slaveOrderInfo = orderPartitionByIdDao.getOrderById(orderEntity.getOrderId());
        Assert.assertNotNull(slaveOrderInfo);
        //update
        OrderEntity updateEntity = new OrderEntity();
        updateEntity.setOrderId(orderInfo.getOrderId());
        updateEntity.setStatus(2);
        updateEntity.setUpdateTime(new Date());
        int affectRows = orderPartitionByIdDao.updateOrderByOrderId(updateEntity);
        Assert.assertTrue( affectRows > 0);
    }
    @Test
    public void testGetListByUserId() {
        int userId = ThreadLocalRandom.current().nextInt(1000,1000000);
        for (int i = 0; i < 5; i++) {
            OrderEntity orderEntity = new OrderEntity();
            orderEntity.setOrderId(SeqIdUtil.nextId(userId % ShardingStrategyConstant.SHARDING_TABLE_NUM));
            orderEntity.setStatus(1);
            orderEntity.setUserId(userId);
            orderEntity.setCreateTime(new Date());
            orderEntity.setUpdateTime(new Date());
            orderEntity.setBookingDate(new Date());
            orderPartitionByIdDao.insertOrder(orderEntity);
        }
        try {
            //防止主從延遲引起的校驗錯誤
            Thread.sleep(1000);
        } catch (InterruptedException e) {
            e.printStackTrace();
        }
        List<OrderEntity> orderListByUserId = orderPartitionByUserIdDao.getOrderListByUserId(userId);
        Assert.assertNotNull(orderListByUserId);
        Assert.assertTrue(orderListByUserId.size() == 5);
    }
}

大功告成:

四、總結(jié)

本篇主要介紹Java版使用Mango框架實現(xiàn)Mysql分庫分表實戰(zhàn)，分庫分表中間件也可以使用類似于ShardingJDBC，或者自研。

以上分庫分表數(shù)量僅供演示參考，實際工作中分表數(shù)量、分庫數(shù)量、是根據(jù)公司實際業(yè)務(wù)數(shù)據(jù)增長速度, 高峰期QPS,物理機器配置等等因素計算。

到此這篇關(guān)于淺談訂單重構(gòu)之 MySQL 分庫分表實戰(zhàn)篇的文章就介紹到這了,更多相關(guān)MySQL 分庫分表內(nèi)容請搜索本站以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持本站！

海外穩(wěn)定服務(wù)器

版權(quán)聲明：本站文章來源標注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請保持原文完整并注明來源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非maisonbaluchon.cn所屬的服務(wù)器上建立鏡像，否則將依法追究法律責任。本站部分內(nèi)容來源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來，僅供學(xué)習(xí)參考，不代表本站立場，如有內(nèi)容涉嫌侵權(quán)，請聯(lián)系alex-e#qq.com處理。

相關(guān)文章

mysql學(xué)習(xí)筆記之表的基本操作

centos編譯安裝mysql 5.6及安裝多個mysql實例詳解

mysql 5.7.11 winx64.zip安裝配置方法圖文教程

mysql 5.7.17 winx64.zip安裝配置方法圖文教程

CentOS安裝mysql5.7 及簡單配置教程詳解

MySQL 5.7 zip版本(zip版)安裝配置步驟詳解

MySQL5.6.31 winx64.zip 安裝配置教程詳解

MySQL注入繞開過濾的技巧總結(jié)

一次Mysql死鎖排查過程的全紀錄

Windows10 64位安裝MySQL5.6.35的圖文教程

服務(wù)器配置數(shù)據(jù)庫配置服務(wù)器運維服務(wù)器安全
互聯(lián)網(wǎng)資訊服務(wù)器公司 IDC數(shù)據(jù)中心
免費服務(wù)器香港服務(wù)器租用美國服務(wù)器租用

五月综合激情婷婷六月,日韩欧美国产一区不卡,他扒开我内裤强吻我下面视频 ,无套内射无矿码免费看黄,天天躁,日日躁,狠狠躁

公司動態(tài)

產(chǎn)品資訊

行業(yè)資訊

淺談訂單重構(gòu)之 MySQL 分庫分表實戰(zhàn)篇

目錄

一、目標

二、環(huán)境準備

1、基本信息

2、數(shù)據(jù)庫環(huán)境準備

3、建庫 & 導(dǎo)入分表

三、配置&實踐

1、pom文件

2、常量配置

3、yml 配置

4、分庫分表策略

5、dao層編寫

6、單元測試

四、總結(jié)

CentOS7安裝MySQL 8.0.26的過程

MySQL中的回表和索引覆蓋示例詳解

實時開通

免備案

全天候客戶服務(wù)

專屬顧問服務(wù)

五月综合激情婷婷六月,日韩欧美国产一区不卡,他扒开我内裤强吻我下面视频 ,无套内射无矿码免费看黄,天天躁,日日躁,狠狠躁

公司動態(tài)

產(chǎn)品資訊

行業(yè)資訊

淺談訂單重構(gòu)之 MySQL 分庫分表實戰(zhàn)篇

目錄

一、目標

二、環(huán)境準備

1、基本信息

2、數(shù)據(jù)庫環(huán)境準備

3、建庫 & 導(dǎo)入分表

三、配置&實踐

1、pom文件

2、常量配置

3、yml 配置

4、分庫分表策略

5、dao層編寫

6、單元測試

四、總結(jié)

CentOS7安裝MySQL 8.0.26的過程

MySQL中的回表和索引覆蓋示例詳解

實時開通

免備案

全天候客戶服務(wù)

專屬顧問服務(wù)

一、目標

二、環(huán)境準備

1、基本信息

2、數(shù)據(jù)庫環(huán)境準備

3、建庫 & 導(dǎo)入分表

三、配置&實踐

1、pom文件

2、常量配置

3、yml 配置

4、分庫分表策略

6、單元測試

四、總結(jié)