This is the 8th day of my participation in the August More text Challenge. For details, see: August More Text Challenge

background

With the development of the time and business, the amount of data in the database growth is not controlled, library and table data will be more and more big, then there is a higher resource consumption, disk, IO, system overhead, there will be a performance bottleneck, a server resources is limited after all, so you need to connect to a different server, split the database and table, In order to provide better data services.

Generally speaking, when the data of a table reaches tens of millions, it will be very difficult to do many operations. Therefore, when the data grows to more than 10 million, it is necessary to divide database and table to relieve the pressure of single database and single table.

Depots table

The data in one database is distributed to multiple databases (hosts) to distribute the load on a single device.

Vertical segmentation

This is known as vertical sharding, which can be divided into different databases (on the host) according to different tables (or schemas).

Is the biggest characteristic of vertical segmentation rules simple, implementation is also more convenient, especially for the coupling between the business is very low, mutual influence is very small, the business logic is very clear system, in this system, can easily achieve different business module used by the table split to different database, depending on the table to break up, The impact on the application is also less, and the split rules are simpler and clearer.

Advantages:

  • After the split, the business is clear and the split rules are clear
  • Easy integration or expansion between systems
  • Simple data maintenance

Disadvantages:

  • Some services cannot be joined and can only be solved through interfaces, which increases the system complexity
  • Each service has a single library performance bottleneck, which makes it difficult to expand data and improve performance
  • Transaction processing complexity

The level of segmentation

According to the logical relationship of the data in the table, the data in the same table is split into multiple databases (hosts) according to certain conditions. This split is called database horizontal (horizontal) splitting.

Horizontal sharding is a bit more complicated than vertical sharding because the rules of splitting different data from the same table into different databases are inherently more complex for different applications than splitting data by table name, and later data maintenance is also more complex.

Advantages:

  • Split rule abstract number, join operation can be basically done by the database
  • There is no single library big data, high concurrency performance bottleneck
  • The application end has less transformation
  • Improved system stability and load capacity

Disadvantages:

  • Split rules are hard to abstract
  • Shard transaction consistency is difficult to resolve
  • The difficulty of multiple data expansion and maintenance is huge
  • Poor performance of cross-library join

Use SpringBoot + MyBatis to realize the operation of library and table

Start by creating the user table in different databases

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `id` int(20) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL.`password` int(25) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 11 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
Copy the code

Creating an entity class


@Data
public class User {
   private int id;
   private String username;
   private int password;
}
Copy the code

Create daOs separately

@Mapper
public interface User1Dao {
    List<User> getUser(a);
​
    User getUserById(Integer id);
​
    void update(User user);
​
    void deleteUser(Integer id);
​
    User queryByName(String name);
}
Copy the code

@Mapper
public interface User2Dao {
    List<User> getUser();
}
Copy the code

Create XML files separately



      
<! DOCTYPEmapper PUBLIC "- / / mybatis.org//DTD Mapper / 3.0 / EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.sense.dao.mapper1.User1Dao">
    <select id="getUser" resultType="com.sense.dingtalkdemo.entity.User">
        select *
        from user
    </select>
</mapper>
Copy the code


      
<! DOCTYPEmapper PUBLIC "- / / mybatis.org//DTD Mapper / 3.0 / EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.sense.dao.mapper2.User2Dao" >
    <select id="getUser" resultType="com.sense.dingtalkdemo.entity.User">
        select * from user
    </select>
</mapper>
Copy the code

For convenience, no service is created here. You can create your own service if needed

Configure the application.yml file


spring:
  datasource:
    one:
      driver-class-name: com.mysql.cj.jdbc.Driver
      jdbc-url: jdbc:mysql://localhost1:3306/monitor? useUnicode=true&useSSL=true
      username: root
      password: root
      max-idle: 10
      min-idle: 10000
      initial-size: 5
    two:
      driver-class-name: com.mysql.cj.jdbc.Driver
      jdbc-url: jdbc:mysql://localhost2:3306/monitor? useUnicode=true&useSSL=true
      username: root
      password: root
      max-idle: 10
      min-idle: 10000
      initial-size: 5
Copy the code

Create a conf with @primary indicating the default data source


@Configuration
public class DataSourceConfig {
    @Bean("dbOne")
    @ConfigurationProperties(prefix = "spring.datasource.one")
    @Primary
    DataSource dbOne(a) {
        return DataSourceBuilder.create().build();
    }
​
    @Bean("dbTwo")
    @ConfigurationProperties(prefix = "spring.datasource.two")
    @Primary
    DataSource dbTwo(a) {
        returnDataSourceBuilder.create().build(); }}Copy the code

@Configuration
@MapperScan(basePackages = "com.sense.dingtalkdemo.dao.mapper1", sqlSessionFactoryRef = "sqlSessionFactory1", sqlSessionTemplateRef = "sqlSessionTemplate1")
public class MybatisConfigOne {
    @Resource(name = "dbOne")
    DataSource dbOne;
​
    @Bean
    @Primary
    SqlSessionFactory sqlSessionFactory1(a) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dbOne);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper1/*.xml"));
        return bean.getObject();
    }
​
    @Bean
    @Primary
    SqlSessionTemplate sqlSessionTemplate1(a) throws Exception {
        return newSqlSessionTemplate(sqlSessionFactory1()); }}Copy the code

@Configuration
@MapperScan(basePackages = "com.sense.dingtalkdemo.dao.mapper2", sqlSessionFactoryRef = "sqlSessionFactory2", sqlSessionTemplateRef = "sqlSessionTemplate2")
public class MybatisConfigTwo {
    @Resource(name = "dbTwo")
    DataSource dbTwo;
​
    @Bean
    SqlSessionFactory sqlSessionFactory2(a) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dbTwo);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper2/*.xml"));
        return bean.getObject();
    }
​
    @Bean
    SqlSessionTemplate sqlSessionTemplate2(a) throws Exception {
        return newSqlSessionTemplate(sqlSessionFactory2()); }}Copy the code

Controller layer, using postman tests


@RestController
@RequestMapping("/get")
public class UserController {
    @Resource
    private User1Dao user1Dao;
    @Resource
    private UserService userService;
    @Resource
    private com.sense.dingtalkdemo.dao.mapper2.User2Dao user2Dao;
​
    @RequestMapping("user1")
    public String getUser1(a) {
        return user1Dao.getUser().toString();
    }
​
    @RequestMapping("user2")
    public String getUser2(a) {
        returnuser2Dao.getUser().toString(); }}Copy the code