MySQL大数据表优化——表分区
- 分区的意义:
- 与传统的单表不分区相比,分区表可存储更多的数据。
- 对于已经失去保存意义的数据,可以通过删除分区,很容易删除。
- 通过对表的分区。可以提升插入速度。
- 按照表分区的规则查询,原本十多秒才能查询到的数据,可以做到秒回
- 涉及到sum(),COUNT()的聚合查询。可以很容易的做到并行处理。只需要将查询的结果合并即可。
- 可以跨多个磁盘来分散查询,提高更大的吞吐量。
2.注意事项:
-
分区列索引约束
-
- 若表有primary key或unique key,则分区表的分区列必须包含在primary key或unique key列表里,这是为了确保主键的效率
- 分区条件列不能为空。
3. 四种分区类型
-
RANGE 分区
-
- 基于属于一个给定连续区间的列值,把多行分配给分区
- 区间要连续且不能相互重叠,使用VALUES LESS THAN操作符来进行定义
- Sql
CREATE TABLE employees (
id INT NOT NULL AUTO_INCREMENT COMMENT '监控设备',,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT NOT NULL,
store_id INT NOT NULL,
PRIMARY KEY (`id`,`store_id`),
)
partition BY RANGE (store_id) (
partition p0 VALUES LESS THAN (6),
partition p1 VALUES LESS THAN (11),
partition p2 VALUES LESS THAN (16),
partition p3 VALUES LESS THAN (21),
PARTITION p4 VALUES LESS THAN MAXVALUE
);
d.MAXVALUE 表示最大的可能的整数值。
2.LISE 分区
- 类似于按RANGE分区,区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。
- LIST分区通过使用“PARTITION BY LIST(expr)”来实现,其中“expr”是某列值或一个基于某个列值、并返回一个整数值的表达式,
- 通过“VALUES IN (value_list)”的方式来定义每个分区,其中“value_list”是一个通过逗号分隔的整数列表。
- LIST分区**没有类似如“VALUES LESS THAN MAXVALUE”**这样的包含其他值在内的定义。将要匹配的任何值都必须在值列表中找到。
- SQL:
CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT,
store_id INT
)
PARTITION BY LIST(store_id)
PARTITION pNorth VALUES IN (3,5,6,9,17),
PARTITION pEast VALUES IN (1,2,10,11,19,20),
PARTITION pWest VALUES IN (4,12,13,14,18),
PARTITION pCentral VALUES IN (7,8,15,16)
);
3.HASH分区
SELECT partition_name part,
partition_expression expr,
partition_description descr,
table_rows
FROM INFORMATION_SCHEMA.partitions
WHERE TABLE_SCHEMA = schema()
AND TABLE_NAME='table';
将分区表改为普通表
ALTER TABLE table_name REMOVE PARTITIONING;
Java输出分区个数
public static void main(String[] args) throws ParseException {
SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
String s = "PARTITION C_20180522 VALUES LESS THAN (TO_DAYS('2018-05-22')) ENGINE = MYISAM,";
Date parse = sdf.parse("20180522");
int i = 737201;
for (int j = 0; j < 54; j++) {
i = i + 7;
Calendar calendar = Calendar.getInstance();
calendar.setTime(parse);
calendar.add(Calendar.DATE, 7);
parse = calendar.getTime();
String format = sdf.format(parse);
System.out.println("PARTITION C_" + format + " VALUES LESS THAN (" + i + ") ENGINE = MYISAM,");
}
}