热血拳皇统计优化日志

最新推荐文章于 2025-05-12 10:21:41 发布

weixin_33775572

最新推荐文章于 2025-05-12 10:21:41 发布

阅读量74

点赞数

本文介绍了一种通过建立联合索引来优化数据库查询的方法，包括注册创建查询、在线图表查询、用户在线图表及充值查询等场景，并展示了如何通过索引来提高查询效率。

#========================================优化注册创建查询 =======================================================
#建立联合索引 I_INDEX1(statistics_date,dist_id,statistics_hour,platform_id)  NIndex1(statistics_date) 符合松散型索引扫描 最左原则  
EXPLAIN SELECT SUM(reg_num) as reg_num,SUM(create_num) as create_num,statistics_date FROM dgs_register
WHERE statistics_date >= 1397534733   AND statistics_date <= 1398830736   AND dist_id in (102) GROUP BY statistics_date ORDER BY statistics_date DESC


#=====================================优化在线图表查询=============================================
#建立联合索引Nindex3(statistics_time,online,max_online,dist_id) 符合紧凑型索引扫描 最左原则  
explain SELECT statistics_time,SUM(online) as online , SUM(max_online) as max_online FROM dgs_online WHERE statistics_time >= 1396929933 AND statistics_time <= 1398312336 GROUP BY statistics_time ORDER BY NULL
explain SELECT statistics_time,SUM(online) as online , SUM(max_online) as max_online FROM dgs_online WHERE statistics_time >= 1396929933 AND statistics_time <= 1398312336 AND dist_id in (106,107) GROUP BY statistics_time ORDER BY NULL
#以上2个查询都能用到联合索引  9W条测试数据提升扫描5W条(根据时间范围扩大而增加)


#====================================优化用户在线图表=========================================
#建立联合索引Nindex4(statistics_date,online,max_online,dist_id) 符合紧凑型索引扫描 最左原则  
explain SELECT a.statistics_date,MIN(a.online) as min_online,MAX(a.max_online) as max_online,CEIL(AVG(a.online)) as avg_online,b.max_online_time,b.min_online_time 
FROM dgs_online a LEFT JOIN dgs_online_time b ON a.statistics_date = b.statistics_date AND a.dist_id = b.dist_id 
WHERE a.statistics_date >= 1397232000 AND a.statistics_date <= 1398485136 AND a.dist_id in (106,107)  GROUP BY a.statistics_date ORDER BY a.statistics_date DESC;
#以上查询可以使用到联合索引 Nindex4


#======================================优化充值查询================================================
#建立联合索引 I_INDEX1(statistics_date,dist_id,statistics_hour,platform_id)  NIndex1(statistics_date) 符合松散索引扫描 最左原则  
explain SELECT statistics_date,SUM(recharge_amount) as recharge_amount, 
SUM(recharge_times) as recharge_times, 
SUM(recharge_members) as recharge_members,
SUM(recharge_members_new) as recharge_members_new, 
SUM(recharge_members_old) as recharge_members_old, 
dist_id
FROM dgs_recharge 
WHERE statistics_date >= 1396368000 AND statistics_date <= 1397750400  AND dist_id in (106,107) GROUP BY statistics_date  ORDER BY statistics_date DESC limit 0,20

#建立联合索引 NIndex2(statistics_date, recharge_amount, recharge_times, recharge_members_new, recharge_members_old)  符合紧凑型索引扫描 最左原则  
explain SELECT 
SUM(a.recharge_amount) as recharge_amount, 
SUM(a.recharge_times) as recharge_times, 
SUM(a.recharge_members) as recharge_members, 
SUM(a.recharge_members_new) as recharge_members_new, 
SUM(a.recharge_members_old) as recharge_members_old, 
SUM(b.person_login_num) as person_login_num 
FROM dgs_recharge a LEFT JOIN dgs_register b ON a.statistics_date = b.statistics_date AND a.dist_id = b.dist_id 
WHERE a.statistics_date >= 1396281600 AND a.statistics_date <=1397664000 AND a.dist_id in (106,107)
//8000条数据 全表扫描提升到 5000条左右


=================================================== 留存率 ======================================
#建立索引NIndex1(statistics_date) 
explain SELECT SUM(reg_num) AS reg_num, SUM(second_day_num) AS second_day_num, SUM(third_day_num) AS third_day_num, 
SUM(seven_day_num) AS seven_day_num, SUM(fifty_day_num) AS fifty_day_num, SUM(thirty_day_num) AS thirty_day_num,
 statistics_date, statistics_time FROM dgs_remain 
WHERE statistics_date >= 1396368000 AND statistics_date <= 1400169600  GROUP BY statistics_date ORDER BY statistics_date DESC limit 0,20