百萬條數據查詢日期條件慢，如何優化？

解決百萬條數據查詢日期條件慢的問題

問題描述：

表中有 100 萬條數據，需要查詢其中 scantime 為當天的數據。但使用 date_format(scantime, ‘%y%m%d’) 或者 between 運算符查詢都很慢，分別耗時 16 秒和 20 秒。

解答：

提供的 sql 語句確實存在一些問題。

原因分析：

1. 索引失效：scantime 字段已創建索引，但如果索引不適用于查詢條件，則不會生效。例如，直接對 date_format(scantime, ‘%y%m%d’) 函數進行索引會導致索引失效。
2. 未使用正確的索引：索引覆蓋率指的是索引是否包含查詢所需的全部列。如果索引不覆蓋所需列，則 mysql 仍需要訪問表數據，這會降低查詢速度。

解決方案：

1. 重寫查詢：將 date_format(scantime, ‘%y%m%d’) 函數從 where 條件中移除，改為直接使用 scantime 字段。例如：

select * from bns_pm_scanhistory_month where date(scantime) = '2023-02-06'

2. 覆蓋索引：創建一個覆蓋索引，包括 scantime 和所需的所有其他列。例如：

CREATE INDEX idx_scantime_date ON bns_pm_scanhistory_month (ScanTime, WorkUser_BarCode, Site_Code, ...)

3. 避免 between 操作符：between 操作符用于查找范圍內的值，但它會強制 mysql 進行兩次全表掃描。建議使用前面提到的 date(scantime) = ‘2023-02-06’ 這樣的范圍查詢替代 between。
4. 其他考慮因素：

• 確保表使用 innodb 引擎，它更適合處理大數據量。
• 優化服務器硬件，例如增加內存或 cpu 數量。
• 考慮將表分區，以便能夠單獨查詢每個分區。