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

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

索引篇

 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的优化，合理的使用索引，恰当的链表方式可以大大提高性能。