MySQL百萬級數據查詢優化：如何高效檢索當日數據？

mysql百萬級數據查詢優化：快速定位當日數據

在處理百萬級MySQL數據時，高效檢索特定日期的數據至關重要。本文通過一個案例分析，探討如何優化查詢語句，提升查詢效率。案例中，bns_pm_scanhistory_month表包含約一百萬條記錄，目標是快速查詢scantime字段（datetime類型）為當天（例如2023年2月6日）的數據。

最初的查詢語句使用date_format函數：

select * from bns_pm_scanhistory_month where date_format(scantime, '%y%m%d') ='20230206'

該語句執行時間約為16秒。 隨后嘗試使用BETWEEN運算符：

select * from bns_pm_scanhistory_month where scantime between '2023-02-06 00:00:00' and '2023-02-06 23:59:59';

令人費解的是，執行時間反而增加至20秒。 雖然scantime字段已建立索引：

index `scantime_index`(`scantime`) using btree,

但date_format函數的使用導致索引失效。MySQL無法直接利用索引，只能進行全表掃描，從而導致查詢緩慢。BETWEEN語句雖然直接使用scantime字段，但效率仍然低下，這可能與數據分布不均或其他索引沖突有關。

根本原因在于date_format(scantime, ‘%y%m%d’) 對索引字段進行了函數計算，阻止了索引的有效利用。對于百萬級數據，全表掃描代價巨大。因此，應避免在WHERE條件中對索引字段使用函數。

優化策略：直接使用日期范圍比較，并驗證索引是否生效?？梢允褂肊XPLaiN語句分析查詢計劃，檢查索引使用情況。如果索引失效，則需檢查索引創建是否正確，是否存在隱式類型轉換或其他條件與索引不匹配的情況。百萬級數據量不算龐大，如此低的查詢效率表明存在索引失效或其他問題。

更有效的查詢方式：

SELECT * FROM bns_pm_scanhistory_month WHERE ScanTime >= '2023-02-06 00:00:00' AND ScanTime < '2023-02-07 00:00:00';

此語句避免函數計算，充分利用scantime索引，顯著提升查詢效率。 BETWEEN語句效率低下的原因需要結合EXPLAIN結果和數據庫配置進行進一步分析。