Shulou(Shulou.com)06/01 Report--

What this article shares with you is about whether database table partitioning will improve insertion efficiency. The editor thinks it is very practical, so I share it with you. I hope you can get something after reading this article. Let's take a look at it with the editor.

Database table partitioning can improve the insertion efficiency; database table partitioning can improve the efficiency of table addition, deletion, modification and query. The principle of database table partition insert operation: when writing a record, the partition layer opens and locks all the underlying tables, then determines which partition accepts the record, and then writes the record to the corresponding underlying table.

What is zoning?

Partitioning is to decompose the table according to the rules, dividing the data into segments and storing them in multiple locations, either on the same disk or on different machines. After partitioning, it is ostensibly a table, but the data is hashed to multiple locations. App still operates on large table names when reading and writing, and db automatically organizes the partitioned data.

Zoning can be divided into two types:

1. Horizontal partition (Horizontal Partitioning)

This form of partitioning partitions the rows of the table so that the data sets divided by the physical columns in different groups are combined for individual partition (single partition) or collective partition (one or more partitions). All columns defined in the table can be found in each dataset

So the characteristics of the table are still maintained.

To take a simple example: a table containing ten-year invoice records can be partitioned into ten different partitions, each containing records for one year. (note: the specific partition method used here will be discussed later. We can first say that it must be divided by a certain attribute column, for example, the column used here is the year.)

2. Vertical partition (Vertical Partitioning)

Generally speaking, this partitioning method reduces the width of the target table by dividing the table vertically, so that some specific columns are divided into specific partitions, and each partition contains the corresponding rows of the columns.

To take a simple example: a table that contains large text and BLOB columns, and these text and BLOB columns are not often accessed, so it is necessary to divide these infrequently used text and BLOB into another partition to improve access speed while ensuring their data relevance.

The principle of partitioned tables

Partitioned tables are implemented by multiple related underlying tables, which are also represented by handle objects, so we can also access each partition directly. Each underlying table managed by the storage engine is the same as managing ordinary tables (all underlying tables must use the same storage engine). The index of the partitioned table only adds the same index to each underlying table, from the point of view of the storage engine. The underlying table is no different from a regular table, and the storage engine does not need to know whether it is a normal table or part of a partitioned table.

The operation on the partition table follows the following operation logic:

Select query:

When querying a partition table, the partition layer first opens and locks all the underlying tables, and the optimizer determines whether some partitions can be filtered, and then calls the corresponding storage engine interface to access the data of each partition.

Insert operation:

When writing a record, the partition layer opens and locks all the underlying tables, then determines which partition accepts the record, and then writes the record to the corresponding underlying table

Delete operation:

When deleting a record, the partition layer first opens and locks all the underlying tables, then determines the partition corresponding to the data, and finally deletes the corresponding underlying tables.

Update operation:

When updating a piece of data, the partition layer first opens and locks all the underlying tables. Mysql first determines the partition in which the records need to be updated, then takes out the data and updates it, then determines which partition the updated data should be placed in, then writes to the underlying table and deletes the underlying table where the original data is located.

Although each operation will open and lock all the underlying tables, this does not mean that the partitioned table locks the whole table during processing. If the storage engine can implement row-level locking on its own, such as innodb, it will release the corresponding table lock in the partition layer. This locking and unlocking process is similar to the query on ordinary Innodb.

Partitioning can play a very important role in the following scenarios:

A: the table is too large to be stored in memory, or there is hot data only in the last part of the table. The rest is historical data.

B: the data of the partition table is easier to maintain. For example, if you want to delete a large amount of data in bulk, you can use the method of clearing the entire partition. In addition, a separate partition can be optimized, checked, repaired, etc.

C: the data of the partition table can be distributed across different physical devices, making efficient use of multiple hardware devices

D: partitioned tables can be used to avoid some special bottlenecks, such as mutually exclusive access to a single index of innodb, inode lock contention of ext3 file systems, etc.

E: if necessary, you can also back up and restore separate partitions, which works very well in scenarios with very large datasets

F: optimize the query so that when the partition column is included in the where sentence, only the necessary partitions can be used to improve query efficiency, and when queries involving aggregate functions such as sum () and count () are involved, they can be processed in parallel on each partition, and finally only need to summarize the results of all partitions.

The partition of the mysql database always treats null as a smaller value than any non-null, which is the same as the order by operation that handles null values in the database, and null always comes first when sorting in ascending order, so the mysql database handles null differently for different partition types.

For range partitions, if null is inserted into the partition column, the mysql database places the value in the leftmost partition. Note that if you delete the partition, everything under the partition is deleted from disk, the partition where null is located is deleted, and the null value is deleted.

To use null under a list partition, it must be explicitly defined in the partition's hash value, otherwise an error will be reported when inserting the null. Hash and key partitions do not handle null in the same way as range,list partitions. Any partition function returns null as 0. 0.

Zoning

Partitioning is to divide a database or its constituent elements into separate parts.

It is a method of organizing table storage in advance.

Mysql supports horizontal partitioning

Assign a specific table row as a subset of the row

Partition distribution is carried out across physical storage

-- according to the specified rules set by the user when needed

Each partition is stored as its own unit

Division of data

-- divide the data into subsets according to the partition function

Partition types and expressions are part of the table definition

The expression can be an integer or a function that returns an integer value.

This value determines, by definition, in which partition each record is stored.

1.primary key and unique key must be included in the partition key, otherwise "ERROR 1503 (HY000)" will be reported when creating primary key and unique index

two。 Range partitions add partitions can only be appended after the maximum value

3. Engine must be the same for all partitions

4. Range partition fields: integer, numeric expression, date column, date function expression (such as year (), to_days (), to_seconds (), unix_timestamp ())

Zoning management

New partition

ALTER TABLE sale_dataADD PARTITION (PARTITION p201010 VALUES LESS THAN (201011))

Delete partition

-when a partition is deleted, all data in that partition is also deleted.

ALTER TABLE sale_data DROP PARTITION p201010

Merging of divisions

The following SQL merges p201001-p201009 into three partitions p2010Q1-p2010Q3

ALTER TABLE sale_dataREORGANIZE PARTITION p201001 PARTITION p2010Q2 VALUES LESS THAN p201002 INTO (PARTITION p2010Q1 VALUES LESS THAN (201004), PARTITION p2010Q2 VALUES LESS THAN (201007), PARTITION p2010Q3 VALUES LESS THAN (201010)); these are the database table partitions that will improve the insertion efficiency. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

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.