-- ============================================================================
|
-- 大屏 /large-screen/* 慢查询优化 SQL 脚本
|
-- 背景: /large-screen/summary 在 MyCat 分表下按 site_id 过滤会广播扫描 50 张
|
-- t_charging_order 物理表(无索引、无时间边界、跨分片 join),导致超时。
|
-- 方案: 新建站点级预聚合表 t_large_screen_site_stat(全局表),由后端定时任务
|
-- 按"月/日/全局"切片汇总写入,大屏接口改为读该表;本脚本为 50 张分表
|
-- 补充 create_time 索引,供聚合任务按月/按日切片范围扫描使用。
|
--
|
-- 执行方式: 在 MySQL 192.168.110.74 上直接执行(不要走 MyCat)。
|
-- 幂等: 可重复执行,不会报错。
|
-- ============================================================================
|
|
-- 1. 预聚合表(全局表,不分区)
|
CREATE TABLE IF NOT EXISTS `charging_pile_order`.`t_large_screen_site_stat` (
|
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
|
`stat_type` char(1) NOT NULL COMMENT '统计粒度:D=日 M=月 G=全局',
|
`period` varchar(16) NOT NULL COMMENT 'D:yyyy-MM-dd / M:yyyy-MM / G:ALL',
|
`site_id` int NOT NULL COMMENT '站点ID, G行固定为0',
|
`order_count` bigint NOT NULL DEFAULT 0 COMMENT '订单数',
|
`user_count` bigint NOT NULL DEFAULT 0 COMMENT '充电用户数(按站点统计, 跨站充电会重复计数)',
|
`capacity` decimal(16,2) NOT NULL DEFAULT 0.00 COMMENT '充电量(kWh)',
|
`electric_fee` decimal(16,2) NOT NULL DEFAULT 0.00 COMMENT '电费',
|
`service_fee` decimal(16,2) NOT NULL DEFAULT 0.00 COMMENT '服务费(含三方0.8折算)',
|
`income` decimal(16,2) NOT NULL DEFAULT 0.00 COMMENT '收入(电费+服务费)',
|
`total_power` decimal(16,2) NOT NULL DEFAULT 0.00 COMMENT '功率合计(仅D行使用)',
|
`registered_user_count` bigint NOT NULL DEFAULT 0 COMMENT '注册用户数(仅G行使用)',
|
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
PRIMARY KEY (`id`),
|
UNIQUE KEY `uk_stat_period_site` (`stat_type`,`period`,`site_id`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='大屏站点统计预聚合表';
|
|
-- 2. 给 50 张 t_charging_order 物理分表补 create_time 索引(幂等,已存在则跳过)
|
DROP PROCEDURE IF EXISTS `charging_pile_order`.`add_large_screen_create_time_index`;
|
DELIMITER $$
|
CREATE PROCEDURE `charging_pile_order`.`add_large_screen_create_time_index`()
|
BEGIN
|
DECLARE i INT DEFAULT 0;
|
DECLARE tbl_name VARCHAR(64);
|
DECLARE idx_exists INT;
|
WHILE i < 50 DO
|
SET tbl_name = CONCAT('t_charging_order_', i);
|
SELECT COUNT(*) INTO idx_exists
|
FROM information_schema.statistics
|
WHERE table_schema = 'charging_pile_order'
|
AND table_name = tbl_name
|
AND index_name = 'idx_create_time';
|
IF idx_exists = 0 THEN
|
SET @ddl = CONCAT('ALTER TABLE `charging_pile_order`.`', tbl_name,
|
'` ADD KEY idx_create_time (create_time)');
|
PREPARE stmt FROM @ddl;
|
EXECUTE stmt;
|
DEALLOCATE PREPARE stmt;
|
END IF;
|
SET i = i + 1;
|
END WHILE;
|
END$$
|
DELIMITER ;
|
CALL `charging_pile_order`.`add_large_screen_create_time_index`();
|
DROP PROCEDURE `charging_pile_order`.`add_large_screen_create_time_index`;
|
|
-- 3. 索引结果核对(应返回 50 行)
|
SELECT table_name, index_name
|
FROM information_schema.statistics
|
WHERE table_schema = 'charging_pile_order'
|
AND table_name LIKE 't_charging_order\_%'
|
AND index_name = 'idx_create_time'
|
ORDER BY table_name;
|
|
-- 1.2 用户-站点对表(充电用户数精确去重)
|
-- 背景: 预聚合表的 user_count 按站点求和, 跨站充电用户会重复计数。
|
-- 本表记录"每个站点出现过哪些用户"(所有订单口径, 与工作台 countAllUserData 一致),
|
-- 接口对选中站点做 COUNT(DISTINCT app_user_id) 精确去重。
|
CREATE TABLE IF NOT EXISTS `charging_pile_order`.`t_large_screen_user_site` (
|
`site_id` int NOT NULL COMMENT '站点ID',
|
`app_user_id` bigint NOT NULL COMMENT '用户ID',
|
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
PRIMARY KEY (`site_id`,`app_user_id`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='大屏充电用户-站点对(精确去重)';
|
|
-- ============================================================================
|
-- 上线后数据一致性对比(只读,可在线执行;对比新老口径差值,应≈0)
|
-- 老口径: 现接口 getLargeScreenSummary 的原始 SQL(挑少量站点对比,避免生产压力)
|
-- ============================================================================
|
-- SELECT COUNT(DISTINCT charging_order.id) AS validOrderCount,
|
-- COALESCE(ROUND(SUM(accounting.charging_capacity), 2), 0) AS totalChargingCapacity,
|
-- COALESCE(ROUND(SUM(accounting.period_electric_price), 2), 0) AS electricityFee,
|
-- COALESCE(ROUND(SUM(CASE WHEN charging_order.order_source IN (0,1)
|
-- THEN accounting.period_service_price ELSE accounting.period_service_price * 0.8 END), 2), 0) AS serviceFee
|
-- FROM charging_pile_order.t_charging_order charging_order
|
-- LEFT JOIN charging_pile_order.t_charging_order_accounting_strategy accounting
|
-- ON accounting.charging_order_id = charging_order.id
|
-- WHERE charging_order.del_flag = 0
|
-- AND charging_order.status = 5
|
-- AND charging_order.recharge_payment_status = 2
|
-- AND charging_order.site_id IN (101, 102);
|
--
|
-- SELECT SUM(order_count) AS validOrderCount,
|
-- SUM(capacity) AS totalChargingCapacity,
|
-- SUM(electric_fee) AS electricityFee,
|
-- SUM(service_fee) AS serviceFee
|
-- FROM charging_pile_order.t_large_screen_site_stat
|
-- WHERE stat_type = 'M' AND site_id IN (101, 102);
|
