SQL表分区 partition的用法

最新推荐文章于 2024-10-12 14:45:09 发布

一只写bug的程序猿

最新推荐文章于 2024-10-12 14:45:09 发布

阅读量4.2w

点赞数 4

分类专栏： SQL 文章标签：数据库 mysql partition values less than tables

本文链接：https://blog.csdn.net/a364416036/article/details/52180807

版权

SQL 专栏收录该内容

10 篇文章 0 订阅

订阅专栏

1.分表与表分区的区别
1.1 关于分表
分表是将一个大表分为几个或是多个小表，例如：table_1每天有1Kw的数据量，table_1随便时间的增长会越来越大，最终达到mysql表的极限，在这种比较极端的情况下
我们可以考虑对table_01进行分表操作，即每天生成与table_1表同样的表，每天一张即table_1_20120920
更多详细：http://blog.51yip.com/mysql/949.html

1.2 关于分区

以myisam为例子，mysql数据库中的数据是以文件的形势存在磁盘上，一张表主要对应着三个文件，一个是frm存放表结构文件，一个存放表数据的，一个是myi存表索引。
也就是将一个表文件分为多个表文件在磁盘上进行存取，提高对io的使用。

1.3 是否支持分区

1.4 查看分区表信息
select * from INFORMATION_SCHEMA.PARTITIONS where TABLE_SCHEMA=’tablename’
2.如何分区
2.1 分区方法
分区有二个方法：水平分区、垂直分区
2.2 分区的类型
=== 水平分区的几种模式：===
* Range（范围） – 这种模式允许DBA将数据划分不同范围。例如DBA可以将一个表通过年份划分成三个分区，80年代（1980′s）的数据，90年代（1990′s）的数据以及任何在2000年（包括2000年）后的数据。

* Hash（哈希） – 这中模式允许DBA通过对表的一个或多个列的Hash Key进行计算，最后通过这个Hash码不同数值对应的数据区域进行分区，。例如DBA可以建立一个对表主键进行分区的表。

* Key（键值） – 上面Hash模式的一种延伸，这里的Hash Key是MySQL系统产生的。

* List（预定义列表） – 这种模式允许系统通过DBA定义的列表的值所对应的行数据进行分割。例如：DBA建立了一个横跨三个分区的表，分别根据2004年2005年和2006年值所对应的数据。

* Composite（复合模式） – 很神秘吧，哈哈，其实是以上模式的组合使用而已，就不解释了。举例：在初始化已经进行了Range范围分区的表上，我们可以对其中一个分区再进行hash哈希分区。

= 垂直分区（按列分）=
举个简单例子：一个包含了大text和BLOB列的表，这些text和BLOB列又不经常被访问，这时候就要把这些不经常使用的text和BLOB了划分到另一个分区，在保证它们数据相关性的同时还能提高访问速度。
2.2 代码演示
range分区如下：

–按天进行划分
错误代码
create table part_range
(
id bigint not null auto_increment,
ftime date,
str text
)engine=myisam
partition by range (ftime)
(
partition p0 values less than (to_days(’2012-09-21′)),
partition p1 values less than (to_days(’2012-09-22′))
)
错误原因：
1. 在partition by range (ftime),ftime需要加to_days转成数字
2.进行分区的字段需要是主键的一部分1
使用以上语句创建时报错
‘A PRIMARY KEY must include all columns in the table’s partitioning function”
默认分区限制分区字段必须是主键（PRIMARY KEY)的一部分

–按天进行分区
–直接使用时间列不可以，RANGE分区函数返回的列需要是整型。
create table part_range
(
id bigint not null auto_increment,
ftime date,
str text,
primary key(id,ftime)
)engine=myisam
partition by range (to_days(ftime))
(
partition p0 values less than (to_days(’2012-09-21′)),
partition p1 values less than (to_days(’2012-09-22′)),
PARTITION p3 VALUES LESS THAN MAXVALUE
);

–按小时进行分区
create table part_range_day
(
id bigint not null auto_increment,
ftime datetime,
str text,
primary key(id,ftime)
)engine=myisam
partition by range(hour(ftime))
(
partition p0 values less than (1),
partition p1 values less than(2),
PARTITION p3 VALUES LESS THAN MAXVALUE
)
[Err] 1493 – VALUES LESS THAN value must be strictly increasing for each partition

使用id进行划分时

–按id进行划分，id只能是由小到大
create table part_range_id
(
id bigint not null auto_increment,
ftime datetime,
str text,
primary key(id,ftime)
)engine=myisam
partition by range (id)
(
partition p0 values less than (10000),
partition p1 values less than (20000),
partition p2 values less than maxvalue
)

–使用list

create table part_range_list
(
id bigint not null auto_increment,
ftime datetime,
str text,
primary key(id,ftime)
)engine=myisam
partition by list (id)
(
partition p0 values in (0,1),
partition p1 values in (2,4)
)

–5.5之后的mysql说可以支持字符目前使用的5.5.24版本，使用字符时依然提示 [Err] 1697 – VALUES value for partition ‘p0′ must have type INT

3.性能测试

硬件：3.6G 内存 cpu Intel(R) Pentiun(R)
软件:win xp2 32位
–没有加分区表part_no_test,myisam引擎

create table part_no_test
(
id bigint primary key auto_increment,
ftime datetime,
str text
)engine=myisam

–加入分区的表part_test，myisam引擎，以小时划分四个区
create table part_test
(
id bigint auto_increment,
ftime datetime,
str text,
primary key(id,ftime)
)engine=myisam
partition by range(hour(ftime))
(
partition p0 values less than (6),
partition p1 values less than (12),
partition p3 values less than (18),
partition p4 values less than maxvalue
)

–随机数据构造，构造2kw数据量
INSERT INTO part_test(ftime,str)values(FROM_UNIXTIME(unix_timestamp(’2012-09-20 08:00:00′)+FLOOR(7 + (RAND() * 360000))),’sss’);
生成数据存储过程
—生成part_no_test数据
drop procedure if exists part_no_insert_data;
create procedure part_insert_data()
begin
set @id=20000000;
while @id>0 do
INSERT INTO part_no_test(ftime,str)values(FROM_UNIXTIME(unix_timestamp(’2012-09-20 08:00:00′)+FLOOR(7 + (RAND() * 360000))),RAND()*RAND()*100000000000);
set @id=@id-1;
end while;
end;

—生成part_test数据

drop procedure if exists part_insert_data;
create procedure part_insert_data()
begin
set @id=20000000;
while @id>0 do
INSERT INTO part_test(ftime,str)values(FROM_UNIXTIME(unix_timestamp(’2012-09-20 08:00:00′)+FLOOR(7 + (RAND() * 360000))),RAND()*RAND()*100000000000);
set @id=@id-1;
end while;
end;

–有分区查询语句
select * from part_test a where a.ftime>’2012-09-20 10:00:00′ and a.ftime<’2012-09-20 12:00:00′;

执行时间：09.906s

–无分区查询语句
select * from part_no_test a where a.ftime>’2012-09-20 10:00:00′ and a.ftime<’2012-09-20 12:00:00′;

执行时间：23.281s

附：
Mysql可用的分区函数
DAY()
DAYOFMONTH()
DAYOFWEEK()
DAYOFYEAR()
DATEDIFF()
EXTRACT()
HOUR()
MICROSECOND()
MINUTE()
MOD()
MONTH()
QUARTER()
SECOND()
TIME_TO_SEC()
TO_DAYS()
WEEKDAY()
YEAR()
YEARWEEK() 等
当然，还有FLOOR（），CEILING() 等，前提是使用这两个分区函数的分区健必须是整型。
要小心使用其中的一些函数，避免犯逻辑性的错误，引起全表扫描。

注：
1.分区的新增、删除每次只能是一个
2.maxvalues 后面不能再加分区
3.分区键必须包含在主键中 ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table’s partitioning function’
4.ERROR 1503 (HY000): A UNIQUE INDEX must include all columns in the table’s partitioning function’说明在表上建约束索引(如唯一索引，普通索引可以)会有问题，必须把约束索引列包含在分区健内
5.只有RANGE和LIST分区才能有子分区，每个分区的子分区数量必须相同，
6. MYSQL将NULL值视为0.自动插入最小的分区中。

= 初步结论 =
* 分区和未分区占用文件空间大致相同（数据和索引文件）
* 如果查询语句中有未建立索引字段，分区时间远远优于未分区时间
* 如果查询语句中字段建立了索引，分区和未分区的差别缩小，分区略优于未分区。
= 最终结论 =
* 对于大数据量，建议使用分区功能。
* 去除不必要的字段
* 根据手册，增加myisam_max_sort_file_size 会增加分区性能