w3resource

MySQL Partitioning

What is Partitioning?

Partitioning (a database design technique) improves performance, manageability, simplifies maintenance and reduce the cost of storing large amounts of data. Partitioning can be achieved without splitting tables by physically putting tables on individual disk drives. Partitioning allows tables, indexes, and index-organized tables to be subdivided into smaller pieces, therefore queries that access only a fraction of the data can run faster because there is fewer data to scan. There are two major forms of partitioning :

  • Horizontal Partitioning : Horizontal partitioning divides table rows into multiple partitions (based on a logic). All columns defined to a table are found in each partition, so no actual table attributes are missing. All the partition can be addressed individually or collectively. For example, a table that contains whole year sale transaction being partitioned horizontally into twelve distinct partitions, where each partition contains one month's data.
  • Vertical Partitioning : Vertical partitioning divides a table into multiple tables that contain fewer columns. Like horizontal partitioning, in vertical partitioning a query scan fewer data which increases query performance. For example, a table that contains a number of very wide text or BLOB columns that aren't addressed often being broken into two tables that have the most referenced columns in one table and the text or BLOB data in another.

MySQL partitioning

Version: MySQL 5.6

MySQL supports basic table partitioning but does not support vertical partitioning ( MySQL 5.6). This section describes in detail how to implement partitioning as part of your database.

By checking the output of the SHOW PLUGINS statement you will be sure whether your MySQL server supports the partition or not. See the following output :

Sample Output:

mysql> SHOW PLUGINS;
+----------------------------+----------+--------------------+---------+---------+
| Name                       | Status   | Type               | Library | License |
+----------------------------+----------+--------------------+---------+---------+
| binlog                     | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| MySQL_native_password      | ACTIVE   | AUTHENTICATION     | NULL    | GPL     |
| MySQL_old_password         | ACTIVE   | AUTHENTICATION     | NULL    | GPL     |
| sha256_password            | ACTIVE   | AUTHENTICATION     | NULL    | GPL     |
| CSV                        | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| MEMORY                     | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| MyISAM                     | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| MRG_MYISAM                 | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| ARCHIVE                    | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| BLACKHOLE                  | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| FEDERATED                  | DISABLED | STORAGE ENGINE     | NULL    | GPL     |
| InnoDB                     | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| INNODB_TRX                 | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_LOCKS               | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMPMEM              | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| ---------------------------| ------   | ------------------ | ----    | ---     |
| ---------------------------| ------   | ------------------ | ----    | ---     |
| ---------------------------| ------   | ------------------ | ----    | ---     |
| INNODB_SYS_TABLESPACES     | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_DATAFILES       | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| PERFORMANCE_SCHEMA         | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| partition                  | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
+----------------------------+----------+--------------------+---------+---------+
42 rows in set (0.21 sec)

MySQL 5.6 Community binaries include partitioning support.

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.

How to partition a table?

In MySQL you can partition a table using CREATE TABLE or ALTER TABLE command. See the following CREATE TABLE syntax :

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
(create_definition,...)
[table_options]
[partition_options]

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]

ALTER TABLE: Partition operations

ALTER TABLE statement can be used for adding, dropping, merging, and splitting partitions, and for performing partitioning maintenance. Here we have defined a nonpartitioned table:

CREATE TABLE sale_mast (
bill_no INT,
bill_date DATETIME
);

This table can be partitioned by HASH (or in another type), using the bill_no column as the partitioning key, into 6 (or other) partitions using ALTER TABLE statement :

ALTER TABLE t1
    PARTITION BY HASH(id)
    PARTITIONS 6;

Partition naming :

Names of partitions follow the rules of other MySQL identifiers such as databases, tables, constraint, stored procedure etc. Partition names are not case-sensitive.

Advantages of partitioning

  • During the scan operation, MySQL optimizer accesses those partitions that will satisfy a particular query. For example, a whole year sale records table may be broken up into 4 partitions (i.e. sale data from of Apr-Jun (partition p0), Jul-Sep (partition p1) , Oct-Dec (partition p2), Jam-Mar (partition p0)) . If a query is issued that contains sale data between Jul-Sep quarter, then it scans the partition p1 only instead of total table records and the query will complete much sooner.
  • Partitioning allows you to have more control over how data is managed inside the database. For example, you can drop specific partitions in a partitioned table where data loses its usefulness. The process of adding new data, in some cases, be greatly facilitated by adding one or more new partitions for storing that data using ALTER TABLE command.
  • In partitioning, it is possible to store more data in one table than can be held on a single disk or file system partition.
  • MySQL 5.6 supports explicit partition selection for queries. For example, SELECT * FROM table1 PARTITION (p0,p1) WHERE col1< 10 selects only those rows in partitions p0 and p1 that match the WHERE condition, this can greatly speed up queries
  • Partition selection also supports the data modification statements DELETE, INSERT, REPLACE, UPDATE, and LOAD DATA, LOAD XML.

Types of MySQL partitioning

Following types of partitioning are available in MySQL 5.6 :

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. This table can be partitioned by range in various of ways, depending on your requirement. Here we have used the bill_date column and decide to partition the table 4 ways by adding a PARTITION BY RANGE clause. In these partitions the range of the sale date (sale_date) are as of follow :

  • partition p0 ( sale between 01-01-2013 to 31-03-2013)
  • partition p1 ( sale between 01-04-2013 to 30-06-2013)
  • partition p2 ( sale between 01-07-2013 to 30-09-2013)
  • partition p3 ( sale between 01-10-2013 to 30-12-2013)

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)

In the above way you can partition the table based on sale amount (amount. In these partitions the range of the sale amount (amount) are as of follow :

  • partition p0 ( sale amount < 100 )
  • partition p1 ( sale amount < 500 )
  • partition p2 ( sale amount <1000 )
  • partition p3 ( sale amount<1500 )

Let create the table :

mysql> CREATE TABLE sale_mast1 (bill_no INT NOT NULL, bill_date TIMESTAMP NOT NULL, 
cust_codE VARCHAR(15) NOT NULL, amount INT NOT NULL) 
PARTITION  BY RANGE (amount) (
PARTITION p0 VALUES LESS THAN (100), 
PARTITION  p1 VALUES LESS THAN (500), 
PARTITION p2 VALUES LESS THAN (1000), 
PARTITION  p3 VALUES LESS THAN (1500)); 
Query OK, 0 rows affected (1.34 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)

MySQL COLUMNS Partitioning

In COLUMNS partitioning it is possible to use multiple columns in partitioning keys. There are two types of COLUMNS partitioning :

In addition, both RANGE COLUMNS partitioning and LIST COLUMNS partitioning support the use of non-integer columns for defining value ranges or list members. The permitted data types are shown in the following list:

Both RANGE COLUMNS partitioning and LIST COLUMNS partitioning support following data types for defining value ranges or list members.

  • All integer types: TINYINT, SMALLINT, MEDIUMINT, INT (INTEGER), and BIGINT.
  • DATE and DATETIME.

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.

Here is the basic syntax for creating a table partitioned by RANGE COLUMNS :

CREATE TABLE table_name
PARTITIONED BY RANGE COLUMNS(column_list) (
    PARTITION partition_name VALUES LESS THAN (value_list)[,
    PARTITION partition_name VALUES LESS THAN (value_list)][,
    ...]
) 

column_list:
    column_name[, column_name][, ...]

value_list:
    value[, value][, ...]
  • 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)

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)

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
);

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)

Previous: MySQL ALTER TABLE
Next: MySQL INSERT



Follow us on Facebook and Twitter for latest update.