使用Count和2个表的MYSQL查询优化问题

debugcn 发表于 Dev

我有2个桌子hjuudges和hentry

hjudges表显示每个事件ID，类别，时间，日期

jeid (primary) | jcategory | jtimeofday | jedate

    101 |beginner    |AM|2020-01-01|
    101 |intermediate|AM|2020-01-01|
    101 |advanced    |AM|2020-01-01|
    102 |beginner    |AM|2020-01-05|
    102 |intermediate|AM|2020-01-05|
    102 |advanced    |AM|2020-01-05|

hentry表显示已“输入”该事件的用户。（省略了一些信息以使其更容易）

hentryid (primary)|heid | hcategory | htimeofday | hedate | huserid | hstatus | hedtime

1| 101 | beginner     |AM|2020-01-01| 5 |active | 2019-12-05 12:00:00
2| 101 | intermediate |AM|2020-01-01| 5 |active | 2019-12-05 12:00:00
3| 101 | beginner     |AM|2020-01-01| 6 |active | 2019-12-05 12:00:00
4| 101 | intermediate |AM|2020-01-01| 6 |active | 2019-12-05 12:00:00

因为事件是有限的，所以当人们进入时，我想对条目进行计数。

以下查询有效，但速度很慢。这需要9秒钟或更长时间。

select 
ei.jcategory as jcategory, 
ei.jtimeofday  as jtimeofday , 
ei.jedate as jedate,
(select count(*) from hentry  e where e.heid =ei.jeid  and e.hcategory = ei.jcategory and ei.jedate = e.hedate and ei.jtimeofday = e.htimeofday 
and (((e.hstatus = 'pending' or e.hstatus = 'npending') and e.hedtime <= DATE_SUB(NOW(), INTERVAL 500000 MICROSECOND) and e.huserid <> '5' ) OR (e.hstatus = 'active')) ) as count 
from hjudges  ei where ei.jeid  = '101' 
group by ei.jedate, ei.jtimeofday, ei.jcategory

下面是结果，但是可能要花费很长时间，具体取决于查询。

jcategory   |jtimeofday|jedate    | count    
 beginner   |AM        |2020-01-01| 2
intermediate|AM        |2020-01-01| 2
advanced    |AM        |2020-01-01| 0

我能够创建一些替代的联接查询，但它要么计数不正确，要么对于高级级别不显示0。因为条目表中没有任何条目。

需要一些帮助。

泽德福克斯

您是否可以尝试联接表，而不是像这样对子查询进行计数：

SELECT ei.jcategory AS jcategory,
       ei.jtimeofday AS jtimeofday ,
       ei.jedate AS jedate,
       count(*)
FROM hjudges ei
LEFT JOIN hentry e on e.heid =ei.jeid
    AND e.hcategory = ei.jcategory
    AND ei.jedate = e.hedate
    AND ei.jtimeofday = e.htimeofday
    AND (e.hstatus = 'active'
        OR
        (e.hstatus in ('pending', 'npending') AND e.hedtime <= DATE_SUB(NOW(), INTERVAL 500000 MICROSECOND) AND e.huserid <> '5'))
    AND ei.jeid = '101'
GROUP BY ei.jedate,
         ei.jtimeofday,
         ei.jcategory

还要在hstatus，hedtime和huserid上创建索引，然后运行查询。还有其他解决方法，例如将查询分为两个。如果查询很短，我会在注释中添加此内容。

提供MySQL提示以使用您选择的索引

当您执行explain select...类似的nbk建议时，MySQL会告诉您所使用的索引。您可以为查询提供提示，以使用与其选择的索引不同的索引。为此，请参见https://dev.mysql.com/doc/refman/5.7/en/index-hints.html。

拆分查询

您可以将查询分为2个-一个的hstatus为active，另一个的hstatus为其他。

select jcategory, jtimeofday, jedate, sum(totals)
from
(
    SELECT ei.jcategory AS jcategory,
           ei.jtimeofday AS jtimeofday ,
           ei.jedate AS jedate,
           count(*) as totals
    FROM hjudges ei
    LEFT JOIN hentry e on e.heid =ei.jeid
        AND e.hcategory = ei.jcategory
        AND ei.jedate = e.hedate
        AND ei.jtimeofday = e.htimeofday
        AND e.hstatus = 'active'
        AND ei.jeid = '101'
    GROUP BY ei.jedate,
             ei.jtimeofday,
             ei.jcategory

    union all

    SELECT ei.jcategory AS jcategory,
           ei.jtimeofday AS jtimeofday ,
           ei.jedate AS jedate,
           count(*) as totals
    FROM hjudges ei
    LEFT JOIN hentry e on e.heid =ei.jeid
        AND e.hcategory = ei.jcategory
        AND ei.jedate = e.hedate
        AND ei.jtimeofday = e.htimeofday
        AND e.hstatus in ('pending', 'npending')
        AND e.hedtime <= DATE_SUB(NOW(), INTERVAL 500000 MICROSECOND)
        AND e.huserid <> '5'
        AND ei.jeid = '101'
    GROUP BY ei.jedate,
             ei.jtimeofday,
             ei.jcategory
) x
group by jcategory, jtimeofday, jedate

这将使您可以注释掉两个查询之一，以分别研究性能，并找出哪个是瓶颈，为什么，以及为使其运行更快而设置什么样的索引。

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。