MySQL partition

Time:2021-2-26

MySQL partition

Advantages of partitioning

  1. Store more data
  2. To optimize the query, only scan one or more necessary partitions. For count () and sum (), only summarize the partition statistics
  3. For data that is out of date or does not need to be saved, partitioning is faster
  4. Data queries are distributed across multiple disks to achieve greater query throughput

Zoning overview

The introduction of partition key.

Query whether partition is supported

mysql> show variables like '%partition%';
Empty set (0.01 sec)

mysql> show variables like '%partition%';
+---------------------------------------+-------+
| Variable_name                         | Value |
+---------------------------------------+-------+
| innodb_adaptive_hash_index_partitions | 1     |
+---------------------------------------+-------+

MySQL supports the engines for creating partitions: MyISAM, InnoDB and memory, but does not support partitions: merge and CSV

In MySQL 5.1, all partitions of the same partition table must use the same storage engine, but in the same MySQL server or the same database, different storage engines can be used for different partition tables.

MySQL partition is applicable to all data and indexes of a table.

Setting engine engine must precede any other part of the CREATE TABLE statement

mysql> create table emp(empid int,salay decimal(7,2),birth_date date) 
engine=innodb 
partition by hash(month(birth_date)) 
partitions 6;
Query OK, 0 rows affected (0.06 sec)

Partition type

  1. Range partition: assign multiple rows to a partition based on the column values belonging to a given continuous interval.
  2. List partition: similar to range partition, the difference is that list partition is based on the value list partition, and range partition is based on the given continuous interval range partition
  3. Hash partition: assign data to different partitions based on the given number of partitions
  4. KEY**Partition * *: similar to range partition

In mysql5.1: range, list, hash partition key must be int type, key can also use blob, text. In mysql5.5, non integer type has been supported as partition key

Note when partitioning

  1. Either there is no primary key / unique key on a partitioned table, or the primary key / unique key of a partitioned table must contain a partition key. (otherwise, an error will be reported)
  2. Partition names are not case sensitive

Range partition

The data is divided into zones by using the value range, and the intervals should be continuous and can’t overlap each other.

In range partition, if the partition key is null, it will be treated as a minimum value.

mysql> create table emp_date(
    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 range (year(separated)) ( 
        partition p0 values less than (1995), 
        partition p1 values less than (2000), 
        partition p2 values less than (2005) 
    );
Query OK, 0 rows affected (0.04 sec)

If it exceeds the maximum partition range, an error will be reported, if there is a maximum valuemaxvalueI just want to get the bottom of it! Whatever you want!

mysql> alter table emp_date add partition (partition p3 values less than maxvalue);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

As mentioned earlier, range only supports int as the partition key, which is too inhumane. There are so many real business scenarios. MySQL 5.5 has improved this problem. The new range columns partition supports non integer partitions, so that the creation of date partitions does not need to be unnecessary through functions.no code no bb!

mysql> drop  table `emp_date`;
Query OK, 0 rows affected (0.04 sec)

mysql> create table emp_date(
    ->     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 range columns (separated) ( 
    ->         partition p0 values less than ('1995-01-01'), 
    ->         partition p1 values less than ('2000-01-01'), 
    ->         partition p2 values less than ('2005-01-01') 
    ->     );
Query OK, 0 rows affected (0.04 sec)

This kind of operation is not commonly used enough. How can we do it by day?

MySQL 5.1: partition date processing function onlyyear()andto_days()

MySQL 5.5: addedto_seconds()To convert the date to seconds.

Having said so much, which situations are the range partition functions particularly suitable for?

  1. When you need to delete expired data, for example, the data of a partition is meaningless, please executealter table emp_date drop partition p0Delete partition. For tens of thousands of data, it is much more efficient than running delete!
  2. Frequently run queries containing partition keys, MySQL can quickly find the corresponding partition, and scan in the corresponding partition.
mysql> insert into emp_date (id,ename,hired,separated,job,store_id) values('7934','miller','1995-01-01','1995-01-01','care',50);
Query OK, 1 row affected (0.01 sec)

mysql> explain partitions select count(1) from emp_date where store_id >=20\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: emp_date
   partitions: p0,p1,p2
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using where
1 row in set, 2 warnings (0.00 sec)

ERROR: 
No query specified

List partition

Feature: a collection of values of an enumeration list. Range is a set of continuous interval values

mysql> CREATE TABLE expenses ( 
    expense_date date NOT NULL, 
    category INT, amount DECIMAL ( 10, 3 ) 
) 
    PARTITION BY list ( category ) (
    -> PARTITION p0 VALUES IN ( 3, 5 ),
    -> PARTITION p1 VALUES IN ( 1, 11 ),
    -> PARTITION p2 VALUES IN ( 4, 9 ),
    -> PARTITION p3 VALUES IN ( 2 ) 
    -> );
Query OK, 0 rows affected (0.07 sec)

As mentioned earlier, list only supports integers. If you are MySQL 5.1, you have to create a separate table.

Mysql5.5 support non integer partition, really sweet!

mysql> CREATE TABLE expensess ( 
    -> expense_date date NOT NULL, 
    -> category varchar (30), 
    -> amount DECIMAL ( 10, 3 ) 
    -> ) 
    -> PARTITION BY list columns ( category ) (
    -> PARTITION p0 VALUES IN ('loading','food' ),
    -> PARTITION p1 VALUES IN ( 'ear', 'frist' ),
    -> PARTITION p2 VALUES IN ( 'hire','horse' ),
    -> PARTITION p3 VALUES IN ( 'fees' ) 
    -> );
Query OK, 0 rows affected (0.06 sec)

List partition, integer is list (expr), string is list columns (expr)

Hash partition

Hash partition is mainly used to disperse hot read to ensure that the data can be evenly distributed in a predetermined number of partitions. When hash partitioning a table, myqsl applies a hash function to the partitioning key to determine which of the N partitions the data should be placed in

1. Hash partition is divided into two types

  1. General partition (hash partition) – > modular algorithm
  2. Linear hash partition: an algorithm of linear 2

2. General partition

Grammar:PARTITION BY HASH(expr) PARTITIONS num

Expt: a column value or an expression that returns an integer value based on a column value

Num: non negative integer, divided into several areas

example:

CREATE TABLE emp_date (
    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;
According to expr, the partition is as follows:

N=MOD(expr,num)

Store_ Id = 234; modulus according to the formula: n = mod (234,4) = 2; distributed in the second partition

Test:

insert into emp_date values(1,'care','2010-10-10','9999-12-31','tos',234);

Look at the implementation plan:

mysql> EXPLAIN PARTITIONS SELECT * FROM emp_date WHERE store_id = 234\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: emp_date
   partitions: p2
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using where
1 row in set, 2 warnings (0.00 sec)

ERROR: 
No query specified

MySQL does not recommend the use of hash expressions involving multiple columns. Expr can be a non random non constant number, which needs to be calculated every time it is added, deleted or modified. There is a performance problem!

Advantages: the data is evenly distributed in each partition, which improves the efficiency
Disadvantages: when adding or merging partitions, the original average data needs to be redistributed through modulus, which is not suitable for the need to flexibly change partitions

3. Linear hash partition

example:

CREATE TABLE emp_dates (
    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 LINEAR HASH ( store_id ) 
partitions 4;

The partition to which the record will be saved is n of num partitions, where n is obtained according to the following algorithm: find the next power of 2 greater than num.. We call this value v

V = POWER(2, CEILING(LOG(2, num)))

N = F(column_list) & (V – 1)

When n > = num: · set V = ceil (V / 2) · set n = n & (V – 1)

Are you as confused as me? Let’s go!

Set 4 partitions, expr = 234
V = 4;
N = 234 & (4-1);
N = 2;
Because n < = 4; will be assigned to the second partition
Advantages and disadvantages of linear hash partition

Advantages: when maintaining partitions (including adding, deleting, merging and splitting partitions), MySQL can handle it more quickly

Disadvantages: compared with linear partition, the distribution of data among partitions is not balanced

Key partition

And hash partition type. difference:

  1. Hash partition allows the use of user-defined expressions, key partition does not allow the use of user-defined expressions, need to use hash function
  2. Hash partition only supports integer partition, key partition supports other types except blob and text as partition key
  3. You can create a key partition without specifying the partition key. The primary key is used by default

Syntax of key partition:partition by keys(expr); expr is a list of zero or more field names

mysql> CREATE TABLE `emp1` (
    -> 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 key  ( job ) partitions 4;
Query OK, 0 rows affected (0.04 sec)

Try not to specify the partition key, the premise must have a primary key!

mysql> 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,
    -> primary key (id)
    -> )
    -> 
    -> PARTITION BY key  ( ) partitions 4;
Query OK, 0 rows affected (0.05 sec)

Step back. You can have no primary key, but you must have a unique key,unique keyAt the same time, the only key must be non empty, you make an empty ghost to know which partition you want to save!

No primary key and no unique key, error!

Cannot execute on a partition table partitioned by keyalter table drop primary key

Similar to hash partition, when you can use the keyword linear key partition, the number of the partition is obtained through the 2 algorithm instead of modulus. When dealing with a large number of data, it can effectively disperse hot spots!

Subarea

A sub partition is a subdivision of each partition in the partition table. Also known as composite partitions.

MySQL 5.1 starts to support sub partitioning of tables that have been partitioned by range or list. Sub partition can use either hash partition or key partition.

mysql> CREATE TABLE `ts` (
    -> id int,
    ->   purchased date 
    -> )
    -> 
    -> PARTITION by range (year(purchased))
    -> SUBPARTITION by hash (TO_DAYS(purchased))
    -> SUBPARTITIONS 2
    -> (
    ->  PARTITION p0 VALUES LESS THAN (1900 ),
    ->  PARTITION p1 VALUES LESS THAN (2000 ),
    ->   PARTITION p2 VALUES LESS THAN MAXVALUE
    -> );
Query OK, 0 rows affected (0.06 sec)

There were three partitions, P0, P1, P2

Each partition is further divided into two sub partitions, a total of 6 partitions

Subarea is suitable for data records with a large amount of data

How MySQL partitions handle null values

MySQL does not prohibit the use of null values on partition keys.

Null value of the specific partition type

  1. Range partition, null will be taken as the minimum value
  2. List partition, null value must appear in the enumeration list, otherwise it will not be accepted (error)
  3. Hash / key partition, null value as 0;

Partition management

MySQL 5.1 provides commands to add, delete, redefine, merge and split partitions. Can be realized through alter table.

1. Range & list partition management

In the process of adding, deleting and redefining partitions, range and list partition are very similar.

1-1. Delete partition

Delete partition:ALTER TABLE DROP PARTITION

Deleting a partition will also delete its corresponding data

//New partition
create table emp_date(
    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 range (YEAR(separated)) ( 
        partition p0 values less than (1995), 
        partition p1 values less than (2000), 
        partition p2 values less than (2005),
                partition p3 values less than (2015),
                partition p4 values less than (2020)
    );

//Insert data
INSERT INTO `sakila`.`emp_date`(`id`, `ename`, `hired`, `separated`, `job`, `store_id`) VALUES (1, 'care', '1970-01-01', '1991-12-31', 'a', 1);
INSERT INTO `sakila`.`emp_date`(`id`, `ename`, `hired`, `separated`, `job`, `store_id`) VALUES (2, 'tony', '1970-01-01', '1996-12-31', 'b', 2);
INSERT INTO `sakila`.`emp_date`(`id`, `ename`, `hired`, `separated`, `job`, `store_id`) VALUES (3, 'pony', '1970-01-01', '2001-12-31', 'c', 3);
INSERT INTO `sakila`.`emp_date`(`id`, `ename`, `hired`, `separated`, `job`, `store_id`) VALUES (4, 'foly', '1970-01-01', '2006-12-31', 'd', 4);
INSERT INTO `sakila`.`emp_date`(`id`, `ename`, `hired`, `separated`, `job`, `store_id`) VALUES (5, 'quly', '1970-01-01', '2016-12-31', 'e', 5);

//Delete P2 partition
ALTER TABLE emp_date DROP PARTITION p2;
//View table creation statement
mysql> SHOW CREATE TABLE emp_date\G;
*************************** 1. row ***************************
       Table: emp_date
Create Table: CREATE TABLE `emp_date` (
  `id` int(11) NOT NULL,
  `ename` varchar(30) DEFAULT NULL,
  `hired` date NOT NULL DEFAULT '1970-01-01',
  `separated` 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 RANGE (year(separated))
(PARTITION p0 VALUES LESS THAN (1995) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (2000) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (2015) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (2020) ENGINE = InnoDB) */
1 row in set (0.00 sec)

ERROR: 
No query specified

Query the partition of the table

SELECT
partition_name part,
partition_expression expr,
partition_description descr,
table_rows
FROM
information_schema.PARTITIONS
WHERE
table_schema = SCHEMA()
AND table_name = 'emp_date';

+------+-----------------+-------+------------+
| part | expr            | descr | table_rows |
+------+-----------------+-------+------------+
| p0   | year(separated) | 1995  |          1 |
| p1   | year(separated) | 2000  |          1 |
| p3   | year(separated) | 2015  |          1 |
| p4   | year(separated) | 2020  |          1 |
+------+-----------------+-------+------------+

Because the list partition is enumeration type, if you do not create a new partition after deleting the partition, you can insert the deleted data directly, and an error will be reported!

1-2. Increase zoning

Add: ` alter table add partition

Didn’t you delete a P2 partition just now? Good! Now add it in

ALTER TABLE emp_date ADD PARTITION (PARTITION p2 VALUES less than (2005));
perhaps
ALTER TABLE emp_date ADD PARTITION (PARTITION p5 VALUES less than (2005));
//Error report
1493 - VALUES LESS THAN value must be strictly increasing for each partition, Time: 0.001000s
Can only be added from the end
ALTER TABLE emp_date ADD PARTITION (PARTITION p2 VALUES less than (2025));
This is the right way!
==Questions==

If you want to restore the partition of this interval, how do you do it?

For example, delete:

ALTER TABLE emp_date DROP PARTITION p2;

The corresponding range of this partition is: less than (2005)

What should I do now to restore the partition in this range?

Example: add partition to list

CREATE TABLE expenses ( 
    expense_date date NOT NULL, 
    category INT, amount DECIMAL ( 10, 3 ) 
) 
    PARTITION BY list ( category ) (
        PARTITION p0 VALUES IN ( 3, 5 ),
    PARTITION p1 VALUES IN ( 1, 11),
    PARTITION p2 VALUES IN ( 4, 9),
    PARTITION p3 VALUES IN ( 2 ) 
);

Add partition
ALTER TABLE expenses ADD PARTITION (PARTITION p4 values in (6,7,8));

List must pay attention to the enumeration value must be unique
ALTER TABLE expenses ADD PARTITION (PARTITION p5 values in (8));
(error report)
1495 - Multiple definition of same constant in list partitioning, Time: 0.000000s
1-3. Redefine partition

At the beginning, I found that the partition was not well defined, such as the range of P4 (2000 ~ 2015) of range partition. Later, the data of this partition was too large, so I need to re partition it into P3 (2000 ~ 2010) and P4 (2010 ~ 2020), provided that there was no P2 or P3 partition before!

Range split partition
//Original zoning
create table emp_date(
    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 range (YEAR(separated)) ( 
        partition p0 values less than (1995), 
        partition p1 values less than (2000), 
                partition p4 values less than (2020)
    );

//After a period of time, I found that the horizontal trough was all concentrated in P4 partition. How can I do that? Quickly redefine the partition

ALTER TABLE emp_date REORGANIZE PARTITION p4 INTO(
PARTITION p3 VALUES less than (2010),
PARTITION p4 VALUES less than (2020)
)
Range merge partition
//Partition before merging
CREATE TABLE `emp_date` (
  `id` int(11) NOT NULL,
  `ename` varchar(30) DEFAULT NULL,
  `hired` date NOT NULL DEFAULT '1970-01-01',
  `separated` 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 RANGE (YEAR(separated))
(PARTITION p0 VALUES LESS THAN (1995) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (2000) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (2010) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (2020) ENGINE = InnoDB) */;

 //Operation merging,
 P 0, p 1 and P 3 were combined into one region, p 1

 ALTER TABLE emp_date REORGANIZE PARTITION p0,p1,p3 INTO(
PARTITION p1 VALUES less than (2010)
)
List split partition
CREATE TABLE expenses ( 
    expense_date date NOT NULL, 
    category INT, amount DECIMAL ( 10, 3 ) 
) 
    PARTITION BY list ( category ) (
    PARTITION p0 VALUES IN ( 3, 5 ),
    PARTITION p1 VALUES IN ( 1, 11 ),
    PARTITION p2 VALUES IN ( 4, 9 ),
    PARTITION p3 VALUES IN ( 2 ),
        PARTITION p4 VALUES IN ( 6),
        PARTITION p5 VALUES IN ( 7,8 )
    );

Objective: partition P4 with values (6, 12)

Scheme 1: add new partitions as before
 ALTER TABLE expenses ADD PARTITION (PARTITION p6 VALUES IN ( 6,12 ));
 //Error report
 1495 - Multiple definition of same constant in list partitioning, Time: 0.000000s

 Scheme 2: add partition first, then redefine partition
 ALTER TABLE expenses ADD PARTITION (PARTITION p6 VALUES IN ( 12));

//P4, P5, P6 redefined to P4 (6,12) P5 (7,8)
 ALTER TABLE expenses REORGANIZE PARTITION p4,p5,p6 INTO(
PARTITION p4 VALUES IN (6,12),
PARTITION p5 VALUES IN (7,8)
)
Conclusion:

When redefining partitions:

  1. The original partition type cannot be changed by redefining the partition

  2. Range and list can only redefine adjacent partitions, and the redefined interval must cover the same interval as the original partition

2. Hash & key partition management

Hash & key partition management is similar, take hash as an example

CREATE TABLE emp(
    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;

//Reduce partitions
ALTER TABLE emp COALESCE PARTITION 2;

//Add partition
Coalesce cannot be used to increase the number of partitions
ALTER TABLE emp COALESCE PARTITION 8;
report errors
1508 - Cannot remove all partitions, use DROP TABLE instead, Time: 0.003000s

//Note that eight more partitions are added
ALTER TABLE emp add PARTITION PARTITIONS 8;

mysql> SHOW CREATE TABLE emp \G;
*************************** 1. row ***************************
       Table: emp
Create Table: CREATE TABLE `emp` (
  `id` int(11) NOT NULL,
  `ename` varchar(30) DEFAULT NULL,
  `hired` date NOT NULL DEFAULT '1970-01-01',
  `separated` 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 10 */
1 row in set (0.00 sec)

ERROR: 
No query specified

This work adoptsCC agreementReprint must indicate the author and the link of this article