当前位置： 首页 > news >正文

提升性能300ms：深入解析Spring多表联接查询优化与SQL调优实战

news 2025/7/1 21:53:25

优化所需知识点（必须掌握）

索引篇

 explain命令

重点：这是后续分析是否使用索引以及使用是否恰当的工具

作用：查看sql的执行计划，可以看sql语句是否使用了索引，索引的使用情况，以及sql的性能。 

使用方法：explain+查询语句，例如：explain select * from test

字段解释（只讲需要用到的）
字段含义
id一条sql语句表的执行顺序(id相同从上到下顺序执行。id不同，值越大越先执行)
type表示连接类型，性能有好到差连接类型为 null,system,const,eq_ref,ref,range,index,all
possible_key可能应用在这张表上的索引(一个或多个)
key实际使用的索引，如果为null，则没有使用索引
Extra字段含义

Using index

性能好，不需要回表查询

Using where

性能好，不需要回表查询

Using index condition

需要回表查询

Using temporary

在查询过程中使用了临时表

Using filesort

使用了额外的排序操作来满足 ORDER BY 子句

 索引使用原则

重点：这决定了你创建哪些索引可以提升性能

  1. 针对数据量大并且查询比较频繁的表建立索引
  2. 针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引。
  3. 尽量选择区分度高的列（例如身份证）作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
  4. 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
  5. 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间， 避免回表，提高查询效率
  6. 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
  7. 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。

索引失效场景

重点：创建了索引但是失效了得不偿失 

  • 联合索引中，出现范围查询(>,<)，范围查询右侧的列索引失效（解决方案：使用>=、<=）
  • 不要在索引列上进行运算操作， 索引将失效
  • 字符串类型字段使用时，不加引号，索引将失效
  • 如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。
  • 用or分割开的条件， 如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会 被用到。

 优化实战

代码展示

   SELECTDISTINCTu.id AS id,uicm.code,mbi.trueName AS name,FLOOR(DATEDIFF(CURDATE(), mbi.birthday) / 365.25) AS age,mbi.education,mbi.height,CASEWHEN mbi.marriage IN (2, 11, 12, 13) THEN '离异'WHEN mbi.marriage = 3 THEN '丧偶'ELSE ''END AS marriage,mbi.hometownCode,mbi.workCity,mbi.occupation,u.sex,u.gmt_create AS time,CASE WHEN uicm.status = 0 THEN 0 ELSE 1 END AS is_off,u.phone,sc.worker_id AS workerId,--         mbi.car,--         mbi.house,--         CASE--         WHEN mbi.income = 3 THEN '3K以上'--         WHEN mbi.income = 4 THEN '3K-5K'--         WHEN mbi.income = 5 THEN '5K-8K'--         WHEN mbi.income = 6 THEN '8K-12K'--         WHEN mbi.income = 7 THEN '12K-20K元'--         WHEN mbi.income = 8 THEN '20K-50K元'--         WHEN mbi.income = 9 THEN '50K以上'--         ELSE NULL--         END AS income,CASEWHEN EXISTS (SELECT 1 FROM lspace_member.sea_customer_listWHERE member_id = u.idAND tenant_id = u.tenant_idAND shop_id = u.shop_id) THEN 1ELSE 0 END as isSea,CASEWHEN EXISTS (select 1 from lspace_member.blank_member where member_id = u.id) THEN 1ELSE 0 END as isBlank,CASEWHEN (SELECT urlFROM lspace_member.member_photoWHERE member_id = u.idAND type = 16AND STATUS = 1ORDER BY id DESC LIMIT 1) IN (NULL, 'https://static.lspace.vip/images/my/boy.jpg', 'https://static.lspace.vip/images/my/girl.jpg')THEN NULLELSE (SELECT urlFROM lspace_member.member_photoWHERE member_id = u.idAND type = 16AND STATUS = 1ORDER BY id DESC LIMIT 1)END AS urlsFROMlspace_usercenter.`user` uinner join lspace_diga.user_id_code_mapping uicm on uicm.user_id=u.idINNER JOINlspace_member.memberbaseinfo mbi ON mbi.memberId = u.id<if test="memberListReq.isMyCustomer == 1">inner join lspace_member.service_case sc on sc.worker_id=#{memberListReq.workerId} and sc.member_id = u.id and sc.case_type=1</if><if test="memberListReq.isMyCustomer == 0">left join lspace_member.service_case sc on sc.member_id = u.id and sc.case_type=1</if>left JOIN lspace_member.upgrade_vip_queue uvq on uvq.member_id=u.id and uvq.status=2left JOIN lspace_member.service_period sp on sp.member_id=u.id and uvq.order_id=sp.order_idWHEREu.sex = #{memberListReq.sex}AND u.sex IS NOT NULL<if test="memberListReq.homeTowns != null and !memberListReq.homeTowns.isEmpty()">and mbi.hometownCode in<foreach item="item" index="index" collection="memberListReq.homeTowns" open="(" separator=","close=")">#{item,jdbcType=INTEGER}</foreach></if><if test="memberListReq.workCitys != null and !memberListReq.workCitys.isEmpty()">and mbi.workCity in<foreach item="item" index="index" collection="memberListReq.workCitys" open="(" separator=","close=")">#{item,jdbcType=INTEGER}</foreach></if><if test="memberListReq.minAge != null">AND FLOOR(DATEDIFF(CURDATE(), mbi.birthday) / 365.25) &gt;= #{memberListReq.minAge}</if><if test="memberListReq.maxAge != null">AND FLOOR(DATEDIFF(CURDATE(), mbi.birthday) / 365.25) &lt;= #{memberListReq.maxAge}</if><if test="memberListReq.text != null and memberListReq.text != ''">AND(uicm.code LIKE CONCAT('%',#{memberListReq.text}, '%')oru.phone=#{memberListReq.text})</if>group byu.idORDER BYuicm.score DESC,CASEWHEN MIN(sp.start_time) IS NOT NULL THEN MIN(sp.start_time)ELSE u.gmt_createEND DESC,u.id ASClimit #{offest},#{size}

第一步：连表优化

 根据阿里巴巴的《Java 开发手册》中的 SQL 设计规范，关于 SQL 查询中涉及的表的数量并没有具体的硬性限制，但是在实践中，阿里规范建议尽量避免过多的表连接，建议连表数量小于3张。

        上面这段sql主要是为了查询出符合条条件的用户。这是优化前代码，链表7张，非必要链表2张。根据业务需求，可以看看你业务需求中是不是连的表都必须连，很影响性能，能拆则拆。我根据业务分析后我发现有两张表可以拆出来，拆！

拆后代码：

   SELECTDISTINCTu.id AS id,uicm.code,mbi.trueName AS name,FLOOR(DATEDIFF(CURDATE(), mbi.birthday) / 365.25) AS age,mbi.education,mbi.height,CASEWHEN mbi.marriage IN (2, 11, 12, 13) THEN '离异'WHEN mbi.marriage = 3 THEN '丧偶'ELSE ''END AS marriage,mbi.hometownCode,mbi.workCity,mbi.occupation,u.sex,u.gmt_create AS time,CASE WHEN uicm.status = 0 THEN 0 ELSE 1 END AS is_off,u.phone,sc.worker_id AS workerId,CASEWHEN (SELECT urlFROM lspace_member.member_photoWHERE member_id = u.idAND type = 16AND STATUS = 1ORDER BY id DESC LIMIT 1) IN (NULL, 'https://static.lspace.vip/images/my/boy.jpg', 'https://static.lspace.vip/images/my/girl.jpg')THEN NULLELSE (SELECT urlFROM lspace_member.member_photoWHERE member_id = u.idAND type = 16AND STATUS = 1ORDER BY id DESC LIMIT 1)END AS urlsFROMlspace_usercenter.`user` uinner join lspace_diga.user_id_code_mapping uicm on uicm.user_id=u.idINNER JOINlspace_member.memberbaseinfo mbi ON mbi.memberId = u.id<if test="memberListReq.isMyCustomer == 1">inner join lspace_member.service_case sc on sc.worker_id=#{memberListReq.workerId} and sc.member_id = u.id and sc.case_type=1</if><if test="memberListReq.isMyCustomer == 0">left join lspace_member.service_case sc on sc.member_id = u.id and sc.case_type=1</if>left JOIN lspace_member.upgrade_vip_queue uvq on uvq.member_id=u.id and uvq.status=2left JOIN lspace_member.service_period sp on sp.member_id=u.id and uvq.order_id=sp.order_idWHEREu.sex = #{memberListReq.sex}AND u.sex IS NOT NULL<if test="memberListReq.homeTowns != null and !memberListReq.homeTowns.isEmpty()">and mbi.hometownCode in<foreach item="item" index="index" collection="memberListReq.homeTowns" open="(" separator=","close=")">#{item,jdbcType=INTEGER}</foreach></if><if test="memberListReq.workCitys != null and !memberListReq.workCitys.isEmpty()">and mbi.workCity in<foreach item="item" index="index" collection="memberListReq.workCitys" open="(" separator=","close=")">#{item,jdbcType=INTEGER}</foreach></if><if test="memberListReq.minAge != null">AND FLOOR(DATEDIFF(CURDATE(), mbi.birthday) / 365.25) &gt;= #{memberListReq.minAge}</if><if test="memberListReq.maxAge != null">AND FLOOR(DATEDIFF(CURDATE(), mbi.birthday) / 365.25) &lt;= #{memberListReq.maxAge}</if><if test="memberListReq.text != null and memberListReq.text != ''">AND(uicm.code LIKE CONCAT('%',#{memberListReq.text}, '%')oru.phone=#{memberListReq.text})</if>group byu.idORDER BYuicm.score DESC,CASEWHEN MIN(sp.start_time) IS NOT NULL THEN MIN(sp.start_time)ELSE u.gmt_createEND DESC,u.id ASClimit #{offest},#{size}

第二步：SQL优化

 在SQL语句中少做运算可提高新增，而且可以有效避免索引失效，所以尽量不要在SQL做运算。

这三种，第一种在java代码中预先计算生日后作为条件搜索，避免了函数运算和索引失效，所以做了第二步优化

1. mbi.birthday <= '2000-01-08'

2. FLOOR(DATEDIFF(CURDATE(), mbi.birthday) / 365.25) >= 25

3.mbi.birthday <= DATE_SUB(CURDATE(), INTERVAL 25 YEAR)

优化后代码：

 SELECTu.id AS id,uicm.code,mbi.trueName AS name,FLOOR(DATEDIFF(CURDATE(), mbi.birthday) / 365.25) AS age,mbi.education,mbi.height,CASEWHEN mbi.marriage IN (2, 11, 12, 13) THEN '离异'WHEN mbi.marriage = 3 THEN '丧偶'ELSE ''END AS marriage,mbi.hometownCode,mbi.workCity,mbi.occupation,u.sex,u.gmt_create AS time,CASE WHEN uicm.status = 0 THEN 0 ELSE 1 END AS is_off,u.phone,sc.worker_id AS workerId,CASEWHEN (SELECT urlFROM lspace_member.member_photoWHERE member_id = u.idAND type = 16AND STATUS = 1ORDER BY id DESC LIMIT 1) IN (NULL, 'https://static.lspace.vip/images/my/boy.jpg', 'https://static.lspace.vip/images/my/girl.jpg')THEN NULLELSE (SELECT urlFROM lspace_member.member_photoWHERE member_id = u.idAND type = 16AND STATUS = 1ORDER BY id DESC LIMIT 1)END AS urlsFROMlspace_usercenter.`user` uinner join lspace_diga.user_id_code_mapping uicm on uicm.user_id=u.idINNER JOINlspace_member.memberbaseinfo mbi ON mbi.memberId = u.id<if test="memberListReq.isMyCustomer == 1">inner join lspace_member.service_case sc on sc.worker_id=#{memberListReq.workerId} and sc.member_id = u.id and sc.case_type=1</if><if test="memberListReq.isMyCustomer == 0">left join lspace_member.service_case sc on sc.member_id = u.id and sc.case_type=1</if>left JOIN lspace_member.upgrade_vip_queue uvq on uvq.member_id=u.id and uvq.status=2left JOIN lspace_member.service_period sp on sp.member_id=u.id and uvq.order_id=sp.order_idWHEREu.sex = #{memberListReq.sex}AND u.sex IS NOT NULL<if test="memberListReq.homeTowns != null and !memberListReq.homeTowns.isEmpty()">and mbi.hometownCode in<foreach item="item" index="index" collection="memberListReq.homeTowns" open="(" separator=","close=")">#{item,jdbcType=INTEGER}</foreach></if><if test="memberListReq.workCitys != null and !memberListReq.workCitys.isEmpty()">and mbi.workCity in<foreach item="item" index="index" collection="memberListReq.workCitys" open="(" separator=","close=")">#{item,jdbcType=INTEGER}</foreach></if><if test="memberListReq.minBirthday != null and memberListReq.minAge!=0">AND mbi.birthday &lt;= #{memberListReq.minBirthday}</if><if test="memberListReq.maxBirthday != null">AND mbi.birthday &gt;= #{memberListReq.maxBirthday}</if>
<!--        <if test="memberListReq.minAge != null">-->
<!--            AND FLOOR(DATEDIFF(CURDATE(), mbi.birthday) / 365.25) &gt;= #{memberListReq.minAge}-->
<!--        </if>-->
<!--        <if test="memberListReq.maxAge != null">-->
<!--            AND FLOOR(DATEDIFF(CURDATE(), mbi.birthday) / 365.25) &lt;= #{memberListReq.maxAge}-->
<!--        </if>--><if test="memberListReq.text != null and memberListReq.text != ''">AND(uicm.code LIKE CONCAT('%',#{memberListReq.text}, '%')oru.phone=#{memberListReq.text})</if>group byu.idORDER BYuicm.score DESC,MIN(sp.start_time) DESC,u.gmt_create desc,u.id ASClimit #{offest},#{size}

第三步：加索引

根据索引篇的知识点，在区分度高，数据列多，条件搜索，排序这个上面创建索引。创建后使用explain命令逐一分析，创建的索引是否有效，是否被使用。在进行调整

根据我的业务需求我添加了3个索引来满足我的业务需求

CREATE INDEX idx_workCity ON lspace_member.memberbaseinfo (workCity);
CREATE INDEX idx_hometown ON lspace_member.memberbaseinfo (hometownCode);CREATE INDEX idx_code ON lspace_diga.user_id_code_mapping (code);

第四步：java代码优化

根据感觉简单优化了一下代码，对效率其实影响不太大。大家也自我感觉优化一下吧，特别涉及是for循环，for循环中有查询的重点关注一下！！！

总结

根据以上方式速度快了将近一半，300ms左右。主要也是对SQL的优化，合理的使用索引，恰当的链表方式可以大大提高性能。

http://www.lryc.cn/news/519987.html

相关文章：

  • 增量导入和全量导入的区别是什么?
  • 【百度智能云客悦智能客服】搭建AI agent智能对话 - 购车推荐
  • 【HTML+CSS+JS+VUE】web前端教程-3-标题标签
  • 逐笔成交逐笔委托Level2高频数据下载和分析：20250102
  • JavaEE之线程池
  • java 中 main 方法使用 KafkaConsumer 拉取 kafka 消息如何禁止输出 debug 日志
  • 【后端面试总结】Golang可能的内存泄漏场景及应对策略
  • Java 反射机制详解
  • 【k8s】scc权限 restricted、anyuid、privileged
  • 2025华数杯国际赛A题完整论文讲解（含每一问python代码＋数据＋可视化图）
  • ThreadLocal 的使用场景
  • 后端开发 Springboot整合Redis Spring Data Redis 模板
  • 代码随想录算法训练营第 4 天（链表 2）| 24. 两两交换链表中的节点19.删除链表的倒数第N个节点 -
  • 【RDMA学习笔记】1：RDMA（Remote Direct Memory Access）介绍
  • 网络安全常见的35个安全框架及模型
  • Elasticsearch介绍及使用
  • Leetocde516. 最长回文子序列 动态规划
  • iOS 逆向学习 - Inter-Process Communication：进程间通信
  • 高级生化大纲
  • YARN WebUI 服务
  • 【Unity3D】利用IJob、Burst优化处理切割物体
  • 【大前端】Vue3 工程化项目使用详解
  • 基于文件系统分布式锁原理
  • 简历整理YH
  • Kotlin 协程基础三 —— 结构化并发（二）
  • 微信小程序实现长按录音，点击播放等功能，CSS实现语音录制动画效果
  • 校园跑腿小程序---轮播图，导航栏开发
  • 详细全面讲解C++中重载、隐藏、覆盖的区别
  • 一文读懂单片机的串口
  • HTML5 网站模板