随着互联网技术在商业应用中的广泛普及,数据库应用日益庞大复杂,随之数据库性能表现的重要性也越发凸显。在数据库的众多性能问题中,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;