MySQL 分区选择未正确优化

Question

我有一个表“fvs_data”，其中包含用于分区的时间戳列。如果我有一个复杂的查询，查询优化器似乎没有选择正确的分区。例如这个查询

EXPLAIN PARTITIONS SELECT * FROM `fvs_data` WHERE `timestamp` = '2011-11-02'

使用正确的分区，如分区列表所示:p_2011_44，因为 TO_DAYS('2011-11-02')=734808。

但是如果添加另一个应该返回完全相同分区的条件，那么它想要检查其他分区之一

EXPLAIN PARTITIONS SELECT * FROM `fvs_data` WHERE `timestamp` > '2011-11-01' AND `timestamp` < '2011-11-03'

返回分区列表的 p_2011_42,p_2011_44。这是我的 CREATE TABLE 语法

CREATE TABLE `fvs_data` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `fvs_client_id` int(11) NOT NULL,
 `timestamp` datetime NOT NULL,
 `setupid` char(20) NOT NULL,
 `assyline` char(20) NOT NULL,
 `machine` char(20) NOT NULL,
 `side` char(20) NOT NULL,
 `module` char(20) NOT NULL,
 `fixtureid` char(20) NOT NULL,
 `fixturepos` char(20) NOT NULL,
 `feedpos` char(20) NOT NULL,
 `partnum` char(20) NOT NULL,
 `vendor` char(20) NOT NULL,
 `tid` char(20) NOT NULL,
 `quant` char(20) NOT NULL,
 `status` char(32) NOT NULL,
 `oper` char(20) NOT NULL,
 `lane1` char(20) NOT NULL,
 `lane2` char(20) NOT NULL,
 `sn` char(20) NOT NULL,
 `totalcomp` char(20) NOT NULL,
 PRIMARY KEY (`id`,`timestamp`),
 KEY `fvs_client_id` (`fvs_client_id`),
 KEY `setupid` (`setupid`),
 KEY `assyline` (`assyline`),
 KEY `machine` (`machine`),
 KEY `side` (`side`),
 KEY `module` (`module`),
 KEY `fixtureid` (`fixtureid`),
 KEY `fixturepos` (`fixturepos`),
 KEY `feedpos` (`feedpos`),
 KEY `partnum` (`partnum`),
 KEY `vendor` (`vendor`),
 KEY `tid` (`tid`),
 KEY `status` (`status`),
 KEY `oper` (`oper`),
 KEY `lane1` (`lane1`),
 KEY `lane2` (`lane2`),
 KEY `sn` (`sn`)
) ENGINE=MyISAM AUTO_INCREMENT=36032 DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE ( TO_DAYS(timestamp))
(PARTITION p_2011_42 VALUES LESS THAN (734796) ENGINE = MyISAM,
PARTITION p_2011_43 VALUES LESS THAN (734803) ENGINE = MyISAM,
PARTITION p_2011_44 VALUES LESS THAN (734810) ENGINE = MyISAM,
PARTITION p_2011_45 VALUES LESS THAN (734817) ENGINE = MyISAM) */

我正在尝试每周进行分区，但不是基于从星期日开始的真实周，我只是将一年中的第几天除以 7 并使用它来创建分区名称。

Answer 1

分区修剪将始终检查第一个分区，无论您要搜索的值是什么。这与分区方式有关 stores NULLs in the first partition .

要解决此问题并优化范围分区，请为小于您通常存储的任何值的值创建一个初始分区:

...
PARTITION BY RANGE ( TO_DAYS(timestamp))
(
PARTITION p_0000 VALUES LESS THAN (0) ENGINE = MyISAM, -- NULLs end up here
PARTITION p_2011_42 VALUES LESS THAN (734796) ENGINE = MyISAM,
PARTITION p_2011_43 VALUES LESS THAN (734803) ENGINE = MyISAM,
PARTITION p_2011_44 VALUES LESS THAN (734810) ENGINE = MyISAM,
PARTITION p_2011_45 VALUES LESS THAN (734817) ENGINE = MyISAM
PARTITION p_MAX VALUES LESS THAN MAXVALUE ENGINE = MyISAM
)

当然，您的时间戳列不是 NULL，因此第一个分区将为空，因此搜索速度非常快。

P.S.:您还应该添加一个分区来捕获任何其他分区范围之外的任何大值。否则插入此类行将失败。