About Mysql Partition

Server environment

1
2
3
4
5
$ cat /etc/issue
Ubuntu 14.04.5 LTS

$ mysqld -V
mysqld Ver 5.5.58-0ubuntu0.14.04.1 for debian-linux-gnu on x86_64 ((Ubuntu))

Check Mysql support

Mysql version is required at least 5.1 to using partition.
Check currency database version whether the partition is supported.

1
2
3
4
5
6
7
mysql> show variables like '%partition%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| have_partitioning | YES |
+-------------------+-------+
1 row in set (0.00 sec)

RANGE

Assign the records to different partition based on the range of store id(0-6,7-11,12-16,17-21,>21).

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE TABLE employees_range (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT NOT NULL,
store_id INT NOT NULL
)
partition BY RANGE (store_id) (
partition p0 VALUES LESS THAN (6),
partition p1 VALUES LESS THAN (11),
partition p2 VALUES LESS THAN (16),
partition p3 VALUES LESS THAN (21),
partition p4 VALUES LESS THAN MAXVALUE
);
  • Add partition can only increase by value, don’t use the MAXVALUE
  • If defined the MAXVALUE, partition can not be extended.

LIST

Assign the records to different partition based on the specified store ids.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE TABLE employees_list (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT,
store_id INT
)
PARTITION BY LIST(store_id) (
PARTITION pNorth VALUES IN (3,5,6,9,17),
PARTITION pEast VALUES IN (1,2,10,11,19,20),
PARTITION pWest VALUES IN (4,12,13,14,18),
PARTITION pCentral VALUES IN (7,8,15,16)
);

HASH

Assign the records to different partition based on customer expression’s return value.

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE employees_hash (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT,
store_id INT
)
PARTITION BY HASH(YEAR(hired))
PARTITIONS 4;

PARTITIONS 4: the number of partition is 4

KEY

Similar to the HASH partition, KEY partition only supports one or more columns.

1
2
3
4
5
6
7
8
9
10
CREATE TABLE employees_key (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT,
store_id INT
)
PARTITION BY HASH (id) PARTITIONS 10;

Created a total of 10 partitions
If Mysql configuration ‘innodb_file_per_table = 1’ is true, each partition table has its own separate file

  • Linear hash partition: When the number of linear hash partition is n times 2, the result of linear hash partition is consistent with the result of regular hash partition

Check Partition

1
2
3
4
5
6
7
8
9
10
11
mysql> SELECT PARTITION_NAME,TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'employees_range';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0 | 0 |
| p1 | 0 |
| p2 | 0 |
| p3 | 0 |
| p4 | 0 |
+----------------+------------+
5 rows in set (0.00 sec)

Increase partition in no partition table

Chekc users table structure

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> show create table users;
+-------+-----------------------------+
| Table | Create Table |
+-------+-----------------------------+
| users | CREATE TABLE `users` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
...
...
`created_at` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `country_age_income` (`country`,`age`,`sex`,`income`)
) ENGINE=MyISAM AUTO_INCREMENT=110305176 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
+-------+----------------------------+
1 row in set (0.00 sec)

Add partition

1
2
mysql> ALTER TABLE users PARTITION BY HASH (id) PARTITIONS 10;
ERROR 1503 (HY000): A UNIQUE INDEX must include all columns in the table's partitioning function

The partition filed id must be included in the primary key (id ,country_age_income). So remove the previous index for test.

1
2
3
mysql> drop index `country_age_income` on users;
Query OK, 10150175 rows affected (6 min 15.40 sec)
Records: 10150175 Duplicates: 0 Warnings: 0

Re-execute

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
mysql> ALTER TABLE users PARTITION BY HASH (id) PARTITIONS 10;
Query OK, 10150175 rows affected (49.95 sec)
Records: 10150175 Duplicates: 0 Warnings: 0

mysql> SELECT PARTITION_NAME,TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'users';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0 | 1015017 |
| p1 | 1015018 |
| p2 | 1015018 |
| p3 | 1015018 |
| p4 | 1015018 |
| p5 | 1015018 |
| p6 | 1015017 |
| p7 | 1015017 |
| p8 | 1015017 |
| p9 | 1015017 |
+----------------+------------+
11 rows in set (0.01 sec)

Selected a few id to test the partition

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
mysql> explain partitions select * from users where id = 230;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | users | p0 | const | PRIMARY | PRIMARY | 4 | const | 1 | |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)

mysql> explain partitions select * from users where id = 231;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | users | p1 | const | PRIMARY | PRIMARY | 4 | const | 1 | |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.01 sec)

mysql> explain partitions select * from users where id = 232;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | users | p2 | const | PRIMARY | PRIMARY | 4 | const | 1 | |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.01 sec)

Check users table partition file structure

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
$ ls -lh /var/lib/mysql/app/users*
-rw-rw---- 1 mysql mysql 17K /var/lib/mysql/app/users.frm
-rw-rw---- 1 mysql mysql 60 /var/lib/mysql/app/users.par
-rw-rw---- 1 mysql mysql 453M /var/lib/mysql/app/users#P#p0.MYD
-rw-rw---- 1 mysql mysql 10M /var/lib/mysql/app/users#P#p0.MYI
-rw-rw---- 1 mysql mysql 453M /var/lib/mysql/app/users#P#p1.MYD
-rw-rw---- 1 mysql mysql 10M /var/lib/mysql/app/users#P#p1.MYI
-rw-rw---- 1 mysql mysql 453M /var/lib/mysql/app/users#P#p2.MYD
-rw-rw---- 1 mysql mysql 10M /var/lib/mysql/app/users#P#p2.MYI
-rw-rw---- 1 mysql mysql 453M /var/lib/mysql/app/users#P#p3.MYD
-rw-rw---- 1 mysql mysql 10M /var/lib/mysql/app/users#P#p3.MYI
-rw-rw---- 1 mysql mysql 453M /var/lib/mysql/app/users#P#p4.MYD
-rw-rw---- 1 mysql mysql 10M /var/lib/mysql/app/users#P#p4.MYI
-rw-rw---- 1 mysql mysql 453M /var/lib/mysql/app/users#P#p5.MYD
-rw-rw---- 1 mysql mysql 10M /var/lib/mysql/app/users#P#p5.MYI
-rw-rw---- 1 mysql mysql 453M /var/lib/mysql/app/users#P#p6.MYD
-rw-rw---- 1 mysql mysql 10M /var/lib/mysql/app/users#P#p6.MYI
-rw-rw---- 1 mysql mysql 453M /var/lib/mysql/app/users#P#p7.MYD
-rw-rw---- 1 mysql mysql 10M /var/lib/mysql/app/users#P#p7.MYI
-rw-rw---- 1 mysql mysql 453M /var/lib/mysql/app/users#P#p8.MYD
-rw-rw---- 1 mysql mysql 10M /var/lib/mysql/app/users#P#p8.MYI
-rw-rw---- 1 mysql mysql 453M /var/lib/mysql/app/users#P#p9.MYD
-rw-rw---- 1 mysql mysql 10M /var/lib/mysql/app/users#P#p9.MYI

Specify the partition file directory

MYISAM storage engine can define the data file directory and index file directory separately.
INNODB storage engine can only define the the data directory.

Whether InnoDB or MyISAM, can not use alter table to modify the data file location.

  • MYISAM
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
CREATE TABLE partition_myisam (
id INT AUTO_INCREMENT,
store_id INT,
order_date DATE,
INDEX idx (id)
) ENGINE = MYISAM
PARTITION BY LIST(store_id) (
PARTITION p1
VALUES IN (1, 3, 4, 17)
DATA DIRECTORY = '/data/data'
INDEX DIRECTORY = '/data/idx',
PARTITION p2
VALUES IN (2, 12, 14)
DATA DIRECTORY = '/data/data'
INDEX DIRECTORY = '/data/idx',
PARTITION p3
VALUES IN (6, 8, 20)
DATA DIRECTORY = '/data/data'
INDEX DIRECTORY = '/data/idx',
PARTITION p4
VALUES IN (5, 7, 9, 11, 16)
DATA DIRECTORY = '/data/data'
INDEX DIRECTORY = '/data/idx',
PARTITION p5
VALUES IN (10, 13, 15, 18)
DATA DIRECTORY = '/data/data'
INDEX DIRECTORY = '/data/idx'
);
  • INNODB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
CREATE TABLE partition_innodb (
id INT AUTO_INCREMENT,
store_id INT,
order_date DATE,
INDEX idx (id)
) ENGINE = INNODB
PARTITION BY LIST(store_id) (
PARTITION p1
VALUES IN (1, 3, 4, 17)
DATA DIRECTORY = '/data/EN',
PARTITION p2
VALUES IN (2, 12, 14)
DATA DIRECTORY = '/data/CN',
PARTITION p3
VALUES IN (6, 8, 20)
DATA DIRECTORY = '/data/JP',
PARTITION p4
VALUES IN (5, 7, 9, 11, 16)
DATA DIRECTORY = '/data/GB',
PARTITION p5
VALUES IN (10, 13, 15, 18)
DATA DIRECTORY = '/data/HK'
);

If you got this error, make sure the data directory exist and right power

1
ERROR 1030 (HY000): Got error 168 from storage engine

1
$ mkdir /data; chown -R mysql:mysql /data/
  • Insert data
1
2
3
4
5
6
7
8
9
10
11
12
13
insert into partition_myisam(id, store_id) values(1, 1);
insert into partition_myisam(id, store_id) values(2, 2);
insert into partition_myisam(id, store_id) values(6, 6);
insert into partition_myisam(id, store_id) values(8, 8);
insert into partition_myisam(id, store_id) values(5, 5);
insert into partition_myisam(id, store_id) values(9, 9);

insert into partition_innodb(id, store_id) values(1, 1);
insert into partition_innodb(id, store_id) values(2, 2);
insert into partition_innodb(id, store_id) values(6, 6);
insert into partition_innodb(id, store_id) values(8, 8);
insert into partition_innodb(id, store_id) values(5, 5);
insert into partition_innodb(id, store_id) values(9, 9);
  • Check data file distributed
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
$ tree /data 
.
|-- CN
| `-- test
| `-- partition_innodb#P#p2.ibd
|-- EN
| `-- test
| `-- partition_innodb#P#p1.ibd
|-- GB
| `-- test
| `-- partition_innodb#P#p4.ibd
|-- HK
| `-- test
| `-- partition_innodb#P#p5.ibd
|-- JP
| `-- test
| `-- partition_innodb#P#p3.ibd

Meet some errors

  • MyISAM specified data directory is nvalid
    The version 5.5 is no problem.

Version 5.7.12 will appear warning. The data directory is still the default directory.
InnoDB data files can not use the same way as MyISAM label link specified data files.

1
2
3
4
5
6
mysql> SHOW VARIABLES LIKE 'have_symlink'; 
+---------------+----------+
| Variable_name | Value |
+---------------+----------+
| have_symlink | DISABLED |
+---------------+----------+

1
2
3
$ vim /etc/mysql/conf.d/mysql.cnf

symbolic-links=1 # Change to 1 than enable the symbolic link
1
$ sudo service mysql restart
Share