Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "sub-table, sub-library, fragment and partition analysis in MySql". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

I. Preface

After the amount of data in the database reaches a certain extent, in order to avoid the bottleneck of system performance. Needs to carry on the data processing, uses the means is the partition, the slice, the sub-library, the sub-table.

2. Slicing (similar to sub-library)

Slicing is an effective way to Scale Out the database to multiple physical nodes. Its main purpose is to break through the limit of the ability of single-node database server and solve the problem of database scalability. The word Shard means "fragments". If you treat a database as a large piece of glass and break it, then each small piece is called a DatabaseShard of the database. The process of smashing the entire database is called shredding, which can be translated into shards.

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

III. Scale Out/Scale Up and vertical split / horizontal split

The expansion scheme of Mysql includes Scale Out and Scale Up.

Scale Out (scale-out) means that Application can scale horizontally. Generally speaking, for data center applications, Scale out means that when more machines are added, applications can still make good use of the resources of these machines to improve their efficiency and achieve good scalability.

Scale Up (vertical expansion) means that Application can be expanded vertically. Generally speaking, for a single machine, Scale Up is worth it when a computing node (machine) adds more CPU Cores, storage devices, and uses more memory, the application can make full use of these resources to improve its efficiency and achieve good scalability.

The Sharding strategy of MySql includes vertical segmentation and horizontal segmentation.

Vertical (vertical) split: split by functional module to resolve io competition between tables. For example, it is divided into order library, commodity library and user library. In this way, the table structure of multiple databases is different.

Horizontal (horizontal) split: data from the same table is chunked and saved to different databases to solve the pressure of data growth in a single table. The table structure in these databases is exactly the same.

The table structure is designed for vertical segmentation. Some common scenarios include

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 of the database should be avoided in performance-critical applications.

Divide vertically according to the purpose of use. For example, enterprise material attributes can be vertically segmented according to basic attributes, sales attributes, procurement attributes, manufacturing attributes, financial and accounting attributes, etc.

Split vertically according to the access frequency. For example, in e-commerce and Web 2.0 systems, if there are a lot of user attribute settings, the basic and frequently used attributes can be vertically separated from the less commonly used ones.

The design of table structure is divided horizontally. Some common scenarios include

For example, online e-commerce sites, the order table data is too large, according to the annual and monthly level of segmentation

Web 2.0 website has too many registered users and online active users. According to the user's ID scope, the relevant users and the closely related tables of the user are divided horizontally.

For example, the top post of the forum needs to be displayed on each page because it involves paging, which can split the top post horizontally and avoid reading it from the table of all posts when taking the top post.

IV. Subtables and partitions

On the surface, subtable is to divide a table into multiple small tables, while partitioning is to divide the data of a table into N blocks, which can be on the same disk or on different disks.

The difference between subtables and partitions

In the way of realization

The sub-table of mysql is a real sub-table. 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, .MYI index file, .frm table structure file).

In data processing

After the sub-table, the data is stored in the sub-table, the total table is just a shell, and the access to the data occurs in a sub-table. Partition does not have the concept of sub-table, partition only divides the file storing data into many small blocks, the partitioned table is still a table, and the data processing is done by itself.

To improve the performance

After dividing the table, the concurrency ability of the single table is improved, and so is the performance of the disk Imax O. The partition breaks through the bottleneck of disk I _ hand O and wants to improve the read and write ability of the disk to increase the performance of mysql.

At this point, the testing focus of partition and sub-table is different, the focus of sub-table is how to improve the concurrency ability of mysql when accessing data, while partition, how to break through the read and write ability of disk, so as to achieve the purpose of improving mysql performance.

In terms of the difficulty of realization

There are many ways to divide a table, and using merge to divide a table is the simplest way. This approach is similar to the difficulty of partitioning and is transparent to the program code. If you use other sub-table methods, it will be more troublesome than partitioning. Partitioning implementation is relatively simple, creating a partition table is no different from building a normal table, and is transparent to the code side.

Applicable scenarios for zoning

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

The data in the table is segmented

The operation of data often involves only 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=InnodbPARTITION BY RANGE (YEAR (order_day)) (PARTITION packs 2010 VALUES LESS THAN (2010), PARTITION packs 2011 VALUES LESS THAN (2011), PARTITION packs 2012 VALUES LESS THAN (2012), PARTITION p_catchall VALUES LESS THAN MAXVALUE); scenarios applicable to sub-tables

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

When frequently inserting or federating queries, the speed slows down.

The implementation of sub-table requires business combination implementation and migration, which is more complex.

V. Sub-table and sub-library

Sub-table can solve the problem of the decline of query efficiency caused by the excessive amount of data in a single table, but it can not improve the concurrent processing ability of the database. In the face of highly concurrent read and write access, when the database master server cannot bear the pressure of write operations, no matter how to expand the slave server, it makes no sense at this time. Therefore, we must change a way of thinking, split the database, so as to improve the database writing ability, which is the so-called sub-database.

Similar to the sub-table strategy, the sub-library can route data access through a keyword module, as shown in the following figure

VI. The difference between zoning and slicing

This is the end of the analysis of subtables, libraries, fragments and partitions in MySql. Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.