By: Sunil Kumar |
In:
MySQL
| Last Updated: 2017/09/20 
Partitioning is a database design technique which is used to improves performance, manageability, simplifies maintenance and reduce the cost of storing large amounts of data. Partitioning can be achieved without splitting your MySQL 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 are fewer data to scan.
Horizontal partitioning divides a table into multiple tables. Each table then contains the same number of columns, but fewer rows.
For example, a table that contains 1 billion rows could be partitioned horizontally into 12 tables, with each smaller table representing one month of data for a specific year.
Vertical partitioning involves creating tables with fewer columns and using additional tables to store the remaining columns. Normalization also involves this splitting of columns across tables, but vertical partitioning goes beyond that and partitions columns even when already normalized.
A popular and favorable application of partitioning is in a distributed database management system. The main purpose of partitioning is maintainability and performance. Your query performance will be much better as compared to the non-partitioned table.
Here we are discussing Hash partitioning and Range partitioning.
Before creating range partition be sure that-
1. The column on which you are partitioning(range partitioning) the table, is a part of every unique key in that table otherwise, you will not be able to make partition of that table.
2. You are partitioning the table on the column(s) which is/are most commonly used in your queries otherwise, there will be no benefit of creating partitions.
Suppose that we have a MySQL table with the following schema
CREATE TABLE `testing_user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(50) DEFAULT NULL, `email` varchar(100) DEFAULT NULL, `country` varchar(50) DEFAULT NULL, `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1
We will be creating some partitions on the MySQL table to understand how to create the partitions.
HASH Partitioning
Partitioning by HASH is used primarily to ensure an even distribution of data among a predetermined number of partitions. You have to specify how many partitions you want to create in a particular table.
In below query the table testing_user will be partitioned into 10 even size partitions
ALTER table testing_user PARTITION BY HASH(id) PARTITIONS 10;
Suppose you want to partition the table on the basis of year in which the user was created
ALTER table testing_user PARTITION BY HASH( YEAR(created) ) PARTITIONS 10;
The above query will create 10 even size partition of the table testing_user on the basis of id and year in which the user was created.
Range partitioning
A table that is partitioned by range is partitioned in such a way that each partition contains rows for which the partitioning expression value lies within a given range. Ranges should be contiguous but not overlapping and are defined using the VALUES LESS THAN operator.
ALTER TABLE testing_user PARTITION BY RANGE (id)
(
PARTITION p1 VALUES LESS THAN (100000),
PARTITION p2 VALUES LESS THAN (200000),
PARTITION p3 VALUES LESS THAN (300000),
PARTITION p4 VALUES LESS THAN (400000),
PARTITION p5 VALUES LESS THAN (500000),
PARTITION p6 VALUES LESS THAN (600000)
PARTITION p7 VALUES LESS THAN MAXVALUE
);
I am the owner of acmeextension. I am a passionate writter and reader. I like writting technical stuff and simplifying complex stuff.
Know More
Comments
Very nice and practical approach of explanation. There should be some explanation about table operations (for both DDL & DML) after partitioning.
What impact will be there on ALTER TABLE, INSERT TABLE, UPDATE TABLE, DELETE FROM TABLE, SELECT TABLE, SELECT WITH JOINS. If these are explained as well, it will be complete. Still I loved it. Thank you Sunil.