查詢性能時如何提高多個案例？

我有以下查詢，當 event_time 等於該月的某一天時計數。

我還對每個表的 id、template_name、from_email_address、event_time、event_status 都有索引。

有沒有更多的方法可以優化它？

我正在針對至少包含 3000 萬條記錄的數據庫執行此查詢，並且此查詢可能會掃描至少 300 萬條記錄。我還必須針對不同的 from_email_addresses 多次執行此查詢。

編輯以包含解釋查詢結果。

select_type        tbl type   possible_keys                   key         

PRIMARY            e   ref    PRIMARY, IDX_FROM_EMAIL_ADDRESS IDX_FROM_EMAIL_ADDRESS 
PRIMARY            d   eq_ref PRIMARY                         PRIMARY
PRIMARY            s   ref    PRIMARY, IDX_EVENT_TIME         PRIMARY
DEPENDENT SUBQUERY ms  ref    PRIMARY, IDX_EVENT_STATUS       IDX_EVENT_STATUS
ref        rows       Extra
const      3,012,345  Using where, Using index, Using temporary, Using filesort
e.id       1
e.id,func  1          Using where; Using index
const,d.id 1          Using where; Using index

   SELECT
    d.template_name, 
    COUNT(CASE WHEN DAYOFMONTH(s.event_time) = 1 THEN 1 END) AS '1',
    COUNT(CASE WHEN DAYOFMONTH(s.event_time) = 2 THEN 1 END) AS '2',
    COUNT(CASE WHEN DAYOFMONTH(s.event_time) = 3 THEN 1 END) AS '3',
    COUNT(CASE WHEN DAYOFMONTH(s.event_time) = 4 THEN 1 END) AS '4',
    COUNT(CASE WHEN DAYOFMONTH(s.event_time) = 5 THEN 1 END) AS '5',
    COUNT(CASE WHEN DAYOFMONTH(s.event_time) = 6 THEN 1 END) AS '6',
    COUNT(CASE WHEN DAYOFMONTH(s.event_time) = 7 THEN 1 END) AS '7',
    COUNT(CASE WHEN DAYOFMONTH(s.event_time) = 8 THEN 1 END) AS '8',
    COUNT(CASE WHEN DAYOFMONTH(s.event_time) = 9 THEN 1 END) AS '9',
    COUNT(CASE WHEN DAYOFMONTH(s.event_time) = 10 THEN 1 END) AS '10',
    COUNT(CASE WHEN DAYOFMONTH(s.event_time) = 11 THEN 1 END) AS '11',
    COUNT(CASE WHEN DAYOFMONTH(s.event_time) = 12 THEN 1 END) AS '12',
    COUNT(CASE WHEN DAYOFMONTH(s.event_time) = 13 THEN 1 END) AS '13',
    COUNT(CASE WHEN DAYOFMONTH(s.event_time) = 14 THEN 1 END) AS '14',
    COUNT(CASE WHEN DAYOFMONTH(s.event_time) = 15 THEN 1 END) AS '15',
    COUNT(CASE WHEN DAYOFMONTH(s.event_time) = 16 THEN 1 END) AS '16',
    COUNT(CASE WHEN DAYOFMONTH(s.event_time) = 17 THEN 1 END) AS '17',
    COUNT(CASE WHEN DAYOFMONTH(s.event_time) = 18 THEN 1 END) AS '18',
    COUNT(CASE WHEN DAYOFMONTH(s.event_time) = 19 THEN 1 END) AS '19',
    COUNT(CASE WHEN DAYOFMONTH(s.event_time) = 20 THEN 1 END) AS '20',
    COUNT(CASE WHEN DAYOFMONTH(s.event_time) = 21 THEN 1 END) AS '21',
    COUNT(CASE WHEN DAYOFMONTH(s.event_time) = 22 THEN 1 END) AS '22',
    COUNT(CASE WHEN DAYOFMONTH(s.event_time) = 23 THEN 1 END) AS '23',
    COUNT(CASE WHEN DAYOFMONTH(s.event_time) = 24 THEN 1 END) AS '24',
    COUNT(CASE WHEN DAYOFMONTH(s.event_time) = 25 THEN 1 END) AS '25',
    COUNT(CASE WHEN DAYOFMONTH(s.event_time) = 26 THEN 1 END) AS '26',
    COUNT(CASE WHEN DAYOFMONTH(s.event_time) = 27 THEN 1 END) AS '27',
    COUNT(CASE WHEN DAYOFMONTH(s.event_time) = 28 THEN 1 END) AS '28',
    COUNT(CASE WHEN DAYOFMONTH(s.event_time) = 29 THEN 1 END) AS '29',
    COUNT(CASE WHEN DAYOFMONTH(s.event_time) = 30 THEN 1 END) AS '30',
    COUNT(CASE WHEN DAYOFMONTH(s.event_time) = 31 THEN 1 END) AS '31',
    COUNT(*) AS 'Grand Total' 
FROM 
 detail d 
LEFT JOIN 
   email e on d.id = e.id 
LEFT JOIN 
   status s on s.id = d.id 
AND s.event_time = (
                       SELECT
                         MINE(ms.event_time) 
                       FROM
                         status ms 
                       WEHRE 
                         ms.id = d.id 
                       AND 
                           ms.event_status = 'SENT'
                   ) 
WHERE
   CASET(s.event_time AS DATE) BETWEEN '2020-03-01' AND '2020-03-31' 
AND
   e.from_email_address = 'abc@google.com' 
GROUP BY
 d.template_name

一種“更好”的方式：

步驟 1. 建構中間表：

SELECT
       d.template_name, 
       DAYOFMONTH(s.event_time) AS dom,
       COUNT(*) AS 'Grand Total' 
   FROM ...
   GROUP BY d.template_name, dom

步驟 2. 樞軸。（由於在其他問答中對此進行了深入討論，因此我只是添加了[pivot]標籤；去看看。）

更簡潔，雖然可能不會比CASE(...31...)

SUM(DAYOFMONTH(s.event_time) = 31)

快點

建立和維護一個匯總表。當您需要此“報告”時，與其重新掃描數百萬行，不如掃描一個小得多且索引可能更好的匯總表。 http://mysql.rjweb.org/doc.php/summarytables

這樣的表格可能實際上是我上面的“步驟 1”的結果。然而，它會每天（或不斷地）增加，而不是重建。

引用自：https://dba.stackexchange.com/questions/265497