mysql优化问题

Question

• 900W+条数据

SELECT
    store_id AS sId,
    x,
    y,
    count( DISTINCT retrack_id ) AS v,
    count( id ) * 0.8 AS t 
FROM
    person_location_copy 
WHERE
    create_time >= '2019-11-15 00:00:00' 
    AND create_time < '2019-11-15 23:59:59' 
    AND mapping_zoo_x !=- 1 
    AND mapping_zoo_y !=- 1 
GROUP BY
    x,
    y

这条SQL会查询出4000多条数据。用时13秒。

执行EXPLAIN发现全表扫描，没有用到索引。
之前也试着加过xy索引 以及create_timemapping_zoo_xmapping_zoo_y索引。
都还是很慢。

有没有优化的方案？

---

尝试添加create_time索引：

发现没有使用索引：

加入FORCE INDEX (creattime),强制使用索引。

查询后发现强制使用索引更慢。

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for person_location_copy
-- ----------------------------
DROP TABLE IF EXISTS `person_location_copy`;
CREATE TABLE `person_location_copy`  (
  `id` int(16) NOT NULL AUTO_INCREMENT,
  `store_id` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '门店Id',
  `equipment_id` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '盒子设备Id',
  `camera_id` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '摄像头Id',
  `person_id` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '人脸库Id',
  `track_id` int(32) NULL DEFAULT NULL,
  `face_x` double(11, 2) NULL DEFAULT NULL,
  `face_y` double(11, 2) NULL DEFAULT NULL,
  `face_w` double(11, 2) NULL DEFAULT NULL,
  `face_h` double(11, 2) NULL DEFAULT NULL,
  `frame_index` bigint(127) NULL DEFAULT NULL,
  `retrack_id` int(11) NULL DEFAULT NULL,
  `mapping_zoo_x` double(11, 8) NULL DEFAULT NULL,
  `mapping_zoo_y` double(11, 8) NULL DEFAULT NULL,
  `image_id` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '图片Id',
  `create_time` datetime(0) NULL DEFAULT NULL COMMENT '记录时间',
  `update_time` timestamp(0) NULL DEFAULT NULL COMMENT '更新时间',
  `state` int(8) NULL DEFAULT NULL COMMENT '状态',
  `x` double(11, 2) NULL DEFAULT NULL,
  `y` double(11, 2) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `person_location_copy_id`(`id`) USING BTREE,
  INDEX `creattime`(`create_time`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 9451907 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

Answer 1

数据采集类应用的可视化查询需求，建议新加一张汇总表，耗时过程使用计划任务定时统计，查询的时候查汇总表

Answer 2

where条件可以改一下，create_time使用BETWEEN,然后建立个复合索引试试。create_time,mapping_zoo_x,mapping_zoo_y

Answer 3

x和y建立一个联合索引试一下