MySQL支持RANGE,LIST,HASH,KEY分区类型,其中以RANGE最为常用:
Range(范围)–这种模式允许将数据划分不同范围。例如可以将一个表通过年份划分成若干个分区。
Hash(哈希)–这中模式允许通过对表的一个或多个列的Hash Key进行计算,最后通过这个Hash码不同数值对应的数据区域进行分区。例如可以建立一个对表主键进行分区的表。
Key(键值)-上面Hash模式的一种延伸,这里的Hash Key是MySQL系统产生的。
List(预定义列表)–这种模式允许系统通过预定义的列表的值来对数据进行分割。
Composite(复合模式) –以上模式的组合使用
二,分区能做什么
逻辑数据分割
提高单一的写和读应用速度
提高分区范围读查询的速度
分割数据能够有多个不同的物理文件路径
高效的保存历史数据
一个表上的约束检查
不同的主从服务器分区策略,例如master按Hash分区,slave按range分区
三,分区的限制
只能对数据表的整型列进行分区,或者数据列可以通过分区函数转化成整型列,例如create_time字段是datetime类型,可通过to_days等函数将其字段值转换成数字类型
最大分区数目不能超过1024
如果含有唯一索引或者主键,则分区列必须包含在“所有的唯一索引或者主键”之内
不支持外键
不支持全文索引(fulltext)
按日期进行分区很非常适合,因为很多日期函数可以用。但是对于字符串来说合适的分区函数不太多
四,什么时候使用分区
海量数据表
历史表快速的查询,可以采用ARCHIVE+PARTITION的方式。
数据表索引大于服务器有效内存
对于大表,特别是索引远远大于服务器有效内存时,可以不用索引,此时分区效率会更有效
五,分区具体操作:
情况一:
创建good_2表,并且按照the_date字段的日期按年份来分区,由于the_date字段是整形类型,所以没法使用时间函数year来做分区,但可以通过div除法运算得出年份
CREATE TABLE `good_2` (
`id` int(11) NOT NULL DEFAULT '0',
`name` char(20) CHARACTER SET utf8 NOT NULL DEFAULT '',
`create_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`the_date` int(8) DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 partition by range ( the_date div 100 )(
partition g2_2013 less than (2014),
partition g2_2014 less than (2015),
partition g2_2015 less than (2016)
);
情况二:
创建good_2表,并且按照create_time字段按年份来分区,timestamp类型的字段无法进行分区,所以得用datetime类型, to_days函数将日期折算成天数
CREATE TABLE `good_2` (
`id` int(11) NOT NULL DEFAULT '0',
`name` char(20) CHARACTER SET utf8 NOT NULL DEFAULT '',
`create_time` datetime,
`the_date` int(8) DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 partition by range ( to_days(create_time) )(
partition g2_2013 values less than (to_days('2014-01-01')),
partition g2_2014 values less than (to_days('2015-01-01')),
partition g2_2015 values less than (to_days('2016-01-01'))
);
情况三:
对已有的表进行分区,使用alter table的语句,如下:
//时间字段为日期格式,例如datetime格式,分区需要用to_days
alter table good3 partition by range( to_days(create_time) )(
partition g2_2013 values less than (to_days('2014-01-01')),
partition g2_2014 values less than (to_days('2015-01-01')),
partition g2_2015 values less than (to_days('2016-01-01')),
partition g21_3 values less than MAXVALUE
);
//时间字段为时间戳,例如timestamp格式,需要使用UNIX_TIMESTAMP方法,而不是to_days
alter table `good_20180902_1` partition by range(UNIX_TIMESTAMP(`create_time`))(
partition g21_1 values less than (UNIX_TIMESTAMP('2018-07-01 00:00:00')),
partition g21_2 values less than (UNIX_TIMESTAMP('2018-08-01 00:00:00')),
partition g21_3 values less than MAXVALUE
);
情况四:
删除表中的指定分区,如下:
ALTER TABLE 表名 DROP PARTITION 分区名; 例如:alter table good_2 drop partition g2_2015;
情况五:
追加分区,如下:
alter table good_2 add partition (
partition g2_2015 values less than (to_days('2016-01-01'))
partition g2_2016 values less than (to_days('2017-01-01'))
);
情况六:
如果表中存在主键字段(primary key),需要把创建分区的字段包含在主键字段中,也就是说需要将分区字段和主键字段作为复合主键
例如存在表me:
CREATE TABLE `me` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` char(20) DEFAULT NULL,
the_date int(8) not null default 0
PRIMARY KEY (`id`)
);
如果需要对the_date字段做分区,需要先将它与主键id做复合主键,操作步骤如下:
先删除自增:alter table me modify id int(11) not null;
删除旧主键:alter table me drop primary key;
创建复合主键:alter table me add primary key(id, the_date);
最后再创建分区
查看表分区的情况(主要是记录数):
SELECT
partition_name part,
partition_expression expr,
partition_description descr,
table_rows
FROM
INFORMATION_SCHEMA.partitions
WHERE
TABLE_SCHEMA = schema()
AND TABLE_NAME='good_2';
查看查询语句使用分区的情况:
explain partitions select * from good_2 where the_date between 20140102 and 20140103;
相关的分区测试结论:
分区和未分区占用文件空间大致相同 (数据和索引文件)
如果查询语句中有未建立索引字段,分区时间远远优于未分区时间
如果查询语句中字段建立了索引,分区和未分区的差别缩小,分区略优于未分区。
对于大数据量,建议使用分区功能。
去除不必要的字段
根据手册,增加myisam_max_sort_file_size 会增加分区性能