Differences between Mysql sub-tables and partitions _ MySQL

Differences between Mysql sub-tables and partitions bitsCN.com

I. What are mysql table shards and partitions?

What is table sharding? on the surface, it means dividing a table into N small tables. for details, see the three methods of mysql table sharding.

What is a partition? partition refers to dividing data in a table into N blocks, which can be on the same disk or on different disks, for more information, see mysql partition function and instance

II. what is the difference between mysql sub-tables and partitions?

1. implementation method

A) mysql sub-tables are real sub-tables. after a table is divided into many tables, each small table is a positive table, which corresponds to three files. MYD data file ,. MYI index file ,. frm table structure file.

SQL code

1. [Root @ BlackGhost test] # ls | grep user
2. Alluser. MRG
3. Alluser. frm
4. User1.MYD
5. User1.MYI
6. User1.frm
7. User2.MYD
8. User2.MYI
9. User2.frm

The preceding table sharding uses the merge storage engine (one for table sharding). alluser is a summary table. There are two table shards, user1 and user2. Both of them are independent tables. we can retrieve data from the total table. There are no. MYD and. MYI files in the summary table. that is to say, the summary table is not a table and there is no data, and the data is stored in the sub-tables. Let's see what MRG is.

SQL code

1. [Root @ BlackGhost test] # cat alluser. MRG | more
2. User1
3. User2
4. # INSERT_METHOD = LAST

From the above we can see that alluser. MRG has some table sharding relationships and data insertion methods. The table can be understood as a shell or a connection pool.

B) the partitions are different. after a large table is partitioned, it is still a table and will not be changed to two tables, but the data storage blocks become more.

SQL code

1. [Root @ BlackGhost test] # ls | grep aa
2. Aa # P # p1.MYD
3. Aa # P # p1.MYI
4. Aa # P # p3.MYD
5. Aa # P # p3.MYI
6. Aa. frm
7. Aa. par

We can see from the above that the aa table is divided into two partitions, p1 and p3, which are originally three partitions and are deleted by me. We all know that a table corresponds to three files. MYD,. MYI,. frm. Partitions split data files and index files according to certain rules. open the par file. after the par file, you can see that the partition information of this table is in the root table. MRG is a bit like. After partitioning, it is still one, rather than multiple tables.

2. Data processing

A) after table sharding, the data is stored in the table sharding table. The total table is only a shell. the accessed data is stored in one table shard. See the following example:

Select * from alluser where id = '12' on the surface, operations are performed on the table alluser, but they are not. Is to operate the table shards in alluser.

B) partition. there is no table sharding concept. partitions only divide the files that store data into many small pieces. the partitioned table is still a table. Data processing is done by yourself.

3. improve performance

A) after table sharding, the single-table concurrency capability is improved, and the disk I/O performance is also improved. Why is the concurrency improved? because it takes a short time to query a table. if there is a high concurrency, the total table can allocate the concurrency pressure to different small tables based on different queries. How can I achieve high disk I/O performance? a very large. MYD file is now allocated to. MYD in each small table.

B) mysql proposed the partition concept. I think I want to break through the disk I/O bottleneck and improve the disk read/write capability to increase mysql Performance.
At this point, the testing focus of partitions and table Shards is different. the focus of table Shards is on how to improve mysql's concurrency when accessing data. how can partitions break through disk read/write capabilities, to improve mysql Performance.

4 ).

A) there are many table sharding methods. using merge for table Sharding is the simplest method. In this way, the root partition is almost easy and transparent to the program code. If other table sharding methods are used, the score zone is troublesome.

B) partition implementation is relatively simple. there is no difference between creating a partition table and creating a common root table, and it is transparent to the code.

III. what is the relationship between mysql table shards and partitions?

1. both of them can improve mysql's high performance and have a good performance in high concurrency.

2. there is no conflict between table shards and partitions. you can work with each other. for tables with large traffic volumes and large table data volumes, we can combine table shards and partitions (if the merge table sharding method cannot be used with partitions, we can try other table shards). tables with low access traffic but with a lot of table data, we can adopt the partitioning method.

BitsCN.com