欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

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

程序员文章站 2022-06-08 22:05:26
...
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从句中包含分区键
  • 具有主键或唯一索引的表,主键或唯一索引必须是分区键的一部分