记一次 Mysql 查询统计问题

####前言
最近发现在公司之前写的日统计功能出现了各种问题，测试环境测试的时候发现没问题，一到线上数据量较大时，统计的数据要么不对要么就像之前所说的统计任务死锁卡住了。
这次的问题是统计结果出错的问题。
####问题分析
下面是有问题的 sql 代码：
```sql
select vehicle_id,
            count(case when sort = 1 and alarm_type=1 then alarm_type_id end) alarm1_count,
            count(case when sort = 1 and alarm_type=2 then alarm_type_id end) alarm1_count2,
            sum(case when sort = 2 and speed > 10 and alarm_type=1 then 1 else 0 end) alarm2_count,
            sum(case when sort = 2 and speed > 10 and alarm_type=2 then 1 else 0 end) alarm2_count2,
            sum(case when sort = 3 and speed > 10 and alarm_type=1 then 1 else 0 end) alarm3_count,
            sum(case when sort = 3 and speed > 10 and alarm_type=2 then 1 else 0 end) alarm3_count2,
            count(case when sort = 4 and alarm_type=1 then alarm_type_id end) alarm4_count,
            count(case when sort = 4 and alarm_type=2 then alarm_type_id end) alarm4_count2,
            count(case when sort = 5 and alarm_type=1 then alarm_type_id end) alarm5_count,
            count(case when sort = 5 and alarm_type=2 then alarm_type_id end) alarm5_count2,
            count(case when sort = 6 and alarm_type=1 then alarm_type_id end) alarm6_count,
            count(case when sort = 6 and alarm_type=2 then alarm_type_id end) alarm6_count2,
            count(case when sort = 7 and alarm_type=1 then alarm_type_id end) alarm7_count,
            count(case when sort = 7 and alarm_type=2 then alarm_type_id end) alarm7_count2,
            count(case when sort = 8 and alarm_type=1 then alarm_type_id end) alarm8_count,
            count(case when sort = 8 and alarm_type=2 then alarm_type_id end) alarm8_count2,
            count(case when sort = 9 and alarm_type=1 then alarm_type_id end) alarm9_count,
            count(case when sort = 9 and alarm_type=2 then alarm_type_id end) alarm9_count2,
            count(case when sort = 11 and alarm_type=1 then alarm_type_id end) alarm11_count,
            count(case when sort = 11 and alarm_type=2 then alarm_type_id end) alarm11_count2,
            count(case when sort = 12 and alarm_type=1 then alarm_type_id end) alarm12_count,
            count(case when sort = 12 and alarm_type=2 then alarm_type_id end) alarm12_count2,
            count(case when sort = 13 and alarm_type=1 then alarm_type_id end) alarm13_count,
            count(case when sort = 13 and alarm_type=2 then alarm_type_id end) alarm13_count2,
            sum(case when sort = 14 and speed > 10 and alarm_type=1 then 1 else 0 end) alarm14_count,
            sum(case when sort = 14 and speed > 10 and alarm_type=2 then 1 else 0 end) alarm14_count2,
            count(case when sort = 15 and alarm_type=1 then alarm_type_id end) alarm15_count,
            count(case when sort = 15 and alarm_type=2 then alarm_type_id end) alarm15_count2,
            count(case when sort = 16 and alarm_type=1 then alarm_type_id end) alarm16_count,
            count(case when sort = 16 and alarm_type=2 then alarm_type_id end) alarm16_count2,
            #{yesterday} alarm_date
        from
            (select VALUE id,sort from nd_dict where dict_type = 'alarm_type_') temp,
            nd_attend_behavior_detail abd
        where
            vehicle_id = #{vehicleId}
            and partition_key = #{partitionKey}
            and alarm_type_id = temp.id
            and date_format(machine_time, '%Y-%m-%d') = #{yesterday}
        group by vehicle_id
```
简单解释一下上面日统计的 sql：根据车辆 ID 、日期统计该车辆某天各类告警的数量，`nd_attend_behavior_detail`是告警表，`nd_dict`是字典表（存储了告警类型），不要问我为什么表设计的这么傻逼，我只是按照上面要求办事 - -~！
`告警表`与`字典表`由告警类型的 ID 关联（在`nd_attend_behavior_detail `告警表中字段是`alarm_type_id`，`nd_dict`字典表中的字段是`value`），alarm1_count-alarm16_count 以及 alarm1_count2-alarm16_count2 存储的是各类告警的一级告警与二级告警的总数，原本是想偷个懒，用字典表中的`sort`字段来进行区分不同的告警类型，所以关联了字典表来进行统计，没想到出现了 BUG，到底为什么会这样目前我也还找不到原因，上面的查询结果就是有些告警类型的统计是正确的，还有一部分告警类型的统计全部是 0 。
经过反复调试 sql，发现改成如下 sql 问题得以解决：
```sql
select vehicle_id,
            count(case when alarm_type_id=9600003 and alarm_type=1 then alarm_type_id end) alarm1_count,
            count(case when alarm_type_id=9600003 and alarm_type=2 then alarm_type_id end) alarm1_count2,
            sum(case when alarm_type_id=9600008 and speed > 10 and alarm_type=1 then 1 else 0 end) alarm2_count,
            sum(case when alarm_type_id=9600008 and speed > 10 and alarm_type=2 then 1 else 0 end) alarm2_count2,
            sum(case when alarm_type_id=9600007 and speed > 10 and alarm_type=1 then 1 else 0 end) alarm3_count,
            sum(case when alarm_type_id=9600007 and speed > 10 and alarm_type=2 then 1 else 0 end) alarm3_count2,
            count(case when alarm_type_id=9700001 and alarm_type=1 then alarm_type_id end) alarm4_count,
            count(case when alarm_type_id=9700001 and alarm_type=2 then alarm_type_id end) alarm4_count2,
            count(case when alarm_type_id=9700002 and alarm_type=1 then alarm_type_id end) alarm5_count,
            count(case when alarm_type_id=9700002 and alarm_type=2 then alarm_type_id end) alarm5_count2,
            count(case when alarm_type_id=9700006 and alarm_type=1 then alarm_type_id end) alarm6_count,
            count(case when alarm_type_id=9700006 and alarm_type=2 then alarm_type_id end) alarm6_count2,
            count(case when alarm_type_id=9001 and alarm_type=1 then alarm_type_id end) alarm7_count,
            count(case when alarm_type_id=9001 and alarm_type=2 then alarm_type_id end) alarm7_count2,
            count(case when alarm_type_id=9003 and alarm_type=1 then alarm_type_id end) alarm8_count,
            count(case when alarm_type_id=9003 and alarm_type=2 then alarm_type_id end) alarm8_count2,
            count(case when alarm_type_id=9002 and alarm_type=1 then alarm_type_id end) alarm9_count,
            count(case when alarm_type_id=9002 and alarm_type=2 then alarm_type_id end) alarm9_count2,
            count(case when alarm_type_id=9700007 and alarm_type=1 then alarm_type_id end) alarm11_count,
            count(case when alarm_type_id=9700007 and alarm_type=2 then alarm_type_id end) alarm11_count2,
            count(case when alarm_type_id=9600002 and alarm_type=1 then alarm_type_id end) alarm12_count,
            count(case when alarm_type_id=9600002 and alarm_type=2 then alarm_type_id end) alarm12_count2,
            sum(case when alarm_type_id=6000 and speed > 10 and alarm_type=1 then 1 else 0 end) alarm14_count,
            sum(case when alarm_type_id=6000 and speed > 10 and alarm_type=2 then 1 else 0 end) alarm14_count2,
            count(case when alarm_type_id=6001 and alarm_type=1 then alarm_type_id end) alarm15_count,
            count(case when alarm_type_id=6001 and alarm_type=2 then alarm_type_id end) alarm15_count2,
            count(case when alarm_type_id=9600009 and alarm_type=1 then alarm_type_id end) alarm16_count,
            count(case when alarm_type_id=9600009 and alarm_type=2 then alarm_type_id end) alarm16_count2,
            #{yesterday} alarm_date
        from
            nd_attend_behavior_detail abd
        where
            vehicle_id = #{vehicleId}
            and partition_key = #{partitionKey}
            and date_format(machine_time, '%Y-%m-%d') = #{yesterday}
        group by vehicle_id
```
上面的 sql 去掉了与字典表的关联，直接用告警表中的告警类型 ID 类进行区分不同告警进行统计。
到底为什么会出现前面那种有些数据统计到了有些统计不到是 0 的问题，最终还是搞不清楚原因，不过好在问题得以解决，由于公司用的 Mycat，有可能是 Mycat 的原因吧（被 Mycat 坑过太多次了，所以只能甩锅给它了）。

记一次 Mysql 查询统计问题

发表评论：