A brief analysis of the differences between mysql table and partition

When the amount of data in the database reaches a certain level, it can avoid the bottleneck of system performance. Data processing is required by means of partition, partition, library and table.

1. What are mysql sub-tables and partitions

What is a sub-table, ostensibly, a table divided into N tables

What is partitioning, which is dividing the data of a table into N blocks, which can be on the same disk or on different disks

2. What's the difference between mysql and partition

1. Implementation method

mysql's sub-table is a real sub-table, after a table is divided into many tables, each small table is a positive table, corresponding to 3 files, 1.MYD data file,.MYI index file,.frm table structure file.

[

[root@BlackGhost test]# ls |grep user
alluser.MRG
alluser.frm
user1.MYD
user1.MYI
user1.frm
user2.MYD
user2.MYI
user2.frm

]

1, the above table is the use of merge storage engine (one of the table), alluser is the master table, there are two table, user1, user2. Both of them are independent tables. When fetching data, we can fetch it through the master table. There are no.MYD and.MYI files in the master table. That is to say, the master table is not a single table. So let's see. What is MRG exactly

[

[root@BlackGhost test]# cat alluser.MRG |more
user1
user2
#INSERT_METHOD=LAST

]

As we can see from the above, alluser.MRG contains the relationship between some sub-tables and the way to insert data. You can think of the master table as a shell or as a join pool.

b) partition is not the same. After partitioning a large table, it still has one table and will not turn into two tables. However, it has more blocks to store data.

[

[root@BlackGhost test]# ls |grep aa
aa#P#p1.MYD
aa#P#p1.MYI
aa#P#p3.MYD
aa#P#p3.MYI
aa.frm
aa.par

]

As can be seen from the above table, aa is divided into two sections, p1 and p3. There were originally three sections, but I deleted one. We all know that one table corresponds to three documents.MYD,.MYI,.frm. Partition according to the rules set by 1 data files and index files are divided, there is an additional.par file, open the.par file and you can see that he recorded the partition information of this table, the.MRG in the root table is a bit like. After partitioning, you still have one table instead of multiple tables.

2. Data processing

a) after the sub-table, the data is stored in the sub-table, the master table is just a shell, access data in the sub-table 1. Take a look at this example:

select * from alluser where ='12' On the surface, where es1101en ='12' on the surface, but it is not. Is to alluser in the sub-table for the operation.

b) partition, there is no partition table concept, partition just store data files into many small pieces, partition after the table, or a table. The data processing is still done on its own.

3. Improve performance

a), single table concurrency improved and disk I/O performance improved. The reason why concurrency is improved is because it takes less time to perform one lookup, and if high concurrency occurs, the master table can divide concurrency pressure into smaller tables depending on the query. Disk I/O performance how high, originally a very large.MYD file is now spread out into the small table.MYD.

b) mysql put forward the concept of partition, I think I want to break the bottleneck of I/O, to improve the disk reading and writing ability, to increase the performance of mysql.
At this point, the test emphasis of partition and sub-table is different. The sub-table focuses on how to improve the concurrency ability of mysql when accessing data. Partitioning, on the other hand, can improve mysql performance by breaking disk read and write.

4. The difficulty of implementation

a) there are many ways to divide tables. Using merge to divide tables is the simplest way. This way, the root partition is equally difficult and transparent to the program code. If it is to use other sub-table on the score area trouble.

b) partition implementation is relatively simple, it makes no difference to build partition tables, root normal tables, and is transparent to the open code side.

3. What is the relationship between mysql and partitioning

1. Both can improve the high sex of mysql and have a good surface in high concurrency state.

2, table and partition is not contradictory, can cooperate with each other, for those big traffic, and more table data table, we can adopt the method of partition table and combined (if merge table in this way, can't and partition, you can try other table), the traffic is not big, but many of the table data table, we can adopt the method of partition, etc.