MySql分区

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从句中包含分区键
  • 具有主键或唯一索引的表,主键或唯一索引必须是分区键
### MySQL 分区表的概念与实现方法 #### 什么是分区? 在 MySQL 中,分区是一种将大表拆分为更小、更容易管理的部分的技术。通过分区,可以显著提高查询效率并简化数据维护工作。当 `have_partition_engine` 的值为 `YES`[^1],表示当前 MySQL 版本支持分区功能。 --- #### 分区的类型 MySQL 支持多种类型的分区方式,每种适用于不同的场景: 1. **Range 分区** 将数据按照某个字段范围分布到不同分区中。例如,可以根据日期或数值区间来划分。 ```sql CREATE TABLE sales ( id INT NOT NULL, sale_date DATE NOT NULL, amount DECIMAL(10,2) ) PARTITION BY RANGE (YEAR(sale_date)) ( PARTITION p0 VALUES LESS THAN (2020), PARTITION p1 VALUES LESS THAN (2021), PARTITION p2 VALUES LESS THAN MAXVALUE ); ``` 2. **List 分区** 类似于 Range 分区,但用于离散值集合而非连续范围。 ```sql CREATE TABLE employees ( emp_id INT NOT NULL, region VARCHAR(10) NOT NULL ) PARTITION BY LIST(region) ( PARTITION north VALUES IN ('North'), PARTITION south VALUES IN ('South'), PARTITION east VALUES IN ('East'), PARTITION west VALUES IN ('West') ); ``` 3. **Hash 分区** 使用哈希函数自动分配记录到指定数量的分区中。 ```sql CREATE TABLE logs ( log_id INT NOT NULL, message TEXT ) PARTITION BY HASH(log_id) PARTITIONS 4; ``` 4. **Key 分区** 类似 Hash 分区,但基于内置哈希算法。 ```sql CREATE TABLE users ( user_id INT NOT NULL, username VARCHAR(50) ) PARTITION BY KEY(user_id) PARTITIONS 8; ``` 5. **Composite 分区** 结合两种分区策略(如 Range 和 Hash),形成复合分区。 ```sql CREATE TABLE transactions ( trans_id INT NOT NULL, trans_date DATE NOT NULL ) PARTITION BY RANGE(YEAR(trans_date)) SUBPARTITION BY HASH(MONTH(trans_date)) SUBPARTITIONS 12 ( PARTITION p0 VALUES LESS THAN (2020), PARTITION p1 VALUES LESS THAN (2021), PARTITION p2 VALUES LESS THAN MAXVALUE ); ``` --- #### 如何管理分区? ##### 创建分区表 可以通过 `CREATE TABLE ... PARTITION BY` 语句定义分区逻辑。具体语法取决于所选的分区类型[^3]。 ##### 修改现有表的分区 如果已有表未启用分区,可通过 `ALTER TABLE` 添加分区: ```sql ALTER TABLE existing_table PARTITION BY RANGE (column_name) ( PARTITION p0 VALUES LESS THAN (value1), PARTITION p1 VALUES LESS THAN (value2), PARTITION pn VALUES LESS THAN MAXVALUE ); ``` ##### 删除分区 删除特定分区时,需注意其行为可能会影响数据重分配。例如: ```sql ALTER TABLE student DROP PARTITION student_2020; -- 此命令会将被删除分区的数据重新分配至其他分区[^2] ``` ##### 合并分区 对于相邻的 Range 或 List 分区,可执行合并操作以减少碎片化: ```sql ALTER TABLE sales REORGANIZE PARTITION p0, p1 INTO ( PARTITION p_new VALUES LESS THAN (2021) ); ``` ##### 查看分区信息 使用以下 SQL 查询获取有关分区的元数据: ```sql SELECT * FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='your_table'; ``` --- #### 性能优化建议 1. 避免频繁更改分区结构,因为这可能导致锁表或其他性能开销。 2. 对于大数据量表,优先考虑按时间戳或业务属性进行分区。 3. 定期清理过期分区中的无用数据,释放存储空间。 ---
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值