mysql表分区

参考：https://blog.csdn.net/qq_26664043/article/details/138452285
存储了百万级乃至千万级条记录的表过于庞大，导致数据库在查询和插入的时候耗时太长，性能低下，如果涉及联合查询的情况，性能会更加糟糕。

分表和表分区的目的就是减少数据库的负担，提高数据库的效率，通常点来讲就是提高表的增删改查效率。

分区是将数据分段划分在多个位置存放，可以是同一块磁盘也可以在不同的机器。分区后，表面上还是一张表，但数据散列到多个位置了。程序读写的时候操作的还是大表名字，db自动去组织分区的数据。

分区的方式：Range、List、Hash、Key

Range分区

-- RANGE 分区
-- 删除p0分区
-- ALTER TABLE td_vipexchangecodeinfo DROP PARTITION p0;

DROP TABLE IF EXISTS `td_vipexchangecodeinfo`;
CREATE TABLE IF NOT EXISTS `td_vipexchangecodeinfo` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `CardNo` varchar(200) DEFAULT NULL,
  `ChannelID` int(11) DEFAULT NULL,
  `Status` int(11) DEFAULT NULL,
  `ManagerID` int(11) DEFAULT NULL,
  `CreateDate` datetime DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE = MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
PARTITION BY RANGE(`ID`)
(
PARTITION p0 VALUES LESS THAN (500000),
PARTITION p1 VALUES LESS THAN (1000000),
PARTITION p2 VALUES LESS THAN (1500000)
PARTITION p6 VALUES LESS THAN MAXVALUE
ENGINE = MyISAM 
);

根据TIMESTAMP范围分区：

...
PARTITION BY RANGE(unix_timestamp(CreateDate))
(
  partition p0 values less than (unix_timestamp('2022-01-01 00:00:00')),
  partition p1 values less than (unix_timestamp('2023-01-01 00:00:00')),
  partition p2 values less than (unix_timestamp('2024-01-01 00:00:00')),
  partition p3 values less than maxvalue
);

添加COLUMNS关键字可定义非integer范围及多列范围，不过需要注意COLUMNS括号内只能是列名，不支持函数；多列范围时，多列范围必须呈递增趋势：

...
PARTITION BY RANGE COLUMNS(CreateDate)
(
  partition p0 values less than ('2022-01-01 00:00:00'),
  partition p1 values less than ('2023-01-01 00:00:00'),
  partition p2 values less than ('2024-01-01 00:00:00'),
  partition p3 values less than maxvalue
);

多列：

...
partition by range columns(a,b)
(
  partition p0 values less than (0,10),
  partition p1 values less than (10,20),
  partition p2 values less than (maxvalue,maxvalue)
)

List分区

DROP TABLE IF EXISTS tb_list;
CREATE TABLE IF NOT EXISTS tb_list( 
ID INT(11) NOT NULL AUTO_INCREMENT,
Age INT(11),
PRIMARY KEY(`ID`)
)ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
PARTITION BY LIST(ID)
(
    PARTITION p0 values in (1,3,5,7,9), 
    PARTITION p1 values in (2,4,6,8,10) 
);

 

HASH分区：

基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。
HASH分区主要用来确保数据在预先确定数目的分区中平均分布。在RANGE和LIST分区中，必须明确指定一个给定的列值或列值集合应该保存在哪个分区中；而在HASH分区中，MySQL 自动完成这些工作，你所要做的只是基于将要被哈希的列值指定一个列值或表达式，以及指定被分区的表将要被分割成的分区数量。
要使用HASH分区来分割一个表，要在CREATE TABLE 语句上添加一个“PARTITION BY HASH (expr)”子句，其中“expr”是一个返回一个整数的表达式。它可以仅仅是字段类型为MySQL 整型的一列的名字。此外，你很可能需要在后面再添加一个“PARTITIONS num”子句，其中num 是一个非负的整数，它表示表将要被分割成分区的数量。如果没有包括一个PARTITIONS子句，那么分区的数量将默认为1。

DROP TABLE IF EXISTS `p_hash`;
CREATE TABLE `p_hash` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`storeid` mediumint(10) NOT NULL DEFAULT '0',
`storename` char(255) DEFAULT NULL,
PRIMARY KEY (`id`,`storeid`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8
/*PARTITION BY HASH (storeid)
PARTITIONS 4 */;

 

Key分区

DROP TABLE IF EXISTS `p_key`;
CREATE TABLE `p_key` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`keyname` char(20) DEFAULT NULL,
`keyval` varchar(1000) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=12 DEFAULT CHARSET=utf8
/* PARTITION BY KEY (id)
PARTITIONS 4 */;

 

range与list分区的管理
删除分区
ALTER TABLE tr DROP PARTITION p1;
需要注意的是删除分区后，该分区的所有数据都没有了。同时删除后存在一个重大影响也就是typeid为5,6,7,8的记录是不能写入到该表了的！
清空数据
如果想要保留表结构与分区结构可以使用 TRUNCATE TABLE 清空表
更改分区保留数据
ALTER TABLE tbl_name REORGANIZE PARTITION partition_list INTO (partition_definitions)；

-- 在现有表上创建分区，不会丢失数据
ALTER TABLE loginlog PARTITION BY RANGE (Id)
(   
PARTITION p_0 VALUES LESS THAN (5),
PARTITION p_1 VALUES LESS THAN (10), 
PARTITION p_2 VALUES LESS THAN (15), 
PARTITION p_3 VALUES LESS THAN MAXVALUE 
);

-- 拆分表分区，不会丢失数据
ALTER TABLE loginlog REORGANIZE PARTITION s_0 INTO (
 PARTITION p_0 VALUES LESS THAN (5),
 PARTITION p_1 VALUES LESS THAN (10)
);

-- 合并表分区，不会丢失数据
ALTER TABLE loginlog REORGANIZE PARTITION p_0,p_1 INTO (
 PARTITION s_0 VALUES LESS THAN (10)
); 

使用 REORGANIZE PARTITION进行数据的合并与拆分，数据是没有丢失的。

在使用REORGANIZE进行重新分区时，需要注意几点：
1、用来确定新分区模式的PARTITION子句使用与用在CREATE TABLE中确定分区模式的PARTITION子句相同的规则。(partition 分区子句必须与创建原分区时的规则相同)
2、partition_definitions 列表中分区的合集应该与在partition_list 中命名分区的合集占有相同的区间或值集合。 (不管是合并还是拆分，s0,s1到p0；p0到s0,s1 里面的区间或者值都必须相同)
3、对于按照RANGE分区的表，只能重新组织相邻的分区；不能跳过RANGE分区。(比如按range年份 p0 1990,p1 2000 ,p2 2013三个分区；在合并时partition p0,p2 into()
   这样是不行的，因为这两个分区不是相邻的分区；)
4、不能使用REORGANIZE PARTITION来改变表的分区类型；也就是说，例如，不能把RANGE分区变为HASH分区，反之亦然。也不能使用该命令来改变分区表达式或列。
增加分区
ALTER TABLE p_list ADD PARTITION (PARTITION p2 VALUES IN (9, 10, 11))；
但是不能使用
ALTER TABLE p_list ADD PARTITION (PARTITION p2 VALUES IN (9, 14))；
这样mysql 会产生错误1465 (HY000): 在LIST分区中，同一个常数的多次定义
hash与key分区的管理在改变分区设置方面，按照HASH分区或KEY分区的表彼此非常相似，但是它们又与按照RANGE或LIST分区的表在很多方面有差别。
关于添加和删除按照RANGE或LIST进行分区的表的分区
不能使用与从按照RANGE或LIST分区的表中删除分区相同的方式，来从HASH或KEY分区的表中删除分区。但是，可以使用“ALTER TABLE ... COALESCE PARTITION”命令来合并HASH或KEY分区。
查看源代码打印帮助1 DROP TABLE IF EXISTS `p_hash`;  2    3 CREATE TABLE `p_hash` (  4 `id` int(10) NOT NULL AUTO_INCREMENT,  5 `storeid` mediumint(10) NOT NULL DEFAULT '0',  6 `storename` char(255) DEFAULT NULL,  7 PRIMARY KEY (`id`,`storeid`)  8 ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8  9 /*!50100 PARTITION BY HASH (storeid)  10 PARTITIONS 4 */; 
如p_hash的分区数为4个；
要减少分区数为2个
ALTER TABLE p_hash COALESCE PARTITION 2；
对于按照HASH，KEY，LINEAR HASH，或LINEAR KEY分区的表， COALESCE能起到同样的作用。COALESCE不能用来增加分区的数量，如果你尝试这么做，结果会出现类似于下面的错误：
mysql> ALTER TABLE clients COALESCE PARTITION 18;
错误1478 (HY000): 不能移动所有分区，使用DROP TABLE代替要增加顾客表的分区数量从12到18，使用“ALTER TABLE … ADD PARTITION”,具体如下：
ALTER TABLE clients ADD PARTITION PARTITIONS 18；注释：“ALTER TABLE … REORGANIZE PARTITION”不能用于按照HASH或HASH分区的表。
分区维护
重建分区
这和先删除保存在分区中的所有记录，然后重新插入它们，具有同样的效果。它可用于整理分区碎片。
ALTER TABLE t1 REBUILD PARTITION (p0, p1)；
优化分区如果从分区中删除了大量的行，或者对一个带有可变长度的行（也就是说，有VARCHAR，BLOB，或TEXT类型的列）作了许多修改，
可以使用“ALTER TABLE … OPTIMIZE PARTITION”来收回没有使用的空间，并整理分区数据文件的碎片。
ALTER TABLE t1 OPTIMIZE PARTITION (p0, p1)；
分析分区
读取并保存分区的键分布
ALTER TABLE t1 ANALYZE PARTITION (p3)；
修补分区： 修补被破坏的分区。
ALTER TABLE t1 REPAIR PARTITION (p0,p1);
检查分区
可以使用几乎与对非分区表使用CHECK TABLE 相同的方式检查分区。
ALTER TABLE trb3 CHECK PARTITION (p1)；
这个命令可以告诉你表t1的分区p1中的数据或索引是否已经被破坏。如果发生了这种情况，使用“ALTER TABLE ... REPAIR PARTITION”来修补该分区。获取分区信息
在mysql服务器信息数据库里面的partitions存放着服务器所有表的分区信息。

代码如下:

explain partitions命令
explain partitions select * from p_hash
+----+-------------+--------+-------------+------+---------------+------+---------+------+------+-------+
| id | select_type | table  | partitions  | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+--------+-------------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE    | p_hash | p0,p1,p2,p3 | ALL  | NULL      | NULL | NULL   | NULL |  10 |    |
+----+-------------+--------+-------------+------+---------------+------+---------+------+------+-------+

-- 获取到p_list表的分区详细信息。
select * from information_schema.`PARTITIONS` where TABLE_NAME = 'p_list';
-- 分区的创建信息
show create table p_list;

更多参考：

http://www.jb51.net/article/42544.htm
http://blog.csdn.net/orangleliu/article/details/57088338