mysql分区表实践

类别:mysql  阅读:249  发布时间:Sun Dec 03 17:32:40 CST 2017

    最近做的网盟项目,发现一张报表table数据量比较大,持续增长,且大部分是历史数据。查询当天数据时都很慢。where条件只有reportTime一个过滤条件。用Explan分析发现reportTime字段的索引已经用上,但row行数仍然有好几十万。想起试试改为分区表试试,将原来的普通表改为分区表。看了一下每天的数据大概有10万到20万,采用range方式分区,每天的数据划分一个分区。

ALTER TABLE `xxx`.`xxx` PARTITION BY RANGE(TO_DAYS(reportTime))
(
	PARTITION p20171201 VALUES less than (TO_DAYS('2017-12-01')),
	PARTITION p20171202 VALUES less than (TO_DAYS('2017-12-02')),
	PARTITION p20171203 VALUES less than (TO_DAYS('2017-12-03')),
	PARTITION p20171204 VALUES less than (TO_DAYS('2017-12-04')),
	PARTITION p20171205 VALUES less than (TO_DAYS('2017-12-05')),
	PARTITION p20171206 VALUES less than (TO_DAYS('2017-12-06')),
	PARTITION p20171207 VALUES less than (TO_DAYS('2017-12-07')),
	PARTITION p20171208 VALUES less than (TO_DAYS('2017-12-08')),
	PARTITION p20171209 VALUES less than (TO_DAYS('2017-12-09')),
	PARTITION p20171210 VALUES less than (TO_DAYS('2017-12-10')),
	PARTITION p20171211 VALUES less than (TO_DAYS('2017-12-11')),
	PARTITION p20171212 VALUES less than (TO_DAYS('2017-12-12'))
);

    上面语句将普通表改为分区表的同时,将2017-12-01前的数据划分为一个分区,后面的时间,每天一个分区。如果觉得2017-12-01前的数据太多,还可以进行分割。

ALTER TABLE `xxx`.`xxx` REORGANIZE PARTITION p20171201 INTO (
    PARTITION p20171101 VALUES LESS THAN ('2017-11-01'),
    PARTITION p20171201 VALUES LESS THAN ('2017-12-01'));

    上面语句将p20171201分区在2017-11-01的数据为界分割为两个分区。

    查看各个分区数据量。

SELECT
	PARTITION_NAME,
	TABLE_ROWS
FROM
	information_schema.`PARTITIONS`
WHERE
	TABLE_NAME = 'xxx'.`xxx`

    改为分区表后,大大提高了查询单天数据的速度。

    附加一些摘自《高性能mysql》的笔记


    ---------------------------------------------------------------------------------------


    在下面的场景中,分区可以起到非常大的作用:

1.表非常大以至于无法全部都放在内存中,或者只在表的最后部分有热点数据,其他均是历史数据。

2.分区表的数据更容易维护。例如想批量删除大量数据可以使用清除整个分区的方式。另外,还可以对一个独立分区进行优化、检查、修复等操作。

3.分区表的数据可以分布在不同的物理设备上,从而高效地利用多个硬件设备。

4.可以使用分区表来避免某些特殊的瓶颈,例如InnoDB的单个索引的互斥访问,ext3文件系统的inode锁竞争等。

5.如果需要,还可以备份和恢复独立的分区,这在非常大的数据集的场景下效果非常好。


分区表本身也有一些限制,下面是其中比较重要的几点:

1.一个表最多只能有1024个分区。

2.在mysql5.1中,分区表达式必须是整数,或者是返回整数的表达式。在mysql5.5中,某些场景中可以直接使用列进行分区。

3.如果分区字段中有主键或者唯一索引的列,那么所有主键列和唯一索引列都必须包含进来。

4.分区表中无法使用外键约束。


关键字:mysql 分区表

© copyright 粤ICP备16108162号-1