请教关于MYSQL的hash分区和range分区的问题

gxk9933 2013-02-18 03:44:10
场景:
CREATE TABLE IF NOT EXISTS `stream` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增id',
`type` int(10) unsigned NOT NULL COMMENT '流水类型',
`ext_1` bigint(20) unsigned NOT NULL COMMENT '扩展字段1 [相关id、相关值]',
`ext_2` bigint(20) unsigned NOT NULL COMMENT '扩展字段2 [相关id、相关值]',
`datetime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '发生时间',
PRIMARY KEY (`id`,`time`),
KEY `idx_type` (`type`),
KEY `idx_time` (`time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY HASH (TO_DAYS(`datetime`))
PARTITIONS 360;



CREATE TABLE IF NOT EXISTS `stream_new` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增id',
`type` int(10) unsigned NOT NULL COMMENT '流水类型',
`ext_1` bigint(20) unsigned NOT NULL COMMENT '扩展字段1 [相关id、相关值]',
`ext_2` bigint(20) unsigned NOT NULL COMMENT '扩展字段2 [相关id、相关值]',
`datetime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '发生时间',
PRIMARY KEY (`id`,`time`),
KEY `idx_type` (`type`),
KEY `idx_time` (`time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE (to_days(datetime)) (
PARTITION p20130202 VALUES LESS THAN (to_days('20130202')),
PARTITION p20130203 VALUES LESS THAN (to_days('20130203')),
PARTITION p20130204 VALUES LESS THAN (to_days('20130204')),
PARTITION p20130205 VALUES LESS THAN (to_days('20130205')),
PARTITION p20130206 VALUES LESS THAN (to_days('20130206')),
.
.
.#一直向后300天
.
PARTITION pn VALUES LESS THAN MAXVALUE
)

也就是上面两个表结构是相同的,但一个使用hash分区,另一个使用range分区
现在表stream中有2000万条数据,我将数据导入stream_new中

接下来我的测试:
第一步、然后我使用sql语句,对两个表进行分析
explain partitions SELECT * FROM `stream` where `datetime` > '2013-02-17'order by `datetime` limit 10;
explain partitions SELECT * FROM `stream_new` where `datetime` > '2013-02-17'order by `datetime` limit 10;
发现stream会查询所有的分区,而stream_new只会查询2013-02-17后的分区
我去掉上面两个语句中的explain partitions进行执行,前一个语句执行了20s,而后一个只用了不到1s

问题:为什么hash分区的查询速度会慢于range分区,我在网上查了一些资料,hash分区也是根据取模将数据放入不同分区中,为什么我根据分区字段去查询,也会扫描所有分区。还有一种说法据说对分区的查询时并发的,是这样的吗?


第二步、我把上面查询条件中的where语句`datetime` > '2013-02-17'去掉,又进行了一遍查询,前后者的时间基本没有变化
问题:没有按分区定义字段进行过滤查询,为什么查询速度没有变慢。


我还有另一个表结构与上面的表基本相同,我没有建立分区,只是有索引,数据量也有1000万多,做各种查询的速度明显优于使用了hash分区的表,这是为什么?

...全文
179 8 1 打赏 收藏 举报
写回复
8 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
dolphine813 2013-04-07
问题在于你写的是> 某个时间段 ,而且有排序,hash结构是无序的,需重新排序 你如果把sql改成 SELECT * FROM `stream` where `datetime` = '2013-02-17',估计hash表分区的会更快
  • 打赏
  • 举报
回复
gxk9933 2013-02-19
很抱歉打扰大家了,我的问题应该是出在字段的选择上,而不是分区上,我使用datetime的速度会远远慢于使用int的速度,datetime的长度是8个字节,而int是4个,我想大概是因为这个原因,之前的问题描述上也有问题,麻烦大家了,非常感谢
  • 打赏
  • 举报
回复
ACMAIN_CHM 2013-02-18
问题:为什么hash分区的查询速度会慢于range分区,我在网上查了一些资料,hash分区也是根据取模将数据放入不同分区中,为什么我根据分区字段去查询,也会扫描所有分区。还有一种说法据说对分区的查询时并发的,是这样的吗? 你的数据是存放在所有分区中的,所有自然会搜索所有分区,这是由HASH算法而决定的。比如假设一种HASH算法是4个分区取余,则 1,5,9在一分区,2,6,10在第二分区,。。 当你搜索 where x <3 ,显然你必须到多个分区中去查询。
  • 打赏
  • 举报
回复
gxk9933 2013-02-18
引用 2 楼 rucypli 的回复:
1 你这时间to_day以后hash 相当于表存储是按照日期随机放的 访问的时候当然要访问所有分区
hash是访问所有分区,如果是并发访问,那么跟访问一个分区的时间应该不会相差那么大,前者20s,后者是0.1秒,这个比较难理解
  • 打赏
  • 举报
回复
gxk9933 2013-02-18
引用 3 楼 rucypli 的回复:
2 两者都扫描全表 也就没有时间差别了 和分区无关了
不好意思,应该是我描述有误,第二步不进行时间过滤条件查询,使用hash分区也会查询20多秒,而range分区还是不到1秒就查出来数据了。这个我很不解
  • 打赏
  • 举报
回复
rucypli 2013-02-18
2 两者都扫描全表 也就没有时间差别了 和分区无关了
  • 打赏
  • 举报
回复
rucypli 2013-02-18
1 你这时间to_day以后hash 相当于表存储是按照日期随机放的 访问的时候当然要访问所有分区
  • 打赏
  • 举报
回复
gxk9933 2013-02-18
自己顶起先,求数据库高手指教
  • 打赏
  • 举报
回复
相关推荐
发帖
MySQL
加入

5.5w+

社区成员

MySQL相关内容讨论专区
申请成为版主
帖子事件
创建了帖子
2013-02-18 03:44
社区公告
暂无公告