"Database" differentiated table, read-Write separation __ Database

 "database" differentiated table, read-write separation first, what is the partition, the table, the sub-Library Partitioning

is to divide the data of a table into n blocks, which logically ultimately is only a table, but the bottom is a table composed of n physical blocks.

is to divide a table into n a table of entities with separate storage space according to certain rules. When the system reads and writes, it needs to get the corresponding word according to the defined rules and then manipulate it. Sub-Library

Once the table is divided, more and more tables will be in one library

Compare the whole database to a library, and a table is a book. If you want to find something in a book, the search efficiency will decrease if you do not divide the chapters. In the same way, partitions are in the database. second, the bottleneck of the common stand-alone database problem Description The greater the amount of single table data, the lower the efficiency of the insert operation to re-establish indexing. A single library has too much data (one database data to 1t-2t is the limit) a single database server is experiencing a bottleneck (concurrent volume hundreds of) when it is too high in read and write speed. Partition is considered when zoning is used.

The query speed of a table is slow enough to affect the use of the time.

SQL optimized

Data in a large table of data is segmented

Operations on data often involve only a subset of the data, not the problem of all data partitioning

The main way to improve the query efficiency of the implementation of the partition (simple)

MYSQL5 starts to support partition functionality

CREATE TABLE sales (
    ID INT auto_increment,
    amount DOUBLE NOT null,
    order_day DATETIME not NULL,
    PRIMARY K EY (ID, order_day)
) Engine=innodb 
PARTITION by RANGE (year (Order_day)) (
    PARTITION p_2010 VALUES less THAN ( ),
    PARTITION p_2011 values less THAN (a), PARTITION p_2012
    values less THAN (a),
PARTITION P_catcha ll VALUES less THAN MAXVALUE);

Iv. Sub-tables when to consider the sub-table.

The query speed of a table is slow enough to affect the use of the time.

SQL optimized for large amounts of data

When frequent inserts or joint queries, the problem of the slow table resolution

After the table, the ability to increase the concurrency of a single table disk I/O performance increased, write efficiency improved query time short data distribution in different files, disk I/O performance improve read and write lock impact of data quantitative small insert the data that needs to be indexed to the database to reduce the way the table is implemented (complex)

Need business system to cooperate with migration and upgrade, the difference and relation of workload partition and table

The purpose of partitioning and dividing table is to reduce the burden of the database, and to improve the efficiency of table deletion and retrieval. Partitioning is just a change in the storage location of data in a table, which divides a table into multiple tables. When the volume of traffic is large and the table data is large, the two ways can be used with each other.

When the amount of traffic is small, but the table data is relatively large, you can only partition. rule Policies for common classification tables (similar) Range (range) hash (hash) after the time to split the hash according to the number of minutes in the authentication library to save the database configuration, is to establish a DB, this db to save the user_id to the DB mapping relationship Five, when the sub-library to consider the use of the sub-Library. The storage space of single db is not enough with the increase of query quantity the single database server has no way to support the problem of the library solution

The main purpose of this paper is to break the I/O capability limit of single node database server and solve the problem of database extensibility. Split vertically

Tables that do not have an association in the system or need to join can be placed on different servers in different databases.

Vertically divided by business. For example: According to the business can be divided into funds, members, orders three database.

Issues to be addressed: cross-database transactions, Jion queries, and more. Horizontal Split

For example, most of the sites. Data is related to the user, you can divide the data according to the user level.

According to the rules, the general horizontal library is after the vertical library. For example, the number of orders processed every day is massive, can be divided according to a certain level of rules. Issues to be addressed: Data routing, assembly. Read and write separation

For time-sensitive data, the database pressure can be alleviated through read-write separation. The problem to be solved is to differentiate in business what business is to allow a certain amount of time delay, and data synchronization issues. train of Thought

Vertical sub-Library--> horizontal--> read and write separation six, split, and then faced with the new problem of the support of the transaction, the sub-table, it becomes a distributed transaction join the cross-Library, cross-table problem of the sub-Library, read-write separation using the distributed, distributed in order to ensure strong consistency, the inevitable delay, resulting in lower performance, the complexity of the system has become higher. Common solutions:

There are no strict boundaries between the different ways, the characteristics are different, the emphasis is different. According to the actual situation, combined with the characteristics of each of the methods to deal with.

Third-party database middleware (atlas,mycat,tddl,drds) is selected, while the business system needs to be upgraded with data storage. vii. Evolution of data storage single-Library single table

A library single table is the most common database design, for example, a user table is placed in database db, and all users can find it in the user table in the DB Library. Single Library multiple tables

As the number of users increases, the amount of data in the user table becomes larger, and when the amount of data reaches a certain level, the query to the user table slowly slows down, affecting the performance of the entire DB. If you use MySQL, there is a more serious problem is that when you need to add a column, MySQL locks the table, all the read and write operations can only wait.

Can be in some way the user to the level of segmentation, resulting in two table structure exactly the same user_0000,user_0001 table, user_0000 + user_0001 + ... The data is just a complete piece of data. Multi- Library multiple tables

As the amount of data increased, perhaps a single db storage space is not enough, as the query volume increased single database server has no way to support. You can then split the database horizontally at this time. Viii. Summary

In general, partitioning is preferred. When the partition does not meet the requirements, start to consider the table, a reasonable division of efficiency will be better than the partition.