MySQL Partition Table

MySQL Partition Table

In the statistical data today, we found that a table uses table partitions and we will take this opportunity to record them.

1. What is Table Partitioning?

Table Partitioning refers to splitting a table in a database into several smaller and easier-to-manage parts according to certain rules. Logically, there is only one table, but the underlying layer is composed of multiple physical partitions.

2. Differences between table partitions and table shards

Table sharding: A table is divided into multiple different tables by certain rules. For example, the user order record is converted into multiple tables based on time. The difference between a table shard and a partition is that a partition has only one table logically, while a table shard breaks down a table into multiple tables.

3. What are the benefits of Table Partitioning?

1) Data in the partition table can be distributed across different physical devices to efficiently utilize multiple hardware devices. 2) You can store more data than a single disk or file system. 3) Optimize the query. When a where statement contains a partition condition, you can scan only one or more partition tables to improve query efficiency. When the sum and count statements are involved, you can also perform Parallel Processing on multiple partitions, the final summary result. 4) partitioned tables are easier to maintain. For example, to delete a large amount of data in batches, you can clear the entire partition. 5) you can use a partition table to avoid some special bottlenecks, such as the mutex access to a single index in InnoDB and the inode lock competition in your system in ext3.

4. Partition Table restrictions

1) A table can have a maximum of 1024 partitions. 2) in MySQL5.1, the partition expression must be an integer or an integer expression is returned. Non-integer expression partitions are supported in MySQL5.5. 3) if the partition field contains a primary key or a column with a unique index, many primary key columns and unique index columns must be included. That is, the partition field either does not contain the primary key or index column, or contains all the primary key and index columns. 4) Foreign key constraints cannot be used in partitioned tables. 5) MySQL partitions are applicable to all data and indexes of a table. Instead of only partitioning the table data, you cannot partition only the index, not the table, or only part of the table's data.

5. How can I determine whether MySQL currently supports partitioning?

Command:show variables like '%partition%'Running result:

mysql> show variables like '%partition%';+-------------------+-------+| Variable_name     | Value |+-------------------+-------+| have_partitioning | YES   |+-------------------+-------+1 row in set (0.00 sec)

The have_partintioning value is YES, indicating that partitions are supported.

6. What partition types does MySQL support?

1) RANGE partitioning: partitions are based on the RANGE range of data. 2) LIST partitioning: partitions are based on the values in the List. The difference with RANGE is that the RANGE range value of RANGE partitioning is continuous. 3) HASH partition 4) KEY PartitionDescriptionIn MySQL, RANGE, LIST, and HASH partitions require that the partition key must be of the INT type, or return the INT type through an expression. However, you can use other types of columns (excluding BLOB and TEXT) as partition keys when partitioning keys.

7. Range partitioning

Use the value range for partitioning. The interval must be continuous and cannot overlap with each other. Syntax:

Partition by range (exp) (// exp can be a column name or expression, for example, to_date (created_date) partition p0 values less than (num ))

For example:

mysql> create table emp(    -> id INT NOT null,    -> store_id int not null    -> )    -> partition by range(store_id)(    ->   partition p0 values less than(10),    ->   partition p1 values less than(20)    -> );

The preceding statement creates an emp table and partitions it based on the store_id field. Values smaller than 10 exist in partition p0, values greater than or equal to 10, and values smaller than 20 exist in partition p1.Note:Each partition is defined in order, from the lowest to the highest. In the preceding statement, if the order of less than (10) and less than (20) is reversed, an error is returned as follows:

ERROR 1493 (HY000): VALUES LESS THAN value must be strictly increasing for each partition

Problems with RANGE partitions

8. LIST partitions

List partitions are used to create a discrete value List to tell the database which partition the specific value belongs. Syntax:

Partition by list (exp) (// exp is the column name or expression http://www.cnblogs.com/roucheng/ partition p0 values in () // values are 3 and 5 in p0 partition)

Different from Range, list partitions do not have to live in any specific order. For example:

mysql> create table emp1(    -> id int not null,    -> store_id int not null    -> )    -> partition by list(store_id)(    ->   partition p0 values in (3,5),    ->   partition p1 values in (2,6,7,9)    -> );

Note:If the partition key value corresponding to the inserted record is not in the value specified by list partition, insertion will fail. In addition, a list cannot provide maxvalue as a range partition.

9. Columns Partition

The partition type introduced in MySQL5.5 solves the problem that range partitions and list partitions only support integer partitions Before Version 5.5. Columns partitions can be subdivided into range columns partitions and list columns partitions. They support three data types: integer, date and time, and string. (Text and blob types are not supported as partition keys.) columns partitions also support multi-column partitions (not detailed here ).

10. Hash Partition

Hash partitions are mainly used to distribute hotspot reads, ensuring that data is evenly distributed among pre-determined partitions. MySQL supports two Hash partitions: Regular Hash partitions and linear Hash partitions. A. Regular Hash partition: Use the modulo algorithm Syntax:

partition by hash(store_id) partitions 4;

In the preceding statement, the storage location of the record is determined by modulo 4 of store_id. For example, the store_id = 234 record, MOD () = 2, so it will be stored in the second partition.

Advantages and disadvantages of regular Hash partitionsAdvantage: data can be evenly distributed as much as possible. Disadvantage: it is not suitable for frequent changes in partitions. If I want to add two new partitions and now there are six partitions, the results of MOD () are different from those of previous MOD, in this way, most of the data needs to be re-computed. To solve this problem, MySQL provides linear Hash partitions.

B. Linear Hash partitioning: a partition function is a linear 2-power algorithm. Syntax:

partition by LINER hash(store_id) partitions 4;

The difference from conventional Hash lies in the "Liner" keyword. Algorithm Introduction: assume that the number of partitions to save the record is N and num is a non-negative integer, which indicates the number of partitions to be split. You can use the following steps to obtain N: Step 1. find a Power greater than or equal to the value of num 2, which is V. V can be obtained through the following formula: V = Power (2, Ceiling (Log (2, num) for example: we have set four partitions, num = 4, Log (2, 4) = 2, Ceiling (2) = 2, power (2, 2) = 4, that is, V = 4 Step 2. set N = F (column_list) & (V-1) for example: Just V = 4, store_id = 234 corresponding to the N value, N = 234 & (4-1) = 2 Step 3. when N> = num, set V = Ceiling (V/2), N = N & (V-1) for example: store_id = 234, N = 2 <4, so N is set to 2. Suppose N = 5, then V = Ceiling (2.5) = 3, N = 234 & (3-1) = 1, that is, in the first partition.

Advantages and disadvantages of linear HashAdvantage: MySQL can process partitions more quickly during partition maintenance (add, delete, merge, and split. Disadvantage: compared with conventional Hash partitions, the data distribution between linear Hash partitions is not balanced.

11. Key Partition

Similar to Hash partitions, Hash partitions allow user-defined expressions, but Key partitions do not allow user-defined expressions. Hash only supports integer partitions, while Key partitions support columns other than Blob and text as partition keys. Syntax:

Partition by key (exp) partitions 4; // The list of exp is zero or multiple field names hovertree.com

Exp can be null when the key is partitioned. If it is null, the primary key is used as the partition key by default. If there is no primary key, a non-null unique key is selected as the partition key.

12. subpartition

In a partitioned table, each partition is partitioned again and becomes a composite partition.

 

Summary:

1. What is Table Partitioning?
2. Differences between table partitions and table shards
3. What are the benefits of Table Partitioning?
4. Partition Table restrictions
5. How can I determine whether MySQL currently supports partitioning?
6. What partition types does MySQL support?
7. Range partitioning
8. LIST partitions
9. Columns Partition
10. Hash Partition
11. Key Partition
12. subpartition

Http://www.cnblogs.com/roucheng/p/nodecookie.html