我有一個 MySQL 表，里面有大約 2m 行。我正在嘗試運行以下查詢，每次需要超過 5 秒才能獲得結果。我在created_at列上有一個索引。下面是EXPLAIN輸出。
這是預期的嗎？
提前致謝。
SELECT
DATE(created_at) AS grouped_date,
HOUR(created_at) AS grouped_hour,
count(*) AS requests
FROM
`advert_requests`
WHERE
DATE(created_at) BETWEEN '2022-09-09' AND '2022-09-12'
GROUP BY
grouped_date,
grouped_hour
uj5u.com熱心網友回復：
EXPLAIN 顯示type: index哪個是索引掃描。也就是說，它正在使用索引，但它遍歷索引中的每個條目，就像表掃描對表中的行所做的那樣。這是支持的，rows: 2861816它告訴您優化器對它將檢查的索引條目數量的估計（這是一個粗略的數字）。這比只檢查與條件匹配的行要昂貴得多，這是我們從索引中尋找的好處。
那么這是為什么呢？
當您在搜索中的索引列上使用任何函式時，如下所示：
WHERE
DATE(created_at) BETWEEN '2022-09-09' AND '2022-09-12'
它破壞了索引減少檢查行數的好處。
MySQL 的優化器對函式的結果沒有任何智能，因此它無法推斷回傳值的順序將與索引的順序相同。因此它不能使用索引排序的事實來縮小搜索范圍。您和我都知道DATE(created_at)與 的順序相同是很自然的created_at，但查詢優化器不知道這一點。還有其他函式MONTH(created_at)，比如結果肯定不是按排序順序排列的，MySQL 的優化器不會嘗試知道哪個函式的結果是可靠排序的。
要修復您的查詢，您可以嘗試以下兩種方法之一：
使用運算式索引。這是 MySQL 8.0 中的一個新特性：
ALTER TABLE `advert_requests` ADD INDEX ((DATE(created_at)))
注意多余的一對括號。這些是定義運算式索引時所必需的。索引條目是該函式或運算式的結果，而不是列的原始值。
如果您在查詢中使用相同的運算式，優化器會識別并使用索引。
mysql> explain SELECT DATE(created_at) AS grouped_date, HOUR(created_at) AS grouped_hour, count(*) AS requests FROM `advert_requests` WHERE DATE(created_at) BETWEEN '2022-09-09' AND '2022-09-12' GROUP BY grouped_date, grouped_hour\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: advert_requests
partitions: NULL
type: range <-- much better than 'index'
possible_keys: functional_index
key: functional_index
key_len: 4
ref: NULL
rows: 1
filtered: 100.00
Extra: Using where; Using temporary
如果使用 MySQL 5.7，則不能直接使用運算式索引，但可以使用虛擬列并在虛擬列上定義索引：
ALTER TABLE advert_requests
ADD COLUMN created_at_date DATE AS (DATE(created_at)),
ADD INDEX (created_at_date);
優化器識別運算式的技巧仍然有效。
如果您使用低于 5.7 的 MySQL 版本，則無論如何都應該升級。MySQL 5.6 和更早的版本現在已經過時了，它們存在安全風險。
您可以做的第二件事是重構查詢，使created_at列不在函式內。
WHERE
created_at >= '2022-09-09' AND created_at < '2022-09-13'
將日期時間與日期值進行比較時，日期值隱含在 00:00:00.000 時間。要包括直到 2022-09-12 23:59:59.999 的每一秒，使用< '2022-09-13'.
對此的解釋表明它使用了 上的現有索引created_at。
mysql> explain SELECT DATE(created_at) AS grouped_date, HOUR(created_at) AS grouped_hour, count(*) AS requests FROM `advert_requests` WHERE created_at >= '2022-09-09' AND created_at < '2022-09-13' GROUP BY grouped_date, grouped_hour\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: advert_requests
partitions: NULL
type: range <-- not 'index'
possible_keys: created_at
key: created_at
key_len: 6
ref: NULL
rows: 1
filtered: 100.00
Extra: Using index condition; Using temporary
此解決方案適用于舊版本的 MySQL 以及 5.7 和 8.0。
uj5u.com熱心網友回復：
使用explain analysis并檢查是否Index range scan。如果不點擊此鏈接：
https ://dev.mysql.com/doc/refman/8.0/en/range-optimization.html
（請注意，如果表中的大多數時間戳屬于選定的日期范圍。據我所知，在這種情況下優化并非易事）
uj5u.com熱心網友回復：
如果我理解EXPLAIN正確，它可以使用索引來實作WHERE過濾。但這將回傳 280 萬行，然后必須按日期和小時分組，這是一個緩慢的程序。
您可以通過為日期和小時創建虛擬列來改進它，并為它們建立索引。
ALTER TABLE advert_requests
ADD COLUMN created_date DATE AS (DATE(created_at)), ADD column created_hour INT AS (HOUR(created_at)), ADD INDEX (created_date, created_hour);
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/507679.html