In-depth explanation of MySql table, database, sharding and partitioning knowledge

1. Introduction

When the amount of data in the database reaches a certain level, it is necessary to avoid bottlenecks in system performance. Data processing is required, and the methods used are partitioning, sharding, database sharding, and table sharding.

2. Sharding (similar to database sharding)

Sharding is an effective way to scale out a database to multiple physical nodes. Its main purpose is to break through the I/O capacity limitations of a single-node database server and solve database scalability issues. The word Shard means "piece". If you regard a database as a large piece of glass and break it, each small piece is called a database shard. The process of breaking up the entire database 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 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 the contents of multiple database instances. Each shard is placed on a database server. A database server can process data from one or more shards. The system needs a server to perform query routing and forwarding, which is responsible for forwarding the query to the shard or shard collection node containing the data accessed by the query for execution.

3. Scale Out/Scale Up and Vertical/Horizontal Split

There are two expansion solutions for MySQL: Scale Out and Scale Up.

Scale Out means that the application can be expanded horizontally. 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 efficiency and achieve good scalability.

Scale Up means that the application can be expanded in the vertical direction. Generally speaking, for a single machine, Scale Up means that 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 and achieve good scalability.

MySql's Sharding strategies include vertical sharding and horizontal sharding.

Vertical splitting: refers to splitting by functional modules to resolve IO competition between tables. For example, it can be divided into order library, product library, user library... In this way, the table structures of multiple databases are different.

Horizontal splitting: Data from the same table is divided into blocks and saved in different databases to solve the pressure caused by the growth of data volume in a single table. The table structures in these databases are exactly the same.

The table structure design is vertically split. Some common scenarios include

a). Vertical segmentation of large fields. Create large fields 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). Vertical division according to usage. For example, the material attributes of an enterprise can be vertically divided according to basic attributes, sales attributes, procurement attributes, production and manufacturing attributes, financial accounting attributes, etc.

c). Vertical segmentation based on access frequency. For example, in e-commerce and Web 2.0 systems, if there are many user attributes, basic, frequently used attributes and infrequently used attributes can be vertically separated.

The table structure design is horizontally split. Some common scenarios include

a). For example, if the order table of an online e-commerce website has a large amount of data, it is necessary to divide it into annual and monthly levels.

b). Web 2.0 websites have too many registered users and online active users. The relevant users and the tables closely related to the users are horizontally split according to the user ID range.

c). For example, the top posts of a forum involve paging issues, and each page needs to display the top posts. In this case, the top posts can be split horizontally to avoid reading from the table of all posts when retrieving the top posts.

4. Table and partition

The superficial meaning of table sharding is to divide a table into multiple small tables, and partitioning is to divide the data of a table into N multiple blocks. These blocks can be on the same disk or on different disks.

The difference between table sharding and partitioning

1. Implementation

MySQL's table sharding is real table sharding. After a table is divided into many tables, each small table is a complete table, corresponding to three files (MyISAM engine: a .MYD data file, a .MYI index file, and a .frm table structure file).

2. Data processing

After the tables are divided, the data are stored in the sub-tables. The main table is just a shell, and data storage and access occur in each sub-table. There is no concept of splitting tables in partitioning. Partitioning only divides the files storing data into many small blocks. The partitioned table is still a table, and data processing is still done by oneself.

3. Improve performance

After the table is split, the concurrency capability of a single table is improved, and the disk I/O performance is also improved. Partitioning breaks through the disk I/O bottleneck and aims to improve the disk's read and write capabilities to increase MySQL performance.

At this point, the focus of partitioning and table sharding is different. The focus of table sharding is on how to improve the concurrency of MySQL when accessing data; while the focus of partitioning is on how to break through the read and write capabilities of the disk to achieve the purpose of improving MySQL performance.

4. Difficulty of implementation

There are many ways to split a table, and using merge is the simplest way. This approach is about the same difficulty as partitioning and can be transparent to program code. If you use other table partitioning methods, it will be more troublesome than partitioning. Partitioning is relatively simple to implement. Creating a partitioned table is no different from creating a normal table, and it is transparent to the code side.

Applicable scenarios for partitions

1. The query speed of a table is so slow that it affects its usage.

2. The data in the table is segmented

3. Operations on data often only involve part of the data, not all of 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);

Applicable scenarios of sub-tables

1. The query speed of a table is so slow that it affects its usage.

2. When frequent insertion or joint query occurs, the speed becomes slower.

The implementation of sharding requires business integration and migration, which is relatively complex.

5. Table and database division

Splitting tables can solve the problem of decreased query efficiency caused by excessive data volume in a single table, but it cannot bring about a qualitative improvement in the concurrent processing capabilities of the database. In the face of high-concurrency read and write access, when the database master server cannot bear the pressure of write operations, no matter how the slave servers are expanded, it is meaningless at this time. Therefore, we must change our thinking and split the database to improve the database writing capability, which is called database sharding.

Similar to the table sharding strategy, database sharding can use a keyword modulo method to route data access, as shown in the following figure

6. Differences between partitioning and sharding

This concludes this article on the in-depth and detailed knowledge of MySql table sharding, database sharding, sharding and partitioning. For more relevant MySql table sharding, database sharding, sharding and partitioning content, please search for previous articles on 123WORDPRESS.COM or continue to browse the related articles below. I hope everyone will support 123WORDPRESS.COM in the future!