PostgreSQL SQL优化用兵法，优化后提高 140倍速度

开头还是介绍一下群，如果感兴趣PolarDB ,MongoDB ,MySQL ,PostgreSQL ,Redis, OceanBase, Sql Server等有问题，有需求都可以加群群内有各大数据库行业大咖，可以解决你的问题。加群请联系 liuaustin3 ，（共2720人左右 1 + 2 + 3 + 4 +5 + 6 + 7 + 8 +9）(1 2 3 4 5 6群均已爆满，7群430+，开8群170+ 9群)

今天的领悟，人生没有白走的，对错都算数，做完一些事情，经常会后悔，如果我当时选择了A，就好了，可我当时选择的是B，然后不断地悔恨。人的眼睛都是长在前面的，选择就选择了，错了就错了，错了吸收教训，不要再犯就好，当时我也站在迷雾中，也很迷茫，哪怕在给我一次机会，还是会选择B，与其后悔，不如持续修炼--- 一个现实世界的“申公豹”留言。

SQL 的优化是DBA工作的主题，这里我想以这篇文章，将SQL优化的方式方法，以及程序员的撰写SQL的问题说清楚，如果有不周之处还请各位老师指点一二。

SQL 本身是什么，完成什么功能这点做DBA的同学要清晰。

1  SQL是程序完成数据输入，输出的必经之路

2  SQL是程序搜取数据不多方案中的最有效的数据提取方案

3  SQL除了必备的数据提取功能，还有数据处理和计算的功能

4  SQL可能完成了程序功能的一部分，甚至是程序核心的一部分

5  SQL是大部分程序中的热点，是一个程序运行好坏的至关重要的一部分

明白了这些SQL的运行的好坏与程序之间的关系就明确了。

为什么SQL就不能一次性写好

1  程序生成的SQL，通过程序将代码转换为SQL，这样的SQL明显不能进行优化的写法实现，在系统初步运行的过程可以接受这样的写法，但在系统高负载的情况下，则这样的SQL就是问题的爆发点。

2  乱建索引，这点很常见，索引的好坏影响SQL的运行。一个好的索引可以助力SQL快速的运行，一个差劲的索引是SQL运行的阻碍，乱建的方式

1  每个字段建立一个索引

2  将很多的字段建立索引

3  不按照语句的规律和查询的字段顺序和方式建立索引

4  索引重复建设 （ORACLE没有这个问题，其他都有）

5  语句作废后的无用索引的滞留

3  业务持续变化，SQL语句从简单到复杂，程序员把SQL当程序写，希望一个SQL解决所有需求（这是产生问题的关键）

下面我们用一个SQL和执行计划来说明如何快速的分析一个PG的SQL的优化点。咱们先看语句

这个语句有几个问题

1  语句中使用了数据源嵌套方式，也就是select * from (select * from )我们俗称的子查询，子查询实际上也没有什么问题，关键是子查询中的SQL复杂度和SQL所过滤后的数据量。在子查询中的过滤后的数据量较大的情况下就会产生临时表，SQL过于复杂会增加产生执行计划准确度的难度。所以我们在撰写SQL都希望通过 join的方式来表达表和表之间的关系，并获得关系后的并集，交集等数据结果。

SELECT
  ata.planId,
  ata.detail_di,
  ata.item_di,
  ata.item_code,
  ata.item_name,
  ata.item_pinyin,
  ata.unit_di,
  ata.unit_name,
  ata.enable_muti_size,
  ata.small_class_di,
  ata.small_class_code,
  ata.small_class_name,
  ata.lane_di,
  ata.lane_name,
  ata.standardTime,
  ata.warnTime,
  SUM(ata.maxQty) AS maxQty
FROM
  (
    SELECT
      kifd.itemfilter_di AS planId,
      kifd.detail_di,
      ai.di AS item_di,
      ai.code AS item_code,
      ai.NAME AS item_name,
      ai.pinyin AS item_pinyin,
      ai.unit_di,
      f_get_unit_name (ai.unit_di) AS unit_name,
      ai.enable_muti_size,
      aic2."di" AS small_class_di,
      aic2.code AS small_class_code,
      aic2.NAME AS small_class_name,
      kifd.lane_di,
      kifl.lane_name AS lane_name,
      ki.standard_time AS standardTime,
      ki.warn_time AS warnTime,
      bmcd.maxqty AS maxQty
    FROM
      kc_itemfilter AS kif
      INNER JOIN kc_itemfilter_detail AS kifd ON kif.di = kifd.itemfilter_di
      AND kifd.setting_mode = 0
      LEFT OUTER JOIN kc_itemfilter_lane AS kifl ON kifd.lane_di = kifl.di
      INNER JOIN _item AS ai ON ai.di = kifd.detail_di
      AND ai.delflg = 0
      AND ai.is_enable = TRUE
      AND ai.is_package = FALSE
      AND ai.create__di = '160539'
      INNER JOIN _item_class AS aic2 ON ai.small_class_di = aic2.di
      AND aic2.delflg = 0
      AND aic2.LEVEL = 1
      AND aic2.create__di = '160539'
      LEFT JOIN kc_item AS ki ON ai.di = ki.item_di
      AND ki.item_size_di = -1
      AND ki.delflg = 0
      AND ki.belong__di = '160540'
      LEFT JOIN bm_machine_combine_dishes AS bmcd ON ai.di = bmcd.item_di
      AND bmcd.size_di = -1
      AND bmcd.delflg = 0
      AND bmcd.create__di = ai.create__di
    WHERE
      kifd.itemfilter_di IN ('16053900000000001')
    UNION
    SELECT
      kifd.itemfilter_di AS planId,
      kifd.detail_di,
      ai.di AS item_di,
      ai.code AS item_code,
      ai.NAME AS item_name,
      ai.pinyin AS item_pinyin,
      ai.unit_di,
      f_get_unit_name (ai.unit_di) AS unit_name,
      ai.enable_muti_size,
      aic2."di" AS small_class_di,
      aic2.code AS small_class_code,
      aic2.NAME AS small_class_name,
      kifd.lane_di,
      kifl.lane_name AS lane_name,
      ki.standard_time AS standardTime,
      ki.warn_time AS warnTime,
      bmcd.maxqty AS maxQty
    FROM
      kc_itemfilter AS kif
      INNER JOIN kc_itemlter_detail AS kifd ON kif.di = kifd.itemfilter_di
      AND kifd.setting_mode <> 0
      LEFT OUTER JOIN kc_iteilter_lane AS kifl ON kifd.lane_di = kifl.di
      INNER JOIN _item AS ai ON ai.small_class_di = kifd.detail_di
      AND ai.delflg = 0
      AND ai.is_enable = TRUE
      AND ai.is_package = FALSE
      AND ai.create__di = '160539'
      INNER JOIN _item_lass AS aic2 ON ai.small_class_di = aic2.di
      AND aic2.delflg = 0
      AND aic2.LEVEL = 1
      AND aic2.create__di = '160539'
      LEFT JOIN kc_item AS ki ON ai.di = ki.item_di
      AND ki.item_size_di = -1
      AND ki.delflg = 0
      AND ki.belong__di = '160540'
      LEFT JOIN mach_cobine_dishes AS bmcd ON ai.di = bmcd.item_di
      AND bmcd.size_di = -1
      AND bmcd.delflg = 0
      AND bmcd.create__di = ai.create__di
    WHERE
      kifd.itemfilter_di IN ('16053900000001')
  ) AS ata
GROUP BY
  ata.planId,
  ata.detail_di,
  ata.item_di,
  ata.item_code,
  ata.item_name,
  ata.item_pinyin,
  ata.unit_di,
  ata.unit_name,
  ata.enable_muti_size,
  ata.small_class_di,
  ata.small_class_code,
  ata.small_class_name,
  ata.lane_di,
  ata.lane_name,
  ata.standardTime,
  ata.warnTime
ORDER BY
  small_class_code

QUERY PLAN                                                                                                                                       
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate  (cost=4296.87..4296.98 rows=2 wdith=861) (actual time=2067.727..2068.947 rows=1068 loops=1)
 Group Key: