MySQL 5.6'da Bölümleme

Uzunca bir süredir beklediğimiz MySQL 5.6 sürümü nihayet yayınlandı. Bu sürüm Oracle'ın satın almadan sonra çıkardığı ikinci sürüm olma özelliğini taşıyor. Yeni sürüm birçok alanda iyileştirmeler ve yenilikler içeriyor. Bu sürümde de İyileştirmelerin ve yeniliklerin ağırlıklı olarak InnoDB depolama motoru üzerinde gerçekleştirildiğini rahatlıkla söyleyebiliriz. Yeni sürümü bu adresten indirebilirsiniz.
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)