MySQL HASH partition and mysqlhash Partition

MySQL HASH partition and mysqlhash Partition
Introduction

Based on the given number of partitions, data is allocated to different partitions. HASH partitions can only HASH integers, and non-integer fields can only be converted to Integers through expressions. The expression can be any valid function or expression in mysql. When a non-shaping HASH is inserted into the table, the calculation operation of the expression is performed, therefore, it is not recommended to use complex expressions to affect performance.

MYSQL supports two HASH partitions: Regular HASH and linear hash ).

 

 

I. Regular HASH

Regular hash is a modulo (%) operation based on the number of partitions. Insert the remainder to the specified partition.

CREATE TABLE tbhash (    id INT NOT NULL,    store_id INT)PARTITION BY HASH(store_id)PARTITIONS 4;

ALTER TABLE tbhash ADD INDEX ix_store_id(store_id);

INSERT INTO tbhash() VALUES(1,100),(1,101),(2,102),(3,103),(4,104);
SELECT PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,TABLE_ROWS,SUBPARTITION_NAME,SUBPARTITION_METHOD,SUBPARTITION_EXPRESSION FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='tbhash';

Among them, the modulo value of 100,104 to 4 is 0, so the two data are allocated to the p0 partition.

 

2. Time Type field

CREATE TABLE employees (    id INT NOT NULL,    hired DATE NOT NULL DEFAULT '1970-01-01',)PARTITION BY HASH( YEAR(hired) )PARTITIONS 4;

Regular hash partitions are very simple. By modulo, data can be evenly distributed to each partition, but the partitions are fixed during table creation. If the data migration for adding or shrinking partitions is large.

2. LINEAR HASH)

The only difference between linear hash and HASH is partition by linear hash.

CREATE TABLE tblinhash (    id INT NOT NULL,    hired DATE NOT NULL DEFAULT '1970-01-01')PARTITION BY LINEAR HASH( YEAR(hired) )PARTITIONS 6;

The linear HASH calculation principle is as follows:

Assume that the number of partitions is num = 6, and N indicates the partitions finally stored in the data.

Sep1:V= POWER (2, CEILING (LOG (2,num), LOG () is the base 2 logarithm of NUM, CEILING () is the rounded up, POWER () is the POWER of 2; if the value of num is a multiple of 2, The result calculated by this expression remains unchanged.

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

V = POWER (2, 3)

V = 8

Sep2: N = values & (V-1); & bitwise AND operation, the two values are converted to 2 for calculation and calculation, when both are 1 to 1; when num is a multiple of 2, The result calculated by V remains unchanged, then the result calculated by values & (V-1) = MOD (values/num) and time HASH modulo is consistent, in this case, this is the case only when the partition is a multiple of 2. Values is the value of YEAR (hired ).

Sep3: while N> = num

Sep3-1:N=N& (CEIL (V/2)-1)

For example:

1. When the inserted value is '2017-04-14'

    V = POWER(2, CEILING( LOG(2,6) )) = 8

N= YEAR ('1970-04-14 ') & (8-1)

= 2003 & 7

= 3

(3> = 6 is FALSE: record stored in partition #3), N is not greater than num, so it is stored in partition 3rd. Note that here 3 refers to P3, the partition number starts with P0.

2. When the inserted value is '2017-10-19'

   V= POWER (2, CEILING (LOG (2, 6) = 8

N= YEAR ('1970-10-19 ') & (8-1)

= 1998 & 7

= 6

(6> = 6 is TRUE: additional step required), because N> = num, the third step is required.

N = N & (CEILING (8/2)-1)

= 6 & 3

= 2

(2> = 6is FALSE: recored in partition #2), because 2 is not greater than 6, it is stored in 2nd partitions. Note that here 3 refers to P2, the partition number starts with P0.

INSERT INTO tblinhash() VALUES(1,'2003-04-14'),(2,'1998-10-19');SELECT PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,TABLE_ROWS,SUBPARTITION_NAME,SUBPARTITION_METHOD,SUBPARTITION_EXPRESSION FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='tblinhash';

EXPLAIN SELECT * FROM tblinhash WHERE hired='2003-04-14';

Iii. Partition Management

The principle of adding and shrinking partitions for regular HASH and linear HASH is the same. After adding and shrinking partitions, the original data will be re-distributed based on the number of existing partitions. HASH partitions cannot be deleted, so they cannot be deleted using the drop partition operation;

Only alter table... coalesce partition num can be used to merge partitions. The num here is the number of partitions subtracted;

You can use alter table... add partition partitions num to add partitions. Here, null is the number of PARTITIONS that were previously added.

1. merge partitions

Subtract 3 partitions

ALTER TABLE tblinhash COALESCE PARTITION 3;

SELECT PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,TABLE_ROWS,SUBPARTITION_NAME,SUBPARTITION_METHOD,SUBPARTITION_EXPRESSION FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='tblinhash';

Note: after the two partitions are subtracted, data is re-distributed based on the existing partitions. For example, '2017-04-14 ': POWER (2, CEILING (LOG (2, 3 ))) = 4,2003 & (4-1) = 3,3> = 3,3 & (CEILING (3/2)-1) = 1, therefore, the current '2017-04-14 'record is changed from the original p3 to p1

2. Add partitions

Add 4 partitions

ALTER TABLE tblinhash add PARTITION partitions 4;

SELECT PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,TABLE_ROWS,SUBPARTITION_NAME,SUBPARTITION_METHOD,SUBPARTITION_EXPRESSION FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='tblinhash';

After four partitions are added to three partitions, '2017-04-14 'is changed from p1 to p3, and another record is changed from p2 to p6.

 

4. Remove table partitions

ALTER TABLE tablenameREMOVE PARTITIONING ;

Note: using remove to remove a PARTITION is to only remove the definition of the PARTITION, and the data will not be deleted differently from the drop PARTITION. The latter will be deleted together with the data.

 

 

Refer:

RANGE partitioning: http://www.cnblogs.com/chenmh/p/5627912.html

LIST partition: http://www.cnblogs.com/chenmh/p/5643174.html

COLUMN partition: http://www.cnblogs.com/chenmh/p/5630834.html

KEY partition: http://www.cnblogs.com/chenmh/p/5647210.html

Subpartition: http://www.cnblogs.com/chenmh/p/5649447.html

Specify partition paths: http://www.cnblogs.com/chenmh/p/5644713.html

Partitioning index: http://www.cnblogs.com/chenmh/p/5761995.html

Partition Summary: http://www.cnblogs.com/chenmh/p/5623474.html

Summary

Regular HASH data distribution is more even and easier to understand. At present, we have not fully understood why linear HASH processing is faster when it shrinks and increases partitions, at the same time, the linear HASH data distribution is uneven.

 

 

Note: Author: pursuer. chen Blog: http://www.cnblogs.com/chenmh All essays on this site are original. You are welcome to repost them. However, you must indicate the source of the article and clearly give the link at the beginning of the article. Welcome to discussion