数据库分区是一种物理数据库设计技术。其主要目的是为了在特定的SQL操作中减少数据读写的总量以缩减sql语句的响应时间,同时对于应用来说分区完全是透明的。
数据库性能的提升和简化数据管理,在扫描操作中MySQL优化器只扫描数据的那个分区以减少扫描范围获得性能的提高。分区技术使得数据管理变得简单,删除某个分区不会对另外的分区造成影响。MySQL从5.1版本开始支持分区,同个表中的分区表名称要唯一
主要有两种形式:水平分区和垂直分区
这种形式的分区是根据表的行进行分区,通过这样的方式不同分组里面的物理列分割的数据集得以组合,从而进行个体分割(单分区)或集体分割(1个或多个分区)。所有在表中定义的列在每个数据集中都能找到,所以表的特性依然得以保持。水平分区一定要通过某个属性列来分割。常见的比如年份,日期等。
这种分区方式一般来说是通过对表的垂直划分来减少目标表的宽度,使某些特定的列被划分到特定的分区,每个分区都包含了其中的列所对应所有行。
可以用 show variables like '%parts%' 命令查询当前的MySQL数据库版本是否支持分区。
现详细介绍下水平分区。水平分区根据所使用的不同分区规则可以分成几大分区类型。
RANGE 分区:基于属于一个给定连续区间的列值,把多行分配给分区。
LIST分区:类似于按RANGE分区,区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。
HASH分区:基于用户定义的表达式的返回值来进行选择的分区,该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL中有效的、产生非负整数值的任何表达式。
KEY分区:类似于按HASH分区,区别在于KEY分区只支持计算一列或多列,且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值。
复合分区:基于RANGE/LIST 类型的分区表中每个分区的再次分割。子分区可以是 HASH/KEY等类型。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | CREATE TABLE cxy7_product ( id BIGINT NOT NULL , NAME VARCHAR (20), price INT ) PARTITION BY RANGE (price)( PARTITION less_1000 -- 小于 1000 VALUES less than (1000), PARTITION b_1000_2000 -- 1000~2000 VALUES less than (2000), PARTITION greater_2000 -- >2000 VALUES less than MAXVALUE ); |
以价格为依据做范围分区,这里最值得注意的是表达式必须有返回值。
1 2 3 4 5 6 7 8 9 10 11 12 | CREATE TABLE cxy7_book ( id BIGINT NOT NULL , NAME VARCHAR (20), category INT ) PARTITION BY LIST (category)( PARTITION edu VALUES IN (1, 3), PARTITION com VALUES IN (2, 4) ); |
以分类作为分区依据,每个分类做一分区。
HASH分区主要用来确保数据在预先确定数目的分区中平均分布。在RANGE和LIST分区中,必须明确指定一个给定的列值或列值集合应该保存在哪 个分区中;而在HASH分区中,MySQL 自动完成这些工作,你所要做的只是为将要被哈希的列值指定一个列值或表达式,以及指定被分区的表将要被分割成的分区数量。
1 2 3 4 5 | CREATE TABLE cxy7_order ( id BIGINT NOT NULL , NAME VARCHAR (20), create_date date NOT NULL ) PARTITION BY HASH ( YEAR (create_date)) |
按照KEY进行分区类似于按照HASH分区,除了HASH分区使用的用户定义的表达式,而KEY分区的哈希函数是由MySQL 服务器提供。KEY分区只采用一个或多个列名。
1 2 3 4 5 | CREATE TABLE cxy7_user ( id BIGINT NOT NULL , NAME VARCHAR (20), birthday date NOT NULL ) PARTITION BY KEY (birthday) |
1 2 3 4 5 6 7 8 9 10 11 12 | CREATE TABLE cxy7_sales ( user_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY , prod_id BIGINT NOT NULL , num INT NOT NULL ) PARTITION BY RANGE (user_id) SUBPARTITION BY HASH (user_id % 2) SUBPARTITIONS 2 ( PARTITION less_1000 VALUES LESS THAN (1000), PARTITION greater_1000 VALUES LESS THAN MAXVALUE ) |
1 2 3 4 5 6 7 8 9 10 11 12 | CREATE TABLE cxy7_user_1 ( id BIGINT NOT NULL , NAME VARCHAR (20), birthday date NOT NULL ) PARTITION BY RANGE (id) subpartition BY KEY (birthday) SUBPARTITIONS 2 ( PARTITION less_1000 VALUES LESS THAN (1000), PARTITION greater_1000 VALUES LESS THAN MAXVALUE ) |
1 2 3 4 5 6 7 8 9 10 11 12 13 | CREATE TABLE cxy7_user_2 ( id BIGINT NOT NULL , dep_no BIGINT NOT NULL , NAME VARCHAR (20), birthday date NOT NULL ) PARTITION BY list (dep_no) subpartition BY HASH ( YEAR (birthday)) subpartitions 2 ( PARTITION p1 VALUES IN (10), PARTITION p2 VALUES IN (20) ); |
1 2 3 4 5 6 7 8 9 10 11 12 13 | CREATE TABLE cxy7_user_3 ( id BIGINT NOT NULL , dep_no BIGINT NOT NULL , NAME VARCHAR (20), birthday date NOT NULL ) PARTITION BY list (dep_no) subpartition BY KEY (birthday) subpartitions 2 ( PARTITION p1 VALUES IN (10), PARTITION p2 VALUES IN (20) ); |
1 2 3 4 5 6 7 8 9 10 11 12 | mysql> SHOW CREATE TABLE cxy7_user_3; CREATE TABLE `cxy7_user_3` ( `id` bigint (20) NOT NULL , `dep_no` bigint (20) NOT NULL , ` NAME ` varchar (20) DEFAULT NULL , `birthday` date NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 PARTITION BY LIST (`dep_no`) SUBPARTITION BY KEY (`birthday`) SUBPARTITIONS 2 (PARTITION `p1` VALUES IN (10) ENGINE = InnoDB, PARTITION `p2` VALUES IN (20) ENGINE = InnoDB) |
1 2 | ALTER TABLE cxy7_user_3 DROP PARTITION p1; ALTER TABLE cxy7_user_3 DROP PARTITION p1, p2 一次性删除多个分区 |
1 2 3 4 5 | ALTER TABLE cxy7_book ADD PARTITION ( PARTITION less_4000 VALUES IN (5, 6) ); |
reorganize partition关键字可以对表的部分分区或全部分区进行修改,并且不会丢失数据。分解前后分区的整体范围应该一致。
1 2 3 4 5 6 7 8 | ALTER TABLE cxy7_book REORGANIZE PARTITION edu INTO ( PARTITION edu_1 VALUES IN (1), PARTITION edu_3 VALUES IN (3) ); |
1 2 | ALTER TABLE cxy7_book REORGANIZE PARTITION edu_1, edu_3 INTO (PARTITION edu VALUES IN (1, 3)); |
1 | ALTER TABLE cxy7_order PARTITION BY HASH (id) PARTITIONS 10; |
1 | ALTER TABLE cxy7_user_2 REMOVE PARTITIONING; |
这和先删除保存在分区中的所有记录,然后重新插入它们,具有同样的效果。它可用于整理分区碎片。
1 | ALTER TABLE cxy7_user_3 REBUILD PARTITION p2; |
如果从分区中删除了大量的行,或者对一个带有可变长度的行(也就是说,有VARCHAR,BLOB,或TEXT类型的列)作了许多修改,可以使用“ALTER TABLE ... OPTIMIZE PARTITION”来收回没有使用的空间,并整理分区数据文件的碎片。
1 | ALTER TABLE cxy7_user_3 optimize partition p2; |
读取并保存分区的键分布。
1 | ALTER TABLE cxy7_user_3 ANALYZE PARTITION p2; |
修补被破坏的分区
1 | ALTER TABLE cxy7_user_3 REPAIR PARTITION p2; |
可以使用几乎与对非分区表使用CHECK TABLE 相同的方式检查分区。
1 | ALTER TABLE cxy7_user_3 CHECK PARTITION p2; |
这个命令可以告诉你表cxy7_user_3的分区p2中的数据或索引是否已经被破坏。如果发生了这种情况,使用“ALTER TABLE ... REPAIR PARTITION”来修补该分区。
MySQL分区处理NULL值的方式
如果分区键所在列没有not null约束。
如果是range分区表,那么null行将被保存在范围最小的分区。
如果是list分区表,那么null行将被保存到list为0的分区。
在按HASH和KEY分区的情况下,任何产生NULL值的表达式MySQL都视同它的返回值为0。
为了避免这种情况的产生,建议分区键设置成NOT NULL。
分区键必须是INT类型,或者通过表达式返回INT类型,可以为NULL。唯一的例外是当分区类型为KEY分区的时候,可以使用其他类型的列作为分区键( BLOB or TEXT 列除外)。
对分区表的分区键创建索引,那么这个索引也将被分区,分区键没有全局索引一说。
只有RANG和LIST分区能进行子分区,HASH和KEY分区不能进行子分区。
临时表不能被分区。
可以查看创建分区表的create语句
可以查看表是不是分区表
1 | SHOW TABLE STATUS LIKE '%cxy7_user_2%' ; |
1 2 3 4 5 6 7 8 9 10 | SELECT partition_name part, partition_expression expr, partition_description descr, table_rows FROM information_schema.PARTITIONS WHERE table_schema = SCHEMA () AND table_name = 'cxy7_user_2' ; |
可以查看表具有哪几个分区、分区的方法、分区中数据的记录数等信息
1 | EXPLAIN PARTITIONS SELECT * FROM cxy7_user_2; |
通过此语句来显示扫描哪些分区,及他们是如何使用的.
联系客服