MySQL分区显示低性能

Question

我试图检查实现MySQL数据库分区是否对我们的应用程序有益。我听说过很多关于对大量记录使用分区的好处。但令人惊讶的是，在实现分区后进行负载测试时，应用程序的响应时间减少了3倍。有人能帮忙解释为什么会发生这种情况吗？

让我详细解释一下：

以下是分区"未"到位时表的DDL。

CREATE TABLE `myTable` ( 
`column1` bigint(20) unsigned NOT NULL AUTO_INCREMENT, 
`column2` char(3) NOT NULL, 
`column3` char(3) NOT NULL, 
`column4` char(2) NOT NULL, 
`column5` smallint(4) unsigned NOT NULL, 
`column6` date NOT NULL, 
`column7` varchar(2) NOT NULL, 
`column8` tinyint(3) unsigned NOT NULL COMMENT 'Seat Count Ranges from 0-9.', 
`column9` varchar(2) NOT NULL, 
`column10` varchar(4) NOT NULL, 
`column11` char(2) NOT NULL, 
`column12` datetime NOT NULL, 
`column13` datetime DEFAULT NULL, 
PRIMARY KEY (`column1`), 
KEY `index1` (`column2`,`column3`,`column4`,`column5`,`column7`,`column6`), 
KEY `index2` (`column2`,`column3`,`column6`,`column4`) 
) ENGINE=InnoDB AUTO_INCREMENT=342024674 DEFAULT CHARSET=latin1; 

下面是实现基于日期字段的"Range"分区后同一个表的DDL。

CREATE TABLE `myTable` ( 
`column1` bigint(20) unsigned NOT NULL AUTO_INCREMENT, 
`column2` char(3) NOT NULL, 
`column3` char(3) NOT NULL, 
`column4` char(2) NOT NULL, 
`column5` smallint(4) unsigned NOT NULL, 
`column6` date NOT NULL, 
`column7` varchar(2) NOT NULL, 
`column8` tinyint(3) unsigned NOT NULL COMMENT 'Seat Count Ranges from 0-9.', 
`column9` varchar(2) NOT NULL, 
`column10` varchar(4) NOT NULL, 
`column11` char(2) NOT NULL, 
`column12` datetime NOT NULL, 
`column13` datetime DEFAULT NULL, 
PRIMARY KEY (`column1`,`column6`), 
KEY `index1` (`column2`,`column3`,`column4`,`column5`,`column7`,`column6`), 
KEY `index2` (`column2`,`column3`,`column6`,`column4`) 
) ENGINE=InnoDB AUTO_INCREMENT=342024674 DEFAULT CHARSET=latin1 
PARTITION BY RANGE COLUMNS(`column6`) 
(PARTITION date_jul_11 VALUES LESS THAN ('2011-08-01') ENGINE = InnoDB, 
PARTITION date_aug_11 VALUES LESS THAN ('2011-09-01') ENGINE = InnoDB, 
PARTITION date_sep_11 VALUES LESS THAN ('2011-10-01') ENGINE = InnoDB, 
PARTITION date_oct_11 VALUES LESS THAN ('2011-11-01') ENGINE = InnoDB, 
PARTITION date_nov_11 VALUES LESS THAN ('2011-12-01') ENGINE = InnoDB, 
PARTITION date_dec_11 VALUES LESS THAN ('2012-01-01') ENGINE = InnoDB, 
PARTITION date_jan_12 VALUES LESS THAN ('2012-02-01') ENGINE = InnoDB, 
PARTITION date_feb_12 VALUES LESS THAN ('2012-03-01') ENGINE = InnoDB, 
PARTITION date_mar_12 VALUES LESS THAN ('2012-04-01') ENGINE = InnoDB, 
PARTITION date_apr_12 VALUES LESS THAN ('2012-05-01') ENGINE = InnoDB, 
PARTITION date_may_12 VALUES LESS THAN ('2012-06-01') ENGINE = InnoDB, 
PARTITION date_jun_12 VALUES LESS THAN ('2012-07-01') ENGINE = InnoDB, 
PARTITION date_jul_12 VALUES LESS THAN ('2012-08-01') ENGINE = InnoDB, 
PARTITION date_aug_12 VALUES LESS THAN ('2012-09-01') ENGINE = InnoDB, 
PARTITION date_sep_12 VALUES LESS THAN ('2012-10-01') ENGINE = InnoDB, 
PARTITION date_oct_12 VALUES LESS THAN ('2012-11-01') ENGINE = InnoDB, 
PARTITION date_nov_12 VALUES LESS THAN ('2012-12-01') ENGINE = InnoDB, 
PARTITION date_dec_12 VALUES LESS THAN ('2013-01-01') ENGINE = InnoDB, 
PARTITION date_jan_13 VALUES LESS THAN ('2013-02-01') ENGINE = InnoDB, 
PARTITION date_feb_13 VALUES LESS THAN ('2013-03-01') ENGINE = InnoDB, 
PARTITION date_mar_13 VALUES LESS THAN ('2013-04-01') ENGINE = InnoDB, 
PARTITION date_apr_13 VALUES LESS THAN ('2013-05-01') ENGINE = InnoDB, 
PARTITION date_may_13 VALUES LESS THAN ('2013-06-01') ENGINE = InnoDB, 
PARTITION date_jun_13 VALUES LESS THAN ('2013-07-01') ENGINE = InnoDB, 
PARTITION date_oth VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB); 

下面是一个用于进行负载测试以测试性能的示例查询。

SELECT column8, column9
FROM myTable
WHERE column2 = ? AND column3 = ? AND column4 =? AND column5 = ? AND column7 = ? AND column6 = ?
LIMIT 1 

将上述?替换为数据库中存在的真实值以进行测试。

请注意，"myTable"表中的记录数量约为3.42亿，用于进行性能测试的测试数据数量约为200万。

然而，正如我所说，实现分区后的性能下降了令人震惊的3倍。知道是什么原因造成的吗？

此外，请让我知道对表结构或索引进行任何进一步的更改是否有助于解决此问题。

Answer 1

请记住，分区的目标是加快查询速度，因为查询限制了可以在其中找到结果的分区数量。我认为问题在于测试查询中的column6 = ?。我猜想，对于column6，需要一个精确的值，而不是一个范围，会将结果集减少到很少的值。因此，在缩小分区的过程中，您基本上已经找到了结果。由于索引是在多个分区中分割的，因此缩小过程是有代价的。

您希望从column6上的分区中受益的查询类型是返回一系列值的查询，这些值仅限于少数分区。例如，尝试这样的测试查询：

SELECT column8, column9
FROM myTable
WHERE column6 < ? AND column6 > ? AND column2 = ? AND column3 = ? AND column4 =? AND column5 = ?

其中column6的范围跨越大约2个分区，并且总结果计数预计会相当大。

这可能会有所帮助：http://dev.mysql.com/tech-resources/articles/partitioning.html

Answer 2

看看这个，我会考虑几件事。

第一个也是最明显的问题是，当你将数据分布在不同的设备（磁盘）上时，分区带来的巨大好处就来了——从发布的代码中没有证据表明这一点。

接下来，您的分区被硬编码到特定的日期范围——因此，当date_oth开始填满时，您必须制定一个更好的计划。

AND column6=？

所以您只测试了来自单个分区的数据的性能？充其量，这不会比一个表中的所有数据更快。

正如Nathan所指出的，您是按列6进行分区的，但在任何索引的前面都没有列6，因此DBMS必须在每个分区中搜索索引才能找到数据，这很可能是性能如此差的原因。（我不同意分区只帮助范围查询的观点）。