mysql创建分区

作用

分区的作用：数据库性能的提升和简化数据管理

在扫描操作中，mysql优化器只扫描保护数据的那个分区以减少扫描范围获得性能的提高。

分区技术使得数据管理变得简单，删除某个分区不会对另外的分区造成影响，分区有系统直接管理不用手工干预。

mysql从5.1版本开始支持分区。每个分区的名称是不区分大小写。同个表中的分区表名称要唯一。

mysql分区类型

根据所使用的不同分区规则可以分成几大分区类型。

RANGE 分区：

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

LIST 分区：

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

HASH分区：

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

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

复合分区：

基于RANGE/LIST 类型的分区表中每个分区的再次分割。子分区可以是 HASH/KEY 等类型。

注意事项

-- 分区创建的时候必须是主键 根据主键进行分区
-- 一个表最多只能有1024个分区。
-- 如果分区字段中有主键或者唯一索引的列，那么所有主键列和唯一索引列都必须包含进来。
-- 分区表中无法使用外键约束。

有主键时必须含主键

容易踩的坑

1、数据表有数据时，创建分区表时报错。

2、不含主键创建分区表时报错。

创建分区语句

1、这是我参考文章按月自动设置表分区参考进行一系列的sql 操作。文章地址：

https://blog.csdn.net/LaFeng233/article/details/138531776

-- 创建分区函数
DELIMITER //
CREATE FUNCTION get_partition_name(p_date DATE)
RETURNS VARCHAR(20)
DETERMINISTIC
BEGIN
    DECLARE p_month VARCHAR(2);
    DECLARE p_year VARCHAR(4);
    SET p_month = LPAD(MONTH(p_date), 2, '0');
    SET p_year = YEAR(p_date);
    RETURN CONCAT('p', p_year, p_month); 
END//
DELIMITER ;

-- 检查是否成功创建函数
SHOW FUNCTION STATUS LIKE 'get_partition_name';

-- 创建存储过程 用于自动产生分区   TABLE_SCHEMA(数据库名称)
-- 存储过程
DELIMITER //
CREATE PROCEDURE create_monthly_partition()
BEGIN
    DECLARE next_month VARCHAR(20);
    DECLARE next_month_first_day DATE;
    
    -- 计算下一个月份的名称和下个月的第一天
    SET next_month = CONCAT('p', DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 1 MONTH), '%Y%m'));
    SET next_month_first_day = LAST_DAY(DATE_ADD(CURDATE(), INTERVAL 1 MONTH)) + INTERVAL 1 DAY;
    
    -- 检查分区是否已存在
    IF NOT EXISTS (
        SELECT NULL
        FROM information_schema.PARTITIONS
        WHERE TABLE_NAME = 't_task_state' AND TABLE_SCHEMA = 'sky' AND PARTITION_NAME = next_month
    ) THEN
        -- 创建新的分区
        SET @sql = CONCAT('ALTER TABLE t_task_state ADD PARTITION (PARTITION ', next_month, ' VALUES LESS THAN (TO_DAYS(\'', next_month_first_day, '\')))');
 
        PREPARE stmt FROM @sql; -- 预定义sql 
        EXECUTE stmt;  -- 执行预定义sql
        DEALLOCATE PREPARE stmt; -- 释放链接 
    END IF;
END//
DELIMITER ;


-- 删除存储过程
DROP PROCEDURE IF EXISTS create_monthly_partition;
-- 检查是否创建成功
SHOW PROCEDURE STATUS LIKE 'create_monthly_partition';
-- 执行函数
CALL create_monthly_partition();
--  执行过程中 > 1505 - Partition management on a not partitioned table is not possible 意思不能对已有数据的数据库进行分区。解决方案较优： 采用第二种方案。先创建分区表，然后导出原表数据，新表名称改为原表名，然后插入，最后建立普通索引。
-- 查询表分区，查看是否成功分区。
SELECT PARTITION_NAME FROM information_schema.PARTITIONS WHERE TABLE_NAME = 't_taskInfo' AND TABLE_SCHEMA = 'sky';

经过多番测试，取消使用根据时间按划分。主要原因为如果采用时间划分主键必须为时间这个不符合现在表设计。最终决定使用key() 没有使用hash的原因为hash 主键必须为数值类型。经过多番尝试之后使用key 可以。

当时考虑使用按月划分区的方案是考虑这样就可以根据月份保存数据，后续删除分区的时候明白数据时什么时候的。

成功的方案

-- 创建表
DROP TABLE IF EXISTS `t_task_state`;
CREATE TABLE `t_task_state`  (
  `id` varchar(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `msg_id` int NOT NULL,
  `task_type` smallint NOT NULL COMMENT '任务类型',
  `task_id` int NOT NULL COMMENT '任务ID',
  `task_prior` int NOT NULL COMMENT '任务优先级',
  `task_start` int NOT NULL COMMENT '任务执行标志',
  `exc_head_angle` decimal(5, 2) NOT NULL COMMENT '期望艏向',
  `exc_speed` decimal(5, 2) NOT NULL COMMENT '期望航速',
  `exc_p_num` int NOT NULL COMMENT '期望跟踪航点编号(折线任务)',
  `exc_angle` decimal(5, 2) NOT NULL COMMENT '期望跟踪角度(圆弧任务)',
  `p_num` smallint NULL DEFAULT NULL COMMENT '航点数目',
  `points` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '点集合',
  `create_time` datetime(6) NOT NULL COMMENT '创建时间',
  `create_user` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '创建人',
  `update_time` datetime(6) NULL DEFAULT NULL COMMENT '修改时间',
  `update_user` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '修改人',
  `valid` varchar(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '有效',
  `msg_text` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL COMMENT '源报文',
  `msg_time` datetime(6) NULL DEFAULT NULL COMMENT '报文时间',
  `task_avoid_cmd` varchar(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `task_avoid` int NULL DEFAULT NULL COMMENT '任务执行标志',
  `executing` varchar(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = DYNAMIC;

-- 创建分区策略
-- 根据 key 方式分区
ALTER TABLE t_task_state
PARTITION BY KEY(id)  PARTITIONS 10;

-- 根据 hash 方式分区----不可行
ALTER TABLE t_task_state
PARTITION BY HASH(id)  PARTITIONS 10;

-- 查看分区信息
SELECT
	PARTITION_NAME,
	PARTITION_METHOD,
	PARTITION_EXPRESSION,
	PARTITION_DESCRIPTION,
	TABLE_ROWS,
	SUBPARTITION_NAME,
	SUBPARTITION_METHOD,
	SUBPARTITION_EXPRESSION 
FROM
	information_schema.PARTITIONS 
WHERE
	TABLE_SCHEMA = SCHEMA () 
	AND TABLE_NAME = 't_task_state';


-- 查看分区 数据
select * from t_task_state PARTITION (p3);