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

[gxk9933]

场景：
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分区的表，这是为什么？

[gxk9933]

自己顶起先，求数据库高手指教

• 打赏
• 举报

回复 赞

[rucypli]

1 你这时间to_day以后hash 相当于表存储是按照日期随机放的 访问的时候当然要访问所有分区

• 打赏
• 举报

回复 赞

[rucypli]

2 两者都扫描全表 也就没有时间差别了 和分区无关了

• 打赏
• 举报

回复 赞

[gxk9933]

引用 3 楼 rucypli 的回复:

2 两者都扫描全表 也就没有时间差别了 和分区无关了

不好意思，应该是我描述有误，第二步不进行时间过滤条件查询，使用hash分区也会查询20多秒，而range分区还是不到1秒就查出来数据了。这个我很不解

• 打赏
• 举报

回复 赞

[gxk9933]

引用 2 楼 rucypli 的回复:

1 你这时间to_day以后hash 相当于表存储是按照日期随机放的 访问的时候当然要访问所有分区

hash是访问所有分区，如果是并发访问，那么跟访问一个分区的时间应该不会相差那么大，前者20s，后者是0.1秒，这个比较难理解

• 打赏
• 举报

回复 赞

[ACMAIN_CHM]

问题：为什么hash分区的查询速度会慢于range分区，我在网上查了一些资料，hash分区也是根据取模将数据放入不同分区中，为什么我根据分区字段去查询，也会扫描所有分区。还有一种说法据说对分区的查询时并发的，是这样的吗？ 你的数据是存放在所有分区中的，所有自然会搜索所有分区，这是由HASH算法而决定的。比如假设一种HASH算法是4个分区取余，则 1,5,9在一分区，2,6,10在第二分区，。。 当你搜索 where x <3 ，显然你必须到多个分区中去查询。

• 打赏
• 举报

回复 赞

[gxk9933]

很抱歉打扰大家了，我的问题应该是出在字段的选择上，而不是分区上，我使用datetime的速度会远远慢于使用int的速度，datetime的长度是8个字节，而int是4个，我想大概是因为这个原因，之前的问题描述上也有问题，麻烦大家了，非常感谢

• 打赏
• 举报

回复 赞

[dolphine813]

问题在于你写的是> 某个时间段 ，而且有排序，hash结构是无序的，需重新排序 你如果把sql改成 SELECT * FROM `stream` where `datetime` = '2013-02-17'，估计hash表分区的会更快

• 打赏
• 举报

回复 赞