MySQL大数据表优化——表分区

MySQL大数据表优化——表分区

1. 分区的意义：

• 与传统的单表不分区相比，分区表可存储更多的数据。
• 对于已经失去保存意义的数据，可以通过删除分区，很容易删除。
• 通过对表的分区。可以提升插入速度。
• 按照表分区的规则查询，原本十多秒才能查询到的数据，可以做到秒回
• 涉及到sum（），COUNT（）的聚合查询。可以很容易的做到并行处理。只需要将查询的结果合并即可。
• 可以跨多个磁盘来分散查询，提高更大的吞吐量。

2.注意事项：

• 分区列索引约束

• • 若表有primary key或unique key，则分区表的分区列必须包含在primary key或unique key列表里，这是为了确保主键的效率
  • 分区条件列不能为空。

3. 四种分区类型

1. RANGE 分区

2. 1. 基于属于一个给定连续区间的列值，把多行分配给分区
   2. 区间要连续且不能相互重叠，使用VALUES LESS THAN操作符来进行定义
   3. 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 分区

1. 类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。
2. LIST分区通过使用“PARTITION BY LIST(expr)”来实现，其中“expr”是某列值或一个基于某个列值、并返回一个整数值的表达式，
3. 通过“VALUES IN (value_list)”的方式来定义每个分区，其中“value_list”是一个通过逗号分隔的整数列表。
4. LIST分区**没有类似如“VALUES LESS THAN MAXVALUE”**这样的包含其他值在内的定义。将要匹配的任何值都必须在值列表中找到。
5. 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分区

查看Mysql表分区语句

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,");

​    }

}