mysql中sql查询慢优化实战方案

最新推荐文章于 2023-03-30 09:06:34 发布

卖柴火的小伙子

最新推荐文章于 2023-03-30 09:06:34 发布

阅读量639

点赞数

文章标签： sql mysql 数据库

本文链接：https://blog.csdn.net/weixin_43401380/article/details/125124004

版权

    记录一次sql性能优化处理问题,希望当前对sql优化有困扰的同学能提供参考和帮助,由于每个人处理业务不同,所以主要理解进行性能优化的处理思路.
    简单介绍一下业务场景和问题:瑜伽馆中会员上课需要购买会员卡,会员卡有两种类型,一种是只能在单个的瑜伽馆使用,另一种是适用于多个瑜伽馆的通用卡.现在有一个会员卡列表查询的需求,要求每个瑜伽馆里面需要查询出本馆创建的会员卡以及其他馆创建的适用于本馆的通用卡.项目上线前期,瑜伽馆数量较少,创建的会员信息和会员卡信息都不多.所以问题没有暴露出来,但是随着瑜伽馆创建数量增多,会员卡查询列表出现的问题是加载数据过慢.从浏览器控制台显示的接口响应时间是48S,页面中会一直显示加载中.用户体验很差.现在就需要进行处理这个加载慢的问题.
    问题分析过程:加载慢的问题原因很多,需要逐个原因去排查,项目是部署在阿里云服务器,配置:4核 8G,带宽3M,刚开始怀疑是服务器配置过低,但是其他页面查询没有出现加载过慢的问题就考虑服务端接口的问题.会员卡列表接口中主要逻辑是数据库查询会员卡列表信息以及对查询后的数据有遍历赋值处理(标识一下会员卡是本馆创建或是其他馆创建),查询了一下sql执行的时间,果然很慢.生产环境中上执行查询耗时19s.sql如下:

	SELECT DISTINCT(manage_staff_card.`card_no`) card_no,manage_staff_card.create_time,manage_studio.id studio_id,manage_staff.`real_name`,
 manage_staff.`mobile`,manage_card.`card_name`,manage_studio.`name`, manage_staff_card.`rest_count`,
 DATE_FORMAT(manage_staff_card.`create_time`,'%Y-%m-%d') create_time,IFNULL(DATE_FORMAT(manage_staff_card.`end_time`,'%Y-%m-%d'),'') end_time,
 manage_staff_card.`status`, manage_staff_card.`card_type`,manage_staff_card.type,manage_staff_card.card_id,
 manage_studio.studio_name,manage_card.time_type,manage_staff_card.self_card_no FROM manage_staff_card 
 LEFT JOIN manage_staff ON manage_staff_card.`login`=manage_staff.`login`
 LEFT JOIN manage_card ON manage_staff_card.`card_id`=manage_card.`id` 
 LEFT JOIN manage_studio ON manage_studio.`id`=manage_staff_card.`studio_id` 
 LEFT JOIN manage_card_course ON manage_card_course.`card_id`=manage_staff_card.`card_id` 
 WHERE (manage_staff_card.`studio_id`=2 OR (manage_card.`flag`=1 AND manage_card_course.`studio_id`=2)) AND manage_staff_card.`status`!=7 order by manage_staff_card.create_time desc

    所以问题很明确,就是要进行sql性能优化.
    sql优化每个人遇到的业务场景不同,都需要结合业务场景具体问题具体分析,保证原来的查询结果不变的基础上提升减少sql耗时.当然了解基本的sql优化知识,就是大家平常面试经常问题的sql优化方式,这个时候就能排上用场.实际处理问题过程中,需要每种方式都需要自己去跑一下看一下耗时,自己也试过很多中方式,这里只记录确实对查询结果性能提升有实际作用的优化方式.
1.使用union all替换掉or
    对查询条件中索引列使用or会导致全表扫描,当前业务中有查询通用卡与非通用卡的区分,所以基于业务需要也要进行区分开.区分开之后逻辑层中循环遍历赋值的处理就可以迁移到表查询中(本馆查询出的会员卡都是本馆的,isCurrentStudio赋值为true.通用查询逻辑中的卡都是非本馆,isCurrentStudio赋值为false),修改之后:

SELECT DISTINCT(t.`card_no`) card_no,t.studio_id,t.`real_name`,
 t.`mobile`,t.`card_name`,t.`name`, t.`rest_count`,
 DATE_FORMAT(t.create_time,'%Y-%m-%d') create_time,t.end_time,
 t.`status`, t.`card_type`,t.type,t.card_id,
 t.studio_name,t.time_type,t.self_card_no
  FROM
 (SELECT manage_staff_card.`card_no`,manage_studio.id studio_id,manage_staff.`real_name`,
 manage_staff.`mobile`,manage_card.`card_name`,manage_studio.`name`, manage_staff_card.`rest_count`,
 manage_staff_card.`create_time`,IFNULL(DATE_FORMAT(manage_staff_card.`end_time`,'%Y-%m-%d'),'') end_time,
 manage_staff_card.`status`, manage_staff_card.`card_type`,manage_staff_card.type,manage_staff_card.card_id,
 manage_studio.studio_name,manage_card.time_type,manage_staff_card.self_card_no FROM manage_staff_card 
 LEFT JOIN manage_staff ON manage_staff_card.`login`=manage_staff.`login`
 LEFT JOIN manage_card ON manage_staff_card.`card_id`=manage_card.`id` 
 LEFT JOIN manage_studio ON manage_studio.`id`=manage_staff_card.`studio_id` 
 WHERE manage_staff_card.`studio_id`=2 AND manage_staff_card.`status`!=7 
 UNION ALL 
 SELECT manage_staff_card.`card_no`,manage_studio.id studio_id,manage_staff.`real_name`,
 manage_staff.`mobile`,manage_card.`card_name`,manage_studio.`name`, manage_staff_card.`rest_count`,
manage_staff_card.`create_time`,IFNULL(DATE_FORMAT(manage_staff_card.`end_time`,'%Y-%m-%d'),'') end_time,
 manage_staff_card.`status`, manage_staff_card.`card_type`,manage_staff_card.type,manage_staff_card.card_id,
 manage_studio.studio_name,manage_card.time_type,manage_staff_card.self_card_no FROM manage_staff_card 
 LEFT JOIN manage_staff ON manage_staff_card.`login`=manage_staff.`login`
 LEFT JOIN manage_card ON manage_staff_card.`card_id`=manage_card.`id` 
 LEFT JOIN manage_studio ON manage_studio.`id`=manage_staff_card.`studio_id` 
 LEFT JOIN manage_card_course ON manage_card_course.`card_id`=manage_staff_card.`card_id` 
 WHERE manage_staff_card.`studio_id`!=2 AND manage_card.`flag`=1 AND manage_card_course.`studio_id`=2 AND manage_staff_card.`status`!=7 ) t ORDER BY t.create_time DESC

2.尽量不使用!=,使用in进行代替(当前数据量的原因,实际耗时查不多)
3.left join 优化
3.1减少left join数量
可以看出关联的left join表过多,阿里开发规范中建议的left关联数不超过3个;查询本馆创建的会员卡列表信息sql中,由于业务进行拆分,所以不需要关联manage_card_course,去除一个多余left关联,优化后的查询本馆会员卡列表信息如下:

SELECT manage_staff_card.`card_no`,manage_studio.id studio_id,manage_staff.`real_name`,
 manage_staff.`mobile`,manage_card.`card_name`,manage_studio.`name`, manage_staff_card.`rest_count`,
 manage_staff_card.`create_time`,IFNULL(DATE_FORMAT(manage_staff_card.`end_time`,'%Y-%m-%d'),'') end_time,
 manage_staff_card.`status`, manage_staff_card.`card_type`,manage_staff_card.type,manage_staff_card.card_id,
 manage_studio.studio_name,manage_card.time_type,manage_staff_card.self_card_no,TRUE isCurrentStudio
  FROM manage_staff_card 
 LEFT JOIN manage_staff ON manage_staff_card.`login`=manage_staff.`login`
 LEFT JOIN manage_card ON manage_staff_card.`card_id`=manage_card.`id` 
 LEFT JOIN manage_studio ON manage_studio.`id`=manage_staff_card.`studio_id` 
 WHERE manage_staff_card.`studio_id`=2  AND manage_staff_card.`status` IN (1,2,3,4,5,6)

优化后耗时:0.188S.
3.2 调整left join表顺序
left join关联要保证小表驱动大表,也就是说数据少的表要放到left join前面,因为左边的表是全局扫描.如果左边是数据大的表,则查询的速度就会更慢.实际查询出的会员卡和课程关联表manage_card_course有2W多条,其中会有很多重复的会员卡id,所以从子查询中进行去重.去重之后sql:

  SELECT manage_staff_card.`card_no`,manage_studio.id studio_id,manage_staff.`real_name`,
 manage_staff.`mobile`,manage_card.`card_name`,manage_studio.`name`, manage_staff_card.`rest_count`,
manage_staff_card.`create_time`,IFNULL(DATE_FORMAT(manage_staff_card.`end_time`,'%Y-%m-%d'),'') end_time,
 manage_staff_card.`status`, manage_staff_card.`card_type`,manage_staff_card.type,manage_staff_card.card_id,
 manage_studio.studio_name,manage_card.time_type,manage_staff_card.self_card_no,FALSE isCurrentStudio FROM (SELECT DISTINCT(card_id) card_id FROM manage_card_course WHERE studio_id=2) t 
 LEFT JOIN manage_card  ON t.`card_id`=manage_card.`id` 
 LEFT JOIN manage_staff_card ON manage_card.id=manage_staff_card.`card_id`  
 LEFT JOIN manage_staff ON manage_staff_card.`login`=manage_staff.`login`
 LEFT JOIN manage_studio ON manage_studio.`id`=manage_staff_card.`studio_id` 
 WHERE manage_card.`flag`=1 AND manage_card.state=1 AND  manage_staff_card.`studio_id` !=2  AND manage_staff_card.`status` IN (1,2,3,4,5,6)

优化后耗时:0.533S.
使用union all 进行组合查询:

 SELECT m.* FROM 
 ( SELECT manage_staff_card.`card_no`,manage_studio.id studio_id,manage_staff.`real_name`,
 manage_staff.`mobile`,manage_card.`card_name`,manage_studio.`name`, manage_staff_card.`rest_count`,
 manage_staff_card.`create_time`,IFNULL(DATE_FORMAT(manage_staff_card.`end_time`,'%Y-%m-%d'),'') end_time,
 manage_staff_card.`status`, manage_staff_card.`card_type`,manage_staff_card.type,manage_staff_card.card_id,
 manage_studio.studio_name,manage_card.time_type,manage_staff_card.self_card_no,TRUE isCurrentStudio
  FROM manage_staff_card 
 LEFT JOIN manage_staff ON manage_staff_card.`login`=manage_staff.`login`
 LEFT JOIN manage_card ON manage_staff_card.`card_id`=manage_card.`id` 
 LEFT JOIN manage_studio ON manage_studio.`id`=manage_staff_card.`studio_id` 
 WHERE manage_staff_card.`studio_id`=2  AND manage_staff_card.`status` IN (1,2,3,4,5,6)
 UNION ALL 
 SELECT manage_staff_card.`card_no`,manage_studio.id studio_id,manage_staff.`real_name`,
 manage_staff.`mobile`,manage_card.`card_name`,manage_studio.`name`, manage_staff_card.`rest_count`,
manage_staff_card.`create_time`,IFNULL(DATE_FORMAT(manage_staff_card.`end_time`,'%Y-%m-%d'),'') end_time,
 manage_staff_card.`status`, manage_staff_card.`card_type`,manage_staff_card.type,manage_staff_card.card_id,
 manage_studio.studio_name,manage_card.time_type,manage_staff_card.self_card_no,FALSE isCurrentStudio FROM (SELECT DISTINCT(card_id) card_id FROM manage_card_course WHERE studio_id=2) t
 LEFT JOIN manage_card  ON t.`card_id`=manage_card.`id` 
 LEFT JOIN manage_staff_card ON manage_card.id=manage_staff_card.`card_id`  
 LEFT JOIN manage_staff ON manage_staff_card.`login`=manage_staff.`login`
 LEFT JOIN manage_studio ON manage_studio.`id`=manage_staff_card.`studio_id` 
 WHERE manage_card.`flag`=1 AND manage_card.state=1 AND manage_staff_card.`studio_id` !=2  AND manage_staff_card.`status` IN (1,2,3,4,5,6) ) m ORDER BY m.create_time DESC

    优化后sql耗时由19S降低到0.751S.
    服务端接口耗时统计:优化之后耗时由48S降低到3S.
    总结:以上处理方式可能不是最合理的,还有优化的空间,但是已经能满足当前的业务需求.业务环境决定了性能优化的层次,当然暂时没有遇到上千万级别的真实场景,所以暂时没有实际的实践方案.所在的平台不同,视野不同,技术要求也会有所不同,项目随着数据量的增多,以后也会进行对应的优化,后期也会进行更新优化方案.希望对你有所帮助,欢迎评论区留言点赞!