欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

淺談?dòng)唵沃貥?gòu)之 MySQL 分庫(kù)分表實(shí)戰(zhàn)篇

 更新時(shí)間：2021年09月08日 10:04:42   作者：公眾賬號(hào)  
這篇文章主要介紹了 MySQL 分庫(kù)分表方法的相關(guān)資料,需要的朋友可以參考下面文章內(nèi)容，希望能幫助到你

一、目標(biāo)

本文將完成如下目標(biāo):

  • 分表數(shù)量: 256    分庫(kù)數(shù)量: 4
  • 以用戶ID(user_id) 為數(shù)據(jù)庫(kù)分片Key
  • 最后測(cè)試訂單創(chuàng)建，更新，刪除, 單訂單號(hào)查詢，根據(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, 本文重點(diǎn)在于分庫(kù)分表實(shí)踐, 只保留具有代表性字段，其它場(chǎng)景可以在此基礎(chǔ)上做改進(jìn)。

全局唯一ID設(shè)計(jì)

要求:1.全局唯一 2:粗略有序 3:可反解出庫(kù)編號(hào)

  • 1bit + 39bit時(shí)間差 + 8bit機(jī)器號(hào) + 8bit用戶編號(hào)(庫(kù)號(hào)) + 8bit自增序列

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

單機(jī)最大QPS: 256000 使用壽命: 17年

二、環(huán)境準(zhǔn)備

1、基本信息

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

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

進(jìn)入mysql:

#主庫(kù)
 mysql -h 172.30.1.21 -uroot -pbytearch

#從庫(kù)
 mysql -h 172.30.1.31 -uroot -pbytearch

進(jìn)入容器

#主
docker exec -it db_1_master /bin/bash

#從
docker exec -it db_1_slave /bin/bash

查看運(yùn)行狀態(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、建庫(kù) & 導(dǎo)入分表

（1）在mysql master實(shí)例分別建庫(kù)

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;

三、配置&實(shí)踐

1、pom文件  

     <!-- mango 分庫(kù)分表中間件 --> 
            <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;

/**
 * 分庫(kù)分表策略常用常量
 */
public class ShardingStrategyConstant {
    /**
     * database 邏輯名稱 ，真實(shí)庫(kù)名為 order_db_XXX
     */
    public static final String LOGIC_ORDER_DATABASE_NAME = "order_db";
    /**
     * 分表數(shù) 256，一旦確定不可更改
     */
    public static final int SHARDING_TABLE_NUM = 256;

    /**
     * 分庫(kù)數(shù), 不建議更改， 可以更改，但是需要DBA遷移數(shù)據(jù)
     */
    public static final int SHARDING_DATABASE_NODE_NUM = 4;
}

3、yml 配置

4主4從數(shù)據(jù)庫(kù)配置， 這里僅測(cè)試默認(rèn)使用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、分庫(kù)分表策略

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

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;

/**
 * 訂單號(hào)分庫(kù)分表策略
 */
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. 計(jì)算步長(zhǎng)
        int step = ShardingStrategyConstant.SHARDING_TABLE_NUM / ShardingStrategyConstant.SHARDING_DATABASE_NODE_NUM;
        //2. 計(jì)算出庫(kù)編號(hào)
        long dbNo = Math.floorDiv(idEntity.getExtraId(), step) + 1;
        //3. 返回?cái)?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());
        }
        // 基于約定，真實(shí)表名為 logicTableName_XXX， XXX不足三位補(bǔ)0
        return String.format("%s_%03d", logicTableName, idEntity.getExtraId());
    }
}

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

package com.bytearch.fast.cloud.mysql.sharding.strategy;

import com.bytearch.fast.cloud.mysql.sharding.common.ShardingStrategyConstant;
import org.jfaster.mango.sharding.ShardingStrategy;

/**
 * 指定分片KEY 分庫(kù)分表策略
 */
public class UserIdShardingStrategy implements ShardingStrategy<Integer, Integer> {

    @Override
    public String getDataSourceFactoryName(Integer userId) {
        //1. 計(jì)算步長(zhǎng) 即單庫(kù)放得表數(shù)量
        int step = ShardingStrategyConstant.SHARDING_TABLE_NUM / ShardingStrategyConstant.SHARDING_DATABASE_NODE_NUM;
        //2. 計(jì)算出庫(kù)編號(hào)
        long dbNo = Math.floorDiv(userId % ShardingStrategyConstant.SHARDING_TABLE_NUM, step) + 1;
        //3. 返回?cái)?shù)據(jù)源名
        return String.format("%s_%s", ShardingStrategyConstant.LOGIC_ORDER_DATABASE_NAME, dbNo);
    }

    @Override
    public String getTargetTable(String logicTableName, Integer userId) {
        // 基于約定，真實(shí)表名為 logicTableName_XXX， XXX不足三位補(bǔ)0
        return String.format("%s_%03d", logicTableName, userId % ShardingStrategyConstant.SHARDING_TABLE_NUM);
    }
}

5、dao層編寫(xiě)

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、單元測(cè)試

@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 {
            //防止主從延遲引起的校驗(yàn)錯(cuò)誤
            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框架實(shí)現(xiàn)Mysql分庫(kù)分表實(shí)戰(zhàn)，分庫(kù)分表中間件也可以使用類似于ShardingJDBC，或者自研。

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

到此這篇關(guān)于淺談?dòng)唵沃貥?gòu)之 MySQL 分庫(kù)分表實(shí)戰(zhàn)篇的文章就介紹到這了,更多相關(guān)MySQL 分庫(kù)分表內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

相關(guān)文章

  • 安裝Mysql時(shí)出現(xiàn)錯(cuò)誤及解決辦法

    安裝Mysql時(shí)出現(xiàn)錯(cuò)誤及解決辦法

    因?yàn)橐粫r(shí)手癢癢更新了一下驅(qū)動(dòng)，結(jié)果導(dǎo)致無(wú)線網(wǎng)卡出了問(wèn)題，本文給大家分享安裝mysql時(shí)出現(xiàn)錯(cuò)誤及解決辦法，對(duì)安裝mysql時(shí)出現(xiàn)錯(cuò)誤相關(guān)知識(shí)感興趣的朋友一起學(xué)習(xí)吧
    2015-12-12
  • mysql中distinct和group?by的區(qū)別淺析

    mysql中distinct和group?by的區(qū)別淺析

    distinct簡(jiǎn)單來(lái)說(shuō)就是用來(lái)去重的,而group by的設(shè)計(jì)目的則是用來(lái)聚合統(tǒng)計(jì)的,兩者在能夠?qū)崿F(xiàn)的功能上有些相同之處,但應(yīng)該仔細(xì)區(qū)分,下面這篇文章主要給大家介紹了關(guān)于mysql中distinct和group?by區(qū)別的相關(guān)資料,需要的朋友可以參考下
    2023-05-05
  • 解決mysql服務(wù)器在無(wú)操作超時(shí)主動(dòng)斷開(kāi)連接的情況

    解決mysql服務(wù)器在無(wú)操作超時(shí)主動(dòng)斷開(kāi)連接的情況

    這篇文章主要介紹了解決mysql服務(wù)器在無(wú)操作超時(shí)主動(dòng)斷開(kāi)連接的情況，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧
    2020-07-07
  • MySQL鎖阻塞的深入分析

    MySQL鎖阻塞的深入分析

    這篇文章主要給大家介紹了關(guān)于MySQL鎖阻塞的相關(guān)資料，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧
    2020-12-12
  • MySQL數(shù)據(jù)庫(kù)安裝和Navicat for MySQL配合使用教程

    MySQL數(shù)據(jù)庫(kù)安裝和Navicat for MySQL配合使用教程

    MySQL是一個(gè)關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng)，由瑞典MySQL AB 公司開(kāi)發(fā)，目前屬于 Oracle 旗下公司。這篇文章主要介紹了MySQL數(shù)據(jù)庫(kù)安裝和Navicat for MySQL配合使用,需要的朋友可以參考下
    2019-06-06
  • MySql 5.7.17免安裝配置教程詳解

    MySql 5.7.17免安裝配置教程詳解

    這篇文章給大家詳細(xì)介紹了MySql 5.7.17免安裝配置教程，首先大家需要先下載mysql 5.7.17 的安裝包，然后解壓，具體配置過(guò)程大家通過(guò)本文一起學(xué)習(xí)吧
    2017-03-03
  • mysql 查看表結(jié)構(gòu)數(shù)據(jù)的實(shí)現(xiàn)

    mysql 查看表結(jié)構(gòu)數(shù)據(jù)的實(shí)現(xiàn)

    在MySQL數(shù)據(jù)庫(kù)中,我們經(jīng)常需要查看表的結(jié)構(gòu)和數(shù)據(jù)信息,以便了解表的字段定義、索引情況等,本文主要介紹了mysql 查看表結(jié)構(gòu)數(shù)據(jù)的實(shí)現(xiàn),感興趣的可以了解一下
    2024-05-05
  • 基于MySQL Master Slave同步配置的操作詳解

    基于MySQL Master Slave同步配置的操作詳解

    本篇文章是對(duì)MySQL Master Slave 同步配置進(jìn)行了詳細(xì)的分析介紹，需要的朋友參考下
    2013-06-06
  • MySql存儲(chǔ)過(guò)程之邏輯判斷和條件控制

    MySql存儲(chǔ)過(guò)程之邏輯判斷和條件控制

    本篇文章給大家介紹了mysql存儲(chǔ)過(guò)程之邏輯判斷、條件控制，涉及到的知識(shí)點(diǎn)有邏輯判斷、條件控制方面的內(nèi)容，對(duì)mysql存儲(chǔ)過(guò)程邏輯判斷和條件控制感興趣的朋友可以參考下本文
    2015-10-10
  • 淺談MySQL中授權(quán)(grant)和撤銷授權(quán)(revoke)用法詳解

    淺談MySQL中授權(quán)(grant)和撤銷授權(quán)(revoke)用法詳解

    下面小編就為大家?guī)?lái)一篇淺談MySQL中授權(quán)(grant)和撤銷授權(quán)(revoke)用法詳解。小編覺(jué)得挺不錯(cuò)的，現(xiàn)在就分享給大家，也給大家做個(gè)參考。一起跟隨小編過(guò)來(lái)看看吧
    2016-09-09

最新評(píng)論