当使用 count(distinct ) 查询运行 select 时，如何加速 mysql 查询

Question

有人可以告诉我如何使用加速这个 mysql 查询吗？我知道由于 count(distinct subcontractorRef) 语法，它目前运行缓慢。还有其他选择吗？

SELECT DISTINCT (contractorsRef) AS cref, RIDGROUP AS ridg,  
    (select count(DISTINCT subcontractorRef) 
    FROM request 
    INNER JOIN request_config ON request_config.RIDGROUP = request.RIDGROUP 
    WHERE request_config.contractorsRef = outer_config.contractorsRef  
    AND currenttaxyear =2011 
    AND weekno =31) AS xxx 
FROM request_config outer_config 
WHERE currenttaxyear =2011 
AND weekno =32 
AND contractorsRef <>132 

Answer 1

尝试使用 JOIN 而不是子查询：

SELECT
    contractorsRef AS cref,
    RIDGROUP AS ridg,
    IFNULL(T1.subcontractorCount, 0) AS xxx
FROM request_config AS outer_config
LEFT JOIN
(
    SELECT
        request_config.contractorsRef,
        COUNT(DISTINCT subcontractorRef) AS subcontractorCount
    FROM request 
    INNER JOIN request_config
    ON request_config.RIDGROUP = request.RIDGROUP 
    AND currenttaxyear = 2011 
    AND weekno = 31
    GROUP BY contractorsRef
) T1
ON T1.contractorsRef = outer_config.contractorsRef 
WHERE currenttaxyear = 2011 
AND weekno = 32 
AND contractorsRef <> 132
GROUP BY outer_config.contractorsRef

Answer 2

由于我们不知道任何索引，因此猜测。但一个非常常见的错误是没有覆盖 where 子句中所有表的索引。
外部选择的示例：

FROM request_config
其中当前纳税年度=2011
AND 周号 =32
AND ContractorsRef <>132

那么你需要一个索引：
在 request_config(currenttaxyear,weekno,contractorsRef) 上创建索引 mynewIndex
该索引中列的顺序可能会产生很大的差异。所以尝试稍微改变一下它们。