MySQL table partition, view partition

Original address: http://blog.csdn.net/feihong247/article/details/7885199, MySQL partition introduction

Database partitioning

Database partitioning is a physical database design technique. Although partitioning technology can achieve many effects, its primary purpose is to reduce the amount of data read and write in a particular SQL operation in order to narrow the response time of the SQL statement, while the partition is completely transparent to the application.

There are two main types of MySQL partitions: horizontal and vertical partitioning

Horizontal partitioning (horizontalpartitioning)

This form of partitioning is the partitioning of rows based on tables, in such a way that the data sets separated by the physical columns within different groupings can be combined for individual segmentation (single partitioning) or collective partitioning (one or more partitions).
All the columns defined in the table can be found in each dataset, so the attributes of the table remain. Horizontal partitioning must be segmented by an attribute column. Common such as the year, date, and so on.

Vertical partitioning (verticalpartitioning)

This partitioning method generally reduces the width of the target table by vertically dividing the table so that certain columns are partitioned into specific partitions, each of which contains all the rows that correspond to the columns.
can use showvariables like '%partition% ';

command to query whether the current MySQL database version supports partitioning.

The role of partitioning: improving database performance and simplifying data management

In the scan operation, the MySQL optimizer scans only the partition that protects the data to reduce the scan range for improved performance.

Partitioning technology makes data management simple, deleting a partition does not affect another partition, and the partitioning system is directly managed without manual intervention.

MySQL supports partitioning starting from version 5.1. The name of each partition is case insensitive. Partition table names in the same table are unique.

Second, MySQL partition type

Depending on the different partitioning rules you use, you can divide into several large partition types.

RANGE Partition:

Assigns multiple rows to a partition based on a column value that belongs to a given contiguous interval.

LIST Partition:

Similar to by range partitioning, the difference is that a list partition is selected based on a value in a set of discrete values that match a column value.

Hash partition:

A partition that is selected based on the return value of a user-defined expression that is evaluated using the column values of those rows that will be inserted into the table. This function can contain any expression that is valid in MySQL that produces a non-negative integer value.

KEY
Partitioning: Similar to partitioning by hash, the difference is that the key partition only supports the calculation of one or more columns, and the MySQL server provides its own hash function. You must have one or more columns that contain integer values.

Composite partitions:

Re-splitting of each partition in a partitioned table based on the range/list type. Sub-partitions can be types such as Hash/key.

Three, MySQL partition table common operation example

Take the departmental staff table as an example:

1) Create a range partition

CREATE TABLE EMP

(empno varchar() NOT NULL,

EmpName varchar(+),

Deptno int,

Birthdate Date,

Salary int

)

Partition by range (salary)

(

Partition P1 values less than (+),

Partition P2 values less than (+),

Partition P3 values less than MaxValue

);

Make a range partition based on the employee's salary.

CREATE TABLE EMP

(empno varchar() NOT NULL,

EmpName varchar(+),

Deptno int,

Birthdate date not NULL,

Salary int

)

Partition by Range (year (birthdate))

(

Partition P1 values less than (1980),

Partition P2 values less than (1990),

Partition P3 values less than MaxValue

);

The Year (birthdate) expression, which calculates the date of birth of the employee, is used as the range partition basis. The most notable thing here is that the expression must have a return value.

2) Create a list partition

CREATE TABLE EMP

(empno varchar() NOT NULL,

EmpName varchar(+),

Deptno int,

Birthdate date not NULL,

Salary int

)

Partition by list (DEPTNO)

(

Partition P1 values in (ten),

Partition P2 values in (),

Partition P3 values in (+)

);

Department as the basis for division, each department to do a partition.

3) Create a hash partition

Hash partitioning is primarily used to ensure that data is evenly distributed over a predetermined number of partitions. In the range and list partitions, you must explicitly specify which partition a given column value or set of column values should be kept in, and in the hash partition, MySQL does the work automatically, all you have to do is to specify a column value or expression based on the column value that will be hashed. and the number of partitions that the partitioned table will be divided into.

CREATE TABLE EMP

(empno varchar() NOT NULL,

EmpName varchar(+),

Deptno int,

Birthdate date not NULL,

Salary int

)

Partition by hash (year (birthdate))

Partitions 4;

4) Create a key partition

Partitioning by key is similar to a user-defined expression that is used in addition to the hash partition, and the hash function of the key partition is provided by the MySQL server, and the server uses its own internal hash function, which is based on the same algorithm as password (). "CREATE TABLE ... The syntax rule for PARTITION by KEY is similar to creating a rule for a table that is partitioned by a hash. The only difference is that the keyword used is a key instead of a hash, and the key partition takes only one or more column names in a list.

CREATE TABLE EMP

(empno varchar() NOT NULL,

EmpName varchar(+),

Deptno int,

Birthdate date not NULL,

Salary int

)

Partition by key (birthdate)

Partitions 4;

5) Create a composite partition

Range-hash (range hash) composite partition

CREATE TABLE EMP

(empno varchar() NOT NULL,

EmpName varchar(+),

Deptno int,

Birthdate date not NULL,

Salary int

)

Partition by range (salary)

Subpartition by Hash (year (birthdate))

Subpartitions 3

(

Partition P1 values less than (+),

Partition P2 values less than MaxValue

);

Range-key Composite Partitioning

CREATE TABLE EMP

(empno varchar() NOT NULL,

EmpName varchar(+),

Deptno int,

Birthdate date not NULL,

Salary int

)

Partition by range (salary)

Subpartition by Key (birthdate)

Subpartitions 3

(

Partition P1 values less than (+),

Partition P2 values less than MaxValue

);

List-hash Composite Partitioning

CREATE TABLE EMP (

empno varchar() not NULL,

EmpName varchar(+),

Deptno int,

Birthdate Date not NULL,

Salary int

)

PARTITION by list (DEPTNO)

Subpartition by Hash (year (birthdate))

Subpartitions 3

(

PARTITION p1 VALUES in (ten),

PARTITION p2 VALUES in (a)

)

;

List-key Composite Partitioning

CREATE TABLE EMPK (

empno varchar() not NULL,

EmpName varchar(+),

Deptno int,

Birthdate Date not NULL,

Salary int

)

PARTITION by list (DEPTNO)

Subpartition by Key (birthdate)

Subpartitions 3

(

PARTITION p1 VALUES in (ten),

PARTITION p2 VALUES in (a)

)

;

6) Managing operations on partitioned tables

To delete a partition:

ALTER TABLE EMP drop partition P1;

You cannot delete a hash or key partition.

Delete multiple partitions at once, ALTER TABLE EMP drop partition P1,P2;

Add Partition:

ALTER TABLE EMP ADD partition (partition P3 values less than (4000));

ALTER TABLE EMPL ADD partition (partition P3 values in ());

Decomposition partition:

The Reorganizepartition keyword can modify some or all of the table's partitions without losing data. The overall range of partitions before and after decomposition should be consistent.

ALTER TABLE TE

Reorganize partition P1 into

(

Partition P1 values less than (+),

Partition P3 values less than (+)

); ----No data loss

Merge partitions:

Merge partition: Merge 2 partitions into one.
ALTER TABLE TE

Reorganize partition P1,P3 into

(partition P1 values less than ());

----No data loss

Redefine the hash partition table:

Alter table EMP Partition by hash (salary) partitions 7;

----No data loss

To redefine a range partition table:

Alter table emp Partitionbyrange (Salary)

(

Partition P1 values less than (+),

Partition P2 values less than (4000)

); ----No data loss

To delete all partitions of a table:

Alter table emp removepartitioning;--does not lose data

To rebuild a partition:

This has the same effect as deleting all the records that were saved in the partition and then re-inserting them. It can be used to defragment partition fragments.

ALTER TABLE EMP Rebuild PARTITIONP1,P2;

Optimize partitions:

If you delete a large number of rows from a partition, or make many changes to a row with variable length (that is, a column with Varchar,blob, or text type), you can use the ALTER TABLE ... OPTIMIZE PARTITION "to reclaim unused space and defragment the partition data file.

ALTER TABLE emp optimize partition p1,p2;

Parsing partitions:

Reads and saves the partition's key distribution.

ALTER TABLE EMP Analyze partition P1,P2;

Patch Partition:

Patch the damaged partition.

ALTER TABLE emp repairpartition p1,p2;

Check partition:

You can check a partition in the same way you would use a check table on a non-partitioned table.

ALTER TABLE emp CHECK partition P1,P2;

This command can tell you whether the data or index in the partition P1,P2 of the table EMP has been corrupted. If this happens, use "ALTER TABLE ... REPAIR PARTITION "to patch the partition.

"Limitations of the MySQL partition table"

1. In version 5.1, the partitioned table has explicit rules for unique constraints, and each unique constraint must contain the partition key (and also the primary KEY constraint) of the partitioned table.

CREATE TABLE Emptt (

empno varchar() not NULL,

EmpName varchar(+),

Deptno int,

Birthdate Date not NULL,

Salary int ,

Primary KEY (EMPNO)

)

PARTITION by Range (salary)

(

PARTITION P1 VALUES less than (+),

PARTITION P2 VALUES less than ($)

);

Such statements will be an error. MySQL Database error:a PRIMARY KEY must include allcolumns in the table ' s partitioning function;

CREATE TABLE Emptt (

empno varchar() not NULL,

EmpName varchar(+),

Deptno int(one),

Birthdate Date not NULL,

Salary int(one),

Primary KEY (Empno,salary)

)

PARTITION by Range (salary)

(

PARTITION P1 VALUES less than (+),

PARTITION P2 VALUES less than ($)

);

Adding the salary column to the primary key is normal.

2. How the MySQL partition handles null values

If the partition key is in the same column, there is no notnull constraint.

If it is a range partition table, then the null row is saved in the partition with the smallest range.

If it is a list partition table, then the null row is saved to the partition with list 0.

In the case of partitioning by hash and key, any expression that produces a null value, MySQL, is treated with a return value of 0.

To avoid this situation, it is recommended that the partition key be set to not NULL.

3. The partition key must be of type int, or it can be null by returning an int type through an expression. The only exception is when the

When the zone type is a key partition, other types of columns can be used as partition keys (except BLOB or TEXT columns).

4. Create an index on the partition key of the partitioned table, the index will also be partitioned, and the partition key does not have a global index.

5. Only rang and list partitions can be sub-partitioned, and hash and key partitions cannot be sub-partitioned.

6. Temporary tables cannot be partitioned.

Iv. several ways to obtain MySQL partition table information

1. Show CREATE TABLE name
You can view the CREATE statement that created the partitioned table

2. Show Table Status
You can see if the table is a partitioned table

3. View the Information_schema.partitions table
Select
Partition_name part,
Partition_expression expr,
Partition_description Descr,
Table_rows
From Information_schema.partitions where
Table_schema = Schema ()
and table_name= ' test ';
You can see what partitions the table has, how it is partitioned, the number of records in the data in the partition, and more

4. Explain partitions SELECT statement
This statement shows which partitions are scanned and how they are used.

Performance Comparison of partition table

1. Create two tables: Part_tab (partition Table), No_part_tab (normal table)

CREATE Tablepart_tab

(c1 int Defaultnull, C2 varchar2 () default NULL, C3 date NOT NULL)

PARTITION Byrange (Year (C3))

(PARTITION p0values less THAN (1995),

PARTITION P1 valuesless THAN (1996),

PARTITION P2 valuesless THAN (1997),

PARTITION P3 valuesless THAN (1998),

PARTITION P4 VALUES less THAN (1999),

PARTITION P5 Valuesless THAN (2000),

PARTITION P6 valuesless THAN (2001),

PARTITION P7 valuesless THAN (2002),

PARTITION P8 valuesless THAN (2003),

PARTITION p9 valuesless THAN (2004),

PARTITION p10values less THAN (2010),

PARTITION p11values less THAN (MAXVALUE));

CREATE TABLE No_part_tab

(c1 int Defaultnull, C2 varchar2 () default NULL, C3 date not null);

2. Inserting 8 million data with stored procedures

CREATE Procedureload_part_tab ()

Begin

DECLARE v int default 0;

While v < 8000000

Do

INSERT INTO Part_tab

Values (V, ' testingpartitions ', adddate (' 1995-01-01 ', (rand (v) *36520) mod 3652));

Set v = v + 1;

End while;

End

INSERT INTO No_part_tab select * FROMPART_TAB;

3. Test SQL Performance

Query partition table:

SelectCount (*) from Part_tab where C3 > Date ' 1995-01-01 ' and C3 < date ' 1995-12-31 ';

+----------+
| COUNT (*) |
+----------+
| 795181 |
+----------+
1 row in Set (2.62 sec)

To query a normal table:

SelectCount (*) from Part_tab where C3 > date ' 1995-01-01 ' and C3 < date ' 1995-12-31 ';

+----------+
| COUNT (*) |
+----------+
| 795181 |
+----------+
1 row in Set (7.33 sec)

Partitioned tables have a 70% less execution time than normal tables.

4. Analysis of execution through explain statements

Mysql>explain Select COUNT (*) from Part_tab where C3 > Date ' 1995-01-01 ' and C3 < date ' 1995-12-31 ';

+----+-------------+----------+------+---------------+------+---------+------+---------+-------------+

| ID |select_type | Table | Type |possible_keys | Key | Key_len | Ref | Rows | Extra |

+----+-------------+----------+------+---------------+------+---------+------+---------+-------------+

| 1 | Simple | Part_tab | All | NULL | NULL | NULL | NULL | 7980796 | Using where |

+----+-------------+----------+------+---------------+------+---------+------+---------+-------------+

1 Rowin Set

Mysql>explain Select COUNT (*) from No_part_tab where C3 > Date ' 1995-01-01 ' and C3 < date ' 1995-12-31 ';

+----+-------------+-------------+------+---------------+------+---------+------+---------+-------------+

| ID |select_type | Table | Type |possible_keys | Key | Key_len | Ref | Rows | Extra |

+----+-------------+-------------+------+---------------+------+---------+------+---------+-------------+

| 1 | Simple | No_part_tab | All | NULL | NULL | NULL | NULL | 8000206 | Using where |

+----+-------------+-------------+------+---------------+------+---------+------+---------+-------------+

1 Rowin Set

MySQL >

The partition table performs a scan of 7980796 rows, while the normal table scans 8000206 rows.

MySQL table partition, view partition