MySQL Partition

What is table partitioning

Table partitioning is a way to divide a large table into smaller, more manageable parts without having to create separate tables for each part.
Partitioning can be achieved without splitting tables by physically putting tables on individual disk drives
Partitioning is not visible to end users, a partitioned table behaves like one logical table when queried.

Why wee need partitioning

Help speed up loading and archiving of data
Perform maintenance operations on individual partitions instead of the whole table
Improve query formance. Instead of scanning the whole table, now you only need to scan only one or a few partitions to find the expected record

Hash partitioning

Được sử dụng khi bạn muốn phân bố đồng đều data cho các partition
Để tạo HASH partition, thêm vào câu lệnh PARTITION BY HASH (expr) sau lệnh CREATE TABLE.expr là 1 expression trả về 1 integer. Ví dụ: PARTITION BY HASH (YEAR(date_created))

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;

Khi 1 record được insert, hệ thống sẽ chọn partition mà record sẽ được lưu dựa trên phép chia dư như sau: N = MOD(expr, num). num là số partition. Ví dụ:

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

Nếu chúng ta thêm 1 record mà giá trị của col3 là 2005-09-15 thì vị trí partition mà record sẽ được lưu được đánh giá như sau:

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

Vậy record sẽ được lưu vào partition số 1

Khi truy vấn hệ thống sẽ tìm partition k cho giá trị hash tương ứng k = MOD(expr, n) của record và tìm record trong parttion k đó thay vì scan toàn bộ bảng để tìm

EXPLAIN SELECT * FROM t1 where col3 = '2005-09-01' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: trb1
   partitions: p0
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10
        Extra: Using filesort

Tìm thấy record trong parttion p0

Linear Hash Partition

Về cơ bản giống HASH PARTITION nhưng LINEAR HASH PARTITION sử dụng thuật toán linear powers-of-two thay vì modulus như thông thường

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 LINEAR HASH( YEAR(hired) )
PARTITIONS 4;

Vị trí N partition mà record sẽ được lưu vào được mô tả dưới thuật toán sau:

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

Set N = expr & (V - 1).

While N >= num:

Set V = V / 2

Set N = N & (V - 1)

Giả sử chúng ta có bảng t1 với schema như sau

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

Chúng ta muốn insert record t1 có giá trị của col3 là '2003-04-14'. Thì partition N mà record được lưu được xác định như sau:

V = POWER(2, CEILING( LOG(2,6) )) = 8
N = YEAR('2003-04-14') & (8 - 1)
   = 2003 & 7
   = 3

(3 >= 6 is FALSE: record stored in partition #3)

The advantage in partitioning by linear hash is that the adding, dropping, merging, and splitting of partitions is made much faster, which can be beneficial when dealing with tables containing extremely large amounts (terabytes) of data. The disadvantage is that data is less likely to be evenly distributed between partitions as compared with the distribution obtained using regular hash partitioning.

Key partitioning

Hoạt động tương tự như HASH nhưng thay vì sử dụng expr do người dùng cung cấp, key partition sử dụng hàm hash được cung cấp bời hệ thống, thông thường là MD5

CREATE TABLE k1 (
    id INT NOT NULL PRIMARY KEY,
    name VARCHAR(20)
)
PARTITION BY KEY(id)
PARTITIONS 2;

KEY() có thể nhận 0 hoặc nhiều giá trị tên cột. Bất cứ tên cột nào được sư dụng phải là 1 thành phần trong các PRIMARY KEY của bảng. Nếu không có tên cột nào được cung cấp cho hàm KEY() thì PRIMARY KEY của bảng sẽ được sử dụng

Range partitioning

Được sử dụng khi muốn nhóm các giá record nằm trong 1 khoảng nào đó lại với nhau.
Giả sử số lượng đơn hàng của 1 cửa hàng trong những năm quá lớn khiến việc truy vấn thông tin của 1 đơn hàng mất rất nhiều thời gian. Vì vậy chúng ta đề ra giải pháp phân chia bảng đơn hàng thành 10 partition để khi truy vấn thông tin 1 đơn hàng, ta chỉ cần tìm thông tin đơn hàng trong partition tương ứng mà đơn hàng đó được lưu

CREATE TABLE bill (
    id INT NOT NULL,
    user_id VARCHAR(20) NOT NULL,
    product_id VARCHAR(20) NOT NULL,
    date_created TIMESTAMP NOT NULL,
    amount DECIMAL NOT NULL,
)
PARTITION BY RANGE ( UNIX_TIMESTAMP(date_created) ) (
    PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-01-01 00:00:00') ),
    PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-04-01 00:00:00') ),
    PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-07-01 00:00:00') ),
    PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-10-01 00:00:00') ),
    PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-01-01 00:00:00') ),
    PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-04-01 00:00:00') ),
    PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-07-01 00:00:00') ),
    PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-10-01 00:00:00') ),
    PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-01-01 00:00:00') ),
    PARTITION p9 VALUES LESS THAN (MAXVALUE)
)

List partitioning

Continental	CountryID
Asia	3, 5, 6, 9, 17
Europe	1, 2, 10, 11, 19, 20
Africa	4, 12, 13, 14, 18
North America	7, 8, 15, 16
South America	20, 25, 30, 44, 50

Giả sử chúng ta cần partition một bảng dữ liệu user theo vị trí địa lý lục địa của user

CREATE TABLE users (
    user_id VARCHAR(20) NOT NULL,
    name VARCHAR(50) NOT NULL,
    country_id INT NOT NULL,
)
PARTITION BY LIST ( country_id ) (
    PARTITION pAsia VALUES IN(3, 5, 6, 9, 17 ),
    PARTITION pEurope VALUES IN (1, 2, 10, 11, 19, 20),
    PARTITION pAfrica VALUES IN( 4, 12, 13, 14, 18) ),
    PARTITION pNAmerica VALUES IN (7, 8, 15, 16)),
    PARTITION pSAmerica VALUES IN (20, 25, 30, 44, 50))
)

Giả sử chúng ta cần insert 1 user

INSERT INTO users (user_id, name, country_id) VALUES ("1", "Peter Crouch", 20)

User này sẽ được lưu vào partition pEurope vì có country_id = 20 nằm trong (1, 2, 10, 11, 19, 20)

Reference

https://www.cathrinewilhelmsen.net/2015/04/12/table-partitioning-in-sql-server/

MySQL Documentation

What is table partitioning#

Why wee need partitioning#

Hash partitioning#

Linear Hash Partition#

Key partitioning#

Range partitioning#

List partitioning#

Reference#