Two MySQL partitions

The importance of MySQL partitions in MySQL databases is self-evident. So what is MySQL partition? What are the two methods for MySQL partitioning?

What is database partition? Database partitioning is a physical database design technology. DBA and database modeling personnel are quite familiar with it. Although partition technology can achieve a lot of results, its main purpose is to reduce the total amount of data read/write in a specific SQL operation to reduce the response time. There are two main MySQL partitions: // The concept of rows and columns: row and column)

Horizontal partition Horizontal Partitioning)

In this MySQL partition form, partitions are used to partition the rows of a table. In this way, datasets separated by physical columns in different groups can be combined to split individual data into single partitions) or group one or more partitions ). All columns defined in the table can be found in each dataset, so the table features are maintained.

For example:

A table containing ten-year invoice records can be partitioned into ten different partitions, each of which contains a one-year record. Peng Yi Note: here we will talk about the specific partitioning method. You can first talk about it. You must use an attribute column to separate it. For example, the column used here is the year)

Vertical partition Vertical Partitioning)

In general, this MySQL partition method reduces the width of the target table by vertical partitioning of the table, so that certain columns are divided into specific partitions, each partition contains the rows corresponding to the columns.

For example:

A table that contains large text and BLOB columns. These text and BLOB columns are not frequently accessed. In this case, we need to divide these infrequently used text and BLOB into another partition, while ensuring their data relevance, it can also increase the access speed.

When database vendors start to create partitions in their database engines, they must design the physical partition structure of the table, do not save redundant data. Different tables contain data in the parent table at the same time.) or join each other into a logical parent object, which is usually a view ). This method will invalidate most of the functions of the horizontal partition, and sometimes affect the vertical partition.

How to modify MySQL Environment Variables

Example of defining MySQL transactions

Example of creating a MySQL Stored Procedure

Use of mysql ifnull Functions

MySQL date functions