mysql分区表

1.什么是分区？

​ 所谓的分区就是将一个表分解成多个区块进行操作和保存，从而降低每次操作的数据，提高性能，而对应用来说是透明的，从逻辑上看就只是一个表（这里跟分库分表的访问不一样），但是物理上的这个表可能是由多个物理分区组成，每个分区都是一个独立的对象，可以进行独立处理。

2.分区目的？

1.进行逻辑数据分割，分割数据能够有多个不同的物理文件路径。

2.可以保存更多的数据，突破系统单个文件的最大限制。

3.提升性能，提高每个分区的读写速度，提高分区范围查询的速度。

4.可以通过删除相关分区来快速删除数据。

5.通过跨多个磁盘来分散数据查询，从而提高磁盘I/O的性能。

6.涉及到例如SUM()和COUNT()这样聚合函数的查询，可以很容易地进行并行处理。

7.分区能支持引擎。

3.分区的类型

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

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

3.HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算，这个函数必须产生非负整数值。

4.KEY分区：类似于按HASH分区，由MySQL服务器提供其自身的哈希函数。

注意事项：不论什么类型的分区，都要注意以下问题：

1.如果表中存在primary key 或者 unique key 时，分区的列必须是primary key 或者 unique key的一个组成部分，也就是说，分区函数的列只能从primary key或者 unique key这些key中取子集。

2.如果表中不存在任何的primary key 或者 unique key 时，则可以指定任何一个列作为分区列。

3.MySQL5.5版本之前的Range、List、Hash分区要求分区键必须是int；MySQL5.5及以上，支持非整型的Range和List分区，即：range columns 和 list columns。

4.分区的优点

1.性能提升: 对于大量数据的查询操作，如果查询条件与分区键相符合，MySQL可以只扫描相关分区，避免全表扫描。

2.维护简便: 对于数据量很大的表，分区可以方便数据的维护工作，例如清除旧数据时只需删除整个分区即可。

3.备份和恢复: 单个分区的备份和恢复速度比整张表快很多。

4.高并发: 分区表可以在物理级别上提供更好的并发支持，因为不同分区的读写操作可以在不同的硬件资源上并行进行。

5.分区限制

虽然分区增加了表的灵活性，但也有一些限制：

1.分区表的所有分区必须使用相同的存储引擎。

2.MySQL不允许分区表具有外键约束，同时也不允许分区表被用作其他表的外键引用。

3.表的分区表达式中使用的所有列都必须是表可能具有的每个唯一键（包括任何主键）的一部分。

4.分区表达式中不允许使用：存储过程、存储函数、可加载函数或插件。声明的变量或用户变量。
很多 MySQL 函数和运算符的结果可能会根据服务器 SQL 模式而变化。因此，在创建分区表后的任何时候更改 SQL 模式都可能导致此类表的行为发生重大变化，并且很容易导致数据损坏或丢失。所以，强烈建议您在创建分区表后永远不要更改服务器 SQL 模式。

6.分区示例：

CREATE TABLE `log_center_ng_log_dtl` (
   window_endtime datetime DEFAULT NULL COMMENT '窗口开始时间',
   succ_login_cnt int DEFAULT NULL COMMENT '成功调用量',
   fail_login_cnt int DEFAULT NULL COMMENT '失败调用量',
   KEY `window_start_index` (`window_endtime`)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
   

-- 按月分区
ALTER TABLE log_center_ng_log_dtl PARTITION BY RANGE (TO_DAYS(window_endtime)) (
    PARTITION p20240101 VALUES LESS THAN (TO_DAYS('2024-01-31')),
    PARTITION p20240201 VALUES LESS THAN (TO_DAYS('2024-02-29')),
	PARTITION p20240301 VALUES LESS THAN (TO_DAYS('2024-03-31')),
    PARTITION p20240401 VALUES LESS THAN (TO_DAYS('2024-04-30')),
	PARTITION p20240501 VALUES LESS THAN (TO_DAYS('2024-05-31')),
    PARTITION p20240601 VALUES LESS THAN (TO_DAYS('2024-06-30')),
	PARTITION p20240701 VALUES LESS THAN (TO_DAYS('2024-07-31')),
    PARTITION p20240801 VALUES LESS THAN (TO_DAYS('2024-08-31')),
	PARTITION p20240901 VALUES LESS THAN (TO_DAYS('2024-09-30')),
    PARTITION p20241001 VALUES LESS THAN (TO_DAYS('2024-10-31')),
    PARTITION p20241101 VALUES LESS THAN (TO_DAYS('2024-11-30')),
    PARTITION p20241201 VALUES LESS THAN (TO_DAYS('2024-12-31'))
);




CREATE TABLE `log_center_bos_login_dtl` (
   window_endtime datetime DEFAULT NULL COMMENT '窗口开始时间',
   succ_login_cnt int DEFAULT NULL COMMENT '调用量',
   fail_login_cnt int DEFAULT NULL COMMENT '调用量',
   KEY `window_start_index` (`window_endtime`)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8;


   
-- 按年分区   
ALTER TABLE log_center_bos_login_dtl PARTITION BY RANGE (TO_DAYS(window_endtime)) (
    PARTITION p2024 VALUES LESS THAN (TO_DAYS('2024-12-31')),
    PARTITION p2025 VALUES LESS THAN (TO_DAYS('2025-12-31'))
);   

添加时间分区脚本

#!/bin/sh
echo "======${0} start ==`date +%Y-%m-%d_%H:%M:%S`=="


source /home/hdfs/.bash_profile 

#mysql报表增加分区，每月一号执行，增加当月所有分区

if [ "$1" != "" ]
then
                today=`date -d "" +%Y%m01`;
else
                today=`date -d "$1" +%Y%m%d`;
fi

filepath=/data/work/cmbh/results

start_dt=`date -d "$today" +%Y%m01`;
end_dt=`date -d "$start_dt +1 month -1 day" +%Y%m%d`;
par_core_dt=""
par_core_wk=""

table_list_dt="
rt_table_dt1
rt_table_dt2
rt_table_dt3
"

table_list_wk="rt_table_wk"

table_list_mt="
rt_table_mt1
rt_table_mt2
rt_table_mt3
"

for ((i=0;i<= $(($(date +%s -d "$end_dt") - $(date +%s -d "$start_dt")))/86400 |bc ;i++)) ;do
        par_core_dt+="PARTITION p_$(date -d "$start_dt +$i day" +%Y%m%d) VALUES LESS THAN (to_days('$(date -d "$start_dt +$((i+1)) day" +%Y-%m-%d)')),"
        if [ `date -d "$start_dt +$i day" +%w` = "1" ]
        then
                par_core_wk+="PARTITION p_$(date -d "$start_dt +$i day" +%Y%m%d) VALUES LESS THAN (to_days('$(date -d "$start_dt +$((i+7)) day" +%Y-%m-%d)')),"
        fi
done

cat /dev/null > $filepath/add_partition.sql
#按天分区
for table_name in ${table_list_dt[@]};do
  echo "ALTER TABLE ${table_name} ADD PARTITION (${par_core_dt%?});" >> $filepath/add_partition.sql
done

#按周分区
echo "ALTER TABLE ${table_list_wk} ADD PARTITION (${par_core_wk%?});" >> $filepath/add_partition.sql

#按月分区
for table_name in ${table_list_mt[@]};do
  echo "ALTER TABLE ${table_name} ADD PARTITION (PARTITION p_$start_dt VALUES LESS THAN (to_days('$(date -d "$end_dt" +%Y-%m-%d)')));" >> $filepath/add_partition.sql
done


mysql ${dw3_connect} -N -f --default-character-set=utf8 -s -h${ip} -u${user}  -P${port} ${database} <<EOD
source $filepath/add_partition.sql
EOD