ysg.point.云施工积分系统分区总结

为什么80%的码农都做不了架构师？>>>   

ysg.point.云施工积分系统分区总结.md

1. 分区介绍 单表分区数量 1024 分区字段：c_time TIMESTAMP, 【Datetime需要调整一点】 查询数据时，必须包括分区字段，才能使用分区表；否则全表扫描，分区不起作用 分区表：只对数据表进行分表分区；表数据量大，单分区数据量在200万左右 分区字段必须是表的主键 ，如PRIMARY KEY (c_time,c_id),

2. 基础库 sys_partition.sql

包括两张表 分区配置表和日志表  sys_partition_cfg
存储过程：
添加分区作业入口：add_partition_job
添加删除分区作业入口：partition_job
添加:add_partition
删除分区：remove_partition 

1. 分区表脚本 时间字段 c_time TIMESTAMP COMMENT '修改时间',

DROP TABLE IF EXISTS poi_action_log CASCADE;
CREATE TABLE poi_action_log (
    c_id VARCHAR (32) NOT NULL COMMENT 'ID',
    c_user_id VARCHAR (80) NOT NULL COMMENT '用户ID',
    c_product_id VARCHAR (32) NOT NULL COMMENT '产品ID',
    c_project_id  VARCHAR (32) COMMENT '项目ID',
    c_type CHAR (1) NOT NULL COMMENT '动作类型，a=行为动作action，b=业务动作biz',
    c_agent_info VARCHAR (500) COMMENT 'agent客户端信息',
    c_time TIMESTAMP COMMENT '修改时间',
    c_product_version  VARCHAR (32) COMMENT '产品版本',
    c_params TEXT COMMENT '动作参数json串',
    c_json VARCHAR (500) COMMENT 'json格式扩展信息',
    c_tag1 VARCHAR (32) COMMENT '冗余字段1',
    c_tag2 VARCHAR (32) COMMENT '冗余字段2',
    PRIMARY KEY (c_product_id,c_time,c_id)
) ENGINE = INNODB COMMENT = '采集的动作日志，上报的原始数据'
PARTITION BY RANGE ( UNIX_TIMESTAMP(c_time) ) 
(
   PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP('1000-01-01 01:00:00') )  
);

1. 分区初始化数据 sys_partition_data.sql 如：3天创建一个分区、预创建30天，保存365天

INSERT INTO sys_partition_cfg (c_table_name, c_table_short_name, c_part_field, c_part_step, c_time_unit, c_pre_create_step, c_keep_days, c_last_create_time) VALUES
('poi_action_log','poi_action_log_','c_time',3,'day',30,365,ADDDATE(CURDATE(),INTERVAL 0 SECOND ));

1. 动态添加分区 定期调用

CALL add_partition_job()
如java Mapper
public interface CcsMapper {
     @Select("CALL add_partition_job()")
    String addPartition();

1. 手工加分区

CALL add_partition('poi_action_log','pa_log_01d_',1,'day',ADDDATE(CURDATE()+INTERVAL 0 DAY,INTERVAL 0 SECOND ));
CALL add_partition('poi_action_log','pa_log_01d_',1,'day',ADDDATE(CURDATE()+INTERVAL 1 DAY,INTERVAL 0 SECOND ));

1. 分区结果 示例

CREATE TABLE poi_action_log (
  c_id varchar(32) NOT NULL COMMENT 'ID',
  c_user_id varchar(80) NOT NULL COMMENT '用户ID',
  c_product_id varchar(32) NOT NULL COMMENT '产品ID',
  c_project_id varchar(32) DEFAULT NULL COMMENT '项目ID',
  c_type char(1) NOT NULL COMMENT '动作类型，a=行为动作action，b=业务动作biz',
  c_agent_info varchar(500) DEFAULT NULL COMMENT 'agent客户端信息',
  c_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  c_product_version varchar(32) DEFAULT NULL COMMENT '产品版本',
  c_params text COMMENT '动作参数json串',
  c_json varchar(500) DEFAULT NULL COMMENT 'json格式扩展信息',
  c_tag1 varchar(32) DEFAULT NULL COMMENT '冗余字段1',
  c_tag2 varchar(32) DEFAULT NULL COMMENT '冗余字段2',
  PRIMARY KEY (c_product_id,c_time,c_id)
) ENGINE=InnoDB DEFAULT CHARSET=gbk COMMENT='采集的动作日志，上报的原始数据'
/*!50100 PARTITION BY RANGE ( UNIX_TIMESTAMP(c_time))
(PARTITION p0 VALUES LESS THAN (0) ENGINE = InnoDB,
PARTITION poi_action_log_20150427_0000 VALUES LESS THAN (1430323200) ENGINE = InnoDB,
PARTITION poi_action_log_20150430_0000 VALUES LESS THAN (1430582400) ENGINE = InnoDB,
PARTITION poi_action_log_20150503_0000 VALUES LESS THAN (1430841600) ENGINE = InnoDB,
PARTITION poi_action_log_20150506_0000 VALUES LESS THAN (1431100800) ENGINE = InnoDB,
PARTITION poi_action_log_20150509_0000 VALUES LESS THAN (1431360000) ENGINE = InnoDB,
PARTITION poi_action_log_20150512_0000 VALUES LESS THAN (1431619200) ENGINE = InnoDB,
PARTITION poi_action_log_20150515_0000 VALUES LESS THAN (1431878400) ENGINE = InnoDB,
PARTITION poi_action_log_20150518_0000 VALUES LESS THAN (1432137600) ENGINE = InnoDB,
PARTITION poi_action_log_20150521_0000 VALUES LESS THAN (1432396800) ENGINE = InnoDB,
PARTITION poi_action_log_20150524_0000 VALUES LESS THAN (1432656000) ENGINE = InnoDB,
PARTITION poi_action_log_20150525_0000 VALUES LESS THAN (1432742400) ENGINE = InnoDB) */;

1. 日期类型分区

CREATE TABLE mon_event
(
	c_id VARCHAR(36) NOT NULL COMMENT '流水号',
	c_company_id VARCHAR(36),
	c_time TIMESTAMP COMMENT '首次发生时间'
)  ENGINE=INNODB  COMMENT='事件数据表'
PARTITION BY RANGE COLUMNS (c_time) 
(
   PARTITION p0 VALUES LESS THAN ('1000-01-01 01:00:00')  
);

分区测试 explain partitions SELECT max(c_time)from poi_user_point WHERE c_time >'2015-05-09' AND c_time<'2015-05-10';

1. datetime分区示例 需要修改现有存储过程

CREATE TABLE terminal_parameter (
  c_time datetime NOT NULL,
  c_user_id int(8) NOT NULL,
  c_terminal_id int(8) DEFAULT NULL,  
  PRIMARY KEY (c_terminal_id , c_time ),
  KEY idx_createtime (c_time ),
  KEY idx_terminal_id (c_terminal_id )
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE(TO_DAYS (c_time ))
(
PARTITION p20101115 VALUES LESS THAN (TO_DAYS('2010-11-15')),
PARTITION p20101130 VALUES LESS THAN (TO_DAYS('2010-11-30')),
PARTITION p20101215 VALUES LESS THAN (TO_DAYS('2010-12-15')),
PARTITION p20101231 VALUES LESS THAN (TO_DAYS('2010-12-31')),
PARTITION p20110115 VALUES LESS THAN (TO_DAYS('2011-01-15')),
PARTITION p20110131 VALUES LESS THAN (TO_DAYS('2011-01-31')),
PARTITION p20110215 VALUES LESS THAN (TO_DAYS('2011-02-15')),
PARTITION p20110228 VALUES LESS THAN (TO_DAYS('2011-02-28')),
PARTITION p20110315 VALUES LESS THAN (TO_DAYS('2011-03-15')),
PARTITION p20110331 VALUES LESS THAN (TO_DAYS('2011-03-31')),
PARTITION p20110415 VALUES LESS THAN (TO_DAYS('2011-04-15')),
PARTITION p20110430 VALUES LESS THAN (TO_DAYS('2011-04-30'))
); 

1. 积分系统先按照产品分表、再按照时间分区

根据界面，自动创建
1.界面添加产品
2.后台 创建分区表，
3.插入分区配置sys_partition_cfg
4.预创建分区

nosql

转载于:https://my.oschina.net/itnms/blog/465750