利用MySQL性能调优与Redis缓存解决设备列表查询延迟问题

背景：

 该系统主要为全球业务系统，主要面向全球各地的经销商或者售后服务人员在使用，使用的是基于 Vue/Element UI 和 Spring Boot/Spring Cloud & Alibaba 前后端分离的分布式微服务架构。后端采用Spring Boot、Spring Cloud & Alibaba。注册中心、配置中心选型Nacos，权限认证使用Redis等技术体系。

问题现象：

设备列表功能是系统的关键功能之一，很多业务都在此页面上完成，同时，当前系统中的设备数据量相对较小，仅有约一千条记录，但在设备列表功能上的查询响应时间却平均达到了2.6秒，这对于终端用户的体验构成了明显的不利影响。这种延迟不仅降低了工作效率，也可能导致用户的满意度下降，进而影响到整个系统的可用性和用户粘性。如下图所示

优化策略：

为了改善查询耗时的问题，以下是一些潜在的优化策略：

1. 数据库性能优化（目前也是主要从数据库性能方面为主要着力点）

• 索引优化：确保数据库表的关键字段（例如设备ID、状态等）已建立了适当的索引，以加速查询过程。(这个是最常见的解决办法，也是非常有效的途径)
• SQL查询优化：利用慢查询日志工具来识别并优化那些执行效率低下的SQL语句。尽量简化查询逻辑，减少不必要的JOIN操作。

2. 后端服务性能优化

• 引入缓存：由于设备数据量不大，可以考虑使用Redis作为缓存层，存储频繁访问的数据，减少数据库访问频率。（像调用的地图接口，把数据引入到缓存，避免了每次请求，就查询一次地图接口）
• 异步处理：对于非关键路径的数据获取，可以采用异步方式加载，以此降低初次响应时间。
• 服务解耦：审查现有的服务架构，确保服务间的依赖关系最小化，必要时可对服务进行进一步拆分以提高整体性能。

3. 前端用户体验优化

• 懒加载：前端实现数据懒加载，只有当用户接近页面底部时才加载更多数据，减少初始加载量。（暂未考虑）
• 按需请求：改变数据请求策略，使得数据仅在必要时（如过滤或排序操作时）才被请求。

4. 监控与持续改进

• 性能监控：部署性能监控工具，持续跟踪系统的健康状况，快速定位并解决性能瓶颈。
• 负载测试：定期执行负载测试，模拟高并发场景下的系统行为，以验证和调优系统性能。

 本次研究的主要着力点是数据库性能方面的调优和引入缓存机制。

 第一步：找到对应的service层代码，打印每段关键代码的耗时，进行分析，如下图所示。找到耗时较长的代码，单独摘出进行分析。

 

  

 

根据控制台输出得知，Code2和Code3耗时，分别是0.77s、1.12s。主要排查点是IPage<DeviceVO> iPage = deviceMapper.selectList(page, param, language);和JSONObject address = BaiduMapsUtil.getAddress(temp.getDeviceLatitudeBaidu(),temp.getDeviceLongitudeBaidu(),language,list);这两段代码。

第二步：找到问题代码

得到deviceMapper.selectList(page, param, language)方法的执行sql代码。在进行查询得出时间为0.265s,当看到这个耗时，就觉得有点问题，关联的每张表，单表的数据库不超过500条，查询耗时就消耗了0.265s,初步怀疑是数据库索引出了问题，或者没有创建相关的索引。

 在对主表和其他关联表查看得知，所有的表都没有创建索引，看来这个系统的编程人员对数据库编程的功底有待加强，既然找到问题原因了，解决问题就相对简单一点。

第三步：用“EXPLAIN”关键字，查看执行计划，

查询执行条件哪些没有走索引查询，主要关注点是type,如下图所示，ALL，eq_ref等，分别代表什么含义。

参考如下：SQL性能优化神器：explain 执行计划-CSDN博客

 第四步：找到对相应的表，建立正常的索引。

 执行建立索引后的SQL语句，查询耗时为0.025s,同样的条件，建立索引的，明显比之前减少了0.24s。

 此时的查询响应时间也缩短到了369毫秒，

 代码执行耗时也降到了0.0204s,比之前减少了0.7516s,效果显著。接下来检查Code 3的代码情况，还有很大优化空间。

  第五步：找到另外一段耗时代码，进行分析

这段代码，是调用百度地图api接口，根据传输的经纬度，解析地址，返回对应的地址信息。如下图所示，这个耗时主要是在代码层面的耗时，api返回结果后，需要对结果进行转码解析，提取需要的地址信息，页面每请求一次，就会调用api接口一下，造成了很多重复的查询，从这方面入手来分析，对性能的提升有很大的帮助。

引入缓存：由于设备数据量不大，可以考虑使用Redis作为缓存层，存储频繁访问的数据，减少数据库访问频率。

主要在这方面做了优化，当第一次查询该经纬度信息，把该经纬度的地址信息，写入到redis缓存中，下次再查询该经纬度的时候，就不会再调用api接口，而是直接使用缓存数据。

同时，也要考虑经纬的经纬度精度问题，如：114.10638351346358000,22.45046778684654000,

在存储在redis的时候，只存储了address:22.4504677,114.106383，百度地图api接口，（22.4504677,114.106383）返回的地址（114.10638351346358000,22.45046778684654000）这个是一样的。

 

百度地图API接口响应时间

第六步：引入缓存：使用Redis作为缓存层，存储频繁访问的数据，减少数据库访问频率。

  /**
     * 保留一个字符串的前10位字符，并且当字符串长度少于10位时不进行截取
     * @param d
     * @return
     */
    public static String keepFirstTenCharacters(Double d) {
        String  latStr = d.toString();
        if (latStr.length() > 10) {
            return latStr.substring(0, 10);
        }
        return latStr;
    }

private void setRedisByAddress(Double lat, Double lng, String address){
        redisService.setCacheObject(CacheConstants.ADDRESS+DataUtil.keepFirstTenCharacters(lat)+","+DataUtil.keepFirstTenCharacters(lng),address);
    }

private String getRedisByAddress(Double lat, Double lng){
        return redisService.getCacheObject(CacheConstants.ADDRESS+DataUtil.keepFirstTenCharacters(lat)+","+DataUtil.keepFirstTenCharacters(lng));
    }

结论：

最终，经过数据库性能调优和引入缓存机制后，查询响应时间明显减短，降到了100毫秒一下，如图所示

 觉得写的不错的朋友，请点点赞！❤❤❤❤❤❤❤❤