MySQL分区表使用场景及特点介绍

MySQL分区表使用场景及特点介绍

本文旨在介绍MySQL分区表的运用场景及其特点，MySQL分区表主要有

• Hash分区表
• Range分区表
• LIST分区表

一、Hash分区表
Hash分区表特点：逻辑上为一个表，但在物理上存储在多个文件中，以用户登陆日志表为例,

user_login_log(
  log_id       int         '日志ID',
  login_id     int         '登陆用户ID',
  login_time   datetime    '用户登陆时间',
  login_ip     int         '登陆IP',
  login_result int          登陆结果：0未成功，1成功',
  PRIMARY KEY pk_loginid(log_id)
) ENGINE = innodb COMMENT '用户登陆日志表'
PARTITION BY HASH(login_id) PARTITIONS 4;
与一般创建表的区别就在于加了 PARTITION这个命令。

文件结构上的区别

普通表结构:
user_login_log.frm
user_login_log.ibd

Hash分区表结构:
user_login_log.frm
user_login_log#P#p0.ibd
user_login_log#P#p1.ibd
user_login_log#P#p2.ibd
user_login_log#P#p3.ibd

Hash分区表的特点:

• 根据MOD（分区建，分区数）的值把数据行存储到表的不同分区
• 数据可以平均的分布在各个分区中
• HASH分区的键值必须是一个INT类型的值，或是通过函数可以转为INT类型比如 UNIX_TIMESTAMP(login_time)

二、Range分区表
Range分区表的特点：根据分区键值的范围把数据行存储到表的不同分区中
多个分区的范围要连续，但是不能重复
默认情况下使用VALUES LESS THAN属性，即每个分区不包括指定的那个值

user_login_log(
  log_id       int         '日志ID',
  login_id     int         '登陆用户ID',
  login_time   datetime    '用户登陆时间',
  login_ip     int         '登陆IP',
  login_result int          登陆结果：0未成功，1成功',
  PRIMARY KEY pk_loginid(log_id)
) ENGINE = innodb COMMENT '用户登陆日志表'
PARTITION BY RANGE (login_id ) (
    PARTITION p0 VALUES LESS THAN (10000),
    PARTITION p1 VALUES LESS THAN (10000),
    PARTITION p2 VALUES LESS THAN (10000),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);
Range分区表的特点:

• 分区键为日期或是时间类型
• 所有SELECT查询中都包括分区键

三、LIST分区表

LIST分区表的特点：

• 按分区键取值的列表进行分区
• 同范围分区一样，各分区的列表值不能重复
• 每一行数据必须能找到对应的分区列表，否则数据插入失败

user_login_log(
  log_id       int         '日志ID',
  login_id     int         '登陆用户ID',
  login_time   datetime    '用户登陆时间',
  login_ip     int         '登陆IP',
  login_result int          登陆结果：0未成功，1成功',
  PRIMARY KEY pk_loginid(log_id)
) ENGINE = innodb COMMENT '用户登陆日志表'
PARTITION BY LIST (login_result) (
    PARTITION p0 VALUES (1,3,5,7,9),
    PARTITION p1 VALUES (2,4,6,8)
);

使用分区表的注意事项

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