MySQL optimized table partition

Reproduced at: https://www.bilibili.com/video/BV1E7411q7Nx?p=1

1, RANGE partition

Multiple rows are assigned to partitions based on column values belonging to a given continuous interval.
These intervals shall be continuous and shall not overlap each other. They shall be defined by using the VALUES LESS THAN operator.

-- RANGE Partition case
create table t_student
(
    id varchar(50) not null,
    name varchar(20),
    age int,
    primary key (id,age)
)
partition by range(age)
(
    partition p01 VALUES less than (10),
    partition p02 VALUES less than (20),
    partition p03 VALUES less than (30),
    partition p04 VALUES less than (MAXVALUE)
)；

2, LIST partition

It is similar to partitioning by RANGE, except that the LIST partition is selected based on the column value matching a value in a discrete value set.
LIST partitions are realized by using "PARTITION BY LIST(expr)", where "expr" is a column value or an expression based on a column value and returns an integer value, and then each partition is defined by "VALUES IN (value list)", where "value list" is a comma separated integer LIST.

-- LIST Partition case
-- LIST Zoning case for a comprehensive online store,There are many kinds of goods. We can according to the goods ID conduct range partition,It can also be divided into zones according to the type of goods. In this example, LIST Zoning gives us more choices.
create table t_commodity 
(
    id varchar(50) not null,
    cid int,
    name varchar(20),
    date datetime
)
partition by list (cid) 
(
    partition po1 values in (1,2,3),
    partition po2 values in (4,5,6), 
    partition po3 values in (7,8,9) 
);

3, HASH partition

Partitions selected based on the return value of a user-defined expression that uses the column values of the rows to be inserted into the table. This function can contain any valid expression in MySQL that produces non negative integer values.
To split a table using HASH partitioning, add a "PARTITION BY HASH (expr)" sub ten day on the CREATE TABLE statement, where "expr" is an expression that returns an integer. It can simply be the name of a column whose field type is MySQL integer. In addition, you may need to add a "PARTITIONS num" clause later, where num is a non negative integer indicating the number of partitions the table will be divided into. If there is no "PARTITIONS num" clause, the number of partitions defaults to 1.
The advantage of using HASH partition is that the data is evenly distributed.

-- HASH Partition case
-- in addition,stay MySQL Cluster in,Partition behavior is automatic. By default,Number and of partitions ndb node Same quantity. Usually, when the number of nodes is large,By configuring the number of partitions and node group Match to adjust.
create table t_company1 
(
  id varchar(50) not null,
  cid int,
  name varchar(20),
  date datetime
) 
partition by hash (cid)
partitions 4;

4, LINEAR HASH partition

The difference between linear hash and conventional hash is that the linear hash function uses a linear powers of two algorithm, while the conventional hash uses the modulus of the hash function value.
The advantage of linear hash partitioning is that adding, deleting, merging and splitting partitions will become faster, which is conducive to processing tables containing extremely large amounts of (1T) data. However, the linear hashing algorithm of MysQL leads to uneven data distribution compared with conventional hashing, which is easy to produce "hotspot nodes".
For the LINEAR HASH algorithm, see the official MySQL document: http://dev.mysql.com/doc/refman/5.1/zh/partitioninq.html#partitioning-linear-hash

-- LINEAR HASH Partition case
-- The only syntactic difference between a linear hash partition and a regular hash partition is,stay"PARTITION BY" Add to Clause"LINEAR"keyword.
create table t_company2 
(
  id varchar(50) not null,
  cid int,
  name varchar(20),
  date datetime
) 
partition by linear hash (cid)
partitions 4;

5, KEY partition

Partitioning according to KEY is similar to partitioning according to HASH, except that the HASH partition uses user-defined expressions, and the HASH function of the KEY partition is provided by the MySQL server. MySQL Cluster uses function MD5() to realize KEY partition; For tables using other storage engines, the server uses its own internal HASH functions based on the same algorithm as PASSWORD().

-- KEY Partition case
create table t_company3 
(
  id varchar(50) not null,
  cid int,
  name varchar(20),
  date datetime
) 
partition by linear key (cid)
partitions 4;

6, Multi column partition

COLUMNS keyword allows string and date COLUMNS to be used as partition definition COLUMNS, as well as multiple COLUMNS to define a partition.

-- Multi column partition case
create table t_order
(
    a int,
    b int,
    c int
)
partition by range columns(a,b)
(
    partition p01 values less than (10,10),
    partition po2 values less than (10,20),
    partition p03 values less than (10,30), 
    partition p04 values less than (10,maxvalue),
    partition p05 values less than (maxvalue,maxvalue)
);

-- Multi column partition case
-- Multi column partition case the first partition is used to store female employees employed before 1990,The second partitioned storage unit is for 1990-2000 Female staff between,The third partition stores all the remaining female employees. For partitions p04 reach p06 ,Our strategy is the same,It's just male staff. The last partition is the control situation.
create table t_emplovees (
    emp_no int,
    birth_date datetime, 
    first_name varchar(20),
    last_name varchar(20),
    gender char(1),
    hire_date datetime
)
engine=myisam
partition by range columns(gender,hire_date)
(
    partition p01 values less than ('F','1990-01-01'),
    partition p02 values less than ('F','2000-01-01'),
    partition p03 values less than ('F',maxvalue),
    partition p04 values less than ('M', '1990-01-01'),
    partition p05 values less than ('M','2000-01-01'),
    partition p06 values less than ('M',maxvalue),
    partition p07 values less than (maxvalue,maxvalue)
);

7, Sub partition

A sub partition is a re partition of each partition in the partition table. Subpartitions can be used for particularly large tables to allocate data and indexes between multiple disks.

-- Sub partition case
create table t_order_details 
(
   id int, 
   udate datetime
 )
 partition by range(year(udate))
 subpartition by hash(to_days(udate))
 subpartitions 2
 (
   partition po values less than (1990),
   partition p1 values less than (2000),
   partition p2 values less than maxvalue
 )
-- Sub partition case
-- Save each sub partition on a different storage,Optimization 1/0 Performance.
-- notes: data directory Followed by the file location where the data is stored, index directory Followed by the file location where the index is stored
create table t_order_content
(
    id int,
    udate datetime
)
partition by range(year(udate))
subpartition by hash(to_days(udate))
(
　　partition p01 values less than (1990)
   (
   　　subpartition s0
   　　data directory ='/var/a/data'
   　　index directory = '/var/a/idx',
  　　 subpartition s1
  　　 data directory = '/var/b/data'
  　　 index directory = '/var/b/idx'
   ),
   partition p02 values less than (2000)
   (
　　subpartition s2
　　data directory ='/var/c/data'
　　index directory = '/var/c/idx',
　　subpartition s3
　　data directory = '/var/d/data'
　　index directory = '/var/d/idx'
　　)
);

Posted on Fri, 03 Dec 2021 15:40:09 -0500 by b0gner17

Programmer Help

MySQL optimized table partition

Hot Tags