干货 | 基于KingbaseES的SQL性能调优（附案例）

随着互联网技术在商业应用中的广泛普及，数据库应用日益庞大复杂，随之数据库性能表现的重要性也越发凸显。在数据库的众多性能问题中，SQL性能问题占有极大的比例，因此，SQL性能调优也就成为了数据库管理员进阶需要掌握的一项重要技能。凭借着金仓的多年来的海量应用数据，我们总结出了一些SQL性能调优经验供读者使用。

常见问题分析

SQL性能问题一般表现在过长的响应时间，当时间长到应用无法忍受时，就会成为一个我们需要解决的性能问题。结合众多现场的情况，可以得出以下常见问题原因：

1、缺少SQL访问结构

缺少索引、物化视图、分区之类的SQL访问结构是导致SQL性能欠佳的典型原因。例如：

• 选择率较低的查询谓词在大表上使用全表扫描，而没有索引

• 排序、max/min等情况没有选用索引

TEST=# explain analyze select max(id) from t1;

                                                    QUERY PLAN

-------------------------------------------------------------------------------------------------------------------

 Aggregate  (cost=17906.00..17906.01 rows=1 width=4) (actual time=180.486..180.486 rows=1 loops=1)

   ->  Seq Scan on T1  (cost=0.00..15406.00 rows=1000000 width=4) (actual time=0.014..89.855 rows=1000000 loops=1)

 Planning time: 0.157 ms

Execution time: 180.531 ms

示例1-问题（解决方法见下文）

2、次优的执行计划

优化器在大部分时候都能给出较优的执行计划。但是，有时候，因为优化器自身的局限性，有可能选择不理想的计划。例如：

• 多表查询的连接顺序未能将可以过滤更多数据的两表连接最先执行

• 连接算法不够好

• 选择率的估算不准确
  

TEST=# explain analyze select * from t3,t1 where t3.c1 = t3.c2 and t3.c2 = t3.c3 and  t1.id = t3.c1;

                                                        QUERY PLAN

---------------------------------------------------------------------------------------------------------------------------

 Nested Loop  (cost=0.42..20617.31 rows=25 width=23) (actual time=0.029..1676.549 rows=1000000 loops=1)

   ->  Seq Scan on T3  (cost=0.00..20406.00 rows=25 width=12) (actual time=0.019..171.783 rows=1000000 loops=1)

         Filter: ((C1 = C2) AND (C2 = C3))

   ->  Index Scan using T1_ID_IDX on T1  (cost=0.42..8.44 rows=1 width=11) (actual time=0.001..0.001 rows=1 loops=1000000)

         Index Cond: (ID = T3.C1)

 Planning time: 0.819 ms

Execution time: 1705.336 ms

(7 rows)

示例2-问题（解决方法见下文）

3、陈旧的统计信息

当统计信息维护操作（自动或手动）无法跟上DML导致的表数据更改时，收集到的统计信息可能会过时。由于表上的陈旧统计信息无法准确反映表数据，因此优化器可以基于错误信息做出决策并生成次优执行计划。

TEST=# explain analyze select * from student where sno > 2;

                                                      QUERY PLAN

-----------------------------------------------------------------------------------------------------------------------

 Bitmap Heap Scan on STUDENT  (cost=60.12..156.12 rows=2560 width=40) (actual time=1.381..3.588 rows=9998 loops=1)

   Recheck Cond: (SNO > 2)

   Heap Blocks: exact=64

   ->  Bitmap Index Scan on IDX_STU  (cost=0.00..59.48 rows=2560 width=0) (actual