1.步骤详解
步骤一：设定my.cnf配置文件。
innodb_file_per_table=1
验证开关已经打开。
mysql> SHOW VARIABLES LIKE 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | ON |
+-----------------------+-------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
步骤二：创建指定路径存储的分区表。
CREATE TABLE orders_list2 (
id INT AUTO_INCREMENT,
customer_surname VARCHAR(30),
store_id INT,
salesperson_id INT,
order_date DATE,
note VARCHAR(500),
INDEX idx (id)
) ENGINE = INNODB
PARTITION BY LIST(store_id) (
PARTITION p1
VALUES IN (1, 3, 4, 17)
INDEX DIRECTORY = '/var/orders/district1'
DATA DIRECTORY = '/var/orders/district1',
PARTITION p2
VALUES IN (2, 12, 14)
INDEX DIRECTORY = '/var/orders/district2'
DATA DIRECTORY = '/var/orders/district2',
PARTITION p3
VALUES IN (6, 8, 20)
INDEX DIRECTORY = '/var/orders/district3'
DATA DIRECTORY = '/var/orders/district3',
PARTITION p4
VALUES IN (5, 7, 9, 11, 16)
INDEX DIRECTORY = '/var/orders/district4'
DATA DIRECTORY = '/var/orders/district4',
PARTITION p5
VALUES IN (10, 13, 15, 18)
INDEX DIRECTORY = '/var/orders/district5'
DATA DIRECTORY = '/var/orders/district5'
);
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
29
30
31
插入记录：
insert into orders_list2(id, customer_surname, store_id, salesperson_id, order_date, note)
values(1, "yang", 1, 1, CURDATE(), "testing");
insert into orders_list2(id, customer_surname, store_id, salesperson_id, order_date, note)
values(2, "yang", 2, 2, CURDATE(), "testing");
insert into orders_list2(id, customer_surname, store_id, salesperson_id, order_date, note)
values(6, "yang", 6, 6, CURDATE(), "testing");
insert into orders_list2(id, customer_surname, store_id, salesperson_id, order_date, note)
values(8, "yang", 8, 8, CURDATE(), "testing");
insert into orders_list2(id, customer_surname, store_id, salesperson_id, order_date, note)
values(5, "yang", 5, 5, CURDATE(), "testing");
insert into orders_list2(id, customer_surname, store_id, salesperson_id, order_date, note)
values(10, "yang", 10, 10, CURDATE(), "testing");
1
2
3
4
5
6
7
8
9
10
11
12
步骤三：到指定新路径下验证。
[root@f033b3fe25e2 orders]# tree
.
├── district1
│ └── test
│ └── orders_list2#P#p1.ibd
├── district2
│ └── test
│ └── orders_list2#P#p2.ibd
├── district3
│ └── test
│ └── orders_list2#P#p3.ibd
├── district4
│ └── test
│ └── orders_list2#P#p4.ibd
└── district5
└── test
└── orders_list2#P#p5.ibd
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
10 directories, 5 files
[root@f033b3fe25e2 orders]# pwd
/var/orders
1
2
3
查询验证：
mysql> explain partitions select * from orders_list2;
+----+-------------+--------------+----------------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------+----------------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | orders_list2 | p1,p2,p3,p4,p5 | ALL | NULL | NULL | NULL | NULL | 11 | NULL |
+----+-------------+--------------+----------------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
步骤四：新增分区处理。
场景假设：比如最新的数据，我们想存储到SSD硬盘上。可以通过增加指定路径的分区文件达到目的。
ALTER TABLE orders_list2 ADD PARTITION (PARTITION p6 VALUES IN (21,22,23)
DATA DIRECTORY = '/var/ssd_testing' INDEX DIRECTORY = '/var/ssd_testing');
[root@f033b3fe25e2 var]# tree ssd_testing/
ssd_testing/
└── test
└── orders_list2#P#p6.ibd
1
2
3
4
5
6
7
六个分区结果：
mysql> explain partitions select * from orders_list2;
+----+-------------+--------------+-------------------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------+-------------------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | orders_list2 | p1,p2,p3,p4,p5,p6 | ALL | NULL | NULL | NULL | NULL | 12 | NULL |
+----+-------------+--------------+-------------------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
参考：https://dev.mysql.com/doc/refman/5.6/en/tablespace-placing.html