問題

我有以下簡單的 SELECT 語句：

select * from `web_notifications` where `user_id` = 123456 order by `created_at` limit 500;

查詢非常簡單（沒有連接語句，沒有聚合函式）。該查詢正在高級 AWS RDS Aurora 機器 (db.r5.4xlarge) 上執行。然而，查詢第一次執行0.70 秒，之後持續執行****0.13 秒。

語境

這是我的 EXPLAIN 結果（如您所見，我正在使用索引）：

| id | select_type | table             | partitions | type | possible_keys                                                            | key                                   | key_len | ref   | rows | filtered | Extra                 |
| -- | ----------- | ----------------- | ---------- | ---- | ------------------------------------------------------------------------ | ------------------------------------- | ------- | ----- | ---- | -------- | --------------------- |
|  1 | SIMPLE      | web_notifications | NULL       | ref  | web_notification_users_seen_at_idx,web_notification_users_created_at_idx | web_notification_users_created_at_idx | 4       | const |  912 |   100.00 | Using index condition |

這是創建表語句：

CREATE TABLE `web_notifications` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`text` varchar(255) NOT NULL,
`url` varchar(255) NOT NULL,
`seen_at` timestamp NULL DEFAULT NULL,
`created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`deleted_at` timestamp NULL DEFAULT NULL,
`user_id` int(10) unsigned NOT NULL,
`operation` varchar(64) DEFAULT NULL,
`start_year` smallint(5) unsigned NOT NULL,
PRIMARY KEY (`id`),
KEY `web_notification_users_seen_at_idx` (`user_id`,`seen_at`),
KEY `web_notification_users_created_at_idx` (`user_id`,`created_at`),
CONSTRAINT `fk_web_notification_users1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=2000000000171904234 DEFAULT CHARSET=utf8

該表有 1.7 億行。

我是不是做錯了什麼，或者我應該為這種高負載和高強度的表（如我的表）尋找替代儲存解決方案（可能是 noSQL web_notifications）？對於這種簡單的查詢，0.70 絕對是不可接受的執行時間。

PS我刪除了這個order by片語，查詢時間並沒有明顯下降。

為了提高該查詢的查詢性能，您可以做的最好的事情是將聚集索引更改為實際鍵，而不是自動遞增的行標識符。

現在，給定的所有記錄user大致根據插入順序分佈在表的頁面中。對於查找這些記錄，這不是最佳選擇，您必須：

1. 在二級索引上遍歷 b 樹（不是什麼大問題）
2. 閱讀所有包含該條目的頁面user，這可能不僅僅是 1 或 2 個。

因此，假設您的 key 是(user_id, created_at)1，您可以將表格設置為：

CREATE TABLE `web_notifications` 
(
 `user_id` int(10) unsigned NOT NULL,
 `text` varchar(255) NOT NULL,
 `url` varchar(255) NOT NULL,
 `seen_at` timestamp NULL DEFAULT NULL,
 `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
 `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
 `deleted_at` timestamp NULL DEFAULT NULL,
 `operation` varchar(64) DEFAULT NULL,
 `start_year` smallint(5) unsigned NOT NULL,
 CONSTRAINT PK_webnotifications PRIMARY KEY (`user_id`,`created_at`),
 /* KEY `web_notification_users_seen_at_idx` (`user_id`,`seen_at`), */ /* This index would probably not be necessary or even utilzed with the new primary key */
 CONSTRAINT `fk_web_notification_users1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) 
ENGINE=InnoDB 
DEFAULT CHARSET=utf8

這具有以下額外好處：

1. 允許引擎通過聚集索引直接訪問行
2. 每行節省 8 個字節
3. 實際唯一性
4. 可能消除額外的索引

但是，將需要額外的維護，即如果由於索引碎片2導致性能下降，則需要定期重建表。可以通過調整 來管理碎片fill_factor，但這將取決於表的成熟度、活動的分佈user_id以及您在表中保留數據的時間3。

其他需要考慮的事項：

1. 不要這樣做SELECT *- 不要在不需要的列上浪費 I/O。
2. 如果您還沒有，請將查詢包裝在儲存過程中。從安全的角度來看更好，並且記憶體計劃應該可以節省一些編譯時間。
3. 你可能不需要deleted_at. 刪除時，只需更改 for 的值updated_at並設置標誌 IsDeleted = 1。可以理解，for 的值updated_at是記錄被刪除的時間。
4. operation看起來它可能具有有限數量的值 - 考慮為每個值創建一個速記程式碼並將其儲存在參考表中。
5. 如果start_year是從另一列派生的，則無需將其持久化。如果它沒有根據web_notifications考慮將其移動到另一個表中的資訊而改變。
6. 如果您要返回大量數據，您仍然可能會受到網路 I/O 的限制，這只是在雲中的現實。但至少您將消除所有其他瓶頸。

1如果唯一性需要額外的列，只需將其添加到鍵中即可。根據您執行的查詢（以及每個使用者有多少記錄），將其保留created_at為索引中的最後一列或緊隨其後的一列可能有意義，也可能沒有意義user_id。

2或者不要像Rick James在評論中提到的那樣打擾。他有更多的 MySQL 經驗，所以我會聽從他的意見。

3在其他具有聚集索引的 DBMS 中調整填充因子可以降低表碎片的速率，但我還是要聽從 Rick 的意見。

如果您不需要使用該ORDER BY子句，那麼是的，這應該會有所幫助，另外停止使用SELECT *並僅提供您需要的列的實際列列表。它將減少需要載入的數據量，並可能導致生成更快的執行計劃。提供EXPLAIN可能也有助於包含在您的問題中。

這是什麼類型的應用程序，對於特定使用者的 500 行（尤其是記憶體後的 0.13 秒），亞秒級返回時間是不可接受的？…根據您的表格的寬度，有一個特定的點，它只是一個數量返回數據的問題（再次只選擇您需要的列）應該會有所幫助，並且以任何其他方式減少數據或增加您的預置伺服器的硬體成為您唯一的選擇。（您還可以考慮從其他並發查詢中優化伺服器的繁忙度，但如果您已經處於亞秒級，我懷疑這就是問題所在。）

如果是這種情況，這是一個數據量問題，那麼即使是NoSQL數據庫也無濟於事（而且也不是一個旨在解決性能問題的系統）。

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