共计 5196 个字符，预计需要花费 13 分钟才能阅读完成。
摘要 ：那些会导致执行效率低下的 SQL 语句及其执行形式，咱们称之为 SQL 中的“坏滋味”。
◆ 什么是 SQL 中的坏滋味
SQL 语言是关系型数据库（RDB）的规范语言，其作用是将使用者的用意翻译成数据库可能了解的语言来执行。人类之间进行交换时，同样的意思用不同的措辞会产生不同的成果。相似地，人类与数据库交流信息时，同样的操作用不同的 SQL 语句来表白，也会导致不同的效率。而有时同样的 SQL 语句，数据库采纳不同的形式来执行，效率也会不同。那些会导致执行效率低下的 SQL 语句及其执行形式，咱们称之为 SQL 中的“坏滋味”。
上面这个简略的例子，能够阐明什么是 SQL 中的坏滋味。
图 1 -a 用 union 合并汇合
在下面的查问语句中，因为应用了 union 来合并两个后果集，在合并后须要排序和去重，减少了开销。实际上合乎 dept_id = 1 和 dept_id > 2 的后果间不会有重叠，所以齐全能够用 union all 来合并，如下图所示。
图 1 -b 用 union all 合并汇合
而更高效的做法是用 or 条件，在扫描的时候间接过滤出所需的后果，岂但节俭了运算，也节俭了保留两头后果所需的内存开销，如下图所示。
图 1 -c 用 or 条件来过滤后果
可见实现同样的操作，用不同的 SQL 语句，效率却天壤之别。前两条 SQL 语句都不同水平地存在着“坏滋味”。
对于这种简略的例子，用户能够很容易发现问题并选出最佳计划。但对于一些简单的 SQL 语句，其性能缺点可能很荫蔽，须要深入分析才有可能开掘进去。这对数据库的使用者提出了很高的要求。即使是资深的数据库专家，有时也很难找出性能劣化的起因。
GaussDB 在执行 SQL 语句时，会对其性能体现进行剖析和记录，通过视图和函数等伎俩出现给用户。本文将简要介绍如何利用 GaussDB 提供的这些“第一手”数据，剖析和定位 SQL 语句中存在的性能问题，辨认和打消 SQL 中的“坏滋味”。
◆ 辨认 SQL 坏滋味之自诊断视图
GaussDB 在执行 SQL 时，会对执行打算以及执行过程中的资源耗费进行记录和剖析，如果发现异常状况还会记录告警信息，用于对起因进行“自诊断”。用户能够通过上面的视图查问这些信息：
• gs_wlm_session_info
• pgxc_wlm_session_info
• gs_wlm_session_history
• pgxc_wlm_session_history
其中 gs_wlm_session_info 是根本表，其余 3 个都是视图。gs_结尾的用于查看以后 CN 节点上收集的信息，pgxc_结尾的则蕴含集群中所有 CN 收集的信息。各表格和视图的定义基本相同，如下表所示。
表 1 自诊断表格 & 函数字段定义
其中的 query 字段就是执行的 SQL 语句。通过剖析每个 query 对应的各字段，例如执行工夫，内存，IO，下盘量和歪斜率等等，能够发现疑似有问题的 SQL 语句，而后联合 query_plan（执行打算）字段，进一步地加以分析。特地地，对于一些在执行过程中发现的异常情况，warning 字段还会以 human-readable 的模式给出告警信息。目前可能提供的自诊断信息如下：
◇多列 / 单列统计信息未收集
优化器依赖于表的统计信息来生成正当的执行打算。如果没有及时对表中各列收集统计信息，可能会影响优化器的判断，从而生成较差的执行打算。如果生成打算时发现某个表的单列或多列统计信息未收集，warning 字段会给出如下告警信息：
Statistic Not Collect:
schemaname.tablename(column name list)此外，如果表格的统计信息已收集过（执行过 analyze），然而间隔上次 analyze 工夫较远，表格内容产生了很大变动，可能使优化器依赖的统计信息不准，无奈生成最优的查问打算。针对这种状况，能够用 pg_total_autovac_tuples 零碎函数查问表格中自从上次剖析以来发生变化的元组的数量。如果数量较大，最好执行一下 analyze 以使优化器取得最新的统计信息。
◇SQL 未下推
执行打算中的算子，如果能下推到 DN 节点执行，则只能在 CN 上执行。因为 CN 的数量远小于 DN，大量操作沉积在 CN 上执行，会影响整体性能。如果遇到不能下推的函数或语法，warning 字段会给出如下告警信息：
SQL is not plan-shipping, reason : %s◇Hash 连贯大表做内表
如果发现在进行 Hash 连贯时应用了大表作为内表，会给出如下告警信息：
PlanNode[%d] Large Table is INNER in HashJoin "%s"目前“大表”的规范是均匀每个 DN 上的行数大于 100，000，并且内表行数是表面行数的 10 倍以上。
◇大表等值连贯应用 NestLoop
如果发现对大表做等值连贯时应用了 NestLoop 形式，会给出如下告警信息：
PlanNode[%d] Large Table with Equal-Condition use Nestloop"%s"目前大表等值连贯的判断规范是内表和表面中行数最大者大于 DN 的数量乘以 100，000。
◇数据歪斜
数据在 DN 之间散布不平均，可导致数据较多的节点成为性能瓶颈。如果发现数据歪斜重大，会给出如下告警信息：
PlanNode[%d] DataSkew:"%s", min_dn_tuples:%.0f, max_dn_tuples:%.0f目前数据歪斜的判断规范是 DN 中行数最多者是起码者的 10 倍以上，且最多者大于 100,000。
◇代价估算不精确
GaussDB 在执行 SQL 语句过程中会统计理论付出的代价，并与之前预计的代价比拟。如果优化器对代价的估算与理论的偏差很大，则很可能生成一个非最优化的打算。如果发现代价预计不精确，会给出如下告警信息：
"PlanNode[%d] Inaccurate Estimation-Rows:"%s" A-Rows:%.0f, E-Rows:%.0f目前的代价由打算节点返回行数来掂量，如果均匀每个 DN 上理论 / 预计返回行数大于 100,000，并且二者相差 10 倍以上，则认定为代价估算不准。
◇Broadcast 量过大
Broadcast 次要适宜小表。对于大表来说，通常采纳 Hash+ 重散布（Redistribute）的形式效率更高。如果发现打算中有大表被播送的环节，会给出如下告警信息：
PlanNode[%d] Large Table in Broadcast "%s"目前对大表播送的认定规范为均匀播送到每个 DN 上的数据行数大于 100,000。
◇索引设置不合理
如果对索引的应用不合理，比方应该采纳索引扫描的中央却采纳了程序扫描，或者应该采纳程序扫描的中央却采纳了索引扫描，可能会导致性能低下。
索引扫描的价值在于缩小数据读取量，因而认为索引扫描过滤掉的行数越多越好。如果采纳索引扫描，但输入行数 / 扫描总行数 >1/1000，并且输入行数 >10000（对于行存表）或 >100（对于列存表），则会给出如下告警信息：
PlanNode[%d] Indexscan is not properly used:"%s", output:%.0f, filtered:%.0f, rate:%.5f程序扫描实用于过滤的行数占总行数比例不大的情景。如果采纳程序扫描，但输入行数 / 扫描总行数 <=1/1000，并且输入行数 <=10000（对于行存表）或 <=100（对于列存表），则会给出如下告警信息：
PlanNode[%d] Indexscan is ought to be used:"%s", output:%.0f, filtered:%.0f, rate:%.5f◇下盘量过大或过早下盘
SQL 语句执行过程中，因为内存不足等起因，可能须要将两头后果的全副或一部分转储的磁盘上。下盘可能导致性能低下，应该尽量避免。如果监测到下盘量过大或过早下盘等状况，会给出如下告警信息：
• Spill file size large than 256MB
• Broadcast size large than 100MB
• Early spill
• Spill times is greater than 3
• Spill on memory adaptive
• Hash table conflict
下盘可能是因为缓冲区设置得过小，也可能是因为表的连贯程序或连贯形式不合理等起因，要联合具体的 SQL 进行剖析。能够通过改写 SQL 语句，或者 HINT 指定连贯形式等伎俩来解决。
应用自诊断视图性能，须要将以下变量设成适合的值：
▲ use_workload_manager（设成 on，默认为 on）
▲ enable_resource_check（设成 on，默认为 on）
▲ resource_track_level（如果设成 query，则收集 query 级别的信息，如果设成 operator，则收集所有信息，如果设成 none，则以用户默认的 log 级别为准）
▲ resource_track_cost（设成适合的正整数。为了不影响性能，只有执行代价大于 resource_track_cost 语句才会被收集。该值越大，收集的语句越少，对性能影响越小；反之越小，收集的语句越多，对性能的影响越大。）
执行完一条代价大于 resource_track_cost 后，诊断信息会寄存在内存 hash 表中，可通过 pgxc_wlm_session_history 或 gs_wlm_session_history 视图查看。
视图中记录的有效期是 3 分钟，过期的记录会被零碎清理。如果设置 enable_resource_record=on，视图中的记录每隔 3 分钟会被转储到 gs_wlm_session_info 表中，因而 3 分钟之前的历史记录能够通过 gs_wlm_session_info 表或 pgxc_wlm_session_info 视图查看。
◆ 发现正在运行的 SQL 的坏滋味
上一节提到的自诊断视图能够显示已实现 SQL 的信息。如果要查看正在运行的 SQL 的状况，能够应用上面的视图：
• gs_wlm_session_statistics
• pgxc_wlm_session_statistics
相似地，gs_结尾的用于查看以后 CN 节点上收集的信息，pgxc_结尾的则蕴含集群中所有 CN 收集的信息。两个视图的定义与上一节的自诊断视图基本相同，应用办法也基本一致。通过观察其中的字段，能够发现正在运行的 SQL 中存在的性能问题。
例如，通过“select queryid, duration from gs_wlm_session_statistics order by duration desc limit 10;”能够查问以后运行的 SQL 中，曾经执行工夫最长的 10 个 SQL。如果工夫过长，可能有必要剖析一下起因。
图 2 -a 通过 gs_wlm_session_statistics 视图发现可能 hang 住 SQL
查到 queryid 后，能够通过 query_plan 字段查看该 SQL 的执行打算，剖析其中可能存在的性能瓶颈和异样点。
图 2 -b 通过 gs_wlm_session_statistics 视图查看以后 SQL 的执行打算
再下一步，能够联合期待视图等其余伎俩定位性能劣化的起因。
图 2 -c 通过 gs_wlm_session_statistics 视图联合期待视图定位性能问题
另外，流动视图 pg_stat_activity 也能提供一些以后执行 SQL 的信息。
◆ Top SQL——利用统计信息发现 SQL 坏滋味
除了针对逐条 SQL 进行剖析，还能够利用统计信息发现 SQL 中的坏滋味。另一篇文章“Unique SQL 个性原理与利用”中提到的 Unique SQL 个性，可能针对执行打算雷同的一类 SQL 进行了性能统计。
与自诊断视图不同的是，如果同一个 SQL 被屡次执行，或者多个 SQL 语句的构造雷同，只有条件中的常量值不同。这些 SQL 在 Unique SQL 视图中会合并为一条记录。因而应用 Unique SQL 视图能更容易看出那些类型的 SQL 语句存在性能问题。
利用这一个性，能够找出某一指标或者某一资源占用量最高 / 最差的那些 SQL 类型。这样的 SQL 被称为“Top SQL”。例如，查找占用 CPU 工夫最长的 SQL 语句，能够用如下 SQL：
select unique_sql_id,query,cpu_time from pgxc_instr_unique_sql order by cpu_time desc limit 10。Unique SQL 的应用形式详见 https://bbs.huaweicloud.com/blogs/197299。
◆ 论断
发现 SQL 中的坏滋味是性能调优的前提。GaussDB 对数据库的运行状况进行了 SQL 级别的监控和记录。这些打点记录的数据能够帮忙用户发现可能存在的异常情况，“嗅”出潜在的坏滋味。从这些数据和提示信息登程，联合其余视图和工具，能够定位出坏滋味的起源，进而有针对性地进行优化。
点击关注，第一工夫理解华为云陈腐技术~
