MySQL 使用 Order by Clause 的性能非常慢

我有一個包含數百萬條目的表。下面是表結構。

CREATE TABLE `useractivity` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`userid` bigint(20) NOT NULL,
`likes` bigint(20) DEFAULT NULL,
`views` bigint(20) DEFAULT NULL,
`shares` bigint(20) DEFAULT NULL,
`totalcount` bigint(20) DEFAULT NULL,
`status` bigint(20) DEFAULT NULL,
`createdat` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`),
 KEY `userid` (`userid`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

下面是我的性能下降的查詢。

SELECT userid, 
(sum(likes)+SUM(views)+SUM(shares)+SUM(totalcount)+SUM(`status`)) as total
from useractivity
GROUP BY userid
ORDER BY total DESC
limit 0, 20;

當我在沒有ORDER BYthen 的情況下執行上面的查詢時，它給了我快速的結果集但是當使用時ORDER BY，這個查詢變得很慢，儘管我使用了分頁限制。

我能做些什麼來加快這個查詢？

您需要在表中儲存總計likes等。使用此表中的生成列來計算總數並使其編制索引：shares``users

ALTER TABLE users
ADD likes BIGINT UNSIGNED NOT NULL,
ADD views BIGINT UNSIGNED NOT NULL,
ADD shares BIGINT UNSIGNED NOT NULL,
ADD totalcount BIGINT UNSIGNED NOT NULL,
ADD status BIGINT UNSIGNED NOT NULL,
ADD total BIGINT UNSIGNED GENERATED ALWAYS AS (likes + views + shares + totalcount + status),
ADD KEY key_total (total);

下面的查詢將使用索引：

explain SELECT id, total
    FROM users
    ORDER BY total DESC
    LIMIT 0, 20;

| 編號 | 選擇類型 | 表| 隔斷 | 類型 | 可能的鍵 | 關鍵 | key_len | 參考 | 行 | 過濾 | 額外 |
| --- | ----------- | ----- | ---------- | ----- | ------------- | --------- | ------- | --- | ---- | -------- | ----------- |
| 1 | 簡單 | 使用者 | | 索引 | | key_total | 9 | | 1 | 100 | 使用索引 |

https://dev.mysql.com/doc/refman/5.7/en/create-table-secondary-indexes.html https://mariadb.com/kb/en/library/generated-columns/

嗯？桌子是乾什麼用的？有人做了一個 LIKE，所以又創建了一行，有一堆 0 和一個 1，再加上一個時間戳？

計劃 A： 增加一個表，每個使用者一行。那麼SUM()不需要。

計劃 B：建立和維護一個匯總表。這樣，您還可以按使用者獲取給定一天或一周或其他任何時間的查看次數（等）。

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