大表上的 MySQL 查询优化

Question

我正在使用 mysql 查询一个表，该表有 1200 万个寄存器，这些寄存器是上述数据的一年。查询必须选择某种数据(硬币、企业、类型等)，然后为该数据的某些字段提供每日平均值，以便我们之后可以绘制图表。梦想能够实时执行此操作，因此响应时间少于 10 秒，但目前它看起来一点也不亮，因为它需要 4 到 6 分钟。例如，其中一个 where 查询产生了 150k 个寄存器，每天拆分大约 500 个，然后我们使用 AVG() 和 GroupBy 平均三个字段(不在 where 子句上)。

现在，对于原始数据，查询是

SELECT 
`Valorizacion`.`fecha`, AVG(tir) AS `tir`, AVG(tirBase) AS `tirBase`, AVG(precioPorcentajeValorPar) AS `precioPorcentajeValorPar` 
FROM `Valorizacion` USE INDEX (ix_mercado2)
WHERE
(Valorizacion.fecha >= '2011-07-17' ) AND
(Valorizacion.fecha <= '2012-07-18' ) AND
(Valorizacion.plazoResidual >= 365 ) AND
(Valorizacion.plazoResidual <= 3650000 ) AND
(Valorizacion.idMoneda_cache IN ('UF')) AND
(Valorizacion.idEmisorFusionado_cache IN ('ABN AMRO','WATTS', ...)) AND
(Valorizacion.idTipoRA_cache IN ('BB', 'BE', 'BS', 'BU'))
GROUP BY `Valorizacion`.`fecha` ORDER BY `Valorizacion`.`fecha` asc;

248 rows in set (4 min 28.82 sec)

索引是遍历顺序中的所有where子句字段

(fecha, idTipoRA_cache, idMoneda_cache, idEmisorFusionado_cache, plazoResidual)

选择“where”寄存器，不使用 group by 或 AVG

149670 rows in set (58.77 sec)

然后选择寄存器，分组并只做一个计数(*)而不是平均花费

248 rows in set (35.15 sec)

这可能是因为它不需要去磁盘上搜索数据，而是直接从索引查询中获取数据。

就目前而言，我的想法是告诉我的老板“很抱歉，但它无法完成”，但在这样做之前，我来找你们问问你们是否认为我可以做些什么来改善这一点。我认为我可以通过索引时间将具有最大基数的索引移到前面等来改进搜索，但即使在那之后，为每条记录访问磁盘和执行 AVG 所花费的时间似乎太多了。

有什么想法吗？

-- 编辑，表结构

CREATE TABLE `Valorizacion` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `idInstrumento` int(11) NOT NULL,
  `fecha` date NOT NULL,
  `tir` decimal(10,4) DEFAULT NULL,
  `tirBase` decimal(10,4) DEFAULT NULL,
  `plazoResidual` double NOT NULL,
  `duracionMacaulay` double DEFAULT NULL,
  `duracionModACT365` double DEFAULT NULL,
  `precioPorcentajeValorPar` decimal(20,15) DEFAULT NULL,
  `valorPar` decimal(20,15) DEFAULT NULL,
  `convexidad` decimal(20,15) DEFAULT NULL,
  `volatilidad` decimal(20,15) DEFAULT NULL,
  `montoCLP` double DEFAULT NULL,
  `tirACT365` decimal(10,4) DEFAULT NULL,
  `tipoVal` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `idEmisorFusionado_cache` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `idMoneda_cache` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `idClasificacionRA_cache` int(11) DEFAULT NULL,
  `idTipoRA_cache` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `fechaPrepagable_cache` date DEFAULT NULL,
  `tasaEmision_cache` decimal(10,4) DEFAULT NULL,
  PRIMARY KEY (`id`,`fecha`),
  KEY `ix_FechaNemo` (`fecha`,`idInstrumento`) USING BTREE,
  KEY `ix_mercado_stackover` (`idMoneda_cache`,`idTipoRA_cache`,`idEmisorFusionado_cache`,`plazoResidual`)
) ENGINE=InnoDB AUTO_INCREMENT=12933194 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Answer 1

无论您尝试做什么，从 12M 记录中选择 150K 条记录并对它们执行聚合函数都不会很快。

您可能主要处理历史数据，因为您的示例查询是针对一年的数据。更好的方法可能是预先计算您的每日平均值并将它们放入单独的表格中。然后您可以查询这些表以获取报告、图表等。您需要决定何时以及如何运行此类计算，这样您就不需要对相同的数据再次运行它们。

当您的要求是对数百万条历史记录进行分析和报告时，您需要考虑数据仓库方法 http://en.wikipedia.org/wiki/Data_warehouse而不是简单的数据库方法。