慢sql如何优化？谁知道啊

mysql数据库

有谁知道这么些慢sql如何优化？

3万条数据下查询耗时，数据会每天增长，长得很快！

男女互动人数 280ms
select sum(interact_male) male,sum(interact_female) female from (select sum(p.behavior=2 and p.sex=1) interact_male,sum(p.behavior=2 and (p.sex=0 or p.sex=100)) interact_female,DATE_FORMAT(p.created_date,'%Y%m%d') date from aiad_passenger_flow as p left join device as d on d.mac=p.mac left join media as m on p.tag_field=m.code  where p.deleted_date is null and p.created_date between '2020-04-01 00:00:00' and '2020-04-08 23:59:59' GROUP BY p.mac, p.tag_field,date ) TEMPORARY 

路过互动折线图 300ms
select p.tag_field, m.name, d.title,p.mac ,sum(p.behavior=0) pass,sum(p.behavior=1) focus,sum(p.behavior=2) interact,DATE_FORMAT(p.created_date,'%Y%m%d') date from aiad_passenger_flow as p left join device as d on d.mac=p.mac left join media as m on p.tag_field=m.code  where p.deleted_date is null  and p.created_date between '2020-04-01 00:00:00' and '2020-04-08 23:59:59' GROUP BY p.mac, p.tag_field,date

列表 410ms
select p.tag_field, m.name, d.title,p.mac ,sum(p.behavior=0) pass,sum(p.behavior=1) focus,sum(p.behavior=2) interact,sum(p.behavior=2 and p.sex=1) interact_male,sum(p.behavior=2 and (p.sex=0 or p.sex=100)) interact_female,sum(case when p.behavior=2 then p.interact_duration else 0 end) interact_time,sum(case when p.sex=1 and p.behavior=2 then p.interact_duration else 0 end ) interact_time_male,sum(case when (p.sex=0 or p.sex=100) and p.behavior=2 then p.interact_duration else 0 end ) interact_time_female,DATE_FORMAT(p.created_date,'%Y%m%d') date from aiad_passenger_flow as p left join device as d on d.mac=p.mac left join media as m on p.tag_field=m.code  where p.deleted_date is null GROUP BY p.mac, p.tag_field,date order by date desc limit 1,10

总数 260ms
SELECT count(*) total from (select DATE_FORMAT(p.created_date,'%Y%m%d') date from aiad_passenger_flow as p left join device as d on d.mac=p.mac left join media as m on p.tag_field=m.code  where p.deleted_date is null GROUP BY p.mac, p.tag_field,date) TEMPORARY