MySQL 5.7中基于UNIX_TIMESTAMP按部门+月度分区员工表是否可行？

当然可行！针对你的需求——先按部门划分，再按月份对门禁记录分区——MySQL 5.7完全支持这样的分区策略，结合**LIST分区（按部门ID）和RANGE子分区（按from_date的月份时间范围）**就能实现。下面我一步步给你讲清楚怎么做，还有需要注意的细节：

一、核心前提确认 #

你的表是InnoDB引擎，MySQL 5.7对InnoDB的分区支持很完善，这点没问题。不过有个关键注意点：

如果你的表后续要添加主键或唯一索引，必须包含所有分区列（也就是dep_id和from_date）。因为MySQL要求分区表的唯一索引（包括主键）必须覆盖分区键，否则无法创建。你当前的表没有主键，只有普通索引，所以暂时不需要调整，但如果之后要设主键，记得把dep_id和from_date加进去。

二、具体实现步骤 #

1. 确定分区逻辑 #

• 一级分区（部门）：用LIST分区，每个部门对应一个分区（比如dep_id为1到20，每个ID对应一个LIST分区）。
• 二级分区（月份）：在每个部门分区下，用RANGE子分区，按from_date的月份对应的UNIX时间戳范围划分（比如2024年1月的时间范围是1704067200到1706745599）。

2. 创建分区表（或转换现有表） #

如果你是新建表，可以直接用下面的语句；如果是现有表，建议先备份数据，再用ALTER TABLE转换。

新建分区表示例：

CREATE TABLE `employee` (
 `employee_id` smallint(5) NOT NULL,
 `dep_id` int(11) NOT NULL,
 `from_date` int(11) NOT NULL,
 `to_date` int(11) NOT NULL,
 KEY `index1` (`employee_id`,`from_date`,`to_date`),
 KEY `idx_partition` (`dep_id`, `from_date`) -- 添加分区键的索引，提升分区查询效率
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY LIST(dep_id)
SUBPARTITION BY RANGE(from_date) (
  -- 部门1的分区，包含12个月的子分区
  PARTITION p_dep1 VALUES IN (1) (
    SUBPARTITION p_dep1_jan2024 VALUES LESS THAN (1706745600),
    SUBPARTITION p_dep1_feb2024 VALUES LESS THAN (1709337600),
    SUBPARTITION p_dep1_mar2024 VALUES LESS THAN (1712016000),
    SUBPARTITION p_dep1_apr2024 VALUES LESS THAN (1714608000),
    SUBPARTITION p_dep1_may2024 VALUES LESS THAN (1717286400),
    SUBPARTITION p_dep1_jun2024 VALUES LESS THAN (1719878400),
    SUBPARTITION p_dep1_jul2024 VALUES LESS THAN (1722556800),
    SUBPARTITION p_dep1_aug2024 VALUES LESS THAN (1725235200),
    SUBPARTITION p_dep1_sep2024 VALUES LESS THAN (1727827200),
    SUBPARTITION p_dep1_oct2024 VALUES LESS THAN (1730505600),
    SUBPARTITION p_dep1_nov2024 VALUES LESS THAN (1733097600),
    SUBPARTITION p_dep1_dec2024 VALUES LESS THAN (1735776000)
  ),
  -- 部门2的分区，同理复制上面的子分区结构，把VALUES IN(1)改成VALUES IN(2)
  PARTITION p_dep2 VALUES IN (2) (
    SUBPARTITION p_dep2_jan2024 VALUES LESS THAN (1706745600),
    SUBPARTITION p_dep2_feb2024 VALUES LESS THAN (1709337600),
    -- ... 其余月份子分区省略，和部门1一致
    SUBPARTITION p_dep2_dec2024 VALUES LESS THAN (1735776000)
  ),
  -- 剩下的18个部门以此类推，或者如果有未分配的部门，可以加一个默认分区
  PARTITION p_dep_other VALUES IN (0) -- 可以用来存放dep_id异常的数据
);

转换现有表的ALTER语句示例：

如果要把已有的employee表改成分区表，先备份数据，然后执行：

ALTER TABLE employee
PARTITION BY LIST(dep_id)
SUBPARTITION BY RANGE(from_date) (
  -- 这里的分区结构和上面新建表的一样，把所有部门的分区和子分区列出来
  PARTITION p_dep1 VALUES IN (1) (
    SUBPARTITION p_dep1_jan2024 VALUES LESS THAN (1706745600),
    -- ... 其余子分区
  ),
  -- ... 其余部门分区
);

3. 时间戳范围的计算 #

上面的时间戳是2024年每个月的结束时间（下个月1号0点的UNIX时间戳），你可以用MySQL函数计算任意月份的时间戳：

-- 计算2024年1月结束的时间戳（即2024-02-01 00:00:00的UNIX时间戳）
SELECT UNIX_TIMESTAMP('2024-02-01 00:00:00');

这样就能准确得到每个月份对应的LESS THAN值。

三、后续维护建议 #

• 新增部门：如果以后有新的部门，用ALTER TABLE添加新的LIST分区：

  ALTER TABLE employee ADD PARTITION (
    PARTITION p_dep21 VALUES IN (21) (
      SUBPARTITION p_dep21_jan2024 VALUES LESS THAN (1706745600),
      -- ... 对应月份子分区
    )
  );
  

• 新增年份的月份：比如到2025年，需要给每个部门的分区添加新的子分区：

  ALTER TABLE employee
  MODIFY PARTITION p_dep1 ADD SUBPARTITION (
    SUBPARTITION p_dep1_jan2025 VALUES LESS THAN (1735862400),
    -- ... 2025年其余月份子分区
  );
  

• 查询优化：查询时尽量带上dep_id和from_date条件，这样MySQL可以直接定位到对应的分区，避免全表扫描。

四、替代方案：复合RANGE COLUMNS分区 #

如果你觉得子分区写起来太繁琐，也可以用RANGE COLUMNS复合分区，同时按dep_id和from_date的月份范围划分，比如：

CREATE TABLE `employee` (
 -- 表结构不变
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE COLUMNS(dep_id, from_date) (
  PARTITION p_dep1_jan2024 VALUES LESS THAN (1, 1706745600),
  PARTITION p_dep1_feb2024 VALUES LESS THAN (1, 1709337600),
  -- ... 部门1的其他月份
  PARTITION p_dep2_jan2024 VALUES LESS THAN (2, 1706745600),
  -- ... 部门2的其他月份
);

这种方式不需要子分区，直接把部门和月份作为复合分区键，但分区数量会是20*12=240个，和子分区的数量一致，维护起来各有优劣，你可以根据自己的习惯选择。

内容的提问来源于stack exchange，提问作者Chap2