MYSQL 求帮助 sql 查询优化，求指导合适的索引或sql语句

查询 park_charge_info 表
该表记录 40W+条 ，但是我在查询中却十分缓慢，需要10秒+
如下是我的sql语句：

 SELECT 
  a.parking_type,
  a.exit_type,
  SUM(a.charge) charge,
  SUM(a.realcharge) realcharge 
FROM
  ( 
     SELECT parking_type,exit_type,charge,realcharge FROM park_charge_info WHERE 
        record_time BETWEEN '2013-12-04 00:00:00' AND '2016-12-13 14:30:14'
     AND  devicecode IN ('3702020101','3702020102','3700009103','3700009104',
        '3700009105','3700009102','3700009178','3700009180','3700009179','3700098010','3700098011','3700098012','3700098013','3700098014', '3700098015',
        '3700098016','3700098017','3700098018','3700098019') 

  ) a 
GROUP BY a.exit_type,a.parking_type ;   

如下是我的这个表的sql 表及数据：

http://pan.baidu.com/s/1dE12ZM5

http://pan.baidu.com/s/1mi6oF8k

如果devicecode字段重复率很高,能否考虑使用位图索引?提高查询效率

不要联合主键
record_time 单独主键

优化之法一： 查询使用nolock
http://www.cnblogs.com/aqbyygyyga/p/3596206.html

试试这样

;WITH q (devicecode) AS (
    SELECT '3702020101' UNION ALL
    SELECT '3702020102' UNION ALL
    SELECT '3700009103' UNION ALL
    SELECT '3700009104' UNION ALL
    SELECT '3700009105' UNION ALL
    SELECT '3700009102' UNION ALL
    SELECT '3700009178' UNION ALL
    SELECT '3700009180' UNION ALL
    SELECT '3700009179' UNION ALL
    SELECT '3700098010' UNION ALL
    SELECT '3700098011' UNION ALL
    SELECT '3700098012' UNION ALL
    SELECT '3700098013' UNION ALL
    SELECT '3700098014' UNION ALL
    SELECT '3700098015' UNION ALL
    SELECT '3700098016' UNION ALL
    SELECT '3700098017' UNION ALL
    SELECT '3700098018' UNION ALL
    SELECT '3700098019'
)
    SELECT a.parking_type,
           a.exit_type,
           SUM(a.charge) charge,
           SUM(a.realcharge) realcharge
      FROM park_charge_info a,
           q
     WHERE a.record_time BETWEEN '2013-12-04 00:00:00' AND '2016-12-13 14:30:14'
       AND a.devicecode = q.devicecode
  GROUP BY a.exit_type,
           a.parking_type;

用这个试试,,,尽量别用子查询 ,,,,效率太低

 SELECT parking_type,exit_type,SUM(charge) s_charge,SUM(realcharge) s_realcharge 
FROM park_charge_info 
WHERE 
    record_time BETWEEN '2013-12-04 00:00:00' AND '2016-12-13 14:30:14'
    AND  
    devicecode IN ('3702020101','3702020102','3700009103','3700009104',
        '3700009105','3700009102','3700009178','3700009180','3700009179','3700098010','3700098011','3700098012','3700098013','3700098014', '3700098015',
        '3700098016','3700098017','3700098018','3700098019') 
GROUP BY exit_type,parking_type;

exit_type,a.parking_type 对分组列加索引