关键字:
SQL调优、人大金仓、KingbaseES
SQL调优的目的
当 SQL 语句无法按照预期性能指标执行时,就会成为一个我们需要解决的性能问题。 在确定问题所在后,典型的性能调优至少包含一个以下目标: • 减少用户响应时间,这意味着减少用户发出请求到接收请求的时间。 • 提高吞吐量,这意味着使用最少的资源来处理请求。 考虑这样一个场景,对于一条执行时间为 30 秒的 SQL 语句,在硬件资源充足的情况下,考虑减少用户效应时 间,我们可以使用并行来提高执行效率;在硬件资源紧张的时候,考虑吞吐量,则需要通过索引、HINT 等手段来减 少资源的处理请求。问题不同则目标不同,目标不同则使用的手段也会不同。
SQL调优步骤
在确定调优目标之后(例如将用户响应时间从 60 秒降低到 5 秒),问题就变成了如何实现此目标。 SQL 调优通常是一个迭代的过程,为了实现得到目标,典型的 SQL 调优需要不断的重复下述的一个或多个步骤:
- 识别高负载语句 通过查看过去的执行历史记录,找到消耗了较多系统资源的 SQL 语句。KingbaseES 提供的工具包括:
● sys_stat_statement
●kbbadger
●SYS_KWR
2. 收集性能相关的数据 优化器统计信息是 SQL 调优的关键,如果统计信息不存在或不够及时准确,那么优化器将无法生成最优 的执行计划。与 SQL 性能相关的其他数据包括该语句访问的表和视图的结构,以及该语句可用的任何索 引的定义,方便后续性能问题原因的分析。 如何收集统计信息请参考本手册章节统计信息的收集 和扩展统计信息
3. 确定性能问题产生的原因 对于特定的 SQL 语句,要确定性能问题产生的原因,最重要的手段是分析执行计划。 分析方法参见本手册章节分析执行计划 通常来说,SQL 性能问题的原因包括:
● 缺少 SQL 访问结构
缺少索引、物化视图、分区之类的 SQL 访问结构是导致 SQL 性能不佳的典型原因。最佳的访 问结构可以将 SQL 性能提高几个数量级。 索引部分请参考本手册章节使用索引 物化视图请参考本手册章节物化视图
●次优的执行计划
优化器的主要作用是给出最优(较优)的执行计划。有时候,因为优化器自身的局限,会选择 访问路径不理想的计划。例如,选择率较低的条件在大表上使用全表扫描,而不是索引;多表 查询的连接顺序未能让可以过滤更多数据的两表连接最先执行。
● 陈旧的优化统计数据
当统计信息维护操作(自动或手动)无法跟上 DML 导致的表数据更改时,收集到的统计信息 可能会过时。由于表上的陈旧统计信息无法准确反映表数据,因此导致优化器基于错误信息做 出决策并生成次优执行计划
● 低效的 SQL 语句设计 如果编写 SQL 语句以使其执行不必要的工作,那么优化器将无法做很多事情来提高其性能。低 效设计的例子包括:
● 忽略添加连接条件,导致笛卡尔积连接
● 指定 UNION 而不是 UNION ALL • 使子查询针对外部查询中的每一行执行
● 硬件问题 受限于当前硬件环境(CPU、内存、IO、网络等),所有的优化手段都不足以达到预期性能, 则需要做硬件扩展。
● 架构问题 单机硬件扩展无法解决的情况下,则需要考虑读写分离集群等方案。
4. 定义问题的范围 解决方案的范围必须与问题的范围相匹配。在数据库级别考虑一个问题和在语句级别考虑一个问题的解决 方案是不一样的。 例如,在单个 SQL 语句未使用有用的索引的情况下,更改整个数据库的优化器参数(enable_seqscan = off)可能会损害整体性能。如果单个 SQL 语句有问题,则可以考虑通过 HINT、改写语句等方式将影响 范围限制在语句级。
5. 实施优化手段 在步骤 3 中,我们可以得知 SQL 性能问题产生的原因较为多样,所以优化手段的实施也会因情况而异。 例如,我们可能会通过将 UNION 改写成 UNIONALL 来提高性能,也可能通过创建索引来提高性能。
6. 评估优化效果 在实施优化手段后,通过实际执行来评估优化效果是否达到了预期。
3. 调优手段
KingbaseES 提供了多种手段来供用户做 SQL调优,常见的调优手段包括: 使用索引 ,使用 HINT ,调整参数 ,使用并行。
参考资料
《KingbaseES数据库SQL调优指南.pdf》