Three scenarios for MySQL redundant data

One, why redundant data

Internet data volume of business scenarios, often the database needs to be horizontal segmentation to reduce the amount of single-Library data.

The horizontal cutting branch has a patition key, the query with Patition key can be directly located to the library, but the query on the non-patition key may need to scan multiple libraries.

The common architecture design is to use the inverse paradigm design of data redundancy to meet the query requirements of different dimensions after the library.

For example: Order business, to users and businesses have order inquiry requirements:

Order (OID, info_detail);

T (buyer_id, seller_id, OID);

If you use buyer_id to divide the library, seller_id queries need to scan multiple libraries.

If you use seller_id to divide the library, buyer_id queries need to scan multiple libraries.

You can now use data redundancy to meet the query requirements on buyer_id and seller_id, respectively:

T1 (buyer_id, seller_id, OID)

T2 (seller_id, buyer_id, OID)

The same data, redundant two copies, a copy of buyer_id to meet the needs of buyers, a copy of the seller_id to meet the seller's query requirements.

How to implement the redundancy of the data is what will be discussed today.

Two, service synchronous double Write

As the name implies, redundant data is written synchronously by the service layer, such as 1-4 processes:

• Business party invoke service, new data

• Service first inserts T1 data

• Service re-inserting T2 data

• Service returns business party new data success

Advantages :

• Not complex, the service layer by a single write, changed two times write

• Data consistency is relatively high (because double write success is not returned)

Disadvantages :

• Request processing time increased (to insert two times, time doubled)

• Data may still be inconsistent, such as after the second-step write T1 completes the service restart, the data is not written to T2

If the system is sensitive to processing time, it leads to the second common scenario.

Three, service asynchronous double write

The double write of the data is no longer done by the service, and the service layer asynchronously sends a message to a dedicated data replication service through the message bus to write redundant data, such as the 1-6 process:

• Business party invoke service, new data

• Service first inserts T1 data

• The service sends an asynchronous message to the message bus (it can be sent, not returned, and is usually done soon)

• Service returns business party new data success

• Message bus delivers messages to data Sync Center

• Data Sync Center Inserts T2 data

Advantages :

• Short request processing time (1 inserts only)

Disadvantages :

• The complexity of the system has increased, introducing a component (message bus) and a service (dedicated data replication Service)

• Since the return line of business data is inserted successfully, the data is not necessarily inserted into the T2, so the data has an inconsistent time window (the window is very short and ultimately consistent)

• Redundant table data is inconsistent when message bus loses message

Whether the service synchronizes double-write or service-asynchronous-write, services need to focus on the complexities of "redundant data." If you want to remove the "data redundancy" of the system coupling, lead to a common third scenario.

Four, offline asynchronous double write

In order to shield the complexity of the "redundant data" to the service, the double write of the data is no longer done by the service layer, but by a service or task under the line, such as the 1-6 process:

• Business party invoke service, new data

• Service first inserts T1 data

• Service returns business party new data success

• Data is written to the log in the database

• Offline service or task read log of database

• Offline service or task insert T2 data

Advantages :

• Data double write and business fully decoupled

• Short request processing time (1 inserts only)

Disadvantages :

• Return line of business when the data is inserted successfully, the data is not necessarily inserted into the T2, so the data has an inconsistent time window (the window is very short and ultimately consistent)

• Consistency of data depends on the reliability of offline services or Tasks

Five, summary

Business scenarios with large Internet data volumes are often:

• Use horizontal slicing to reduce the amount of data in a single library

• Use inverse paradigm design of data redundancy to meet query requirements in different dimensions

• Data redundancy can be easily achieved by using service synchronization two-notation

• To reduce the delay, you can optimize the asynchronous double-notation for services

• To shield the complexity of the service from redundant data, it can be optimized for offline asynchronous double-writing

Three scenarios for MySQL redundant data