Database reading and writing separation and vertical sub-Library, horizontal sub-table

database reading and writing separation and vertical sub-Library, horizontal sub-table

As the business of a website expands, data increases, the pressure on the database becomes larger, and the basic optimization of the database or SQL may not be the ultimate result, we can use a read-write separation strategy to change the status quo. Read-write separations are now heavily used in many large Web sites, and this technology is not surprising. ebay is doing very well. ebay uses Oracle, and is heard to use Quest Share Plex for master-slave replication data.

Read-write separation is simply the database read and write operations to separate the corresponding database server, so as to effectively reduce the database pressure, but also to reduce the IO pressure. The main database provides write operations, from the database to provide read operations, in fact, in many systems, mainly read operations. When the primary database is written, the data is synchronized to the database from which the database integrity can be effectively guaranteed. Quest Shareplex is a comparison of cattle synchronization data tools, heard than the flow of Oracle itself is good, MySQL also has its own synchronous data technology. MySQL replicates data as long as it is binary log. Replication data is achieved through the logging of duplicate primary databases from the database. The good thing about this replication is that the data is synchronized to the database from the asynchronous method.

After the primary database is synchronized to the slave database, it is generally composed of multiple databases from the database in order to alleviate the stress. How the read operation is assigned to the from database. The read operation should be assigned to the server based on the pressure of the server, rather than a simple random allocation. MySQL provides mysql-proxy for read and write separation operations. But Mysql-proxy seems to be a long time not updated. Oracle can efficiently allocate pressure to read from the database through F5.

ebay's Read and write separation (found on the internet to use it)

MySQL read-write separation
The above-mentioned database synchronous replication, all in the same database, if I want to synchronize Oracle data to MySQL, in fact, the reason to implement this scenario is very simple, MySQL free, Oracle is too expensive. As if quest Shareplex can not realize the change function. It seems that there is no such tool in the market now. That's how data synchronization should be achieved. In fact, we can consider ourselves to develop a set of synchronous data components, through the message to achieve asynchronous replication data. In fact, this implementation to consider many aspects of the problem, high concurrency problems, failure records and so on. In fact, this method can also synchronize data to Memcache. I heard that Oracle's stream can be implemented, but I haven't tried it.

---------------------Gorgeous split-line--------------------------------------------------------------

Vertical sub-Library, horizontal sub-table

The horizontal division of the database and vertical division has long been in contact, but no practice, no experience, only the last two years have contact, today also talk with you.

Vertical Division

Divide the data into different databases and servers according to the function.

When a Web site started to be created, it might just consider a day only dozens of or hundreds of people access, the database may be a db, all the tables are put together, a common server may be enough, and the developers are very happy, and confident, because all the tables are in a library, So the query statement can be casually related, how beautiful a thing. But with the increase in access pressure, read and write operations continue to increase, the database pressure is definitely getting bigger, possibly close to the limit, when people may think of increasing from the server, do what cluster, but the problem comes again, the data volume is also growing rapidly.

You can then consider separating the read and write operations and putting different data into different libraries according to the business. In fact, in a large and bloated database of data between tables and tables is not related to many, or more do not need (join) operation, in theory, they should be placed on separate servers. For example, the user's favorite data and the blog's database can be placed on two separate servers. This is called vertical partitioning (which is actually called unimportant).

When the data of the blog or collection is constantly increasing, what should be done, this leads to another approach, called Horizontal division.

Horizontal Division

The data of a table is divided into different databases, and the table structure of two databases is the same. How to divide, according to certain rules, can be based on the data of the creator to do the guidance, the above data is generated by people, can be based on the ID of the person to divide the database. Then according to a certain rule, first know the data in which database.

In fact, many large sites have experienced the vertical division of the database and the level of the division of the stage. In fact this can be based on experience to determine, not necessarily by some hard rules.

As an example of the blog just now, data can be classified according to the parity of the UserID. Put the ID of the base to a library, for even the B-Library.

This allows you to know which database the user's blog data is in by using UserID. Can actually be handled according to USERID%10. can also be processed according to the famous hash algorithm.

The architecture of the mobile home was found to be:

Horizontal slicing: Split the data horizontally.

A. It is best to share the same database.

B. A programme that has proven to be feasible: Master Table + Auxiliary table.

C. There are 3 types: the main table is not broken, the main table is broken without auxiliary table, the main table is beaten with auxiliary table.

D. But for programmers, TA sees only a table, which may be called a virtual table (logical table)? , this virtual table may actually consist of N-sheet (physical table).