RANGE 与 RANGE COLUMNS 范围分区

最新推荐文章于 2024-09-11 14:34:14 发布
最新推荐文章于 2024-09-11 14:34:14 发布
阅读量4k 收藏 1
点赞数
分类专栏： mysql



MySQL基于时间字段进行分区的方案总结


MySQL支持的分区类型一共有四种：RANGE，LIST，HASH，KEY。其中，RANGE又可分为原生RANGE和RANGE COLUMNS，LIST分为原生LIST和LIST COLUMNS，HASH分为原生HASH和LINEAR HASH，KEY包含原生KEY和LINEAR HASH。

 

实现方式

主要是以下几种：

1. 基于RANGE

2. 基于RANGE COLUMNS

3. 基于HASH

 

测试数据 

为了测试以上三种方案，特构造了100万的测试数据，放在test表中，test表只有两列：id和hiredate，其中hiredate只包含10天的数据，从2015-12-01到2015-12-10。具体信息如下：

复制代码 
mysql> show create table test\G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `id` int(11) DEFAULT NULL,
  `hiredate` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> select min(hiredate),max(hiredate) from test;
+---------------------+---------------------+
| min(hiredate)       | max(hiredate)       |
+---------------------+---------------------+
| 2015-12-01 00:00:00 | 2015-12-10 23:59:56 |
+---------------------+---------------------+
1 row in set (0.44 sec)

mysql> select date(hiredate),count(*) from test group by date(hiredate);
+----------------+----------+
| date(hiredate) | count(*) |
+----------------+----------+
| 2015-12-01     |    99963 |
| 2015-12-02     |   100032 |
| 2015-12-03     |   100150 |
| 2015-12-04     |    99989 |
| 2015-12-05     |    99908 |
| 2015-12-06     |    99897 |
| 2015-12-07     |   100137 |
| 2015-12-08     |   100171 |
| 2015-12-09     |    99851 |
| 2015-12-10     |    99902 |
+----------------+----------+
10 rows in set (0.98 sec)
复制代码

 

测试的维度

测试的维度主要从两个方面进行，

一、分区剪裁

针对特定的查询，是否能进行分区剪裁（即只查询相关的分区，而不是所有分区）

二、查询时间

鉴于该批测试数据是静止的（即没有并发进行的insert，update和delete操作），数据量也不太大，从这个维度来考量貌似意义也不是很大。

因此，重点测试第一个维度。

 

基于RANGE的分区方案

在这里，选用了TO_DAYS函数

复制代码 
CREATE TABLE range_datetime(
    id INT,
    hiredate DATETIME
)
PARTITION BY RANGE (TO_DAYS(hiredate) ) (
    PARTITION p1 VALUES LESS THAN ( TO_DAYS('20151202') ),
    PARTITION p2 VALUES LESS THAN ( TO_DAYS('20151203') ),
    PARTITION p3 VALUES LESS THAN ( TO_DAYS('20151204') ),
    PARTITION p4 VALUES LESS THAN ( TO_DAYS('20151205') ),
    PARTITION p5 VALUES LESS THAN ( TO_DAYS('20151206') ),
    PARTITION p6 VALUES LESS THAN ( TO_DAYS('20151207') ),
    PARTITION p7 VALUES LESS THAN ( TO_DAYS('20151208') ),
    PARTITION p8 VALUES LESS THAN ( TO_DAYS('20151209') ),
    PARTITION p9 VALUES LESS THAN ( TO_DAYS('20151210') ),
    PARTITION p10 VALUES LESS THAN ( TO_DAYS('20151211') )
);
复制代码

插入数据并查看特定查询的执行计划

复制代码 
mysql> insert into range_datetime select * from test;                                                                    
Query OK, 1000000 rows affected (8.15 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

mysql> explain partitions select * from range_datetime where hiredate >= '20151207124503' and hiredate<='20151210111230'; 
+----+-------------+----------------+--------------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table          | partitions   | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+----------------+--------------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | range_datetime | p7,p8,p9,p10 | ALL  | NULL          | NULL | NULL    | NULL | 400061 | Using where |
+----+-------------+----------------+--------------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.03 sec)
复制代码

注意执行计划中的partitions的内容，只查询了p7，p8，p9，p10三个分区，由此来看，使用to_days函数确实可以实现分区裁剪。

 

基于RANGE COLUMNS的分区方案

RANGE COLUMNS可以直接基于列，而无需像上述RANGE那种，分区的对象只能为整数。

创表语句如下：

复制代码 
CREATE TABLE range_columns ( 
    id INT,
    hiredate DATETIME
)
PARTITION BY RANGE COLUMNS(hiredate) (
    PARTITION p1 VALUES LESS THAN ( '20151202' ),
    PARTITION p2 VALUES LESS THAN ( '20151203' ),
    PARTITION p3 VALUES LESS THAN ( '20151204' ),
    PARTITION p4 VALUES LESS THAN ( '20151205' ),
    PARTITION p5 VALUES LESS THAN ( '20151206' ),
    PARTITION p6 VALUES LESS THAN ( '20151207' ),
    PARTITION p7 VALUES LESS THAN ( '20151208' ),
    PARTITION p8 VALUES LESS THAN ( '20151209' ),
    PARTITION p9 VALUES LESS THAN ( '20151210' ),
    PARTITION p10 VALUES LESS THAN ('20151211' )
);
复制代码

插入数据并查看上述查询的执行计划

复制代码 
mysql> insert into range_columns select * from test;                                                                    
Query OK, 1000000 rows affected (9.20 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

mysql> explain partitions select * from range_columns where hiredate >= '20151207124503' and hiredate<='20151210111230'; 
+----+-------------+---------------+--------------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table         | partitions   | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+---------------+--------------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | range_columns | p7,p8,p9,p10 | ALL  | NULL          | NULL | NULL    | NULL | 400210 | Using where |
+----+-------------+---------------+--------------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.11 sec)
复制代码

同样，使用该分区方案也实现了分区剪裁。

 

基于HASH的分区方案

因HASH分区对象同样只能为整数，所以我们无法像上述RANGE COLUMNS那种直接引用列，在这里，同样用了TO_DAYS函数进行转换。

创表语句如下：

CREATE TABLE hash_datetime (
   id INT,
   hiredate DATETIME
)
PARTITION BY HASH( TO_DAYS(hiredate) )
PARTITIONS 10;

插入数据并查看上述查询的执行计划

复制代码 
mysql> insert into hash_datetime select * from test;
Query OK, 1000000 rows affected (9.43 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

mysql> explain partitions select * from hash_datetime where hiredate >= '20151207124503' and hiredate<='20151210111230';
+----+-------------+---------------+-------------------------------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table         | partitions                    | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+---------------+-------------------------------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | hash_datetime | p0,p1,p2,p3,p4,p5,p6,p7,p8,p9 | ALL  | NULL          | NULL | NULL    | NULL | 1000500 | Using where |
+----+-------------+---------------+-------------------------------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)
复制代码

不难看出，使用hash分区并不能有效的实现分区裁剪，至少在本例，基于天的需求中如此。

 

以上三种方案都是基于datetime的，那么，对于timestamp类型，又该如何选择呢？

事实上，MySQL提供了一种基于UNIX_TIMESTAMP函数的RANGE分区方案，而且，只能使用UNIX_TIMESTAMP函数，如果使用其它函数，譬如to_days，会报如下错误：“ERROR 1486 (HY000): Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed”。

而且官方文档中也提到“Any other expressions involving TIMESTAMP values are not permitted. (See Bug #42849.)”。

下面来测试一下基于UNIX_TIMESTAMP函数的RANGE分区方案，看其能否实现分区裁剪。

 

针对TIMESTAMP的分区方案

创表语句如下：

复制代码 
CREATE TABLE range_timestamp (
    id INT,
    hiredate TIMESTAMP
)
PARTITION BY RANGE ( UNIX_TIMESTAMP(hiredate) ) (
    PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2015-12-02 00:00:00') ),
    PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2015-12-03 00:00:00') ),
    PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2015-12-04 00:00:00') ),
    PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP('2015-12-05 00:00:00') ),
    PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP('2015-12-06 00:00:00') ),
    PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP('2015-12-07 00:00:00') ),
    PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP('2015-12-08 00:00:00') ),
    PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP('2015-12-09 00:00:00') ),
    PARTITION p9 VALUES LESS THAN ( UNIX_TIMESTAMP('2015-12-10 00:00:00') ),
    PARTITION p10 VALUES LESS THAN (UNIX_TIMESTAMP('2015-12-11 00:00:00') )
);
复制代码

插入数据并查看上述查询的执行计划

复制代码 
mysql> insert into range_timestamp select * from test;
Query OK, 1000000 rows affected (13.25 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

mysql> explain partitions select * from range_timestamp where hiredate >= '20151207124503' and hiredate<='20151210111230';
+----+-------------+-----------------+--------------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table           | partitions   | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-----------------+--------------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | range_timestamp | p7,p8,p9,p10 | ALL  | NULL          | NULL | NULL    | NULL | 400448 | Using where |
+----+-------------+-----------------+--------------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)
复制代码

同样也能实现分区裁剪。

 

总结：

1. 经过对比，个人倾向于第二种方案，即基于RANGE COLUMNS的分区实现。

2. 在5.7版本之前，对于DATA和DATETIME类型的列，如果要实现分区裁剪，只能使用YEAR() 和TO_DAYS()函数，在5.7版本中，又新增了TO_SECONDS()函数。

3. 其实LIST也能实现基于天的分区方案，但在这个需求上，相比于RANGE，还是显得很鸡肋。

4. TIMESTAMP类型的列，只能基于UNIX_TIMESTAMP函数进行分区，切记！

18.4.1 RANGE COLUMNS分区
weixin_41275260的博客
06-25 94
18.4.1　RANGE COLUMNS分区 RANGE COLUMNS不仅增加了支持的数据类型，而且还能够对数据表中的多个字段进行分区。例如，创建一个根据字段group_id和group_code分区的t_members_range_columns数据表。 mysql> CREATE TABLE t_members_range_columns( -> id I...
mysql分区详解，range分区操作案例
勾勒1991的博客
07-17 2386
一、分区概述 分区有利于管理非常大的表，根据一定的规则，数据库把一个表分解成多个小表。逻辑上只有一个表或一个索引，实际上这个表由多个物理分区对象组成，每个分区都是一个独立的对象。分区对应用来讲是完全透明的，不影响应用的业务逻辑， 其优点主要由以下几个方面 1.可以存储更多数据。 2.优化查询。在where子句中包含分区条件时，可以只扫描必要的一个或几个分区，提高查询效率；同时在涉及SUM(...
MySQL基于时间字段进行分区的方案总结
weixin_33932129的博客
12-09 462
MySQL支持的分区类型一共有四种：RANGE，LIST，HASH，KEY。其中，RANGE又可分为原生RANGE和RANGE COLUMNS，LIST分为原生LIST和LIST COLUMNS，HASH分为原生HASH和LINEAR HASH，KEY包含原生KEY和LINEAR HASH。关于这些分区之间的差别，改日另写文章进行阐述。 最近，碰到一个需求，要对表的时间字段（类型：datetim...
mysql datetime 分区_在MySQL分区表中支持datetime类型的列
weixin_32434033的博客
01-19 834
MySQL从5.1版开始分区表，但是只能针对整数型的字段，对于经常使用的按时间分区，只能变通实现，比如使用YEAR()函数和TO_DAYS()函数：--使用YEAR按年分成三个区ALTER TABLE testPARTITION BY RANGE(YEAR(Time))(PARTITION p0 VALUES LESS THAN (2012),PARTITION p1 VALUES LESS TH...
猫头虎分享：PostgreSQL 中分区表 PARTITION BY RANGE 的使用详解与数据迁移，索引创建细节详解
最新发布
猫头虎：授渔优于赠鱼，兴趣引领智慧，探索之乐尤显珍贵。商务合作：Libin9iOak，共赴知识与乐趣的探索之旅！
09-11 1万+
分区表（Partitioned Table）是将大表拆分为多个更小、更高效管理的小表。通过这种方式，数据库在进行数据操作时只需访问相关的分区表，而不是整个大表，从而大大提高了查询性能。通过使用 PostgreSQL 的，我们可以有效提升数据库的查询性能，并且通过合理的分区策略，实现数据的自动化管理与迁移。分区表不仅能带来性能的提升，也为数据的归档和清理提供了便捷的方式。PostgreSQL 分区表、PARTITION BY RANGE、年度分区、数据迁移、索引优化。
【mysql分区分表】mysql 按时间分区 【partition】
热门推荐
烤鸭的世界我们不懂的博客
06-18 1万+
大家好，我是烤鸭： 今天分享一下有关 mysql 分区。 需求： 按时间分区。 对千万数据左右的表，进行分区，数据的增加量大概千万/年。 代码实现： 模拟之前已经存在的表： DROP TABLE IF EXISTS `test`; CREATE TABLE `test` ( `id` bigint(20) NOT NULL AUTO_INCREMENT CO...
数据库根据时间自动创建分区的流程
llliarby的博客
03-03 1003
1、创建分区表 CREATE TABLE `表名称` ( `id` char(32) NOT NULL COMMENT '12位的imei', `ts` bigint(20) NOT NULL COMMENT '报文解析出来的时间戳', `create_time` bigint(20) NOT NULL COMMENT '存入数据库时间戳', PRIMARY KEY (`id`,`ts`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE...
MYSQL之表分区----按日期分区
leveretz的博客
11-15 5058
MYSQL之表分区----按日期分区
mysql的range分区和range columns分区有什么区别
09-24
总结而言，range分区适用于单个连续值的范围查询，而range columns分区适用于多个列组合的范围查询。两种分区方式都可以提高对特定范围内数据的查询性能，并且都支持子分区，提高分区的灵活性和查询性能。根据具体的...
Range分区
qq_34749144的博客
11-15 588
入门例子： –建立表 CREATE TABLE sale( product_id VARCHAR2(5), sale_count NUMBER(10,2) ) –分区 PARTITION BY RANGE (sale_count) ( PARTITION p1 VALUES LESS THAN (1000), PARTITION p2 VALUES LESS THAN (2000), PARTIT...
MySQL之RANGE和RANGE COLUMNS分区初见
L
07-08 1529
本文基于version 5.7版本讨论，版本查看 SELECT VERSION(); 可按如下两种方式检查你的MySQL服务器是否支持分区。 命令 SHOW PLUGINS; 如图： 或者 SELECT PLUGIN_NAME as Name, PLUGIN_VERSION as Version, PLUGIN_STATUS as Status FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_TYPE='STORAGE
Mysql已有亿级数据大表按时间分区
ma_anjun的博客
03-17 3033
上一篇Mysql数据库快速插入亿级数据，介绍了如何造亿级数据。OK，现在数据有了，怎么分区？常见的思路有两个： ①使用ALTER TABLE创建分区； ②先创建一张与原来一样的新表，对新的空表分区，然后将原表数据备份到新表，然后删除原表，将新表改名为原表名。 下面就来实践这两种思路。 原表sql： CREATE TABLE `t_send_message_send` ( `id` bigint...
MySQL 按时间进行表分区
心若有所向往，何惧道阻且长。
04-19 2387
MySQL 按时间进行表分区
Mysql 分区介绍(四) —— RANGE COLUMNS分区
weixin_33851177的博客
03-17 1621
RANGE COLUMNS和RANGE分区是非常类似的, 但是这两个也有很多不同的地方。 RANGE COLUMNS 不可以使用表达式, 只能使用列名 RANGE COLUMNS 接受一个或多个字段的列表 RANGE COLUMNS 分区列是不限制于数字列的;字符串, DATE和DATETIME 列也可以使用在分区列 基本定义: CREATE TABLE table_n...
mysql columns分区_Mysql 分区介绍(四) —— RANGE COLUMNS分区
weixin_39606361的博客
01-25 353
RANGE COLUMNS 不可以使用表达式, 只能使用列名RANGE COLUMNS 接受一个或多个字段的列表RANGE COLUMNS 分区列是不限制于数字列的;字符串, DATE和DATETIME 列也可以使用在分区列基本定义:CREATE TABLE table_namePARTITIONED BY RANGE COLUMNS(column_list) (PARTITION partiti...
Mysql --分区表(5)Columns分区
FaN()
08-09 2787
COLUMNS分区COLUMNS分区是RANGE和LIST分区的变种。COLUMNS分区支持多列作为分区键进行分区 RANGE COLUNMS分区和LIST COLUMNS都支持非INT型列作为分区键： 1.所有数据类型: TINYINT, SMALLINT, MEDIUMINT, INT (INTEGER), and BIGINT. (This is the same as with part
深入浅析MySQL COLUMNS分区
09-09
它也允许基于列的值进行分区，但与RANGE不同，它接受预定义的值列表。例如，可以创建一个名为`rcx`的表，基于两个字段`a`和`b`进行LIST COLUMN分区： ```sql CREATE TABLE rcx ( a INT, b INT ) PARTITION BY ...
达梦数据库分区表管理，例子：range分区表
weixin_42447367的博客
07-26 996
按照range(范围)创建一个分区表，分区的字段是time字段；将表callinfo按照time字段的值分为4个分区，其中time<2010-04-01的分为p1区; time>=2010-04-01&time<2010-07-01的分为p2区；time>=2010-07-01&time<2010-10-01分为p3区，time>=2010-10-01&time<2010-12-31的分为p4区 partition by 用于指定分区方式 r
mysql timestamp分区,时间戳上的MySQL表分区
weixin_36422707的博客
01-19 766
I have partitioned a table (because of an out of memory error - table got too big). I have partitioned it on a timestamp column as shown below:CREATE TABLE test (fname VARCHAR(50) NOT NULL,lname VARC...
MySQL基于时间范围分区的2种方案
penriver的博客
01-20 4305
在MySQL 5.7中，有2种基于时间范围或间隔的分区方案，可以根据实际业务需要进行选择。 本文提供了针对DATE、TIME、DATETIME、TIMESTAMP 列如何正确进行时间分区的示例
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值