如何使用多個 group by 子句優化 MYSQL 查詢

我有一個 Web 應用程序，使用者可以在其中查看性能數據。將有大約 200 到 250 個使用者。每個使用者每天將擁有大約 100,000 行數據。

它只是表，這是我的表的結構

CREATE TABLE `performance` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `user_id` int(11) NOT NULL,
 `OfferId` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
 `title` varchar(256) COLLATE utf8_unicode_ci NOT NULL,
 `CampaignName` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 `Impressions` int(11) NOT NULL,
 `Clicks` int(11) NOT NULL,
 `Ctr` double(10,2) NOT NULL,
 `AverageCpc` int(11) NOT NULL,
 `Cost` int(11) NOT NULL,
 `ConvertedClicks` int(11) NOT NULL,
 `Conversions` int(11) NOT NULL,
 `CostPerConvertedClick` int(11) NOT NULL,
 `ClickConversionRate` double(10,2) NOT NULL,
 `ConversionValue` int(11) NOT NULL,
 `Roi` double(10,2) NOT NULL,
 `AdGroupName` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 `Brand` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
 `Price` double(10,2) NOT NULL,
 `L1` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
 `L2` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
 `L3` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
 `L4` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
 `L5` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
 `Attribute0` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
 `Attribute1` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
 `Attribute2` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
 `Attribute3` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
 `Attribute4` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
 `Date` date NOT NULL,
 `DataStatus` tinyint(1) NOT NULL DEFAULT '0',
 `valueChanged` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 `attribute_changed` int(11) DEFAULT NULL ,
 `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`,`user_id`),
 KEY `performance_offerid_index` (`OfferId`),
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
/*!50100 PARTITION BY HASH (user_id)
PARTITIONS 256 */;

該表中的數據量將高達數十億行，並不斷增加。

該GROUP BY子句可以在以下列上，並且是隨機的。它將從前端控制。例如，如果使用者想在前端查看 Brand 列，那麼查詢將包含 Brand 列，否則不會。

OfferId 將始終是 group by 子句的一部分

按列列表分組

OfferId
CampaignName
AdGroupName
Brand
Attribute0
Attribute1
Attribute2
Attribute3
Attribute4
L1
L2
L3
L4
L5
Title

聚合函式將應用於這些列

Impressions
Clicks
Ctr
AverageCpc
Cost
ConvertedClicks
Conversions
CostPerConvertedClick
ClickConversionRate
ConversionValue
Roi

在每個查詢中，user_id 子句和 date 子句都是必須的。其餘的可以有多個條件AND一起編輯，因為前端界面允許使用者對數據執行過濾。

查詢花費了太多時間。我為 user_id 1 載入了 250 萬條記錄。當我執行一個簡單的 3 列分組查詢時。大約需要 250 秒。但可接受的最大時間為 5 秒。我該如何優化它？

查詢是

SELECT `OfferId` AS `OfferId`,`Attribute0` AS `Attribute0`,
SUM(`Impressions`) AS `Impressions`,SUM(`Ctr`) AS `Ctr`,
ROUND(AVG(`AverageCpc`),2) AS `AverageCpc`,SUM(`Cost`) AS `Cost`,
SUM(`ConvertedClicks`) AS `ConvertedClicks`,SUM(`Conversions`) AS `Conversions`,
ROUND(AVG(`CostPerConvertedClick`),2) AS `CostPerConvertedClick`,
ROUND(AVG(`ClickConversionRate`),2) AS `ClickConversionRate`,
ROUND(AVG(`ConversionValue`),2) AS `ConversionValue`,
ROUND(AVG(`Roi`),2) AS `Roi`,`id` AS `id` FROM `performance`
WHERE (`user_id` = 13) 
AND (`Date` BETWEEN '2016-01-08 00:00:00'
AND  '2016-02-08 23:59:59')
GROUP BY `OfferId`,`Attribute0` 
having Cost > 100
order by Cost
LIMIT 0,10;

我可以添加一個覆蓋索引，但問題是查詢是隨機生成的，就像訂單一樣，有時會包含一些列GROUP BY，有時不包含。

例如，假設我添加了一個覆蓋索引“attribute_index”

Attribute0
Attribute1
Attribute2
Attribute3
Attribute4

這些列具有此順序。但不能保證這 5 列將始終在查詢中使用。所以如果只有

Attribute0
Attribute4

在查詢中使用attribute_index 將不會被使用。

我正在使用 RDS Aurora 。我的問題有什麼解決辦法嗎？

不要使用double(10,2)它涉及兩個舍入。要麼做DECIMAL(10,2)要麼平原DOUBLE。對於貨幣值 ( price) 使用DECIMAL(...)，這樣就不會有四捨五入。 DOUBLE是 8 個字節；DECIMAL(10,2)是 5 個字節。

你能有“負面”點擊嗎？建議INT UNSIGNED。

跨列（L1，…和 ​​Attribute1，…）展開數組通常不是一個好主意，而是為它們準備另一個表。（好吧，我不知道這會對GROUP BYAttributes 的查詢產生什麼影響。）

PARTITION BY HASH因為沒有已知的提高性能的案例。

attribute_changed int(11) DEFAULT NULL– 如果那隻是一面旗幟，那就做吧TINYINT UNSIGNED NOT NULL DEFAULT '0'；這將節省 3 個字節，加上NULL.

你會有十億個不同的活動嗎？ CampaignName varchar(255)應該被規範化並替換為a MEDIUMINT UNSIGNED（3字節）以節省大量空間。對於任何其他重複很多的 varchars，同上。

如果您期望有數十億行，那麼每行擠出幾個字節可以加起來。反過來，這將減少 I/O，從而加快查詢速度。

您對 5 個屬性的覆蓋索引可能會佔用大量空間。此外，如果有十億行，它可能會減慢INSERTs到每個磁碟命中一行！在傳統驅動器上，這僅為 100/秒。您需要超過 300/秒。

既然您說使用者必須包含 Date 子句，那麼使用PARTITION BY RANGE(TO_DAYS(Date)). 目前還不清楚，但聽起來這user_id也是查詢中的要求？那時，我建議INDEX(user_id, Date) 不要分區。該綜合索引比“日期索引”要好得多。向該索引添加更多列將無濟於事。

您的範例的日期範圍為一個月加一天；這是合理的，還是只是一個傻瓜？

每個客戶一張桌子沒有幫助。

不要按使用者分區*——250*個分區有其自身的性能問題。大約 50 是分區數量的“實際”限制。

匯總表（複數）是使這個數十億行表充分執行的唯一方法。每個PRIMARY KEY匯總表的 將包括 user_id 和 date （可能截斷為day），以及其他幾個“維度”。然後幾個聚合（主要是COUNTsand SUMs）將是其他列。這些表將比您的“事實”表小得多，並且它們可以承受多個索引。請注意，AVG因為平均值的平均值在數學上不正確。而是儲存SUM和COUNT，然後將平均值計算為SUM(sums)/SUM(counts)。

更多關於數據倉庫和匯總表的資訊。

（是的，我的一些陳述確實不同意之前的評論和答案。）

您說“在每個查詢中，user_id 子句和 date 子句都是必須的”，因此索引(user_id, Date)應該對一切都有幫助。由於其他條件是可變的，這可能是您為此查詢索引所能做的最好的事情。如果常用其他列，您可以將它們添加到索引的末尾。使用目前的結構，優化器似乎唯一能做的就是分區修剪，這會有所幫助，但可能不如我建議的索引那麼多。我還會比較使用和不使用分區的性能，因為有了這個新索引，分區可能沒有多大幫助。

經過一番討論，我提出了以下修改建議。如果分區保持不變，每個分區只有一個使用者，最好的索引應該是 on (Date)。但是，如果沒有分區，最好的索引應該(user_id, Date)按照最初的建議打開。

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