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

Https://dev.mysql.com/doc/refman/5.7/en/alter-table-partition-operations.html

I. Overview of zoning

Partitioning is a table design pattern that has supported partitioning since version 5.1, logically it is a table, and physically it may be multiple objects. Storage engines such as MyISAM, INNODB and NDB all support partitioning, but CSV, MERGE and FEDORATED do not.

1. Zoning category

Horizontal partitioning: table records are split and records of different rows of the same table are assigned to different physical files.

Range partitions: rows are placed into partitions based on column values that belong to a given contiguous interval. Column range partitions are supported since MYSQL5.5, the most commonly used partition.

List partition: same as Range partition, except that List partition faces discrete values, since MYSQL5.5 expenditure supports column List partition.

Hash partition: partition according to the return value of a user-defined expression, which cannot be negative.

Key partitioning: partitioning according to the hash function provided by the MYSQL database.

Vertical partitioning: split the table field process (not supported by MYSQL). Different columns in the same table are assigned to different physical files.

two。 Advantages and disadvantages of zoning

Advantages:

It can greatly improve the query efficiency.

Mainly used for high availability of database, easy to manage

Disadvantages:

Regardless of partition, if there is a primary key or unique key index in the table, the partitioned column must be part of the unique index.

2. Detailed explanation of partition types. 1. Range partition

Create table t_range (id int)

Partition by range (id)

(partition p0 values less than (100)

Partition p1 values less than (500)

Partition p2 values less than maxvalue)

Insert into t_range values (10), (120),

(root:localhost:Sat Jul 8 20:05:12 2017) [dbtest] >\! Ls-lnrth / home/mysql/dbtest

Total 320K

-rw-rw---- 1 500 500 61 Mar 17 15:58 db.opt

-rw-rw---- 1,500 500 32 Jul 8 20:04 t_range.par # # Storage Partition Information

-rw-rw---- 1 500 500 8.4K Jul 8 20:04 t_range.frm

-rw-rw---- 1 500 500 96K Jul 8 20:05 t_range#P#p2.ibd

-rw-rw---- 1 500 500 96K Jul 8 20:05 t_range#P#p1.ibd

-rw-rw---- 1 500 500 96K Jul 8 20:05 t_range#P#p0.ibd

(root:localhost:Sat Jul 8 20:14:13 2017) [(none)] > select * from information_schema.partitions where table_name='t_range'\ G

* * 1. Row *

TABLE_CATALOG: def

TABLE_SCHEMA: dbtest

TABLE_NAME: t_range

PARTITION_NAME: p0

SUBPARTITION_NAME: NULL

PARTITION_ORDINAL_POSITION: 1

SUBPARTITION_ORDINAL_POSITION: NULL

PARTITION_METHOD: RANGE

SUBPARTITION_METHOD: NULL

PARTITION_EXPRESSION: id

SUBPARTITION_EXPRESSION: NULL

PARTITION_DESCRIPTION: 100

TABLE_ROWS: 1

AVG_ROW_LENGTH: 16384

DATA_LENGTH: 16384

MAX_DATA_LENGTH: NULL

INDEX_LENGTH: 0

DATA_FREE: 0

CREATE_TIME: NULL

UPDATE_TIME: NULL

CHECK_TIME: NULL

CHECKSUM: NULL

PARTITION_COMMENT:

NODEGROUP: default

TABLESPACE_NAME: NULL

* 2. Row * *

TABLE_CATALOG: def

TABLE_SCHEMA: dbtest

TABLE_NAME: t_range

PARTITION_NAME: p1

SUBPARTITION_NAME: NULL

PARTITION_ORDINAL_POSITION: 2

SUBPARTITION_ORDINAL_POSITION: NULL

PARTITION_METHOD: RANGE

SUBPARTITION_METHOD: NULL

PARTITION_EXPRESSION: id

SUBPARTITION_EXPRESSION: NULL

PARTITION_DESCRIPTION: 500

TABLE_ROWS: 1

AVG_ROW_LENGTH: 16384

DATA_LENGTH: 16384

MAX_DATA_LENGTH: NULL

INDEX_LENGTH: 0

DATA_FREE: 0

CREATE_TIME: NULL

UPDATE_TIME: NULL

CHECK_TIME: NULL

CHECKSUM: NULL

PARTITION_COMMENT:

NODEGROUP: default

TABLESPACE_NAME: NULL

* 3. Row * *

TABLE_CATALOG: def

TABLE_SCHEMA: dbtest

TABLE_NAME: t_range

PARTITION_NAME: p2

SUBPARTITION_NAME: NULL

PARTITION_ORDINAL_POSITION: 3

SUBPARTITION_ORDINAL_POSITION: NULL

PARTITION_METHOD: RANGE

SUBPARTITION_METHOD: NULL

PARTITION_EXPRESSION: id

SUBPARTITION_EXPRESSION: NULL

PARTITION_DESCRIPTION: MAXVALUE

TABLE_ROWS: 1

AVG_ROW_LENGTH: 16384

DATA_LENGTH: 16384

MAX_DATA_LENGTH: NULL

INDEX_LENGTH: 0

DATA_FREE: 0

CREATE_TIME: NULL

UPDATE_TIME: NULL

CHECK_TIME: NULL

CHECKSUM: NULL

PARTITION_COMMENT:

NODEGROUP: default

TABLESPACE_NAME: NULL

3 rows in set (0.00 sec)

2. List partition

-rw-rw---- 1,500 28 Jul 8 21:39 t_list.par

-rw-rw---- 1 500 500 8.4K Jul 8 21:39 t_list.frm

-rw-rw---- 1 500 500 96K Jul 8 21:39 t_list#P#p1.ibd

-rw-rw---- 1 500 500 96K Jul 8 21:39 t_list#P#p0.ibd

CREATE TABLE `tList` (

`id`int (11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8

/ * 50100 PARTITION BY LIST (id)

(PARTITION p0 VALUES IN (1, 3, 7, 9) ENGINE = InnoDB

PARTITION p1 VALUES IN (2, 4, 6, 8, 10) ENGINE = InnoDB) * /

3. Hash partition

HASH partitions distribute data evenly among predefined partitions, ensuring that the amount of data in each partition is roughly the same. When defining range and list partitions, you must specify which partition the column values or sets of column values are stored in, while the hash partition automatically allocates the column values, averaging the data in different partitions.

CREATE TABLE `t _ hash` (

`id`int (11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8

/ * 50100 PARTITION BY HASH (id)

PARTITIONS 4 * /

-rw-rw---- 1 500 500 32 Jul 8 21:53 t_hash.par

-rw-rw---- 1 500 500 8.4K Jul 8 21:53 t_hash.frm

-rw-rw---- 1 500 500 96K Jul 8 21:54 t_hash#P#p3.ibd

-rw-rw---- 1 500 500 96K Jul 8 21:54 t_hash#P#p2.ibd

-rw-rw---- 1 500 500 96K Jul 8 21:54 t_hash#P#p1.ibd

-rw-rw---- 1 500 500 96K Jul 8 21:54 t_hash#P#p0.ibd

4. Key partition

Hash partitions are partitioned according to user-defined functions, and key uses functions provided by the MYSQL database for partitioning.

CREATE TABLE `t _ key` (

`id`int (11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8

/ * 50100 PARTITION BY KEY (id)

PARTITIONS 4 * /

5. Columns partition

Range, List, Hash, and Key partitions are all for integer partitions, and if they are not integer partitions, they need to be converted by related functions. However, with columns partitions, no conversion is required.

Columns supports int/small int/tinyint/bigint/date/datetime/char/varchar/binary, but not float/decimal/blob/text.

Create table t_range_columns (dtime datetime)

Partition by range columns (dtime)

(partition p0 values less than ('2016-01-01')

Partition p1 values less than ('2017-01-01')

Partition p2 values less than maxvalue)

6. Subpartition

Partition is carried out on the basis of partition, also known as compound partition.

III. District maintenance and management

Alter table table_name

| | ADD PARTITION (partition_definition) |

| | DROP PARTITION partition_names |

| | TRUNCATE PARTITION {partition_names | ALL} |

| | COALESCE PARTITION number |

| | REORGANIZE PARTITION partition_names INTO (partition_definitions) |

| | ANALYZE PARTITION {partition_names | ALL} |

| | CHECK PARTITION {partition_names | ALL} |

| | OPTIMIZE PARTITION {partition_names | ALL} |

| | REBUILD PARTITION {partition_names | ALL} |

| | REPAIR PARTITION {partition_names | ALL} |

| | REMOVE PARTITIONING |

1. Increase zoning

Alter table t_range add partition (partition p2 values less than maxvalue)

two。 Delete partition

Alter table t_range drop partition p2

Alter table t_list remove partitioning

3. View partition

Information_schema.partitions

4. Clear partition data

Alter table t_range truncate partition p2

5. Analytic partition

(root:localhost:Sat Jul 8 21:30:03 2017) [dbtest] > explain partitions select * from t_range\ G

* * 1. Row *

Id: 1

Select_type: SIMPLE

Table: t_range

Partitions: p0,p1,p2

Type: ALL

Possible_keys: NULL

Key: NULL

Key_len: NULL

Ref: NULL

Rows: 3

Extra: NULL

1 row in set (0.00 sec)

(root:localhost:Sat Jul 8 21:30:18 2017) [dbtest] > explain partitions select * from t_range where id=800\ G

* * 1. Row *

Id: 1

Select_type: SIMPLE

Table: t_range

Partitions: p2

Type: ALL

Possible_keys: NULL

Key: NULL

Key_len: NULL

Ref: NULL

Rows: 2

Extra: Using where

1 row in set (0.00 sec)

(query only specified partitions)

6. Swap partition

MYSQL5.6 supports swap partitions. The syntax is as follows:

Alter table t_range exchange partition p0 with table t

The data of the P0 partition of the partition table t_range is exchanged into the t table, and the data of the t table is also exchanged into the t _ range table, and the exchange is bidirectional.

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.