MySQL partition Table principle and use detailed

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:

Partition by Range (exp) (//exp can be a column name or an expression, such as 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),   partition P1 values less than    );

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:

ERROR 1493 (HY000): VALUES Less THAN value must is 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> INSERT INTO EMP (ID,STORE_ID) values (2,30); ERROR 1526 (HY000): Table have no partition for value 30

   Tip 30 This value does not have a corresponding partition. Solutions A. Estimate the value of the partition key and add the partition in time. B. When partitioning is set, the values less than maxvalue clause is used, MaxValue 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.

2. 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 is a column name or expression        partition P0 values in (3,5)  //value 3 and 5 in P0 partition    )

Unlike range, the list partition does not have to be in any particular order of life. 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 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:

Partition 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:

Partition 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 to a non-negative integer, representing the number of partitions divided into, then n can be obtained by the following steps:

Step 1. Find a power of 2 greater than or equal to NUM, the value of which is v,v can be obtained by the following formula:
V = Power (2,ceiling (Log (2,num)))
For example: just set up 4 partitions, Num=4,log (2,4) =2,ceiling (2) =2,power (2,2) = 4, i.e. 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 is the value of 2, can be.
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 is a list of 0 or more field names

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.

13. Partitioning for null value handling

MYSQ allows the partition key value to be null, the partition key may be a field or a user-defined expression. In general, MySQL treats null values as 0 values or a minimum value when partitioning.

Attention
Range partition: Null values are treated as minimum values
List partition: The null value must be listed, otherwise it will not be accepted
Hash/key partition: null value is treated as 0 value

14. Partition Management

Partition management includes additions, deletions, and queries for partitions.

Add Partition:

For range partitions and list partitions:

ALTER TABLE TABLE_NAME ADD partition (partition P0 values ... (exp))

The content behind values differs depending on the type of partition.

For hash partitions and key partitions:

ALTER TABLE TABLE_NAME add partition partitions 8;

The above statement refers to the addition of 8 partitions.

Delete Partition

For range partitions and list partitions:

ALTER TABLE table_name DROP partition P0; P0 is the name of the partition to be deleted

The partition is deleted and all data in that partition is also deleted. At the same time, if the partition is deleted and the partition cannot overwrite all the values, the data will be inserted with an error.

For hash and key partitioning:

ALTER TABLE TABLE_NAME COALESCE partition 2; Reduce the partition to a total of 2

Coalesce [? k???? Les] vi. Union, merger

Partition query 1) query How many partitions there are in a table

Mysql> Select  ,   partition_name,   partition_expression,   partition_description,- >   table_rows,  information_schema.partitions,   where,   table_schema= ' test ' AND   table_name = ' emp ', +----------------+----------------------+-----------------------+------------+| Partition_name | partition_expression | partition_description | Table_rows |+----------------+----------------------+-----------------------+------------+| P0 |             store_id             |          0 | | P1 |             store_id             |          1 |+----------------+----------------------+-----------------------+------------+

That is, you can query from the Information_schema.partitions table.

2) Review the execution plan to determine if the query data is filtered by partition

mysql> Explain partitions select * from emp where store_id=10 \g;*************************** 1. Row ***************************        id:1select_type:simple     table:emppartitions:p1      type:systempossible_ Keys:null       key:null   key_len:null       ref:null      rows:1     extra:1 row in Set (0.00 sec)

The above result: PARTITIONS:P1 indicates that the data is retrieved in the P1 partition.

MySQL partition Table principle and use detailed