mysql分区表
分区表的特点,在逻辑上为一个表,在物理上存储在多个文件中。
create table `login_log`(
id int(10) unsigned not null comment'登陆用户id',
time timestamp not null default current_timestamp,
ip int(10) unsigned not null comment'登陆类型'
)engine=innodb default charset=utf8 partition by hash(id) partitions 4;
分区键
分区引入分区键的概念,分区键用于根据某个区间值、特定值、或哈希函数值执行数据的聚集,让数据根据规则分布在不同的分区中。
分区类型
- RANGE分区
- LIST分区
- HASH分区
无论哪种分区类型,要么分区表上没有主键/唯一键,要么分区表的分区键必须是主键/唯一键;就是说有主键/唯一键的表,不能使用其他字段分区。
RANGE分区
- 根据分区键值的范围把数据行存储到不同分区中
- 多个分区的范围要连续,但是不能重叠
- 分区的范围是左闭右开的,若设置100,取不到100
create table `partition_range`(
id int(10) unsigned not null comment '登陆id',
time timestamp not null default current_timestamp,
ip int(10) unsigned not null comment '登陆ip'
)engine=innodb
partition range(id)(
partition p0 values less than(1000), # 实际范围0~999
partition p1 values less than(2000), #实际范围1000~1999
partition p2 values less than(3000), #实际范围2000~2999
partition by p3 values less than maxvalue #大于3000的数据
);
-- 插入数据
insert into partition_range values(1,default,1);
insert into partition_range values(1000,default,1000);
insert into partition_range values(2000,default,2000);
insert into partition_range values(3000,default,3000);
-- explain
mysql> explain select id from partition_range where id=1;
+----+-------------+-----------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | partition_range | p0 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |
+----+-------------+-----------------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select id from partition_range where id=3000;
+----+-------------+-----------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | partition_range | p3 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |
+----+-------------+-----------------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
-- 查看分区纲要
select partition_name,partition_expression,partition_description,table_rows
from information_schema.partitions
where table_schema=schema() and table_name='partition_range';
+----------------+----------------------+-----------------------+------------+
| PARTITION_NAME | PARTITION_EXPRESSION | PARTITION_DESCRIPTION | TABLE_ROWS |
+----------------+----------------------+-----------------------+------------+
| p0 | `id` | 1000 | 1 |
| p1 | `id` | 2000 | 1 |
| p2 | `id` | 3000 | 1 |
| p3 | `id` | MAXVALUE | 1 |
+----------------+----------------------+-----------------------+------------+
-- 如果想加一个p4是加不进去的,
--因为maxvalue的存在,
--但可以对p3做一个拆分
alter table partition_range reorganize partition p3 into(
partition p3 values less than(4000),
partition pmax values less than maxvalue
);
-- 插入新的分区
alter table partition_range add partition(partition p4 values less than(5000));
-- 删除分区
alter table partition_range drop partition p0;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-2poHVWD6-1578148191848)(D896260E8AFC47B38498CE34DFB660F3)]
应用场景
- 分区键为日期或时间类型
- 经常运行包含分区的查询,mysql可以快速确定只从某一个或某些分区扫描,如id小于1000的数据,只需扫描p0
- 定期按分区范围清理数据
HASH分区
- 根据MOD的值把数据行存储到表的不同分区内
- 数据可以平均分布在各个分区
- HASH分区的键值必须是一个int类型的值,或是通过函数可以转为INT类型的
-- 文章开始建的分区表就是哈希分区
-- 需要注意的是如果以时间分区
create table `login_log`(
id int(10) unsigned not null comment'登陆用户id',
time timestamp not null default current_timestamp,
ip int(10) unsigned not null comment'登陆类型'
)engine=innodb default charset=utf8 partition by hash(unix_timestamp(login_time)) partitions 4;
LIST分区
- 按分区键取值的列表进行分区
- 各个分区的链表值不能重复
- 插入的数据必须有对应的分区
create table partition_list(
id int(10) unsigned not null,
time timestamp not null default current_timestamp,
ip int(10) unsigned not null
)engine=innodb
partition by list(id)(
partition p0 values in(1,3,5,7,9),
partition p1 values in(2,4,6,8)
);
查询表纲要
select table_name,partition_name,partition_expression,partition_description,table_rows
from information_schema.partitions
where table_schema=schema() and table_name='partition_range';
修改分区-添加分区
alter table partition_list add partition(partition p3 values in (10,11,12));
分区删除
alter table partition_list drop partition p3;
注意
- 结合业务场景选择分区键,避免跨分区查询
- 对分区表进行查询最好在where从句中包含分区键
- 具有主键或唯一索引的表,主键或唯一索引必须是分区键