百萬條數(shù)據(jù)查詢?nèi)掌跅l件慢，如何優(yōu)化？

解決百萬條數(shù)據(jù)查詢?nèi)掌跅l件慢的問題

問題描述：

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

解答：

提供的 sql 語句確實(shí)存在一些問題。

原因分析：

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

解決方案：

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

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

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

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

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

• 確保表使用 innodb 引擎，它更適合處理大數(shù)據(jù)量。
• 優(yōu)化服務(wù)器硬件，例如增加內(nèi)存或 cpu 數(shù)量。
• 考慮將表分區(qū)，以便能夠單獨(dú)查詢每個分區(qū)。