SQL表分区partition的⽤法
1.2 关于分区
以myisam为例⼦，mysql数据库中的数据是以⽂件的形势存在磁盘上，⼀张表主要对应着三个⽂件，⼀个是frm存放表结构⽂件，⼀个存放表数据的，⼀个是myi存表索引。
也就是将⼀个表⽂件分为多个表⽂件在磁盘上进⾏存取，提⾼对io的使⽤。
1.3 是否⽀持分区
mysql> show variables like ‘%partition%’;
+——————-+——-+
| Variable_name | Value |
+——————-+——-+
| have_partitioning | YES |
+——————-+——-+
出现YES表⽰当前版本⽀持表分区
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进⾏计算，最后通过这个H
ash码不同数值对应的数据区域进⾏分区，。例如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()
exists的用法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 会增加分区性能
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论