Mysql Partition Introduction (vi)--HASH partition

A hash partition uses a primary key to ensure that the data is evenly distributed across a predetermined number of partitions. In the range or list partition. You must explicitly specify which partition the given data is written to, or set a column value to save; In the hash partition. MySQL has been prepared for you. You only need to specify the value of a column or expression based on the column values to hash and partition the number in which partition table.

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,    store_id INT)PARTITION BY HASH(store_id)PARTITIONS 4;

If you do not include partitions, then the default is 1 partitions

使用日期分区

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,    store_id INT)PARTITION BY HASH( YEAR(hired) )PARTITIONS 4;

Expr must be a non-const number, not a random number, or the number is different, but it can be determined.

How to determine the partition of a piece of data? Create a table first

CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE)    PARTITION BY HASH( YEAR(col3) )    PARTITIONS 4;

If you insert a record into T1, the col3 value is "2005-09-15", and then the allocation of its storage is determined as follows:

MOD(YEAR(‘2005-09-01‘),4)=  MOD(2005,4)=  1

1. Linear Hash Partitioning

Linear hash Partitioning uses a linear 2 power algorithm

REATE 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,    store_id INT)PARTITION BY LINEAR HASH( YEAR(hired) )PARTITIONS 4;

Given an expression of expr, the records in the partition are stored in a linear hash using the partition number n in the partition of NUM, where n is deduced according to the following algorithm:

1. Find a number greater than 2 that we call the power of this value V; it can be calculated as:

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

   (assuming a number of 13). Then log (2,13) is 3.7004397181411. CEILING (3.7004397181411) is 4, and V = Power (2,4), which is 16. ）

2. N = F (column_list) & (V-1).

3. N >= Num:

4. Set V = V/2
5. Set n = n & (V-1)

The benefits of linear hash partitioning are increased, dropped, merged, and split partitions can be faster and beneficial in processing containing very large numbers of data tables (million megabytes). The disadvantage is that it is unlikely that the data will be evenly distributed between partitions compared to the distributions obtained by regular hash partitions.

Mysql Partition Introduction (vi)--HASH partition