SUM 和按優化分組

我使用這個查詢：

SELECT 
    logdate, 
    SUM(bytes)/1024/1024/1024 as traffic, 
    customer 
FROM 
    `Logdata` 
WHERE 
    customer is not null AND 
    logdate >= "2016-07-01" AND 
    logdate <= "2016-07-30" 
GROUP By logdate, customer

在這張表上，目前大約有 6 個 mio 行（但行數會多 10 倍）：

CREATE TABLE `Logdata` (
 `id` bigint(20) UNSIGNED NOT NULL,
 `logdate` date DEFAULT NULL,
 `logtime` time DEFAULT NULL,
 `bytes` int(10) UNSIGNED DEFAULT NULL,
 `uri` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `customer` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
 `method` smallint(6) DEFAULT '200',
 `region` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

用這個鍵：

 ADD PRIMARY KEY (`id`),
 ADD KEY `IDX_logdate` (`logdate`),
 ADD KEY `IDX_method` (`method`),
 ADD KEY `IDX_customer` (`customer`),
 ADD KEY `IDX_customer_logdate` (`logdate`,`customer`);

這個執行計劃：

id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra
1 | SIMPLE | Logdata | NULL | index | IDX_logdate,IDX_customer,idx_customer_logdate | idx_customer_logdate | 391 | NULL | 6247535 | 25.00 | Using where

InnoDB 配置是：

InnoDB buffer pool / data size: 2.0G/1.1G
InnoDB buffer pool instances: 2

表中有 ~6mio 行，查詢需要 14 秒。隨著行數以非常快的速度增加，情況會變得更糟。

問題：

• 在這種情況下，我會從 myisam 表引擎中受益嗎？
• 我可以做些什麼來進一步優化查詢或設置？

在這種情況下，我會從 myisam 表引擎中受益嗎？

不，你不會。

我可以做些什麼來進一步優化查詢或設置？

實現查詢。

MySQL 沒有內置的方法來輕鬆地做到這一點（類似於 SQL Server 中的索引視圖或 Oracle 中的物化視圖），因此您必須付出一些努力。

像這樣創建一個表：

CREATE TABLE log_customer_date
       (
       customer BIGINT NOT NULL,
       logdate DATE NOT NULL,
       sumbytes BIGINT NOT NULL,
       countrecords BIGINT NOT NULL,
       PRIMARY KEY (customer, logdate)
       )

填充：

INSERT
INTO    log_customer_date
SELECT  customer, logdate, SUM(bytes) sumbytes, COUNT(*) countrecords
FROM    logdata
GROUP BY
       customer, logdate

並在觸發器中添加更多記錄：

INSERT
INTO    log_customer_date
VALUES  (NEW.customer, NEW.logdate, NEW.bytes, 1)
ON DUPLICATE KEY
UPDATE  sumbytes = sumbytes + VALUES(sumbytes),
       countrecords = countrecords + VALUES(countrecords)

或每隔一段時間在一個腳本中：

INSERT
INTO    log_customer_date
SELECT  customer, logdate, SUM(bytes) sumbytes, COUNT(*) countrecords
FROM    logdata
WHERE   id > :lastid
GROUP BY
       customer, logdate
ON DUPLICATE KEY
UPDATE  sumbytes = sumbytes + VALUES(sumbytes),
       countrecords = countrecords + VALUES(countrecords)

，在某處插入時記錄最高id。

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