目錄

1 分頁查詢

2 鎖并發(fā)控制

3 返回大數(shù)量級(jí)結(jié)果集

4 大批量INSERT

1 分頁查詢

分頁查詢?cè)跀?shù)據(jù)庫應(yīng)用中很常見，幾種常見的分頁查詢場景。分頁查詢一般是要求排序的，如果可以不排序，性能一般沒有問題，下面重點(diǎn)探討需要排序的情況。

常見分頁查詢寫法有以下兩種：

● 第一種

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

場景優(yōu)化及建議

● 場景1

場景描述：select * from tab1 u where status = 1查出的數(shù)據(jù)非常多，表中90%

以上的數(shù)據(jù)滿足條件。

優(yōu)化建議：為了避免對(duì)大表全表掃描并且排序，建議走排序字段的索引規(guī)避排序（index full sacn），尤其是大部分的查詢是訪問第一頁或前幾頁的話，這樣性能是很高的。

● 場景2

場景描述：select * from tab1 u where status = 1查出的數(shù)據(jù)非常少，表中5%以

下數(shù)據(jù)滿足條件

優(yōu)化建議：此時(shí)應(yīng)該走where條件的索引。

● 場景3

場景描述：如果是對(duì)一個(gè)大表不斷翻頁，直到把數(shù)據(jù)處理完

優(yōu)化建議：如果還用普通的分頁查詢的SQL，那么越往后會(huì)越慢，很多數(shù)據(jù)會(huì)重

復(fù)掃描，效率很低。

比較好的做法如下：

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，查詢時(shí)指定大于上次的最大ID，走ID索引實(shí)際不排序，

這樣的效率會(huì)比較高，每次查詢的效率也是比較穩(wěn)定的。

● 場景4

場景描述：需要對(duì)統(tǒng)計(jì)分頁數(shù)據(jù)的總數(shù)，并且數(shù)據(jù)量很大

優(yōu)化建議：如果每次分頁查詢都需要統(tǒng)計(jì)總數(shù)，并且數(shù)據(jù)量還很大，那么性能肯

定很差，建議業(yè)務(wù)上對(duì)結(jié)果緩存或者不統(tǒng)計(jì)總數(shù)，例如可以顯示前10頁。

2 鎖并發(fā)控制

場景分析

● 重復(fù)插入數(shù)據(jù)

問題描述：需要判斷某條數(shù)據(jù)是否存在，如果不存在則插入該數(shù)據(jù)。在并發(fā)場景下，如果不做鎖控制并且表上沒有唯一鍵的話，就可能出現(xiàn)重復(fù)插入數(shù)據(jù)的情況。

例如，用戶訂購業(yè)務(wù)前判斷下是否存在該業(yè)務(wù)，但是用戶ID和業(yè)務(wù)ID并不是聯(lián)合主鍵，并發(fā)場景下，兩個(gè)會(huì)話同時(shí)查詢某個(gè)用戶是否有該業(yè)務(wù)訂購關(guān)系時(shí)結(jié)果都是不存在，然后兩個(gè)會(huì)話就會(huì)為該用戶重復(fù)訂購了該業(yè)務(wù)。

解決方法：

– 通過行鎖，在查詢用戶會(huì)否有該訂購關(guān)系前先鎖定用戶的某條數(shù)據(jù)。例如，鎖定用戶的信息表中的數(shù)據(jù)，這里肯定不能去鎖定要插入的數(shù)據(jù)，因?yàn)閿?shù)據(jù)還不存在，也不能鎖定公共數(shù)據(jù)，那樣的話所有用戶的并發(fā)都受影響了。鎖的方式最好是使用select for update，比update開銷要小一些。

方案缺點(diǎn)：必須有適合鎖定的數(shù)據(jù)，不夠通用；必須在一個(gè)事務(wù)中，否則commit后鎖就不存在了。

– 咨詢鎖

咨詢鎖是用戶自定義鎖，COMMIT/ROLLBACK不會(huì)釋放鎖，需要用戶顯式的釋放鎖，會(huì)話中斷會(huì)自動(dòng)釋放鎖。

使用咨詢鎖可以不鎖定任何用戶數(shù)據(jù)解決這個(gè)問題，并且可以跨事務(wù)，推薦使用該方案。

? GET_LOCK(name_expr[, timeout_expr])

GET_LOCK()的返回值如下：

1： 成功獲取到鎖。

0： 未能獲取到鎖。

通過GET_LOCK()獲取到的鎖可通過以下兩種方式釋放：

顯式釋放：通過調(diào)用RELEASE_LOCK()釋放。

隱式釋放：會(huì)話中斷(不論正?；虍惓?時(shí)該會(huì)話占有的鎖自動(dòng)釋放。

? RELEASE_LOCK(name_expr)

功能：通過鎖名釋放會(huì)話先前使用GET_LOCK()函數(shù)上的鎖。

RELEASE_LOCK()的返回值如下：

1： 成功獲取所指定的鎖。

NULL： 當(dāng)前會(huì)話并不占有所指定的鎖。

● 并發(fā)更新同一條數(shù)據(jù)

問題描述：用戶開戸后更新當(dāng)天 開戸人數(shù)，如果很多會(huì)話要同時(shí)更新一條數(shù)據(jù)，那么事務(wù)鎖等待會(huì)成為性能瓶頸。一般情況業(yè)務(wù)要避免出現(xiàn)這樣的邏輯，如果必須這樣做，也要盡量減少鎖的影響。如果一個(gè) 開戸事務(wù)執(zhí)行100ms，更新 開戸數(shù)需要5ms，也是在 開戸事務(wù)中，怎么做性能高？

解決方案：以下三種方式，雖然只是更新 開戸數(shù)的位置不同，但是對(duì)性能影響差

距是很大的。如果在 開戸事務(wù)開始就更新用戶數(shù)，那么差不多要鎖定100ms，而如果在最后更新用戶數(shù)，那么最多鎖定幾毫秒。所以，對(duì)于可能阻塞其他會(huì)話的SQL應(yīng)該盡量放在事務(wù)的最后面。

– 先更新 開戸數(shù)

– 中間更新 開戸數(shù)

– 最后更新 開戸數(shù)

3 返回大數(shù)量級(jí)結(jié)果集

如果查詢結(jié)果返回很多數(shù)據(jù)，設(shè)置fetch_size非常重要，如果fetch_size過小會(huì)導(dǎo)致交互

次數(shù)多、查詢效率低；如果fetch_size過大或者全部返回，客戶端程序的內(nèi)存可能會(huì)撐爆。不同數(shù)據(jù)庫的默認(rèn)fetch_size不同，GaussDB T默認(rèn)大小是100，對(duì)應(yīng)參數(shù)：

_PREFETCH_ROWS。

這個(gè)特性非常重要，應(yīng)用程序在返回大數(shù)據(jù)量結(jié)果集時(shí)就不要分批查詢了，因?yàn)榉峙?/div>
當(dāng)前標(biāo)題：GaussDB性能調(diào)優(yōu)-常見解決方法
標(biāo)題路徑：http://weahome.cn/article/gsgioo.html