Several ways to implement read and write separation allocation in SQL

The separation of read and write allocation, i.e. how to route read and write operations to different database instances in an application, can be implemented in several different ways. These methods can be done in the application layer, the database layer, or using middleware. Here are several common implementation methods:

Application layer implementation

Read and write separation is implemented at the application layer, usually by configuring multiple data sources and explicitly selecting the appropriate data source in the code. Using AOP (System-Oriented Programming) to automatically select data sources is a common approach.

Specific implementation steps

Configure multiple data sources: Configure one master data source (for write operations) and multiple slave data sources (for read operations).
Implement routing logic: Select the appropriate data source in the code through AOP or other means.
Use custom annotations: Tags the method that needs to be routed to different data sources.

The following is a detailed implementation example:

Configuration File

existConfigure the information of the master and slave libraries.

# 
spring:
  datasource:
    master:
      url: jdbc:mysql://master-db:3306/mydb
      username: root
      password: root
    slaves:
      - url: jdbc:mysql://slave-db1:3306/mydb
        username: root
        password: root
      - url: jdbc:mysql://slave-db2:3306/mydb
        username: root
        password: root

Data source configuration

import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;

import ;
import ;
import ;

@Configuration
public class DataSourceConfig {

    @Autowired
    private MasterDataSourceProperties masterProperties;

    @Autowired
    private SlaveDataSourceProperties slaveProperties;

    @Bean
    @Primary
    public DataSource dataSource() {
        AbstractRoutingDataSource routingDataSource = new ReplicationRoutingDataSource();

        HikariDataSource masterDataSource = new HikariDataSource();
        (());
        (());
        (());

        Map<Object, Object> targetDataSources = new HashMap<>();
        ("master", masterDataSource);

        for (int i = 0; i < ().size(); i++) {
            SlaveProperties slave = ().get(i);
            HikariDataSource slaveDataSource = new HikariDataSource();
            (());
            (());
            (());
            ("slave" + i, slaveDataSource);
        }

        (targetDataSources);
        (masterDataSource);

        return routingDataSource;
    }
}

Routing data sources

import ;

public class ReplicationRoutingDataSource extends AbstractRoutingDataSource {

    private static final ThreadLocal<String> contextHolder = new ThreadLocal<>();

    public static void setDataSourceType(String dataSourceType) {
        (dataSourceType);
    }

    public static void clearDataSourceType() {
        ();
    }

    @Override
    protected Object determineCurrentLookupKey() {
        return ();
    }
}

Data source selector

import ;
import ;
import ;

@Aspect
@Component
public class DataSourceAspect {

    @Before("@annotation()")
    public void setWriteDataSourceType() {
        ("master");
    }

    @Before("@annotation() || execution(* ..*.find*(..))")
    public void setReadDataSourceType() {
        ("slave0"); // Can implement load balancing strategies    }
}

Custom annotations

import ;
import ;
import ;
import ;

@Retention()
@Target()
public @interface Master {
}

@Retention()
@Target()
public @interface Slave {
}

Sample Service

import ;
import ;
import ;

@Service
public class UserService {

    @Autowired
    private UserRepository userRepository;

    @Master
    @Transactional
    public void saveUser(User user) {
        (user);
    }

    @Slave
    public User findUserById(Long id) {
        return (id).orElse(null);
    }
}

Using middleware

Using middleware to achieve read and write separation is also a common method. Middleware is usually located between the application and the database and is responsible for routing requests to the appropriate database instance based on the operation type. Common middlewares include ProxySQL for MySQL and MaxScale for MariaDB.

ProxySQL sample configuration

Install ProxySQL: ProxySQL can be installed through the package manager.
Configure ProxySQL:existConfigure the master and slave database in the file.

datadir="/var/lib/proxysql"

admin_variables=
{
    admin_credentials="admin:admin"
    mysql_ifaces="0.0.0.0:6032"
}

mysql_variables=
{
    threads=4
    max_connections=1024
}

mysql_servers =
(
    { address="master-db", port=3306, hostgroup=0, max_connections=1000, weight=1 },
    { address="slave-db1", port=3306, hostgroup=1, max_connections=1000, weight=1 },
    { address="slave-db2", port=3306, hostgroup=1, max_connections=1000, weight=1 }
)

mysql_users =
(
    { username="proxyuser", password="proxypassword", default_hostgroup=0, transaction_persistent=1 }
)

mysql_query_rules =
(
    { rule_id=1, match_pattern="^SELECT", destination_hostgroup=1, apply=1 }
)

Start ProxySQL:usesystemctlor other ways to start ProxySQL.

systemctl start proxysql

Database layer implementation

Some databases themselves provide the function of reading and writing separation. For example, MySQL's replication mechanism allows configuration of a master database and multiple slave databases, and then read-write separation is achieved through connection pools or drivers.

Summarize

There are many ways to implement read and write separation, and you can choose the appropriate method according to specific needs and technology stack. It is more flexible to implement read and write separation at the application layer, and can finely control the routing logic of read and write operations; using middleware to implement read and write separation can simplify the application logic, but it requires additional maintenance of middleware configuration and management; implementing read and write separation at the database layer can utilize the functions of the database itself to reduce changes to the application.

This is the article about several ways to achieve SQL separation and distribution. For more related SQL reading and writing separation and distribution, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!