国产xxxx99真实实拍_久久不雅视频_高清韩国a级特黄毛片_嗯老师别我我受不了了小说

資訊專欄INFORMATION COLUMN

使用 Sharding-Jdbc 實現分庫分表、讀寫分離（未完待續）

zzbo / 2434人閱讀

摘要：未完待續規劃實現分庫分表讀寫分離。計劃表按照分庫，按照主鍵分片表按照分庫，按照主鍵分片原有庫分成兩個庫，使用對取模，例如的存入庫，的存入庫。讀寫分離設置為個寫庫，個讀庫。虛擬表物理表指的是分表后真實存在的表名。

先貼代碼, 回頭講解。未完待續

規劃

實現分庫分表、讀寫分離。
準備兩張表：svc_order, svc_order_item

結構如下，可以想象成是未實施分庫分表之前的數據庫結構：

CREATE DATABASE IF NOT EXISTS DATABASE;
USE DATABASE;

CREATE TABLE IF NOT EXISTS `svc_order` (
  `order_id` bigint(64) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(64) DEFAULT NULL,
  `order_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS  `svc_order_item` (
  `order_item_id` bigint(64) NOT NULL AUTO_INCREMENT,
  `order_id` bigint(64) DEFAULT NULL,
  `order_item_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`order_item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

一定要對原有結構做到心中有數，重復表述一下：
我們原有一個數據庫叫 database, 里面有兩張表，分別是 svc_order 訂單表和 svc_order_item 訂單明細表，每個表都有一個主鍵ID 、用戶ID(user_id) 和 名稱。

計劃：
svc_order 表按照 user_id 分庫，按照主鍵 order_id 分片
svc_order_item 表按照 user_id 分庫，按照主鍵 order_item_id 分片

原有庫分成兩個庫，使用 user_id 對 2 取模，例如 user_id = 2 的存入 0 庫，user_id = 3 的存入 1 庫。
原有表分成兩個表，svc_order 訂單表使用 order_id 對 2 取模，svc_order_item 表使用 order_item_id 對 2 取模，例如 order_id = 2 的存入 0 表，order_id = 3 的存入 1 表。

讀寫分離設置為 1 個寫庫，2 個讀庫。

為了便于理解，我們定義常用名詞：

邏輯表：指的就是未實施分表之前的表名。例如 svc_order 就是邏輯表名。

虛擬表、物理表：指的是分表后真實存在的表名。上面我們說到存到 0 表、 1 表就是虛擬表，為了便于理解，我們把邏輯表名加在前面，比如 svc_order 的兩個虛表我們可以命名為 svc_order_0、svc_order_1

順便說明一下數據庫名，我們原有 database, 由于要分成兩個庫，我們可以命名為 database_0、database_1，由于，需要讀寫分離（1帶2），相當于又多了四個數據庫，至此，我們的數據庫應該有 write_database_0,read_0_database_0,read_1_database_0,write_database_1,read_0_database_1,read_1_database_1

數據庫

init.sql

DROP DATABASE IF EXISTS write_database_0;
DROP DATABASE IF EXISTS read_0_database_0;
DROP DATABASE IF EXISTS read_1_database_0;
DROP DATABASE IF EXISTS write_database_1;
DROP DATABASE IF EXISTS read_0_database_1;
DROP DATABASE IF EXISTS read_1_database_1;

CREATE DATABASE IF NOT EXISTS write_database_0;
USE write_database_0;

CREATE TABLE IF NOT EXISTS `svc_order_0` (
`order_id` bigint(64) NOT NULL AUTO_INCREMENT,
`user_id` bigint(64) DEFAULT NULL,
`order_name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS  `svc_order_1` (
`order_id` bigint(64) NOT NULL AUTO_INCREMENT,
`user_id` bigint(64) DEFAULT NULL,
`order_name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS  `svc_order_item_0` (
`order_item_id` bigint(64) NOT NULL AUTO_INCREMENT,
`order_id` bigint(64) DEFAULT NULL,
`order_item_name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`order_item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS  `svc_order_item_1` (
`order_item_id` bigint(64) NOT NULL AUTO_INCREMENT,
`order_id` bigint(64) DEFAULT NULL,
`order_item_name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`order_item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE DATABASE IF NOT EXISTS read_1_database_0;
USE read_1_database_0;

CREATE TABLE IF NOT EXISTS `svc_order_0` (
`order_id` bigint(64) NOT NULL AUTO_INCREMENT,
`user_id` bigint(64) DEFAULT NULL,
`order_name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS  `svc_order_1` (
`order_id` bigint(64) NOT NULL AUTO_INCREMENT,
`user_id` bigint(64) DEFAULT NULL,
`order_name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS  `svc_order_item_0` (
`order_item_id` bigint(64) NOT NULL AUTO_INCREMENT,
`order_id` bigint(64) DEFAULT NULL,
`order_item_name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`order_item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS  `svc_order_item_1` (
`order_item_id` bigint(64) NOT NULL AUTO_INCREMENT,
`order_id` bigint(64) DEFAULT NULL,
`order_item_name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`order_item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE DATABASE IF NOT EXISTS read_0_database_0;
USE read_0_database_0;

CREATE TABLE IF NOT EXISTS `svc_order_0` (
`order_id` bigint(64) NOT NULL AUTO_INCREMENT,
`user_id` bigint(64) DEFAULT NULL,
`order_name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS  `svc_order_1` (
`order_id` bigint(64) NOT NULL AUTO_INCREMENT,
`user_id` bigint(64) DEFAULT NULL,
`order_name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS  `svc_order_item_0` (
`order_item_id` bigint(64) NOT NULL AUTO_INCREMENT,
`order_id` bigint(64) DEFAULT NULL,
`order_item_name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`order_item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS  `svc_order_item_1` (
`order_item_id` bigint(64) NOT NULL AUTO_INCREMENT,
`order_id` bigint(64) DEFAULT NULL,
`order_item_name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`order_item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE DATABASE IF NOT EXISTS write_database_1;
USE write_database_1;

CREATE TABLE IF NOT EXISTS `svc_order_0` (
`order_id` bigint(64) NOT NULL AUTO_INCREMENT,
`user_id` bigint(64) DEFAULT NULL,
`order_name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS  `svc_order_1` (
`order_id` bigint(64) NOT NULL AUTO_INCREMENT,
`user_id` bigint(64) DEFAULT NULL,
`order_name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS  `svc_order_item_0` (
`order_item_id` bigint(64) NOT NULL AUTO_INCREMENT,
`order_id` bigint(64) DEFAULT NULL,
`order_item_name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`order_item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS  `svc_order_item_1` (
`order_item_id` bigint(64) NOT NULL AUTO_INCREMENT,
`order_id` bigint(64) DEFAULT NULL,
`order_item_name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`order_item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE DATABASE IF NOT EXISTS read_0_database_1;
USE read_0_database_1;

CREATE TABLE IF NOT EXISTS `svc_order_0` (
`order_id` bigint(64) NOT NULL AUTO_INCREMENT,
`user_id` bigint(64) DEFAULT NULL,
`order_name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS  `svc_order_1` (
`order_id` bigint(64) NOT NULL AUTO_INCREMENT,
`user_id` bigint(64) DEFAULT NULL,
`order_name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS  `svc_order_item_0` (
`order_item_id` bigint(64) NOT NULL AUTO_INCREMENT,
`order_id` bigint(64) DEFAULT NULL,
`order_item_name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`order_item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS  `svc_order_item_1` (
`order_item_id` bigint(64) NOT NULL AUTO_INCREMENT,
`order_id` bigint(64) DEFAULT NULL,
`order_item_name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`order_item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE DATABASE IF NOT EXISTS read_1_database_1;
USE read_1_database_1;

CREATE TABLE IF NOT EXISTS `svc_order_0` (
`order_id` bigint(64) NOT NULL AUTO_INCREMENT,
`user_id` bigint(64) DEFAULT NULL,
`order_name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS  `svc_order_1` (
`order_id` bigint(64) NOT NULL AUTO_INCREMENT,
`user_id` bigint(64) DEFAULT NULL,
`order_name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS  `svc_order_item_0` (
`order_item_id` bigint(64) NOT NULL AUTO_INCREMENT,
`order_id` bigint(64) DEFAULT NULL,
`order_item_name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`order_item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS  `svc_order_item_1` (
`order_item_id` bigint(64) NOT NULL AUTO_INCREMENT,
`order_id` bigint(64) DEFAULT NULL,
`order_item_name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`order_item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
依賴

pom.xml


    com.alibaba
    druid
    1.1.5


    mysql
    mysql-connector-java
    6.0.6


    com.dangdang
    sharding-jdbc-core
    1.5.4.1


    org.projectlombok
    lombok
    1.16.18
    provided
配置

ShardingJdbcConfig.java

package com.junbaor.sharding.config;

import com.alibaba.druid.pool.DruidDataSource;
import com.dangdang.ddframe.rdb.sharding.api.MasterSlaveDataSourceFactory;
import com.dangdang.ddframe.rdb.sharding.api.ShardingDataSourceFactory;
import com.dangdang.ddframe.rdb.sharding.api.rule.BindingTableRule;
import com.dangdang.ddframe.rdb.sharding.api.rule.DataSourceRule;
import com.dangdang.ddframe.rdb.sharding.api.rule.ShardingRule;
import com.dangdang.ddframe.rdb.sharding.api.rule.TableRule;
import com.dangdang.ddframe.rdb.sharding.api.strategy.database.DatabaseShardingStrategy;
import com.dangdang.ddframe.rdb.sharding.api.strategy.slave.MasterSlaveLoadBalanceStrategyType;
import com.dangdang.ddframe.rdb.sharding.api.strategy.table.TableShardingStrategy;
import com.dangdang.ddframe.rdb.sharding.config.ShardingPropertiesConstant;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;


@Configuration
public class ShardingJdbcConfig {

    @Bean
    public DataSource buildDataSource() throws SQLException {
        /*讀寫分離 start */
        DataSource writeDatabase0 = createDataSource("write_database_0");
        Map slave0DataBase = new HashMap<>(2);
        slave0DataBase.put("read0_database_0", createDataSource("read0_database_0"));
        slave0DataBase.put("read1_database_0", createDataSource("read1_database_0"));
        DataSource database0 = MasterSlaveDataSourceFactory.createDataSource("write_database_0", "write_database_0", writeDatabase0, slave0DataBase, MasterSlaveLoadBalanceStrategyType.ROUND_ROBIN);

        DataSource writeDatabase1 = createDataSource("write_database_1");
        Map slave1DataBase = new HashMap<>(2);
        slave1DataBase.put("read0_database_1", createDataSource("read0_database_1"));
        slave1DataBase.put("read1_database_1", createDataSource("read1_database_1"));
        DataSource database1 = MasterSlaveDataSourceFactory.createDataSource("write_database_1", "write_database_1", writeDatabase1, slave1DataBase, MasterSlaveLoadBalanceStrategyType.ROUND_ROBIN);
        /*讀寫分離 end */

        Map dataSourceMap = new HashMap<>(2);
        dataSourceMap.put("database_0", database0);
        dataSourceMap.put("database_1", database1);

        /*分庫分表 start*/
        DataSourceRule dataSourceRule = new DataSourceRule(dataSourceMap, "database_0");

        TableRule orderTableRule = TableRule.builder("svc_order")
                .actualTables(Arrays.asList("svc_order_0", "svc_order_1"))
                .generateKeyColumn("order_id", KeyGenerate.class)
                .dataSourceRule(dataSourceRule)
                .build();

        TableRule orderItemTableRule = TableRule.builder("svc_order_item")
                .actualTables(Arrays.asList("svc_order_item_0", "svc_order_item_1"))
                .generateKeyColumn("order_item_id",KeyGenerate.class)
                .dataSourceRule(dataSourceRule)
                .build();

        BindingTableRule bindOrderAndOrderItem = new BindingTableRule(Arrays.asList(orderTableRule, orderItemTableRule));

        ShardingRule shardingRule = ShardingRule.builder()
                .dataSourceRule(dataSourceRule)
                .tableRules(Arrays.asList(orderTableRule, orderItemTableRule))
                .databaseShardingStrategy(new DatabaseShardingStrategy(Arrays.asList("user_id"), new DbSharding()))
                .tableShardingStrategy(new TableShardingStrategy(Arrays.asList("order_id"), new TableSharding()))
                .bindingTableRules(Arrays.asList(bindOrderAndOrderItem))
                .build();
        /*分庫分表 end*/

        Properties properties = new Properties();
        properties.setProperty(ShardingPropertiesConstant.SQL_SHOW.getKey(), "true");
        DataSource dataSource = ShardingDataSourceFactory.createDataSource(shardingRule, properties);

        return dataSource;
    }

    private static DataSource createDataSource(final String dataSourceName) {
        DruidDataSource result = new DruidDataSource();
        result.setUrl(String.format("jdbc:mysql://127.0.0.1:3306/%s?useUnicode=true&characterEncoding=utf-8&useSSL=false", dataSourceName));
        result.setUsername("root");
        result.setPassword("root");
        return result;
    }

}
分片工具

AppUtils.java

package com.junbaor.sharding.util;

import com.dangdang.ddframe.rdb.sharding.api.ShardingValue;

import java.util.Arrays;
import java.util.Collection;
import java.util.Iterator;

public class AppUtils {

    public static Collection sharding(Collection availableTargetNames, Collection> shardingValues) {
        long shardingvalue = 0;

        Iterator> iterator = shardingValues.iterator();
        if (iterator.hasNext()) {
            shardingvalue = ((Long) iterator.next().getValue()).intValue();
        }

        for (String tableName : availableTargetNames) {
            if (tableName.endsWith((shardingvalue % 2) + "")) {
                return Arrays.asList(tableName);
            }
        }
        return null;
    }
}
分庫算法

DbSharding.java

package com.junbaor.sharding.config;

import com.dangdang.ddframe.rdb.sharding.api.ShardingValue;
import com.dangdang.ddframe.rdb.sharding.api.strategy.database.MultipleKeysDatabaseShardingAlgorithm;
import com.junbaor.sharding.util.AppUtils;

import java.util.Collection;

/**
 * 利用多分片鍵接口實現單鍵分片算法
 */
public class DbSharding implements MultipleKeysDatabaseShardingAlgorithm {

    @Override
    public Collection doSharding(Collection availableTargetNames, Collection> shardingValues) {
        Collection sharding = AppUtils.sharding(availableTargetNames, shardingValues);
        if (sharding != null) {
            return sharding;
        }
        return null;
    }
}
分表算法

TableSharding.java

package com.junbaor.sharding.config;

import com.dangdang.ddframe.rdb.sharding.api.ShardingValue;
import com.dangdang.ddframe.rdb.sharding.api.strategy.table.MultipleKeysTableShardingAlgorithm;
import com.junbaor.sharding.util.AppUtils;

import java.util.Collection;

/**
 * 利用多分片鍵接口實現單鍵分片算法
 */
public class TableSharding implements MultipleKeysTableShardingAlgorithm {

    @Override
    public Collection doSharding(Collection availableTargetNames, Collection> shardingValues) {
        Collection sharding = AppUtils.sharding(availableTargetNames, shardingValues);
        if (sharding != null) {
            return sharding;
        }
        return null;
    }

}
主鍵生成器

KeyGenerate.java 僅供單機測試

package com.junbaor.sharding.config;

import com.dangdang.ddframe.rdb.sharding.keygen.KeyGenerator;
import lombok.extern.slf4j.Slf4j;

import java.util.concurrent.atomic.AtomicLong;

@Slf4j
public class KeyGenerate implements KeyGenerator {
    
    private static AtomicLong atomicInteger = new AtomicLong(1);

    @Override
    public Number generateKey() {
        long nextId = atomicInteger.addAndGet(1);
        log.info("nextId:{}", nextId);
        return nextId;
    }
}

文章版權歸作者所有，未經允許請勿轉載,若此文章存在違規行為，您可以聯系管理員刪除。

轉載請注明本文地址：http://specialneedsforspecialkids.com/yun/68050.html

相關文章

  • Sharding-Jdbc實現mysql分庫分表

    摘要：實現數據庫分庫分表可以自己實現，也可以使用和實現。分布式數據庫的自增不是自增的。分布式數據庫分頁查詢需要使用插入時間實現。包含分庫分片和讀寫分離功能。 Sharding-Jdbc實現mysql分庫分表 簡單介紹 數據庫分庫分表和讀寫分離區別，分庫分表是在多個庫建相同的表和同一個庫建不同的表，根據隨機或者哈希等方式查找實現。讀寫分離是為了解決數據庫的讀寫性能不足，使用主庫master進行...

    go4it 評論0 收藏0

  • Spring Boot中整合Sharding-JDBC讀寫分離示例

    摘要：今天就給大家介紹下方式的使用，主要講解讀寫分離的配置，其余的后面再介紹。主要還是用提供的，配置如下配置內容如下主數據源從數據源讀寫分離配置查詢時的負載均衡算法，目前有種算法，輪詢和隨機，算法接口是。 在我《Spring Cloud微服務-全棧技術與案例解析》書中，第18章節分庫分表解決方案里有對Sharding-JDBC的使用進行詳細的講解。 之前是通過XML方式來配置數據源，讀寫分離...

    kbyyd24 評論0 收藏0

  • 數據庫的分庫分表

    摘要：如成千上萬的螞蟻完成一項搬運工作縱向擴展又叫垂直擴展，擴展一個點的能力支撐更大的請求。如利用個人的能力，如蜘蛛俠逼停火車參考分庫分表的基本思想 一 概念:什么是分庫分表(sharding) 1 將集中于單一節點的數據拆分并分別存儲到多個數據庫或表,稱為分庫分表 2 數據切分分為兩種方式,垂直切分和水平切分 3 分庫:因為表多導致數據過多使用垂直切分,垂直切分就是根據業務的耦合性,將關聯...

    elarity 評論0 收藏0

發表評論

0條評論

zzbo

男|高級講師

TA的文章

閱讀更多
最新活動
閱讀需要支付1元查看
<