我试图在 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 中使用范围分区获得意外结果
发布评论