天天快消息！遇到慢SQL該怎么辦？（下）

改寫規(guī)則

改寫條件

改寫說明

原始查詢語句示例

改寫后語句示例

將"select distinct *" 改寫為"select *"

所查詢表格含唯一列或主鍵

通過確定 tuple 無重復(fù)，去掉 distinct，從而省去去重步驟，提升效率

select distinct * from bmsql_customer limit 10;

select * from bmsql_customer limit 10;

將 having 子句中條件放到 where 子句中

-

將謂詞表達式提前，可有效縮減 group 時的數(shù)據(jù)集

select cfg_name from bmsql_config group by cfg_name having cfg_name="1"

select cfg_name from bmsql_config where cfg_name = "1" group by cfg_name

簡化 where 子句中謂詞表達式

-

某些復(fù)雜謂詞無法有效觸發(fā) openGauss 內(nèi)的 rewrite 邏輯，無法使用索引掃描

select o_w_id, o_d_id, o_id, o_c_id from bmsql_oorder where o_w_id + 1> 3

select o_w_id, o_d_id, o_id, o_c_id from bmsql_oorder where o_w_id > 2

將 order by 或 group by 中的無用列去掉

group by 或 order by 涉及的列包含在 where 子句中的等值表達式中

去掉無用字段，SQL 更為簡潔

select cfg_name from bmsql_config where cfg_name="2" group by cfg_name order by cfg_name, cfg_value

select cfg_name from bmsql_config where cfg_name = "2" order by cfg_value

去掉 where 子句中永為真的表達式

-

去掉無用字段，SQL 更為簡潔

select * from bmsql_config where 1=1 and 2=2 limit 10

select * from bmsql_config limit 10

將 union 轉(zhuǎn)換為 union all

-

避免了去重帶來的執(zhí)行代價

select * from bmsql_config union select * from bmsql_config

select * from bmsql_config union all select * from bmsql_config

將 delete 語句轉(zhuǎn)換為 truncate 語句

無 where 子句

將 DML 語句轉(zhuǎn)換為 DDL 語句，一次性回收表空間，執(zhí)行速度更快

delete from bmsql_config

truncate table bmsql_config

將 where 子句中 "or" 連接的等式轉(zhuǎn)換為 "in" 結(jié)構(gòu)

-

"in" 結(jié)構(gòu)可加快過濾速度

select * from bmsql_stock where s_w_id=10 or s_w_id=1 or s_w_id=100 or s_i_id=1 or s_i_id=10

select * from bmsql_stock where s_w_id in (1,10,100) or s_i_id in(1,10)

將 self join 查詢拆分為效率更高兩個子查詢

1) self join 查詢。

2) where 子句包含相同列差值的范圍查詢。

例如 1

通過等值謂詞加快查詢速度

select a.c_id from bmsql_customer a, bmsql_customer b where a.c_id - b.c_id <= 20 and a.c_id > b.c_id

select * from (select a.c_id from bmsql_customer as a, bmsql_customer as b where trunc((a.c_id) / 20) = trunc(b.c_id / 20) and a.c_id > b.c_id union all select a.c_id from bmsql_customer as a, bmsql_customer as b where trunc((a.c_id) / 20) = trunc(b.c_id / 20 + 1) and a.c_id - b.c_id <= 20)

檢查項

檢查方式（系統(tǒng)表或系統(tǒng)視圖）

檢查方法

語句執(zhí)行時存在鎖競爭

dbe_perf.statement_history(start_time,finish_time, query)、pg_locks(pid, mode, locktype, grant)、pg_stat_activity(xact_start, query_start, query, pid)

查詢在語句執(zhí)行期間是否被阻塞。

表中死元組占比超過設(shè)定閾值

dbe_perf.statement_history(query, dbname, schemaname)

pg_stat_users_tables(relname, schemaname,n_live_tup, n_dead_tup)

n_dead_tup /n_live_tup，占比超過閾值則認為表過度膨脹 (默認閾值：0.2)。

語句掃描行數(shù)較多

dbe_perf.statement_history(n_tuples_fetched, n_tuples_returned, n_live_tup, n_dead_tup)

n_tuples_fetched+n_tuples_returned，超過閾值則認為過大（默認閾值：10000）。

語句緩存命中率較低

dbe_perf.statement_history(n_blocks_fetched, n_blocks_hit)

n_block_hit /n_block_fetched，小于閾值則認為較低（默認閾值：0.95）

慢 SQL (delete、insert、update) 相關(guān)表存在冗余索引

dbe_perf.statement_history(dbname, schemaname, query), pg_stat_user_indexes(schemaname, relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch)

pg_indexes(schemaname, tablename, indexname, indexdef)

SQL 相關(guān)表滿足：① 不是唯一索引；② (idx_scan, idx_tup_read,idx_tup_fetch)=(0,0,0)；③ 索引不在數(shù)據(jù)庫的（"pg_catalog", "information_schema","snapshot", "dbe_pldeveloper"）schema 下。如果滿足則認為次索引為冗余索引，否則為有效索引。

更新數(shù)據(jù)量較多

dbe_perf.statement_history(query, n_tuples_updated)

pg_stat_user_tables(n_live_tup, n_dead_tup)

n_tuples_updated 超過閾值則認為更新數(shù)據(jù)量較多（默認閾值：1000）。

插入數(shù)據(jù)量較多

dbe_perf.statement_history(query, n_tuples_inserted)

pg_stat_user_tables(n_live_tup, n_dead_tup)

n_tuples_inserted 超過閾值則認為插入數(shù)據(jù)量較多（默認閾值：1000）。

刪除數(shù)據(jù)量較多

dbe_perf.statement_history(query, n_tuples_deleted)

pg_stat_user_tables(n_live_tup, n_dead_tup)

n_tuples_deleted 超過閾值則認為刪除數(shù)據(jù)量較多（默認閾值：1000）。

相關(guān)表索引個數(shù)較多

pg_stat_user_indexes(relname,schemaname, indexrelname)

如果表中索引數(shù)大于閾值并且索引與字段數(shù)比率超過設(shè)定閾值，則認為索引數(shù)較多（索引個數(shù)閾值：3，比率默認閾值：0.6）。

執(zhí)行語句發(fā)生落盤（外排序）行為

dbe_perf.statement(sort_count, sort_spilled_count, sort_mem_used, hash_count, hash_spilled_count, hash_ued_mem, n_calls)

分析指標判斷是否有 hash 或者 order 導(dǎo)致的落盤行為，主要邏輯為：

1 如果 sort_count 或者 hash_count 不為 0，sort_mem_used 或者 hash_mem_used 為 0，則此 SQL 一定發(fā)生了落盤行為；

2 如果 sort_spilled_count 或者 hash_spilled_count 不為 0，則執(zhí)行可能發(fā)生落盤行為；

語句執(zhí)行期間相關(guān)表正在執(zhí)行 AUTOVACUUM 或 AUTOANALYZE 操作

dbe_perf.statement_history(start_time, finish_time, query)

pg_stat_user_tables(last_autovacuum, last_autoanalyze)

執(zhí)行 SQL 期間，正在發(fā)生 vacuum 或者 analyze 行為。

數(shù)據(jù)庫 TPS 較大

dbe_perf.statement_history(start_time, finish_time)

pg_stat_database(datname, xact_commit, xact_rolback)

相對于正常業(yè)務(wù)時的 TPS，當(dāng)前 TPS 增長較大，則認為數(shù)據(jù)庫 TPS 較大；TPS 短期內(nèi)增長異常則認為是業(yè)務(wù)風(fēng)暴。

IOWait 指標大于設(shè)定閾值

系統(tǒng) IOWait 指標異常升高

IOWait 大于用戶設(shè)定閾值（默認閾值：10%）

IOPS 指標大于設(shè)定閾值

系統(tǒng) IOPS 指標異常

IOPS 指標大于用戶設(shè)定閾值（默認閾值：1000）。

load average 指標大于設(shè)定閾值

系統(tǒng) load average 指標異常

load average 與服務(wù)器邏輯核數(shù)比率大于用戶設(shè)定閾值（默認閾值：0.6）。

CPU USAGE 指標大于設(shè)定閾值

系統(tǒng) CPU USAGE 指標異常

CPU USAGE 指標大于用戶設(shè)定閾值（默認閾值：0.6）。

IOUTILS 指標大于設(shè)定閾值

系統(tǒng) IOUTILS 指標異常

IOUTILS (磁盤利用率) 大于用戶設(shè)定閾值（默認閾值：0.5）。

IOCAPACITY 指標大于設(shè)定閾值

系統(tǒng) IO CAPACITY 指標異常

IOCAPACITY (IO 吞吐量) 大于用戶設(shè)定閾值 (默認閾值：50MB/s)。

IODELAY 指標大于設(shè)定閾值

系統(tǒng) IO DELAY 指標異常

IO DELAY（IO 延遲）大于用戶設(shè)定閾值（默認閾值：50ms）。

網(wǎng)卡丟包率

系統(tǒng)網(wǎng)卡丟包率異常

NETWORK DROP RATE 大于用戶設(shè)定閾值（默認 0 閾值：0.01）。

網(wǎng)卡錯誤率

系統(tǒng)網(wǎng)卡錯誤率異常

NETWORK ERROR RATE 大于用戶設(shè)定閾值（默認閾值：0.01）。

線程池占用量異常

dbe_perf.global_threadpool_status

數(shù)據(jù)庫線程池使用率大于閾值（默認閾值：0.95）

連接池占用量異常

pg_settings.max_connections，pg_stat_activity

數(shù)據(jù)庫連接池占用率大于閾值（默認閾值：0.8）

雙寫延遲較大

dbe_perf.wait_events

雙寫延遲大于閾值（默認閾值：100us）

表長時間未更新

pg_stat_user_tables

表未更新時長超過閾值（默認閾值：60s）

checkpoint 效率低（本規(guī)則僅作為粗略判斷）

pg_stat_bgwriter

數(shù)據(jù)庫 buffers_backend 與（buffers_clean+buffers_checkpoint）占比小于閾值（默認閾值：1000）

主備復(fù)制效率較低

pg_stat_replication

主備 write_diff、replay_diff、sent_diff 超過閾值（默認閾值：500000）

執(zhí)行計劃存在異常 seqscan 算子

執(zhí)行計劃

seqscan 算子代價與總代價比率超過閾值（默認閾值：0.3），此特征也會判斷是否缺少相關(guān)索引。

執(zhí)行計劃存在異常 nestloop 算子

執(zhí)行計劃

nestloop 算子代價與總代價比率超過閾值（默認閾值：0.3）并且進行 nestloop 的結(jié)果集行數(shù)超過閾值（默認閾值：10000）。

執(zhí)行計劃存在異常 hashjoin 算子

執(zhí)行計劃

hashjoin 算子代價與總代價比率超過閾值（默認閾值：0.3）并且進行 hashjoin 的結(jié)果集小于閾值（默認閾值：10000）。

執(zhí)行計劃存在異常 groupagg 算子

執(zhí)行計劃

groupagg 算子代價與總代價比率超過閾值（默認閾值：0.3）并且執(zhí)行 groupagg 的行數(shù)超過閾值（默認閾值：10000）。

SQL 寫法不優(yōu)

SQL 文本、pg_stat_user_tables

SQL 寫法不優(yōu)導(dǎo)致執(zhí)行性能較差

SQL 執(zhí)行被定時任務(wù)影響

pg_job,

dbe_perf.statement_history

定時任務(wù)執(zhí)行影響了 SQL 執(zhí)行性能，考慮調(diào)整定時任務(wù)時間，避免產(chǎn)生影響。

執(zhí)行計劃生成時間較長

dbe_perf.statement_history

SQL 執(zhí)行計劃生成時間較長。