第十九篇：Mysql两次Group by和ip转数字作比较的一次优化

业务场景：两张表，ipconfig_group表存了单位和 ip 起始段信息



visit_info表存储了访问次数，失败次数，访问流量，用户ip等信息



两张表的关系为：
    一个部门下有若干ip段，对应的下面表的visitorip，每个visitorip有访问次数，失败次数，流量
    现在，要按部门统计各部门的访问次数，失败次数，流量
通常的做法：
第一步：统计visit_info 表各个ip的次数

第二步：统计这些ip对应哪个部门，相同部门的对应字段叠加 

常规方法：如果先搞定第一步，然后程序处理第二步，那么就需要用java模拟数据库查询的Group by
方法如下： 
 
这里是只统计了流量，因为是已经做好的一个需求的逻辑，用java程序处理比较好理解
现在要统计3个字段累加，那么就只能定义一个Map<String,List<Integer>>,先遍历第一步返回的结果list
String是部门作为键，List<Integer>是后3个字段，放在一个List<Map<String,List<Integer>>>中
也可以用Map<String,Integer>存，放在List<Map<String,Map<String,Integer>>>，然后遍历最外层的list

像上图加红字标注的地方，那是一个简单的Integer相加，这里因为有3个要累加，
所以要写一个实现相同键（部门）对应的值（List<Integer>或Map<String,Integer>）累加的方法，怎么叠加很简单就不说了

可以想见，写起来非常麻烦，这就是java代替数据库Group by的方法逻辑吧

优化如下：首先肯定需要组合查询，第一步还是查一张表统计ip：
结果如下


第二步：组合查询，查询ip在符合的ip段内的部门统计结果
这里先要用到一个函数：inet_aton(ip),这个函数可以将任意一个标准的ip值：XXX.XXX.XXX.XXX转换成整数int型（最多10位）

好了所有的路铺平了，只差复合sql查出来就行了：


所有的逻辑，在数据库层面做完，程序再也不用那么麻烦，代码简化一大截
看，现在就一句代码了^^^^^^^^

性能的问题，暂时没考虑，因为截图测试的数据量很少，
但是如果是程序自己实现Group by，只是分步骤，数据库查询sql简单了，但是次数就非常多了，总的时间并不见得会短

这里有几个关键：
1、Group by用java程序实现，虽然是笨了点，但是也为以后做统计给了一种思路
2、ip段作比较用了mysql的inet_aton函数，事实上一开始我没想到有这个函数，实在是程序实现统计的逻辑太烦想图简单才去查阅
这也给了我们一个思路，上面截图最大的ip也才是10位的整型。mysql还有一个inet_ntoa函数用来将数字转为IP值

数据库优化原则：字段类型定义使用最合适（最小），最简单的数据类型
那么我们存进去的时候就可以考虑存为整型，取出来的时候用inet_ntoa()转为ip
附：a.b.c.d 的ip number是：
a * 2的11次方 + b * 2的10次方 + c * 2的8次方 + d * 2的0次方

转载于:https://www.cnblogs.com/yb38156/p/9821973.html