背景:项目中有个小需求需要统计某个地区在某个时间段的注册用户数,已经提供该地区对应的ip段,注册数据表中存有注册用户ip。
初期的想法是:完善ip段内的所有ip数,形成一个列表,然后用注册用户ip字段进行in查询。
实施的时候出了以下问题:
1.读取ip段文件,完善150多个ip段时出现了1000多W的ip信息。完善ip段信息,主要是通过 ip2long() 函数和 long2ip() 函数(先将ip段的起始ip和结束ip均转为长整形,然后完善长整形之间的值,并每个都转回ip信息)。
2.注册表的数据量在5000W左右,in 查询ip列表,测试1000个ip查时耗时到60s
改善后的想法:考虑到php有转换ip和long类型的参数,果断百度了下mysql是否有。幸运的是,mysql足够强大也有对应的 inet_aton() 和 inet_ntoa() 函数
改善后的做法是直接在mysql端进行转换比较,如下:
select count(userId) as user_num,date_format(FROM_UNIXTIME(`addtime`),'%y%m') sdate
from t_user_attr
where addtime >= unix_timestamp("2014-09-01 00:00:00")
AND addtime < unix_timestamp("2014-10-31 23:59:59")
AND inet_aton(regIp) >= inet_aton("1.34.0.0") AND inet_aton(regIp)<=inet_aton("1.35.255.255")
运行的效果是:
测试ip段包含ip数达近20W个时,查询用户id用了23 s左右 (测试ip段为 60.248.0.0|60.251.255.255)
总结:当数据量级大了起来后,以前体现不出的性能就会很明显。
上面的sql还可以优化下,将条件里时间的比较在php端完成(时间字符串转时间戳),不在mysql端转换。