MySQL partition Table

Today's statistics show that a table uses table partitioning to take this opportunity to record.

1. What is a table partition?

Table partitioning refers to the decomposition of a table in a database into smaller, easier-to-manage parts according to certain rules. Logically, there is only one table, but the bottom layer is made up of multiple physical partitions.

2. Differences between table partitions and tables

Sub-table: refers to a certain rule, a table is broken into a number of different tables. For example, the user order records based on time into multiple tables. The difference between a table and a partition is that a partition logically has only one table, while a table breaks a table into multiple tables.

3. What are the benefits of table partitioning?

1) Data from partitioned tables can be distributed across different physical devices, enabling efficient use of multiple hardware devices. 2) You can store more data 3) and optimize the query than a single disk or file system. When you include a partitioning condition in a where statement, you can scan only one or more partitioned tables to improve query efficiency, and when you involve the sum and count statements, you can also work on multiple partitions in parallel, and finally summarize the results. 4) Partitioned tables are easier to maintain. For example, to bulk delete large amounts of data, you can clear the entire partition. 5) You can use partitioned tables to avoid certain special bottlenecks, such as mutually exclusive access to a single index of InnoDB, ext3 ask for your system's Inode lock competition.

4. Limiting factors in partitioned tables

1) A table can have at most 1024 partitions 2) in MySQL5.1, the partition expression must be an integer or an expression that returns an integer. Support for non-integer expression partitioning is provided in MySQL5.5. 3) If there is a primary key or a unique indexed column in the partition field, then more primary and unique index columns must be included. That is, the partition field either does not contain a primary key or an indexed column, or contains all primary keys and index columns. 4) cannot use FOREIGN KEY constraint in partition table 5) MySQL partition applies to all data and indexes of a table, cannot partition the table data only, not partition the index, or partition the index partition, not the table, or only part of the table data.

5. How can I tell if the current MySQL supports partitioning?

Command: show variables like ‘%partition%‘ Run Result:

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

The value of have_partintioning is yes, which indicates that the partition is supported.

6. What are the types of partitions supported by MySQL?

1) Range Partition: partition by range of data 2) list partition: According to the values in the list, the difference between range is that range range values are continuous. 3) hash partition 4) key partition description in the MySQL5.1 version, the Range,list,hash partition requires that the partition key must be of type int or that the int type is returned through an expression. However, when the key is partitioned, you can use other types of columns (except the Blob,text type) as the partition key.

7. Range Partition

Use the range of values to partition, the intervals are continuous and cannot overlap each other. Grammar:

range(exp)( //exp可以为列名或者表达式，比如to_date(created_date) partition p0 values less than(num))

For example:

Mysql> CREATE TABLE EMP (-> IDInt not > store_id int not  null->)-> partition by Range (store_id) (-> partition P 0 values less than (< Span class= "Hljs-number" >10> partition P1 values Less than (20>);               

The above statement creates an EMP table and partitions according to the store_id field, with values less than 10 present in the partition P0, greater than or equal to 10, and values less than 20 exist in the partition P1. note that each partition is defined sequentially, from lowest to highest. The above statement, if the order of less than (10) and than (20) is reversed, then the error will be as follows:

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

Problem with range partition

1. Range coverage PROBLEM: The INSERT statement fails when the value of the corresponding partition key in the inserted record is not in the range defined by the partition. The above example, what happens if I insert a record of store_id = 30? When we partition above, the maximum value is 20, if inserting a record more than 20, will be error:

   Mysql>InsertInto emp ( id,store_id)   values (2< Span class= "hljs-function" >,< Span class= "Hljs-params" >30); ERROR 1526 (HY000): Table has no partition for value 30           

   Tip 30 This value does not have a corresponding partition.SolutionsA. Estimate the value of the partition key and add the partition in time. B. When partitioning is set, use thevalues less than maxvalueThe MaxValue clause, which represents the largest possible integer value. C. Try to select all the fields that can be overwritten as partition keys, such as 12 months of the year.
3. In a range partition, the value of the partition key, if NULL, is treated as a minimum value.

8. List partition

A list partition is a set of discrete values that tells the database which partition a particular value belongs to. Grammar:

    partition by list(exp)( //exp为列名或者表达式 http://www.cnblogs.com/roucheng/        partition p0 values in (3,5) //值为3和5的在p0分区 )

Unlike range, the list partition does not have to be in any particular order of life. For example:

Mysql> CREATE TABLE Emp1 (-> ID intNotNULL,-> store_id intNotNull->)-> Partition by List (store_id) (-> Partition P0ValuesInch (3 , 5 > partition P1 values  in ( 2,6 ,7 , 9- >                 

Note If the value of the partition key that the inserted record corresponds to is not in the value specified by the list partition, the insertion fails. Also, List cannot provide maxvalue as the range partition does.

9. Columns Partition

The partition type introduced in MySQL5.5 resolves the problem that the range partition and the list partition only support integer partitioning before the 5.5 release. Columns partitions can be subdivided into a range columns partition and a list columns partition, they all support Integer, DateTime, and string three-big data types. (Text and BLOB types are not supported as partition keys) The columns partition also supports multi-column partitioning (this is not expanded in detail here).

Ten. Hash partition

Hash partitioning is primarily used to disperse hot-spot readings, ensuring that data is distributed as evenly as possible in predetermined numbers of partitions. MySQL supports two types of hash partitioning: regular hash partitioning and linear hash partitioning. A. Regular hash partitioning: Using the modulo algorithm syntax:

by hash(store_id) partitions 4;

The above statement, according to the store_id 4 modulo, determines the record storage location. For example store_id = 234 of the record, MOD (234,4) = 2, so it will be stored in the second partition.

the advantages and disadvantages of regular hash partitioning : the ability to distribute the data as evenly as possible. Cons: not suitable for frequently changing needs of partitions. If I were to add two new partitions and now have 6 partitions, then the result of the MoD (234,6) would be inconsistent with the results of the previous mod (234,4), so that most of the data would need to be recalculated. To solve this problem, MySQL provides a linear hash partition.

B. Linear hash Partitioning: The partitioning function is a linear 2 exponentiation algorithm. Grammar:

by LINER hash(store_id) partitions 4;

Unlike regular hash, the "Liner" keyword. Algorithm Introduction: Assuming that the partition number to save the record is n,num as a non-negative integer representing the number of partitions divided into, then n can be obtained by the following steps: Step 1. A power of 2 is found that is greater than or equal to NUM, and the value v,v can be obtained by the following formula: V = Power (2,ceiling (2,num)) For example: just set 4 partitions, Num=4,log (2,4) =2,ceiling (2) = 2, Power (2,2) = 4, v=4 Step 2. Set N=f (column_list) & (V-1) For example: Just v=4,store_id=234 corresponds 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 takes a value of 2. Assuming the above calculated n=5, then v=ceiling (2.5) =3,n=234& (3-1) = 1, that is, in the first partition.

advantages and disadvantages of linear hashing : MySQL can handle more quickly when partition maintenance (add, delete, merge, split partition). Disadvantage: The data distribution between each partition of the linear hash is not very balanced compared with the regular hash partition.

One. Key partition

Like hash partitioning, hash partitioning allows user-defined expressions to be used, but the key partition does not allow user-defined expressions. Hash only supports integer partitioning, while key partitioning supports other types of columns except BLOBs and text as partition keys. Grammar:

partition by key(exp) partitions 4;//exp是零个或多个字段名的列表 hovertree.com

When the key partition, exp can be empty, if empty, the default is to use the primary key as the partition key, when there is no primary key, the non-null unique key is selected as the partition key.

12. Sub-partitions

The partition table splits each partition again and becomes a composite partition.

Summarize this article:

1. What is a table partition?
2. Differences between table partitions and tables
3. What are the benefits of table partitioning?
4. Limiting factors in partitioned tables
5. How can I tell if the current MySQL supports partitioning?
6. What are the types of partitions supported by MySQL?
7. Range Partition
8. List partition
9. Columns Partition
Ten. Hash partition
One. Key partition
12. Sub-partitions

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

MySQL partition Table