在 MySQL 中使用范围分区获得意外结果 [英] Getting unexpected result with Range Partitioning in MySQL

问题描述

我试图在 2014 年的所有日子都使用 Range 进行分区

<前>按范围分区(UNIX_TIMESTAMP(gps_time))(分区 p01 值小于 (UNIX_TIMESTAMP('2014-01-01 00:00:00')),
...分区 p365 值小于 (UNIX_TIMESTAMP('2015-01-01 00:00:00')));

如果我插入较少的行，它会按预期进行分区.坐在特定的分区，那很好.

但是当我尝试一次插入数千行时，例如应该放在 2014-07-07 00:00:00 上的值放在最后一个分区，即 p365.

我面临的另一个问题是...当我在将值插入分区后一次又一次地键入以下查询时

<前>SELECT partition_name, table_rowsFROM information_schema.partitionsWHERE table_name = 'vehicle_gps';

每个分区的行数变化

为什么???请帮我解决这些问题

谢谢:)

解决方案

InnoDB 行计数是近似值.这可能解释了每个分区中的行数发生变化".将该数字视为(通常)在准确行数的 2 倍以内.

出于性能原因，一个表中的分区不要超过 50 个左右.

至于为什么批量插入存储到错误的分区，我想看到更多的'证明'.也许从一些这样的例子中会产生某种模式.

I'm trying to partitioning using Range for all days in 2014

PARTITION BY RANGE(UNIX_TIMESTAMP(gps_time)) (
PARTITION p01 VALUES LESS THAN (UNIX_TIMESTAMP('2014-01-01 00:00:00')),
                       .
                       .
                       .
PARTITION p365 VALUES LESS THAN (UNIX_TIMESTAMP('2015-01-01 00:00:00')));

If I insert few lesser rows It's partitioning as expected. sitting in particular partition and that's fine.

But when I try to insert thousands of rows at a time, for instance values which supposed to sit on 2014-07-07 00:00:00 placing at last partition i.e p365.

Another Problem Which I have been facing is...when I type following Query again and again after inserting values into partitions

      SELECT partition_name, table_rows 
      FROM information_schema.partitions
      WHERE table_name = 'vehicle_gps';

The number of rows in each partition changing

why??? Please help me in solving these problems

Thank You :)

解决方案

InnoDB Row counts are approximate. This probably explains "The number of rows in each partition changing". Treat the number as being (usually) within a factor of 2 of the exact number of rows.

For performance reasons, don't have more than about 50 partitions in a table.

As for why the batch insert stored into the wrong partition, I would like to see more 'proof'. Perhaps some pattern will arise from some examples of such.

这篇关于在 MySQL 中使用范围分区获得意外结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！