SQL语句调优相关方法

SQL语句慢的原因：
1，数据库表的统计信息不完整
2，like查询估计不准确
调优方法：
1，查看表中数据的条数；
2， explain analyze target_SQL;
查看SQL执行计划；
比较SQL总执行时间和各个部分actual time的大小
可以更改SQL的执行顺序
可以执行部分SQL语句逐步分析慢的地方；
有问题的SQL，row估计有问题。
函数调整为比较常量
postgresql join_colapse

 1 PostgreSQL SQL的性能调试方法--借助统计信息

在数据库应用开发中，速度慢的SQL比比皆是。很多速度很慢都是SQL写的不好，效率不高。比如无用的循环查询，判断，
不必要的子查询，写的SQL用不上索引等等。特别是数据量很大的时候，很是头疼。
几千万条数据的表的查询，由于子查询过多，要几个小时才执行完。显然是不符合要求的。

     要解决这个速度问题 ，我们首先最主要的是要找到那些SQL很慢，或者SQL中的那部分很慢。怎样寻找速度很慢的SQL，我 们 可以借助系 统 提供的 统计 信息功能来 查 找。
   1.pg_stat_user_functions :SQL文中用了存储过 程或者函数的情况，可以通 过这 个 统计 信息view来查看。
    funcid, schemaname, funcname :函数的ID
    calls : 执行次数
    total_time : 函数执行的总时间，单位毫秒。
    self_time : 不包含其他函数的执 行 时间 的自己本身的 执 行 时间 。
例子：     
=# SELECT * FROM pg_stat_user_functions;
  funcid | schemaname | funcname | calls | total_time | self_time
--------+------------+----------+-------+------------+-----------
  16434 | public     | proc_1   |     4 |        185 |       185
  16738 | public     | proc_2   |     5 |         91 |        91
  16741 | public     | proc_3   |     2 |         76 |         5
(3 rows)
从这里面我们可以看到proc_3自己自身执行的时间相对于总的时间来说耗费的时间非常少，
可以初步确定速度慢的函数不是proc_3。因此我就需要去查询其他函数的执行时间。
这个机能系统默认是没有的，需要在postgresql.conf 里面设置。默认是none。需要改为pl或者all。
 track_functions = pl # none, pl, all
2.pg_stat_statements ：contrib/pg_stat_statements模块下的机制，系统默认也是没有的。
    userid : 执行SQL的用户  
    dbid : 数据库 ID
    query : SQL文
    calls : 执行次数
    total_time : 执行总时间，单位微秒。
    rows : 处理行数。返回的行数或者修改的行数。
    
例子：     
SELECT query, calls, total_time, rows
     FROM pg_stat_statements ORDER BY total_time DESC LIMIT 3;
-[ RECORD 1 ]------------------------------------------------------------
query      | UPDATE branches SET bbalance = bbalance + $1 WHERE bid = $2;
calls      | 3000
total_time | 35.9654100
rows       | 3000
      
这个功能也是需要在postgresql.conf 里面设置。
    shared_preload_libraries = 'pg_stat_statements'
    custom_variable_classes = 'pg_stat_statements'
通过上面的两种方法我们就可以找到那些SQL或者函数很慢，这样就能够对症下药了，
要不然真是找不原因，很耗费时间。


 2 PostgreSQL SQL的性能调试方法--数据库log分析

1.log_min_duration_statement

从log找出执行超过一定时间的 SQL。postgresql.conf配置文件设置 log_min_duration_statement参数的值。
这个参数是设置执行最小多长时间的SQL输出到log。
例如输出执行超过 3秒的SQL:log_min_duration_statement = 3s
这个参数设置为 -1是无效。 设置为 0是输出所有的SQL，但这样会增加服务器负担，一般不要设置太低的值。
这样设置后输出的SQL例子如下：
LOG: duration: 3016.724 ms statement: SELECT count(*)
   FROM pg_class
 
2.contrib/auto_explain功能。Postgres8.4后增加的功能。
   默认这个功能不能使用的，需要在postgresql.conf 配置文件中设置以下参数。
       shared_preload_libraries = 'auto_explain'
       custom_variable_classes = 'auto_explain'
       auto_explain.log_min_duration = 4s
    这样系统在执行的时候如果遇到超过4秒的SQL的话，会自动把执行计划输出到log。这样就直接看log就更加容易找到问题点。

    执行计划例子：

    LOG:  duration: 4016.724 ms  plan:
    Aggregate  (cost=14.90..14.91 rows=1 width=0)
     -> Hash Join  (cost=3.91..14.70 rows=81 width=0)
         Hash Cond: (pg_class.oid = pg_index.indrelid)
          -> Seq Scan on pg_class  (cost=0.00..8.27 rows=227 width=4)
          -> Hash  (cost=2.90..2.90 rows=81 width=4)
                -> Seq Scan on pg_index  (cost=0.00..2.90 rows=81 width=4)
                     Filter: indisunique
    STATEMENT:  SELECT count(*)
              FROM pg_class, pg_index
             WHERE oid = indrelid AND indisunique;
 
3.log统计分析工具（PostgreSQL log analyzer）

比较有名是pgFouine 。这个工具是自动分析指定的log，然后生成HTML报表。把SQL log图像化后更加直观。

可以统计分析最慢的SQL，调用最多的SQL，花费时间最多的SQL等等分类。这样我们就很容易找到速度慢的SQL。再加以改善。

3 PostgreSQL SQL的性能调试方法--查看执行计划

利用pgAdmin中的自带的 查看 执行计划工具。 我 们 用的最多的就是 这 个工具， 这 个工具因 为 不需要其他什么配置或安装。
这个工具也是PostgreSQL主打的调试工具。
1.下面简单介绍一下执行计划结果的读法
①Explaining → Widths 　
　=# EXPLAIN SELECT oid FROM pg_proc;
QUERY PLAN
------------------------------------------
Seq Scan on pg_proc
(cost=0.00..87.47 rows=1747 width=4)
查询结果的所有字段的总宽度。这个参数并不是关键指标。每个字段的宽度定义如下：
  　text [ n 文字]：n+４
　　varchar(n)：n+1
　　char(n)：n+1
    boolean：1
  　bigint ：8
  　integer：4
 
②Explaining → Rows
   预测的行数。与实际的行数可能有出入，经常vacuum或者analyze的话，这个值和实际值将更加接近。
 
③Explaining → Cost
　cost是比较重要的指标。例子中的cost=0.00..87.47有两个部分，启动时间（startup）=0.00 和总时间（total）=87.47。单位是毫秒。这个指标也只是预测值。
启动时间也有解释为找到符合条件的第一行所花的时间。
 
④Explaining → Explain Analyze
   想知道实际的执行时候的执行计划的话，用这个命令。
=# EXPLAIN ANALYZE SELECT oid FROM pg_proc;
QUERY PLAN
------------------------------------------
Seq Scan on pg_proc
(cost=0.00..87.47 rows=1747 width=4)
(actual time=0.077..17.082 rows=1747 loops=1)
Total runtime: 20.125 ms
  loops：循环的次数。
  Total runtime:总的时间
 
⑤Explaining → 执行计划运算类型
    Seq Scan：扫描表。无启动时间。
    Index Scan：索引扫描。无启动时间。
    Bitmap Index Scan：索引扫描。有启动时间。
    Bitmap Heap Scan：索引扫描。有启动时间。
    Subquery Scan：子查询。无启动时间。
    Tid Scan：ctid = …条件。无启动时间。
    Function Scan：函数扫描。无启动时间。
    Nested Loop：循环结合。无启动时间。
    Merge Join：合并结合。有启动时间。
    Hash Join：哈希结合。有启动时间。
    Sort：排序，ORDER BY操作。有启动时间。
    Hash：哈希运算。有启动时间。
    Result：函数扫描，和具体的表无关。无启动时间。
    Unique：DISTINCT，UNION操作。有启动时间。
    Limit：LIMIT，OFFSET操作。有启动时间。
    Aggregate：count, sum,avg, stddev集约函数。有启动时间。
    Group：GROUP BY分组操作。有启动时间。
    Append：UNION操作。无启动时间。
    Materialize：子查询。有启动时间。
    SetOp：INTERCECT，EXCEPT。有启动时间。

 
下面是一个hash，hash join例子：
=# EXPLAIN SELECT relname, nspname FROM pg_class JOIN
pg_namespace ON (pg_class.relnamespace=pg_namespace.oid);
QUERY PLAN
------------------------------------------------------------------------
Hash Join (cost=1.06..10.71 rows=186 width=128)
  Hash Cond: ("outer".relnamespace = "inner".oid)
     -> Seq Scan on pg_class (cost=0.00..6.86 rows=186 width=68)
     -> Hash (cost=1.05..1.05 rows=5 width=68)
        -> Seq Scan on pg_namespace (cost=0.00..1.05 rows=5 width=68)
两个表间 INNER JOIN和LEFT OUTER JOIN 连接的时候，这个运算是很常用的。 这个运算是先把外表中关联条件部分做一个哈希表，然后去和内部表关联。
下面是一个Nested Loop例子：
=# SELECT * FROM pg_foo JOIN pg_namespace
ON (pg_foo.pronamespace=pg_namespace.oid);
QUERY PLAN
----------------------------------------------------------------------
Nested Loop (cost=1.05..39920.17 rows=5867 width=68)
   Join Filter: ("outer".pronamespace = "inner".oid)
      -> Seq Scan on pg_foo (cost=0.00..13520.54 rows=234654 width=68)
        -> Materialize (cost=1.05..1.10 rows=5 width=4)
        -> Seq Scan on pg_namespace (cost=0.00..1.05 rows=5 width=4)

两个表间 INNER JOIN和LEFT OUTER JOIN 连接的时候，这个运算是很常用的。这个运算是扫描外表，然后去内部找所符合条件的记录。
哈希运算要做一张哈希表，如果外部的tb1的数据不是特别多的时候是比较快的。如果tb1相当大，这时候做一张可能花时候反而更多。

因为做的哈希表内存装不下，需要输出到硬盘，这样IO读取多了，速度就低下了。
合并查询对外部和内部表都要用两个表的关联字段各自做一张，并且还要排序。
如果是已经拍好序的，速度是很快。这中运算有数据特大的时候

还有可能不如循环结合快。
因此我们有个别地方可能直接用系统默认的执行计划的话反而很慢。如果是那样的话，可以尝试强制改变执行计划。禁止使用hash结合或合并结合。

这个要具体问题具体分析。
2. 强制改 变执行计划
   有时候我们不想用系统默认的执行计划。这时候就需要自己 强制控制 执行计划。
   禁止某种运算的SQL语法： SET enable_运算 类型 = off;   //或者=false
   开启某种运算的SQL语 法：SET enable_运算 = on;   //或者=true
   执行计划可以改 变 的运算方法如下：
   – enable_bitmapscan
   – enable_hashagg
   – enable_hashjoin
   – enable_indexscan
   – enable_mergejoin
   – enable_nestloop
   – enable_seqscan
   – enable_sort
   – enable_tidscan
   如果我们只想改变当前要执行的SQL的执行计划，而不想影响其他的SQL的话。在设置SQL里面加一个关键字session即可。
   例子： set session enable_hashjoin=false   //禁止使用哈希结合算法
 
通过查看执行计划，我们就能够找到SQL中的哪部分比较慢，或者说花费时间多。然后重点分析哪部分的逻辑，
比如减少循环查询，或者强制改变执行计划。