- 现状
征对mysql查询ip库性能贫瘠,先考虑将ip库缓存redis,进行性能调优。
- 开篇
参考:
https://www.cnblogs.com/weiguang3100/p/4342233.html
https://www.cnblogs.com/focus-lei/p/9466814.html
https://stackoverflow.com/questions/9989023/store-ip-ranges-in-redis (首推)
http://bylijinnan.iteye.com/blog/2180983
https://www.cnblogs.com/rookie404/p/5875948.html
- 应用
5min上报500w数据。Redis的如果在1KB内,理论上1s内能响应10w+命令,实际受限于网络,带宽等等,我们的环境达不到。
@Override
@Transactional(rollbackFor = Exception.class)
public void addRefreshDataDb(String url,boolean proxyFlag) throws IOException {
List<IpLibrary> ipLibrarys = new ArrayList<>();
List<CountryCode> countryCodes = new ArrayList<>();
List<AreaCode> areaCodes = new ArrayList<>();
List<IspCode> ispCodes = new ArrayList<>();
List<PCityCode> cityCodes = new ArrayList<>();
String proxyIp = CustomizedPropertyConfigurer.getCtxProp("socke.ip");
Integer proxyPort = Integer.parseInt(CustomizedPropertyConfigurer.getCtxProp("socke.port"));
String result = HttpUtil.sendGet(url, proxyFlag, proxyIp, proxyPort);
if(null == result){
log.error("-------- addRefreshDataDb get failure -------");
return;
}
String[] arrays = result.split("\n");
/**
* 更新redis缓存
*/
if (Constant.IP_LIBRARY.equals(url)) {
RedisUtil.elementRestoreToRedis(arrays, Constant.IP_2_CODE);
} else if (Constant.COUNTRY_CODE.equals(url)) {
RedisUtil.elementRestoreToRedis(arrays, Constant.CODE_2_COUNTRY);
} else if (Constant.AREA_CODE.equals(url)) {
RedisUtil.elementRestoreToRedis(arrays, Constant.CODE_2_AREA);
} else if (Constant.ISP_CODE.equals(url)) {
RedisUtil.elementRestoreToRedis(arrays, Constant.CODE_2_ISP);
}
for (String array : arrays){
if (Constant.IP_LIBRARY.equals(url)) {
/**
* 获取ip库信息
*/
IpLibrary ipLibrary = fillIpAddressInfo(array);
ipLibrarys.add(ipLibrary);
} else if (Constant.COUNTRY_CODE.equals(url)) {
/**
* 获取国家信息
*/
CountryCode countryCode = fillCountryCode(array);
countryCodes.add(countryCode);
} else if (Constant.AREA_CODE.equals(url)) {
/**
* 获取地区信息
*/
AreaCode areaCode = fillAreaCode(array);
areaCodes.add(areaCode);
} else if (Constant.ISP_CODE.equals(url)) {
/**
* 获取运营商信息
*/
IspCode ispCode = fillIspCode(array);
ispCodes.add(ispCode);
RedisUtil.elementRestoreToRedis(arrays, Constant.CODE_2_ISP);
} else if (Constant.CITY_CODE.equals(url)) {
/**
* 获取城市信息
*/
PCityCode cityCode = fillCityCode(array);
cityCodes.add(cityCode);
}
}
if (Constant.IP_LIBRARY.equals(url)) {
ipLibraryService.deleteIpLibrary();
} else if (Constant.COUNTRY_CODE.equals(url)) {
countryCodeService.deleteCountryCode();
} else if (Constant.AREA_CODE.equals(url)) {
areaCodeService.deleteAreaCode();
} else if (Constant.ISP_CODE.equals(url)) {
ispCodeService.deleteIspCode();
} else if (Constant.CITY_CODE.equals(url)) {
cityCodeService.deleteCityCode();
}
if (!ipLibrarys.isEmpty()) {
/**
* 刷库ip库
*/
int size = ipLibrarys.size();
int time = (size % THREETY_THOUSAND == 0) ? (size / THREETY_THOUSAND) : (size / THREETY_THOUSAND) + 1;
for (int i = 0; i < time ; i++){
List<IpLibrary> tempIpLibrarys;
if(i == (time - 1)){
tempIpLibrarys = ipLibrarys.subList(i * THREETY_THOUSAND , size);
}else{
tempIpLibrarys = ipLibrarys.subList(i * THREETY_THOUSAND , (i + 1) * THREETY_THOUSAND);
}
ipLibraryService.addIpLibrary(tempIpLibrarys);
}
}
if (!countryCodes.isEmpty()) {
/**
* 刷库countrycode库
*/
countryCodeService.addCountryCode(countryCodes);
}
if (!areaCodes.isEmpty()) {
/**
* 刷库areacode库
*/
areaCodeService.addAreaCode(areaCodes);
}
if (!ispCodes.isEmpty()) {
/**
* 刷库ispcode库
*/
ispCodeService.addIspCode(ispCodes);
}
if (!cityCodes.isEmpty()) {
/**
* 刷库citycode库
*/
cityCodeService.addCityCode(cityCodes);
}
}
package com.**.pcdn.common.util;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
import lombok.extern.slf4j.Slf4j;
import redis.clients.jedis.Jedis;
import redis.clients.jedis.Pipeline;
import redis.clients.jedis.Response;
@Slf4j
public class RedisUtil {
protected static JedisClientImpl jedisClient;
static {
jedisClient = new JedisClientImpl("common-sedis.xml");
}
private RedisUtil() {}
/**
* 将序列化对象值value关联到key, 如果key已经持有其他值,SET就覆写旧值,无视类型 时间复杂度O(1)
*
* @param key
* @param value
* @return
*/
public static String set(final String key, final String value) {
try {
return jedisClient.execute(jedis -> jedis.set(key, value));
} catch (Exception e) {
log.error("set key : {}, value : {} error!", key, value, e);
}
return null;
}
/**
* 返回key所关联的序列化对象。如果key不存在则返回null。 时间复杂度O(1)
*
* @param key
* @return
*/
public static String get(final String key) {
try {
return jedisClient.execute(jedis -> jedis.get(key));
} catch (Exception e) {
log.error("get key: {} from redis error", key, e);
}
return null;
}
/**
* 将哈希表key中的域field的值设为value。如果key不存在,一个新的哈希表被创建并进行HSET操作。 果域field已经存在于哈希表中,旧值将被覆盖。 时间复杂度O(1)
*
* @param key field value
* @return 如果field是哈希表中的一个新建域,并且值设置成功,返回1。 如果哈希表中域field已经存在且旧值已被新值覆盖,返回0。
*/
public static Long hset(final String key, final String field, final String value) {
try {
return jedisClient.execute(new JedisAction<Long>() {
@Override
public Long doAction(Jedis jedis) {
return jedis.hset(key, field, value);
}
});
} catch (Exception e) {
log.error("hset key : {} error!", key, e);
}
return 0L;
}
/**
* 返回哈希表key中给定域field的值。 时间复杂度O(1)
*
* @param key field
* @return 给定域的值。 当给定域不存在或是给定key不存在时,返回null。
*/
public static String hget(final String key, final String field) {
try {
return jedisClient.execute(new JedisAction<String>() {
@Override
public String doAction(Jedis jedis) {
return jedis.hget(key, field);
}
});
} catch (Exception e) {
log.error("hget key : {} error!", key, e);
}
return null;
}
/**
* 同时将多个域-值对设置到哈希表key中。如果key不存在,一个空哈希表被创建并执行HMSET操作。 时间复杂度O(N),N为域-值对的数量。
*
* @param key hash
* @return
*/
public static String hmset(final String key, final Map<String, String> hash) {
try {
return jedisClient.execute(new JedisAction<String>() {
@Override
public String doAction(Jedis jedis) {
return jedis.hmset(key, hash);
}
});
} catch (Exception e) {
log.error("hmset key : {} error!", key, e);
}
return null;
}
/**
*/
public static Map<String, String> hgetAll(final String key) {
try {
return jedisClient.execute(new JedisAction<Map<String, String>>() {
@Override
public Map<String, String> doAction(Jedis jedis) {
return jedis.hgetAll(key);
}
});
} catch (Exception e) {
log.error("hgetAll key : {} error!", key, e);
}
return null;
}
/**
* 根据正则表达式获取匹配所有的key值
*
* @param keyPattern
* @return
*/
public static Set<String> keys(String keyPattern) {
try {
return jedisClient.execute(new JedisAction<Set<String>>() {
@Override
public Set<String> doAction(Jedis jedis) {
return jedis.keys(keyPattern);
}
});
} catch (Exception e) {
log.error("get keys : {} error!", keyPattern, e);
}
return new HashSet<>();
}
/**
* 获取所有key信息
*
* @param keySet
* @return
*/
public static List<String> mget(Set<String> keySet) {
if (keySet == null || keySet.isEmpty()) {
return new ArrayList<>();
}
try {
return jedisClient.execute(new JedisAction<List<String>>() {
@Override
public List<String> doAction(Jedis jedis) {
String[] keys = new String[keySet.size()];
keySet.toArray(keys);
return jedis.mget(keys);
}
});
} catch (Exception e) {
log.error("mget : {} error!", keySet, e);
}
return new ArrayList<>();
}
/**
* 元素存储Redis
* @param list
* @param key
*/
public static void elementRestoreToRedis(String[] list, String key){
jedisClient.execute(new JedisAction<String>() {
@Override
public String doAction(Jedis jedis) {
Pipeline pipeline = jedis.pipelined();
for (int i = 0; i < list.length ; i++){
String[] arrays = list[i].split("\t");
if(arrays.length == 6){
Map<String,String> hash = new HashMap<>();
hash.put("beginIp", arrays[0]);
hash.put("endIp", arrays[1]);
hash.put("countryCode", arrays[2]);
hash.put("provinceCode", arrays[3]);
hash.put("cityCode", arrays[4]);
hash.put("ispCode", arrays[5]);
pipeline.hmset(key + i, hash);
pipeline.zadd(key + "index", Long.parseLong(arrays[1]), String.valueOf(i));
}
if (arrays.length == 2) {
pipeline.hset(key, arrays[0], arrays[1]);
}
if (arrays.length == 3) {
pipeline.hset(key, arrays[0] + "_" + arrays[1], arrays[2]);
}
}
if(Constant.IP_2_CODE.equals(key)){
pipeline.zrange(key + "index", 0, -1);
}
pipeline.syncAndReturnAll();
return null;
}
});
}
/**
* 获取ip所属索引
* @param ip
*/
public static String getIpOwnedIndex(long ip){
return jedisClient.execute(new JedisAction<String>() {
@Override
public String doAction(Jedis jedis) {
Pipeline pipeline = jedis.pipelined();
Response<Set<String>> results = pipeline.zrangeByScore(Constant.IP_2_CODE + "index", ip, Long.MAX_VALUE, 0, 1);
pipeline.sync();
Set<String> result = results.get();
if(result.isEmpty())
return null;
String code = result.iterator().next();
return code;
}
});
}
/**
* hmget
* @param key
* @return
*/
public static String hmget(String key,String... fields){
return jedisClient.execute(new JedisAction<String>() {
@Override
public String doAction(Jedis jedis) {
List<String> result = jedis.hmget(key, fields);
if (result.isEmpty() || null == result.get(0))
return null;
StringBuilder builder = new StringBuilder();
for (String content : result){
builder.append("_").append(content);
}
return builder.substring(1);
}
});
}
}
/**
* 通过缓存查询IpLibraryVO
* @param ip
* @return
*/
public IpLibraryVO getIpLibraryVOByIp(Long ip){
IpLibraryVO ipLibraryVO = null;
String index = RedisUtil.getIpOwnedIndex(ip);
if(null == index){
return ipLibraryVO;
}
String value = RedisUtil.hmget(Constant.IP_2_CODE + index, "beginIp", "endIp", "countryCode", "provinceCode", "cityCode", "ispCode");
if (null == value){
return ipLibraryVO;
}
ipLibraryVO = new IpLibraryVO();
String[] arrays = value.split("_");
ipLibraryVO.setCountryCode(Long.parseLong(arrays[2]));
ipLibraryVO.setProvinceCode(Long.parseLong(arrays[3]));
ipLibraryVO.setIspCode(Long.parseLong(arrays[5]));
String isp = RedisUtil.hget(Constant.CODE_2_ISP, arrays[5]);
ipLibraryVO.setIsp(isp);
String country = RedisUtil.hget(Constant.CODE_2_COUNTRY, arrays[2]);
ipLibraryVO.setCountry(country);
String area = RedisUtil.hget(Constant.CODE_2_AREA, arrays[3]);
ipLibraryVO.setArea(area);
return ipLibraryVO;
}
另附:
import lombok.extern.slf4j.Slf4j;
import org.apache.http.HttpHost;
import org.apache.http.client.config.RequestConfig;
import org.apache.http.client.methods.CloseableHttpResponse;
import org.apache.http.client.methods.HttpGet;
import org.apache.http.impl.client.CloseableHttpClient;
import org.apache.http.impl.client.HttpClientBuilder;
import org.apache.http.util.EntityUtils;
import java.io.IOException;
@Slf4j
public class HttpUtil {
/**
* 发送get请求
* @param url
* @return
*/
public static String sendGet(String url,boolean flag,String proxyIp,Integer proxyPort) throws IOException {
CloseableHttpClient httpClient = HttpClientBuilder.create().build();
HttpHost proxy = new HttpHost(proxyIp, proxyPort, "http");
RequestConfig.Builder builder = RequestConfig.custom()
.setConnectTimeout(180 * 1000).setConnectionRequestTimeout(180 * 1000)
.setSocketTimeout(180 * 1000).setRedirectsEnabled(true);
if (flag){
builder.setProxy(proxy);
}
RequestConfig requestConfig = builder.build();
HttpGet httpGet = new HttpGet(url);
httpGet.setConfig(requestConfig);
String result = null;
CloseableHttpResponse response = httpClient.execute(httpGet);
if(response != null && response.getStatusLine().getStatusCode() == 200) {
result = EntityUtils.toString(response.getEntity(), "utf-8");
httpClient.close();
}
return result;
}
}