2.mysql分区表

一.确认mysql服务器是否支持分区表

mysql>show plugins;

这里写图片描述

二. 在逻辑上为一个表,在物理上存储在多个文件中

这里写图片描述

三.创建分区表的方式(常用的几种方法)

 1. 按hash分区

1) hash分区的特点
    根据mod的值把数据行存储到表的不同分区中
    数据可以平局的分布在各个分区中
    hash分区的键值必须是一个int类型的值,或者通过函数可以转为int类型
2) 如何建立分区表

利用表中的整型列,创建分区表

create table text(
    id int not null,
    login_time timestamp not null;
)engine=innodb
partition by hash(id) //根据id分区
partitions 4; // 分区数量

利用表中的非整型列,创建分区表

create table text(
    id int not null,
    login_time timestamp not null;
)engine=innodb
//根据login_time 分区,login_time 不是整型转为整型
partition by hash(UNIX_TIMESTAMP(login_time )) 
partitions 4;

mysql常用的转整型的函数
这里写图片描述

2.按范围分区(RANGE)

1)特点

- 根据分区键值的范围吧数据行存储到表的不同分区中
- 多个分区的范围要连续但是不能重叠
-  默认情况下使用values less than属性即每个分区不包括指定的值

2)如何建立range分区

create table text(
    id int not null,
    login_time timestamp not null;
)engine=innodb
partition by range(id)(//根据id分区
    partition p0 values less than(1000),//0-999
    partition p1 values less than(2000),//1000 - 1999
    partition p2 values less than maxvalue >2000
);

3) 使用场景

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

list分区

1)特点
- 按分区键取值的列表进行分区
- 同范围分区一样,个分区的列表值不能重复
- 每一行数据必须能找到对应的分区列表,否则数据插入失败

2)如何建立list分区

create table text(
    id int not null,
    login_type int  not null , //取值为12345678
    login_time timestamp not null;
)engine=innodb
partition by LIST(login_type )(//根据login_type 分区
    partition p0 values in(1,3,5,7),
    partition p1 values in (2,4,6,8)
)

四.栗子

1.需求
设计用户登录表 customer_login_log表分区
2.业务场景
- 用户每次登录都会记录日志
- 用户登录日志保存一年一年后可以删除

3.使用range分区(login_time为分区键)

create table customer_login_log(
    customer_id int unsigned not null,
    login_time DATETIME NOT NULL,
    login_ip INT(10) UNSIGNED NOT NULL.
    login_type TINYINT(4) NOT NULL
)ENGINE = INNODB
PARTITION BY RANGE(YEAR(login_time))(
    PARTITION p0 VALUES LESS THAN(2015),
    PARTITION P1 VALUES LESS THAN(2016),
    PARTITION P2 VALUES LESS THAN(2017)
);

4.查看分区表的使用情况

select table_name partition_name , partition_description,table_rows
from information_schema.'partitions'
where table_name = 'customer_login_log'

5.增加分区

alter table customer_login_log add partition( partition p4 values less than(2018));

6.删除分区

//删除p0分区
alter table customer_login_log drop partition p0;

五.使用分区表的注意事项

  • 结合业务场景选择分区表,避免跨分区查询
  • 对分区表进行查询最好在where从句中包含分区键
  • 具有主键或者唯一索引的表,主键或者唯一索引必须是分区键的一部分
版权声明:本文为u014136910原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/u014136910/article/details/79248573