mysql中分区表怎样查分区

目录

• 1、什么是分区？
• 2、分类
• 3、RANGE分区
• 4、LIST分区
• 5、HASH分区
• 6、KEY分区
• 7、性能比较
• 8、分区的管理

1、什么是分区？

单表数据库本地文件为下表所示：

存储引擎	文件类型	存储内容
MyISAM	.frm	存放表结构，字段长度等
	.myd	存储数据信息
	.myi	存储索引信息
InnoDB	.frm	存放表结构，字段长度等
	.ibd	存储数据信息和索引信息

如果一张表的数据量很大，那么myd、myi或者ibd就会很大，读写就会很慢。 分区是将数据分段存放在多个位置，可以是不同的硬盘、系统，或者是不同服务器存储介子中。分区后，在逻辑上还是一张表，但底层是由多个物理区块组成。业务读写操作时还是原大表的名字，数据库自动去组织分区的数据。

分区之后，数据文件被分成了很多块，只要知道了所查数据在哪一块，就可以在哪一块上快速查找。

2、分类

RANGE分区：基于一个给定的不重叠的连续区间范围的列值，把多行数据分配到不同的分区。比如：00-09年的数据、10-19年的数据、20年及以后的数据。
LIST分区：类似于range分区，区别在于list分区是基于列值匹配一个离散值集合中的某个值来进行选择。比如：2019年的数据、2020年的数据。
HASH分区：通过对表的一个或多个列的Hash Key进行计算，最后通过得到的Hash码对应到不同的数据分区。
KEY分区：类似于hash分区，但是只能使用MySQL服务器提供的哈希函数进行计算。

说明：（1）在MySQL5.1版本中，RANGE,LIST,HASH分区要求分区键必须是INT类型，或者通过表达式返回INT类型。但KEY分区的时候，可以使用其他类型的列(BLOB，TEXT类型除外)作为分区键。
（2）如果表中存在主键或者唯一索引的列，那么分区键中的列必须在主键或唯一索引中包括。否则会报错1503。
（3）一个表最多只能有1024个分区。
（4）分区表中无法使用外键约束。
（5）RANGE分区将null值放在范围最小的分区，LIST将其保存在0的分区；在按HASH和KEY分区的情况下，任何产生NULL值的表达式mysql都视同它的返回值为0。为了避免这种情况的产生，建议分区键设置成NOT NULL。

3、RANGE分区

（1）首先确认分区功能开启
MySQL从5.1版本开始支持分区，5.6以下的版本：

show variables like '%partition%

会出现have_partitioning YES。
5.6以上的版本

show plugins;

出现partition ACTIVE

（2）创建表

DROP TABLE IF EXISTS test;
CREATE TABLE test (
	id INT(11) NOT NULL AUTO_INCREMENT,
	uid INT(11) NOT NULL,
	name VARCHAR(30) NOT NULL,
	placeid INT(11) NOT NULL,
	salary FLOAT,
  PRIMARY KEY(id, placeid)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
PARTITION BY RANGE (placeid)(
	PARTITION p0 VALUES LESS THAN (6),
	PARTITION p1 VALUES LESS THAN (11),
	PARTITION p2 VALUES LESS THAN (MAXVALUE)
);

注意这里，因为存在主键，所以用来分区的键必须包含在主键里。；less than不能出现无法区分的情况，不如出现两次less than (21)。

创建完成后，在mysql/data/separation目录下可以看到，

其中.par文件存储分区信息。

（3）插入数据

INSERT INTO test VALUES(1, 10001, '张三', 1, 5555.55);
INSERT INTO test VALUES(2, 10002, '李四', 3, 5555.55);
INSERT INTO test VALUES(3, 10003, '小六', 7, 6666.66);
INSERT INTO test VALUES(4, 10004, '王五', 13, 7000);
INSERT INTO test VALUES(5, 10005, '赵一', 24, 7000);

其中前两条会插入p0，第三条插入p1，后两条插入p2。
(4)根据时间划分
根据TIMESTAMP范围：

DROP TABLE IF EXISTS test_timestamp;
CREATE TABLE test_timestamp(
	id INT(11) NOT NULL,
	time TIMESTAMP
) ENGINE=MyISAM DEFAULT CHARSET=utf8
PARTITION BY RANGE (UNIX_TIMESTAMP(time))(
	PARTITION p0 VALUES LESS THAN (UNIX_TIMESTAMP('2019-12-31 00:00:00')),
	PARTITION p1 VALUES LESS THAN (UNIX_TIMESTAMP('2020-05-12 00:00:00')),
	PARTITION p2 VALUES LESS THAN (MAXVALUE)
);

根据DATE、DATETIME范围：
添加COLUMNS关键字可以定义非INT类型，但是COLUMNS括号内只能是列名，不支持函数。

DROP TABLE IF EXISTS test_date;
CREATE TABLE test_date(
	id INT(11) NOT NULL,
	time DATE
) ENGINE=MyISAM DEFAULT CHARSET=utf8
PARTITION BY RANGE COLUMNS(time)(
	PARTITION p0 VALUES LESS THAN ('2019-12-31'),
	PARTITION p1 VALUES LESS THAN ('2020-05-12'),
	PARTITION p2 VALUES LESS THAN (MAXVALUE)
);

这里range(year(time))可以很方便的按年份分区。
（5）多列分区
COLUMNS关键字也可用于多列分区

DROP TABLE IF EXISTS test_columns;
CREATE TABLE test_columns (
	id INT(11) NOT NULL,
	uid INT(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8
PARTITION BY RANGE COLUMNS(id, uid)(
	PARTITION p0 VALUES LESS THAN (6, 10),
	PARTITION p1 VALUES LESS THAN (11, 20),
	PARTITION p2 VALUES LESS THAN (MAXVALUE, MAXVALUE)
);

注意这里每一列只能出现一次MAXVALUE，出现多次会报错1493。

4、LIST分区

和RANGE分区类似，不使用COLUMNS关键字时，只允许LIST括号内为INT类型或返回INT类型的函数。
假设国内城市的city_id为：1、3、7、11、19；国外城市的city_id为：5、10、20。

DROP TABLE IF EXISTS test_list;
CREATE TABLE test_list (
	id INT(11) NOT NULL,
	city_id INT(11)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
PARTITION BY LIST (city_id)(
	PARTITION p_china VALUES IN (1, 3, 7, 11, 19),
	PARTITION p_foreign VALUES IN (5, 10, 20)
);

5、HASH分区

HASH分区主要用来确保数据在确定好的分区数目的分区中均匀分布，实际上就是将整数对分区数取模后确定分区。

DROP TABLE IF EXISTS test_hash;
CREATE TABLE test_hash (
	id INT(11) NOT NULL,
	city_id INT(11)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
PARTITION BY HASH (city_id)
PARTITIONS 3;

HASH括号里可以是整数也可以是返回整数的表达式；“PARTITIONS num”表示要被分割成分区的数量，如果没有该语句，默认分区数量为1。
另外将HASH替换为LINEAR HASH：

PARTITION BY LINEAR HASH (city_id)

普通HASH(value)的计算过程比较简单是:value%num，但是其扩展性差。LINEAR HASH的计算过程：

（1）V = POWER(2, CEILING(LOG(2, num))) 
这一步是找到第一个大于等于num的2的幂次整数；
POWER为指数计算，CEILINGL为向上取整运算，LOG为对数运算；
（2）N = value & (V-1), &为按位与操作；
（3）当N >= num:
		V = CEIL(V/2), N = N & (V-1), CEIL为向下取整操作。

线性哈希分区的好处是增加，下降，合并，拆分分区可以更快，有利于在处理含有非常大量的数据表（百万兆字节）。缺点是，与常规哈希分区获得的分布相比，分区之间不太可能均匀分布数据。

6、KEY分区

KEY分区和HASH分区相似，只需要将HASH分区的HASH换为KEY即可，区别是两者的哈希函数不同。KEY分区中同样可以使用LINEAR关键字。
另外当表存在主键或唯一索引时可省略Key括号内的列名，Mysql将按照主键-唯一索引的顺序选择，当找不到唯一索引时报错1488。

DROP TABLE IF EXISTS test_key;
CREATE TABLE test_key (
	id INT(11) NOT NULL PRIMARY KEY,
	city_id INT(11)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
PARTITION BY KEY ()
PARTITIONS 3;

7、性能比较

以range分区为例，采用range分区中(2)(3)创建的数据表test，未分区前，查询placeid = 13的数据，可以看到搜索了5条数据，

而添加分区后，由于placeid = 13只可能存在于分区p2中，所以只需要搜索p2分区，搜索2条数据即可。现在因为数据量少差距不大，当数据量很大时，分区能够带来很大程度的性能提升。

8、分区的管理

以range分区为例，采用range分区中(2)(3)创建的数据表test。
（1）增加分区

ALTER TABLE test ADD PARTITION (PARTITION p3 VALUES LESS THAN (100));

（2）删除分区

ALTER TABLE test DROP PARTITION p0;

删除分区同时删除该分区数据；不可删除HASH分区和KEY分区。
（3）删除全部分区

Alter TABLE test REMOVE PARTITIONING;

只清除所有分区，保留数据。
（4）修改分区

ALTER TABLE test PARTITION BY RANGE (placeid)(
	PARTITION p0 VALUES LESS THAN (6),
	PARTITION p1 VALUES LESS THAN (11),
	PARTITION p2 VALUES LESS THAN (16),
	PARTITION p3 VALUES LESS THAN (MAXVALUE)
);

若修改的是HASH分区或者KEY分区：

ALTER TABLE test_hash PARTITION BY HASH(city_id)PARTITIONS 7;

（5）拆分分区

ALTER TABLE test
REORGANIZE PARTITION p0 INTO
(
PARTITION p0_1 VALUES LESS THAN (3),
PARTITION p0_2 VALUES LESS THAN (6)
);

注意拆分后的总范围应保持拆分前的范围保持一致。
（6）合并分区

ALTER TABLE test
REORGANIZE PARTITION p0_1,p0_2 INTO
(
PARTITION p0 VALUES LESS THAN (6)
);

同样需要注意合成后的范围应该和合成前各分区的总范围一致。
（7）重建分区

ALTER TABLE test REBUILD PARTITION p0,p1;

此操作和先删除保存在分区的数据，然后重新插入它们具有相同的效果，可以用来整理分区碎片。
（8）优化分区

ALTER TABLE test OPTIMIZE PARTITION p0,p1;

如果从分区中删除了大量的行，或者对一个带有可变长度的行（也就是说，有VARCHAR，BLOB，或TEXT类型的列）作了许多修改，该操作可用来回收没有使用的空间，并整理分区数据文件碎片。

和（7）（8）操作类似的还有：

ALTER TABLE test ANALYZE PARTITION p0,p1;   -- 分析分区
ALTER TABLE test REPAIR PARTITION p0,p1;   -- 修补分区
ALTER TABLE test CHECK PARTITION p0,p1;   -- 检查分区