mysql - 任何人都可以帮助我提高查询效率吗？

Question

有人可以帮助我提高查询效率吗？我是 SQL 初学者，据我所知，高效的方法是使用索引或主键来让B树搜索更快，因此，我已经为rid设置了主键，为(Date, Time)设置了索引)但是，我的查询效率仍然太低，无法输出结果。

我正在尝试查找“2017-03-04”上间隔最大的传感器(sid)。这是我的代码:

select tmp4.sid as largest_ivl_sensor
from
(
    select tmp3.sid, MAX(tmp3.Dif) as max_for_each
    from
    (
        select tmp1.sid, MIN(TIME_TO_SEC(DATE_SUB(tmp2.Time, INTERVAL tmp1.Time HOUR_SECOND))) as Dif
        from
            (
            select se.sid, r.rid, r.Time
            from (select rr.rid, rr.Time from records rr where rr.Date = '2017-03-04') as r, send se
            where se.rid = r.rid
            order by se.sid
            ) as tmp1
            INNER JOIN
            (
            select se2.sid, r2.rid, r2.Time
            from (select rr2.rid, rr2.Time from records rr2 where rr2.Date = '2017-03-04') as r2, send se2
            where se2.rid = r2.rid
            order by se2.sid
            ) as tmp2 ON tmp1.sid = tmp2.sid and TIME_TO_SEC(tmp1.Time) <= TIME_TO_SEC(tmp2.Time) and tmp1.rid <> tmp2.rid
        GROUP BY tmp1.sid, tmp1.Time
    ) as tmp3
    GROUP BY tmp3.sid
) as tmp4
group by tmp4.max_for_each
having tmp4.max_for_each = MAX(tmp4.max_for_each);

这是架构:记录

+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| rid         | int(11)     | NO   | PRI | NULL    |       |
| Date        | date        | YES  | MUL | NULL    |       |
| Time        | time        | YES  |     | NULL    |       |
| Humidity    | double(5,2) | YES  |     | NULL    |       |
| Temperature | double(5,2) | YES  |     | NULL    |       |
| PM1         | int(11)     | YES  |     | NULL    |       |
| PM10        | int(11)     | YES  |     | NULL    |       |
| PM25        | int(11)     | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+

发送

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| sid   | varchar(30) | NO   |     | NULL    |       |
| rid   | int(11)     | NO   | PRI | NULL    |       |
+-------+-------------+------+-----+---------+-------+

这是一个例子:

| rid |sid |   Time   |
|   1 |  a | 00:00:00 |
|   2 |  a | 00:01:00 |
|   3 |  b | 00:05:00 |
|   4 |  b | 00:07:00 |
|   5 |  b | 00:11:00 |
|   6 |  c | 00:00:00 |
|   7 |  c | 00:03:00 |
|   8 |  c | 00:04:00 |

期望的结果:

| sid|
|   b|

因为它的最大间隔是4分钟

另一个例子:

| rid |sid |   Time   |
|   1 |  a | 00:00:00 |
|   2 |  b | 00:11:00 |
|   3 |  c | 00:04:00 |
|   4 |  b | 00:07:00 |
|   5 |  a | 00:01:00 |
|   6 |  c | 00:00:00 |
|   7 |  c | 00:03:00 |
|   8 |  b | 00:05:00 |

谢谢你帮助我。

Answer 1

您可以利用 SQL 中的变量来动态跟踪差异。此外，order by 与 limit 1 结合使用可以方便地获取具有最高值的记录:

select     sid,
           max(diff) as max_diff
from       (
                select     if(sid = @s, secs - @t, null) as diff,
                           @t := secs as secs,
                           @s := sid as sid
                from       (select     sid, 
                                       time_to_sec(time) as secs
                            from       records
                            inner join send on send.rid = records.rid
                            where      date = '2017-03-04'
                            order by   sid, time
                           ) ordered
                cross join (select @s := null, @t := null) init
           ) data
group by   sid
order by   max_diff desc
limit 1;

查看它在 rextester.com 上运行.