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

6个回答

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

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;

不要联合主键
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;
Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问