MySQL Partition introduction 01/14 Update SLTechnology News&Howtos

MySQL Partition introduction

2025-01-14 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

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

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

Mysql > create table T1 (

-> col1 int not null,col2 date not null,col3 int not null,col4 int not null,unique key (col1,col2)) partition by hash (col3) partitions 4

ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

Mysql > create table T1 (

-> col1 int not null,col2 date not null,col3 int not null,col4 int not null,unique key (col1,col2,col3)) partition by hash (col3) partitions 4

Query OK, 0 rows affected (0.49 sec)

Mysql > create table T2 (

-> col1 int null

-> col2 date null

-> col3 int null

-> col4 int null

->) engine=innodb

-> partition by hash (col3)

-> partitions 4

Query OK, 0 rows affected (0.40 sec)

Mysql > create table T3 (

-> col1 int null

-> col2 date null

-> col3 int null

-> col4 int null

-> key (col4)

->) engine=innodb

-> partition by hash (col3)

-> partitions 4

Query OK, 0 rows affected (0.23 sec)

-- check whether the database supports partitioning

MariaDB [test] > show plugins

+-+

+-+

+-+

MariaDB [test] > select * from INFORMATION_SCHEMA.plugins where plugin_name='partition'\ G

* * 1. Row *

PLUGIN_NAME: partition

PLUGIN_VERSION: 1.0

PLUGIN_STATUS: ACTIVE

PLUGIN_TYPE: STORAGE ENGINE

PLUGIN_TYPE_VERSION: 100114.0

PLUGIN_LIBRARY: NULL

PLUGIN_LIBRARY_VERSION: NULL

PLUGIN_AUTHOR: Mikael Ronstrom, MySQL AB

PLUGIN_DESCRIPTION: Partition Storage Engine Helper

PLUGIN_LICENSE: GPL

LOAD_OPTION: ON

PLUGIN_MATURITY: Stable

PLUGIN_AUTH_VERSION: 1.0

1 row in set (0.00 sec)

-range Partition

MariaDB [test] > CREATE TABLE members (

-> firstname VARCHAR (25) NOT NULL

-> lastname VARCHAR (25) NOT NULL

-> username VARCHAR (16) NOT NULL

> email VARCHAR (35)

-> joined DATE NOT NULL

->)

-> PARTITION BY RANGE COLUMNS (joined)

-> PARTITION p0 VALUES LESS THAN ('1960-01-01')

-> PARTITION p1 VALUES LESS THAN ('1970-01-01')

-> PARTITION p2 VALUES LESS THAN ('1980-01-01')

-> PARTITION p3 VALUES LESS THAN ('1990-01-01')

-> PARTITION p4 VALUES LESS THAN MAXVALUE

->)

Query OK, 0 rows affected (0.45 sec)

MariaDB [test] > CREATE TABLE employees (

-> id INT NOT NULL

> fname VARCHAR (30)

> lname VARCHAR (30)

-> hired DATE NOT NULL DEFAULT '1970-01-01'

-> separated DATE NOT NULL DEFAULT '9999-12-31'

-> job_code INT NOT NULL

-> store_id INT NOT NULL

->)

-> PARTITION BY RANGE (store_id)

-> PARTITION p0 VALUES LESS THAN (6)

PARTITION p1 VALUES LESS THAN (11)

PARTITION p2 VALUES LESS THAN (16)

-> PARTITION p3 VALUES LESS THAN MAXVALUE

->)

Query OK, 0 rows affected (0.49 sec)

MariaDB [test] > insert into employees (id,fname,lname,job_code,store_id) values

Query OK, 1 row affected (0.06 sec)

MariaDB [test] > insert into employees (id,fname,lname,job_code,store_id) values (2)

Query OK, 1 row affected (0.06 sec)

MariaDB [test] > insert into employees (id,fname,lname,job_code,store_id) values (2)

Query OK, 1 row affected (0.03 sec)

MariaDB [test] > insert into employees (id,fname,lname,job_code,store_id) values (3)

Query OK, 1 row affected (0.13 sec)

MariaDB [test] > insert into employees (id,fname,lname,job_code,store_id) values (4)

Query OK, 1 row affected (0.02 sec)

MariaDB [test] > insert into employees (id,fname,lname,job_code,store_id) values (5)

Query OK, 1 row affected (0.08 sec)

MariaDB [test] > insert into employees (id,fname,lname,job_code,store_id) values (6)

Query OK, 1 row affected (0.02 sec)

MariaDB [test] > select * from employees

+-- +

+-- +

| | 2 | Tom | Carl | 1970-01-01 | 9999-12-31 | 10 | 1 |

| | 2 | Lily | Berg | 1970-01-01 | 9999-12-31 | 20 | 7 |

| | 3 | Lucy | Phynix | 1970-01-01 | 9999-12-31 | 20 | 10 |

| | 4 | Bill | Jones | 1970-01-01 | 9999-12-31 | 20 | 15 |

| | 5 | Jill | Deco | 1970-01-01 | 9999-12-31 | 30 | 12 | |

| | 1 | John | Terry | 1970-01-01 | 9999-12-31 | 10 | 100 |

| | 6 | Emily | Aaron | 1970-01-01 | 9999-12-31 | 30 | 20 |

+-- +

7 rows in set (0.00 sec)

MariaDB [test] > show create table employees\ G

* * 1. Row *

Table: employees

Create Table: CREATE TABLE `employees` (

`id`int (11) NOT NULL

`fname` varchar (30) DEFAULT NULL

`lname` varchar (30) DEFAULT NULL

`hired`date NOT NULL DEFAULT '1970-01-01'

`roomated`date NOT NULL DEFAULT '9999-12-31'

`job_ code` int (11) NOT NULL

`store_ id` int (11) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8

/ * 50100 PARTITION BY RANGE (store_id)

(PARTITION p0 VALUES LESS THAN (6) ENGINE = InnoDB

PARTITION p1 VALUES LESS THAN (11) ENGINE = InnoDB

PARTITION p2 VALUES LESS THAN (16) ENGINE = InnoDB

PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) * /

1 row in set (0.00 sec)

Zoning by year

Mysql > create table sales (

-> money int unsigned not null

-> date datetime

->) engine=innodb

-> partition by range (year (date))

Partition p2008 values less than (2009)

-> partition p2009 values less than (2010)

-> partition p2010 values less than (2011)

->)

Query OK, 0 rows affected (0.31 sec)

Mysql > insert into sales values (100,100-01-01-01), 2008-02-01), 2008-01-02), 2009-03-01), 2010-03-01

Query OK, 5 rows affected (0.13 sec)

Records: 5 Duplicates: 0 Warnings: 0

Mysql > commit

Query OK, 0 rows affected (0.00 sec)

Mysql > alter table sales drop partition p2008

Query OK, 0 rows affected (0.12 sec)

Records: 0 Duplicates: 0 Warnings: 0

Mysql > explain partitions

-> select * from sales

-> where date > = '2009-01-01' and date create table sales2 (

-> money int unsigned not null

-> date datetime

->) engine=innodb

-> partition by range (year (date) * 100+month (date))

-> partition p201001 values less than (201002)

-> partition p201002 values less than (201003)

-> partition p201003 values less than (201004)

->)

Query OK, 0 rows affected (0.20 sec)

Mysql > explain partitions select * from sales2 where date > = '2010-01-01' and date create table sales1 (

-> money int unsigned not null

-> date datetime) engine=innodb

-> partition by range (to_days (date))

-> partition p201001

-> values less than (to_days ('2010-02-01'))

-> partition p201002

-> values less than (to_days ('2010-03-01'))

-> partition p201003

-> values less than (to_days ('2010-04-01'))

->)

Query OK, 0 rows affected (0.20 sec)

Mysql > explain partitions select * from sales1 where date > = '2010-01-01' and date create table t (

-> id int

->) engine=innodb

-> partition by range (id)

-> partition p0 values less than (10)

-> partition p1 values less than (20)

Query OK, 0 rows affected (0.55 sec)

Mysql > system ls-lrt / var/lib/mysql/test

-rw-rw----. 1 mysql mysql 8556 Nov 3 14:22 t.frm

-rw-rw----. 1 mysql mysql 28 Nov 3 14:22 t.par

-rw-rw----. 1 mysql mysql 98304 Nov 3 14:22 t#P#p0.ibd

-rw-rw----. 1 mysql mysql 98304 Nov 3 14:22 t#P#p1.ibd

Mysql > select * from information_schema.partitions

-> where table_schema=database () and table_name='t'\ G

* * 1. Row *

TABLE_CATALOG: def

TABLE_SCHEMA: test

TABLE_NAME: t

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

TABLE_ROWS: 1

AVG_ROW_LENGTH: 16384

DATA_LENGTH: 16384

MAX_DATA_LENGTH: NULL

INDEX_LENGTH: 0

DATA_FREE: 0

CREATE_TIME: 2016-11-03 14:22:00

UPDATE_TIME: NULL

CHECK_TIME: NULL

CHECKSUM: NULL

PARTITION_COMMENT:

NODEGROUP: default

TABLESPACE_NAME: NULL

* 2. Row * *

TABLE_CATALOG: def

TABLE_SCHEMA: test

TABLE_NAME: t

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

TABLE_ROWS: 2

AVG_ROW_LENGTH: 8192

DATA_LENGTH: 16384

MAX_DATA_LENGTH: NULL

INDEX_LENGTH: 0

DATA_FREE: 0

CREATE_TIME: 2016-11-03 14:22:00

UPDATE_TIME: NULL

CHECK_TIME: NULL

CHECKSUM: NULL

PARTITION_COMMENT:

NODEGROUP: default

TABLESPACE_NAME: NULL

2 rows in set (0.00 sec)

Mysql > insert into t values (50)

ERROR 1526 (HY000): Table has no partition for value 50

Mysql > alter table t

-> add partition (

-> partition p2 values less than maxvalue)

Query OK, 0 rows affected (0.49 sec)

Records: 0 Duplicates: 0 Warnings: 0

Mysql > insert into t values (50)

Query OK, 1 row affected (0.00 sec)

Mysql > commit

Query OK, 0 rows affected (0.01 sec)

-- LIST partition

MariaDB [test] > CREATE TABLE employees5 (

-> id INT NOT NULL

> fname VARCHAR (30)

> lname VARCHAR (30)

-> hired DATE NOT NULL DEFAULT '1970-01-01'

-> separated DATE NOT NULL DEFAULT '9999-12-31'

-> job_code INT

-> store_id INT

->)

-> PARTITION BY LIST (store_id)

-> PARTITION pNorth VALUES IN (3, 5, 6, 9, 17)

-> PARTITION pEast VALUES IN (1, 2, 10, 11, 19, 20)

-> PARTITION pWest VALUES IN (4, 12, 13, 14, 18)

-> PARTITION pCentral VALUES IN (7, 8, 15, 16)

->)

Query OK, 0 rows affected (5.13 sec)

-- COLUMN partition

Field partitioning is a variation of range partitions and list partitions, and field partitions can use multiple fields as partitioning keys.

Range field partition and list field partition support non-integer fields, and the supported data types are as follows:

All integer types: TINYINT, SMALLINT, MEDIUMINT, INT,BIGINT.

DATE,DATETIME .

CHAR, VARCHAR, BINARY,VARBINARY .

MariaDB [test] > CREATE TABLE rc2 (

-> an INT

-> b INT

->)

-> PARTITION BY RANGE COLUMNS (aformab) (

-> PARTITION p0 VALUES LESS THAN (0p10)

-> PARTITION p1 VALUES LESS THAN (102.20)

-> PARTITION p2 VALUES LESS THAN (105.30)

-> PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE)

->)

Query OK, 0 rows affected (0.27 sec)

Mysql > create table t_columns_range (

-> an int

-> b datetime

->) engine=innodb

-> partition by range columns (b) (

-> partition p0 values less than ('2009-01-01')

-> partition p1 values less than ('2010-01-01')

->)

Query OK, 0 rows affected (0.20 sec)

Mysql > create table customers_1 (

First_name varchar (25)

Last_name varchar (25)

> street_1 varchar (30)

> street_2 varchar (30)

City varchar (15)

-> renewal date

->)

-> partition by list columns (city)

-> partition pRegion_1

-> values in ('Oskarshamn',' Hogsby', 'Monsters')

-> partition pRegion_2

-> values in ('Vimmerby',' Hultsfred', 'Vastervik')

-> partition pRegion_3

-> values in ('Nassjo',' Eksjo', 'Vetlanda')

-> partition pRegion_4

-> values in ('Uppvidinge',' Alvesta', 'Vaxjo')

->)

Query OK, 0 rows affected (0.23 sec)

Mysql > create table rcx (

-> an int

-> b int

-> c char (3)

-> d int

->) engine=innodb

-> partition by range columns (a _

-> partition p0 values less than (5pm 10pm GGG')

-> partition p1 values less than (10pm 20pm / mm)

-> partition p2 values less than (15p21 p2 values less than)

-> partition p3 values less than (MAXVALUE,MAXVALUE,MAXVALUE)

->)

Query OK, 0 rows affected (0.32 sec)

-- Hash partition

Hash partitioning mainly ensures that the data in the partition table is evenly distributed among the partitions.

Mysql > create table t_hash (an int,b datetime) engine=innodb

-> partition by hash (year (b)

-> partitions 4

Query OK, 0 rows affected (7.81 sec)

MariaDB [test] > CREATE TABLE employees7 (

-> id INT NOT NULL

> fname VARCHAR (30)

> lname VARCHAR (30)

-> hired DATE NOT NULL DEFAULT '1970-01-01'

-> separated DATE NOT NULL DEFAULT '9999-12-31'

-> job_code INT

-> store_id INT

->)

-> PARTITION BY HASH (store_id)

-> PARTITIONS 4

Query OK, 0 rows affected (0.22 sec)

The MySQL database also supports a partition called LINEAR HASH, which uses a more complex algorithm to determine where new rows are inserted into already partitioned tables

The advantage of LINEAR HASH partitions is that adding, deleting, merging, and splitting partitions will be faster, which is good for dealing with tables with large amounts of data. The disadvantage of LINEAR HASH partitioning is that

Compared with the data distribution obtained by using HASH partition, the distribution of data in each interval may not be balanced.

Mysql > create table t_linear_hash (

-> an int

-> b datetime

->) engine=innodb

-> partition by linear hash (year (b)

-> partitions 4

Query OK, 0 rows affected (0.23 sec)

-- KEY partition

KEY partitions are similar to hash partitions, except that hash partitions use user-defined expressions. The partition key column must contain the primary keys of some or all tables.

MariaDB [test] > CREATE TABLE K1 (

-> id INT NOT NULL

-> name VARCHAR (20)

-> UNIQUE KEY (id)

->)

-> PARTITION BY KEY ()

-> PARTITIONS 2

Query OK, 0 rows affected (0.11 sec)

-- compound partition

The MySQL database allows subpartitions of HASH or KEY on the partitions of RANGE and LIST

MariaDB [test] > CREATE TABLE ts (id INT, purchased DATE)

-> PARTITION BY RANGE (YEAR (purchased))

-> SUBPARTITION BY HASH (TO_DAYS (purchased))

-> PARTITION p0 VALUES LESS THAN (1990) (

-> SUBPARTITION s0

-> SUBPARTITION S1

->)

-> PARTITION p1 VALUES LESS THAN (2000)

-> SUBPARTITION S2

-> SUBPARTITION S3

->)

-> PARTITION p2 VALUES LESS THAN MAXVALUE (

-> SUBPARTITION S4

-> SUBPARTITION S5

->)

Query OK, 0 rows affected (0.51sec)

Mysql > create table ts (an int,b date) engine=innodb

-> partition by range (year (b)

-> subpartition by hash (to_days (b)

-> subpartitions 2 (

-> partition p0 values less than (1990)

-> partition p1 values less than (2000)

-> partition p2 values less than MAXVALUE

->)

Query OK, 0 rows affected (0.24 sec)

Mysql > system ls-lh / var/lib/mysql/test/ts*

-rw-rw----. 1 mysql mysql 8.4K Nov 4 15:44 / var/lib/mysql/test/ts.frm

-rw-rw----. 1 mysql mysql 96 Nov 4 15:44 / var/lib/mysql/test/ts.par

-rw-rw----. 1 mysql mysql 96K Nov 4 15:44 / var/lib/mysql/test/ts#P#p0#SP#p0sp0.ibd

-rw-rw----. 1 mysql mysql 96K Nov 4 15:44 / var/lib/mysql/test/ts#P#p0#SP#p0sp1.ibd

-rw-rw----. 1 mysql mysql 96K Nov 4 15:44 / var/lib/mysql/test/ts#P#p1#SP#p1sp0.ibd

-rw-rw----. 1 mysql mysql 96K Nov 4 15:44 / var/lib/mysql/test/ts#P#p1#SP#p1sp1.ibd

-rw-rw----. 1 mysql mysql 96K Nov 4 15:44 / var/lib/mysql/test/ts#P#p2#SP#p2sp0.ibd

-rw-rw----. 1 mysql mysql 96K Nov 4 15:44 / var/lib/mysql/test/ts#P#p2#SP#p2sp1.ibd

Mysql > create table ts (an int, b date)

-> partition by range (year (b)

-> subpartition by hash (to_days (b))

-> partition p0 values less than (1990) (

-> subpartition s0

-> subpartition S1

->)

-> partition p1 values less than (2000)

-> subpartition S2

-> subpartition S3

->)

-> partition p2 values less than MAXVALUE (

-> subpartition S4

-> subpartition S5

->)

Query OK, 0 rows affected (0.15 sec)

Mysql > create table ts (an int,b date) engine=innodb

-> partition by range (year (b)

-> subpartition by hash (to_days (b))

-> partition p0 values less than (2000) (

-> subpartition s0

-> data directory ='/ disk0/data'

-> index directory ='/ disk0/idx'

-> subpartition S1

-> data directory ='/ disk1/data'

-> index directory ='/ disk1/idx'

->)

-> partition p1 values less than (2010)

-> subpartition S2

-> data directory ='/ disk2/data'

-> index directory ='/ disk2/idx'

-> subpartition S3

-> data directory ='/ disk3/data'

-> index directory ='/ disk3/idx'

->)

-> partition p2 values less than maxvalue (

-> subpartition S4

-> data directory ='/ disk4/data'

-> index directory ='/ disk4/idx'

-> subpartition S5

-> data directory ='/ disk5/data'

-> index directory ='/ disk5/idx'

->)

Query OK, 0 rows affected, 6 warnings (0.32 sec)

Mysql > show warnings

+-+

+-+

+-+

6 rows in set (0.00 sec)

-- View the partition

MariaDB [test] > select * from INFORMATION_SCHEMA.PARTITIONS where table_name = 'employees'\ G

* * 1. Row *

TABLE_CATALOG: def

TABLE_SCHEMA: test

TABLE_NAME: employees

PARTITION_NAME: p0

SUBPARTITION_NAME: NULL

PARTITION_ORDINAL_POSITION: 1

SUBPARTITION_ORDINAL_POSITION: NULL

PARTITION_METHOD: RANGE

SUBPARTITION_METHOD: NULL

PARTITION_EXPRESSION: store_id

SUBPARTITION_EXPRESSION: NULL

PARTITION_DESCRIPTION: 6

TABLE_ROWS: 0

AVG_ROW_LENGTH: 0

DATA_LENGTH: 16384

MAX_DATA_LENGTH: NULL

INDEX_LENGTH: 0

DATA_FREE: 0

CREATE_TIME: 2016-07-04 00:42:16

UPDATE_TIME: NULL

CHECK_TIME: NULL

CHECKSUM: NULL

PARTITION_COMMENT:

NODEGROUP: default

TABLESPACE_NAME: NULL

* 2. Row * *

TABLE_CATALOG: def

TABLE_SCHEMA: test

TABLE_NAME: employees

PARTITION_NAME: p1

SUBPARTITION_NAME: NULL

PARTITION_ORDINAL_POSITION: 2

SUBPARTITION_ORDINAL_POSITION: NULL

PARTITION_METHOD: RANGE

SUBPARTITION_METHOD: NULL

PARTITION_EXPRESSION: store_id

SUBPARTITION_EXPRESSION: NULL

PARTITION_DESCRIPTION: 11

TABLE_ROWS: 2

AVG_ROW_LENGTH: 8192

DATA_LENGTH: 16384

MAX_DATA_LENGTH: NULL

INDEX_LENGTH: 0

DATA_FREE: 0

CREATE_TIME: 2016-07-04 00:42:16

UPDATE_TIME: NULL

CHECK_TIME: NULL

CHECKSUM: NULL

PARTITION_COMMENT:

NODEGROUP: default

TABLESPACE_NAME: NULL

* 3. Row * *

TABLE_CATALOG: def

TABLE_SCHEMA: test

TABLE_NAME: employees

PARTITION_NAME: p2

SUBPARTITION_NAME: NULL

PARTITION_ORDINAL_POSITION: 3

SUBPARTITION_ORDINAL_POSITION: NULL

PARTITION_METHOD: RANGE

SUBPARTITION_METHOD: NULL

PARTITION_EXPRESSION: store_id

SUBPARTITION_EXPRESSION: NULL

PARTITION_DESCRIPTION: 16

TABLE_ROWS: 2

AVG_ROW_LENGTH: 8192

DATA_LENGTH: 16384

MAX_DATA_LENGTH: NULL

INDEX_LENGTH: 0

DATA_FREE: 0

CREATE_TIME: 2016-07-04 00:42:16

UPDATE_TIME: NULL

CHECK_TIME: NULL

CHECKSUM: NULL

PARTITION_COMMENT:

NODEGROUP: default

TABLESPACE_NAME: NULL

* * 4. Row *

TABLE_CATALOG: def

TABLE_SCHEMA: test

TABLE_NAME: employees

PARTITION_NAME: p3

SUBPARTITION_NAME: NULL

PARTITION_ORDINAL_POSITION: 4

SUBPARTITION_ORDINAL_POSITION: NULL

PARTITION_METHOD: RANGE

SUBPARTITION_METHOD: NULL

PARTITION_EXPRESSION: store_id

SUBPARTITION_EXPRESSION: NULL

PARTITION_DESCRIPTION: MAXVALUE

TABLE_ROWS: 2

AVG_ROW_LENGTH: 8192

DATA_LENGTH: 16384

MAX_DATA_LENGTH: NULL

INDEX_LENGTH: 0

DATA_FREE: 0

CREATE_TIME: 2016-07-04 00:42:16

UPDATE_TIME: NULL

CHECK_TIME: NULL

CHECKSUM: NULL

PARTITION_COMMENT:

NODEGROUP: default

TABLESPACE_NAME: NULL

4 rows in set (0.00 sec)

-- View the partition table execution plan

MariaDB [test] > explain partitions select * from employees

+-+

+-+

+-+

1 row in set (0.00 sec)

MariaDB [test] > explain partitions select * from employees where store_id

Alter table employees add partition (partition p3 values less than (20))

Query OK, 0 rows affected (0.11 sec)

Records: 0 Duplicates: 0 Warnings: 0

MariaDB [test] > alter table employees add partition (partition p5 values less than maxvalue)

Query OK, 0 rows affected (0.18 sec)

Records: 0 Duplicates: 0 Warnings: 0

-- TRUNCATE specifies the partition

MariaDB [test] > alter table employees truncate partition p0

Query OK, 0 rows affected (0.12 sec)

-- Delete the specified partition

MariaDB [test] > alter table employees drop partition p0

Query OK, 0 rows affected (0.26 sec)

Records: 0 Duplicates: 0 Warnings: 0

-- split a partition into multiple partitions

MariaDB [test] > select partition_name,partition_expression,partition_description,table_rows from information_schema.partitions where table_schema=schema () and table_name='employees'

+-- +-+

+-- +-+

| | p1 | store_id | 11 | 2 | |

| | p2 | store_id | 16 | 2 | |

| | p3 | store_id | 20 | 0 | |

| | P5 | store_id | MAXVALUE | 0 | |

+-- +-+

4 rows in set (0.00 sec)

MariaDB [test] > ALTER TABLE employees

-> REORGANIZE PARTITION p1 INTO (

-> PARTITION n0 VALUES LESS THAN (5)

-> PARTITION N1 VALUES LESS THAN (11)

->)

Query OK, 2 rows affected (0.49 sec)

Records: 2 Duplicates: 0 Warnings: 0

MariaDB [test] > select partition_name,partition_expression,partition_description,table_rows from information_schema.partitions where table_schema=schema () and table_name='employees'

+-- +-+

+-- +-+

| | N0 | store_id | 5 | 0 | |

| | N1 | store_id | 11 | 2 | |

| | p2 | store_id | 16 | 2 | |

| | p3 | store_id | 20 | 0 | |

| | P5 | store_id | MAXVALUE | 0 | |

+-- +-+

5 rows in set (0.06 sec)

-merge multiple partitions into a single partition

MariaDB [test] > select partition_name,partition_expression,partition_description,table_rows from information_schema.partitions where table_schema=schema () and table_name='employees'

+-- +-+

+-- +-+

| | N0 | store_id | 5 | 0 | |

| | N1 | store_id | 11 | 2 | |

| | p2 | store_id | 16 | 2 | |

| | p3 | store_id | 20 | 0 | |

| | P5 | store_id | MAXVALUE | 0 | |

+-- +-+

5 rows in set (0.00 sec)

MariaDB [test] > ALTER TABLE employees

-> REORGANIZE PARTITION n0remenn1recoverp2 INTO (

-> PARTITION p2 VALUES LESS THAN (16)

Query OK, 4 rows affected (0.28 sec)

Records: 4 Duplicates: 0 Warnings: 0

MariaDB [test] > select partition_name,partition_expression,partition_description,table_rows from information_schema.partitions where table_schema=schema () and table_name='employees'

+-- +-+

+-- +-+

| | p2 | store_id | 16 | 4 | |

| | p3 | store_id | 20 | 0 | |

| | P5 | store_id | MAXVALUE | 0 | |

+-- +-+

3 rows in set (0.03 sec)

-reduce the number of hash partitions

MariaDB [test] > create table emp2 (id int not null,ename varchar (30)

-> hired date not null default '1970-01-01'

-> separated date not null default '9999-12-31'

-> job varchar (30) not null

-> store_id int not null)

-> partition by hash (store_id) partitions 4

Query OK, 0 rows affected (0.60 sec)

MariaDB [test] > alter table emp2 coalesce partition 2

Query OK, 0 rows affected (0.33 sec)

Records: 0 Duplicates: 0 Warnings: 0

MariaDB [test] > show create table emp2\ G

* * 1. Row *

Table: emp2

Create Table: CREATE TABLE `emp2` (

`id`int (11) NOT NULL

`ename` varchar (30) DEFAULT NULL

`hired`date NOT NULL DEFAULT '1970-01-01'

`roomated`date NOT NULL DEFAULT '9999-12-31'

`job` varchar (30) NOT NULL

`store_ id` int (11) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8

/ * 50100 PARTITION BY HASH (store_id)

PARTITIONS 2 * /

1 row in set (0.00 sec)

Increase the number of hash partitions

MariaDB [test] > alter table emp2 add partition partitions 5

Query OK, 0 rows affected (.54 sec)

Records: 0 Duplicates: 0 Warnings: 0

MariaDB [test] > show create table emp2\ G

* * 1. Row *

Table: emp2

Create Table: CREATE TABLE `emp2` (

`id`int (11) NOT NULL

`ename` varchar (30) DEFAULT NULL

`hired`date NOT NULL DEFAULT '1970-01-01'

`roomated`date NOT NULL DEFAULT '9999-12-31'

`job` varchar (30) NOT NULL

`store_ id` int (11) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8

/ * 50100 PARTITION BY HASH (store_id)

PARTITIONS 7 * /

1 row in set (0.00 sec)

Exchange data between tables and partitions

Mysql > create table e (

-> id int not null

> fname varchar (30)

> lname varchar (30)

->)

-> partition by range (id)

-> partition p0 values less than (50)

-> partition p1 values less than

-> partition p2 values less than

-> partition p3 values less than (MAXVALUE)

->)

Query OK, 0 rows affected (0.32 sec)

Mysql > insert into e values (1669, "Jim", "Smith"), (337, "Mary", "Jones"), (16, "Frank", "White"), (2005, "Linda", "Black")

Query OK, 4 rows affected (0.01sec)

Records: 4 Duplicates: 0 Warnings: 0

Mysql > commit

Query OK, 0 rows affected (0.01 sec)

Create an exchange table

Mysql > create table e2 like e

Query OK, 0 rows affected (0.29 sec)

Mysql > show create table E2\ G

* * 1. Row *

Table: e2

Create Table: CREATE TABLE `e2` (

`id`int (11) NOT NULL

`fname` varchar (30) DEFAULT NULL

`lname` varchar (30) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1

/ * 50100 PARTITION BY RANGE (id)

(PARTITION p0 VALUES LESS THAN (50) ENGINE = InnoDB

PARTITION p1 VALUES LESS THAN (100) ENGINE = InnoDB

PARTITION p2 VALUES LESS THAN (150) ENGINE = InnoDB

PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) * /

1 row in set (0.00 sec)

Change a partition table to a regular table

Mysql > alter table e2 remove partitioning

Query OK, 0 rows affected (0.25 sec)

Records: 0 Duplicates: 0 Warnings: 0

Mysql > show create table E2\ G

* * 1. Row *

Table: e2

Create Table: CREATE TABLE `e2` (

`id`int (11) NOT NULL

`fname` varchar (30) DEFAULT NULL

`lname` varchar (30) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

The data in partition p0 of table e is moved to table e2, and the data in partition p0 is moved to table e2.

Mysql > alter table e exchange partition p0 with table e2

Query OK, 0 rows affected (0.17 sec)

Mysql > select partition_name,table_rows from information_schema.partitions where table_name='e'

+-+ +

| | partition_name | table_rows |

+-+ +

| | p0 | 0 | |

| | p1 | 0 |

| | p2 | 0 | |

| | p3 | 2 | |

+-+ +

4 rows in set (0.00 sec)

Mysql > select * from e2

+-- +

+-- +

+-- +

1 row in set (0.00 sec)

-- query the specified partition

MariaDB [test] > select * from employees partition (p1)

+-- +

+-- +

| | 2 | Lily | Berg | 1970-01-01 | 9999-12-31 | 20 | 7 |

| | 3 | Lucy | Phynix | 1970-01-01 | 9999-12-31 | 20 | 10 |

+-- +

2 rows in set (0.00 sec)

-convert a non-partitioned table to a partitioned table

MariaDB [test] > CREATE TABLE employees2 (

-> id INT NOT NULL

> fname VARCHAR (30)

> lname VARCHAR (30)

-> hired DATE NOT NULL DEFAULT '1970-01-01'

-> separated DATE NOT NULL DEFAULT '9999-12-31'

-> job_code INT NOT NULL

-> store_id INT NOT NULL

->)

Query OK, 0 rows affected (0.08 sec)

MariaDB [test] > insert into employees2 (id,fname,lname,job_code,store_id) values

Query OK, 1 row affected (0.00 sec)

MariaDB [test] > insert into employees2 (id,fname,lname,job_code,store_id) values (2)

Query OK, 1 row affected (0.03 sec)

MariaDB [test] > insert into employees2 (id,fname,lname,job_code,store_id) values (2)

Query OK, 1 row affected (0.04 sec)

MariaDB [test] > insert into employees2 (id,fname,lname,job_code,store_id) values (3)

Query OK, 1 row affected (0.06 sec)

MariaDB [test] > insert into employees2 (id,fname,lname,job_code,store_id) values (4)

Query OK, 1 row affected (0.00 sec)

MariaDB [test] > insert into employees2 (id,fname,lname,job_code,store_id) values (5)

Query OK, 1 row affected (0.00 sec)

MariaDB [test] > insert into employees2 (id,fname,lname,job_code,store_id) values (6)

Query OK, 1 row affected (0.02 sec)

MariaDB [test] > select * from employees2

+-- +

+-- +

| | 1 | John | Terry | 1970-01-01 | 9999-12-31 | 10 | 100 |

| | 2 | Tom | Carl | 1970-01-01 | 9999-12-31 | 10 | 1 |

| | 2 | Lily | Berg | 1970-01-01 | 9999-12-31 | 20 | 7 |

| | 3 | Lucy | Phynix | 1970-01-01 | 9999-12-31 | 20 | 10 |

| | 4 | Bill | Jones | 1970-01-01 | 9999-12-31 | 20 | 15 |

| | 5 | Jill | Deco | 1970-01-01 | 9999-12-31 | 30 | 12 | |

| | 6 | Emily | Aaron | 1970-01-01 | 9999-12-31 | 30 | 20 |

+-- +

7 rows in set (0.00 sec)

MariaDB [test] > alter table employees2

-> PARTITION BY RANGE (store_id)

-> PARTITION p0 VALUES LESS THAN (6)

PARTITION p1 VALUES LESS THAN (11)

PARTITION p2 VALUES LESS THAN (16)

-> PARTITION p3 VALUES LESS THAN MAXVALUE

->)

Query OK, 7 rows affected (.59 sec)

Records: 7 Duplicates: 0 Warnings: 0

-- Test the storage of null values in the partition

Null values are treated as minimum values in RANGE partitions; null values must appear in enumerated lists in LIST partitions; and null values are treated as zero values in HASH/KEY partitions

MariaDB [test] > create table tb_range (id int,name varchar (5))

-> partition by range (id)

-> (

-> partition p0 values less than (- 6)

-> partition p1 values less than (0)

-> partition p2 values less than (1)

-> partition p3 values less than maxvalue

->)

Query OK, 0 rows affected (0.69 sec)

MariaDB [test] > insert into tb_range values (null,'null')

Query OK, 1 row affected (0.02 sec)

MariaDB [test] > select partition_name,partition_expression,partition_description,table_rows from information_schema.partitions where table_schema=schema () and table_name='tb_range'

+-- +-+

+-- +-+

| | p0 | id |-6 | 1 | |

| | p1 | id | 0 | 0 | |

| | p2 | id | 1 | 0 | |

| | P3 | id | MAXVALUE | 0 | |

+-- +-+

4 rows in set (0.00 sec)

MariaDB [test] > create table tb_list (id int,name varchar (5))

-> partition by list (id)

-> (

-> partition p1 values in (0)

-> partition p2 values in (1)

->)

Query OK, 0 rows affected (0.15 sec)

MariaDB [test] > insert into tb_list values (null,'null')

ERROR 1526 (HY000): Table has no partition for value NULL

MariaDB [test] > insert into tb_list values (null,'null')

ERROR 1526 (HY000): Table has no partition for value NULL

MariaDB [test] > create table tb_hash (id int,name varchar (5))

-> partition by hash (id)

-> partitions 2

Query OK, 0 rows affected (0.13 sec)

MariaDB [test] > insert into tb_hash values (null, 'null')

Query OK, 1 row affected (0.01sec)

MariaDB [test] > select partition_name,partition_expression,partition_description,table_rows from information_schema.partitions where table_schema=schema () and table_name='tb_hash'

+-- +-+

+-- +-+

| | p0 | id | NULL | 1 | |

| | p1 | id | NULL | 0 | |

+-- +-+

2 rows in set (0.00 sec)

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.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.