您当前的位置:首页 > 电脑百科 > 数据库 > 百科

600倍效率提升sql查询优化从几十秒到几十毫秒

时间:2023-03-28 15:27:16  来源:今日头条  作者:进击的程序猿

背景

运维反馈说线上一个接口越来越慢,大概二三十秒才有返回。

查看接口代码,定位问题出在sql查询效率上。

sql意图是将多个号码最新的数据返回给前端使用。

单表数据量 530万左右,id为主键,phone为普通索引

优化过程

  1. 原sql如下,通过in + 子查询方式查询
select * from
	t_user_track 
where
	id in 
    (select max(id) as id 
  		from t_user_track 
			where phone in ('xxxxx', 'xxxxx') 
			group by phone)

执行时间在30秒左右,

 

explain查看执行计划

 

可以看出子查询走上了索引,主查询没有走索引,全表查询,rows=2333216

  1. 同事写了一段新的sql来查询,如下
select * from (
            select
            DISTINCT *
            from t_user_track
            where
            phone in ('xxxxx', 'xxxx')
            order by locate_time desc
        ) t
        group by phone;

执行时间在4秒左右

 

查看执行计划

 

 

派生表查询走上了phone的索引,rows=157108,主查询没有走上索引,row=157108

  1. 改成如下sql,关联表查询
select *
  from t_user_track t1,
    (select max(id) as id from t_user_track 
  			where phone in ('xxxxxx', 'xxxxx') group by phone) t2
where t1.id = t2.id;

查询时间为0.04秒

 

执行计划如下

 

 

  1. 改成inner join关联表,如下sql
select * 
  from t_user_track t1 
  INNER JOIN
  (select max(id) as id from t_user_track where phone in ('xxxxxx', 'xxxxx') group by phone) t2
 on t1.id = t2.id

执行时间为0.041秒

 

 

执行计划如下

 

 

结果

3、4两种关联表查询效率最高

原理分析

  1. 3、4两种连表查询只有一次查询,且通过小表驱动大表,所以查询效率较高
  2. 第一种in + 子查询的方式,需要两次查询,查询效率较低

留下一个疑问in+子查询的方式,为什么子查询走了索引,主查询却没有走索引

MySQL官方文档

mysql手册也提到过,具体的原文在mysql文档的这个章节:

I.3. Restrictions on Subqueries

13.2.8. Subquery Syntax

摘抄:

1)关于使用IN的子查询:

Subquery optimization for IN is not as effective as for the = operator or for IN(value_list) constructs.

A typical case for poor IN subquery performance is when the subquery returns a small number of rows but the outer query returns a large number of rows to be compared to the subquery result.

The problem is that, for a statement that uses an IN subquery, the optimizer rewrites it as a correlated subquery. Consider the following statement that uses an uncorrelated subquery:

SELECT ... FROM t1 WHERE t1.a IN (SELECT b FROM t2);

The optimizer rewrites the statement to a correlated subquery:

SELECT ... FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.b = t1.a);

If the inner and outer queries return M and N rows, respectively, the execution time becomes on the order of O(M×N), rather than O(M+N) as it would be for an uncorrelated subquery.

An implication is that an IN subquery can be much slower than a query written using an IN(value_list) construct that lists the same values that the subquery would return.

2)关于把子查询转换成join的:

The optimizer is more mature for joins than for subqueries, so in many cases a statement that uses a subquery can be executed more efficiently if you rewrite it as a join.

An exception occurs for the case where an IN subquery can be rewritten as a SELECT DISTINCT join. Example:

SELECT col FROM t1 WHERE id_col IN (SELECT id_col2 FROM t2 WHERE condition);

That statement can be rewritten as follows:

SELECT DISTINCT col FROM t1, t2 WHERE t1.id_col = t2.id_col AND condition;

But in this case, the join requires an extra DISTINCT operation and is not more efficient than the subquery



Tags:sql查询   点击:()  评论:()
声明:本站部分内容及图片来自互联网,转载是出于传递更多信息之目的,内容观点仅代表作者本人,如有任何标注错误或版权侵犯请与我们联系(Email:2595517585@qq.com),我们将及时更正、删除,谢谢。
▌相关推荐
大家好,我是IT共享者,人称皮皮。今天给大家讲讲MySQL中的查询操作。前言我们在以前,大概学了一下如何通过命令创建数据库,创建表等基本操作。但是实际上啊,至少在我的实际工作...【详细内容】
2023-03-29  Tags: sql查询  点击:(4)  评论:(0)  加入收藏
背景运维反馈说线上一个接口越来越慢,大概二三十秒才有返回。查看接口代码,定位问题出在sql查询效率上。sql意图是将多个号码最新的数据返回给前端使用。单表数据量 530万左右...【详细内容】
2023-03-28  Tags: sql查询  点击:(0)  评论:(0)  加入收藏
我熟练应用ctrl c和ctrl v 开发curd代码好多年了。 mysql查询为什么会慢,关于这个问题,在实际开发经常会遇到,而面试中,也是个高频题。 遇到这种问题,我们一般也会想到是因为索引...【详细内容】
2022-07-12  Tags: sql查询  点击:(117)  评论:(0)  加入收藏
1、查找以前是否装有mysql命令:rpm -qa|grep -i mysql 可以看到如下图的所示:说明之前安装了: MySQL-client-5.5.25a-1.rhel5 MySQL-server-5.5.25a-1.rhel52、停止mysql服务...【详细内容】
2022-03-10  Tags: sql查询  点击:(230)  评论:(0)  加入收藏
初学者的数据库索引简介> Congratulations今天在ORM的时代,我们作为开发人员不必经常触摸数据库。我自己自己建立了我的第一个项目而不在项目内写一行的原始SQL。它起初工作...【详细内容】
2021-07-12  Tags: sql查询  点击:(224)  评论:(0)  加入收藏
SQL Select 语句完整的执行顺序:1、from 获取不同数据库表的数据;2、where 子句跟随筛选条件,对数据进行过滤;3、group by 子句可以将数据根据分组字段进行分组处理;4、可使用聚...【详细内容】
2021-04-27  Tags: sql查询  点击:(194)  评论:(0)  加入收藏
今天给大家分享的是MySQL性能优化,也是大数据开发指南MySQL的最后一部分。性能优化对于老刘来说,是必须掌握的一个手段,如何让自己变得更加优秀,这块内容还是好好看看!本篇内容相...【详细内容】
2021-02-07  Tags: sql查询  点击:(258)  评论:(0)  加入收藏
在冯小刚冯导作为导演拍摄的《天下无贼》中有一句经典台词,那就是出自葛优之口:21世纪什么最贵?人才!从这句话说出到现在,已经16年过去了,那么在现在这个大数据时代,什么最贵呢?那...【详细内容】
2020-12-30  Tags: sql查询  点击:(183)  评论:(0)  加入收藏
之前给大家介绍过,利用Excel内置的SQL查询引擎,来执行查询操作。通过编写SQL查询语句,可以完成Excel内置函数不能够完成的工作。带好笔纸做笔记吧,下面通过几个案例,介绍SQL查询...【详细内容】
2020-11-12  Tags: sql查询  点击:(620)  评论:(0)  加入收藏
写在前面MySQL数据库在互联网行业使用的比较多,有些小伙伴可能会认为MySQL数据库比较小,存储不了很多的数据。其实,这些小伙伴是真的不了解MySQL。MySQL的小不是说使用MySQL存...【详细内容】
2020-11-05  Tags: sql查询  点击:(220)  评论:(0)  加入收藏
▌简易百科推荐
最近做项目遇到一个问题,外键关联查询,返回长度为1,但是值为 All elements are null没数据。有两张表,分别为a表有数据,b表无数据。select b.* from a as a left join b as b on...【详细内容】
2023-03-30  青木川30  今日头条  Tags:Mybatis   点击:(7)  评论:(0)  加入收藏
译者 | 布加迪审校 | 孙淑娟​远程托管数据库可以将您的数据库与本地环境隔离开来,带来诸多好处,包括数据安全性、全局可访问性、可靠性、可扩展性和资源有效性。​您是否将数...【详细内容】
2023-03-29  布加迪  51CTO  Tags:数据库   点击:(11)  评论:(0)  加入收藏
背景运维反馈说线上一个接口越来越慢,大概二三十秒才有返回。查看接口代码,定位问题出在sql查询效率上。sql意图是将多个号码最新的数据返回给前端使用。单表数据量 530万左右...【详细内容】
2023-03-28  进击的程序猿  今日头条  Tags:sql查询   点击:(0)  评论:(0)  加入收藏
一、业务背景最近遇到这样一个场景:在业务正式开始前1-2天,需要导入一批来自合作渠道的数据,在业务周期结束后,再将同一批数据导出,交付给渠道方;简单理解,就是数据的「导入」和「...【详细内容】
2023-03-28  知了一笑  今日头条  Tags:数据   点击:(7)  评论:(0)  加入收藏
SQL(Structured Query Language)是用于管理和操作关系型数据库的标准化语言。它允许用户从数据库中检索和操作数据,以及创建、修改和删除表和其他数据库对象。在本篇文章中,我们...【详细内容】
2023-03-28  树言树语Tree  今日头条  Tags:SQL   点击:(7)  评论:(0)  加入收藏
TiDB 是一个分布式 NewSQL 数据库。它支持水平弹性扩展、ACID 事务、标准 SQL、MySQL 语法和 MySQL 协议,具有数据强一致的高可用特性,是一个不仅适合 OLTP 场景还适合 OLAP...【详细内容】
2023-03-28  码猿技术专栏  微信公众号  Tags:TiDB   点击:(7)  评论:(0)  加入收藏
自上世纪 70 年代起,关系数据库已走过半个世纪,如今成为社会的信息基础设施。随着时代、应用、场景的不断变更,给今天的数据库提出了更多要求,如敏捷伸缩、实时分析、降本增效等...【详细内容】
2023-03-28    CSDN  Tags:数据库   点击:(6)  评论:(0)  加入收藏
背景:随着业务的发展,我们同一套业务系统需支持提供给多个客户(不同的企业用户)使用,所以需确保在多用户环境下,各用户间数据的隔离。但目前系统在早期设计的时候没有考虑到多租户...【详细内容】
2023-03-24  IvanLan    Tags:数据   点击:(12)  评论:(0)  加入收藏
今天特地给大家汇总了一些目前市面上比较常用的ETL数据迁移工具,希望对你会有所帮助。前言最近有些小伙伴问我,ETL数据迁移工具该用哪些。ETL(是Extract-Transform-Load的缩写...【详细内容】
2023-03-24  苏三呀  微信公众号  Tags:数据迁移   点击:(11)  评论:(0)  加入收藏
数据库管理员(DBA)的职责是什么?在依赖 Postgres 作为主要数据库管理系统的现代 IT 组织中,Postgres DevOps DBA 发挥着关键作用。Postgres DevOps DBA 的角色涉及许多职责、技...【详细内容】
2023-03-22  Doug Ortiz  Linux中国  Tags:数据库   点击:(16)  评论:(0)  加入收藏
站内最新
站内热门
站内头条