Сложный запрос чрезвычайно медленный при группировании или упорядочении предложений по индексу?

ProgramBox

Сложный запрос чрезвычайно медленный при группировании или упорядочении предложений по индексу?

Post author:admin
Запись опубликована:20 марта, 2023
Post category:Вопросы по программированию

#mysql #group-by #sql-order-by

#mysql #группировка по #sql-order-by

Вопрос:

Итак, мне была поставлена задача воспроизвести функциональность, которую мы в настоящее время обрабатываем с помощью кода, в MySQL.

Приведенный ниже запрос работает прекрасно, возвращая 245 000 строк за 40 мс, однако, как только вы касаетесь его с помощью группы или порядка, он занимает более 6 с.

Есть ли у кого-нибудь какие-либо предложения о том, какие изменения необходимо внести в индексы или, возможно, как изменить запрос, чтобы улучшить его?

Спасибо

Без какой-либо группировки или упорядочения

 select 
    s.id as sensorid,
    s.sensortypeid,
    COALESCE(s.pulserate, 1) as pulserate,
    COALESCE(s.correctionFactor, 1) as correctionFactor,

    ur.id as unitrateid,
    COALESCE(ur.priceperkwh, 0) as priceperkwh,
    COALESCE(ur.duosCharges, 0) as duosCharges,

    IF(t.blnnonunitratecharges, t.nonunitratecharge/48, 0) as nonunitratecost,
    IF(t.blnFeedIn, COALESCE(t.feedInRate, 0), 0) as feedInRate,
    IF(t.blnRoc, COALESCE(t.rocRate, 0), 0) as rocRate,

    from_unixtime(FLOOR(UNIX_TIMESTAMP(srs.dateTimeStamp)/(30*60))*(30*60)) as timeKey

from sensorreadings srs
    inner join sensorpoints sp on (sp.id = srs.sensorpointid)
    inner join sensors s on (s.id = sp.sensorid)
    left join unitrates ur on ur.id = (
        select 
            ur.id 
        from unitrates ur, tariffs t, companyhubs ch
        where 
            ur.tariffid = t.id and
            t.companyid = ch.companyid and 
            ch.hubid = s.hubid and 
            t.utilitytypeid = s.utilitytypeid and 
            (srs.dateTimeStamp between t.startdate and t.enddate) and 
            ((time(srs.dateTimeStamp) between ur.starttime and ur.endtime) and 
            (ur.dayMask amp; POW(2, WEEKDAY(srs.dateTimeStamp)) <> 0) and 
            (ur.monthMask amp; POW(2, MONTH(srs.dateTimeStamp) - 1) <> 0)) 
        order by 
            t.startdate desc, 
            ur.starttime desc 
        limit 0, 1
    )
    left join tariffs t on (t.id = ur.tariffid)
where 
    s.id = 5289

С группировкой и упорядочением

 select 
    s.id as sensorid,
    s.sensortypeid,
    COALESCE(s.pulserate, 1) as pulserate,
    COALESCE(s.correctionFactor, 1) as correctionFactor,

    ur.id as unitrateid,
    COALESCE(ur.priceperkwh, 0) as priceperkwh,
    COALESCE(ur.duosCharges, 0) as duosCharges,

    IF(t.blnnonunitratecharges, t.nonunitratecharge/48, 0) as nonunitratecost,
    IF(t.blnFeedIn, COALESCE(t.feedInRate, 0), 0) as feedInRate,
    IF(t.blnRoc, COALESCE(t.rocRate, 0), 0) as rocRate,

    min(srs.reading) as minReading,
    avg(srs.reading) as avgReading,

    from_unixtime(FLOOR(UNIX_TIMESTAMP(srs.dateTimeStamp)/(30*60))*(30*60)) as timeKey

from sensorreadings srs
    inner join sensorpoints sp on (sp.id = srs.sensorpointid)
    inner join sensors s on (s.id = sp.sensorid)
    left join unitrates ur on ur.id = (
        select 
            ur.id 
        from unitrates ur, tariffs t, companyhubs ch
        where 
            ur.tariffid = t.id and
            t.companyid = ch.companyid and 
            ch.hubid = s.hubid and 
            t.utilitytypeid = s.utilitytypeid and 
            (srs.dateTimeStamp between t.startdate and t.enddate) and 
            ((time(srs.dateTimeStamp) between ur.starttime and ur.endtime) and 
            (ur.dayMask amp; POW(2, WEEKDAY(srs.dateTimeStamp)) <> 0) and 
            (ur.monthMask amp; POW(2, MONTH(srs.dateTimeStamp) - 1) <> 0)) 
        order by 
            t.startdate desc, 
            ur.starttime desc 
        limit 0, 1
    )
    left join tariffs t on (t.id = ur.tariffid)
where 
    s.id = 5289
group by timeKey
order by timeKey desc

Схемы

 CREATE TABLE `sensorreadings` (
    `sensorpointid` int(11) NOT NULL DEFAULT '0',
    `reading` decimal(15,5) NOT NULL,
    `dateTimeStamp` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
    PRIMARY KEY (`sensorpointid`,`dateTimeStamp`),
    KEY `sensormetricid` (`sensormetricid`),
    KEY `sensorreadings_timestamp` (`dateTimeStamp`,`sensorpointid`),
    KEY `sensorpointid` (`sensorpointid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `sensorpoints` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `sensorid` int(11) DEFAULT NULL,
    `hubpointid` int(11) DEFAULT NULL,
    `pointlabel` varchar(255) NOT NULL,
    `pointhash` varchar(255) NOT NULL,
    `target` decimal(10,0) DEFAULT NULL,
    `tolerance` decimal(10,0) DEFAULT '0',
    `blnlivepoint` int(1) NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`),
    KEY `FK_sensorpoints_sensors` (`sensorid`),
    CONSTRAINT `FK_sensorpoints_sensors` FOREIGN KEY (`sensorid`) REFERENCES `sensors` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=8824 DEFAULT CHARSET=latin1;

CREATE TABLE `sensors` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `hubid` int(11) DEFAULT NULL,
    `sensortypeid` int(11) NOT NULL DEFAULT '5',
    `pulserate` decimal(10,6) DEFAULT NULL,
    `utilitytypeid` int(11) NOT NULL DEFAULT '1',
    `correctionfactor` decimal(10,3) DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `FK_sensors_sensortypes` (`sensortypeid`),
    KEY `FK_sensors_hubs` (`hubid`),
    KEY `FK_sensors_utilitytypes` (`utilitytypeid`),
    CONSTRAINT `FK_sensors_hubs` FOREIGN KEY (`hubid`) REFERENCES `hubs` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `FK_sensors_sensortypes` FOREIGN KEY (`sensortypeid`) REFERENCES `sensortypes` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
) ENGINE=InnoDB AUTO_INCREMENT=5503 DEFAULT CHARSET=latin1;


CREATE TABLE `tariffs` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `companyid` int(11) DEFAULT NULL,
    `utilitytypeid` int(11) DEFAULT NULL,
    `startdate` date NOT NULL,
    `enddate` date NOT NULL,
    `blnnonunitratecharges` int(1) DEFAULT '0',
    `nonunitratecharge` decimal(16,8) DEFAULT '0.00000000',
    `blnFeedIn` int(1) DEFAULT '0',
    `blnRoc` int(1) DEFAULT '0',
    `rocRate` decimal(16,8) DEFAULT '0.00000000',
    `feedInRate` decimal(16,8) DEFAULT '0.00000000',
    PRIMARY KEY (`id`),
    KEY `companyid` (`companyid`,`utilitytypeid`,`startdate`,`enddate`),
    KEY `startdate` (`startdate`,`enddate`),
) ENGINE=InnoDB AUTO_INCREMENT=1107 DEFAULT CHARSET=latin1;

CREATE TABLE `unitrates` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `tariffid` int(11) NOT NULL,
    `priceperkwh` decimal(16,8) NOT NULL,
    `starttime` time NOT NULL,
    `endtime` time NOT NULL,
    `duoscharges` decimal(10,5) DEFAULT NULL,
    `dayMask` int(11) DEFAULT '127',
    `monthMask` int(11) DEFAULT '4095',
    PRIMARY KEY (`id`),
    KEY `FK_unitrates_tariffs` (`tariffid`),
    KEY `times` (`starttime`,`endtime`),
    KEY `masks` (`dayMask`,`monthMask`),
    CONSTRAINT `FK_unitrates_tariffs` FOREIGN KEY (`tariffid`) REFERENCES `tariffs` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
) ENGINE=InnoDB AUTO_INCREMENT=3104 DEFAULT CHARSET=latin1;

Объясняет

Без групп / упорядочения

 | id | select_type        | table | type   | possible_keys                   | key                     | key_len | ref                           | rows | Extra                                        | 
|----|--------------------|-------|--------|---------------------------------|-------------------------|---------|-------------------------------|------|----------------------------------------------| 
| 1  | PRIMARY            | s     | const  | PRIMARY                         | PRIMARY                 | 4       | const                         | 1    | NULL                                         | 
| 1  | PRIMARY            | sp    | ref    | PRIMARY,FK_sensorpoints_sensors | FK_sensorpoints_sensors | 5       | const                         | 1    | Using index                                  | 
| 1  | PRIMARY            | srs   | ref    | PRIMARY,sensorpointid           | PRIMARY                 | 4       | dbnameprod.sp.id              | 211  | Using index                                  | 
| 1  | PRIMARY            | ur    | eq_ref | PRIMARY                         | PRIMARY                 | 4       | func                          | 1    | Using where                                  | 
| 1  | PRIMARY            | t     | eq_ref | PRIMARY                         | PRIMARY                 | 4       | dbnameprod.ur.tariffid        | 1    | NULL                                         | 
| 2  | DEPENDENT SUBQUERY | ch    | ref    | hubid                           | hubid                   | 5       | const                         | 1    | Using where; Using temporary; Using filesort | 
| 2  | DEPENDENT SUBQUERY | t     | ref    | PRIMARY,companyid,startdate     | companyid               | 10      | dbnameprod.ch.companyid,const | 1    | Using where; Using index                     | 
| 2  | DEPENDENT SUBQUERY | ur    | ref    | FK_unitrates_tariffs,times      | FK_unitrates_tariffs    | 4       | dbnameprod.t.id               | 1    | Using where                                  |

С упорядочением / группировкой

 | id | select_type        | table | type   | possible_keys                                                 | key                     | key_len | ref                           | rows | Extra                                        | 
|----|--------------------|-------|--------|---------------------------------------------------------------|-------------------------|---------|-------------------------------|------|----------------------------------------------| 
| 1  | PRIMARY            | s     | const  | PRIMARY                                                       | PRIMARY                 | 4       | const                         | 1    | Using temporary; Using filesort              | 
| 1  | PRIMARY            | sp    | ref    | PRIMARY,FK_sensorpoints_sensors                               | FK_sensorpoints_sensors | 5       | const                         | 1    | Using index                                  | 
| 1  | PRIMARY            | srs   | ref    | PRIMARY,sensormetricid,sensorreadings_timestamp,sensorpointid | PRIMARY                 | 4       | dbnameprod.sp.id              | 211  | Using index                                  | 
| 1  | PRIMARY            | ur    | eq_ref | PRIMARY                                                       | PRIMARY                 | 4       | func                          | 1    | Using where                                  | 
| 1  | PRIMARY            | t     | eq_ref | PRIMARY                                                       | PRIMARY                 | 4       | dbnameprod.ur.tariffid        | 1    | NULL                                         | 
| 2  | DEPENDENT SUBQUERY | ch    | ref    | hubid                                                         | hubid                   | 5       | const                         | 1    | Using where; Using temporary; Using filesort | 
| 2  | DEPENDENT SUBQUERY | t     | ref    | PRIMARY,companyid,startdate                                   | companyid               | 10      | dbnameprod.ch.companyid,const | 1    | Using where; Using index                     | 
| 2  | DEPENDENT SUBQUERY | ur    | ref    | FK_unitrates_tariffs,times                                    | FK_unitrates_tariffs    | 4       | dbnameprod.t.id               | 1    | Using where                                  |

1. Вопросы производительности должны включать EXPLAIN ANALYZE и некоторую информацию о размере таблицы, индексе, текущей производительности, времени ожидания и т. Д. Slow это относительный термин, и нам нужно реальное значение для сравнения. MySQL

2. @JuanCarlosOropeza — Спасибо, что перезвонили мне. Я добавил EXPLAIN анализ — есть ли что-нибудь, что мне нужно добавить в OP?

3. Продвигая использование explict JOIN sintaxis, Аарон Бертран написал об этом хорошую статью » Вредные привычки: использование объединений в старом стиле «.

Ответ №1:

Ну, вы группируете и упорядочиваете вычисляемое поле timeKey , а в базе данных нет индекса для этого поля.

Поэтому db необходимо вычислить все строки перед выполнением group by, а затем выполнить упорядочение, и без index не может ускорить вычисления.

Предложение: создайте поле времени в своей базе данных и добавьте индекс для этого поля.

1. Спасибо Хуан. Вычисляемое поле timeKey является динамическим (т. Е. (30*60) Может меняться в зависимости от требований пользователя) — при выполнении некоторого тестирования удаление левого соединения unitrates значительно ускоряет процесс при группировании / упорядочении? Есть мысли?

2. Аналогичная проблема. В этом соединении вы пытаетесь найти соответствие с подзапросом. И у этого нет предварительно вычисленного индекса. Но не имеет смысла, чтобы первый запрос выполнялся быстрее с тем же соединением. Я предлагаю вам сначала попытаться оптимизировать только подзапрос, потому что он также использует вычисляемое поле для создания объединений

Ответ №2:

Прежде чем рассматривать производительность, давайте обсудим вероятность того, что запрос нарушен.

При выполнении a GROUP BY все неагрегированные SELECT значения должны быть включены в GROUP BY . В противном случае может быть доставлено любое случайное значение.

Кроме того, этот шаблон:

 SELECT ..., AVG(a.x)
    FROM a 
    JOIN b ON ...
    GROUP BY a.id

обычно приводит к увеличению количества строк (из-за JOIN ) с последующим вычислением агрегатов по завышенному количеству строк. Добавьте COUNT(*) , чтобы узнать, подхожу ли я для вашего случая. Потому COUNT что ответ может быть явно неправильным; потому AVG что он может быть слегка неправильным; потому MIN что он, вероятно, правильный. И, наконец GROUP BY , уменьшается количество строк.

Обычным способом лечения является вычисление агрегатов без JOINs (я не уверен, возможно ли это в вашем случае). Может быть, что-то вроде…

 ...
JOIN (
    SELECT  min(srs.reading) as minReading,
            avg(srs.reading) as avgReading,
            from_unixtime(FLOOR(UNIX_TIMESTAMP(srs.dateTimeStamp)/
                (30*60))*(30*60)) as timeKey
        FROM srs
        GROUP BY timeKey
      ) AS r
JOIN ...

Обычно «плохая» идея иметь date и time в отдельных столбцах. С DATETIME or TIMESTAMP легче сравнивать и т. Д. (Мне неясно, что вы делаете с вашей отдельной датой и временем.) Это также может быть проблемой производительности.

3 таблицы приводят к куче JOINing , WHERE s.id = 5289 что затрудняет передачу в srs. Возможно, вам потребуется переосмыслить схему как еще одну проблему с производительностью.

Я понимаю, что значения разные, но могли бы

     order by 
        t.startdate desc, 
        ur.starttime desc

заменяется на

     order by srs.dateTimeStamp

Это может привести к тому, что мы сможем использовать индекс.

Я удивлен, что вы используете DECIMAL(m,n) вместо FLOAT для показаний датчиков.

1. Спасибо, Рик, я совершенно забыл, что опубликовал это. В итоге я нашел решение, но в конечном итоге оно не подходит для того, на что я надеялся. После внесения некоторых изменений я смог вернуть 100 тыс. строк примерно за 400 мс, однако, если бы я захотел ограничить это только 10 строками, это все равно заняло бы 400 мс. Для отдельного времени / даты у нас есть тарифы, которые основаны на датах, а затем каждый тариф имеет переменные затраты в зависимости от дня, месяца и времени. Еще раз спасибо 🙂