MySQL五千万大表查询优化实战

背景

DBA同事在钉钉发了两张告警截图，作为“始作俑者”的我很心虚，因为刚才是我在管理后台查询数据，结果很久都没出来，并且用多个维度查了N次

问题分析

这是当天上线的功能，完事我立马锁定SQL然后开启排查

# 原SQL，此为分页接口中count部分
SELECTCOUNT( 1 ) 
FROMshop_day_statistics aLEFT JOIN (SELECTshop_id shop_id,statistics_time,merchant_id,GROUP_CONCAT( CONCAT_ws( '-', service_id, service_name ) SEPARATOR ',' ) AS service_name,sum( profit_amount ) profit_amount FROMservice_day_statistics GROUP BYshop_id,statistics_time ) s ON s.shop_id = a.shop_idAND a.statistics_time = s.statistics_time 
WHEREa.statistics_time BETWEEN '2023-09-05 00:00:00.0' AND '2024-10-22 00:00:00.0' AND a.shop_id = 999999;# SQL解释
此SQL中有两张表：shop_day_statistics(5800W)、service_day_statistics(30多万)
下面将用A表来替代shop_day_statistics，B表来替代service_day_statistics
A表与B表的关系是一对多，因业务的需要，故之前在一条SQL上查询得出表结构：
DROP TABLE IF EXISTS `shop_day_statistics`;
CREATE TABLE `shop_day_statistics`  (`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'id',`statistics_time` date NOT NULL COMMENT '统计日期',`group_id` bigint NULL DEFAULT NULL COMMENT '代理商ID',`group_name` varchar(70) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '代理商名称',`shop_id` bigint NULL DEFAULT NULL COMMENT '门店ID',`shop_name` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '门店名称',`merchant_id` bigint NULL DEFAULT NULL COMMENT '商户ID',`merchant_name` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '商户名称',`employee_id` bigint NULL DEFAULT NULL COMMENT '员工ID',`employee_name` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '员工名称',PRIMARY KEY (`id`) USING BTREE,INDEX `idx_groupid_time`(`group_id` ASC, `statistics_time` ASC) USING BTREE,INDEX `idx_merchantid_time`(`merchant_id` ASC, `statistics_time` ASC) USING BTREE,INDEX `idx_statistics_time`(`statistics_time` ASC) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '门店日收益统计' ROW_FORMAT = DYNAMIC;DROP TABLE IF EXISTS `service_day_statistics`;
CREATE TABLE `service_day_statistics`  (`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'id',`statistics_time` date NOT NULL COMMENT '统计日期',`shop_id` bigint NULL DEFAULT NULL COMMENT '门店ID',`shop_name` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '门店名称',`merchant_id` bigint NULL DEFAULT NULL COMMENT '商户ID',`merchant_name` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '商户名称',`service_id` bigint NULL DEFAULT NULL COMMENT '服务商id',`service_name` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '服务商名称',`profit_amount` bigint NULL DEFAULT NULL COMMENT '服务商收益金额',`gmt_create` datetime NOT NULL COMMENT '创建时间',PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '服务商日收益统计' ROW_FORMAT = DYNAMIC;

SQL解析

从上图可以了解到几个点

内层的联表是ALL，也很慢，虽然只有三十多万数据，时间也去到了一秒
外层A表是ALL，全表扫描非常慢

依次改造

子查询中是没有带任何条件的，故在B表中新增两个参数：group_id,group_name，并为A B表关联的字段增加索引，在后续查询中将外层的条件(门店商户代理商ID)尽可能的加到子查询中，这样能直接命中索引，无需对B进行全表扫描
外层A表根据shop_id、statistics_time建立联合索引(shop_id必须放前面，区分度更加高)

改造后SQL及其执行计划

SELECTCOUNT( 1 ) 
FROMshop_day_statistics aLEFT JOIN (SELECTshop_id shop_id,statistics_time,merchant_id,GROUP_CONCAT( CONCAT_ws( '-', service_id, service_name ) SEPARATOR ',' ) AS service_name,sum( profit_amount ) profit_amount FROMservice_day_statistics WHEREstatistics_time BETWEEN '2023-09-05 00:00:00.0' AND '2024-10-22 00:00:00.0' AND shop_id = 99999 GROUP BYshop_id,statistics_time ) s ON s.shop_id = a.shop_id AND a.statistics_time = s.statistics_time 
WHEREa.statistics_time BETWEEN '2023-09-05 00:00:00.0' AND '2024-10-22 00:00:00.0' AND a.shop_id = 99999;