MySQL 分区:为什么不采用适当的分区 [英] MySQL Partitioning: why it's not taking appropriate partition

问题描述

          DROP TABLE temp;
          CREATE TABLE `temp` (
         `CallID` bigint(8) unsigned NOT NULL,
         `InfoID` bigint(8) unsigned NOT NULL,
         `CallStartTime` datetime NOT NULL,
         `PartitionID` int(4) unsigned NOT NULL,
         KEY `CallStartTime`(`CallStartTime`)
       ) ENGINE=InnoDB DEFAULT CHARSET=latin1
         PARTITION BY HASH (PartitionID)
          PARTITIONS 366 

我在示例查询中使用 EXPLAIN 得到下一个结果:

I use EXPLAIN in a sample query I get the next result:

EXPLAIN PARTITIONS SELECT * FROM temp where PartitionID = 1

或

 EXPLAIN PARTITIONS SELECT * FROM temp where PartitionID = DAYOFYEAR('2013-01-01 10:24:00')

结果:

   id   select_type table   partitions  type    possible_keys   key key_len ref rows    Extra
  1         SIMPLE          temp    p1  ALL                 2   Using where

我不知道为什么它使用 p1 分区.这里 parititon 以 p0 开头

I don't know why it's using the p1 partition. Here parititon start with p0

推荐答案

HASH 分区方案意味着 MySQL 将您的任意数值转换为它自己的哈希值.您已经定义了 366 个分区.如果您的查询是:

The HASH partitioning scheme means MySQL translates your arbitrary numerical value into its own hash value. You have defined 366 partitions. What do you think would happen if your query were:

EXPLAIN PARTITIONS SELECT * FROM temp where PartitionID = 400?

EXPLAIN PARTITIONS SELECT * FROM temp where PartitionID = 400 ?

在这种情况下，您的 PartitionID 不能表示实际分区的 ID/名称，因为没有分区 400.

Your PartitionID cannot mean in this case the real partition's ID/name, since there is no partition 400.

现在，就在我们两个之间，您可能有兴趣了解 MySQL 的 HASHing 函数是一个简单的模数.因此，0 映射到分区 p0，1 映射到分区 p1，400 映射到分区 34 (== 400-366).

Now, just between the two of us, you might be interested to learn that MySQL's HASHing function is a simple modulus. Thus, 0 maps to partition p0, 1 maps to partition p1, and 400 maps to partition 34 (== 400-366).

一般来说，您不应该对正在使用的特定分区的标识太感兴趣.您应该更有兴趣知道分区之间存在良好的平衡.

Generally speaking you should not be too interested in the identify of the particular partition being used. You should be more interested to know that there is a good balance of partitions.

如果您觉得余额不合适，您可以考虑使用 KEY 分区方案，其中散列算法基于校验和函数，这意味着与 HASH 模数分区相比，值更随机"地分布方案.

If the balance doesn't seem right to you, you might consider using the KEY partitioning scheme, where the hashing algorithm is based on a checksum function, which means values are more "randomly" spread as compared to the HASH modulus partitioning scheme.

这篇关于MySQL 分区:为什么不采用适当的分区的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！