MySQL分区之RANGE分区（续）

最新推荐文章于 2024-08-23 00:44:28 发布

笑天居士

最新推荐文章于 2024-08-23 00:44:28 发布

阅读量2.4k

点赞数

分类专栏： MySQL高级及性能优化

本文链接：https://blog.csdn.net/jiao_fuyou/article/details/14214213

版权

MySQL高级及性能优化专栏收录该内容

71 篇文章 1 订阅

订阅专栏

大数据，特别是一些和时间有关的，每天很大量，但是热数据都是近几个月的，特别适合用分区表

MySQL5.5 分区表新特性

MySQL 5.5的分区对用户绝对是个好消息，更易于使用的增强功能，以及TRUNCATE PARTITION命令都可以为DBA节省大量的时间，有时对最终用户亦如此：
　　1) 非整数列分区：任何使用过MySQL分区的人应该都遇到过不少问题，特别是面对非整数列分区时，MySQL 5.1只能处理整数列分区，如果你想在日期或字符串列上进行分区，你不得不使用函数对其进行转换。很麻烦，而MySQL 5.5中新增了两类分区方法，RANG和LIST分区法，同时在新的函数中增加了一个COLUMNS关键词，支持INT,SMALLINT,TINYINT,BIGINT.DATE,DATETIME.CHAR,VARCHAR,BINARY,VARBINARY列类型。在MySQL 5.1中使用分区另一个让人头痛的问题是date类型(即日期列)，你不能直接使用它们，必须使用YEAR或TO_DAYS转换这些列，但在MySQL 5.5中情况发生了很大的变化，现在在日期列上可以直接分区，并且方法也很简单;
　　2) 多列分区：COLUMNS关键字现在允许字符串和日期列作为分区定义列，同时还允许使用多个列定义一个分区;
　　3) 可用性增强：truncate分区。分区最吸引人的一个功能是瞬间移除大量记录的能力，DBA都喜欢将历史记录存储到按日期分区的分区表中，这样可以定期删除过时的历史数据。　但当你需要移除分区中的部分数据时，事情就不是那么简单了，删除分区没有问题，但如果是清空分区，就很头痛了，要移除分区中的所有数据，但需要保留分区本身，你可以：使用DELETE语句，但我们知道DELETE语句的性能都很差。使用DROP PARTITION语句，紧跟着一个EORGANIZE PARTITIONS语句重新创建分区，但这样做比前一个方法的成本要高出许多。MySQL 5.5引入了TRUNCATE PARTITION，它和DROP PARTITION语句有些类似，但它保留了分区本身，也就是说分区还可以重复利用。TRUNCATE PARTITION应该是DBA工具箱中的必备工具;
　　4) 更多微调功能：TO_SECONDS：分区增强包有一个新的函数处理DATE和DATETIME列，使用TO_SECONDS函数，你可以将日期/时间列转换成自0年以来的秒数，如果你想使用小于1天的间隔进行分区，那么这个函数就可以帮到你。

MySQL 5.5中新增了两类分区方法，RANG和LIST分区法，同时在新的函数中增加了一个COLUMNS关键词。

对于上面的spccdr2表，可以用如下方法定义：

CREATE TABLE scpcdr2 (
calltime datetime not null,
msisdn char(11) not null,
calltype char(2) not null,
othercallno char(32) not null,
calldura integer not null,
key scpcdridx1(calltime,msisdn)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
PARTITION BY RANGE COLUMNS(calltime)
(
PARTITION p2012a VALUES LESS THAN ('2013-01-01') ENGINE = InnoDB,
PARTITION p2013a VALUES LESS THAN ('2014-01-01') ENGINE = InnoDB,
PARTITION p2014a VALUES LESS THAN ('2014-07-01') ENGINE = InnoDB,
PARTITION p2014b VALUES LESS THAN ('2015-01-01') ENGINE = InnoDB
);

这样的分区语句除了更加易读外，对数据的组织和管理也非常清晰，并且支持INT,SMALLINT,TINYINT,BIGINT.DATE,DATETIME.CHAR,VARCHAR,BINARY,VARBINARY列类型

而且
mysql> explain partitions select * from scpcdr2 where calltime between '2013-10-18 00:00:00' and '2013-10-18 23:59:59'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: scpcdr2
partitions: p2013a
type: range
possible_keys: scpcdridx1
key: scpcdridx1
key_len: 8
ref: NULL
rows: 1
Extra: Using where
1 row in set (0.00 sec)

只会优化到p2013a，不会是象上面的p2012a,p2013a两个分区

按一些文档里说5.5里的分区表查询里可以使用非赤裸列，但是我试验过后这个还是不行，还是只能是赤裸列

mysql> explain partitions select * from scpcdr2 where date(calltime) between '2013-10-18' and '2013-10-18'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: scpcdr2
partitions: p2012a,p2013a,p2014a,p2014b,p201xx
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 10
Extra: Using where
1 row in set (0.00 sec)