mysql使用group by分类统计几万条以上数据处理

mysql如果group by分类统计几万条以上数据太慢，因为条件查询会走全表搜索

使用explain 可以看到自己的sql效率问题出现在哪，如：它查了7万多条数据才把结果查出来 

 

 

sql优化后：　　使用select * from ( select xxx from table_name1 group by xxx) 这样优化就快多了，先把结果集查出，再运用条件查询去过滤结果

select 
    rbw.location_name as locationName, 
    t1.worklocation_id as worklocationId,
(
select count(rrl.id) from t_b_robot_record_logrecord rrl
where  rrl.status = 1
and rrl.station_id = '1567701277018120194'
and rrl.errorcode = 1
and rrl.worklocation_id = t1.worklocation_id
) as errorcode1,
(
select count(rrl.id) from t_b_robot_record_logrecord rrl
where  rrl.status = 1
and rrl.station_id = '1567701277018120194'
and rrl.errorcode = 2
and rrl.worklocation_id = t1.worklocation_id
) as errorcode2,
(
select count(rrl.id) from t_b_robot_record_logrecord rrl
where  rrl.status = 1
and rrl.station_id = '1567701277018120194'
and rrl.errorcode = 3
and rrl.worklocation_id = t1.worklocation_id
) as errorcode3

 from (
select worklocation_id from t_b_robot_record_logrecord 
group by worklocation_id
) t1
left join t_b_robot_base_worklocation rbw on rbw.id = t1.worklocation_id
where t1.worklocation_id is not null

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

。