mysql中，A表有100万条数据，查询某个字符（datetime类型）为今天的数据，有什么优化方案？

Question

1.数据量太大，表中有100万条数据
2.只需要时间为今天的数据

如下sql写法不快，16秒左右
ScanTime已经添加了索引
设计到多个系统，所以不允许拆表

SELECT  * from bns_pm_scanhistory_month WHERE DATE_FORMAT(ScanTime, '%Y%m%d') ='20230206'

如下用了between之后，更慢，20秒左右

SELECT * FROM bns_pm_scanhistory_month WHERE ScanTime BETWEEN '2023-02-06 00:00:00' AND '2023-02-06 23:59:59';

如下是explain between的输出，总数据100万，符合条件的6000条

如下是explan的结果

 explain SELECT  * from bns_pm_scanhistory_month WHERE DATE_FORMAT(ScanTime, '%Y%m%d') ='20230206'

建表的sql如下

CREATE TABLE `bns_pm_scanhistory_month`  (
  `ScanSceHistory_Month_ID` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT 'ID',
  `Site_Id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '工厂主键',
  `Site_Code` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '工厂编码',
  `Operation_ID` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'BarCode表主键',
  `WorkUser_BarCode` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '条码号',
  `wifi_Code` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'wifi码',
  `cipher` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '暗码',
  `Production_Plan_Id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '工单主键',
  `Production_Plan_Code` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '工单编码（条码）',
  `Work_Cell_Id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '工作单元主键',
  `Work_Cell_Code` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '工作单元编码（工作单元编码）',
  `User_Id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '人员表主键',
  `User_Code` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '扫描人员（人员编码）',
  `ScanTime` datetime NULL DEFAULT NULL COMMENT '扫描时间',
  `Production_Line_Id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '线体编码主键',
  `Production_Line_Code` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '线体编码',
  `ID` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '生产订单主键',
  `Code` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '订单号（生产订单）',
  `Prod_Id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '产品主键',
  `Prod_Code` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '型号（产品编码）',
  `Team_Id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '班组主键',
  `Team_Code` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '班组编码',
  `Shift_Id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '班次主键',
  `Shift_Code` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '班次编码',
  `Type` int(11) NULL DEFAULT NULL COMMENT '类型（0上线，1下线）',
  `Create_By` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '创建人',
  `Create_Date` datetime NULL DEFAULT NULL COMMENT '创建时间',
  `Last_Update_By` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '最后更新人',
  `Last_Update_Date` datetime NULL DEFAULT NULL COMMENT '最后更新时间',
  `Active` smallint(6) NULL DEFAULT NULL COMMENT '可用标识',
  `Reserved1` decimal(18, 3) NULL DEFAULT NULL COMMENT '内筒测试数据是否解析1-已解析',
  `Reserved2` decimal(18, 3) NULL DEFAULT NULL COMMENT '备注',
  `Reserved3` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '备注',
  `Reserved4` varchar(256) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '备注',
  `Enterprise_Code` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '企业编码',
  `Enterprise_Id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '企业Id',
  `Work_Date` date NULL DEFAULT NULL,
  `ClientVersion` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`ScanSceHistory_Month_ID`) USING BTREE,
  INDEX `ProdId_index`(`Prod_Id`) USING BTREE,
  INDEX `WorkUserBarCode_index`(`WorkUser_BarCode`) USING BTREE,
  INDEX `ProductionLineId_index`(`Production_Plan_Id`) USING BTREE,
  INDEX `ProductionLineCode_index`(`Production_Plan_Code`) USING BTREE,
  INDEX `OperationID_index`(`Operation_ID`) USING BTREE,
  INDEX `WorkCellId_index`(`Work_Cell_Id`) USING BTREE,
  INDEX `WorkCellCode_index`(`Work_Cell_Code`) USING BTREE,
  INDEX `ScanTime_index`(`ScanTime`) USING BTREE,
  INDEX `Code_index`(`Code`) USING BTREE,
  INDEX `ProdCode_index`(`Prod_Code`) USING BTREE,
  INDEX `UserCode_index`(`User_Code`) USING BTREE,
  INDEX `IDX_WORK_DATE`(`Work_Date`) USING BTREE,
  INDEX `IDX_TYPE`(`Type`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '历史扫描记录表' ROW_FORMAT = COMPACT;

SET FOREIGN_KEY_CHECKS = 1;

Answer 1

这就不合理啊
表也不宽
数量量才100W 也很少

SELECT * from bns_pm_scanhistory_month WHERE DATE_FORMAT(ScanTime, '%Y%m%d') ='20230206' 很慢我理解

SELECT * FROM bns_pm_scanhistory_month WHERE ScanTime BETWEEN '2023-02-06 00:00:00' AND '2023-02-06 23:59:59';
这个很慢就绝了

我自己本地mock了百万级数据
实测
如下

楼主自己姿势有问题，查查索引失效有哪些情况 ，一一核对吧

或者用的不是innodb引擎？

Answer 2

两个建议

1. 执行优化表
   初步怀疑索引统计信息不准确，选择在业务低峰时执行！

   optimize table bns_pm_scanhistory_month;

2. 建立动态列

   alter table bns_pm_scanhistory_month
     add column ScanDate date GENERATED ALWAYS AS (date(ScanTime)) COMMENT '扫描日期',
     add index idx_ScanDate(ScanDate);
   
   # 查询语句变更为
   SELECT  * from bns_pm_scanhistory_month WHERE ScanDate ='2023-02-06';

Answer 3

思路1. 加索引。
思路2. 如果记录的id是递增的。先查询当天的最新一天数据。 然后在查询id>=这条数据的所有数据。
思路3. 拆表。如果当前表大多数业务只用得到当天的数据，则可以拆成历史表和当天表。更快

Answer 4

先 EXPLAIN 一下看看, 能不能给 Create_Date 加一下索引;

另外建议用BETWEEN, 你的方法1 是先把结果时间处理一遍再比较 怕是全表扫哦

Answer 5

看下BETWEEN 的explain吧。

Answer 6

直接用大于小于号试试，我这边测试可以走索引，如下

SELECT  * from bns_pm_scanhistory_month WHERE ScanTime >= '2023-02-06' and ScanTime < '2023-02-07'

Answer 7

说一说个人的笨办法哈,由于数据量大,需要统计 所以 在业务处理上 之前的设计是, 对每一天的数据做了 redis 映射, 就是:

1. 时间为key, 然后 数据唯一标识是作为值, 在统计的时候 直接通过redis 拿到唯一标识 查询 数据库的 目前 数据是 200万左右,耗时是低于10毫秒的
2. DATE_FORMAT 会导致索引失效 试试 explan BETWEEN AND 看走没走索引
3. 之前有听过 这么一个结论 你试试 查询数据条数约占总条数五分之一以下时能够使用到索引，但超过五分之一时，则使用全表扫描了.所以 查询的时候 强制走一下索引 FORCE INDEX ,explan 看一下

Answer 8

因为你用了函数date_format，它需要对每行的该字段运行这个函数，根据函数的返回值比对

Answer 9

分区试试?
https://segmentfault.com/a/11...