Database (vii), read/write separated to CQRS

Read/write separation

When a company's business continues to expand, the number of users increased, the original use of the database is likely to be unable to survive. Then you can

• Scale-in, expanding the performance of the hardware, but it is likely that the user volume continues to grow, and the increased performance is quickly eaten up.

• Read and write separation: The database cannot hold up, is nothing more than read and write volume, especially some complex queries such as the last 24 hours of the hottest products. Requires very complex SQL statements, which are of course slow to run.

But in order to read and write separation, the database needs to be split into master library and slave Library,

The major relational databases on the market support data replication , so you can split a database into master and slave two roles, and write operations on the master, which is synchronized with the other slave servers.

Offline operations such as read operations and data analysis are performed on the slave server.

We know that many applications of the Internet are read, so that multiple slave can be load-shared , and the availability and correctness of the data can be guaranteed.

But the corresponding original application code also needs to be modified, must be changed to write data with the master library, read the data when using the slave library, it is equivalent to rewrite.

Complex queries

But even if the code is rewritten, the performance can not be significantly improved, the reason is still the use of too many complex queries, for example, there are many tables in the join, in the database (iv), the database components we have said that the connection is very consumption performance.

Then can we use a single table to store the last 24 hours of popular products Ah, so only need to use simple SQL to get it done.

In other words, a single set of database tables is inappropriate for different behaviors such as reports, searches, transactions, and so on.

Today's tables are designed to add and modify data , and are not suitable for complex queries.

But we also need to consider how this query library is updated, or whether it may not be updated in real time, and we can tolerate this delay.

CQRS

The question of whether or not to endure the delay needs to be seen in business, such as the last 24 hours of popular Acura, a little outdated information does not have much impact, only the final agreement is required.

We can use CQRS (command query Responsibility segregation), that is, adding and deleting commands and query responsibility separation .

In CQRS, it is emphasized that the read (Query) write (Command) is separated, because the data that the user reads is usually out of date, so why do you need to read it again from the database and create a read data source directly. It can be a cache, XML, JSON, and so on.

How to solve the problem of how to update before? You can use the event, which is an incident, such as a product sold, you can publish an event, modify the original read Model.

In this way, synchronization becomes asynchronous through the event mechanism.

Finally, this method is best used only in complex queries , the original simple query is still in the relational database to take. Why is it? Because the introduction of a new technology to pay a price, such as synchronous and asynchronous, but also the event mechanism, we can not only see the advantages of new technologies, and see no shortcomings.

Main references

How does Zhang solve performance problems from read-write separation to CQRS?

Database (vii), read/write separated to CQRS