5.6 ile gelen önemli yeniliklerden biri bölümleme ile ilgili. Hem başarımı iyileştirilmiş hem de yönetimini kolaylaştırılmış. Örneğin artık belirli bir bölümü seçip üzerinde SELECT, INSERT, UPDATE ve DELETE cümlelleri çalıştırabiliyoruz. Bir bölümü başka bir tablo ile değiş tokuş edebiliyoruz. Bunlara ilişkin örnekleri aşağıda bulabilirsiniz.
mysql> create table employees (
-> id int not null auto_increment primary key,
-> first_name varchar(30),
-> last_name varchar(30),
-> salary float(8,2) )
-> partition by hash(id) partitions 5;
1 row in set (0.00 sec)
mysql> show table status like 'employees'\G
*************************** 1. row ***************************
Name: employees
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 5
Avg_row_length: 16384
Data_length: 81920
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: 1
Create_time: NULL
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options: partitioned
Comment:
1 row in set (0.00 sec)
mysql> show create table employees\G
*************************** 1. row ***************************
Table: employees
Create Table: CREATE TABLE `employees` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`first_name` varchar(30) DEFAULT NULL,
`last_name` varchar(30) DEFAULT NULL,
`salary` float(8,2) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY HASH (id)
PARTITIONS 5 */
1 row in set (0.00 sec)
mysql> select partition_name,table_rows from information_schema.partitions where table_name='employees';
+----------------+------------+
| partition_name | table_rows |
+----------------+------------+
| p0 | 0 |
| p1 | 0 |
| p2 | 0 |
| p3 | 0 |
| p4 | 0 |
+----------------+------------+
5 rows in set (0.01 sec)
mysql> insert into employees values
-> (1,'jack','bauer',24000),
-> (2,'jack','shephard',32000),
-> (3,'kate','austen',27500),
-> (4,'ben','linus',21750),
-> (5,'claire','littleton',43000),
-> (6,'jin','kwon',35000),
-> (7,'sun','kwon',26500),
-> (8,'john','locke',43500),
-> (9,'hugo','reyes',125000),
-> (10,'sayid','jarrah',12500),
-> (11,'james','ford',33500),
-> (12,'desmond','hume',25000),
-> (13,'charlie','pace',45000),
-> (14,'micheal','dawson',47000),
-> (15,'juliet','burke',37000);
Query OK, 15 rows affected, 15 warnings (0.07 sec)
Records: 15 Duplicates: 0 Warnings: 15
mysql> select partition_name,table_rows from information_schema.partitions where table_name='employees';
+----------------+------------+
| partition_name | table_rows |
+----------------+------------+
| p0 | 3 |
| p1 | 3 |
| p2 | 3 |
| p3 | 3 |
| p4 | 3 |
+----------------+------------+
5 rows in set (0.01 sec)
mysql> select * from employees partition(p0);
+----+------------+-----------+----------+
| id | first_name | last_name | salary |
+----+------------+-----------+----------+
| 5 | claire | littleton | 43000.00 |
| 10 | sayid | jarrah | 12500.00 |
| 15 | juliet | burke | 37000.00 |
+----+------------+-----------+----------+
3 rows in set (0.00 sec)
mysql> select * from employees partition(p1);
+----+------------+-----------+----------+
| id | first_name | last_name | salary |
+----+------------+-----------+----------+
| 1 | jack | bauer | 24000.00 |
| 6 | jin | kwon | 35000.00 |
| 11 | james | ford | 33500.00 |
+----+------------+-----------+----------+
3 rows in set (0.00 sec)
mysql> select * from employees partition(p2);
+----+------------+-----------+----------+
| id | first_name | last_name | salary |
+----+------------+-----------+----------+
| 2 | jack | shephard | 32000.00 |
| 7 | sun | kwon | 26500.00 |
| 12 | desmond | hume | 25000.00 |
+----+------------+-----------+----------+
3 rows in set (0.00 sec)
mysql> select * from employees partition(p3);
+----+------------+-----------+----------+
| id | first_name | last_name | salary |
+----+------------+-----------+----------+
| 3 | kate | austen | 27500.00 |
| 8 | john | locke | 43500.00 |
| 13 | charlie | pace | 45000.00 |
+----+------------+-----------+----------+
3 rows in set (0.00 sec)
mysql> select * from employees partition(p4);
+----+------------+-----------+-----------+
| id | first_name | last_name | salary |
+----+------------+-----------+-----------+
| 4 | ben | linus | 21750.00 |
| 9 | hugo | reyes | 125000.00 |
| 14 | micheal | dawson | 47000.00 |
+----+------------+-----------+-----------+
3 rows in set (0.00 sec)
mysql> select * from employees partition(p1,p3);
+----+------------+-----------+----------+
| id | first_name | last_name | salary |
+----+------------+-----------+----------+
| 1 | jack | bauer | 24000.00 |
| 6 | jin | kwon | 35000.00 |
| 11 | james | ford | 33500.00 |
| 3 | kate | austen | 27500.00 |
| 8 | john | locke | 43500.00 |
| 13 | charlie | pace | 45000.00 |
+----+------------+-----------+----------+
6 rows in set (0.00 sec)
mysql> insert into employees partition(p1) values (NULL,'shannon','rutherford',75000);
Query OK, 1 row affected (0.03 sec)
mysql> select * from employees partition(p1);
+----+------------+------------+----------+
| id | first_name | last_name | salary |
+----+------------+------------+----------+
| 1 | jack | bauer | 24000.00 |
| 6 | jin | kwon | 35000.00 |
| 11 | james | ford | 33500.00 |
| 16 | shannon | rutherford | 75000.00 |
+----+------------+------------+----------+
4 rows in set (0.00 sec)
mysql> insert into employees partition(p2) values (NULL,'ana','cortez',51250);
Query OK, 1 row affected (0.06 sec)
mysql> select * from employees partition(p2);
+----+------------+-----------+----------+
| id | first_name | last_name | salary |
+----+------------+-----------+----------+
| 2 | jack | shephard | 32000.00 |
| 7 | sun | kwon | 26500.00 |
| 12 | desmond | hume | 25000.00 |
| 17 | ana | cortez | 51250.00 |
+----+------------+-----------+----------+
4 rows in set (0.00 sec)
mysql> create table emp_backup like employees;
Query OK, 0 rows affected (1.61 sec)
mysql> alter table emp_backup remove partitioning;
Query OK, 0 rows affected (1.52 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table employees exchange partition p0 with table emp_backup ;
Query OK, 0 rows affected (0.52 sec)
mysql> select * from employees partition (p0);
Empty set (0.00 sec)
mysql> select * from emp_backup;
+----+------------+-----------+----------+
| id | first_name | last_name | salary |
+----+------------+-----------+----------+
| 5 | claire | littleton | 43000.00 |
| 10 | sayid | jarrah | 12500.00 |
| 15 | juliet | burke | 37000.00 |
+----+------------+-----------+----------+
3 rows in set (0.00 sec)
