解决百万条数据查询日期条件慢的问题
问题描述：
表中有 100 万条数据，需要查询其中 scantime 为当天的数据。但使用 date_format(scantime, '%y%m%d') 或者 between 运算符查询都很慢，分别耗时 16 秒和 20 秒。
解答：
提供的 sql 语句确实存在一些问题。
原因分析：
- 索引失效：scantime 字段已创建索引，但如果索引不适用于查询条件，则不会生效。例如，直接对 date_format(scantime, '%y%m%d') 函数进行索引会导致索引失效。
- 未使用正确的索引：索引覆盖率指的是索引是否包含查询所需的全部列。如果索引不覆盖所需列，则 mysql 仍需要访问表数据，这会降低查询速度。
解决方案：
- 重写查询：将 date_format(scantime, '%y%m%d') 函数从 where 条件中移除，改为直接使用 scantime 字段。例如：
select * from bns_pm_scanhistory_month where date(scantime) = '2023-02-06'
- 覆盖索引：创建一个覆盖索引，包括 scantime 和所需的所有其他列。例如：
CREATE INDEX idx_scantime_date ON bns_pm_scanhistory_month (ScanTime, WorkUser_BarCode, Site_Code, ...)
- 避免 between 操作符：between 操作符用于查找范围内的值，但它会强制 mysql 进行两次全表扫描。建议使用前面提到的 date(scantime) = '2023-02-06' 这样的范围查询替代 between。
- 其他考虑因素：
- 确保表使用 innodb 引擎，它更适合处理大数据量。
- 优化服务器硬件，例如增加内存或 cpu 数量。
- 考虑将表分区，以便能够单独查询每个分区。
