关注我们获得更多精彩内容
讲师介绍：
李海龙，2012 年加入 Qunar，目前在网站运营中心担任 PostgreSQL DBA 总监。PostgreSQL 中国社区成员，热爱数据库技术, 乐于在公司内外分享使用中的各种经验。
SQL Fundamentals
SQL 全名是结构化查询语言（Structured Query Language），是用于数据库中的标准数据查询语言。传统来讲，SQL 语言分为三个部分：
（1） “数据定义语言”（DDL : Data Definition Language），用于定义 SQL 模式、基本表、视图和索引的创建和撤消操作。
（2）“数据操纵语言”（DML : Data Manipulation Language），数据操纵分为数据查询和数据更新两类。数据更新又分成插入、删除、和修改三种操作。
（3）“数据控制语言”（DCL : Data Control Language），包括对基本表和视图的授权，完整性规则的描述，事务控制等内容。 对应的 SQL 语句如下：
还有一类语句，即 BEGIN , COMMIT , SAVEPOINT , ROLLBACK ，这些是基于 ANSI SQL (最新标准为 SQL 2016 )的扩展，属于 Data Transaction Language, 有些资料直接将其归类于 DCL。个人记忆方法：你可以简单粗暴的记忆为：SQL 里就有10个最基本的动词（或者命令），然后分三大类，这样去记忆。
注:
在 Oracle 中 TRUNCATE 属于 DDL, 在 PostgreSQL 中 TRUNCATE 属于 DML。
Optimize Query
发给 DBMS 的所有的 DDL,DML,DCL 的可统称为 Query，俗称 SQL 语句。不是所有的 Query 都可以优化，DCL 基本不存在优化的问题，我们探讨优化的重点是 DML 及 DDL。
注意:
（1） Slow Query 优化， 包括但不限于我们日常实践中优化最多的 SELECT 语句
（2）Query 优化，不都是单纯的将 query 的运行时间提速 , 有时候需要在运行时间和 lock 粒度之间做出权衡。
本人以运维实践的角度出发, 将 Query 优化分为2类:
（1）保证 DML 返回的结果集(select)相同 或 更改的结果集(insert update delete) 相同的情况下，将 query 优化使其运行的更快
（2）保证 DML 或 DDL 操作结果相同的情况下，将 lock 的级别降低, lock 的范围 变小，提高 DBMS 的并发度。
本文先简单讲一下 Query 优化的基本原则，然后讲讲 Index 的类型及 Index 的维护，最后谈谈一些常见的可调参数。旨在使广大开发同学和初级 DBA 在做 Query 优化时有章可循 。
Some Cases in PostgreSQL
下面分类举几个实例，简单讲一下优化方法。
Case 1 Create Index Directly
总结:
这种方法是最常用的方法，需要注意一个数据选择比的问题，如果 where 条件是 update_flag = false
，
那么效果就不会有这么明显，因为如果 update_flag 字段值如果几乎都是 false，那么尽管有 index，其实和 Seq Scan 的时间也没什么差别，或者说差别不大。
Case 2 Change Conditions to Use Index
这类 SQL 就是本来 table 的 column 上有 index，然后 Query 没有用上。
public.order 上在 pay_time 上有 index ， 且是 bigint 类型，但是使用函数 to_timestamp(pay_time) 转换为 timestamptz 类型后，没有用上这个 index。
那么我们想办法改变 where 条件，使用到 index：
应尽量避免在 where 子句中对字段(索引列)进行运算，这将导致查询规划器放弃使用 index。如
应尽量避免在 where 子句中对字段类型进行强转，进而导致查询规划器放弃使用 index。如
应尽量避免在 where 子句中对字段与不同类型的常量(或字段)进行比较， 将导致隐式类型转换，进而导致查询规划器放弃使用 index。如
总结：
我们要想办法，灵活改变 where 中的查询条件，使用上 index，以加快我们的查询。
Case 3 Removal Unecessary Sub-query and Outer Join
上面的大长 SQL 是一个前端页面的 SQL，原运行时间是 3000ms，优化后 30ms，极大提升了页面的响应时间。
总结:
不影响得到正确的结果集前提下，结合业务逻辑, 少用 Outer Join；减少不必要的 Sub-query 层级数。
Case 4 Eliminating redundant columns
总结:
单纯看 Query 运行时间，可能优化效果并不十分明显，但如果考虑到计算中间结果所需的内存和磁盘空间及网络传输所耗带宽，SELECT * 应坚决避免。
Case 5 Indexes on Expressions
Indexes on Expressions 例1:
Indexes on Expressions 例2:
Indexes on Expressions 例3:
总结: 善于观察字段数据类型的操作符及函数，结合where condition，研究使用表达式index。
Case 6 Partial Indexes
注意:
不是所有的条件比较多的 query 都适合建 Partial Indexes，主要适合场景为: where 中的条件固定，且选择比好的情况下。
Case 7 Decompose DDL
很多 DDL 操作， 需要对 table 加 ACCESS EXCLUSIVE 这种高粒度的 Table-level Locks，所以权衡利弊，可以将其分解执行，以低粒度的 lock 及稍长的执行时间替换高粒度 lock。
PostgreSQL 11 的 New Feature 中，一个极为实用的特性就是 avoid a table rewrite fo r ALTER TABLE ... ADD COLUMN with a non-null column default ，无论多大的表，都可以秒级搞定。
Case 8 Comprehensive optimization
建 Multicolumn Indexes 和 Partial Indexes ,且分解执行
Case 9 Use CTE
CTE（Common Table Expressions），即通用表表达式，在 Slow query 中应用比较多的是 1 个较长 Query 中多次使用的同一 subquery 场景可以单拿出来先做 CTE，使 Query 提速且使 Query 更简洁，但此处谈及的是只使用 1 次的特殊 case。
例1:
总结: in 后面的 subquery 里的返回字段存在很大比例的重复值可以尝试 CTE。
例2：
总结: 当发现查询规化器没有使用最优的 index，可以尝试使用 CTE 调整查询计划。
注意:
并不是所有的 Query 使用 CTE 都有性能提升，此 case 更多是提供一个尝试优化的思路。
About Index
对 query 的优化，index 是最核心问题之一。
索引并不是越多越好，存储索引本身也有空间开销，扫描索引本身也有时间开销，索引固然可以提高相应的 query(不限于 select)的执行效率，但同时也降低了写入的效率，因为写入时有可能会维护索引，所以怎样建索引需要慎重考虑，视具体情况而定。
Index Types
PostgreSQL provides several index types: B-tree, Hash, GiST(Generalized Search Tree), SP-GiST(space-partitioned GiST) , GIN (Generalized Inverted Index) and BRIN(Block Range INdexes)。
各自适用范围简要说明：
（1）B-tree: 最常用的 index, 适合处理等值及范围 queries。
（2）Hash: 只能处理简单等值 queries， 但由于在 PostgreSQL 9.6 及之前的版本中， Hash index 的更改无法写入 WAL， 所以一旦实例崩溃重启，可能需要 reindex 或重建， 特别是有 Primary/Standby 结构的集群中，禁止使用 Hash index。 注意： 在 PostgreSQL10 做了优化，无此限制。
（3）GiST: 不是一种的简单 index 类型，而是一种架构,可以在这种架构上实现很多不同的 index 策略。 PostgreSQL 中的几何数据类型有很多 GiST 操作符类。 SP-GiST: GiST 的增强，引入新的 index 算法提高 GiST 在某些情况下的性能。
（4）GIN: 反转 index，又称广义倒排 index，它可以处理包括多个键的值， 如数组，Jsonb 等。
（5）BRIN: 首次出现于 9.5 中
。
BRIN 索引适用于数据值分布和物理值分布相关性很好的情况，比如我们的订单表中的 create_time 字段，时间的增量和数据位置的偏移一致，那么各块的独立性将会很好，不会出现交叉，这种情况就非常适合 BRIN。同理，流式日志数据的，比如用户行为的日志，大批量的数据按时间顺序不停的插入数据表也非常适合使用 BRIN 索引。BRIN 索引是有损索引，不能直接从索引中匹配需要查询的记录，但通过索引可以将查询范围有效缩小，而且远没有 BTree 索引那么臃肿;且由于 BTree 的维护成本要远高于 BRIN，所以数据插入效率 BRIN 也是高于 BTree 的。
更多信息参见