MySQL分区和联接 [英] Mysql partitioning and joins

问题描述

很抱歉，如果这是一个愚蠢的问题，但这是新问题，因此需要一些帮助以了解一些内容.我目前正在将mysql升级到5.1，因此我可以在mysql中使用分区.我的问题是我是否对表进行分区，包括修剪过程在内的分区表是否仍可以使用联接进行查询，或者如果仅查询具有分区的表，则分区是否最佳?

Sorry if this is a stupid question but new to this so need some help to understand a couple of things. Im currently upgrading mysql to 5.1 so I can use partitions in mysql. My question is if I partition a table, would a partitioned table, including the pruning process, still work on a quering using a join or is partitioning optimal if your just quering the table that has the partitions?

编辑

以下是示例查询:

SELECT event.*,site.* FROM event INNER JOIN site ON event.siteid = site.id
WHERE event.eventdate >= [somedate] AND event.eventdate <= [somedate]
AND event.siteid = [siteid]

我使用eventdate字段在事件表上设置了分区. mysql仍然可以使用事件表上的分区，包括修剪过程吗?

And I have partitions setup on the events table using the eventdate field. Would mysql still be able to use the partitions on the events table including the pruning process?

推荐答案

分区表可以在联接中使用.

Partioned tables can be used in joins.

调整where子句以仅包含一个分区以获得最佳性能.
例如如果按年份划分，则可以像这样进行联接:

Tweak the where clause to include one partition only to get the best performance.
e.g. if you partition by year, you can do a join like:

select * from a
inner join partioned_table p on (p.id = a.id)
where p.year = '2011';

此查询将在有和没有where子句的情况下都可以使用，但是有了where子句，它将更快得多，因为您只访问一个分区.
如果您访问更多分区，则MySQL必须先使用临时表将分区缝合在一起，然后才能进行联接.
这种破坏了分区的目的.

This query will work with and without the where clause, but with the where clause it will be much faster because you are only accessing one partition.
If you access more partitions, MySQL has to use a temporary table to stitch the partitions together before it can do the join.
This kind of defeats the purpose of partitions.

这篇关于MySQL分区和联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！