Partitioning MySQL 5.6 Style

One of the major feature enhancement in MySQL 5.6 is partitioning.    We use partitioning on a table that stores archive data.  The data is partitioned via a date range.  Pre 5.6 we had to select the specified range for reporting.  Now with some code modification we can select the exact partition.


Here are some examples of the new features of partitioning:
CREATE TABLE testpart(id int(11) NOT NULL,
  fname varchar(25) NOT NULL,
  lname varchar(25) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (id)
(PARTITION p0 VALUES LESS THAN (1000) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (2000) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (3000) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */;


I have populated the table with some random data.


In 5.6 you can get information about partitions from the information_schema.partitions table.
SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'testpart';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0             |          3 |
| p1             |          2 |
| p2             |          9 |
| p3             |          7 |
+----------------+------------+
4 rows in set (0.01 sec)


You now select directly from a partition:
select * from testpart partition (p2);
+------+---------+----------+
| id   | fname   | lname    |
+------+---------+----------+
| 2000 | Mo      | Farah    |
| 2001 | Geraint | Thomas   |
| 2002 | Kat     | Copeland |
| 2003 | Laura   | Trott    |
| 2004 | Ben     | Ainsile  |
| 2005 | Nick    | Skelton  |
| 2006 | Jason   | Kenny    |
| 2007 | Carl    | Hester   |
| 2008 | Jade    | Jones    |
+------+---------+----------+
9 rows in set (0.00 sec)


Or you can select from multiple partitions:
select count(*) from testpart partition (p0,p1);

You can truncate a single partition:
alter table testpart truncate partition p1;
Query OK, 0 rows affected (0.06 sec)


SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'testpart';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0             |          3 |
| p1             |          0 |
| p2             |          9 |
| p3             |          7 |
+----------------+------------+
4 rows in set (0.00 sec)


Or you can truncate the all the partitions:
alter table testpart truncate partition all;
Query OK, 0 rows affected (0.08 sec)


SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'testpart';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0             |          0 |
| p1             |          0 |
| p2             |          0 |
| p3             |          0 |
+----------------+------------+
4 rows in set (0.00 sec)


A really cool feature of 5.6 partitioning is the ability to exchange a partition.  This feature allows you to take the data from a non partition table and 'exchange' the data into a specific partition.   If data exists in the partition table it will move that to the non partition table.

create table testpart_np like testpart;
Query OK, 0 rows affected (0.10 sec)

alter table testpart_np remove partitioning;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

select count(*) from testpart_np;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

insert into testpart_np values (11,'John','Doe');
Query OK, 1 row affected (0.00 sec)

commit;
Query OK, 0 rows affected (0.00 sec)

SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'testpart';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0             |          3 |
| p1             |          2 |
| p2             |          9 |
| p3             |          7 |
+----------------+------------+
4 rows in set (0.00 sec)

select * from testpart_np;
+----+-------+-------+
| id | fname | lname |
+----+-------+-------+
| 11 | John  | Doe   |
+----+-------+-------+
1 row in set (0.00 sec)

alter table testpart truncate partition p0;
Query OK, 0 rows affected (0.02 sec)

alter table testpart exchange partition p0 with table testpart_np;
Query OK, 0 rows affected (0.02 sec)

select * from testpart partition (p0);
+----+-------+-------+
| id | fname | lname |
+----+-------+-------+
| 11 | John  | Doe   |
+----+-------+-------+
1 row in set (0.01 sec)


An example of switching the database between the partition table and the non-partitioned table.

insert into testpart_np values (12,'Jane','Doe');
Query OK, 1 row affected (0.00 sec)

commit;
Query OK, 0 rows affected (0.00 sec)

select * from testpart_np;
+----+-------+-------+
| id | fname | lname |
+----+-------+-------+
| 12 | Jane  | Doe   |
+----+-------+-------+
1 row in set (0.00 sec)

insert into testpart_np values (13,'George','Washington');
Query OK, 1 row affected (0.00 sec)

commit;
Query OK, 0 rows affected (0.00 sec)

select * from testpart_np;
+----+--------+------------+
| id | fname  | lname      |
+----+--------+------------+
| 12 | Jane   | Doe        |
| 13 | George | Washington |
+----+--------+------------+
2 rows in set (0.00 sec)

select * from testpart partition (p0);
+----+-------+-------+
| id | fname | lname |
+----+-------+-------+
| 11 | John  | Doe   |
+----+-------+-------+
1 row in set (0.00 sec)

alter table testpart exchange partition p0 with table testpart_np;
Query OK, 0 rows affected (0.06 sec)

select * from testpart_np;
+----+-------+-------+
| id | fname | lname |
+----+-------+-------+
| 11 | John  | Doe   |
+----+-------+-------+
1 row in set (0.00 sec)

select * from testpart partition (p0);
+----+--------+------------+
| id | fname  | lname      |
+----+--------+------------+
| 12 | Jane   | Doe        |
| 13 | George | Washington |
+----+--------+------------+
2 rows in set (0.00 sec)


As you can see from the example above the data overwrites any existing data so be careful when you use it.

A bug with exchanging partitions is after you performed an exchange partition if you select from information_schema.partitions it shows the wrong results.

drop table testpart_np;
Query OK, 0 rows affected (0.01 sec)

create table testpart_np like testpart;
Query OK, 0 rows affected (0.10 sec)

alter table testpart_np remove partitioning;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

select count(*) from testpart_np;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

insert into testpart_np values (11,'John','Doe');
Query OK, 1 row affected (0.00 sec)

commit;
Query OK, 0 rows affected (0.00 sec)

SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'testpart';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0             |          3 |
| p1             |          2 |
| p2             |          9 |
| p3             |          7 |
+----------------+------------+
4 rows in set (0.00 sec)

select * from testpart_np;
+----+-------+-------+
| id | fname | lname |
+----+-------+-------+
| 11 | John  | Doe   |
+----+-------+-------+
1 row in set (0.00 sec)

alter table testpart truncate partition p0;
Query OK, 0 rows affected (0.02 sec)

alter table testpart exchange partition p0 with table testpart_np;
Query OK, 0 rows affected (0.02 sec)

select * from testpart partition (p0);
+----+-------+-------+
| id | fname | lname |
+----+-------+-------+
| 11 | John  | Doe   |
+----+-------+-------+
1 row in set (0.01 sec)

SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'testpart';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0             |          0 |
| p1             |          2 |
| p2             |          9 |
| p3             |          7 |
+----------------+------------+
4 rows in set (0.00 sec


The above value should be 1.

The workaround is:
- use innodb_stats_persistent = 0 (like 5.5)
- analyze table testpart;

No comments:

Post a Comment