哪里建设网站好,百度手机关键词排名工具,有免费的网站做农家院宣传,wordpress商品展示模块. 前提#xff1a;mysql5.6.6以上的版本以上的版本才支持单表指定目录#xff0c;且目录权限是MySQL:mysql。 在mysql中数据文件存放于在my.cnf中datadir指定的路径#xff0c;使用的表引擎不同产生的文件格式、表文件个数也会有所差异。 mysql的表引擎有多种#xff0c;…. 前提mysql5.6.6以上的版本以上的版本才支持单表指定目录且目录权限是MySQL:mysql。 在mysql中数据文件存放于在my.cnf中datadir指定的路径使用的表引擎不同产生的文件格式、表文件个数也会有所差异。 mysql的表引擎有多种表的扩展名也不一样如innodb用“ .ibd”archive用“.arc ”csv用“.csv”等。1.步骤详解步骤一设定my.cnf配置文件。innodb_file_per_table1验证开关已经打开。12345671234567mysql SHOW VARIABLES LIKE innodb_file_per_table; ------------------------------| Variable_name | Value | ------------------------------| innodb_file_per_table | ON | ------------------------------1 row in set (0.00 sec)步骤二创建指定路径存储的分区表。1234567891011121314151617181920212223242526272829303112345678910111213141516171819202122232425262728293031CREATE 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 );插入记录123456789101112123456789101112insert 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);步骤三到指定新路径下验证。12345678910111213141516171819201234567891011121314151617181920[rootf033b3fe25e2 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.ibd12312310 directories, 5 files[rootf033b3fe25e2 orders]# pwd/var/orders查询验证12345671234567mysql 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)步骤四新增分区处理。场景假设比如最新的数据我们想存储到SSD硬盘上。可以通过增加指定路径的分区文件达到目的。12345671234567ALTER TABLE orders_list2 ADD PARTITION (PARTITION p6 VALUES IN (21,22,23) DATA DIRECTORY /var/ssd_testing INDEX DIRECTORY /var/ssd_testing);[rootf033b3fe25e2 var]# tree ssd_testing/ssd_testing/└── test└── orders_list2#P#p6.ibd六个分区结果12345671234567mysql 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)2016年10月21日 20:56 思于家中床前
