MySQL 表分區查詢與新增分區實戰指南

MySQL 表分區查詢與新增分區實戰指南

1. 查詢現有分區資訊

SELECT
  partition_name AS part,
  partition_expression AS expr,
  partition_description AS descr,
  FROM_DAYS(partition_description) AS lessthan_sendtime,
  table_rows
FROM INFORMATION_SCHEMA.partitions
WHERE TABLE_SCHEMA = SCHEMA()
  AND TABLE_NAME = 'td_sendmessagelog';

輸出各分區名稱、分區運算式、描述（如 TO_DAYS('2015-02-10')）、對應日期與行數。

2. 新增範圍分區

ALTER TABLE td_sendmessagelog ADD PARTITION (
  PARTITION p20150210 VALUES LESS THAN (TO_DAYS('2015-02-10')),
  PARTITION p20150220 VALUES LESS THAN (TO_DAYS('2015-02-20')),
  PARTITION p20150301 VALUES LESS THAN (TO_DAYS('2015-03-01')),
  PARTITION p20150310 VALUES LESS THAN (TO_DAYS('2015-03-10')),
  PARTITION p20150320 VALUES LESS THAN (TO_DAYS('2015-03-20')),
  PARTITION p20150401 VALUES LESS THAN (TO_DAYS('2015-04-01')),
  PARTITION p20150410 VALUES LESS THAN (TO_DAYS('2015-04-10')),
  PARTITION p20150420 VALUES LESS THAN (TO_DAYS('2015-04-20')),
  PARTITION p20150501 VALUES LESS THAN (TO_DAYS('2015-05-01')),
  PARTITION pmax VALUES LESS THAN (MAXVALUE)
);

3. 建表時指定分區鍵（關鍵）

CREATE TABLE td_sendmessagelog (
  id INT NOT NULL,
  sendtime DATETIME NOT NULL,
  PRIMARY KEY (id, sendtime)  -- 分區鍵必須包含在主鍵中
) PARTITION BY RANGE (TO_DAYS(sendtime)) (
  PARTITION p20150101 VALUES LESS THAN (TO_DAYS('2015-01-01')),
  PARTITION pmax VALUES LESS THAN (MAXVALUE)
);

4. 建立輔助索引

CREATE INDEX idx_sid_sendtime ON td_sendmessagelog (sid, sendtime);

注意事項

• 分區鍵必須包含於主鍵/唯一鍵中
• MAXVALUE 分區捕獲超出範圍資料，避免插入失敗
• 分區裁剪需查詢條件包含分區鍵（如 WHERE sendtime >= '2015-03-01'）
• 定期維護：ANALYZE TABLE、OPTIMIZE TABLE、監控分區行數平衡