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

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;