mysql - 分组或排序时复杂查询非常慢 - 索引建议？

Question

因此，我的任务是在 MySQL 中复制我们当前通过代码处理的功能。

下面的查询运行良好，在 40 毫秒内返回 245,000 行，但是一旦您使用组或订单触摸它，它就会花费超过 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 & POW(2, WEEKDAY(srs.dateTimeStamp)) <> 0) and 
            (ur.monthMask & 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 & POW(2, WEEKDAY(srs.dateTimeStamp)) <> 0) and 
            (ur.monthMask & 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                                  | 

Answer 1

您正在对计算字段 timeKey 进行分组和排序，而 db 在该字段上没有任何索引。

所以数据库需要在进行分组之前计算所有行，然后进行排序，没有索引无法加快计算速度。

建议:在数据库上创建一个时间字段并为该字段添加索引。