Detailed analysis of table partitioning technology in Mysql _mysql

MySQL Partitioning technology (is the MySQL 5.1 version after the start with-> is Oracle MySQL Technology team maintenance staff to plug into MySQL in the form of technology)

1. Overview

Once the database table reaches a certain amount, the performance will decay, such as Mysql\sql server and so on, so it is necessary to partition the data. At the same time, sometimes there may be data stripping or something, the partition table is more useful!

The new partition (Partition) feature in MySQL 5.1 starts to grow, and the advantages are increasingly obvious:

1. More data can be stored than a single disk or file system partition
2. It's easy to remove unused or obsolete data.
3. Some queries can be greatly optimized
4. When aggregating functions such as SUM ()/count () are involved, they can be performed in parallel
5. Greater IO Throughput
6. Partitions allow rules that can be set to any size, allocating multiple portions of a single table across a file system. In fact, different parts of the table are stored as separate tables in different places.
   

2. Regional technical Support

Before 5.6, use this parameter to see if the partition is supported when it is configured:

Mysql> show VARIABLES like '%partition% ';
+-----------------------+-------+
| Variable_name     | Value |
+-----------------------+-------+
| have_partition_engine | YES  |
+-----------------------+-------+

If yes indicates your current configuration support partition. After 5.6 and adopted, it is viewed in the following manner:

Mysql> show PLUGINS;
+----------------------------+----------+--------------------+---------+---------+
| Name            | Status  | Type        | Library | License |
+----------------------------+----------+--------------------+---------+---------+
| Binlog           | ACTIVE  | STORAGE ENGINE   | NULL  | GPL   |
| Mysql_native_password   | ACTIVE  | Authentication   | NULL  | GPL   |
..................................................................................
| Innodb_locks        | ACTIVE  | Information SCHEMA | NULL  | GPL   |
| Innodb_lock_waits     | ACTIVE  | Information SCHEMA | NULL  | GPL   |
| partition         | ACTIVE  | STORAGE ENGINE   | NULL  | GPL   |
+----------------------------+----------+--------------------+---------+---------+

The last line of rows in Set (0.00 sec), you can see that partition is active and supports partitioning.

3, zoning types and examples

3.1 Range Partition

RANGE partition : Assigns multiple rows to a partition based on a column value that belongs to a given contiguous interval. such as time, continuous constant value, and so on--partition by year

mysql> use mytest;
Database changed
mysql> create TABLE range_p ( 
  -> perid Int (one), 
  -> pername char () NOT NULL, 
  - > Monsalary DECIMAL (10,2),
  -> credate datetime 
  ->) partition by range (year (credate)) ( 
  -> Partition p2011 values less than (a), 
  -> partition p2012 values less than (a),-> partition-p2013 
  VA Lues less Than (2013), 
  -> partition p2014 values less than (2014),
  -> partition p2015 values less than Max Value 
  ->);
Query OK, 0 rows affected (0.12 sec)

3.2 Enumerating partitions

list partition : Similar to a range partition, the difference is that the list partition is selected based on a column value matching a value in a discrete-value collection. For example, such as gender (1,2) and other attribute values.

Mysql> CREATE TABLE list_p (  -> perid Int (one),  -> pername char () NOT NULL,  -> sex int (1) NULL,  -> monsalary DECIMAL (10,2),  -> credate datetime  ->) partition by list (sex) (  -> p Artition psex1 values in (1),  -> partition psex2 values in (2)); Query OK, 0 rows affected (0.06 sec)

Note that the list can only be numbers, using characters to error error 1697 (HY000): VALUES value for partition ' Psex1 ' must type INT.

3.3 Discrete partitions

Hash partition : A partition that is selected based on the return value of a user-defined expression, calculated using the column values of the rows that will be inserted into the table. This function can be wrapped > contains any expressions that are valid in MySQL and produce non-negative integer values.

--Hash partition with int field

CREATE TABLE hash_p ( 
Perid Int (one), 
Pername char () NOT NULL,
sex int (1) is not NULL,
monsalary DECIMAL (10,2),
credate datetime 
) partition by hash (Perid) 
partitions 8;

--hash partition with TIME function

Mysql> CREATE TABLE hash_p ( 
  -> perid Int (one), 
  -> pername char () NOT NULL,
  -> sex int (1) is not NULL ,
  -> monsalary DECIMAL (10,2),
  -> credate datetime 
  ->) partition by hash (year (credate)) 
  -> partitions 8;
Query OK, 0 rows affected (0.11 sec)

3.4-Value partition

Key partitions: Similar to a hash partition, except that the key partition only supports the calculation of one or more columns, and the MySQL server provides its own hash function. Must have one or more columns containing > integer values. The partitioning method is similar to the hash:

Mysql> CREATE TABLE key_p ( 
  -> perid Int (one), 
  -> pername char () NOT NULL,
  -> sex int (1) Not Nu ll,
  -> monsalary DECIMAL (10,2),
  -> credate datetime 
  ->) partition by Key (Perid) 
  -> partitions 8;
Query OK, 0 rows affected (0.12 sec)

3.5 Other Notes

mysql-5.5 begins to support columns partitions, which can be considered an evolution of range and list partitions, and columns partitions can be partitioned directly using non-reshaping data. The columns partition supports the following data types: All reshaping, such as int SMALLINT TINYINT BIGINT. float and decimal are not supported. Date types, such as Date and DateTime. The remaining date types are not supported. String types, such as char, VARCHAR, binary, and varbinary. Blob and text types are not supported. Columns can be partitioned using multiple columns.

Mysql> CREATE TABLE range_p ( 
  -> perid Int (one), 
  -> pername char () NOT NULL, 
  -> monsalary DECIMAL (10,2),
  -> credate datetime 
  ->) PARTITION by RANGE COLUMNS (credate) (-> PARTITION, p20151 
  values les s than (' 2015-04-01 '), 
  -> partition p20152 values less than (' 2015-07-01 '), 
  -> partition values p20153 Than (' 2015-10-01 '), 
  -> partition p20154 values less than (' 2016-01-01 '),
  -> partition the values p20161 t Han (' 2016-04-01 '),
  -> partition partlog values, less than MaxValue 
  );
Query OK, 0 rows affected (0.12 sec)

Summarize:

Partition table is the new function in MySQL5.1, the partitioning technology is not very mature until mysql5.1.22-rc, the maintenance and management function of many partitions is not realized. For example, the partition of data storage space recovery, partition repair, partition optimization, the MySQL partition can be used in the table can be deleted by the partition, and the database modification operation is not small, and frequently in accordance with the Partition field query table (such as the statistics in malicious code by day partition, often according to the time to query, grouping, etc. , and you can delete partitions on a per-day basis. In addition, since MySQL has no global index but only a partitioned index, this table cannot be partitioned when a single 2 unique index [Z5] is included, and the partition column must contain a primary key. Otherwise, MySQL will complain.

In short, MySQL has a lot of restrictions on partitioning, and individuals think that the actual meaning of hash and key partition is not too big.

Partitioning introduces a new way of optimizing queries (and, of course, corresponding drawbacks). The optimizer can use partition functions to trim partitions, or remove partitions from the query completely. It does this by inferring whether data can be found on a particular partition. So in the best case, trimming allows queries to access less data. It is important to define the partitioning key in the WHERE clause, even if it looks like it is superfluous. By partitioning keys, the optimizer can remove unused partitions, otherwise the execution engine accesses all of the table's partitions like a merged table, which can be very slow on large tables. Partitioned data is better maintained than non partitioned data and can be removed by removing the partition to remove the old data. Partitioned data can be distributed to different physical locations so that the server can use multiple hard drives more efficiently.

The return value of the [Z1] partition function must be an integer, and the partition function return value for the new partition should be greater than the return value of the partition function for any one of the existing partitions.
[Z2] Error tip for table with primary key: #1503
A PRIMARY key must INCLUDE all COLUMNS inthe TABLE ' S partitioning FUNCTION, no primary key without this constraint
[Z3] Note: For tables with range partitions, you can only add new partitions to the high-end of the partition list using the Add partition. That is, you cannot add partitions that are smaller than the partition's range.
[Z4] You can only rearrange adjacent partitions for a table that is partitioned by a range, and you cannot skip a range partition. You cannot use Reorganizepartition to alter the partition type of a table, i.e., you cannot change a range partition into a hash partition, or vice versa. You cannot use this command to change a partition expression or column.
[Z5] Note the difference between a primary key and a unique index

Official information: https://dev.mysql.com/doc/refman/5.5/en/partitioning.html