Shulou(Shulou.com)06/01 Report--

1. Overview

After the database single table reaches a certain amount, the performance will decline, such as mysql\ sql server and so on, so the data needs to be partitioned. At the same time, sometimes there may be a data split or something, so the partition table is even more useful!

The new Partition features in MySQL 5.1 began to increase, and the advantages became more and more obvious:

-- more data can be stored than a single disk or file system partition

It is easy to delete data that is not used or out of date.

Some queries can be greatly optimized

-- when it comes to aggregate functions such as SUM () / COUNT (), it can be done in parallel

-- IO has greater throughput

Partitioning allows rules that can be set to any size to allocate multiple parts of a single table across the file system. In fact, different parts of the table are stored as separate tables in different locations.

2. Zoning technical support

Before 5.6, use this parameter to see if partitioning is supported when the configuration is to be configured.

Mysql > SHOW VARIABLES LIKE'% partition%'

+-+ +

| | Variable_name | Value |

+-+ +

| | have_partition_engine | YES |

+-+ +

If yes indicates that your current configuration supports partitions

After 5.6 and after adoption, view it as follows

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

+-+

42 rows in set (0.00 sec)

On the last line, you can see that partition is ACTIVE, indicating that partitions are supported

3. Partition types and examples

3.1 range partitioning

RANGE partition: assigns multiple rows to a partition based on column values that belong to a given contiguous interval. Such as time, continuous constant values, etc.

-- Division by year

Mysql > use mytest

Database changed

Mysql > create table range_p (

> perid int (11)

-> pername char (12) not null

-> monsalary DECIMAL (10jue 2)

-> credate datetime

->) partition by range (year (credate))

-> partition p2011 values less than (2011)

-> partition p2012 values less than (2012)

-> partition p2013 values less than (2013)

-> partition p2014 values less than (2014)

-> partition p2015 values less than maxvalue

->)

Query OK, 0 rows affected (0.12 sec)

3.2 enumerate partitions

LIST partitions: similar to partitioning by RANGE, except that LIST partitions are selected based on column values matching a value in a set of discrete values. For example, attribute values such as gender (1 and 2).

Mysql > create table list_p (

> perid int (11)

-> pername char (12) not null

-> sex int (1) not null

-> monsalary DECIMAL (10jue 2)

-> credate datetime

->) partition by list (sex)

-> partition psex1 values in (1)

-> partition psex2 values in (2))

Query OK, 0 rows affected (0.06 sec)

Note that list can only be a number. If you use characters, you will get an error ERROR 1697 (HY000): VALUES value for partition 'psex1' must have type INT.

3.3 discrete Partition

HASH partition: a partition that is selected based on the return value of a user-defined expression that uses the column values of the rows to be inserted into the table. This function can contain any expression that is valid in MySQL and produces a non-negative integer value.

-- partitioned by int field hash

Create table hash_p (

Perid int (11)

Pername char (12) not null

Sex int (1) not null

Monsalary DECIMAL (10Phone2)

Credate datetime

) partition by hash (perid)

Partitions 8

-- hash partition with time function

Mysql > create table hash_p (

> perid int (11)

-> pername char (12) not null

-> sex int (1) not null

-> monsalary DECIMAL (10jue 2)

-> credate datetime

->) partition by hash (year (credate))

-> partitions 8

Query OK, 0 rows affected (0.11 sec)

3.4 key value Partition

KEY partitions: similar to partitioning by HASH, except that KEY partitions only support computing one or more columns, and the MySQL server provides its own hash function. One or more columns must contain > integer values.

Its zoning method is very similar to that of hash.

Mysql > create table key_p (

> perid int (11)

-> pername char (12) not null

-> sex int (1) not null

-> monsalary DECIMAL (10jue 2)

-> credate datetime

->) partition by key (perid)

-> partitions 8

Query OK, 0 rows affected (0.12 sec)

3.5 other instructions

Mysql-5.5 began to support COLUMNS partitions, which can be seen as the evolution of RANGE and LIST partitions, and COLUMNS partitions can be partitioned directly using non-shaping data. COLUMNS partitions support the following data types:

All plastic surgery, such as INT SMALLINT TINYINT BIGINT. FLOAT and DECIMAL do not support it.

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 use multiple columns for partitioning.

Mysql > create table range_p (

> perid int (11)

-> pername char (12) not null

-> monsalary DECIMAL (10jue 2)

-> credate datetime

->) PARTITION BY RANGE COLUMNS (credate)

-> partition p20151 values less than ('2015-04-01')

-> partition p20152 values less than ('2015-07-01')

-> partition p20153 values less than ('2015-10-01')

-> partition p20154 values less than ('2016-01-01')

-> partition p20161 values less than ('2016-04-01')

-> partition partlog values less than maxvalue

->)

Query OK, 0 rows affected (0.12 sec)

4. Summary

Generally speaking, the partition of mysql is still in progress and needs to be further strengthened. Compared with oracle, there is still a gap! For example, there is no partitioning technology based on non-data types.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.