mysql表分区实验总结
草稿丢失，发表得不完整，稍后重新整理
mysql表分区技术能有效解决水平拆分和垂直拆分的不足，可操作性和效率都更优。以下是一些实验总结。
InnoDB引擎需先在配置文件中设置： innodb_file_per_table=1
--innodb : 主表.frm 保存表结构和分区数目一致的 .ibd 文件，用于保存数据和索引。
--myisam：主表.frm保存表结构定义，主表.par保存分区信息， 和分区数目一致的 .MYD文件，用于保存数据，.MDI文件用于索引。
range分区：
CREATE TABLE a(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME CHAR(20))ENGINE=INNODB CHARSET=utf8
PARTITION BY RANGE(id)(
PARTITION p1 VALUES LESS THAN (100),
PARTITION p2 VALUES LESS THAN (200),
PARTITION p3 VALUES LESS THAN (300),
PARTITION p4 VALUES LESS THAN MAXVALUE);
----------建立一个以id 区间来划分的分别，当id 小于100时数据保存到p1分区，100到199时保存到p2分区，200到299时保存到p3分区，大于300时保存到p4分区；
[root@master test]#dir
a.frm a#P#p1.ibd a#P#p2.ibd a#P#p4.ibd db.opt
List分区：
CREATE TABLE bc(
id INT NOT NULL AUTO_INCREMENT,
par_no INT NOT NULL DEFAULT '1',
a_name CHAR(20) NOT NULL,
PRIMARY KEY(id,par_no)) ENGINE=MYISAM CHARSET=gbk PARTITION BY LIST(par_no)(
PARTITION p0 VALUES IN (10,20,30),
PARTITION p1 VALUES IN (40,50,60),
PARTITION p2 VALUES IN (70,80,100));
---插入数据时，par_no的值必须在分区定义中存在，否则不能插入并报错。
[root@master test]# dir nb*
nb.frmnb.parnb#P#p0.MYD nb#P#p0.MYI nb#P#p1.MYD nb#P#p1.MYI nb#P#p2.MYD nb#P#p2.MYI nb#P#p3.MYD nb#P#p3.MYInb#P#p4.MYD nb#P#p4.MYI
提示警告：意思大概mysql分区以后的版本不支持myisam引擎吧，换成innodb就可以了。
Warning Code : 1287
The partition engine, used by table 'test.bc', is deprecated and will be removed in a future release. Please use native partitioning instead.
Hash分区：
CREATE TABLE nb(
id INT NOT NULL AUTO_INCREMENT,
par_no INT NOT NULL DEFAULT '1',
a_name CHAR(20) NOT NULL,
PRIMARY KEY(id,par_no)) ENGINE=MYISAM CHARSET=gbk PARTITION BY HASH(id)
PARTITIONS 5; --partition 多了个s ,让mysql自动id的Hash 值存储到5个分区里。
查询分区表中存在的数据量：
SELECT PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,TABLE_ROWS,SUBPARTITION_NAME,SUBPARTITION_METHOD,SUBPARTITION_EXPRESSION
FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='bcd'
分区管理--测试总结：删除分区：ALTER TABLE tablename REMOVE PARTITIONING ; -- 删除分区定义，所有数据集中到一个表，数据不丢失ALTER TABLE bc DROP PARTITION p1; --删除分区的同时会删除分区中的数据修改分区：ALTER TABLE nb PARTITION BY HASH(id) PARTITIONS 2; --Hash分区重新定义为两个，并会重新分配数据合并分区ALTER TABLE bc REORGANIZE PARTITION p1,p0 INTO ( PARTITION p6 VALUES IN (10,20,30,40)); --将BC表中的p0,p1分区合并到p6分区，并且p0,p1的[list]值必须包含在新的分区内，否则不在新区[list]中的数据会丢失；ALTER TABLE a REORGANIZE PARTITION p0,p1,p2 INTO (PARTITION p0 VALUES LESS THAN (500),PARTITION p1 VALUES LESS THAN maxvalue); --重定义分区结构：将Range分区p1,p2合并到p0分区，由于原p2分区是maxvalue值，所以还得同时增加一个新的maxvalue分区，否则报错。拆分分区：添加分区:未有分区的情况下:ALTER TABLE nb PARTITION BY HASH(id) PARTITIONS 2; --用id 列给表分两个hash分区;如果有unique键,要先删除.ALTER TABLE a PARTITION BY RANGE(id)( --a 表无分区.PARTITION p0 VALUES LESS THAN (1000),PARTITION p1 VALUES LESS THAN (2000),PARTITION p2 VALUES LESS THAN maxvalue)--------------------------------------------------------------------ALTER TABLE bc PARTITION BY LIST(par_no) (PARTITION p1 ----为未定义分区表的BC表添加两个list分区；VALUES IN (10,20,30),PARTITION p2 VALUES IN (40,50,60,70,80));已有分区的情况下:合并分区：ALTER TABLE abc REORGANIZE PARTITION p2 INTO (PARTITION p2 VALUES LESS THAN (6000),PARTITION p3 VALUES LESS THAN maxvalue); --重新定义Range分区p2，并新加一个p3分区,p2分区的值不能小于现有的最大值，且须新境一个包含最大值（Maxvalue）的新分区，否则报错ALTER TABLE bc ADD PARTITION (PARTITION p3 VALUES IN (20,30))；--添加一个list分区ALTER TABLE nb ADD PARTITION PARTITIONS 2; --(Hash分区),新加后现有分区里的数据会平滑分摊到新分区,myisam引擎在上面的查询语句中可以体现出来,innodb引擎则计数信息丢失,从0开始重新计数,但表中数据变不会丢失.
注意：删除分区同时会将分区中的数据删除，同时枚举的list值也被删除，后面无法往表中插入该值的数据。
