MySQL

MySQL分区表的使用

Posted by jintang on 2016-02-11

分区表的使用

  1. 结合业务场景选择分区键，避免跨分区查询
  2. 对分区表进行查询最好在WHERE从句中包含分区键
  3. 具有主键或唯一索引的表，主键或唯一索引必须是分区键的一部分

分区表实例

用户登录日志(使用range分区，按照登录时间的“年”做分区键)：

1
2
3
4
5
6
7
8
9
10
CREATE TABLE `customer_login_log` (
  `customer_id` int(10) unsigned NOT NULL,
  `login_time` datetime NOT NULL,
  `login_ip` int(10) unsigned NOT NULL,
  `login_type` tinyint(4) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
/*!50100 PARTITION BY RANGE (year(login_time))
(PARTITION p0 VALUES LESS THAN (2014) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (2015) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (2016) ENGINE = InnoDB) */

2015以前的数据归档：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# 建立2015年前归档表
CREATE TABLE `arch_customer_login_log_2015` (
  `customer_id` int(10) unsigned NOT NULL,
  `login_time` datetime NOT NULL,
  `login_ip` int(10) unsigned NOT NULL,
  `login_type` tinyint(4) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

# 使用交换分区操作
# 把2015以前的数据归档到 arch_customer_login_log_2015表中

alter table customer_login_log exchange p1 with table arch_customer_login_log_2015;
# 此时customer_login_log表中2015以前的数据会移动到arch_customer_login_log_2015中

# 删除对应的分区
alter table customer_login_log drop partition p0;

增加2016年的数据分区：

1
alter table customer_login_log add parition (partition p3 values less than (2016));