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

痛定思痛。 2022-12-22 09:53 250阅读 0赞

# 0. 前提： #

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

# 1.步骤详解 #

## 步骤一：设定my.cnf配置文件。 ##

innodb\_file\_per\_table=1

验证开关已经打开。

## 步骤二：创建指定路径存储的分区表。 ##

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

插入记录：

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

## 步骤三：到指定新路径下验证。 ##

[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

10 directories, 5 files
    [root@f033b3fe25e2 orders]# pwd
    /var/orders

查询验证：

## 步骤四：新增分区处理。 ##

场景假设：比如最新的数据，我们想存储到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

六个分区结果：

参考：[https://dev.mysql.com/doc/refman/5.6/en/tablespace-placing.html][https_dev.mysql.com_doc_refman_5.6_en_tablespace-placing.html]

[20161021205451995]: /images/20221120/76db10e6d1654f43beb43d363eb40dc8.png
[https_dev.mysql.com_doc_refman_5.6_en_tablespace-placing.html]: https://dev.mysql.com/doc/refman/5.6/en/tablespace-placing.html