SQL调优

SQL（Structured Query Language：结构化查询语言）是用于管理关系数据库管理系统（RDBMS）。 SQL 的范围包括数据插入、查询、更新和删除，数据库模式创建和修改，以及数据访问控制。

很多公司的业务系统刚上线初期数据量都比较小，并且可能没有 DBA 这个岗位去对你开发项目中的 SQL 进行一个最终的审核，导致开发的时候写了一些慢 SQL，数据量小的时候可能还好，一旦数据量上来了，查询效能极低，并且请求次数过多的话很可能会因为这一个慢 SQL 把整个系统拖垮，不能正常对外提供服务。

SQL 优化的意思就在于此，按照关系型数据库的使用经验，我们总结了后续的一些 SQL 使用规范，在开发过程中遵循这些使用规范可以避免后续可能的 90% 以上 SQL 导致的性能问题产生。

捕获慢 SQL

SQL 优化的首要任务是识别和捕获慢 SQL，并获取到 SQL 执行时的必要绑定变量值

查询慢 SQL 语句分为以下两种情况：

• 查看历史慢 SQL 语句：

  通过观察 pg_stat_statements 视图，执行时间较长的 SQL 语句。

  SELECT query,max_exec_time FROM pg_stat_statements ORDER BY max_exec_time DESC LIMIT 10;
  

• 查看当前慢 SQL 语句。

  查询执行时间超过 1 秒的 SQL：

  SELECT * FROM pg_stat_activity WHERE state<>'idle' AND now()-query_start > interval '1 s' ORDER BY query_start ;
  

执行计划分析

通过使用 EXPLAIN 查询 SQL 计划，可分析 SQL 的快慢或资源占用情况。

获取执行计划

• 获取执行计划，不执行 SQL ：

  EXPLAIN SELECT * FROM table WHERE column = x; 
  

• 获取真实执行的执行计划 ：

  EXPLAIN analyze SELECT * FROM table WHERE column = x; 
  

• 获取真实执行的详细执行计划 ：

  EXPLAIN (analyze, verbose) SELECT * FROM table WHERE column = x; 
  

解析执行计划

执行计划的解读，按照从上往下，从右往左的原则进行。

定位异常的快速步骤：

• 时间递减法： 从起始步骤逐级往下查找，每次查找下层最耗时步骤，直到最后一层，图中 红色路径
• 关键步骤搜索： 直接在执行计划中搜索关键步骤，例如 Seq Scan/Removed

具体调优手段

SQL 逻辑改写

不同的 SQL 语句写法，对最终的执行计划生成有很大的影响，最终影响 SQL 的执行效率

• With 改写 ：可通过 with 将部分子查询结果缓存，并多次使用；同时简介干预表关联顺序
• 过滤条件内推：通过将有效的过滤条件内推到最内层子查询，可降低表关联的数据规模
• 函数计算外移：某些情况下，通过将函数计算移动到最外层，可减少函数计算次数
• 移除冗余关联 ：移除 SQL 中部分相同表的重复关联操作

通过 Hint 优化执行计划

AntDB 使用基于代价的优化器，优化路线使用统计数据而非固定的规则。对于一条 SQL 语句，优化器会去评估所有可能的代价并最终选择代价最低的去执行。优化器会尽力选择最好的执行计划，但由于其并不了解数据中可能存在的一些内在连接关系，导致这些执行计划可能并不完美。当判断数据库优化器生成的执行计划不是最优的时候，可以在不修改 SQL 的情况下（例如：等价改写、非等价改写）通过 pg_hint_plan 扩展插件，可以直接干预执行计划的生成，产生符合预期的执行计划。

加载插件：

• 必须执行 create extension pg_hint_plan，不然找不到 hint_plan.hints 表。

• 修改 postgresql.conf 中的 shared_preload_libraries 参数，修改完重启数据库。

  shared_preload_libraries = 'pg_hint_plan'
  

支持的 hint 提示类型介绍：

根据提示短语影响执行计划的方式，支持的提示类型包括扫描方法提示、连接方法提示、连接顺序提示、行数校正提示、并行执行提示和 GUC 参数设置提示。

扫描方法提示

扫描方法提示对目标表强制执行特定的扫描方法，pg_hint_plan 通过表的别名（如果存在的话）来识别目标表。扫描方法可能是序列扫描、索引扫描等。

扫描提示对普通表、继承表、无日志表、临时表和系统表有效。对外部表、表函数、常量值语句、通用表达式、视图和子查询无效。

示例命令如下：

/*+
    SeqScan(t1)
    IndexScan(t2 t2_pkey)
 */
 SELECT * FROM table1 t1 JOIN table table2 t2 ON (t1.key = t2.key);

连接方法提示

连接方法提示强制指定相关表聚合在一起的方法。对普通表、继承表、无日志表、临时表、外部表、系统表、表函数、常量值命令和通用表达式有效。对视图和子查询无效。

连接顺序提示

连接顺序提示指定两张或多张表的连接顺序。包括两种强制指定方法：

• 强制执行特定的连接顺序，但不限制每个连接级别的方向。
• 强制连接方向。

示例命令如下：

 /*+
    NestLoop(t1 t2)
    MergeJoin(t1 t2 t3)
    Leading(t1 t2 t3)
  */
 SELECT * FROM table1 t1
     JOIN table table2 t2 ON (t1.key = t2.key)
     JOIN table table3 t3 ON (t2.key = t3.key);

说明：

• NestLoop(t1 t2)：指定表 t1 和 t2 的连接方法。
• MergeJoin(t1 t2 t3)：指定表 t1、t2 和 t3 之间的连接方法。
• Leading(t1 t2 t3)：指定三张表的连接顺序。

行数校正提示

行数校正提示会校正由于查询优化器限制而导致的行数错误。

 /*+ Rows(a b #10) */ SELECT... ; # 设置连接结果的行数为10
 /*+ Rows(a b +10) */ SELECT... ; # 行数增加10
 /*+ Rows(a b -10) */ SELECT... ; # 行数减去10
 /*+ Rows(a b *10) */ SELECT... ; # 行数增大10倍

并行执行提示

并行执行提示会指定并行的执行计划。

并行级别提示对普通的表、继承表、无日志表和系统表有效。对外部表、常量从句、通用表达式、视图和子查询无效。视图的内部表可以通过其真实名称或别名指定目标对象。

下面两个示例说明在每张表上执行查询的方式不同：

• 方式一：指定表 c1 的并行度为 3，表 c2 的并行度为 5。

  EXPLAIN /*+ Parallel(c1 3 hard) Parallel(c2 5 hard) */
       SELECT c2.a FROM c1 JOIN c2 ON (c1.a = c2.a);
  

  返回结果如下：

                                  QUERY PLAN
  -------------------------------------------------------------------------------
  Hash Join  (cost=2.86..11406.38 rows=101 width=4)
   Hash Cond: (c1.a = c2.a)
   ->  Gather  (cost=0.00..7652.13 rows=1000101 width=4)
         Workers Planned: 3
         ->  Parallel Seq Scan on c1  (cost=0.00..7652.13 rows=322613 width=4)
   ->  Hash  (cost=1.59..1.59 rows=101 width=4)
         ->  Gather  (cost=0.00..1.59 rows=101 width=4)
               Workers Planned: 5
               ->  Parallel Seq Scan on c2  (cost=0.00..1.59 rows=59 width=4)
  
  

• 方式二：指定表 t1 的并行度为 5。

  EXPLAIN /*+ Parallel(tl 5 hard) */ SELECT sum(a) FROM tl;
  

  返回结果如下：

                                    QUERY PLAN
  -----------------------------------------------------------------------------------
  Finalize Aggregate  (cost=693.02..693.03 rows=1 width=8)
   ->  Gather  (cost=693.00..693.01 rows=5 width=8)
         Workers Planned: 5
         ->  Partial Aggregate  (cost=693.00..693.01 rows=1 width=8)
               ->  Parallel Seq Scan on tl  (cost=0.00..643.00 rows=20000 width=4)
  

• GUC 参数设置提示

  在执行查询的过程中改变 GUC 参数的值。此值仅在执行器生成查询计划期间有效，并且不会对其它语句产生影响。如果对同一个GUC 参数进行多次设置，则以最后一个为准。

  示例命令如下：

  /*+ Set(random_page_cost 2.0) */
  SELECT * FROM table1 t1 WHERE key = 'value';
  

索引优化

索引可以有效的降低数据访问的规模，不同的场景需要不同类型的索引进行优化。

索引类别

AntDB 中，常用的索引类型如下：

• B-Tree 索引 ： 最常使用的数据库索引，也是数据库默认的索引类型，广泛用于通用的大批量数据过滤。包括： 一般 B-Tree 索引，唯一索引，函数索引，部分索引，复合索引等
• 哈希索引 ： 只在等值查询中使用，提供等值查询记录的快速访问，适用于字段值较长不适合 B-Tree 的部分场景
• Gist 索引 ： 通用搜索树索引，可作为索引模板实现任意的索引模式，通常用于多维数据类型或集合类型中
• Gin 索引 ： 通用倒排索引，通常用于全文搜索，数组元素索引，同样也可扩展到其他类型或自定义类型
• Brin 索引 ： 基于数据块级别的范围索引，记录一批数据块的的统计信息（最大，最小，Count, Sum 等），空间占用极小

使用场景

B-Tree 索引是绝大部分情况下都适用的索引，其可使用场景分类如下：

• 等值查询： 对索引列的等值查询，例如： col = 123
• 范围查询： 对索引列的范围查询，例如： col between 1 and 10, col < 10
• 模糊查询： 部分右侧扩展的模糊查询，例如： col like 'test%'

注意事项

对于 B-Tree 索引，在使用过程中需要注意以下事项，不当使用可能导致无法正常使用索引：

• 不等于条件 ： 使用 != 过滤条件，无法使用索引
• 在索引列上计算 ： 包括使用函数计算和其他类型的计算转换，其中函数计算可通过函数索引解决
• 使用 or 过滤条件 ：在过滤条件中，多个条件使用 or 组合，可能导致无法使用索引
• not in 条件 ： 基本等同于不等于条件，也无法使用索引
• in 条件 ： 若 in 列表过长，可能无法使用索引
• like 条件 ： 在 like 条件中，若关键词前后均有 % 通配符，则无法使用索引
• 选择性差 ： 优化器在判断是否使用索引的过程中，会通过统计信息，判断过滤条件的选择性，选择性太差可能无法使用索引
• 统计信息不足 ： 执行计划的生成，严重依赖于表和索引的统计信息，若统计信息不足，则可能导致无法使用索引

注意： 索引的使用与否，最终要取决于优化器在当前数据库环境下的综合评估