目錄
3.3 視圖顯示訪問特定表的統(tǒng)計信息
3.4 視圖顯示特定索引的統(tǒng)計
四、參考資料
一、前言
關(guān)于sql優(yōu)化，面試或工作中，都是高頻遇到的問題。自己也時常被難住sql獲取當(dāng)前系統(tǒng)時間，基于此，特將看到或用到的一些sql優(yōu)化信息進行整理，以便有問題時，自己也可以方便復(fù)現(xiàn)或者解決問題。
二、sql優(yōu)化目標(biāo)思考出發(fā)點
為何優(yōu)化sql優(yōu)化，無非就是想要快速獲取查詢數(shù)據(jù)的結(jié)果，對應(yīng)到一些專業(yè)術(shù)語：高性能、高效。那判斷高效的標(biāo)準(zhǔn)：在滿足業(yè)務(wù)需求的情況下，執(zhí)行sql語句耗時最少或者盡可能少。
sql優(yōu)化的目標(biāo)是讓 sql程序執(zhí)行時間盡可能短，那如何發(fā)現(xiàn)需要優(yōu)化的sql呢？可以從以下三點出發(fā)考慮[1]：
針對上述考慮，進一步需要明確的優(yōu)化問題，可以從以下問題進一步明確：
三、sql優(yōu)化相關(guān)幫助視圖介紹3.1 系統(tǒng)內(nèi)置活動視圖
視圖將為每一個服務(wù)器進程顯示一行，顯示與該進程當(dāng)前活動相關(guān)的信息。理論上能捕抓到每個實時的SQL，運行結(jié)束可能就會消失，也就是說實際上有些運行時間短的SQL人為可能會看不到。該視圖可以實時查詢當(dāng)前在執(zhí)行的SQL，包括狀態(tài)，比如是否阻塞、等待鎖等。
3.1.1 視圖主要字段
. ：后端正在等待的事件類型，如果不存在則為 NULL。可能的值有：
state：這個后端的當(dāng)前總體狀態(tài)。可能的值是：
3.1.2 常用sql
select?*?from?pg_stat_activity;
--?查找非空閑的SQL
select?*?from?pg_stat_activity?where?state?<>'idle';
--?按照開始時間進行排序
select?*,now()-query_start?from?pg_stat_activity?where?state?<>'idle'?ORDER?BY?query_start?asc?;
--?kill?SQL，pg_terminate_backend(PID);
SELECT?pg_terminate_backend(15322);阻塞問題：如果是非異常的阻塞，可以直接kill掉，使用。
注意：
其他字段的詳細(xì)介紹可以進一步參考：[2]監(jiān)控數(shù)據(jù)庫活動。
3.2 SQL執(zhí)行統(tǒng)計視圖
數(shù)據(jù)庫資源分為多個維度、CPU、內(nèi)存、IO 等，為能夠從各個維度層面查找最消耗數(shù)據(jù)庫資源的 SQL，您可以使用 插件統(tǒng)計數(shù)據(jù)庫的資源開銷和分析 Top SQL。該統(tǒng)計視圖的使用需要安裝插件。
執(zhí)行如下命令，在需要查詢 TOP SQL 的數(shù)據(jù)庫中，創(chuàng)建 插件。
;
3.2.1 視圖輸出內(nèi)容介紹
通過查詢 視圖，您可以得到數(shù)據(jù)庫資源開銷的統(tǒng)計信息。SQL 語句中的一些過濾條件在 中會被替換成變量，可以減少重復(fù)顯示的問題。
視圖包含了一些重要信息，例如：：
下表列出了 輸出內(nèi)容中各參數(shù)的含義。
在這里插入圖片描述
中文版(簡版)
3.2.2 常見案例 Top SQL
--最耗IO?SQL
--單次調(diào)用最耗IO?SQL?TOP?5
select?userid::regrole,?dbid,?query?from?pg_stat_statements?order?by?(blk_read_time+blk_write_time)/calls?desc?limit?5;
--總最耗IO?SQL?TOP?5
select?userid::regrole,?dbid,?query?from?pg_stat_statements?order?by?(blk_read_time+blk_write_time)?desc?limit?5;
--最耗時?SQL
--單次調(diào)用最耗時?SQL?TOP?5
select?userid::regrole,?dbid,?query?from?pg_stat_statements?order?by?mean_time?desc?limit?5;?
--總最耗時?SQL?TOP?5
select?userid::regrole,?dbid,?query?from?pg_stat_statements?order?by?total_time?desc?limit?5;?
--響應(yīng)時間抖動最嚴(yán)重?SQL
select?userid::regrole,?dbid,?query?from?pg_stat_statements?order?by?stddev_time?desc?limit?5;?
--最耗共享內(nèi)存?SQL
select?userid::regrole,?dbid,?query?from?pg_stat_statements?order?by?(shared_blks_hit+shared_blks_dirtied)?desc?limit?5;
?
--最耗臨時空間?SQL
select?userid::regrole,?dbid,?query?from?pg_stat_statements?order?by?temp_blks_written?desc?limit?5;注意：因為參數(shù)化的問題，這里的SQL的是不可直接執(zhí)行的sql獲取當(dāng)前系統(tǒng)時間，我們需要根據(jù)SQL的一些信息，在IDE中找到對應(yīng)的邏輯，打日志，抓到對應(yīng)的SQL，進行分析。我們需要重點關(guān)注：調(diào)用次數(shù)，總耗時，平均執(zhí)行時間，這3個指標(biāo)，SQL執(zhí)行時間長，那說明有優(yōu)化的空間。
3.2.3 重置統(tǒng)計信息
是累積的統(tǒng)計，如果要查看某個時間段的統(tǒng)計，需要查詢快照的信息，詳情請參見《 AWR報告（for 阿里云 PgSQL）》。您也可以通過執(zhí)行如下命令，來定期清理歷史統(tǒng)計信息。
eset();
3.3 視圖顯示訪問特定表的統(tǒng)計信息
索引主要用于查詢邏輯，一般用于大表。反過來，我們需要關(guān)注索引的使用情況。日常維護中觀察大表（所占存儲空間大、記錄多、索引所占空間大的表）和索引的情況，考慮是否需要為關(guān)鍵字段加索引、刪掉未曾使用過的索引。
3.3.1 視圖輸出信息介紹
視圖將包含 當(dāng)前數(shù)據(jù)庫中每個表的一行（包括TOAST表），顯示訪問特定表的統(tǒng)計信息。和 視圖 包含相同的信息，但是過濾只分別顯示用戶和系統(tǒng)表。
在這里插入圖片描述
需要注意的數(shù)據(jù)：，，，。
3.3.2 常用sql
1.判斷表的大小
--?問下pg怎么求大小？！
select?*?from?pg_proc??where?proname?~?'size';
--?按照頁的數(shù)量進行排序
select?*?from?pg_class?where?relnamespace?=?2200?ORDER?BY?relpages?desc;
--?按照實體的大小進行排序
select?pg_size_pretty(pg_total_relation_size(oid)),*?from?pg_class?where?relnamespace?=?2200?and?relname?!~'bak|copy'?ORDER?BY?pg_total_relation_size(oid)?desc;
select?pg_size_pretty(pg_total_relation_size(oid)),*?from?pg_class?where?relnamespace?=?2200?and?relkind?=?'r'?and?relname?!~'bak|copy'?ORDER?BY?pg_total_relation_size(oid)?desc;
select?pg_size_pretty(pg_total_relation_size(oid)),*?from?pg_class?where?relnamespace?=?2200?and?relkind?=?'i'?and?relname?!~'bak|copy'?ORDER?BY?pg_total_relation_size(oid)?desc;
select?pg_size_pretty(pg_total_relation_size(oid)),*?from?pg_class?where?relnamespace?=?2200?and?relkind?=?'m'?and?relname?!~'bak|copy'?ORDER?BY?pg_total_relation_size(oid)?desc;1.關(guān)注表使用情況
--?seq_scan，idx_scan，n_live_tup，n_dead_tup
select?*?from?pg_stat_user_tables?limit?10;
select?*?from?pg_stat_user_tables?ORDER?BY?seq_scan?desc;
select?*?from?pg_stat_user_tables?ORDER?BY?idx_scan?desc;
select?*?from?pg_stat_user_tables?ORDER?BY?n_live_tup?desc;
select?*?from?pg_stat_user_tables?ORDER?BY?n_dead_tup?desc;
--?死亡元組多的表可以考慮清一波
select?'VACUUM?VERBOSE?ANALYZE?'||?relname?||?';',*?from?pg_stat_user_tables?ORDER?BY?n_dead_tup?desc;
--?排查：大表的全表掃描比索引掃描用得多的情況（考慮建索引）
select?*?from?pg_stat_user_tables?where?seq_scan?>?idx_scan?ORDER?BY?seq_scan?desc;
select?*?from?pg_stat_user_tables?where?n_live_tup?>?10000?and?seq_scan?>?idx_scan?ORDER?BY?seq_scan?desc;3.4 視圖顯示特定索引的統(tǒng)計3.4.1 視圖輸出內(nèi)容介紹
視圖將包含當(dāng)前數(shù)據(jù)庫中的每個索引行，顯示訪問特定索引的統(tǒng)計。和 視圖包含相同的信息， 但是過濾只是分別顯示用戶和系統(tǒng)索引。
在這里插入圖片描述3.4.2 常用sql
--?查看某個表索引使用情況
select?*?from?pg_stat_user_indexes?limit?10;
select?*?from?pg_stat_user_indexes?where?relname?=?'test'?ORDER?BY?idx_scan?asc;根據(jù) 視圖找到需要關(guān)注的表，然后根據(jù) 視圖查看該表的索引使用情況。
--?查看某個表索引使用情況
select?*?from?pg_stat_user_indexes?limit?10;
select?*?from?pg_stat_user_indexes?where?relname?=?'test'?ORDER?BY?idx_scan?asc;四、參考資料
[1]之如何進行SQL優(yōu)化?()
[2]監(jiān)控數(shù)據(jù)庫活動(#PG-STAT--VIEW)
[3]查找最耗費資源的 SQL（Top SQL）()
[4]:《 AWR報告（for 阿里云 PgSQL）》()