#========================================优化注册创建查询 ======================================================= #建立联合索引 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
