我有一个大表(股票价格历史数据,InnoDB,3500万行和增长)与复合主键(assetid(int),日期(日期)).除了定价信息,我还有200个需要与每条记录对应的双值.

CREATE TABLE `mytable` (
`assetid` int(11) NOT NULL,`date` date NOT NULL,`close` double NOT NULL,`f1` double DEFAULT NULL,`f2` double DEFAULT NULL,`f3` double DEFAULT NULL,`f4` double DEFAULT NULL,... skip a few …
`f200` double DEFAULT NULL,PRIMARY KEY (`assetid`,`date`)) ENGINE=`InnoDB` DEFAULT CHARACTER SET latin1 COLLATE
    latin1_swedish_ci ROW_FORMAT=COMPACT CHECKSUM=0 DELAY_KEY_WRITE=0 
    PARTITION BY RANGE COLUMNS(`date`) PARTITIONS 51;

我最初直接在这个表中存储了200个双列,以便于更新和检索,而且这个工作正常,因为在这个表上唯一的查询是由assetid和date(这些在宗教上包含在对这个表的任何查询中) ),只读了200个双列.我的数据库大小约为45 Gig

但是,现在我有了这个要求,我需要能够通过这200个列(名为f1,f2,… f200)的任意组合查询此表,例如：

select from mytable 
where assetid in (1,2,3,4,5,6,7,....)
and date > '2010-1-1' and date < '2013-4-5'
and f1 > -0.23 and f1 < 0.9
and f117 > 0.012 and f117 < .877
etc,etc

我以前没有必要处理这么大量的数据,所以我的第一直觉是这200个列中的每一个都需要索引,或者我会结束大型表扫描等等.对我来说这意味着我需要为200列中的每一列提供一个表,其中包含主键,值和索引值.所以我顺其自然.

CREATE TABLE `f1` (
`assetid` int(11) NOT NULL DEFAULT '0',`date` date NOT NULL DEFAULT '0000-00-00',`value` double NOT NULL DEFAULT '0',`date`),INDEX `val` (`value`)
) ENGINE=`InnoDB` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ROW_FORMAT=COMPACT CHECKSUM=0 DELAY_KEY_WRITE=0;

我填写并索引了所有200张桌子.我将所有200列完整地保留在主表中,因为它经常在assetid和日期范围内查询,并且所有200列都被选中.我认为将这些列留在父表(未编入索引)中以供读取,然后另外将它们在自己的表中编入索引(用于连接过滤)将是最高效的.我在查询的新形式上运行了解释

select count(p.assetid) as total 
from mytable p 
inner join f1 f1 on f1.assetid = p.assetid and f1.date = p.date
inner join f2 f2 on f2.assetid = p.assetid and f2.date = p.date 
where p.assetid in(1,7)
and p.date >= '2011-01-01' and p.date < '2013-03-14' 
and(f1.value >= 0.96 and f1.value <= 0.97 and f2.value >= 0.96 and f2.value <= 0.97)

确实我的预期结果已经实现,解释说明扫描的行对于此查询来说要小得多.然而,我结束了一些不良的副作用.

1)我的数据库从45 Gig变为110 Gig.我不能再将数据库保存在RAM中. (我途中有256Gig的RAM)

2)每晚插入新数据需要完成200次而不是一次

3)新200个表的维护/碎片整理比仅1个表长200倍.它不能在一个晚上完成.

4)对f1等表的查询不一定是高性能的.例如：

select min(value) from f1 
 where assetid in (1,7) 
 and date >= '2013-3-18' and date < '2013-3-19'

上面的查询,虽然解释显示它在< 1000行,可能需要30秒才能完成.我认为这是因为索引太大而无法放入内存中. 由于这有很多坏消息,我进一步观察并找到了分区.我在主表上实现了分区,每隔3个月进行一次分区.每月似乎对我有意义,但我已经读过,一旦你超过120个分区左右,性能就会受到影响.在接下来的20年左右的时间里,按季划分将使我处于这种状态之下.每个分区都低于2 Gig.我运行解释分区,一切似乎正在修剪,所以无论我觉得分区是一个很好的步骤,至少是分析/优化/修复目的. 我花了很多时间在这篇文章上 http://ftp.nchu.edu.tw/MySQL/tech-resources/articles/testing-partitions-large-db.html

我的表目前仍然使用主键进行分区.文章提到主键可以使分区表变慢,但如果你有一台可以处理分区表的机器,分区表上的主键会更快.知道我有一台大机器(256 G RAM),我把钥匙打开了.

所以我看到了,这是我的选择

选项1

1)删除额外的200个表并让查询执行表扫描以查找f1,f2等值.非唯一索引实际上可能会损害正确分区表上的性能.在用户运行查询之前运行解释,如果扫描的行数超过我定义的某个阈值,则拒绝它们.拯救自己巨大的数据库的痛苦.哎呀,无论如何它很快就会在记忆中.

子问题：

听起来我选择了合适的分区方案吗？

选项2

使用相同的3个月方案对所有200个表进行分区.享受较小的行扫描,并允许用户运行更大的查询.现在它们被分区了至少我可以一次管理它们1个分区以进行维护.哎呀,无论如何它很快就会在记忆中.开发有效的方法来每晚更新它们.

子问题：

你有没有看到我可以避免这些f1,f3,f4 …表上的主键索引的原因,知道我在查询时总是有自己的断言和日期？对我来说似乎反直觉,但我不习惯这种大小的数据集.这会缩小数据库我认为的一堆

选项3

删除主表中的f1,f3列以回收该空间.如果我需要阅读200个功能,可以做200个连接,也许它不会听起来那么慢.

选项4

你们都有比我想象的更好的结构方法.

*注意：我很快会在每个项目中添加另外50-100这些双倍值,所以我需要设计知道即将到来.

感谢您的帮助

更新#1 – 2013年3月24日

我接受了下面的评论中提出的想法,并使用以下设置创建了一个新表：

create table 'features'{
  assetid int,date    date,feature varchar(4),value   double
}

我以3个月的间隔对桌子进行了分区.

我吹走了之前的200个表,以便我的数据库恢复到45 Gig并开始填写这个新表.一天半之后,它完成了,我的数据库现在处于胖乎乎的状态
220演出！

它确实允许从主表中删除这200个值,因为我可以从一个连接中获取它们,但这实际上只能让我回到25 Gigs左右

我要求它创建一个关于assetid,date,feature和value的索引的主键,经过9个小时的chugging它真的没有凹痕,似乎冻结了所以我杀死了那部分.

我重建了几个分区,但它似乎没有回收太多/任何空间.

所以这个解决方案看起来似乎不太理想.我想知道这些行占用的空间比空间大得多吗？这就是为什么这个解决方案占用了更多的空间？

我偶然发现了这篇文章：

http://www.chrismoos.com/2010/01/31/mysql-partitioning-tables-with-millions-of-rows

它给了我一个主意.它说：

At first,I thought about RANGE partitioning by date,and while I am using the date in my queries,it is very common for a query to have a very large date range,and that means it could easily span all partitions.

现在我也按日期进行范围分区,但也允许按大日期范围进行搜索,这会降低我的分区效率.当我搜索时,我将始终有一个日期范围,但是我也将始终有一个清单列表.也许我的解决方案应该是通过assetid和date进行分区,其中我识别通常搜索的assetid范围(我可以提出,有标准列表,S& P 500,Russell 2000等).这样我几乎不会看整个数据集.

然后,我仍然主要关注assetid和日期,所以也许这没有多大帮助.

任何更多的想法/意见将不胜感激.