接口响应优化，sql优化实战

已于 2023-06-20 18:13:16 修改
阅读量1.1k 收藏 5
点赞数 1
分类专栏： mysql 文章标签： sql 数据库 database
于 2022-05-30 20:57:10 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_48412846/article/details/125052507
版权

接口响应速度优化和sql优化实操

接口响应速度达到20s

前端展示接口平均速度为8s，最坏情况到达20s
在这里插入图片描述
默认搜索条件十分复杂，基本每个字段都传条件，不好建立联合索引
在这里插入图片描述

多线程异步查询

接口内容分为两部分，并且查询相互独立
1、查询页面集合（方法内存在很多业务逻辑，不是简单查询）
2、查询总数 （方法内存在很多业务逻辑，不是简单查询）
在这里插入图片描述所以使用线程池来执行查询操作，这里使用Callable接口，Callable对象执行后可以有返回值，运行Callable任务可以得到一个Future对象，通过Future对象可以了解任务执行情况，可以使用future.get()方法等待线程的执行结果。（这里也可以使用CountDownLanch）

在这里插入图片描述

成功解决？

本以为到这里简单处理一下，就可以达到需求方可以接受的层度，自测接口一看
线程1耗时：6000ms
线程2耗时：7000ms
没办法只有梳理上百行的业务和sql（苦逼）！！！
一条一条的找到慢sql(这里还是精简后的sql，实际查询条件那叫一个眼花缭乱)

这里命名为：慢sqlA，执行耗时7s

SELECT COUNT(1) FROM ( 
SELECT COUNT(1) FROM `tb_product_information_pass` AS pass LEFT JOIN tb_product_contacts 
AS con ON con.product_information_id = pass.id LEFT JOIN tb_product_query_data pqd ON 
pqd.product_id = pass.id WHERE `product_status` = 1 AND pass.`show_id` != 1
 GROUP BY product_group_name ) AS t

这里命名为：慢sqlB，执行耗时5s

SELECT COUNT(DISTINCT pass.id) AS `all`, 
SUM(IF(pass.`customer_rank` = '003003001', 1, 0)) AS `hide`, 
SUM(IF(pass.`customer_rank` = '003003002', 1, 0)) AS `toContact`, 
SUM(IF(pass.`customer_rank` = '003003003', 1, 0)) AS `setContact`, 
SUM(IF(pass.`customer_rank` = '003003004', 1, 0)) AS `cooperationed`, 
SUM(IF(pass.`customer_rank` = '003003005', 1, 0)) AS `lose` 
FROM ( 
 SELECT pc_index_data,pass.*
FROM `tb_product_information_pass` AS pass
LEFT JOIN tb_product_query_data pqd ON pqd.product_id = pass.id  
 GROUP BY pass.id 
) AS pass   
LEFT JOIN tb_product_contacts AS con ON con.product_information_id = pass.id 
WHERE  pass.`show_id` != 1 and `business_affairs` IN ( '方' ) and pass.`product_status` = 1

这里命名为：慢sqlC，耗时6s

SELECT pc_index_data, COUNT(temp.productId) AS child_amount,`productId`, `product_name`, `product_source`, `product_iscompany`, `company_name`, `company_nature`, `user_name`, 
`id_card`, `contact_address`, `customer_level`, `control_volume`, `number`, `settlement_cycles`, `settlement_method`, `share_proportion`, `cycle`, `kpi`, `advance_payment`, 
`payment_method`, `business_affairs`, `not_cooperation_reasons`, `loss_feedback`, `input_time`, `customer_rank`, `related_Id`, `edit_type`, `delete_type`, `second_id`, `person_id`, 
`productPass`, `product_status`, `contract_time`, `second_signing_people`, `second_company_term`, `contract_code`, `stage_time` , `hits` , `contact_number`,`product_group_name` 
FROM ( 
  SELECT MAX( IFNULL(pqd.pc_index_data,0)) AS pc_index_data, pass.`id` AS `productId`, `product_name`, `product_source`, `product_iscompany`, `company_name`, `company_nature`, `user_name`, `id_card`,
   `contact_address`, `customer_level`, `control_volume`, `number`, `settlement_cycles`, `settlement_method`, `share_proportion`, `cycle`, `kpi`, `advance_payment`, `payment_method`, `business_affairs`, 
   `not_cooperation_reasons`, `loss_feedback`, `input_time`, `customer_rank`, `related_Id`, `edit_type`, `delete_type`, `second_id`, `person_id`, pass.`pass_type` AS `productPass`, `product_status`,
    `contract_time`, `second_signing_people`, `second_company_term`, `contract_code`, `stage_time` , `hits` , '' AS `contact_number`,`product_group_name` 
    FROM `tb_product_information_pass` AS pass LEFT JOIN tb_product_contacts AS con ON con.product_information_id = pass.id LEFT JOIN tb_product_query_data pqd ON pqd.product_id = pass.id 
    WHERE `product_status` = 1 AND pass.`show_id` != 1 GROUP BY pass.id ) AS temp GROUP BY product_group_name ORDER BY `productPass` ASC, input_time DESC

数据量情况
tb_product_information_pass：7w
tb_product_query_data ：300w
tb_product_contacts ：8w

慢sqlA优化

我心伤悲

对product_status和product_group_name字段建立联合索引，查询速度并没有明显变化。
多表联查优化策略：
1、left join时候采用小表驱动大表的方式，即把数据量较小的表放在左边（无明显）
==2、将left join 改成inner join（实际上是减少数据量，但是这里右表的null值对业务来说任然具有意义） ==
在这里插入图片描述这里用了出现了using filesort与using temporate，using index，using where。
1、using index：当我们的查询列表以及搜索条件中只包含属于某个索引的列， 也就是在可以使用索引覆盖的情况下会出现
2、using index condition：部分条件使用索引。
2、using where：MySQL 将根据 WHERE 条件对返回的结果进行过滤。
3、using filesort：多出现在分组或者排序时，很多情况下排序操作无法使用到索引， 只能在内存中（记录较少的时候） 或者磁盘中（记录较多的时候） 进行排序， MySQL把这种在内存中或者磁盘上进行排序的方式统称为文件排序。 如果某个查询需要使用文件排序的方式执行查询， 就会在执行计划的 Extra 列中显示 Using filesort提示
4、using temporate：在许多查询的执行过程中， MySQL 可能会借助临时表来完成一些功能， 比如去重、 排序之类的， 比如我们在执行许多包含 DISTINCT、 GROUP BY、 UNION 等子句的查询过程中， 如果不能有效利用索引来完成查询， MySQL 很有可能寻求通过建立内部的临时表来执行查询。 如果查询中使用到了内部的临时表， 在执行计划的 Extra 列将会显示 Using temporary 提示
主要优化2，3，4条

经过测试发现，这里的sql主要是由于group by而变慢的
group by 实际上等于group by column order by column,所以将
GROUP BY product_group_name 改为直接排序
GROUP BY product_group_name order by null，但是sql并没有明显提速。

柳暗花明又一村

优化using temporate
mysql在使用临时表时会首先使用内存临时表，当数据量溢出内存临时表时切换磁盘临时表
SQL_BIG_RESULT：这个提示告诉mysql直接走磁盘临时表，跳过内存临时表。
惊了（没干什么都提速到毫秒）
优化后的sql：

SELECT COUNT(1) FROM ( SELECT SQL_BIG_RESULT COUNT(1) FROM `tb_product_information_pass` AS pass LEFT JOIN tb_product_contacts AS con ON con.product_information_id = pass.id LEFT JOIN tb_product_query_data pqd ON pqd.product_id = pass.id WHERE `product_status` = 1 AND pass.`show_id` != 1 GROUP BY product_group_name ) AS t

慢sqlB优化

这里比较简单，前置搜索条件减少子查询的数据量,sql响应速度从5s变为1s。（nice）

SELECT COUNT(DISTINCT pass.id) AS `all`, 
SUM(IF(pass.`customer_rank` = '003003001', 1, 0)) AS `hide`, 
SUM(IF(pass.`customer_rank` = '003003002', 1, 0)) AS `toContact`, 
SUM(IF(pass.`customer_rank` = '003003003', 1, 0)) AS `setContact`, 
SUM(IF(pass.`customer_rank` = '003003004', 1, 0)) AS `cooperationed`, 
SUM(IF(pass.`customer_rank` = '003003005', 1, 0)) AS `lose` 
FROM ( 
 SELECT pc_index_data,pass.*
FROM `tb_product_information_pass` AS pass
LEFT JOIN tb_product_query_data pqd ON pqd.product_id = pass.id  
WHERE `business_affairs` IN ( '方' ) and pass.`product_status` = 1 GROUP BY pass.id 
) AS pass   
LEFT JOIN tb_product_contacts AS con ON con.product_information_id = pass.id 
WHERE  pass.`show_id` != 1

继续努力慢sqlC优化

分解sql

这条sql两层分组，每层分组都具有聚合函数，拆解sql，其主要耗时再MAX( IFNULL(pqd.pc_index_data,0))这条函数上，其数据量100w条，在这个基本上载进行函数操作可想而知的慢。
这里我首先想到的是有没有办法从sql层面削去两层分组和函数。（并没有找到合适的方法，跪求sql语句层面的优化）
由于数据量的问题，内存中处理函数也不可取

分析业务和数据表。

tb_product_query_data 表的数据量达到惊人的300w，但是联表查询只用了一个字段
pc_index_data，是不是可以减少一个连表查询内，在tb_product_information_pass表建立冗余字段pc_index_data，降低查询数据量呢

说干就干，建冗余字段

1、建冗余字段

在这里插入图片描述

2、并将tb_product_query_data 的数据同步置tb_product_information_pass

UPDATE tb_product_information_pass a , (SELECT product_id,MAX(IFNULL(pc_index_data,0)) AS pc_index_data FROM tb_product_query_data GROUP BY product_id) b SET a.pc_index_data = b.pc_index_data WHERE a.id = b.`product_id`

3、建立触发器，监控tb_product_query_data 表的数据变化同步到tb_product_information_pass

DELIMITER $$

USE `bz_zone_test`$$

DROP TRIGGER /*!50032 IF EXISTS */ `insert_product_query`$$

CREATE
    /*!50017 DEFINER = 'bz_zone_test'@'%' */
    TRIGGER `insert_product_query` AFTER INSERT ON `tb_product_query_data` 
    FOR EACH ROW BEGIN
UPDATE tb_product_information_pass SET pc_index_data = IF(new.pc_index_data <= pc_index_data,pc_index_data,new.pc_index_data)
WHERE id = new.product_id;
END;
$$

DELIMITER ;

DELIMITER $$

USE `bz_zone_test`$$

DROP TRIGGER /*!50032 IF EXISTS */ `update_product_query`$$

CREATE
    /*!50017 DEFINER = 'bz_zone_test'@'%' */
    TRIGGER `update_product_query` AFTER UPDATE ON `tb_product_query_data` 
    FOR EACH ROW BEGIN
UPDATE tb_product_information_pass SET pc_index_data = IF(new.pc_index_data <= pc_index_data,pc_index_data,new.pc_index_data)
WHERE id = new.product_id;
END;
$$

DELIMITER ;

修改原sql，去掉烦人的函数

速度优化置1s内（开心）

大功告成

成就感十足
在这里插入图片描述

接口优化小结

1、分解sql查询具体耗时的操作。
优化sql，expain分析三列
rows列：表示影响的mysql预估的查找行数，搜索条件前置或者命中索引都可以减少扫描行数
extra列：避免出现using where，using filesort，using temporate
type列：system > const > eq_ref > ref > range > index （扫描整个索引树，例如count（*））> ALL（全表扫描），至少保证range级别
2、分解互不关联业务，寻找最耗时的业务，使用多线程，保证其他线程的执行时间接近最耗时的业务。

索引创建原则

1、遵循最左前缀法则
2、非null字段建立索引，区分度高的字段建立索引
3、查询条件建立索引
4、长字段使用前缀索引

想去22世纪
关注
专栏目录
SQL优化实战
winkooxx
10-09 272
SQL优化实战 1. Order by优化 在Order by中，如果排序会造成文件排序(在磁盘中完成排序，性能比较差)，就说明没有命中索引，这个时候可以使用最左前缀法则，让排序遵循最左前缀法则，避免文件排序 1）ORDER BY的字段改到一种表、不要夸表（设计表结构时需注意这一点） 2）OEDER BY字段建索引、多个字段时建联合索引（联合索引的字段顺序要与ORSER BY中的字段顺序一致） 3）ORDER BY中字段中联合索引的所有字段DESC或ASC要统一，否则索引不起作用 --- name,age,
接口性能优化实战：从20s到500ms，三招搞定
hebiwen95的博客
08-15 2038
因为如果在评价表中冗余一个组织code字段，万一哪天组织表中的组织code有修改，不得不通过某种机制，通知我们同步修改评价表的组织code，不然就会出现数据不一致的问题。查看该接口的调用信息，发现绝大数情况下，该接口响应还是比较快的，大部分情况都是500ms左右就能返回，但也有少部分超过了20s的请求。温馨提醒一下，无论是在批量查询评价接口查询数据库，还是在业务系统中调用批量查询评价接口，使用多线程调用，都只是一个临时方案，并不完美。接口性能问题，对于从事后端开发的同学来说，是一个绕不开的话题。...
sql优化实战：从6秒+到2秒（使用索引）
weixin_30604651的博客
11-07 171
今天客服反馈 客户在前两天查询一个移动端报表时报错了。 我看了一下报错信息，大致是timeout，这种错误基本可以确定是由于查询时间超过阀值（一般为3秒）。 sql代码如下： SELECT hh.EMP_ID , hh.STORE_ID , hh.pn1 , SUM(hh.numb) numb , SUM(hh....
一文介绍接口测试中的请求和响应
最新发布
2401_83014911的博客
07-22 964
我们一般在浏览器中测试web项目，通常网络交互中使用的都是http和https,所以首先我们要搞清楚的是http协议，https是对http的升级，主要是在http协议基础上增加了安全协议ssl，是一种比http更安全的协议，只要我们了解了http，就可以搞清楚一般的交互过程。
sql优化实战：从250秒+到10秒（简化语句）
weixin_30902251的博客
11-07 302
今天客服又反馈了一个问题，说客户在访问后台web页面中有一个查询时非常慢，直接报了“查询超时，请联系管理员！”。 于是用客户账号登录系统，最后确实是超时，但是用管理员账号登录查询时，虽然返回了68w条数据，但也就18秒。 sql代码如下： SELECT o.id1 AS o_id1 , o.id2 AS o_id2 , o.id3 AS o_id3...
接口性能优化技巧，干掉慢代码！
好好学java
12-21 582
今日推荐推荐 20 款 IDEA 主题！ 开源困境：Log4j2 维护者发声：没有工资，还要挨骂！！ RedisJson 横空出世，性能碾压ES和Mongo！ 还在发愁写API文档？...
sql优化实战：从40分钟到10秒（更新统计信息）
weixin_30640291的博客
11-07 247
sql代码如下（这个代码只是所有代码的一部分）： SELECT inout.biz_date , inout.EMP_ID , inout.duty_id , inout.store_id , org_id ...
接口响应优化方案
jiangzhongwei_的博客
11-15 558
最近收到客户反映系统卡顿严重，然后让他截图看了下，最长响应时长居然高达16s,其他3s,4s的接口一大堆，简直是恐怖！
Node + Redis 接口性能优化实战
06-15
为什么要使用Redis优化接口...我们在碰到需要执行耗时特别久，且结果不频繁变动的SQL，就特别适合将运行结果放入缓存，这样，后面的请求就去缓存中读取，请求使得能够非常迅速的响应，极大的提升我们的API接口的性能。
接口优化从20s优化到500ms
BUG指挥课堂
10-14 1511
接口性能问题，对于从事后端开发的同学来说，是一个绕不开的话题。想要优化一个接口的性能，需要从多个方面着手。其实，我之前也写过一篇接口性能优化相关的文章《聊聊接口性能优化的11个小技巧》，发表之后在全网广受好评，感兴趣的小伙们可以仔细看看。本文将会接着接口性能优化这个话题，从实战的角度出发，聊聊我是如何优化一个慢查询接口的。上周我优化了一下线上的批量评分查询接口，将接口性能从最初的20s，优化到目前的500ms以内。总体来说，用三招就搞定了。到底经历了什么？
Oracle PL/SQL实战（待续）
07-27
Oracle PL/SQL是一种强大的编程语言，它结合了SQL的数据处理能力与PL的程序设计...通过实践和学习"Oracle PL/SQL实战（待续）"的相关内容，我们可以提升在Oracle环境中解决问题的能力，更好地管理和优化数据库系统。
SQL优化纯实战
12-29
倾力打造。书中包含了详细的案例研究、最佳实践和丰富的0racle新的调整特性的代码示例。
Java 性能优化实战 21 讲
10-19
在Java性能优化实战的21讲中，涵盖了Java开发中至关重要的性能调优技术，旨在提升应用程序的效率、稳定性和可扩展性。以下是对这些关键知识点的详细解析： 1. **JVM内存模型**：理解Java虚拟机（JVM）的内存结构是...
sql优化实战
demo小王子
11-24 340
SQL优化 单表优化 #表结构：文章表 CREATE TABLE `explain`.`Untitled` ( `id` int(11) NOT NULL, `author_id` int(10) NOT NULL, `category_id` int(10) NOT NULL, `views` int(10) NOT NULL, `comments` int(10) NOT NULL, `title` varchar(255) CHARACTER SET latin1 COLLA
SQL性能优化实战
weixin_42076433的博客
03-20 198
于是就开干，经过仔细的分析，总感觉 t_form_main 与 t_form_step 直接到关系查询有问题，索引列proc_inst_id 没有充分使用的.因为他们之间的函数是使用的EXISTS,遍历全表去过滤.想想数据那么大,2个全表一关联,笛卡尔积是多么的恐怖.这个是我的代表查询，查询的单据表、审批流程步骤表、用户表、视图表。多表关联查询，除用户表，每张表数据都是千万级别。这个SQL执行完：时间到了16s,用户多次反馈，性能太差。直到有一天，太闲了，不能总摸鱼吧。简单使用了一个子查询，利用索引。
SQL优化实战一
NanGuoHuangDou的博客
07-23 398
1.分别创建课程表，学生表，成绩表 CREATE TABLE Course ( c_id int PRIMARY KEY, name varchar(10) ); CREATE TABLE Student ( id int PRIMARY KEY, name varchar(10) ); CREATE TABLE SC ( sc_id int PRIMARY KEY, s_id...
SQL优化（三）:SQL优化实战
FeiChangWuRao的博客
09-08 810
前两节基本是讲了SQL优化重要的工具大概思路，你连explain都看不明白，遇到慢查询一个SQL执行半天的情况，估计优化起来肯定无处着手。 这节主要是SQL优化的具体实战，常见的一些问题。本文实际上也收到很多别的文章影响，收集了一些大牛的优化干货总结。 一 优化原则 1.提高数据的访问效率： 设置合理的字段类型，能减少空间自然也就减少磁盘IO，同样设置索引通过索引访问也会减少IO提高效率 2 减少访问次数： 有时候很多次数据库交互才是导致访问时间长，服务超时的原因，比如说代码里面for循环里面数据库访问，循
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值