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

怎么优化你的SQL查询?以PostgreSQL为例

时间:2019-09-05 09:20:25  来源:  作者:

实际工作中,我们每个人难免都会要写SQL,执行SQL,但是有时时候执行非常慢,甚至获得不了结果。这时候你会怎么办?放弃?去苦口婆心的求隔壁房间胡子擦擦的猥琐DBA大叔?

怎么优化你的SQL查询?以PostgreSQL为例

 

NO,正确方法是先检查一下你的SQL语句。本文虫虫给你列出来用来排查SQL查询比较慢的常见方法和对策。文中所有方法和例子均基于PostgreSQL,当然这些都可以快速移植到MySQL和其他数据库,因为SQL语句基本上都是相通的。

了解现状

首先,需要先清楚当前数据的环境情况。数据库是不是很繁忙?有多少用户在线,多少查询在执行?当时失败正处在高峰期?

对策:

可以通过询问数据库来了解数据库当前状态。不需要你去@ DBA或者运维,你只需执行SQL语句就可以获得这些信息:

我们可以通过以下语句列出当前所有运行的和空闲的查询:

select * from pg_stat_activity

下面的语句查找导致锁表的查询:

select pid,usename,pg_blocking_pids(pid) as blocked_by,query as blocked_query from pg_stat_activity where cardinality(pg_blocking_pids(pid))> 0;

表当时正在更新吗?如果你查询时候恰好遇到ETL进程在更新被锁定的表,你也就无法对其查询。

对策:

了解这些ETL更新执行时间,避开这些时间再执行查询。

有针对性的查询

知道了当前数据库的状态。现在可以具体从你的SQL语句入手了。首先看你的SQL语句:SELECT * from XXX

咦,为啥要 SELECT * ?

对策:

如果知识为了了解表的结构,请从模式树获取表字段。

d 表名
怎么优化你的SQL查询?以PostgreSQL为例

 

为了执行更快,只SELECT具体的字段值,不要用SELECT * ;

如果有一个特别大的表或宽表(表示字段很多),查询引擎不可能将所有数据都取过来。使用'LIMIT'来限制查询,如果你确实需要关注每一行的内容那另说;

如果要COUNT计算,不要运行查询通过查询结果底部统计行数来获取统计,请使用计算行数的子查询:

select count(*) from
(
select
id
from users
where preferred_language = 'zh_CN'
and private_profile = True
) as temp;
怎么优化你的SQL查询?以PostgreSQL为例

 

大小写

PostgreSQL是区分大小写的,这对于windows下用户习惯SQL Serve的人来说可能有点别扭。

对策:

如果"小写化"或"大写化"数据,比较费劲,在将数据加入查询中之前,先查看字段的形式。

如果在join时候需求,请仅在join一侧使用;尝试使用ILIKE进行不区分大小写的匹配。

避免使用NOT IN

尽量避免使用"IN"或"NOT IN"。此操作需要全表扫描,查询引擎需要对比每一行数据以检查是否满足条件。

对策:

尝试使用"EXCEPT"或"NOT EXISTS",这些对查询计划的影响远小于"NOT IN"。

CTE

怎么优化你的SQL查询?以PostgreSQL为例

 

CTE(公共表达式)比子查询更易于阅读,但在PostgreSQL中该角色优化有限,查询优化器无法对其变动约束条件实现查询优化。

对策:

CTE和子查询虽然都很有用,但是都有其适用范围。使用CTE时候请考虑表大小,可能返回的行数以及写入时在CTE中执行的操作。

通配符和模糊查询

在LIKE的开头和结尾使用通配符会降低查询效率。并且可能会获得比预期更多的结果。

对策:

在必需地方使用通配符,通常简易,只在LIKE后的开头或者结尾一端使用:

select name, email,location from users where name like 'CC%';

怎么优化你的SQL查询?以PostgreSQL为例

 

尝试写入一张表

将几个嵌套查询用作函数进行操作非常昂贵,这时候尝试写入表会更快。

对策:

如果流程有很多步骤,请考虑创建临时表,以便加入较小的数据子集。

视图的视图

视图是引用查询运行的查询结果。如果要调用多个视图,或者更复杂情况下访问视图的视图,要求查询引擎运行多个查询返回结果。

对策:

如果需要每天/每周/每月等定期的查询快照,不是动态过滤的查询视图,请使用定期结果入表,而不要用视图。

如果要使用嵌套视图,请考虑是否有更直接的方法通过编写查询来获取所需的结果,不要使用多个查询的嵌套视图。

索引

索引通过对数据字段序列化来加速查询,可以以让数据库引擎快速定位数据的位置。索引类型决定了索引的工作方式。

对策:

对数据表中需要经常查询的,使用频繁的字段(或者字段组合)加索引。

评估表中现存的索引确保表中没有太多的索引或者有无用的索引。

总结

本文列出了SQL查询中常见可能会导致性能问题事项,并提供具体对策用以优化。当然这些只是给出了一般性质的建议,针对具体问题具体分析才是解决问题的关键。



Tags:SQL查询   点击:()  评论:()
声明:本站部分内容来自互联网,转载是出于传递更多信息之目的,内容观点仅代表作者本人,如有任何标注错误或版权侵犯请与我们联系,我们将及时更正、删除,谢谢。
▌相关评论
发表评论 共有条评论
用户名: 密码:
验证码: 匿名发表
▌相关推荐
连接查询的优化无论什么数据库,多表连接的查询成本都是比较高的,因此对于高并发应用,应该尽量减少有连接的查询,多表连接的个数不要超过4张表。一般数据量少的时候,连接开小不...【详细内容】
2019-12-17   SQL查询  点击:(59)  评论:(0)  加入收藏
我们公司的数据量非常大,需要的不仅仅是提取数据,要了解SQL方案优化的。一般在写SQL时需要注意哪些问题,可以提高查询的效率? 【解题思路】数据量大的情况下,不同的SQL语句,消耗的...【详细内容】
2019-11-11   SQL查询  点击:(115)  评论:(0)  加入收藏
手把手教你MySQL查询优化分析前言MySQL是关系性数据库中的一种,查询功能强,数据一致性高,数据安全性高,支持二级索引。但性能方面稍逊于非关系性数据库,特别是百万级别以上的数...【详细内容】
2019-11-11   SQL查询  点击:(53)  评论:(0)  加入收藏
SQL语句执行顺序 结合上图,整理出如下伪SQL查询语句。 从这个顺序中我们可以发现,所有的查询语句都是从 FROM 开始执行的。在实际执行过程中,每个步骤都会为下一个步骤生成一个...【详细内容】
2019-11-11   SQL查询  点击:(31)  评论:(0)  加入收藏
本篇文章将通过一条 SQL 的执行过程来介绍 MySQL 的基础架构。首先有一个 user_info 表,表里有一个 id 字段,执行下面这条查询语句:select * from user_info where id = 1;返回...【详细内容】
2019-10-09   SQL查询  点击:(40)  评论:(0)  加入收藏
一般来说,SQL查询优化器分析给定查询的许多选项,预估每个选项的成本,最后选择成本最低的选项。如果查询优化器选择了错误的计划,则性能差异可能从几毫秒到几分钟。幸运的是,现在有许多第三方SQL查询优化工具可以自动优化每...【详细内容】
2019-09-05   SQL查询  点击:(90)  评论:(0)  加入收藏
实际工作中,我们每个人难免都会要写SQL,执行SQL,但是有时时候执行非常慢,甚至获得不了结果。这时候你会怎么办?放弃?去苦口婆心的求隔壁房间胡子擦擦的猥琐DBA大叔? NO,正确方法是先...【详细内容】
2019-09-05   SQL查询  点击:(110)  评论:(0)  加入收藏
最近在联调某个业务时发现使用的签名总是验证不过,该业务根据如用户名userName后加了空格依然能够根据userName查询到结果。即select * from user where username = "asdf"...【详细内容】
2019-08-30   SQL查询  点击:(172)  评论:(0)  加入收藏
SQL语言,是结构化查询语言(Structured Query Language)的简称。SQL语言是一种数据库查询和程序设计语言,用于存取数据以及查询、更新和管理关系数据库系统;同时也是数据库脚本...【详细内容】
2019-07-30   SQL查询  点击:(85)  评论:(0)  加入收藏
最新更新
栏目热门
栏目头条