mysql 表分区几种方式

最新推荐文章于 2024-06-12 13:27:20 发布

Ta来了Ta来了

最新推荐文章于 2024-06-12 13:27:20 发布

阅读量422

点赞数

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/u010519674/article/details/106885882

版权

mysql 专栏收录该内容

12 篇文章 1 订阅

订阅专栏

介绍

数据分区是一种物理数据库的设计技术，它的目的是为了在特定的SQL操作中减少数据读写的总量以缩减响应时间。
分区并不是生成新的数据表，而是将表的数据均衡分摊到不同的硬盘，系统或是不同服务器存储介质中，实际上还是一张表。

为何分区

表数据量很大
查询非常缓慢，历史数据比较多
如果历史数据要归档，将数据从原来的库中挪走

分区方式

HASH 分区
LIST 分区
RANGE 分区
KEY 分区

主要是通过以上4中分区方式进行分区操作

hash 分区

根据MOD将分区键计算后分到指定表区域
可以基本平均的分布
HASH分区键值必须是INT类型，或者通过函数转成INT

创建分区表

CREATE TABLE `customer_login_log` (
  `customer_id` int(10) unsigned NOT NULL COMMENT '登录用户ID',
  `login_time` datetime NOT NULL COMMENT '用户登录时间',
  `login_ip` int(10) unsigned NOT NULL COMMENT '登录IP',
  `login_type` tinyint(4) NOT NULL COMMENT '登录类型:0未成功 1成功'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY HASH(customer_id) PARTITIONS 4;  -- 设置分区
# 插入数据
insert into customer_login_log values(1,'2020-06-20 22:30:01',1,1);
insert into customer_login_log values(2,'2020-06-20 22:30:02',2,1);
insert into customer_login_log values(3,'2020-06-20 22:30:03',3,1);
insert into customer_login_log values(4,'2020-06-20 22:30:04',4,1);
insert into customer_login_log values(5,'2020-06-20 22:30:05',5,1);

生产了对应4个分区文件
在这里插入图片描述

查询

mysql> select * from customer_login_log;
+-------------+---------------------+----------+------------+
| customer_id | login_time          | login_ip | login_type |
+-------------+---------------------+----------+------------+
|           4 | 2020-06-20 22:30:04 |        4 |          1 |
|           1 | 2020-06-20 22:30:01 |        1 |          1 |
|           1 | 2020-06-20 22:30:01 |        1 |          1 |
|           2 | 2020-06-20 22:30:02 |        2 |          1 |
|           3 | 2020-06-20 22:30:03 |        3 |          1 |
+-------------+---------------------+----------+------------+
5 rows in set (0.00 sec)

mysql>

分了4个区，数据按照 id%4 排列

查看分区

mysql> explain partitions select * from customer_login_log; 
+----+-------------+--------------------+-------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table              | partitions  | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+--------------------+-------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | customer_login_log | p0,p1,p2,p3 | ALL  | NULL          | NULL | NULL    | NULL |    5 |   100.00 | NULL  |
+----+-------------+--------------------+-------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 2 warnings (0.00 sec)

p0,p1,p2,p3 四个分区

查看每个分区数据

mysql> select table_name, partition_name, partition_description, table_rows from information_schema.partitions where table_name='customer_login_log'; 
+--------------------+----------------+-----------------------+------------+
| table_name         | partition_name | partition_description | table_rows |
+--------------------+----------------+-----------------------+------------+
| customer_login_log | p0             | NULL                  |          1 |
| customer_login_log | p1             | NULL                  |          2 |
| customer_login_log | p2             | NULL                  |          1 |
| customer_login_log | p3             | NULL                  |          1 |
+--------------------+----------------+-----------------------+------------+
4 rows in set (0.00 sec)

查看分区数据

mysql> select * from customer_login_log partition(p2,p3); 
+-------------+---------------------+----------+------------+
| customer_id | login_time          | login_ip | login_type |
+-------------+---------------------+----------+------------+
|           2 | 2020-06-20 22:30:02 |        2 |          1 |
|           3 | 2020-06-20 22:30:03 |        3 |          1 |
+-------------+---------------------+----------+------------+
2 rows in set (0.00 sec)

mysql> select * from customer_login_log partition(p2,p3) where customer_id=3;
+-------------+---------------------+----------+------------+
| customer_id | login_time          | login_ip | login_type |
+-------------+---------------------+----------+------------+
|           3 | 2020-06-20 22:30:03 |        3 |          1 |
+-------------+---------------------+----------+------------+
1 row in set (0.00 sec)

list 分区

按照分区键的枚举来进行分区的
各分区的列表不能重复
每一行数据都必须要找到对应的分区才能插入数据

分区表

CREATE TABLE `customer_login_log_list` (
  `customer_id` int(10) unsigned NOT NULL COMMENT '登录用户ID',
  `login_time` datetime NOT NULL COMMENT '用户登录时间',
  `login_ip` int(10) unsigned NOT NULL COMMENT '登录IP',
  `login_type` tinyint(4) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY LIST(login_type)(
	PARTITION jishu VALUES in (1,3,5,7,9),
	PARTITION oushu VALUES in (2,4,6,8)
);

# 插入数据
insert into customer_login_log_list values(1,'2020-06-20 22:30:01',1,1);
insert into customer_login_log_list values(2,'2020-06-20 22:30:02',2,2);
insert into customer_login_log_list values(3,'2020-06-20 22:30:03',3,3);
insert into customer_login_log_list values(4,'2020-06-20 22:30:04',4,4);

只能保存分区中已存在数据

mysql> insert into customer_login_log_list values(6, '2020-06-21 18:06:06',4,10);
ERROR 1526 (HY000): Table has no partition for value 10

删除分区

mysql> alter table customer_login_log_list drop partition jishu;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 
mysql> explain partitions select * from customer_login_log_list;
+----+-------------+-------------------------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table                   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------------------------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | customer_login_log_list | oushu      | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | NULL  |
+----+-------------+-------------------------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 2 warnings (0.00 sec)

mysql>

range分区

根据分区的不同范围值将数据放不同文件中
多个分区要连续，不能重叠
要有封口的MAXVALUE

创建分区表

CREATE TABLE `customer_login_log_range` (
  `customer_id` int(10) unsigned NOT NULL COMMENT '登录用户ID',
  `login_time` datetime NOT NULL COMMENT '用户登录时间',
  `login_ip` int(10) unsigned NOT NULL COMMENT '登录IP',
  `login_type` tinyint(4) NOT NULL COMMENT '登录类型:0未成功 1成功'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE(YEAR(login_time))(
	PARTITION y2017 VALUES LESS THAN (2017),
	PARTITION y2018 VALUES LESS THAN (2018),
	PARTITION y2019 VALUES LESS THAN (2019),
	PARTITION y2020 VALUES LESS THAN (2020),
	PARTITION maxyear VALUES LESS THAN MAXVALUE
);

insert into customer_login_log_range values(1,'2016-06-20 22:30:01',1,1);
insert into customer_login_log_range values(2,'2017-06-20 22:30:02',2,2);
insert into customer_login_log_range values(3,'2018-06-20 22:30:03',3,3);
insert into customer_login_log_range values(5,'2019-06-20 22:30:04',4,4);
insert into customer_login_log_range values(6,'2020-06-20 22:30:04',4,4);
insert into customer_login_log_range values(7,'2021-06-20 22:30:04',4,4);
insert into customer_login_log_range values(8,'2022-06-20 22:30:04',4,4);
insert into customer_login_log_range values(9,'2023-06-20 22:30:04',4,4);

分区查看

mysql> explain partitions select * from customer_login_log_range;
+----+-------------+--------------------------+---------------------------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table                    | partitions                      | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+--------------------------+---------------------------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | customer_login_log_range | y2017,y2018,y2019,y2020,maxyear | ALL  | NULL          | NULL | NULL    | NULL |    8 |   100.00 | NULL  |
+----+-------------+--------------------------+---------------------------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 2 warnings (0.00 sec)

mysql>

分区数据查看

mysql> select table_name, partition_name, partition_description, table_rows from information_schema.partitions where table_name='customer_login_log_range';    
+--------------------------+----------------+-----------------------+------------+
| table_name               | partition_name | partition_description | table_rows |
+--------------------------+----------------+-----------------------+------------+
| customer_login_log_range | y2017          | 2017                  |          1 |
| customer_login_log_range | y2018          | 2018                  |          1 |
| customer_login_log_range | y2019          | 2019                  |          1 |
| customer_login_log_range | y2020          | 2020                  |          1 |
| customer_login_log_range | maxyear        | MAXVALUE              |          4 |
+--------------------------+----------------+-----------------------+------------+
5 rows in set (0.01 sec)