I. Preface
After the data volume of the database reaches a certain level, in order to avoid bringing bottlenecks in system performance. The need for data processing, the means used is partitioning, slicing, library, table.
II. Segmentation (similar to sub-collections)
Sharding is an effective way to horizontally scale out a database to multiple physical nodes, the main purpose of which is to break through the limitations of the I/O capacity of a single-node database server and solve database scalability problems.The word "shard" means "shard." If you think of a database as a large piece of glass and break the glass, each small piece is called a DatabaseShard. If a database is treated as a large piece of glass and the glass is broken, then each small piece is called a DatabaseShard. 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 over multiple physical nodes. Each partition contains a portion of the database, called a slice, which can be partitioned in any way and is not limited to traditional horizontal and vertical partitioning. A slice can contain the contents of multiple tables or even the contents of multiple database instances. Each slice is placed on a database server. A database server can process data from one or more slices. The system requires a server for query routing and forwarding, which is responsible for forwarding the query to the shard or shard collection node that contains the data accessed by the query for execution.
Three,Scale Out/Scale Up and Vertical Split/Horizontal Split
Mysql's scaling options include Scale Out and Scale Up.
Scale Out means that the application can scale out horizontally. Generally 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 their efficiency and thus achieve good scalability.
Scale Up means that the Application can scale up vertically. Generally for a single machine, Scale Up is worthwhile because when a compute node (machine) adds more CPU Cores, storage devices, and uses more memory, the application can fully utilize these resources to improve its efficiency and thus achieve good scalability.
MySql's sharding strategy includes both vertical and horizontal slicing.
Vertical (Vertical) Split: It refers to splitting by functional modules to solve io competition between tables. For example, it is divided into order library, product library, user library... This way multiple databases have different table structures between them.
Horizontal (horizontal) splitting: chunking the data from the same table into different databases to address the pressure that occurs when the amount of data in a single table grows. The table structures in these databases are identical.
The table structure is designed to be vertically sliced. Some common scenarios include
a). Vertical slicing of large fields. Separate large fields in a separate table to improve the access performance of the base table, in principle, large fields in the database should be avoided in performance-critical applications.
b). Vertical slicing according to usage. For example, enterprise material attributes can be vertically sliced according to the use of basic attributes, sales attributes, purchasing attributes, manufacturing attributes, financial accounting attributes, etc.
c). Vertical slicing according to access frequency. For example, in e-commerce and Web 2.0 systems, if there are many user attributes, you can vertically slice and dice the basic, frequently used attributes and the infrequently used attributes.
Table structure design horizontal slicing. Some common scenarios include
a). For example, an online e-commerce site with an excessive amount of order form data, sliced at annual and monthly levels
b). Web 2.0 websites have too many registered users and online active users, and the relevant users and the tables closely associated with the users are horizontally sliced by user ID ranges, etc.
c). For example, the top post of the forum, because it involves paging issues, each page needs to display the top post, this case can be the top post level cut apart, to avoid taking the top post when reading from the table of all posts
IV. Tables and partitions
While table partitioning superficially means dividing a table into multiple smaller tables, partitioning is the process of dividing a table's data into n number of blocks, which can be on the same disk or on different disks.
The Difference Between Split Tables and Partitions
1, the realization of the way
mysql's split table is a real split table, a table is divided into many tables, each small table is a complete table, all correspond to three files (MyISAM engine: a .MYD data file, .MYI index file, .frm table structure file).
2, on data processing
After the table data are stored in the sub-table, the total table is just a shell, access to data occurs in a sub-table inside. Partitioning does not exist in the concept of sub-table, partitioning is only the data stored in the file is divided into a number of small pieces, after partitioning the table is still a table, data processing is still done by themselves.
3, improve performance on
After partitioning, the concurrency of a single table is increased and the disk I/O performance is increased. Partitioning breaks through the disk I/O bottleneck and wants to increase the read and write capacity of the disk to increase mysql performance.
At this point, partitioning and table measurement focus is different, table focus is to access the data, how to improve mysql concurrency; and partitioning, how to break through the disk's read and write capabilities, so as to achieve the purpose of improving mysql performance.
4, on the ease of realization
There are many ways to partition a table, and using merge to partition a table is one of the easiest ways. This way and partitioning is about the same level of difficulty, and can be done transparently to the program code. If you are using other ways to divide the table than the partition trouble. Partitioning is relatively simple to realize, the establishment of partitioned tables, with the construction of the usual table is no difference, and the code side of the transparent.
Scenarios for partitioning
1. When the query speed of a table has slowed down to the point where it is affecting its use.
2. The data in the table is segmented
3. Manipulation of data often involves only a portion of the data, not all of it
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);
Scenarios for Split Tables
1. When the query speed of a table has slowed down to the point where it is affecting its use.
2. When frequent inserts or joint queries, the speed becomes slower.
The implementation of split tables requires a business combination of implementation and migration, which is more complex.
V. Sub-tables and sub-collections
Sub-table can solve the problem of decreased query efficiency brought about by the large amount of data in a single table, but it can't bring qualitative improvement to the concurrent processing capability of the database. In the face of highly concurrent read and write access, when the database master server can not carry the pressure of write operations, no matter how to expand the slave server, at this point there is no meaning. Therefore, we must change a way of thinking, the database is split, thus improving the database write capability, which is known as sub-database.
Similar to the split table strategy, split libraries can be used to route data access through a keyword fetch model, as shown below
VI. Differences between partitioning and slicing
This article on the MySql table, library, sharding and partitioning knowledge in-depth explanation of the article is introduced to this, more related to MySql table, library, sharding, partitioning content, please search for my previous articles or continue to browse the following related articles I hope that you will support me in the future more!