MySQL 表分区异常行为(查询突然变慢)

Question

(MySQL版本:5.6.15)

我有一个包含 1000 万行的巨大表 (Table_A)，采用实体-属性-值模型。它有一个复合唯一键 [Field_A + Element + DataTime]。

CREATE TABLE TABLE_A 
(
  `Field_A` varchar(5) NOT NULL,
  `Element` varchar(5) NOT NULL,
  `DataTime` datetime NOT NULL,
  `Value` decimal(10,2) DEFAULT NULL,
  UNIQUE KEY `A_ELE_TIME` (`Field_A`,`Element`,`DataTime`),
  KEY `DATATIME` (`DataTime`),
  KEY `ELEID` (`ELEID`),
  KEY `ELE_TIME` (`ELEID`,`DataTime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

行每分钟插入/更新到表中，因此每个 [DataTime](即每分钟)的行大小是固定的，大约 3K 行。

在上面的“插入/更新”之后，我有一个来自该表的“选择”查询。查询选择最近 25 小时内的一个指定元素(大约 30K 行)。此查询通常在 3 秒内处理。

SELECT 
    Field_A, Element, DataTime, `Value` 
FROM 
    Table_A 
WHERE 
    Element="XX" 
    AND DataTime between [time] and [time].

最初的内务处理将在 3 天后每 5 分钟删除任何行。

为了更好地管理，我尝试每 6 小时根据 [DataTime] 对表进行分区。 (本地时间00,06,12,18)

PARTITION BY RANGE (TO_DAYS(DataTime)*100+hour(DataTime))
(PARTITION p2014103112 VALUES LESS THAN (73590212) ENGINE = InnoDB,
...
PARTITION p2014110506 VALUES LESS THAN (73590706) ENGINE = InnoDB,
PARTITION pFuture VALUES LESS THAN MAXVALUE ENGINE = InnoDB)

我的管理脚本会删除过期的分区然后创建一个新的

ALTER TABLE TABLE_A REORGANIZE PARTITION pFuture INTO ( 
  PARTITION [new_partition_name] VALUES LESS THAN ([bound_value]), 
  PARTITION pFuture VALUES LESS THAN MAXVALUE 
)

新流程似乎运行顺利。

但是，SELECT 查询会突然变慢(> 100 秒)。

即使所有进程停止，查询仍然很慢。在“分析分区”(读取和存储分区的 key 分布)之前，它不会被修复。

通常一天发生一次。

非分区表不会发生这种情况。

因此，我们认为这是由分区 MySQL(巨大)表中损坏的索引引起的。

有没有人知道如何解决它？

非常感谢!!

Answer 1

如果您PARTITION BY RANGE (TO_DAYS(DataTime)*100+hour(DataTime))，当您使用between [from] and [to]操作过滤日期时间, mysql 将扫描所有分区，除非 [from] 等于 [to]。

所以您的查询突然变慢是合理的。

我的建议是使用没有小时的TO_DAYS(DataTime)分区，如果你查询最近25小时的数据，它最多只会扫描2个分区。

我不擅长MySql，我无法解释，希望其他聪明的人能进一步解释。但是您可以使用 EXPLAIN PARTITIONS 来证明这一点。这是 Sql Fiddle Demo .