mysql sharding partition database partition table

After the amount of data in the database reaches a certain level, in order to avoid bottlenecks in system performance. Data needs to be processed by means of partitioning, sharding, databases, and tables.

Recommended courses: MySQL Tutorial.

Sharding (similar to sharding)

Sharding is to scale out the database to multiple physical nodes It is an effective way, and its main purpose is to break through the I/O capacity limitations of a single-node database server and solve the database scalability problem. The word shard means "fragment". If a database is treated as a large piece of glass and the glass is broken, then each small piece is called a fragment of the database (Database Shard). The process of breaking the entire database into pieces is called sharding, which can be translated as sharding.

Formally, sharding can be simply defined as a partitioning scheme that distributes a large database across multiple physical nodes. Each partition contains a certain part of the database, called a slice. The partitioning method can be arbitrary and is not limited to traditional horizontal partitioning and vertical partitioning. A shard can contain the contents of multiple tables or even multiple database instances. Each shard is placed on a database server. A database server can handle one or more shards of data. A server is required in the system for query routing and forwarding, and is responsible for forwarding the query to the shard or shard collection node containing the data accessed by the query for execution.

Scale Out/Scale Up and vertical split/horizontal split

Mysql’s expansion plan includes Scale Out and Scale Up.

Scale Out (horizontal expansion) means that the Application can be expanded in the horizontal direction. Generally speaking, for data center applications, Scale out means that when more machines are added, the application can still make good use of the resources of these machines to improve its own efficiency and achieve good scalability.

Scale Up (vertical expansion) means that the Application can expand in the vertical direction. Generally speaking, for a single machine, Scale Up is worth it. When a computing node (machine) adds more CPU Cores, storage devices, and uses larger memory, the application can make full use of these resources to improve its efficiency. Thus achieving good scalability.

MySql’s Sharding strategy includes vertical sharding and horizontal sharding.

Vertical (vertical) split: refers to splitting by functional modules to solve the io competition between tables. For example, it is divided into order database, product database, user database... In this way, the table structures of multiple databases are different.

Horizontal (horizontal) split: Save the data of the same table in blocks and save it in different databases to solve the pressure of increasing data volume in a single table. The table structures in these databases are exactly the same.

Table structure design is divided vertically. Some common scenarios include

a). Vertical segmentation of large fields. Build large fields separately in another table to improve the access performance of the basic table. In principle, large fields in the database should be avoided in performance-critical applications

b). Split them vertically according to the purpose of use. For example, enterprise material attributes can be vertically segmented according to basic attributes, sales attributes, purchasing attributes, manufacturing attributes, financial accounting attributes, etc.

c). Vertically segmented according to access frequency. For example, in e-commerce and Web 2.0 systems, if there are a lot of user attribute settings, basic, frequently used attributes and infrequently used attributes can be divided vertically and the table structure design can be divided horizontally. Some common scenarios include

a). For example, in an online e-commerce website, the amount of order table data is too large, and it is divided into annual and monthly levels

b). Web 2.0 website registered users, online There are too many active users. According to the user ID range, etc., horizontally segment the relevant users and the tables closely related to the user

c). For example, the top post of the forum, because it involves paging issues, each page It is necessary to display the pinned post. In this case, the pinned post can be divided horizontally to avoid reading from the table of all posts when fetching the pinned post

Tables and partitions

Table splitting superficially means dividing a table into multiple small tables. Partitioning means dividing the data of a table into N blocks. These blocks can be on the same disk or on different disks. on disk.

The difference between table splitting and partitioning

1, in terms of implementation method

mysql’s split table is a real split table. After one table is divided into many tables, Each small table is a complete table and corresponds to three files (MyISAM engine: a .MYD data file, a .MYI index file, and a .frm table structure file).

2. In terms of data processing,

the data is stored in the sub-tables after being divided into tables. The main table is just a shell, and data access occurs in each sub-table. There is no concept of table partitioning in partitioning. Partitioning just divides the file storing data into many small blocks. The partitioned table is still one table, and the data processing is still completed by yourself.

3. Improve performance

After splitting the tables, the concurrency capability of a single table is improved, and the disk I/O performance is also improved. The partition breaks through the disk I/O bottleneck, and I want to improve the read and write capabilities of the disk to increase mysql performance.

At this point, the testing focus of partitions and sub-tables is different. The focus of sub-tables is how to improve mysql concurrency when accessing data; and partitions, how to break through the read and write capabilities of the disk, thereby improving mysql performance. the goal of.

4. Regarding the difficulty of implementation, there are many ways to divide tables. Using merge to divide tables is the simplest way. This method is about as easy as partitioning and can be transparent to the program code. If you use other table partitioning methods, it will be more troublesome than partitioning. The implementation of partitioning is relatively simple. Creating a partitioned table is no different from building an ordinary table, and it is transparent to the code side.

Applicable scenarios of partitioning

1. The query speed of a table has been slow enough to affect its use.

2. The data in the table is segmented

3. The operation of data often only involves part of the data, not all the data

CREATE TABLE sales (

    id INT AUTO_INCREMENT,

    amount DOUBLE NOT NULL,

    order_day DATETIME NOT NULL,

    PRIMARY KEY(id, order_day)

) ENGINE=Innodb

PARTITION BY RANGE(YEAR(order_day)) (

    PARTITION p_2010 VALUES LESS THAN (2010),

    PARTITION p_2011 VALUES LESS THAN (2011),

    PARTITION p_2012 VALUES LESS THAN (2012),

PARTITION p_catchall VALUES LESS THAN MAXVALUE);

Application of sub-tables Scenario

1. The query speed of a table has become so slow that it affects its use.

2. When inserting frequently or jointly querying, the speed becomes slower.

The implementation of sub-tables requires a combination of business implementation and migration, which is relatively complex.

Sub-table and sub-database

Sub-table can solve the problem of reduced query efficiency caused by excessive data volume in a single table, but it cannot improve the concurrency of the database. Processing capabilities bring qualitative improvements. In the face of highly concurrent read and write access, when the database master server cannot bear the pressure of write operations, it is meaningless no matter how to expand the slave server. Therefore, we must change our thinking and split the database to improve the database writing capability. This is the so-called sub-database.

Similar to the table sharding strategy, sharding can use a keyword modulo to route data access.

The above is the detailed content of mysql sharding partition database partition table. For more information, please follow other related articles on the PHP Chinese website!