原文:http://blog.csdn.net/hantiannan/article/details/4509231 在数据库应用开发中,速度慢的SQL比比皆是。很多速度很慢都是SQL写的不好,效率不高。比如无用的循环查询,判断,不必要的子查询,写的SQL用不上索引等等。特别是数据量很大的时候,很是头疼。我就遇到过几千万条数据的表的查询,由于子查询过多,要几个小时才执行完。显然是不符合要求的。 那该怎么改善呢? |
要解决这个速度问题,我们首先最主要的是要找到那些SQL很慢,或者SQL中的那部分很慢。怎样寻找速度很慢的SQL,我们可以借助系统提供的统计信息功能来查找。 |
1.pg_stat_user_functions:SQL文中用了存储过程或者函数的情况,可以通过这个统计信息view来查看。 |
funcid,schemaname,funcname :函数的ID |
calls:执行回数 |
total_time :函数执行的总的时间,单位毫秒。 |
self_time :不包含其他函数的执行时间的自己本身的执行时间。 |
例子: |
|
=#SELECT * FROM pg_stat_user_functions; |
funcid | schemaname | funcname | calls |total_time | self_time |
--------+------------+----------+-------+------------+----------- |
16434 |public| proc_1|4|185|185 |
16738 |public| proc_2|5|91|91 |
16741 |public| proc_3|2|76|5 |
(3 rows) |
|
|
|
从这里面我们可以看到proc_3自己自身执行的时间相对于总的时间来说耗费的时间非常少,可以初步确定速度慢的函数不是proc_3。因此我就需要去查询其他函数的执行时间。 |
|
|
这个机能系统默认是没有的,需要在postgresql.conf里面设置。默认是none。需要改为pl或者all。 |
track_functions = pl # none,pl,all |
|
|
2.pg_stat_statements:contrib/pg_stat_statements模块下的机能,系统默认也是没有的。 |
userid:执行SQL的用户 |
dbid : 数据库ID |
query : SQL文 |
calls:执行回数 |
total_time:执行总时间。单位微秒。 |
rows:处理行数。返回的行数或者修改的行数。 |
|
|
例子: |
|
SELECT query,calls,total_time,rows |
FROM pg_stat_statements ORDER BY total_time DESC LIMIT 3; |
-[RECORD 1]------------------------------------------------------------ |
query| UPDATE branches SET bbalance = bbalance + $1 WHERE bid = $2; |
calls| 3000 |
total_time | 35.9654100 |
rows| 3000 |
|
|
这个功能也是需要在postgresql.conf里面设置。 |
shared_preload_libraries ='pg_stat_statements' |
custom_variable_classes ='pg_stat_statements' |
|
|
通过上面的两种方法我们就可以找到那些SQL或者函数很慢,这样就能够对症下药了,要不然真是找不原因,很耗费时间。 |