查询优化

Question

您是否知道如何优化此查询：

SELECT
    *
FROM
    branch
WHERE
    wid IN (
        SELECT
            MAX(wid) AS wid
        FROM
            branch
        WHERE
            og = 'XXX'
        AND st = 'YYY'
        AND guide IN ('...')
        AND uid IN ('...')
        GROUP BY
            uid,
            guide
    )
AND stp = 1;

 CREATE TABLE `branch` (
 `wid` bigint(20) unsigned NOT NULL,
 `branchid` int(11) NOT NULL,
 `stp` int(11) NOT NULL,
 `st` varchar(16) DEFAULT NULL,
 `og` varchar(32) DEFAULT NULL,
 `uid` bigint(20) unsigned NOT NULL,
 ...
 PRIMARY KEY (`wid`,`branchid`,`stp`),
 KEY `branch_guide_d` (`guide`,`d`),
 KEY `branch_st_c` (`st`,`c`),
 KEY `branch_st_m` (`st`,`m`),
 KEY `branch_uid_guide_wid` (`uid`,`guide`,`wid`),
) 

Answer 1

您可以使用以下查询

SELECT
        MAX(wid) AS wid into #Temp 
    FROM
        branch
    WHERE
        og = 'XXX'
    AND st = 'YYY'
    AND guide IN ('...')
    AND uid IN ('...')
    GROUP BY
        uid,
        guide

然后创建一个索引

Create Clustered Index Temp On  #Temp(wid)

现在使用联接

Select b.* 
from branch b 
join #Temp t on t.wid=b.wid where st = 1

Answer 2

尝试内部连接，而不是'IN‘

SELECT branch.*
FROM branch
INNER JOIN
(
        SELECT MAX(wid) AS wid
        FROM branch
        WHERE og = 'XXX'
        AND st = 'YYY'
        AND guide IN ('...')
        AND uid IN ('...')
        GROUP BY uid, guide
)tb 
ON tb.wid = branch.wid
AND st = 1;

Answer 3

这是您的查询：

SELECT b.*
FROM branch b
WHERE wid IN (SELECT MAX(wid) AS wid
              FROM branch b2
              WHERE og = 'XXX' AND st = 'YYY' AND guide IN ('...') AND
                    uid IN ('...')
              GROUP BY uid, guide
             ) AND
     stp = 1;

如果MAX(wid)是唯一的，那么我将把它写成一个JOIN

SELECT b.*
FROM branch b JOIN
     (SELECT MAX(wid) AS wid
      FROM branch b2
      WHERE og = 'XXX' AND st = 'YYY' AND guide IN ('...') AND
            uid IN ('...')
      GROUP BY uid, guide
     ) bb
     ON b.wid = bb.wid
WHERE b.stp = 1;

然后索引。想到了以下几点：branch(stp, wid)和branch(og, stp, guid, uid, wid)。取决于哪个是更有选择性的，您可能真的想要：branch(og, stp, uid, guid, wid)。