php - mysql大查询性能提升

Question

有人知道如何优化这个查询吗？它可以工作，但需要很长时间，以至于在负载较重时会导致服务器超时。

$screenRestrict="clientID='1' AND screenID='1'";
$lastupdate="1318515710";

INSERT INTO allvisits (clientID, screenID, hitID, entryPageID, entryPageName, xentryTime, xexitTime, pagecount, minsonsite)(
    SELECT 
        clientID, screenID, id AS hitID, pageID AS entryPageID,
        (SELECT name FROM pages WHERE id=entryPageID) AS entryPageName,
        (SELECT clicktime FROM clicks WHERE id = hitID AND isFirstClick=1 ) AS xentryTime,
        (SELECT MIN(clicktime) FROM clicks WHERE $screenRestrict AND isLastClick=1 AND clicktime > xentryTime) AS xexitTime,
        (SELECT COUNT(*) FROM clicks WHERE $screenRestrict AND clicktime BETWEEN xentryTime AND xexitTime) AS pagecount,
        (SELECT (xexitTime-xentryTime)/60) AS minsonsite
    FROM clicks WHERE $screenRestrict AND isFirstClick=1 AND clicktime>'$lastupdate'
)

非常感谢:)

更新:

感谢大家的提示。我已经向 isLastClick 添加了一个索引，并设法大大加快了速度，但在服务器负载较低的情况下仍然需要 10 秒以上的时间。我已经确定了最后一个瓶颈并将其标记在下面。有没有更好的方法来选择晚于 xentrytime 的第一个“isLastClick”记录？

SELECT clientid, 
             screenid, 
             id                                                      AS hitid, 
             pageid                                                  AS entrypageid, 
             clicktime                                               AS xentrytime, 
             (SELECT name 
                FROM   pages 
                WHERE  id = entrypageid)                               AS entrypagename, 
             (SELECT clicktime 
                FROM   clicks 
                WHERE  clicktime > xentrytime //<<removing this cuts 8.5 seconds!!
                             AND screenid = '2' 
                             AND islastclick = 1 
                             LIMIT 1)             AS xexittime, 
             (SELECT COUNT(1) 
                FROM   clicks 
                WHERE  screenid = '2' 
                             AND clicktime BETWEEN xentrytime AND xexittime) AS pagecount, 
             (SELECT ( xexittime - xentrytime ) / 60)                AS minsonsite 
FROM   clicks 
WHERE  screenid = '2' 
             AND isfirstclick = 1 
             AND clicktime > '1318961057'

Answer 1

请检查评论。

如果页面的 $screenrestrict 和页面 id 相同。尝试使用该页面 ID 连接内表

我不确定您对 xentrytime 和 xexittime 列的想法。因为，我看到使用子查询获取相同内容的 feilds。

请记住，为了调整查询，请尝试获得过滤器并限制每个子查询中选择的行数。有些过滤器是基于业务逻辑编写的，因此请考虑在编写查询时也包括这些条件，并查看解释计划的性能是否有所提高。

INSERT INTO allvisits 
            (clientid, 
             screenid, 
             hitid, 
             entrypageid, 
             entrypagename, 
             xentrytime, 
             xexittime, 
             pagecount, 
             minsonsite) 
(SELECT clientid, 
        screenid, 
        id                                                      AS hitid, 
        pageid                                                  AS entrypageid, 
        (SELECT name 
         FROM   pages 
         WHERE  id = entrypageid)                               AS entrypagename 
        , 
        clicktime                        AS xentrytime, //this should work w.r.t your code
        (SELECT MIN(clicktime)             //try to change this logic. The logic written here doesnt look good at all. and try to filter out data by joining with outer table. 
         FROM   clicks 
         WHERE  $screenrestrict 
                AND islastclick = 1 
                AND clicktime > xentrytime)                     AS xexittime, 
        (SELECT COUNT(1)   //this will give some performance improvement
         FROM   clicks 
         WHERE  $screenrestrict 
                AND clicktime BETWEEN xentrytime AND xexittime) AS pagecount, 
        (SELECT ( xexittime - xentrytime ) / 60)                AS minsonsite 
 FROM   clicks 
 WHERE  $screenrestrict 
        AND isfirstclick = 1 
        AND clicktime > '$lastupdate') 

已更新

这两个内部查询需要更多的微调

       SELECT MIN(innClick.clicktime)             
         FROM   clicks innClick1
         WHERE  innClick1.screenid =  '2' // when u put it as part of big query use  WHERE  innClick1.screenid = outClick.screenid instead of hard coding it. where outClick is alias for Click table in outside
                AND innClick1.islastclick = 1 
                AND innClick1.clicktime > innClick1.xentrytime

        SELECT COUNT(1) 
         FROM   clicks innClick2
         WHERE  innClick2.screenid =  '2' 
                AND innClick2.clicktime BETWEEN innClick2.xentrytime AND innClick2.xexittime

w.r.t解释计划

• 您可以尝试添加更多过滤器以减少内部查询中提取的行数。
• 尽量减少任何全表扫描，如果扫描是基于索引扫描(全表扫描也可以)可以暂时忽略。
• 尝试根据已编入索引的列添加过滤器。
• 尝试通过在这些子查询中进一步过滤数据来进行大量的试验和错误 -
• 尝试分别减少每个查询的时间。当你有更好的表现时，最后加入他们

我猜，如果索引了 clicktime、xentrytime 和 xexittime，将为查询带来更好的性能。您可以尝试一下，但索引仍然会减慢您的插入语句，因为每次插入时都应该更新这些额外的索引。