MySQL Sub-Library sub-table scheme

MySQL Sub-Library sub-table scheme

1. Why Divide the table:

When the data of a table reaches tens of millions of, the time you spend querying is much more, and if you have a joint query, I think I might die there. The purpose of the sub-table is to reduce the burden on the database and shorten the query time.

one mechanism in MySQL is table locking and row locking to ensure the integrity of the data . Table locking means that you can't operate on this table, and you have to wait until I finish working on the table. Row locking is the same, other SQL must wait until I'm done with this data before I can manipulate this piece of data.

2. mysql Proxy:amoeba

Do MySQL cluster , using amoeba.

From the upper Java program, you do not need to know the primary server and the source from the server, that is, the master-slave database server is transparent to the upper level. Can be configured via Amoeba .

3. Large data volumes and frequently accessed tables, divided into several tables

For example, the database table of a website platform - table, this n How much, according to the actual situation.

      The current amount of data for a website is at most 5000 million, you can design the amount of data that each table holds 500 million, that is, split into 10

So how can you tell if a table's data is full? Can be in the program section for the table to add data, before inserting the number of statistical records of the operation, when <500 data, directly inserted, when the threshold has been reached, you can create a new database table in the program segment (or have been created beforehand), and then perform the insert operation.

4. Using the merge storage Engine to implement the sub-table

If you want to separate the existing big data scale is painful, the most painful thing is to change the code, because the SQL statement inside the program has been written . Using the merge storage engine to implement the sub-table , this method is more suitable.

Example:

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

Database Schema

1, simple MySQL master-slave replication:

MySQL's master-slave replication solves the database read-write separation, and a good performance to improve read, the figure is as follows:

The process of its master-slave replication is as follows:

However, master-slave replication also brings a number of other performance bottlenecks:

1. Write cannot be extended

2. Write cannot be cached

3. Replication delay

4. The lock example rises

5. Table becomes larger, cache rate drops

The problem has to be solved, which results in the following optimization scheme, together to see.

2. mysql vertical partition

If the business is cut enough to be independent, it will be a good idea to put different business data into different database servers, and in case one of the services crashes, it will not affect the normal operation of other business, and also play a role of load shunt, greatly improving the throughput of the database. The database schema diagram after vertical partitioning is as follows:

However, although the business is already independent enough, but some of the business is more or less connected to each other, such as the user, basically will be associated with each business, and this partitioning method does not solve the problem of the single-sheet data explosion, so why not try to split horizontally?

3. mysql level shard (sharding)

This is a very good idea, the user according to a certain rule (by ID hash) group, and the group of users of the data stored in a database shard, that is, a sharding, so as the number of users increased, as long as the simple configuration of a server, the schematic is as follows:

How to determine the Shard of a user, you can build a user and shard corresponding data table, each request first from this table to find the user's Shard ID, and then from the corresponding shard query the relevant data, as shown in:

MySQL Sub-Library sub-table scheme