Mysql数据库表分区存储到指定磁盘路径

. 前提：

mysql5.6.6以上的版本以上的版本才支持单表指定目录，且目录权限是MySQL:mysql。 
在mysql中数据文件存放于在my.cnf中datadir指定的路径，使用的表引擎不同产生的文件格式、表文件个数也会有所差异。 
mysql的表引擎有多种，表的扩展名也不一样，如innodb用“ .ibd”，archive用“.arc ”，csv用“.csv”等。 

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
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
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
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
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
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
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
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
1
2
3
4
5
6
7

参考：https://dev.mysql.com/doc/refman/5.6/en/tablespace-placing.html

2016年10月21日 20:56 思于家中床前

作者：铭毅天下 
转载请标明出处，原文地址： 
http://blog.csdn.net/laoyang360/article/details/52887016