MySQL RANGE分区表

一、开启分区表功能

MySQL想要使用分区表,必须安装partition插件。

查看是否安装了partition插件。

MySQL 5.6二进制版本默认提供分区功能;如果是编译安装,那么编译时请加上 -DWITH_PARTITION_STORAGE_ENGINE 选项。
如果不想使用分区,启动MySQL服务请加上–skip-partition 选项。

分区表中的分区必须为同一种存储引擎。
分区不能使用 MERGE, CSV, or FEDERATED 存储引擎。

创建hash分区表ti,并分配6个分区。

可以看到,每个分区都有单独的.idb文件

分区操作针对一张表的数据和所有索引,你不能单独对数据分区,而不对索引分区。

二、分区表的优点

●分区表可以存储更多数据,不同的分区可以存放在单独磁盘或者系统分区上。
●便于清理不需要的数据,使用drop partition即可。当然,也能为新数据创建分区。
●优化SQL查询,自适应where条件,只扫描where条件过滤后数据对应的分区,排除了其他不需要的分区。另外,可以根据日常的SQL,将热数据重组到一个分区,能够提高不少性能。从5.6开始,查询支持指定分区。如 SELECT * FROM t PARTITION (p0,p1) WHERE c < 5,该SQL只扫描p0和p1分区,然后使用where条件过滤。大大提升了SQL查询速度。分区表支持 DELETE, INSERT, REPLACE, UPDATE, LOAD DATA, LOAD XML等命令。
●聚合函数默认并行化执行,例如 SUM() 和 COUNT()。 SELECT salesperson_id, COUNT(orders) as order_total FROM sales GROUP BY salesperson_id; 这条SQL,在每个分区同时并行执行,最后将结果合并得出最终结果。
●如果将数据分布到不同磁盘上,可以大大提高吞吐量。

 

三、分区表类型

1、范围分区(RANGE partitioning)
顾名思义,每个分区中存放的数据都有指定的范围,数据符合这个范围就存放到对应的分区中。范围界定使用 VALUES LESS THAN 关键字。

##1、普通数值类型的分区,如INT类型
创建range分区表,以store_id列作为分区列,store_id<6落在p0分区,6<store_id<11落在p1分区,11<store_id<16落在p2分区,16<store_id<21落在p3分区。
这里没有指定存储引起,它以MySQL默认为准。

分区表的详细信息,查询 information_schema.PARTITIONS 可以得到。
字段解释
TABLE_SCHEMA:表属于的database名字。
PARTITION_NAME:分区名。
PARTITION_ORDINAL_POSITION:分区序号。
PARTITION_METHOD:分区类型,如RANGE、LIST、HASH等。
PARTITION_DESCRIPTION:用于RANGE和LIST分区,分区范围的值。

 

 

竖排版更利于阅读

熟悉Oracle分区表的朋友,应该会发现上面分区表创建的有问题。没错,就是没有MAXVALUE分区,MAXVALUE 分区用于存放大于现有分区范围的数据。一是避免忘记分配分区,造成数据无法插入;二是插入的数据存在很大不确定性,用于存储这些不确定数据。

RANGE分区表必须含有MAXVALUE分区,语法 LESS THAN MAXVALUE 。大于16的数据都会存储在p3分区红。

MySQL官方文档有一个很有意思的例子,按照工号(job_code)位数来分区。
2位工号代表普通工人;3位工号代表办公室人员和供应人员;4位工号代表管理人员。
这样工人的数据存放在p0分区,办公室人员和供应人员的数据存放在p1分区,而管理人员的数据存放在p2分区。

由于工号的位数是固定的,所以无需考虑创建MAXVALUE分区。

2、date类型的列做RANGE分区

例子中,separated列是DATE类型。

3、TIMESTAMP类型的列做RANGE分区
report_updated列为TIMESTAMP时间戳类型。如果我们直接使用时间戳分区,还要把时间计算成时间戳,然后分区,麻烦而且容易出错。
而UNIX_TIMESTAMP()函数可以把TIMESTAMP类型转换成时间,就容易很多,不容易出错。

4、RANGE分区适用场景
●可以按照分区删除老数据,ALTER TABLE employees DROP PARTITION p0。比delete高效的多,而且影响小。
●表中有日期或者时间类型的列
●经常基于分区列查询,MySQL能够快速根据where条件扫描数据所在的分区。EXPLAIN PARTITIONS SELECT COUNT(*) FROM employees WHERE separated BETWEEN ‘2000-01-01’ AND ‘2000-12-31’ GROUP BY store_id;

 

发表评论

电子邮件地址不会被公开。 必填项已用*标注