利用Redis对MySQL查询做缓存提高查询速度

太过爱你忘了你带给我的痛 2024-04-18 10:56 181阅读 0赞

### MySQL配置 ###

1.  application.properties中配置数据源

spring.datasource.url=jdbc:mysql://localhost:3306/test?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&useSSL=false
    
    spring.datasource.username=root
    
    spring.datasource.password=root
    
    spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

1.  添加maven依赖

org.mybatis.spring.boot
    
    		    mybatis-spring-boot-starter
    
    		    2.0.0

1.  编写mapper接口并在其中写实体类(实体类写在外面也可以)

import org.apache.ibatis.annotations.Insert;
    
    import org.apache.ibatis.annotations.Mapper;
    
    import org.apache.ibatis.annotations.Select;
    
    
    
    import java.util.List;
    
    
    
    @Mapper
    
    public interface MapTestMapper { 
    
    
    
        public class MapTest{ 
    
            private String start_ip;
    
            private String end_ip;
    
            private String info_json;
    
    
    
            public String getStart_ip() { 
    
                return start_ip;
    
            }
    
    
    
            public void setStart_ip(String start_ip) { 
    
                this.start_ip = start_ip;
    
            }
    
    
    
            public String getEnd_ip() { 
    
                return end_ip;
    
            }
    
    
    
            public void setEnd_ip(String end_ip) { 
    
                this.end_ip = end_ip;
    
            }
    
    
    
            public String getInfo_json() { 
    
                return info_json;
    
            }
    
    
    
            public void setInfo_json(String info_json) { 
    
                this.info_json = info_json;
    
            }
    
        }
    
        @Insert("INSERT INTO map_geo VALUES (#{start_ip},#{end_ip},#{info_json})")
    
        public void insertMap(MapTest maptest);
    
    
    
        @Select("SELECT info_json FROM map_geo WHERE start_ip <= #{ipNum} and end_ip > #{ipNum}")
    
        public List selectMap(String ipNum);
    
    
    }

1.  使用

@Service
    public class MapUtil { 
        @Autowired
        private MapTestMapper mapTestMapper;
        
        public void insertMapIntoMysql() throws Exception{ 
                geoSql.setIp("127.0.0.1");
                geoSql.setInfo_json("本机");
                mapGeoMapper.inserGeotMap(geoSql);
            }
        }

### Redis配置 ###

1.  application.properties中配置Redis源

spring.redis.database=0
    spring.redis.host=localhost
    spring.redis.port=6379
    spring.redis.password=
    spring.redis.pool.max-active=8
    spring.redis.pool.max-wait=-1
    spring.redis.pool.max-idle=8
    spring.redis.pool.min-idle=0
    spring.redis.timeout=5000

1.  添加maven依赖

org.springframework.boot
    		spring-boot-starter-data-redis

1.  编写redis工具类
    
    > StringRedisTemplate继承了RedisTemplate类，唯一的区别就是其是专门针对String字符串进行了转码，如果是字符串操作，没有转码，则推荐使用StringRedisTemplate。

import java.util.List;
    import java.util.Map;
    import java.util.Set;
    import java.util.concurrent.TimeUnit;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.data.redis.core.StringRedisTemplate;
    import org.springframework.stereotype.Service;
    import com.alibaba.fastjson.JSON;
    
    @Service
    public class RedisUtil { 
    
    	@Autowired
    	private StringRedisTemplate redisTemplate;
    	
    	public void setString(String key, String value){ 
    		redisTemplate.opsForValue().set(key, value);
    		redisTemplate.expire(key, 0, TimeUnit.SECONDS);
    	}
    	public void getString(String key){ 
    		redisTemplate.opsForValue().get(key);
    	}
    
    }

### 使用 ###

@Service
    
    public class CacheService { 
    
        private final Logger logger = LoggerFactory.getLogger(getClass());
        @Autowired
        private EsUtil esUtil;
        @Autowired
        private RedisUtil redisUtil;
        @Autowired
        private MapTestMapper mapTestMapper;
        public JSONObject getIpInfo(String ipNum){ 
            JSONObject jsonObject = new JSONObject();
            //若redis存在，则直接从redis中取
            String geoInfo = redisUtil.getString(ipNum);
            if(StringUtils.isEmpty(geoInfo)){ 
            //若redis中不存在，则从数据库中取值并赋给redis
                List list = mapTestMapper.selectMap(ipNum);
                if(list != null && list.size() > 0){ 
                    geoInfo = list.get(0).getInfo_json();
                    redisUtil.setString(ipNum, geoInfo);
                }else{ 
                    return jsonObject;
                }
            }
            jsonObject = JSONObject.parseObject(geoInfo);
            return jsonObject;
        }