Mysql資料庫表分區存儲到指定磁盤路徑

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'

);

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");

values(2, "yang", 2, 2, CURDATE(), "testing");

values(6, "yang", 6, 6, CURDATE(), "testing");

values(8, "yang", 8, 8, CURDATE(), "testing");

values(5, "yang", 5, 5, CURDATE(), "testing");

values(10, "yang", 10, 10, CURDATE(), "testing");

步驟三：到指定新路徑下驗證。

[root@f033b3fe25e2 orders]# tree

.

├── district1

│   └── test

│   └── orders_list2#P#p1.ibd

├── district2

│   └── orders_list2#P#p2.ibd

├── district3

│   └── orders_list2#P#p3.ibd

├── district4

│   └── orders_list2#P#p4.ibd

└── district5

 └── test

 └── orders_list2#P#p5.ibd

10 directories, 5 files

[root@f033b3fe25e2 orders]# pwd

/var/orders

查詢驗證：

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 |

步驟四：新增分區處理。

場景假設：比如最新的資料，我們想存儲到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 | SIMPLE | orders_list2 | p1,p2,p3,p4,p5,p6 | ALL | NULL | NULL | NULL | NULL | 12 | NULL |

參考：

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