GaussDB性能調優-常見解決方法
目錄
1 分頁查詢
2 鎖并發控制
3 返回大數量級結果集
4 大批量INSERT
1 分頁查詢
分頁查詢在數據庫應用中很常見，幾種常見的分頁查詢場景。分頁查詢一般是要求排序的，如果可以不排序，性能一般沒有問題，下面重點探討需要排序的情況。
常見分頁查詢寫法有以下兩種：
● 第一種
select * from
( select a.*, rownum rn from
( select * from tab1 u where status = 1 order by u.id) a
where rownum <= 500
) b
where rn >= 1
● 第二種
select * from tab1 u where status = 1 order by u.id limit 500, 1
場景優化及建議
● 場景1
場景描述：select * from tab1 u where status = 1查出的數據非常多，表中90%
以上的數據滿足條件。
優化建議：為了避免對大表全表掃描并且排序，建議走排序字段的索引規避排序（index full sacn），尤其是大部分的查詢是訪問第一頁或前幾頁的話，這樣性能是很高的。
● 場景2
場景描述：select * from tab1 u where status = 1查出的數據非常少，表中5%以
下數據滿足條件
優化建議：此時應該走where條件的索引。
● 場景3
場景描述：如果是對一個大表不斷翻頁，直到把數據處理完
優化建議：如果還用普通的分頁查詢的SQL，那么越往后會越慢，很多數據會重
復掃描，效率很低。
比較好的做法如下：
select a.*, rownum rn from
( select * from tab1 u where status = 1 and id > :max_id order by u.id) a
where rownum <= 500
或者
select * from tab1 where id > :max_id order by id limit 500;
每次獲取上次的最大ID，查詢時指定大于上次的最大ID，走ID索引實際不排序，
這樣的效率會比較高，每次查詢的效率也是比較穩定的。
● 場景4
場景描述：需要對統計分頁數據的總數，并且數據量很大
優化建議：如果每次分頁查詢都需要統計總數，并且數據量還很大，那么性能肯
定很差，建議業務上對結果緩存或者不統計總數，例如可以顯示前10頁。
2 鎖并發控制
場景分析
● 重復插入數據
問題描述：需要判斷某條數據是否存在，如果不存在則插入該數據。在并發場景下，如果不做鎖控制并且表上沒有唯一鍵的話，就可能出現重復插入數據的情況。
例如，用戶訂購業務前判斷下是否存在該業務，但是用戶ID和業務ID并不是聯合主鍵，并發場景下，兩個會話同時查詢某個用戶是否有該業務訂購關系時結果都是不存在，然后兩個會話就會為該用戶重復訂購了該業務。
解決方法：
– 通過行鎖，在查詢用戶會否有該訂購關系前先鎖定用戶的某條數據。例如，鎖定用戶的信息表中的數據，這里肯定不能去鎖定要插入的數據，因為數據還不存在，也不能鎖定公共數據，那樣的話所有用戶的并發都受影響了。鎖的方式最好是使用select for update，比update開銷要小一些。
方案缺點：必須有適合鎖定的數據，不夠通用；必須在一個事務中，否則commit后鎖就不存在了。
– 咨詢鎖
咨詢鎖是用戶自定義鎖，COMMIT/ROLLBACK不會釋放鎖，需要用戶顯式的釋放鎖，會話中斷會自動釋放鎖。
使用咨詢鎖可以不鎖定任何用戶數據解決這個問題，并且可以跨事務，推薦使用該方案。
? GET_LOCK(name_expr[, timeout_expr])
GET_LOCK()的返回值如下：
1： 成功獲取到鎖。
0： 未能獲取到鎖。
通過GET_LOCK()獲取到的鎖可通過以下兩種方式釋放：
顯式釋放：通過調用RELEASE_LOCK()釋放。
隱式釋放：會話中斷(不論正常或異常)時該會話占有的鎖自動釋放。
? RELEASE_LOCK(name_expr)
功能：通過鎖名釋放會話先前使用GET_LOCK()函數上的鎖。
RELEASE_LOCK()的返回值如下：
1： 成功獲取所指定的鎖。
NULL： 當前會話并不占有所指定的鎖。
● 并發更新同一條數據
問題描述：用戶開戸后更新當天
開戸人數，如果很多會話要同時更新一條數據，那么事務鎖等待會成為性能瓶頸。一般情況業務要避免出現這樣的邏輯，如果必須這樣做，也要盡量減少鎖的影響。如果一個
開戸事務執行100ms，更新
開戸數需要5ms，也是在
開戸事務中，怎么做性能高？
解決方案：以下三種方式，雖然只是更新
開戸數的位置不同，但是對性能影響差
距是很大的。如果在
開戸事務開始就更新用戶數，那么差不多要鎖定100ms，而如果在最后更新用戶數，那么最多鎖定幾毫秒。所以，對于可能阻塞其他會話的SQL應該盡量放在事務的最后面。
– 先更新
開戸數
– 中間更新
開戸數
– 最后更新
開戸數
3 返回大數量級結果集
如果查詢結果返回很多數據，設置fetch_size非常重要，如果fetch_size過小會導致交互
次數多、查詢效率低；如果fetch_size過大或者全部返回，客戶端程序的內存可能會撐爆。不同數據庫的默認fetch_size不同，GaussDB T默認大小是100，對應參數：
_PREFETCH_ROWS。
這個特性非常重要，應用程序在返回大數據量結果集時就不要分批查詢了，因為分批
查詢比較麻煩，性能也可能很差，只要合理的設置fetch_size就可以一次查詢所有數據
了，應用不用分批查詢，由數據庫分批fetch。
JDBC接口：java.sql.PreparedStatement.setFetchSize(int)
不過，如果一次性查詢太多的數據，可能出現快照過舊的問題，需要綜合考慮。
4 大批量 INSERT
大批量的insert數據是業務常見的場景，為了提高性能需要注意以下方面：
● 使用動態extent表空間：默認8K的extent性能比較差。
● 要有足夠的redo：redo過小會導致日志追尾。
● 使用分區表和分區索引：往一個空分區插入數據比往一個大表插入數據性能高，主要是維護索引的開銷不同；同時往不同分區insert比同時往一個分區insert效率高，因為可以減少beffer busy waits。
● 避免過多的索引：表上有過多的索引對insert性能影響很大。
● 一次parse，多次bind：可以減少parse的次數。
● 避免逐條commit：逐條commit回導致log fi?? sync較多的等待。
● 避免分布式事務：分布式事務開銷比單機事務大。
2020年3月13日
