MYSQL 使用聚合器優化分組查詢

SELECT  id_num
   ,   sum(expired) as expired
   ,   max(`date`) as max_date
FROM accounts
where `date`<=20170505
group by id_num;

accounts 表在 id_num 上有一個複合索引，date並且有大約 1 億行。這個查詢看起來很基本，但它需要很長時間，我不知道如何分解它以加快速度。我考慮過首先為 DISTINCT id_num（~3mil 行）創建一個輔助表，但是我不確定如何在date不將輔助函式加入帳戶表的情況下獲取 sum(expired) 和 max() 列並執行相同的操作原始查詢。

CREATE TABLE `accounts` (
   `id_num` int(11) NOT NULL,
   `date` date NOT NULL,
  `time` datetime NOT NULL,
  `price` decimal(10,4) NOT NULL,
  `cost` decimal(10,4) NOT NULL,
  `time_slices` int(11) NOT NULL,
  `sub_expired` tinyint(1) NOT NULL,
  PRIMARY KEY (`id_num`,`date`),
  KEY `date` (`date`),
  CONSTRAINT `accounts_ibfk_1` FOREIGN KEY (`id_num`) REFERENCES `cust` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

日期範圍可以追溯到大約 18 個月，並且數據在所有日期中分佈得相當均勻。這些行是按時間順序插入的，很少有更新/刪除。

INDEX(date,             -- to satisfy the WHERE; must be first
     id_num, expired)  -- to complete "covering"; must be last (either order)

因為是“覆蓋”，所以會完全在索引的BTree中執行。

通過首先放置date，它會查看最少的行數。

這是一個相當不尋常的查詢，因此不要期望這些原則會延續到其他查詢。

即使您對此感到有些滿意，我也可能會告訴您如何使其速度提高 10 倍。但首先，我需要查看SHOW CREATE TABLE並提供表格中日期範圍和日期分佈的一些線索。此外，是否按時間順序插入了行？有沒有UPDATEs或DELETEs？

匯總表？

也許該表可以匯總為一個匯總表，每個 id_num 每月有一行。然後，原始查詢將從該表中以 30 倍的速度完成大部分工作，並且仍然從巨大的表中完成一些工作。匯總表將逐漸增加。

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