mySQL查询代码与索引优化

Question

下面是一个800万行mySQL表的查询，运行时间为24秒。

请你告诉我：

1. 如何优化SQL代码
2. 表中哪些索引最好？

提前感谢

SELECT 
    CASE
        WHEN (`Data ultima attestazione` >= '2016-12-25' AND Operatore = 'XXX') THEN 'Attestato nell''ultimo mese'
        WHEN (`Data ultima attestazione` < '2016-12-25' AND `Data ultima attestazione` != '' AND Operatore = 'XXX') THEN 'Non Attestato fino a 6 mesi'
        WHEN (`Data ultima attestazione` = '' AND Operatore = 'XXX') THEN 'Silente'
        WHEN Operatore = 'CESS' THEN 'Cessato'
        WHEN Operatore = 'FFF' THEN 'Passato a FFF'
        WHEN Operatore = 'VVV' THEN 'Passato a VVV'
        WHEN Operatore = 'WWW' THEN 'Passato a WWW'
        WHEN Operatore = 'HHH' THEN 'Passato a HHH'
        WHEN Operatore = 'PPP' THEN 'Passato a PPP'
        WHEN Operatore = 'CCC' THEN 'Passato a CCC'
        WHEN Operatore = 'TTT' THEN 'Passato a TTT'
        WHEN Operatore = 'NNN' THEN 'Passato a NNN'
        WHEN Operatore = 'LLL' THEN 'Passato a LLL'
        ELSE 'Silente'
    END As Stato,
    SUM(CASE WHEN `Tipizzazione` = 'UUU'
             THEN 1
             ELSE 0
        END
    ) As UUU,
    SUM(CASE WHEN `Tipizzazione` = 'NUU'
             THEN 1
             ELSE 0
        END
    ) As NUU,
    SUM(CASE WHEN `Tipizzazione` = 'LSC'
             THEN 1
             ELSE 0
        END
    ) As LSC,
    COUNT(NTT) As NTT
FROM RETE_ANAGRAFICA_UTENZE_SENZA_SERVIZI
WHERE TERR LIKE '%'
  AND AREA like '%' 
  AND STRUTTURA_VENDITA like '%' 
  AND CF like '%'
GROUP BY Stato
ORDER BY Totale DESC;

Answer 1

您没有筛选(LIKE '%')，所以您要求查看所有8M行。读取和处理这么多数据需要时间；24秒并非不合理。没有帮助索引。

如果有时需要过滤，那么请考虑以下几点：

• 索引可能有助于使用类似的'abc%‘
• 没有索引可以帮助LIKE '%abc'或LIKE '%abc%'。
• 您可能需要3个索引：INDEX(AREA), INDEX(STRUTTURA_VENDITA), INDEX(CF)。

如果模式可以使用汇总表，那么这将提供一种提高性能的方法。

Answer 2

当要搜索所有记录时，应包括一个参数以加快筛选速度。

WHERE (@search_terr = 0 OR TERR LIKE @search_terr_txt)
  AND (@search_area = 0 OR AREA like @search_area_txt
  AND (@search_stru = 0 OR STRUTTURA_VENDITA like @search_strut_txt
  AND (@search_cf   = 0 OR CF like @search_cf_txt
GROUP BY Stato
ORDER BY Totale DESC;