The separation of database reading and writing is a development stage that many companies can't get around , From monomer database , To active standby mode , Then to read-write separation , Sub database and sub table . Each stage can solve some problems for us , But it also brings new challenges . In this article, we mainly study the database read-write separation and how to solve the problems it brings .

1 Development of database architecture

First, briefly introduce the development of database architecture , Basically monomer , Active and standby , Read write separation , Sub database and sub table , Let's introduce them respectively .

1.1 Monomer architecture

Early stage of business development , The pressure on the database is relatively small , At this time, a single library can be used .

Questions raised : If the database fails , Our business cannot be used , It can only be said to stop and restart to repair the fault .

1.2 Active / standby architecture

Problems caused by monomer , At this time, we need to add a spare library , In case of emergency, it can be used on the top of the standby warehouse , It's equivalent to adding a substitute .

adopt MySQL Built in master-slave synchronization mechanism , We can put our substitutes online .

Be a regular member ( Main library ) Failure , We can manually get it offline , Let the substitutes ( Spare database ) highest point .

Questions raised : With the large-scale outbreak of business , The pressure of the main reservoir is too high , We just want the backup library to take on greater responsibility .

1.3 Read write separation architecture

The essence of the read-write separation architecture is the active / standby architecture , There is no essential difference between active and standby architectures , It is based on the active and standby architecture , Add a layer to handle read / write requests , So that it can make greater use of the standby library to share some reading pressure for us .

Read write separation architecture , You need to add a layer in the middle to control the routing of read-write requests

1.4 Sub database and sub table

The essence of sub database and sub table is to segment data , It is due to the improvement of data level , Not segmenting data will seriously affect the read and write performance of the database .

Even if you don't cut it , disk , Memory ,CPU Can't bear such pressure , The database is on the verge of collapse at any time .

Sub database and sub table are essentially different from the first three , After dividing the database into tables, each database partition can adopt any of the above three methods , It can be monomer fragment , It can also be active and standby partition , It can also be a fragment with read-write separation .

The relationship between sub database and sub table and one of the first three is symbiosis .

2 Read write separation design scheme

Master slave replication is MySQL Functions of database , But if we want to separate reading and writing, we need to do some work ourselves MySQL Master slave synchronization . There are many options .

2.1 agent

Add an agent layer between the application and the database , The agent layer receives the application's request for the database , Forward to different instances according to different request types , Load balancing can be realized while separating reading and writing ( Read requests are transmitted to each slave node according to load balancing rules ).

Agent is the way of using middleware , Control different types of requests , Enter different databases .

Currently commonly used mysql Read write separation Middleware :


MySQL Own an open source project , Through its own Lua Script SQL judge


Qihoo 360, stay mysql-proxy 0.8.2 Based on version , It is optimized , Added some new features .



MariaDB development


Ali development


2.2 Intra application routing

Control in the program , We use the interceptor implementation of the persistence layer framework , Dynamically route different data sources .

utilize Sharding-JDBC It can also be realized

Realization idea :

* Configure multiple data sources
* Set default data source , Configure the switching strategy of the data source
* Intercept requests to enter the database , Set which data source to go according to business requirements .
3 What about the read delay caused by read-write separation ?

Generally, the read-write separation architecture is adopted , There will be synchronization delay , We can only find a way to overcome this problem .

3.1 Data synchronous write slave Library

Master slave replication mode , Generally, data is written to the slave library asynchronously , Of course, this asynchrony can also be set to synchronous , Only when writing from the library is complete , Only write requests on the main library can be returned .

This scheme is the best and most effective one , But it is also the one with the worst performance , Especially when there are a large number of slave libraries , Seriously affect request efficiency .

3.2 cache ( middleware ) Routing method

Cache record on write request key, this key The failure time of is set as the delay of master-slave synchronization , When reading a request, first go to the cache to confirm whether it exists key, If key Existence indicates that a write request has occurred , Data not synchronized to slave Library , Then go to the main warehouse , If this does not exist key, You can query directly from the library .

Middleware should also be able to judge whether synchronization is completed , Similar to using cached records .

The biggest disadvantage of this scheme is the introduction of cache , System complexity increases .

3.3 Selective forced reading of Primary Library

For some special business scenarios , Forced reading of main library is adopted .

malpractice , We need to find out every such situation , Set to force the main library .

3.4 etc. GTID programme

MySQL After executing the transaction , The transaction's GTID Will give the client , The client can then use this command to execute the read operation from the library , Wait for the
GTID, After waiting for success , Then perform the read operation ; If the wait times out , Then go to the main library to read , Or change another slave library to perform the above process .

MariaDB of MaxScale Is to use the scheme ,MaxScale yes MariaDB A database intelligent agent service is developed ( Also support
MySQL), Allow database SQL Statement to redirect a request to a target server or servers , Steering rules of various complexity can be set .

3.5 Maintaining the status quo

There is a delay, there is a delay , Scenarios with low requirements for strong data consistency can be left unchecked .