表结构

/******************************************/
/*   DatabaseName = bg_scm   */
/*   TableName = demeter_order_deliver   */
/******************************************/
CREATE TABLE `demeter_order_deliver` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `order_no` varchar(64) NOT NULL COMMENT '订单编号',
  `order_id` bigint(20) NOT NULL COMMENT '订单ID',
  `order_deliver_no` varchar(64) NOT NULL COMMENT '发货单编号',
  `stock_no` varchar(32) NOT NULL COMMENT '仓库编码',
  `stock_id` bigint(20) NOT NULL COMMENT '仓库ID',
  `stock_name` varchar(10) NOT NULL COMMENT '仓库名称',
  `company_id` bigint(20) NOT NULL COMMENT '订单所属公司ID',
  `status` varchar(32) NOT NULL DEFAULT '0' COMMENT '发货单状态',
  `weight_total` decimal(8,2) DEFAULT NULL COMMENT '商品总重量',
  `volume_total` decimal(12,2) DEFAULT NULL COMMENT '商品总体积',
  `consignee_realname` varchar(32) NOT NULL COMMENT '收货人姓名',
  `consignee_telphone` varchar(32) NOT NULL COMMENT '收货人联系电话',
  `province_id` bigint(12) NOT NULL COMMENT '收货省',
  `city_id` bigint(12) NOT NULL COMMENT '收货市',
  `district_id` bigint(12) NOT NULL COMMENT '收货区',
  `street_id` binary(12) DEFAULT NULL COMMENT '街道',
  `province_name` varchar(50) DEFAULT NULL COMMENT '省名称',
  `city_name` varchar(50) DEFAULT NULL COMMENT '市名称',
  `county_name` varchar(50) DEFAULT NULL COMMENT '区域名称',
  `street_name` varchar(50) DEFAULT NULL COMMENT '街道',
  `address` varchar(250) NOT NULL COMMENT '收货地址',
  `order_remark` varchar(256) DEFAULT NULL COMMENT '订单备注',
  `statement_date` date DEFAULT NULL COMMENT '订单归属日期',
  `estimate_deliver_date` datetime DEFAULT NULL COMMENT '预计送达时间',
  `real_deliver_date` datetime DEFAULT NULL COMMENT '实际送达时间',
  `out_stock_time` datetime DEFAULT NULL COMMENT '出库时间',
  `store_id` bigint(20) NOT NULL COMMENT '店铺id',
  `store_name` varchar(100) DEFAULT NULL COMMENT '店铺名称',
  `store_type` varchar(32) DEFAULT NULL COMMENT '店铺类别',
  `business_type` varchar(32) DEFAULT NULL COMMENT '店铺业务类型',
  `sort_proportion` int(11) DEFAULT '2' COMMENT '排序比重',
  `type` varchar(32) NOT NULL COMMENT '类型（配送、调度）',
  `route` varchar(20) DEFAULT NULL COMMENT '线路',
  `route_index` varchar(20) DEFAULT NULL COMMENT '线路序号',
  `route_platenumber` varchar(10) DEFAULT NULL COMMENT '车牌',
  `sorting_route` varchar(20) DEFAULT NULL COMMENT '分拣线路',
  `sorting` int(11) DEFAULT NULL COMMENT '分拣口',
  `number` int(11) NOT NULL DEFAULT '0' COMMENT '原始数量',
  `out_number` int(11) NOT NULL DEFAULT '0' COMMENT '出库数量',
  `out_weight` decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT '出库重量',
  `out_volume` decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT '出库体积',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
  `station_range_id` bigint(20) DEFAULT NULL COMMENT '配送站点范围id',
  PRIMARY KEY (`id`),
  UNIQUE KEY `UK_ORDER_DELIVER_IDX` (`order_deliver_no`),
  KEY `NK_PLATE_NUMBER_IDX` (`estimate_deliver_date`,`route_platenumber`),
  KEY `NK_ORDER_NO_IDX` (`order_no`),
  KEY `NK_ORDER_ID_IDX` (`order_id`),
  KEY `NK_DATE_STORE_ID_IDX` (`store_id`,`estimate_deliver_date`) USING BTREE,
  KEY `idx_statement_date` (`statement_date`) USING BTREE,
  KEY `NK_ESTIMATE_COMAPNY_STOCK_IDX` (`estimate_deliver_date`,`company_id`,`stock_id`)
) ENGINE=InnoDB AUTO_INCREMENT=13816094 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='发货单明细表'
;

数据量

   SELECT count(1) FROM demeter_order_deliver

sql语句

  explain  SELECT *
FROM demeter_order_deliver
WHERE 
     `estimate_deliver_date` >= ?
    AND `estimate_deliver_date` < ? and `company_id` = ?
ORDER BY `id` DESC
LIMIT 0, 10

某天突然sql告警显示这条sql执行60多秒理论上是可以命中esttimate_deliver_date NK_ESTIMATE_COMAPNY_STOCK_IDX索引

我们手动执行以下也显示正常

    SELECT *FROM demeter_order_deliver
WHERE 
     `estimate_deliver_date` >= '2021-12-03 00:00:00.0'
    AND `estimate_deliver_date` < '2021-12-03 23:59:59.0' and `company_id` = 2
ORDER BY `id` DESC
LIMIT 0, 10

数据行

但是发现以下sql就没命中索引

    SELECT *
FROM demeter_order_deliver
WHERE 
     `estimate_deliver_date` >= '2021-12-03 00:00:00.0'
    AND `estimate_deliver_date` < '2021-12-03 23:59:59.0' and `company_id` = 3
ORDER BY `id` DESC
LIMIT 0, 10

优化成了主键索引导致全表扫描条件为3的数据行数只有1条

优化改为强制使用索引

       SELECT *
FROM demeter_order_deliver 
FORCE INDEX(NK_ESTIMATE_COMAPNY_STOCK_IDX)
WHERE 
     `estimate_deliver_date` >= '2021-12-03 00:00:00.0'
    AND `estimate_deliver_date` < '2021-12-03 23:59:59.0' and `company_id` = 3
ORDER BY `id` DESC
LIMIT 0, 10

总结

虽然不知道原因,但是不建议order by 主键与limit一起使用