mysql表分区及批量定时建分区

我们经常在数仓hive里边使用各式分区，而在mysql表里大家可能很少使用分区。其实，mysql也有分区功能，通过分区处理能提高一些数据里大、有明显规则的增量mysql表的查询性能。下面我们来给大家介绍一些mysql的分区方法。

1.mysql分区类型

RANGE分区：基于属于一个给定连续区间的列值，把多行分配给分区。

LIST分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。

HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。

KEY分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值。

下面给大家用sql代码说明分区建立方法：

RANGE分区Sql代码：

CREATE TABLE IF NOT EXISTS `file_logs` (
  `BATCH_ID`     INT(11) NOT NULL COMMENT '任务ID',
  `FILE_SIZE`    VARCHAR(200) DEFAULT NULL COMMENT '文件大小',
  `FILE_TIME`    TIMESTAMP COMMENT '文件生成时间',
  `FILE_PATH`    VARCHAR(200) NOT NULL COMMENT '文件路径',
  `FILE_NAME`    VARCHAR(200) NOT NULL COMMENT '文件名',
  `DATA_TYPE`    VARCHAR(5) DEFAULT NULL COMMENT '数据类型',
  `DAY_ID` INT(11) NOT NULL COMMENT '开始时间',
  PRIMARY KEY (`FILE_NAME`,DAY_ID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE(DAY_ID)(
PARTITION p20210101 VALUES less than (20210102),
PARTITION p20210102 VALUES less than (20210103),
PARTITION p20210103 VALUES less than (20210104));

注意：这种分区会把day_id=20210102的数据放到下一个分区p20210103 ，day_id=20210103的数据放到下一个分区p20210104…

LIST分区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)
);

LIST分区类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择

HASH分区和KEY分区比较复杂，也不常用，这里不做介绍

2. 分区操作的一些常用命令

#查看表分区

select partition_name part, partition_expression expr, partition_description descr, table_rows 
from information_schema.partitions  where table_schema = schema() and table_name='file_logs';  

#查找表分区数据

select count(*) from file_logspartition (p20210212); 

#删除分区

alter table file_logs drop partition p20210212; 

#添加分区,只能连续递增添加

alter table file_logs add partition (partition p20201227 values less than (20210228));  

#分解分区（不丢失数据）

alter table file_logs reorganize partition p20210223 into
 (partition p20210222 values less THAN (20210223),
 partition p20210223 values less than (20210224)
);

#合并分区（不丢失数据）

alter table file_logs reorganize partition p20210222,p20210223 INTO (partition p1 values less than (20210224));

3. 批量建RANG类型分区

按yyyMMdd这种日期值做分区值，每个月份天数一样，进行提前批量建分区会造成一些问题。以上面range类型分区示例，day_id是int类型，其值在月底和下个月之间数值不连续，如20210227，20210228，20210301，20210302，日期20210228和20210301并不是连续递增的。这样会在批量建分区时造成一些问题，下面我们用脚本createpartitions.sh做了一些处理，保证批量建分区日期值正常。使用crontab定时每月月初批量量建下一月的表分区。

#!/bin/bash

# mysql config
MYSQL_IP=xx.xx.xx.xx
MYSQL_PORT=xx
MYSQL_USER=xx
MYSQL_PWD=xx
MYSQL_DBS=xx
TABLE_NAME=xx

# crete partitions
day=`date -d "1 months" +"%Y%m%d"`
#day=$1
tmonth=`date -d "$day" +"%m"`
tmonthformat=`printf "%d" $tmonth`
days_list=(31 28 31 30 31 30 31 31 30 31 30 31)
day_delay=${days_list[tmonthformat-1]}

for (( i=1; i<=$day_delay; i++));do
day_id=`date -d "$i days ${day}" +"%Y%m%d"`
day_partition=`date -d "$((i+1)) days ${day}" +"%Y%m%d"`
add_partition="alter table ${TABLE_NAME} add partition(partition p${day_id} values less than (${day_partition}));"
echo "=====excute comand: $add_partition"
mysql --force -h${MYSQL_IP} -P${MYSQL_PORT} -u${MYSQL_USER} -p${MYSQL_PWD} -D${MYSQL_DBS} --local-infile=1 --default-character-set=utf8 -e "${add_partition};"
done

定时任务：

crontab -e     #或者vi /etc/crontab 编辑定时时间

* 12 1 * *  sh createpartitions.sh >> dev/null   #定时每月一号执行