记近段时间的数据库调优,百万数据统计八分钟到秒级

交通后台管理系统分片区,分地址的大数据统计SQL调优

Posted by dzt on October 29, 2019

仅为自己查阅,不做解释

以地址分组统计

select device_info.address, ifnull(COUNT(((data_group.event_image != '' or data_group.event_image != null ) AND data_group.wf_time >= '2019-12-19 00:00:00'AND data_group.wf_time <= '2019-12-19 13:40:04.353518') or null ), 0) ASsjcount, ifnull(COUNT(((data_group.event_image != '' or data_group.event_image != null) AND (data_group.image != '' or data_group.image != null) AND data_group.wf_time >= '2019-12-19 00:00:00'AND data_group.wf_time <= '2019-12-19 13:40:04.353518') or null), 0) AS qzcount, ifnull(COUNT(((data_group.event_image != '' or data_group.event_image != null) AND (data_group.sms_result like '%短信发送成功%') AND data_group.wf_time >= '2019-12-19 00:00:00' AND data_group.wf_time <= '2019-12-19 13:40:04.353518') or null), 0) AS dxcgcount, ifnull(COUNT(((data_group.event_image != '' or data_group.event_image != null) AND data_group.push_result LIKE  '%成功%' AND data_group.wf_time >= '2019-12-19 00:00:00' AND data_group.wf_time <= '2019-12-19 13:40:04.353518') or null), 0) AS rkcgcount, ifnull(COUNT(((data_group.event_image != '' or data_group.event_image != null) AND data_group.sms_result LIKE '%超时%' AND data_group.wf_time >= '2019-12-19 00:00:00' AND data_group.wf_time <= '2019-12-19 13:40:04.353518') or null), 0) AS cscount from ( select data_info_wtdatainfo.ip_id, data_info_wtdatainfo.wf_time, data_info_wtdatainfo.event_image, data_info_wtdatainfo.image, data_info_wtdatainfo.sms_image, data_info_wtdatainfo.sms_result, data_info_wtdatainfo.push_result from data_info_wtdatainfo ) data_group right join (select ip, address, area_id from devices_devicesinfo where device_type='违停') device_info on data_group.ip_id=device_info.ip group by data_group.ip_id, device_info.ip order by device_info.area_id

以片区分组统计

select areainfo.area, ifnull(COUNT(((data_group.event_image != '' or data_group.event_image != null ) AND data_group.wf_time >= '2019-12-19 00:00:00'AND data_group.wf_time <= '2019-12-19 13:38:08.611226') or null ), 0) AS sjcount, ifnull(COUNT(((data_group.event_image != '' or data_group.event_image != null) AND (data_group.image != '' or data_group.image != null) AND data_group.wf_time >= '2019-12-19 00:00:00'AND data_group.wf_time <= '2019-12-19 13:38:08.611226') or null), 0) AS qzcount, ifnull(COUNT(((data_group.event_image != '' or data_group.event_image != null) AND (data_group.sms_result like '%短信发送成功%') AND data_group.wf_time >= '2019-12-19 00:00:00' AND data_group.wf_time <= '2019-12-19 13:38:08.611226') or null), 0) AS dxcgcount, ifnull(COUNT(((data_group.event_image != '' or data_group.event_image != null) AND data_group.push_result LIKE  '%成功%' AND data_group.wf_time >= '2019-12-19 00:00:00' AND data_group.wf_time <= '2019-12-19 13:38:08.611226') or null), 0) AS rkcgcount, ifnull(COUNT(((data_group.event_image != '' or data_group.event_image != null) AND data_group.sms_result LIKE '%超时%' AND data_group.wf_time >= '2019-12-19 00:00:00' AND data_group.wf_time <= '2019-12-19 13:38:08.611226') or null), 0) AS cscount from ( select data_info_wtdatainfo.ip_id, data_info_wtdatainfo.wf_time, data_info_wtdatainfo.event_image, data_info_wtdatainfo.image, data_info_wtdatainfo.sms_image, data_info_wtdatainfo.sms_result, data_info_wtdatainfo.push_result from data_info_wtdatainfo ) data_group right join (select ip, area_id from devices_devicesinfo) device_info on data_group.ip_id=device_info.ip right join (select id, area from devices_areainfo ) areainfo on areainfo.id=device_info.area_id group by areainfo.id order by areainfo.area

以时间分组统计平均值

SELECT adcpinfo.time, AVG(adcpinfo.speed) as avg_speed, AVG(adcpinfo.direction) as avg_direction from (SELECT datainfo_adcpdatainfo.time, datainfo_adcpdatainfo.speed, datainfo_adcpdatainfo.depth, datainfo_adcpdatainfo.direction, datainfo_adcpdatainfo.distance FROM datainfo_adcpdatainfo WHERE  datainfo_adcpdatainfo.time >= '2019-12-16 13:42:42' AND datainfo_adcpdatainfo.time <= '2019-12-19 13:42:42' ) adcpinfo  RIGHT JOIN (SELECT station_id FROM devices_devicesinfo) deviceinfo on deviceinfo.station_id='2' GROUP BY adcpinfo.time ORDER BY adcpinfo.time