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

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

文章插图

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 表名

文章插图

为了执行更快，只SELECT具体的字段值，不要用SELECT * ；
如果有一个特别大的表或宽表（表示字段很多），查询引擎不可能将所有数据都取过来。使用'LIMIT'来限制查询，如果你确实需要关注每一行的内容那另说；
如果要COUNT计算，不要运行查询通过查询结果底部统计行数来获取统计，请使用计算行数的子查询：
select count(*) from(selectidfrom userswhere preferred_language = 'zh_CN'and private_profile = True) as temp;

文章插图

大小写PostgreSQL是区分大小写的，这对于windows下用户习惯SQL Serve的人来说可能有点别扭。
对策：
如果"小写化"或"大写化"数据，比较费劲，在将数据加入查询中之前，先查看字段的形式。
如果在join时候需求，请仅在join一侧使用；尝试使用ILIKE进行不区分大小写的匹配。
避免使用NOT IN尽量避免使用"IN"或"NOT IN" 。此操作需要全表扫描，查询引擎需要对比每一行数据以检查是否满足条件。
对策：
尝试使用"EXCEPT"或"NOT EXISTS" ，这些对查询计划的影响远小于"NOT IN" 。
CTE

文章插图

CTE（公共表达式）比子查询更易于阅读，但在PostgreSQL中该角色优化有限，查询优化器无法对其变动约束条件实现查询优化。
对策：
CTE和子查询虽然都很有用，但是都有其适用范围。使用CTE时候请考虑表大小，可能返回的行数以及写入时在CTE中执行的操作。
通配符和模糊查询在LIKE的开头和结尾使用通配符会降低查询效率。并且可能会获得比预期更多的结果。
对策：
在必需地方使用通配符，通常简易，只在LIKE后的开头或者结尾一端使用:
select name, email,location from users where name like 'CC%';