Я работаю с mysql, запрашивая таблицу с 12 миллионами регистров, которые составляют год указанных данных. Запрос должен выбрать определенный тип данных (монета, предприятие, тип и т. д.), а затем предоставить среднее значение за день для определенных полей этих данных, чтобы мы могли впоследствии построить график. Мечта состоит в том, чтобы иметь возможность делать это в режиме реального времени, поэтому время отклика составляет менее 10 секунд, однако на данный момент это совсем не выглядит ярко, так как это занимает от 4 до 6 минут. Например, один из запросов where дает 150 тыс. регистров, разбивается примерно на 500 в день, а затем мы усредняем три поля (которых нет в предложении where) с помощью AVG() и GroupBy.
Теперь к необработанным данным запрос
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)
Выбор регистров «где» без использования группы или 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
idMoneda_cache, idTipoRA_cache, idEmisorFusionado_cache, plazoResidual
(не уверен, чтоplazoResidual
илиfecha
должен быть последним). А затем запустите его (илиEXPLAIN
) безUSE INDEX
. Причина в том, что MySQL будет использовать индекс только до поля, для которого есть условие диапазона (в вашем случае вы используете только столбецfecha
из вашего индекса). - person Vatev   schedule 27.07.2012SHOW CREATE TABLE ...
(возможно, без каких-либо нерелевантных столбцов) и полный выводEXPLAIN
- person Vatev   schedule 27.07.2012