Partitioning in MySQL

Partitioning in MySQL

MySQL partitioning is about altering – ideally, optimizing – the way the database engine physically stores data. It allows you to distribute portions of table data (a.k.a. partitions) across the file system based on a set of user-defined rules (a.k.a. the “partitioning function”). In this way, if the queries you perform access only a fraction of table data and the partitioning function is properly set, there will be less to scan and queries will be faster. 

Enable and disable partitioning support :

•To enable partitioning (if you are compiling MySQL 5.6 from source), the build must be configured with the -DWITH_PARTITION_STORAGE_ENGINE option.

•To disable partitioning support, you can start the MySQL Server with the --skip-partition option, in which case the value of have_partitioning is DISABLED.

Partition_options:

PARTITION BY

{ [LINEAR] HASH(expr)

| [LINEAR] KEY(column_list)

| RANGE(expr)

| LIST(expr) }

[PARTITIONS num]

[SUBPARTITION BY

{ [LINEAR] HASH(expr)

| [LINEAR] KEY(column_list) }

[SUBPARTITIONS num]

]

[(partition_definition[, partition_definition] ...)

partition_definition:

PARTITION partition_name

[VALUES

{LESS THAN {(expr) | MAXVALUE}

|

IN (value_list)}]

[[STORAGE] ENGINE [=] engine_name]

[COMMENT [=] 'comment_text' ]

[DATA DIRECTORY [=] 'data_dir']

[INDEX DIRECTORY [=] 'index_dir']

[MAX_ROWS [=] max_number_of_rows]

[MIN_ROWS [=] min_number_of_rows]

[TABLESPACE [=] tablespace_name]

[NODEGROUP [=] node_group_id]

[(subpartition_definition [, subpartition_definition] ...)]

subpartition_definition:

SUBPARTITION logical_name

[[STORAGE] ENGINE [=] engine_name]

[COMMENT [=] 'comment_text' ]

[DATA DIRECTORY [=] 'data_dir']

[INDEX DIRECTORY [=] 'index_dir']

[MAX_ROWS [=] max_number_of_rows]

[MIN_ROWS [=] min_number_of_rows]

[TABLESPACE [=] tablespace_name]

[NODEGROUP [=] node_group_id]

Types of MySQL partitioning

Following types of partitioning are available in MySQL 5.6 :

•RANGE Partitioning

•LIST Partitioning

•COLUMNS Partitioning

•HASH Partitioning

•KEY Partitioning

•Subpartitioning

MySQL RANGE Partitioning

In MySQL, RANGE partitioning mode allows us to specify various ranges for which data is assigned. Ranges should be contiguous but not overlapping, and are defined using the VALUES LESS THAN operator. In the following example, sale_mast table contains four columns bill_no, bill_date, cust_code and amount.

Let create the table :

mysql> CREATE TABLE sale_mast (bill_no INT NOT NULL, bill_date TIMESTAMP NOT NULL,

cust_code VARCHAR(15) NOT NULL, amount DECIMAL(8,2) NOT NULL) 

PARTITION BY RANGE (UNIX_TIMESTAMP(bill_date))(

PARTITION p0 VALUES LESS THAN (UNIX_TIMESTAMP('2013-04-01')),

PARTITION p1 VALUES LESS THAN (UNIX_TIMESTAMP('2013-07-01')),

PARTITION p2 VALUES LESS THAN (UNIX_TIMESTAMP('2013-10-01')),

PARTITION p3 VALUES LESS THAN (UNIX_TIMESTAMP('2014-01-01')));

Query OK, 0 rows affected (1.50 sec)

Now insert some records in sale_mast table :

mysql> INSERT INTO sale_mast VALUES (1, '2013-01-02', 'C001', 125.56),

(2, '2013-01-25', 'C003', 456.50),

(3, '2013-02-15', 'C012', 365.00),

(4, '2013-03-26', 'C345', 785.00),

(5, '2013-04-19', 'C234', 656.00),

(6, '2013-05-31', 'C743', 854.00),

(7, '2013-06-11', 'C234', 542.00),

(8, '2013-07-24', 'C003', 300.00),

(8, '2013-08-02', 'C456', 475.20);

Query OK, 9 rows affected (0.07 sec)

Records: 9  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM sale_mast;

+---------+---------------------+-----------+--------+

| bill_no | bill_date           | cust_code | amount |

+---------+---------------------+-----------+--------+

|       1 | 2013-01-02 00:00:00 | C001      | 125.56 |

|       2 | 2013-01-25 00:00:00 | C003      | 456.50 |

|       3 | 2013-02-15 00:00:00 | C012      | 365.00 |

|       4 | 2013-03-26 00:00:00 | C345      | 785.00 |

|       5 | 2013-04-19 00:00:00 | C234      | 656.00 |

|       6 | 2013-05-31 00:00:00 | C743      | 854.00 |

|       7 | 2013-06-11 00:00:00 | C234      | 542.00 |

|       8 | 2013-07-24 00:00:00 | C003      | 300.00 |

|       9 | 2013-08-02 00:00:00 | C456      | 475.20 |

+---------+---------------------+-----------+--------+

9 rows in set (0.00 sec)

Here is the partition status of sale_mast table:

mysql> SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='sale_mast';

+----------------+------------+

| PARTITION_NAME | TABLE_ROWS |

+----------------+------------+

| p0             |          4 |

| p1             |          3 |

| p2             |          2 |

| p3             |          0 |

+----------------+------------+

4 rows in set (0.02 sec)

Drop a MySQL partition

If you feel some data are useless in a partitioned table you can drop one or more partition(s). To delete all rows from partition p0 of sale_mast, you can use the following statement :

MySQL> ALTER TABLE sale_mast TRUNCATE PARTITION p0;

Query OK, 0 rows affected (0.49 sec)

mysql> SELECT * FROM sale_mast;

+---------+---------------------+-----------+--------+

| bill_no | bill_date           | cust_code | amount |

+---------+---------------------+-----------+--------+

|       5 | 2013-04-19 00:00:00 | C234      | 656.00 |

|       6 | 2013-05-31 00:00:00 | C743      | 854.00 |

|       7 | 2013-06-11 00:00:00 | C234      | 542.00 |

|       8 | 2013-07-24 00:00:00 | C003      | 300.00 |

|       9 | 2013-08-02 00:00:00 | C456      | 475.20 |

+---------+---------------------+-----------+--------+

5 rows in set (0.01 sec)

Here is the partition status of sale_mast after dropping the partition p0 :

MySQL> SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS

WHERE TABLE_NAME='sale_mast';

+----------------+------------+

| PARTITION_NAME | TABLE_ROWS |

+----------------+------------+

| p0             |          0 |

| p1             |          3 |

| p2             |          2 |

| p3             |          0 |

+----------------+------------+

4 rows in set (0.05 sec)

MySQL LIST Partitioning

List partition allows us to segment data based on a pre-defined set of values (e.g. 1, 2, 3). This is done by using PARTITION BY LIST(expr) where expr is a column value and then defining each partition by means of a VALUES IN (value_list), where value_list is a comma-separated list of integers. In MySQL 5.6, it is possible to match against only a list of integers (and possibly NULL) when partitioning by LIST. In the following example, sale_mast2 table contains four columns bill_no, bill_date, agent_code, and amount. Suppose there are 11 agents represent three cities A, B, C these can be arranged in three partitions with LIST Partitioning as follows :

City

Agent ID

A 1, 2, 3 

B 4, 5, 6 

C 7, 8, 9, 10, 11 

Let create the table :

mysql> CREATE TABLE sale_mast2 (bill_no INT NOT NULL, bill_date TIMESTAMP NOT NULL,

agent_codE INT NOT NULL, amount INT NOT NULL)

PARTITION  BY LIST(agent_code) (

PARTITION pA VALUES IN (1,2,3),

PARTITION pB VALUES IN (4,5,6),

PARTITION pC VALUES IN (7,8,9,10,11));

Query OK, 0 rows affected (1.17 sec)

RANGE COLUMNS partitioning

RANGE COLUMNS partitioning is similar to range partitioning with some significant difference. RANGE COLUMNS accepts a list of one or more columns as partition keys. You can define the ranges using various columns of types (mentioned above) other than integer types.

•column_list is a list of one or more columns.

•value_list is a list of values and must be supplied for each partition definition.

•column list and in the value list defining each partition must occur in the same order

•The order of the column names in the partitioning column list and the value lists do not have to be the same as the order of the table column definitions in CREATE TABLE statement.

Here is an example :

mysql> CREATE TABLE table3 (col1 INT, col2 INT, col3 CHAR(5), col4 INT)

PARTITION BY RANGE COLUMNS(col1, col2, col3)

 (PARTITION p0 VALUES LESS THAN (50, 100, 'aaaaa'),

 PARTITION p1 VALUES LESS THAN (100,200,'bbbbb'),

 PARTITION p2 VALUES LESS THAN (150,300,'ccccc'),

 PARTITION p3 VALUES LESS THAN (MAXVALUE, MAXVALUE, MAXVALUE));

Query OK, 0 rows affected (1.39 sec)

In the above example -

•Table table3 contains the columns col1, col2, col3, col4

•The first three columns have participated in partitioning COLUMNS clause, in the order col1, col2, col3.

•Each value list used to define a partition contains 3 values in the same order and (INT, INT, CHAR(5)) form.

LIST COLUMNS partitioning

LIST COLUMNS accepts a list of one or more columns as partition keys.You can use various columns of data of types other than integer types as partitioning columns. You can use string types, DATE, and DATETIME columns

In a company there are agents in 3 cities, for sales and marketing purposes. We have organized the agents in 3 cities as shown in the following table :

City

Agent ID

A A1, A2, A3 

B B1, B2, B3 

C C1, C2, C3, C4, C5 

Let create a table with LIST COLUMNS partitioning based on the above information :

mysql> CREATE TABLE salemast ( agent_id VARCHAR(15), agent_name VARCHAR(50),

agent_address VARCHAR(100), city_code VARCHAR(10))

PARTITION BY LIST COLUMNS(agent_id) (

PARTITION pcity_a VALUES IN('A1', 'A2', 'A3'),

PARTITION pcity_b VALUES IN('B1', 'B2', 'B3'),

PARTITION pcity_c VALUES IN ('C1', 'C2', 'C3', 'C4', 'C5'));

Query OK, 0 rows affected (1.06 sec)

You can use DATE and DATETIME columns in LIST COLUMNS partitioning, see the following example :

CREATE TABLE sale_master (bill_no INT NOT NULL, bill_date DATE,

cust_code VARCHAR(15) NOT NULL, amount DECIMAL(8,2) NOT NULL) 

PARTITION BY RANGE COLUMNS (bill_date)(

PARTITION p_qtr1 VALUES LESS THAN ('2013-04-01'),

PARTITION p_qtr2 VALUES LESS THAN ('2013-07-01'),

PARTITION p_qtr3 VALUES LESS THAN ('2013-10-01'),

PARTITION p_qtr4 VALUES LESS THAN ('2014-01-01'));

MySQL HASH Partitioning

MySQL HASH partition is used to distribute data among a predefined number of partitions on a column value or expression based on a column value. This is done by using PARTITION BY HASH(expr) clause, adding in CREATE TABLE STATEMENT. In PARTITIONS num clause, num is a positive integer represents the number of partitions of the table. The following statement creates a table that uses hashing on the studetn_id column and is divided into 4 partitions :

MySQL>CREATE TABLE student (student_id INT NOT NULL,

class VARCHAR(8), name VARCHAR(40),

date_of_admission DATE NOT NULL DEFAULT '2000-01-01')

PARTITION BY HASH(student_id)

PARTITIONS 4;

Query OK, 0 rows affected (1.43 sec)

123456

It is also possible to make a partition based on the year in which a student was admitted. See the following statement :

MySQL> CREATE TABLE student (student_id INT NOT NULL,

class VARCHAR(8), class VARCHAR(8), name VARCHAR(40),

date_of_admission DATE NOT NULL DEFAULT '2000-01-01')

PARTITION BY HASH(YEAR(date_of_admission))

PARTITIONS 4;

Query OK, 0 rows affected (1.27 sec)

1234567

MySQL KEY Partitioning

MySQL KEY partition is a special form of HASH partition, where the hashing function for key partitioning is supplied by the MySQL server. The server employs its own internal hashing function which is based on the same algorithm as PASSWORD(). This is done by using PARTITION BY KEY, adding in CREATE TABLE STATEMENT. In KEY partitioning KEY takes only a list of zero or more column names. Any columns used as the partitioning key must comprise part or all of the table's primary key if the table has one. If there is a primary key in a table, it is used as partitioning key when no column is specified as the partitioning key. Here is an example :

MySQL> CREATE TABLE table1 ( id INT NOT NULL PRIMARY KEY,

fname  VARCHAR(25), lname VARCHAR(25))

PARTITION BY KEY()

PARTITIONS 2;

Query OK, 0 rows affected (0.84 sec)

If there is no primary key but there is a unique key in a table, then the unique key is used for the partitioning key :

MySQL> CREATE TABLE table2 ( id INT NOT NULL, fname  VARCHAR(25),

lname VARCHAR(25),

UNIQUE KEY (id))

PARTITION BY KEY()

PARTITIONS 2;

Query OK, 0 rows affected (0.77 sec)

MySQL Subpartitioning

Subpartitioning is a method to divide each partition further in a partitioned table. See the following CREATE TABLE statement :

CREATE TABLE table10 (BILL_NO INT, sale_date DATE, cust_code VARCHAR(15),

AMOUNT DECIMAL(8,2))

PARTITION BY RANGE(YEAR(sale_date) )

SUBPARTITION BY HASH(TO_DAYS(sale_date))

SUBPARTITIONS 4 (

PARTITION p0 VALUES LESS THAN (1990),

PARTITION p1 VALUES LESS THAN (2000),

PARTITION p2 VALUES LESS THAN (2010),

PARTITION p3 VALUES LESS THAN MAXVALUE

);

12345678910

In the above statement -

•The table has 4 RANGE partitions.

• Each of these partitions—p0, p1, p2 and p3—is further divided into 4 subpartitions.

•Therefore the entire table is divided into 4 * 4 = 16 partitions.

Here is the partition status of table10 :

mysql> SELECT PARTITION_NAME, TABLE_ROWS FROM

INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='stable';

+----------------+------------+

| PARTITION_NAME | TABLE_ROWS |

+----------------+------------+

| p0             |          0 |

| p0             |          0 |

| p0             |          0 |

| p0             |          0 |

| p1             |          0 |

| p1             |          0 |

| p1             |          0 |

| p1             |          0 |

| p2             |          0 |

| p2             |          0 |

| p2             |          0 |

| p2             |          0 |

| p3             |          0 |

| p3             |          0 |

| p3             |          0 |

| p3             |          0 |

+----------------+------------+

16 rows in set (0.16 sec)